As the Analytical Accounting module has become a more integral and tightly integrated part of Dynamics GP it is becoming more commonplace to see SQL scripting included in support cases to outline possible AA data issues behind unexpected reporting results.

This blog is a follow-up to Adam Gaber's article on using SQL scripting to determine if AA is enabled for a company, and hopefully will be the first of many offering additional insight into this subject:

  
The following is the main SQL table structure for AA data in Dynamics GP:
- The 'AAG10000' AA GL Work series holds AA information attached to GL work records held in a batch that were either entered directly in the GL or posted to the GL batch from other modules.  The records in the series are interconnected by the aaGLWorkHdrID field:

AAG10000 (aaGLWorkHdr) -> AAG10001 (aaGLWorkDist) -> AAG10002 (aaSubLedgerHdr) -> AAG10003 (aaGLWorkCode)

- The 'AAG20000' AA Subledger series holds AA information attached to financial records created in a submodule prior to posting to the GL and will remain in this table indefinitely to accommodate future processing actions that may create additional financial records on the GL side.  The records in the series are interconnected by the aaSubLedgerHdrID field:

AAG20000 (aaSubLedgerHdr) -> AAG20001 (aaSubLedgerDist) -> AAG20002 (aaSubLedgerAssign) -> AAG20003 (aaSubLedgerCode)

- The 'AAG30000' AA GL Header series holds AA information for posted open year transactions and is the table series most AA reporing is based upon.  The records in the series are interconnected by the aaGLHdrID field:
  
AAG30000 (aaGLHdr) -> AAG30001 (aaGLDist) -> AAG30002 (aaGLAssign) -> AAG30003 (aaGLCode)
  
- The 'AAG40000' AA GL History series holds AA information for historical year transactions and gets filled with information from the 'AAG30000' series during the GL Year End Close process and auto-filled with existing historical year entries during the AA activation process.  The records in the series are also interconnected by the aaGLHdrID field, which maintains the same unique value from the 'AAG30000' series after the YEC activity has pushed it to 'AAG40000':
    
AAG40000 (aaGLHistHdr) -> AAG40001 (aaGLHistDist) -> AAG40002 (aaGLHistAssign) -> AAG40003 (aaGLHistCode)

As far as the AA table content, the AAG10000/AAG20000/AAG30000/AAG40000 'Header' tables will hold the non-distribution detail for singular transactions, including the Journal Entry (JRNENTRY), Posted Date (GLPOSTDT) and Ledger_ID values that should match the GL side exactly for optimal AA reporting result.

The JRNENTRY field is also the main connector for GL/AA data comparison with the GL20000 (GL Open) with the SERIES/DOCNUMBR being the AAG20000 connection to the associated submodule.


For example, if you aren't seeing AA information for a particular JE on AA reporting you can use basic SQL scripting to verify if there is default record attached on the AA side (or not):


select * from AAG30000 where JRNENTRY='###'
-- Verifying there is an AA record assignment on the AA side of a GL open year transaction (replace '###' with the JE in question).

select * from GL20000 where JRNENTRY not in (select JRNENTRY from AAG30000)
-- Create a listing of current year JEs that do not have any AA records attached.


The AAG10001/AAG20001/AAG30001/AAG40001 'Distribution' tables will hold a carbon copy of the same account distributions you find in the GL and submodule entries.  It will contain the same associated JRNENTRY/DOCNUMBR distribution values, as well as further detail (such as Multicurrency, Intercompany ID and sequence number values) to provide additional link to the parent transaction.

The Account Index (ACTINDX) field of the tables serves as the main connector to the GL Account tables (GL00100/GL00105) to pull additional account display and description from.  The aaGLDistID field is also introduced in the 'Distribution' tables to connect to each individual distribution line in the 'AA Distribution' tables containing the same AA header ID value.

Note that the tables will contain ALL existing financial transaction data from the GL/Submodules, regardless if an included account is considered an "AA account" (attached to an AA Class ID) or not.  This design allows the user to view the full distribution entry in the Transactions | Financial | Analytical Accounting | Edit Analysis window and/or make AA assignments to if the non-AA accounts via the same window if it ever does become part of an AA Class at a future date. 

For example, if you wanted to check which default values on the AA side may not be in line with the GL side you can use the following SQL scripting:

SELECT a.aaGLHdrID,
       b.JRNENTRY,
       d.ACTNUMST, -- formatted account number string value
       a.DEBITAMT, -- default debit value for transaction on the AA side
       a.CRDTAMNT, -- default credit value for transaction on the AA side
       a.ORDBTAMT, -- default MC debit value for transaction on the AA side
       a.ORCRDAMT -- default MC credit value for transaction on the AA side
FROM   AAG30001 a
       JOIN AAG30000 b
         ON a.aaGLHdrID = b.aaGLHdrID
       JOIN GL20000 c
         ON b.JRNENTRY = c.JRNENTRY
            AND a.ACTINDX = c.ACTINDX
            AND a.SEQNUMBR = c.SEQNUMBR
       JOIN GL00105 d
         ON a.ACTINDX = d.ACTINDX
WHERE  a.DEBITAMT <> c.DEBITAMT -- default debit values do not match
        OR a.CRDTAMNT <> c.CRDTAMNT -- default credit values do not match
        OR a.ORDBTAMT <> c.ORDBTAMT -- default MC debit values do not match
        OR a.ORCRDAMT <> c.ORCRDAMT -- default MC credit values do not match
AND c.SOURCDOC not in ('BBF','P/L')

The AAG10002/AAG20002/AAG30002/AAG40002 'AA Distribution' tables contain the same distribution materials but reflect the AA-specific values present if the amounts were broken down into percentages during posting to reflect a split AA distribution/code assignment. 

To illustrate, if a two-line $100 transaction was posted without AA attached the AAG30001 would contain the full $100 value for the debit/credit fields:

aaGLHdrID - aaGLDistID - DEBITAMT - CRDTAMNT
1234 - 1 - 100.00000 - 0.00000
1234 - 2 - 0.00000 - 100.00000

If one of the accounts was an AA account with $40 assigned to AA Dimension 'A' and $60 assigned to AA Dimension 'B' the AAG30002 would contain one line for each split amount:

aaGLHdrID - aaGLDistID - aaGLAssignID - DEBITAMT - CRDTAMNT - aaAssignedPercent
1234 - 1 - 1 - 40.00000 - 0.00000 - 4000
1234 - 1 - 2 - 60.00000 - 0.00000 - 6000
1234 - 2 - 1 - 0.00000 - 100.00000 - 10000

Note that the aaGLAssignID field is added in the 'AA Distribution' tables in order to split the same aaGLDistID line to the varied amounts.  The other unique table field is aaAssignedPercent, which represents the percentage amount assigned to each line accordingly.

For example, the following SQL scripting will populate the percentage amounts assigned to a JE entry containing a split amount for an AA account:

SELECT b.JRNENTRY,
       d.ACTNUMST,
       a.DEBITAMT,
       a.CRDTAMNT,
       a.aaAssignedPercent * .01 AS '%'
FROM   AAG30002 a
       JOIN AAG30000 b
         ON a.aaGLHdrID = b.aaGLHdrID
       JOIN AAG30001 c
         ON a.aaGLHdrID = c.aaGLHdrID
            AND a.aaGLDistID = c.aaGLDistID
       JOIN GL00105 d
         ON c.ACTINDX = d.ACTINDX
WHERE  b.JRNENTRY = '###'


The final AAG10003/AAG20003/AAG30003/AAG40003 'AA Codes' tables will contain any AA Dimension/Code assignment placed on the singular line amounts established in the 'AA Distribution' series.  The aaTrxDimID and aaTrxCodeID fields contain the associated AA Dimension/AA Code values that are present in the AAG00400 (aaTrxDimMstr)/AAG00401 (aaTrxDimCodeSetp) tables accordingly.

For example, to see the AA code assignment of a split AA transaction you can use the following SQL scripting (also based on the JRNENTRY field):

SELECT a.aaGLHdrID,
       c.JRNENTRY,
       e.ACTNUMST,
       f.aaTrxDimDescr,
       g.aaTrxDimCodeDescr,
       b.aaAssignedPercent * .01 AS '%',
       b.DEBITAMT,
       b.CRDTAMNT
FROM   AAG30003 a
       JOIN AAG30002 b
         ON a.aaGLHdrID = b.aaGLHdrID
            AND a.aaGLDistID = b.aaGLDistID
            AND a.aaGLAssignID = b.aaGLAssignID
       JOIN AAG30000 c
         ON a.aaGLHdrID = c.aaGLHdrID
       JOIN AAG30001 d
         ON a.aaGLHdrID = d.aaGLHdrID
            AND a.aaGLDistID = d.aaGLDistID
       JOIN GL00105 e
         ON d.ACTINDX = e.ACTINDX
       JOIN AAG00400 f
         ON a.aaTrxDimID = f.aaTrxDimID
       JOIN AAG00401 g
         ON a.aaTrxCodeID = g.aaTrxDimCodeID
            AND f.aaTrxDimID = g.aaTrxDimID
WHERE  c.JRNENTRY = '###'


For far more robust SQL examples I would recommend reviewing the SQL scripting contained in the following KB articles:

The Year-end close procedures for Analytical Accounting in Microsoft Dynamics GP
https://support.microsoft.com/en-us/help/960356/the-year-end-close-procedures-for-analytical-accounting-in-microsoft-d

Financial Reports from Management Reporter do not match the General Ledger Trial Balance Reports in Microsoft Dynamics GP
https://support.microsoft.com/en-us/help/2910626/financial-reports-from-management-reporter-do-not-match-the-general-le


As for detailed SQL table content, Microsoft Dynamics GP Software Development Kit downloads available on PartnerSource/CustomerSource contain a comprehensive 'Analytical Accounting Table Design' document outlining all interconnected AA SQL tables in the program (including field descriptions and definitions) which serves an invaluable reference tool in designing AA reporting.


Regarding SQL shortcuts, the SQL Views contained in the company db used to AA SmartLists (AA SmartLists need to be enabled) do all the SQL work for you and would be the best 'one-stop' reference to review the existing AA materials attached to a given transaction.

The default SQL views containing the pre-formatted AA information include AA Transactions, AAOpenTransactions and AAHistoryTransactions.

For example, the following SQL line should produce the same result as the above SQL scripting without producing the associated headache:

select * from AAOpenTransactions where [Journal Entry]='###'

I hope this is useful information.  Your feedback and direction for further blog volumes on this same topic is appreciated!

JG