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.