“Unposted” General Ledger Journals with Analytical Accounting Details –SQL Script

In a previous post, a report for general ledger journals with Analytical Accounting details has been provided. Now it is important to have a brief and centralized report for the “unposted” journals in the General Ledger with Analytical details, originating directly from General Ledger or sub ledgers (considering the posting setup, post to or post through).

/*--------------------------------------------------------------------------

Creation Date: 19, April, 2015

Created by: Mahmoud M. AlSaadi

The main purpose of the script is to retrieve all "unposted" journals with Analytical Accounting details.

 

The script has been tested on a very limited sample data.

Revision History:

Revision No.            RevisionDate    Description

1                       19/04/2015      Original Version

------------------------------------------------------------------------- */

 

Tables Included:

• AAG10000 | aaGLWorkHdr
• AAG10001 | aaGLWorkDist
• AAG10002 | aaGLWorkAssign
• AAG10003 | aaGLWorkCode
• AAG00400 | aaTrxDimMstr
• AAG00401 | aaTrxDimCodeSetp
• GL00100  | Account Master
• GL00105  | Account Index Master
• GL10000  | Transaction Work
• GL10001  | Transaction Amounts Work
• SY00900  | Source Document Master

 

SELECT  AA_Status ,

        AccountNumber ,

        AccountDescription ,

        AA.JRNENTRY ,

              ISNULL(SOURCDOC,'') AS SourceDocument,

              ISNULL(SDOCDSCR,'') AS SourceDescription,

        ISNULL(DSCRIPTN,'') AS Journal_Reference ,

        ISNULL(REFRENCE,'') AS GL_DistributionReference ,

        aaTrxDim ,

        aaTrxDimCode ,

        AA_DistribitionReference ,

        GLPOSTDT ,

        DEBITAMT ,

        CRDTAMNT ,

        ORDBTAMT ,

        AA.ACTINDX ,

        ORCRDAMT ,

        AAassignedAmount_Debit ,

        AAassignedAmount_Credit ,

        AAassignedAmount_OriginalDebit ,

        AAassignedAmount_OriginalCredit ,

        CURNCYID ,

        XCHGRATE ,

        aaAssignedPercent ,

        aaAliasID

FROM    ( SELECT    'Unposted GL' AA_Status ,

                    ISNULL(ACTNUMST, '') AS AccountNumber ,

                    ISNULL(ACTDESCR, '') AS AccountDescription ,

                    A.JRNENTRY ,

                    ISNULL(aaTrxDim, '') AS aaTrxDim ,

                    ISNULL(aaTrxDimCode, '') AS aaTrxDimCode ,

                    ISNULL(DistRef,'') AA_DistribitionReference ,

                    GLPOSTDT ,

                    B.DEBITAMT ,

                    B.CRDTAMNT ,

                    B.ORDBTAMT ,

                    B.ACTINDX ,

                    B.ORCRDAMT ,

                    C.DEBITAMT AS AAassignedAmount_Debit ,

                    C.CRDTAMNT AS AAassignedAmount_Credit ,

                    C.ORDBTAMT AS AAassignedAmount_OriginalDebit ,

                    C.ORCRDAMT AS AAassignedAmount_OriginalCredit ,

                    CURNCYID ,

                    XCHGRATE ,

                    aaAssignedPercent ,

                    aaAliasID

          FROM      [AAG10000] AS A

                LEFT OUTER JOIN [AAG10001] AS B

                                  ON A.aaGLWorkHdrID= B.aaGLWorkHdrID

                LEFT OUTER JOIN AAG10002 AS C

                                  ON B.aaGLWorkHdrID = C.aaGLWorkHdrID AND

                                     B.aaGLWorkDistID= C.aaGLWorkDistID

                LEFT OUTER JOIN dbo.AAG10003 AS D

                                  ON C.aaGLWorkHdrID = D.aaGLWorkHdrID AND

                       C.aaGLWorkDistID = D.aaGLWorkDistID AND

                       C.aaGLWorkAssignID= D.aaGLWorkAssignID

                LEFT OUTER JOIN dbo.AAG00400 AS E

                                  ON D.aaTrxDimID = E.aaTrxDimID

                LEFT OUTER JOIN dbo.AAG00401 AS F

                                  ON D.aaTrxCodeID = F.aaTrxDimCodeID

                LEFT OUTER JOIN dbo.GL00100 AS X

                                  ON B.ACTINDX = X.ACTINDX

                LEFT OUTER JOIN dbo.GL00105 AS Y

                           ON X.ACTINDX = Y.ACTINDX

                           )  AS AA

                           LEFT OUTER JOIN

                           (

                           SELECT X.JRNENTRY,

                                     REFRENCE,

                                     Y.DSCRIPTN,

                                     Y.ACTINDX,

                                     X.SOURCDOC,

                                     Z.SDOCDSCR

                           FROM  dbo.GL10000 AS X

                           INNER JOIN dbo.GL10001 AS Y

                           ON X.JRNENTRY = Y.JRNENTRY

                           INNER JOIN SY00900 AS Z

                           ON X.SOURCDOC = Z.SOURCDOC

                           ) AS GL

                           ON AA.ACTINDX = GL.ACTINDX AND

                              AA.JRNENTRY = GL.JRNENTRY

 

You can download the script directly from here >> Download Link

 

Best Regards,
Mahmoud M. AlSaadi