Analytical Accounting GL Accounts and Dimensions Assignment Details –SQL View

 

 

In this post, a view for the GL accounts assigned to dimensions are provided along with all the associated details, as shown below;

 

Tables Included;

• AAG00201 | AA Account Class Master
• AAG00202 | AA Account Class Dimension
• AAG00200L|AA Account Class Link
• AAG00400 | AA Transaction Dimension Master
• AAG00401 | AA Transaction Dimension Code Setup
• GL00105  | Account Index Master
• GL00100  | Account Master

 

SELECT  aaAcctClassID ,

        aaAccountClass ,

        aaTrxDimID ,

        ACTINDX ,

        ACTNUMST ,

        Master_TB ,

        aaTrxDim ,

        aaTrxDimCode

FROM    ( SELECT    A.aaAcctClassID ,

                    A.aaAccountClass ,

                    B.aaTrxDimID ,

                    C.ACTINDX ,

                    D.ACTNUMST ,

                    CASE F.TPCLBLNC

                      WHEN 0 THEN 'Debit'

                      WHEN 1 THEN 'Credit'

                      ELSE ' '

                    END AS Master_TB ,

                    E.aaTrxDim ,

                    E.aaTrxDimCode

          FROM      AAG00201 AS A

                    INNER JOIN AAG00202 AS B ON A.aaAcctClassID = B.aaAcctClassID

                    LEFT OUTER JOIN AAG00200L AS C ON B.aaAcctClassID = C.aaAcctClassID

                    LEFT OUTER JOIN GL00105 AS D ON C.ACTINDX = D.ACTINDX

                    LEFT OUTER JOIN GL00100 AS F ON D.ACTINDX = F.ACTINDX

                    LEFT OUTER JOIN ( SELECT    A.aaTrxDimID ,

                                                A.aaTrxDim ,

                                                B.aaTrxDimCode

                                      FROM      AAG00400 AS A

                                                INNER JOIN AAG00401 AS B ON A.aaTrxDimID = B.aaTrxDimID

                                    ) AS E ON B.aaTrxDimID = E.aaTrxDimID

        ) AS AA

 

 

Best Regards,
Mahmoud M. AlSaadi