Incoming Payment Report

Notes • Replace [Start Date] and [End Date] with the desired date range, e.g., '2024-01-01' and '2024-12-31'. • This query is for incoming payments from customers. For outgoing payments to vendors, modify the table links to OPCH for purchase invoices. • You can adjust the columns in the SELECT clause to include additional information, such as currency or payment methods if needed.

11/14/20241 min read

SELECT

T0.CardCode AS 'Customer Code',

T0.CardName AS 'Customer Name',

T1.DocNum AS 'Payment Document Number',

T1.DocDate AS 'Payment Date',

T1.DocTotal AS 'Payment Amount',

T1.Comments AS 'Remarks',

T2.DocNum AS 'Linked Invoice Number',

T2.DocDate AS 'Invoice Date',

T2.DocTotal AS 'Invoice Amount',

T3.PymntGroup AS 'Payment Terms'

FROM

OCRD T0

INNER JOIN ORCT T1 ON T0.CardCode = T1.CardCode -- Link to incoming payment

LEFT JOIN RCT2 T2R ON T1.DocEntry = T2R.DocNum -- Link payment to invoice references

LEFT JOIN OINV T2 ON T2R.DocEntry = T2.DocEntry -- Get invoice details

LEFT JOIN OCTG T3 ON T0.GroupNum = T3.GroupNum -- Link to payment terms

WHERE

T1.Canceled = 'N' -- Exclude canceled payments

AND T1.DocDate BETWEEN '[Start Date]' AND '[End Date]' -- Set the date range here

ORDER BY

T1.DocDate DESC, T0.CardCode;

Solutions

Empowering businesses with ERP expertise and training.

Support

Growth

erpfactory@outlook.com