SQL Scripts for Microsoft Dynamics GP: Remaining Budget By Year Using PO Commitment Budget

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client who wanted to be able to see how much of the annual PO commitment budget was remaining. It takes into account postings to the GL, PO commitments and entered purchase requisitions. This particular client wasn;t interested in seeing having a period check, just a check against the overall annual budget amount.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	ACTINDX
	,SUM(VALUE) AS VALUE
	,YEAR1
FROM
	(SELECT
		GLB.ACTINDX AS ACTINDX
		,SUM(GLB.BUDGETAMT) AS VALUE
		,GLB.YEAR1 AS YEAR1
	FROM
		GL00201 AS GLB --[gpt=GL00201]
	INNER JOIN
		CPO40002 AS CPOS --[gpt=CPO40002]
			ON
				CPOS.BUDGETID = GLB.BUDGETID WHERE PERIODID NOT IN (0,13)
	GROUP BY
		GLB.ACTINDX
		,GLB.YEAR1
	UNION ALL
		SELECT
			GLPB.ACTINDX AS ACTINDX
			,SUM(ISNULL(GLPB.PERDBLNC,0)) * -1 AS VALUE
			,GLPB.YEAR1 AS YEAR1
		FROM
			GL10110 AS GLPB --[gpt=GL10110]
		WHERE
			GLPB.PERIODID NOT IN (0,13)
		GROUP BY
			GLPB.ACTINDX
			,GLPB.YEAR1
		UNION ALL
			SELECT
				CPO.ACTINDX AS ACTINDX
				,SUM(ISNULL(CPO.Committed_Amount,0)) * -1 AS VALUE
				,SYY.YEAR1 AS YEAR1
			FROM
				CPO10110 AS CPO --[gpt=CPO10110]
			INNER JOIN
				SY40100 AS SYY --[gpt=SY40100]
					ON
						SYY.ODESCTN = 'Budget Transaction Entry'
					AND
						PERIODDT <= CPO.REQDATE
					AND
						PERDENDT >= CPO.REQDATE
						AND
							PERIODID NOT IN (0,13)
			GROUP BY
				CPO.ACTINDX
				,SYY.YEAR1
			UNION ALL
				SELECT
					POP.INVINDX AS ACTINDX
					,SUM(ISNULL(POP.EXTDCOST,0)) * -1 AS VALUE
					,SYY.YEAR1 AS YEAR1
				FROM
					POP10210 AS POP --[gpt=POP10210]
				INNER JOIN
					SY40100 AS SYY --[gpt=SY40100]
						ON
							SYY.ODESCTN = 'Budget Transaction Entry'
						AND
							PERIODDT <= POP.REQDATE
						AND
							PERDENDT >= POP.REQDATE
						AND
							PERIODID  NOT IN (0,13)
				GROUP BY
					POP.INVINDX
					,SYY.YEAR1
	) AS REMBUDGT
GROUP BY
	ACTINDX
	,YEAR1