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 

 

Best Regards,
Mahmoud M. AlSaadi