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
