SQL Script to Reconcile GL vs. AA Balances

Hi all

Having used Analytical Accounting for quite some time now, I have come across various issues in Analytical Accounting module, which has caused serious data discrepancy between the General Ledger tables and the Analytical Accounting tables.

So I had decided to write up a simple SQL script to reconcile the balances for every account currency combination between the General Ledger tables and the Analytical Accounting tables.

 
DECLARE @ASOFDATE AS DATETIME 
 
SET @ASOFDATE = '2010-12-31'
 
SELECT  SUMM.COMPANY ,
	SUMM.JOURNALENTRY ,
        SUMM.GPACCOUNTNO ,
        SUMM.CURNCYID ,
        SUMM.GLORIGAMT ,
        SUMM.GLFUNCAMT ,
        SUMM.AA30001ORIGAMT ,
        SUMM.AA30001FUNCAMT ,
        SUMM.AA30002ORIGAMT ,
        SUMM.AA30002FUNCAMT ,
        SUMM.GLAAORIGMATCH ,
        SUMM.GLAAFUNCMATCH
FROM    ( SELECT    'TWO' AS COMPANY ,
                    X.JRNENTRY AS JOURNALENTRY ,
                    RTRIM(X.ACTNUMST) AS GPACCOUNTNO ,
                    X.CURNCYID ,
                    X.GLORIGAMT ,
                    X.GLFUNCAMT ,
                    X.AA30001ORIGAMT ,
                    X.AA30001FUNCAMT ,
                    X.AA30002ORIGAMT ,
                    X.AA30002FUNCAMT ,
                    CASE WHEN ( ( X.GLORIGAMT = X.AA30002ORIGAMT )
                                AND ( X.GLORIGAMT = X.AA30001ORIGAMT )
                                AND ( X.AA30001ORIGAMT = X.AA30002ORIGAMT )
                              ) THEN 'TRUE'
                         ELSE 'FALSE'
                    END AS GLAAORIGMATCH ,
                    CASE WHEN ( ( X.GLFUNCAMT = X.AA30002FUNCAMT )
                                AND ( X.GLFUNCAMT = X.AA30001FUNCAMT )
                                AND ( X.AA30001FUNCAMT = X.AA30002FUNCAMT )
                              ) THEN 'TRUE'
                         ELSE 'FALSE'
                    END AS GLAAFUNCMATCH ,
                    ( X.GLORIGAMT - X.AA30002ORIGAMT ) ORIGDIFFERENCE ,
                    ( X.GLFUNCAMT - X.AA30002FUNCAMT ) FUNCDIFFERENCE
          FROM      ( SELECT    A.JRNENTRY ,
                                B.ACTNUMST ,
                                B.ACTINDX ,
                                A.CURNCYID ,
                                SUM(A.ORDBTAMT - A.ORCRDAMT) AS GLORIGAMT ,
                                SUM(A.DEBITAMT - A.CRDTAMNT) AS GLFUNCAMT ,
                                ( SELECT    ISNULL(SUM(C.ORDBTAMT - C.ORCRDAMT), 0)
                                  FROM      TWO..AAG30001 C
                                            INNER JOIN TWO..AAG30000 D ON C.aaGLHdrID = D.aaGLHdrID
                                  WHERE     D.GLPOSTDT <= @ASOFDATE
                                            AND C.ACTINDX = B.ACTINDX
                                            AND C.CURNCYID = A.CURNCYID
                                            AND D.JRNENTRY = A.JRNENTRY
                                ) AS AA30001ORIGAMT ,
                                ( SELECT    ISNULL(SUM(C.DEBITAMT - C.CRDTAMNT), 0)
                                  FROM      TWO..AAG30001 C
                                            INNER JOIN TWO..AAG30000 D ON C.aaGLHdrID = D.aaGLHdrID
                                  WHERE     D.GLPOSTDT <= @ASOFDATE
                                            AND C.ACTINDX = B.ACTINDX
                                            AND C.CURNCYID = A.CURNCYID
                                            AND D.JRNENTRY = A.JRNENTRY
                                ) AS AA30001FUNCAMT ,
                                ( SELECT    ISNULL(SUM(E.ORDBTAMT - E.ORCRDAMT), 0)
                                  FROM      TWO..AAG30002 E
                                            INNER JOIN TWO..AAG30001 C ON E.aaGLHdrID = C.aaGLHdrID
                                                              AND E.aaGLDistID = C.aaGLDistID
                                            INNER JOIN TWO..AAG30000 D ON C.aaGLHdrID = D.aaGLHdrID
                                            INNER JOIN TWO..AAG30003 F ON E.aaGLHdrID = F.aaGLHdrID
                                                              AND E.aaGLDistID = F.aaGLDistID
                                                              AND E.aaGLAssignID = F.aaGLAssignID
                                  WHERE     D.GLPOSTDT <= @ASOFDATE
                                            AND C.ACTINDX = B.ACTINDX
                                            AND C.CURNCYID = A.CURNCYID
                                            AND D.JRNENTRY = A.JRNENTRY
                                            AND F.aaTrxCodeID <> 0
                                ) AS AA30002ORIGAMT ,
                                ( SELECT    ISNULL(SUM(E.DEBITAMT - E.CRDTAMNT), 0)
                                  FROM      TWO..AAG30002 E
                                            INNER JOIN TWO..AAG30001 C ON E.aaGLHdrID = C.aaGLHdrID
                                                              AND E.aaGLDistID = C.aaGLDistID
                                            INNER JOIN TWO..AAG30000 D ON C.aaGLHdrID = D.aaGLHdrID
                                            INNER JOIN TWO..AAG30003 F ON E.aaGLHdrID = F.aaGLHdrID
                                                              AND E.aaGLDistID = F.aaGLDistID
                                                              AND E.aaGLAssignID = F.aaGLAssignID
                                  WHERE     D.GLPOSTDT <= @ASOFDATE
                                            AND C.ACTINDX = B.ACTINDX
                                            AND C.CURNCYID = A.CURNCYID
                                            AND D.JRNENTRY = A.JRNENTRY
                                            AND F.aaTrxCodeID <> 0
                                ) AS AA30002FUNCAMT
                      FROM      TWO..GL20000 A
                                INNER JOIN TWO..GL00105 B ON A.ACTINDX = B.ACTINDX
                      WHERE     A.TRXDATE <= @ASOFDATE
                      GROUP BY  A.JRNENTRY ,
                                B.ACTNUMST ,
                                B.ACTINDX ,
                                A.CURNCYID
                    ) X
        ) SUMM
WHERE (SUMM.GLAAORIGMATCH = 'FALSE' OR SUMM.GLAAFUNCMATCH = 'FALSE')

This script is free for use and you can modify this script to suit your business requirements.

Note: I have updated the above script to validate the balances in all the 3 tables (GL20000, AAG30001 & AAG30002) to be in sync with one another. This is a critical enhancement since the AAG30002 table is the important table from where the data is picked up for reporting in AA.

Hope this script is helpful for folks.

Until next post…

VN:F [1.9.13_1145]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

May 16, 2010 · veeyeskay · 6 Comments
Tags: , , ,  Â· Posted in: Analytical Accounting, Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 1,773

  • Pingback: SQL Script to Reconcile GL vs. AA Balances for Accounts Payable - Interesting Findings & Knowledge Sharing

  • Santosh

    Hi Thanks for this. Do you have Reconcile GL Vs AR/AP code? Appriciate if you share this sql code. Regards, Santosh

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)
    • veeyeskay

      Santosh – I am working on it. Will post it shortly, Siva

      VN:F [1.9.13_1145]
      Rating: 0.0/5 (0 votes cast)
      VN:F [1.9.13_1145]
      Rating: 0 (from 0 votes)
  • veeyeskay

    Thanks for the info Denis. Appreciated. I have posted an updated script to validate both AAG30001 and AAG30002 tables with the GL20000 table. Hope this helps.

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)
  • Denis

    Hi,

    I Did a lot a work around AA too,

    one other probleme around AA is Gp let you post transaction with no dimension in some case, in other case a lot a related to COG account. the dimension just gone after posting

    here is a script I build to locate those Line in AAG30003 are mark to require a Dimension and the dimension is not here anymore.

    let me know!

    /*

    Created By: Denis St-Germain / Dstgermain@forstum.com

    This query return a list of all Line in the AAG30000,1,2,3 table should have a Trxdimension assign to
    In the accounting class setup, the trx dimension is set to required

    */

    select
    a.RCTRXSEQ, a.glpostdt As DateComptable, b.aagldistid as LigneAA, c.aaGLAssignID as SousLingeAA, a.aaglhdrid NumeroAA, a.jrnentry as [Entree de journall], rtrim(f.actnumst) as CompteGL, b.crdtamnt as Credit,
    b.debitamt as Debit, gl.sourcdoc as SourceDocument, gl.orgntsrc as SourceOrigine, gl.orctrnum as NumeroTRX

    from aag30000 as a

    left join aag30001 as b
    on a.aaGLHdrID = b.aaGLHdrID

    left join AAG30002 as c
    on b.aaGLHdrID = c.aaGLHdrID
    and b.aaGLDistID = c.aaGLDistID

    left join AAG30003 as d
    on c.aaGLHdrID = d.aaGLHdrID
    and c.aaGLDistID = d.aaGLDistID
    and c.aaGLAssignID = d.aaGLAssignID

    left join (
    –retunr a list of all actindx link to a require AA accounting class
    select distinct(a.actindx) from dbo.aag00200 as a
    left join aag00202 as b
    on a.aaAcctClassID = b.aaAcctclassid
    where b.aadataentry = 2
    ) as subquery
    on b.actindx = subquery.actindx

    left join gl00105 as f
    on b.actindx = f.actindx

    left join gl20000 as gl
    on a.jrnentry = gl.jrnentry
    and b.seqnumbr = gl.seqnumbr
    and a.RCTRXSEQ = gl.RCTRXSEQ

    where
    Subquery.actindx is not null
    and d.aatrxdimid is null
    and a.glpostdt >= '2009-01-01'
    and gl.voided = 0

    VA:F [1.9.13_1145]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.13_1145]
    Rating: 0 (from 0 votes)

  • Pingback: SQL Script to Reconcile GL vs. AA Balances - DynamicAccounting.net