Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Monday, December 28, 2015

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