Payables HATB With Aging By Due Date (using Document Date)

| 07/25/2011 | 3 Comments

As a follow up to my script for generating the Payables HATB by Document Date, I have decided to post a script for the same with aging buckets based on the due date. This will provide the aging buckets for each transaction based on the receivables aging buckets setup.

/******************************************************************
Created July 25, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Payables HATB for aging by due date and
picking transactions using the Document 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
 ******************************************************************/
 
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 ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @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 ,
                                                                                            DATE1 ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            APTODCDT
                                                                                  FROM      dbo.PM10200
                                                                                  WHERE     POSTED = 1
                                                                                  UNION
                                                                                  SELECT    VENDORID ,
                                                                                            DATE1 ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            APTODCDT
                                                                                  FROM      dbo.PM30300
                                                                                ) Y
                                                                         WHERE  Y.DATE1 <= @ASOFDATE
                                                                                AND Y.APTODCDT <= @ASOFDATE
                                                                                AND Y.APTODCDT <> @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 ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @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 ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @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 ,
                                                                                        DATE1 ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DATE1 ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                    WHERE   Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APTODCDT <= @ASOFDATE
                                                                            AND Y.APTODCDT <> @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.DUEDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            CASE WHEN DUEDATE = '1900-01-01' THEN @ASOFDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            @EMPTYDATE AS VOIDPDATE
                                  FROM      dbo.PM20000
                                  UNION ALL
                                  SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            CASE WHEN DUEDATE = '1900-01-01' THEN @ASOFDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDPDATE
                                  FROM      dbo.PM30200
                                ) Z
                      WHERE     Z.DOCDATE <= @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 ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APPLDAMT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @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 ,
                                                                                            DATE1 ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            APTODCDT
                                                                                  FROM      dbo.PM10200
                                                                                  WHERE     POSTED = 1
                                                                                  UNION
                                                                                  SELECT    VENDORID ,
                                                                                            DATE1 ,
                                                                                            APPLDAMT ,
                                                                                            VCHRNMBR ,
                                                                                            DOCTYPE ,
                                                                                            APTVCHNM ,
                                                                                            APTODCTY ,
                                                                                            APTODCDT
                                                                                  FROM      dbo.PM30300
                                                                                ) Y
                                                                         WHERE  Y.DATE1 <= @ASOFDATE
                                                                                AND Y.APTODCDT <= @ASOFDATE
                                                                                AND Y.APTODCDT <> @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 ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @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 ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DOCDATE ,
                                                                                        DATE1 ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.DOCDATE <= @ASOFDATE
                                                                            AND Y.DOCDATE <> @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 ,
                                                                                        DATE1 ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM10200
                                                                              WHERE     POSTED = 1
                                                                              UNION
                                                                              SELECT    VENDORID ,
                                                                                        DATE1 ,
                                                                                        VCHRNMBR ,
                                                                                        DOCTYPE ,
                                                                                        APTVCHNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.PM30300
                                                                            ) Y
                                                                    WHERE   Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APTODCDT <= @ASOFDATE
                                                                            AND Y.APTODCDT <> @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.DUEDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            CASE WHEN DUEDATE = '1900-01-01' THEN @ASOFDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            @EMPTYDATE AS VOIDPDATE
                                  FROM      dbo.PM20000
                                  UNION
                                  SELECT    VCHRNMBR ,
                                            VENDORID ,
                                            DOCTYPE ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            DOCAMNT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDED ,
                                            PSTGDATE ,
                                            CASE WHEN DUEDATE = '1900-01-01' THEN @ASOFDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDPDATE
                                  FROM      dbo.PM30200
                                ) Z
                      WHERE     Z.DOCDATE <= @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 it 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 ()