SQL View to extract Analytical Accounting Budget Data With Accounts in Dynamics GP

Have you ever wanted to extract your Analytical Accounting Budgets that were linked to accounts and dimensions from the database? I been in this situation today where the client wanted to export his budget in the following format:

Script below will do the task for you:

SELECT DISTINCT
                      dbo.AAG00903.YEAR1 AS [Budget Year], dbo.AAG00401.aaTrxDimCode AS [Dimension Code], dbo.AAG00401.aaTrxDimCodeDescr AS [Dimension Description],
                      dbo.GL00105.ACTNUMST AS [Account Number], dbo.GL00100.ACTDESCR AS [Account Description], dbo.aagMLFiscalView.aaFiscalPeriod AS Period,
                      dbo.AAG00905.Balance AS Budget
FROM         dbo.GL00100 INNER JOIN
                      dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                      dbo.AAG00903 INNER JOIN
                      dbo.aagMLFiscalView ON dbo.AAG00903.YEAR1 = dbo.aagMLFiscalView.aaFiscalYear INNER JOIN
                      dbo.AAG00902 ON dbo.AAG00903.aaBudgetTreeID = dbo.AAG00902.aaBudgetTreeID INNER JOIN
                      dbo.AAG00901 ON dbo.AAG00902.aaBudgetTreeID = dbo.AAG00901.aaBudgetTreeID INNER JOIN
                      dbo.AAG00401 ON dbo.AAG00901.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND dbo.AAG00902.aaTrxDimCodeID = dbo.AAG00401.aaTrxDimCodeID INNER JOIN
                      dbo.AAG00905 ON dbo.AAG00903.aaBudgetID = dbo.AAG00905.aaBudgetID AND dbo.AAG00902.aaCodeSequence = dbo.AAG00905.aaCodeSequence AND
                      dbo.aagMLFiscalView.aaFiscalPeriod = dbo.AAG00905.aaFiscalPeriod ON dbo.GL00100.ACTINDX = dbo.AAG00905.ACTINDX
WHERE     (dbo.AAG00902.aaCodeSequence <> 1) AND (dbo.AAG00905.Balance <> 0)

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
me@mohdaoud.com
www.mohdaoud.com