web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics GP Essentials / Analytical Accounting GL Ac...

Analytical Accounting GL Accounts and Dimensions Assignment Details –SQL View

Views (1959)
Mahmoud Saadi Profile Picture Mahmoud Saadi 32,738
 
 
In this post, a view for the GL accounts assigned to dimensions are provided along with all the associated details, as shown below;
 
AA_View

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


This was originally posted here.

Comments

*This post is locked for comments