32,738
Like
Share
Report
In this post, SQL script for the GL Journal entries with Analytical Accounting details is provided along with all the associated details, as shown below;
Tables Included:
· AAG30000 | Analytical Accounting GL Header
· AAG30001 | Analytical Accounting GL Distribution
· AAG30002 | Analytical Accounting GL Assignment
· AAG30003 | Analytical Accounting GL Code
· AAG00401 | Analytical Accounting Transaction Dimension
· GL00100 | Account Master
· GL00105 | Account Index Master
SELECT YEAR1 ,
JRNENTRY ,
B.ACTINDX ,
H.ACTNUMST ,
G.ACTDESCR ,
A.aaTRXType ,
aaGLTRXSource ,
aaTRXSource ,
GLPOSTDT ,
B.DEBITAMT AS TotalDebit ,
B.CRDTAMNT AS TotalCredit ,
C.DEBITAMT AS aaDebit ,
C.CRDTAMNT AS aaCredit ,
aaTrxDim ,
aaTrxDimDescr ,
aaTrxDimDescr2 ,
aaTrxDimCode ,
aaTrxDimCodeDescr ,
aaTrxDimCodeDescr2
FROM [AAG30000] AS A
LEFT OUTER JOIN [AAG30001] AS B ON A.[aaGLHdrID] = B.[aaGLHdrID]
LEFT OUTER JOIN [AAG30002] AS C ON B.[aaGLHdrID] = C.[aaGLHdrID]
AND B.[aaGLDistID] = C.[aaGLDistID]
INNER JOIN [AAG30003] AS D ON C.[aaGLAssignID] = D.[aaGLAssignID]
AND C.[aaGLDistID] = D.[aaGLDistID]
AND C.[aaGLHdrID] = D.[aaGLHdrID]
INNER JOIN [AAG00401] AS E ON D.[aaTrxDimID] = E.[aaTrxDimID]
AND D.[aaTrxCodeID] = E.[aaTrxDimCodeID]
INNER JOIN [AAG00400] AS F ON E.[aaTrxDimID] = F.[aaTrxDimID]
LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX
LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX
Best Regards,
Mahmoud M. AlSaadi
*This post is locked for comments