Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Sunday, April 19, 2015

“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).

AA Unposted Data Set


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 ,


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 ,


          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,






                           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

No comments:

Post a Comment