Payables HATB With Aging By Payment Terms (using GL Posting Date)

| 07/25/2011 | 1 Comment

As a follow up to my script for generating the Payables HATB With Aging By Due Date (using GL Posting Date), I have decided to post a script for the same with aging buckets based on the document date (with the logic of payment terms). This will provide the aging buckets for each transaction based on the payables aging buckets setup.

/******************************************************************
Created July 25, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Payables HATB for aging the document
date (using the payment terms on the document) and
picking transactions using the GL Posting Date.
 
Tables used:
 - PM20000 - PM Transaction OPEN File
 - PM30200 - PM Paid Transaction History File
 - PM10200 - PM Apply To WORK OPEN File
 - PM30300 - PM Apply To History File
 - PM40101 - PM Period Setup File
 - PM40102 - Payables Document Types
 - SY03300 - Payment Terms Master
 ******************************************************************/
 
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
 
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12'
 
SELECT  W.VENDORID ,
        W.VCHRNMBR ,
        W1.DOCTYNAM AS DOCTYPE ,
        W.DOCNUMBR ,
        W.DOCDATE ,
        W.TRXSORCE ,
        W.PSTGDATE ,
        W.DUEDATE ,
        W.AGINGBUCKET ,
        W.DOCUMENTAMT ,
        W.CURTRXAMT
FROM    ( SELECT    X.VENDORID ,
                    X.VCHRNMBR ,
                    X.DOCTYPE ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            DSCRIPTN
                                                     FROM   dbo.PM40101
                                                     ORDER BY ENDGPDYS DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                DSCRIPTN
                                       FROM     dbo.PM40101 AG
                                       WHERE    X.DAYSDUE <= AG.ENDGPDYS
                                       ORDER BY ENDGPDYS
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.DOCTYPE <= 3
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT + X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT + X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.VCHRNMBR ,
                                Z.VENDORID ,
                                Z.DOCTYPE ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.DOCAMNT AS DOCUMENTAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     WHEN DOCTYPE > 3
                                          AND DOCTYPE <= 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                         FROM   ( SELECT    VENDORID ,
                                                                                            GLPOSTDT ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            ApplyToGLPostDate
                                                                                  FROM      dbo.PM10200
                                                                                  WHERE     POSTED = 1
                                                                                  UNION
                                                                                  SELECT    VENDORID ,
                                                                                            GLPOSTDT ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            ApplyToGLPostDate
                                                                                  FROM      dbo.PM30300
                                                                                ) Y
                                                                         WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                                AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                                AND Y.VENDORID = Z.VENDORID
                                                                                AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                AND Y.DOCTYPE = Z.DOCTYPE
                                                                       ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN DOCTYPE > 3 THEN ISNULL(( SELECT  SUM(Y.RLGANLOS)
                                                                    FROM    ( SELECT    VENDORID ,
                                                                                        GLPOSTDT ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        GLPOSTDT ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                    WHERE   Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                            AND Y.DOCTYPE = Z.DOCTYPE
                                                                  ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            @EMPTYDATE AS VOIDPDATE
                                  FROM      dbo.PM20000 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                  UNION ALL
                                  SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            VOIDPDATE
                                  FROM      dbo.PM30200 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                ) Z
                      WHERE     Z.PSTGDATE <= @ASOFDATE
                                AND Z.VOIDED = 0
                    ) X
          UNION ALL
          SELECT    X.VENDORID ,
                    X.VCHRNMBR ,
                    X.DOCTYPE ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            DSCRIPTN
                                                     FROM   dbo.PM40101
                                                     ORDER BY ENDGPDYS DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                DSCRIPTN
                                       FROM     dbo.PM40101 AG
                                       WHERE    X.DAYSDUE <= AG.ENDGPDYS
                                       ORDER BY ENDGPDYS
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.DOCTYPE <= 3
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT + X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT + X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.VCHRNMBR ,
                                Z.VENDORID ,
                                Z.DOCTYPE ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.DOCAMNT AS DOCUMENTAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     WHEN DOCTYPE > 3
                                          AND DOCTYPE <= 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                         FROM   ( SELECT    VENDORID ,
                                                                                            GLPOSTDT ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            ApplyToGLPostDate
                                                                                  FROM      dbo.PM10200
                                                                                  WHERE     POSTED = 1
                                                                                  UNION
                                                                                  SELECT    VENDORID ,
                                                                                            GLPOSTDT ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            ApplyToGLPostDate
                                                                                  FROM      dbo.PM30300
                                                                                ) Y
                                                                         WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                                AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                                AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                                AND Y.VENDORID = Z.VENDORID
                                                                                AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                                AND Y.DOCTYPE = Z.DOCTYPE
                                                                       ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN DOCTYPE <= 3 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.APTVCHNM = Z.VCHRNMBR
                                                                            AND Y.APTODCTY = Z.DOCTYPE
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN DOCTYPE > 3 THEN ISNULL(( SELECT  SUM(Y.RLGANLOS)
                                                                    FROM    ( SELECT    VENDORID ,
                                                                                        GLPOSTDT ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        GLPOSTDT ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                    WHERE   Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                            AND Y.VENDORID = Z.VENDORID
                                                                            AND Y.VCHRNMBR = Z.VCHRNMBR
                                                                            AND Y.DOCTYPE = Z.DOCTYPE
                                                                  ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            @EMPTYDATE AS VOIDPDATE
                                  FROM      dbo.PM20000 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                  UNION
                                  SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            DATEADD(dd, ISNULL(C.DUEDTDS, 0), DOCDATE) AS DUEDATE ,
                                            VOIDPDATE
                                  FROM      dbo.PM30200 A
                                            LEFT OUTER JOIN dbo.SY03300 C ON A.PYMTRMID = C.PYMTRMID
                                ) Z
                      WHERE     Z.PSTGDATE <= @ASOFDATE
                                AND Z.VOIDED = 1
                                AND Z.VOIDPDATE > @ASOFDATE
                    ) X
        ) W
        INNER JOIN dbo.PM40102 W1 ON W.DOCTYPE = W1.DOCTYPE
WHERE   W.CURTRXAMT <> 0

Hope this helps the community…

Update: 11/28/2011 – Based on feedback from a community member Louella Thomas, fixed an issue where applied information for an unposted payment was considered in the result.

Until next post!

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Tags: , , , , ,

Category: Accounts Payables, Dynamics, Great Plains, SQL Server, SQL Server 2005

About the Author ()