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.
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Weekly Dynamic:Accounts Over Budget SQL for Dynamics GP
Posted in Dynamics GP by DynamicAccounting.net 1037 days ago (http://feedproxy.google.com)
My goal is to share my love and knowledge of Microsoft Dynamics software. If you want fluff-free stories about software with legs, you've come to the right place.
DynamicsTips.com is the place to get the latest news and technical tips on Microsoft Dynamics products, whether you're a developer or a user.
Stay In Touch,
Follow Me!
Comments