Payment Document Management – Post Dated Checks Report, SQL Script

In this post, an SQL script gathers all types of Post Dated Checks along with all the associated details. The report data set is illustrated in the table below:

 

 

 

Post dated checks types are:

• Saved, before the cash receipt is posted
• Received, when the cash receipt is posted
• Collected, when the cash receipt is collected

 

Tables Included:

                     ·         RVLPD008 | Sales Payment Document Work

                     ·         RVLPD009 | Sales Payment Document Open

 

SELECT  'Saved' AS Document_Status ,

        DOCNUMBR AS [Cash Receipt Number] ,

        CUSTNMBR AS [Customer Number] ,

        CUSTNAME AS [Customer Name] ,

        PMTDOCID AS [Payment Document ID] ,

        CHEKNMBR AS [Check Number] ,

        CHEKBKID AS [Checkbook],

        DOCDATE AS [Document Date] ,

        DUEDATE AS [Check Due Date] ,

        DOCAMNT AS [Check Amount]

FROM    dbo.RVLPD008

WHERE   PDOCSTRM = 1

UNION ALL

SELECT  'Collected' AS Document_Status ,

        DOCNUMBR AS [Cash Receipt Number] ,

        CUSTNMBR AS [Customer Number] ,

        CUSTNAME AS [Customer Name] ,

        PMTDOCID AS [Payment Document ID] ,

        CHEKNMBR AS [Check Number] ,

        CHEKBKID AS Checkbook ,

        DOCDATE AS [Document Date] ,

        DUEDATE AS [Check Due Date] ,

        DOCAMNT AS [Check Amount]

FROM    dbo.RVLPD009

WHERE   PDOCSTRM = 4

UNION ALL

SELECT  'Received' AS Document_Status ,

        DOCNUMBR AS [Cash Receipt Number] ,

        CUSTNMBR AS [Customer Number] ,

        CUSTNAME AS [Customer Name] ,

        PMTDOCID AS [Payment Document ID] ,

        CHEKNMBR AS [Check Number] ,

        CHEKBKID AS Checkbook ,

        DOCDATE AS [Document Date] ,

        DUEDATE AS [Check Due Date] ,

        DOCAMNT AS [Check Amount]

FROM    dbo.RVLPD009

WHERE   PDOCSTRM = 1

 

Best Regards,
Mahmoud M. AlSaadi