I use the query below to get budget Amount from Tables, AAG00900. AAG00901, AAG00902, AAG00400, AAG00401, AAG00903, and AAG00904.
But I can't join this to get AA Transaction amount in GL.
If I Look at AAG00905, it is not populated with any budgets?
How is that done or what am I doing wrong?
SELECT rtrim(BudgetData.aaTrxDimCode) [Project],
SUM(BudgetData.Balance) [Project Budget],
rtrim(BudgetData.YEAR1) [Year]
FROM
(
SELECT 1 AS REPTYPE,
AAG00903.aaBudgetID,
AAG00903.aaBudget,
AAG00903.aaBudgetDescr,
ISNULL(AAG00903.YEAR1, '2020') [YEAR1],
AAG00900.aaBudgetTreeID,
AAG00900.aaBudgetTree,
AAG00900.aaBudgetTreeDescr,
AAG00901.aaTrxDimID,
AAG00400.aaTrxDim,
AAG00400.aaTrxDimDescr,
AAG00901.aaOrder,
AAG00902.aaTrxDimCodeID,
ISNULL(AAG00401.aaTrxDimCode,'No Project') [aaTrxDimCode],
AAG00401.aaTrxDimCodeDescr,
AAG00902.aaCodeSequence,
AAG00902.aaLvlCodeString,
ISNULL(AAG00904.aaFiscalPeriod, 0) AS aaFiscalPeriod,
ISNULL(AAG00904.aaActualPriliminary, 0) AS aaActualPriliminary,
ISNULL(AAG00904.Balance, 0) AS Balance
FROM AAG00900
INNER JOIN AAG00901 ON AAG00900.aaBudgetTreeID = AAG00901.aaBudgetTreeID
INNER JOIN AAG00902 ON AAG00901.aaBudgetTreeID = AAG00902.aaBudgetTreeID
INNER JOIN AAG00400 ON AAG00901.aaTrxDimID = AAG00400.aaTrxDimID
INNER JOIN AAG00401 ON AAG00902.aaTrxDimCodeID = AAG00401.aaTrxDimCodeID
AND AAG00400.aaTrxDimID = AAG00401.aaTrxDimID
INNER JOIN AAG00903 ON AAG00902.aaBudgetTreeID = AAG00903.aaBudgetTreeID
FULL OUTER JOIN AAG00904 ON AAG00903.aaBudgetID = AAG00904.aaBudgetID
AND AAG00902.aaCodeSequence = AAG00904.aaCodeSequence
) AS BudgetData
