Weekly Dynamic:Accounts Over Budget SQL for Dynamics GP

I’m wrapping up my metrics series today with some SQL code that shows GL accounts that are over budget. In the past I’ve used this as part of a regular alert to see accounts over budget. It can also be tweaked to show accounts that are approaching budget. This script is already available for download.

--Displays accounts overbudget for the month as of today.


--Set your budget ID in place of 'Budget 2008'. Leave in the single quotes.



declare @BudgetID as varchar(30)

Set @BudgetID='Budget 2008'



SELECT left(GL00105.ACTNUMST,15) as Account,

left(GL00100.ACTDESCR,15) as Description,

left(GL10110.PERIODID,5) as Period,

right(convert (nchar,cast(GL10110.PERDBLNC as Money),1),12) as GL,

right(convert(nchar, cast(GL00201.BUDGETAMT as money),1),12) as Budget,

right (convert (nchar,cast(GL10110.PERDBLNC - GL00201.BUDGETAMT as money),1),12) AS Overbudget

FROM GL00105 INNER JOIN

GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX INNER JOIN

GL10110 ON GL00105.ACTINDX = GL10110.ACTINDX INNER JOIN

GL00201 ON GL10110.ACTINDX = GL00201.ACTINDX AND GL10110.PERIODID = GL00201.PERIODID AND

GL10110.PERDBLNC > GL00201.BUDGETAMT INNER JOIN

SY40100 ON GL10110.PERIODID = SY40100.PERIODID AND GL10110.YEAR1 = SY40100.YEAR1

WHERE (GL10110.YEAR1 = year(getdate())) AND (GL00201.BUDGETID = @BudgetID)

AND (GL10110.PERDBLNC - GL00201.BUDGETAMT > 100) AND ((GETDATE()

>= SY40100.PERIODDT) AND (GETDATE() <= SY40100.PERDENDT))and sy40100.ODESCTN='General Entry'





Don’t forget to subscribe to the DynamicAccounting.net newsletter for monthly news and tips on Dynamics GP.