Purchase Requisitions with Analytical Accounting Details - Report

With the introduction of GP 2013 R2, a new feature came along to integrate Analytical Accounting with Purchase requisitions so that the requested can enter associated dimension code values. In this post, an SQL script is provided for the requisitions with Analytical Accounting details. 

Report Sample

The Script can be downloaded from this link >>> Download Link 

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

Creation Date: The 28th of December, 2015

Created by: Mahmoud M. AlSaadi

This script provides a detailed list of all requisitions with associated

analytical accounting details (dimension and dimension codes)

Revision History:

Revision No.            Revision Date    Description

1                       28/12/2015      Original Version

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

DECLARE @Series INT,

    @DocumentType INT

SET @Series = 12

SET @DocumentType = 21

SELECT  aaSubLedgerHdrID,

        DOCNUMBR,

        Master_ID,

        ACTINDX,

        AccountNumber,

        AccountDescription,

        Debit,

        Credit,

        aaSubLedgerDistID,

        DimensionID,

        ISNULL(aaTrxDim, '') AS Dimension,

        DimensionCodeID,

        ISNULL(aaTrxDimCode, '') DimensionCode,

        aaDebit,

        aaCredit

FROM    ( SELECT   

                     X.aaSubLedgerHdrID ,

                     x.SERIES ,

                     X.DOCTYPE ,

                     X.DOCNUMBR ,

                     ISNULL(X.Master_ID, '') AS Master_ID ,

                     ISNULL(Y.ACTINDX, '') AS ACTINDX ,

                     ISNULL(GLM.ACTNUMST, '') AS AccountNumber ,

                     ISNULL(GL.ACTDESCR, '') AS AccountDescription ,

                     ISNULL(Y.DEBITAMT, 0) AS Debit ,

                     ISNULL(Y.CRDTAMNT, 0) AS Credit ,

                     ISNULL(W.aaTrxDimID, '') AS DimensionID ,

                     ISNULL(W.aaTrxCodeID, '') DimensionCodeID ,

                     ISNULL(Z.DEBITAMT, 0) AS aaDebit ,

                     ISNULL(Z.CRDTAMNT, 0) AS aaCredit,

                     w.aaSubLedgerDistID

       FROM      AAG20000 AS X

                     LEFT OUTER JOIN dbo.AAG20001 AS Y

                           ON X.aaSubLedgerHdrID = Y.aaSubLedgerHdrID

                     LEFT OUTER JOIN AAG20002 AS Z

                           ON Y.aaSubLedgerHdrID = Z.aaSubLedgerHdrID

                           AND Y.aaSubLedgerDistID = Z.aaSubLedgerDistID

                     LEFT OUTER JOIN dbo.AAG20003 AS W

                           ON W.aaSubLedgerHdrID = X.aaSubLedgerHdrID

                           AND W.aaSubLedgerDistID = Z.aaSubLedgerDistID

                           AND W.aaSubLedgerAssignID = Z.aaSubLedgerAssignID

                     LEFT OUTER JOIN GL00100 AS GL

                           ON Y.ACTINDX = GL.ACTINDX

                     LEFT OUTER JOIN GL00105 AS GLM

                           ON Y.ACTINDX = GLM.ACTINDX

        ) Trx

        LEFT OUTER JOIN

              (

              SELECT    A.aaTrxDimID ,

                A.aaTrxDim ,

                B.aaTrxDimCodeID ,

                B.aaTrxDimCode

        FROM      dbo.AAG00400 AS A

                LEFT OUTER JOIN dbo.AAG00401 AS B

                ON A.aaTrxDimID = B.aaTrxDimID

          ) AS Dim

                     ON TRX.DimensionID = Dim.aaTrxDimID

            AND Trx.DimensionCodeID = Dim.aaTrxDimCodeID

WHERE   ACTINDX IS NOT NULL

        AND SERIES = @Series

        AND DOCTYPE = @DocumentType

        AND aaTrxDimCode IS NOT NULL

        AND aaTrxDim IS NOT NULL

Best Regards, 
Mahmoud M. AlSaadi