Payment Document Management Link to General Ledger

For customers who are using Payment Document Management module, they will be in a big need to reconcile their post dated checks against general ledger, and this is almost requested by all our customers.

The below view works to get this resolved! It brings the journal entry number and GL details for each Post Date Document and for both Receivables and Payables checks.

 

/****** Object:  View [dbo].[DI_PDC_GL_Link]    Script Date: 8/24/2017 10:13:57 AM ******/
CREATE VIEW [dbo].[DI_PDC_GL_Link] AS -- Sales Checks (Checks Under Collections)

SELECT GL20000.JRNENTRY AS [Journal Number],
       GL20000.SEQNUMBR AS [GL Line Sequence Number],
       dbo.CM20600.CMXFRNUM AS [Transfer Number],
       dbo.RVLPD011.CMRecordNumber AS [Transfer Record Number],
       dbo.RVLPD011.REMITID AS [Remitance ID],
       dbo.RVLPD011.PMTDOCID AS [Payment Document],
       dbo.RVLPD011.ORICBOOK AS [From Checkbook],
       dbo.RVLPD011.DESCBOOK AS [To Checkbook],
       dbo.RVLPD011.CURNCYID AS Currency,
       dbo.RVLPD011.TRXDATE AS [Transaction Date],
       dbo.RVLPD011.NUMOFTRX AS [Number of Checks],
       dbo.RVLPD011.FUNCTAMT AS [Functional Total Amount],
       dbo.RVLPD011.ORIGAMT AS [Originating Total Amount],
       RVLPD013Sorted.RMDTYPAL AS [Document Type],
       RVLPD013Sorted.DOCNUMBR AS [Document Number],
       dbo.RVLPD009.CUSTNMBR AS [Customer Number],
       dbo.RVLPD009.CUSTNAME AS [Customer Name],
       dbo.RVLPD009.STMTNAME AS [Statement Name],
       dbo.RVLPD009.CHEKNMBR AS [Check Number],
       dbo.RVLPD009.CHEKBKID AS [Checkbook ID],
       dbo.RVLPD009.DOCDATE AS [Check Date],
       dbo.RVLPD009.DUEDATE AS [Due Date],
       RVLPD009.DOCAMNT AS [Check Amount],
       RVLPD013Sorted.Sequence
FROM dbo.CM20600
LEFT OUTER JOIN dbo.RVLPD011 ON dbo.CM20600.Xfr_Record_Number = dbo.RVLPD011.CMRecordNumber
LEFT OUTER JOIN
  (SELECT *,
          CASE
              WHEN ROW_NUMBER() OVER (Partition BY REMITID
                                      ORDER BY Dex_Row_ID) = 1 THEN 1
              ELSE (ROW_NUMBER() OVER (Partition BY REMITID
                                       ORDER BY Dex_Row_ID)*2+1)-2
          END * 16384 AS SEQUENCE
   FROM RVLPD013) RVLPD013Sorted ON dbo.RVLPD011.REMITID = RVLPD013Sorted.REMITID
LEFT OUTER JOIN dbo.RVLPD009 ON RVLPD013Sorted.DOCNUMBR = dbo.RVLPD009.DOCNUMBR
LEFT OUTER JOIN GL20000 ON ORCTRNUM = dbo.RVLPD011.REMITID
AND SOURCDOC = 'RMPDC'
AND RVLPD013Sorted.Sequence = SEQNUMBR
WHERE GL20000.JRNENTRY IS NOT NULL
UNION ALL --Purchasing Checks (Deferred Checks)

SELECT GL20000.JRNENTRY AS [Journal Number],
       GL20000.SEQNUMBR AS [GL Line Sequence Number],
       dbo.CM20600.CMXFRNUM AS [Transfer Number],
       dbo.RVLPD027.CMRecordNumber AS [Transfer Record Number],
       dbo.RVLPD027.REMITID AS [Remitance ID],
       dbo.RVLPD027.PMTDOCID AS [Payment Document],
       dbo.RVLPD027.ORICBOOK AS [From Checkbook],
       dbo.RVLPD027.DESCBOOK AS [To Checkbook],
       dbo.RVLPD027.CURNCYID AS Currency,
       dbo.RVLPD027.TRXDATE AS [Transaction Date],
       dbo.RVLPD027.NUMOFTRX AS [Number of Checks],
       dbo.RVLPD027.FUNCTAMT AS [Functional Total Amount],
       dbo.RVLPD027.ORIGAMT AS [Originating Total Amount],
       RVLPD029Sorted.DOCTYPE AS [Document Type],
       RVLPD029Sorted.DOCNUMBR AS [Document Number],
       RVLPD025.VENDORID AS [Vendor Number],
       RVLPD025.VENDNAME AS [Vendor Name],
       RVLPD025.VNDCHKNM AS [Statement Name],
       RVLPD025.CHEKNMBR AS [Check Number],
       RVLPD025.CHEKBKID AS [Checkbook ID],
       RVLPD025.DOCDATE AS [Check Date],
       RVLPD025.DUEDATE AS [Due Date],
       RVLPD025.DOCAMNT AS [Check Amount],
       RVLPD029Sorted.Sequence
FROM dbo.CM20600
LEFT OUTER JOIN dbo.RVLPD027 ON dbo.CM20600.Xfr_Record_Number = dbo.RVLPD027.CMRecordNumber
LEFT OUTER JOIN
  (SELECT *,
          CASE
              WHEN ROW_NUMBER() OVER (Partition BY REMITID
                                      ORDER BY Dex_Row_ID) = 1 THEN 1
              ELSE (ROW_NUMBER() OVER (Partition BY REMITID
                                       ORDER BY Dex_Row_ID)*2+1)-2
          END * 16384 AS SEQUENCE
   FROM RVLPD029) RVLPD029Sorted ON dbo.RVLPD027.REMITID = RVLPD029Sorted.REMITID
LEFT OUTER JOIN
  (SELECT *
   FROM RVLPD025
   UNION ALL SELECT *
   FROM RVLPD026) RVLPD025 ON RVLPD029Sorted.DOCNUMBR = RVLPD025.DOCNUMBR
LEFT OUTER JOIN GL20000 ON ORCTRNUM = dbo.RVLPD027.REMITID
AND SOURCDOC = 'PMPDC'
AND RVLPD029Sorted.Sequence = SEQNUMBR
WHERE GL20000.JRNENTRY IS NOT NULL

Hope that this helps.

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo