General Ledger Journals with Multi Dimensional Analysis Details – SQL Script
For analysis details in the General Ledger module, some companies consider the AA (Analytical Accounting) module while others consider the MDA (Multi Dimensional Analysis). A previous post provided an SQL script for General Ledger Journals with Analytical Accounting Details –SQL Script, now it is important to proceed and provide an SQL script for the MDA details.
Tables Included:
- DTA10100 | Transaction Analysis Groups
- DTA10200 | Transaction Analysis Codes
- GL00100 | Account Master
- GL00105 | Account Index Master
SELECT CASE A.DTASERIES
WHEN 2 THEN 'Financial'
WHEN 3 THEN 'Sales'
WHEN 4 THEN 'Purchasing'
WHEN 5 THEN 'Inventory'
WHEN 6 THEN 'Payroll – USA'
WHEN 7 THEN 'Project'
ELSE ''
END AS DTA_Series ,
JRNENTRY AS JournalEntry,
D.ACTNUMST AS AccountNumber,
C.ACTDESCR AS AccountDescription,
A.GROUPID AS DTA_GroupID,
CODEID AS DTA_CodeID,
GROUPAMT ,
CASE
WHEN CODEAMT > 0 THEN CODEAMT
ELSE 0
END AS Debit,
CASE
WHEN CODEAMT < 0 THEN CODEAMT
ELSE 0
END AS Credit,
A.TRXDATE AS TransactionDate,
A.DTAREF AS DTA_Reference,
DTA_GL_Reference ,
A.DOCNUMBR AS OriginalDocumentNumber ,
A.RMDTYPAL ,
CASE PSTGSTUS
WHEN 1 THEN 'Unposted'
WHEN 2 THEN 'Posted'
ELSE ''
END AS DTA_PostingStatus ,
B.DOCNUMBR ,
B.RMDTYPAL ,
POSTDESC AS PostingDescription,
DTAQNTY
FROM dbo.DTA10100 AS A
LEFT OUTER JOIN dbo.DTA10200 AS B ON A.ACTINDX = B.ACTINDX
AND A.DOCNUMBR = B.DOCNUMBR
AND A.DTAREF = B.DTAREF
AND A.DTASERIES = B.DTASERIES
AND A.GROUPID = B.GROUPID
AND A.SEQNUMBR = B.SEQNUMBR
LEFT OUTER JOIN dbo.GL00100 AS C ON A.ACTINDX = C.ACTINDX
LEFT OUTER JOIN dbo.GL00105 AS D ON C.ACTINDX = D.ACTINDX
WHEN 2 THEN 'Financial'
WHEN 3 THEN 'Sales'
WHEN 4 THEN 'Purchasing'
WHEN 5 THEN 'Inventory'
WHEN 6 THEN 'Payroll – USA'
WHEN 7 THEN 'Project'
ELSE ''
END AS DTA_Series ,
JRNENTRY AS JournalEntry,
D.ACTNUMST AS AccountNumber,
C.ACTDESCR AS AccountDescription,
A.GROUPID AS DTA_GroupID,
CODEID AS DTA_CodeID,
GROUPAMT ,
CASE
WHEN CODEAMT > 0 THEN CODEAMT
ELSE 0
END AS Debit,
CASE
WHEN CODEAMT < 0 THEN CODEAMT
ELSE 0
END AS Credit,
A.TRXDATE AS TransactionDate,
A.DTAREF AS DTA_Reference,
DTA_GL_Reference ,
A.DOCNUMBR AS OriginalDocumentNumber ,
A.RMDTYPAL ,
CASE PSTGSTUS
WHEN 1 THEN 'Unposted'
WHEN 2 THEN 'Posted'
ELSE ''
END AS DTA_PostingStatus ,
B.DOCNUMBR ,
B.RMDTYPAL ,
POSTDESC AS PostingDescription,
DTAQNTY
FROM dbo.DTA10100 AS A
LEFT OUTER JOIN dbo.DTA10200 AS B ON A.ACTINDX = B.ACTINDX
AND A.DOCNUMBR = B.DOCNUMBR
AND A.DTAREF = B.DTAREF
AND A.DTASERIES = B.DTASERIES
AND A.GROUPID = B.GROUPID
AND A.SEQNUMBR = B.SEQNUMBR
LEFT OUTER JOIN dbo.GL00100 AS C ON A.ACTINDX = C.ACTINDX
LEFT OUTER JOIN dbo.GL00105 AS D ON C.ACTINDX = D.ACTINDX
Best Regards,
Mahmoud M. AlSaadi
Mahmoud M. AlSaadi
*This post is locked for comments