Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Tuesday, July 15, 2014

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:

Untitled

 

 

 

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

No comments:

Post a Comment