Payment Report

Notes • For outgoing payments (vendor payments), you would link to the OPCH table instead of OINV for purchase invoices. • This query provides a consolidated view. For more details, such as foreign currency amounts or multiple linked invoices, you may need to expand on it further.

5/8/20241 min read

SELECT

T0.CardCode AS 'Customer/Vendor Code',

T0.CardName AS 'Customer/Vendor Name',

T1.DocNum AS 'Payment Document Number',

T1.DocDate AS 'Payment Date',

T1.DocTotal AS 'Payment Amount',

T1.Comments AS 'Remarks',

T2.DocNum AS 'Related 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

LEFT JOIN RCT2 T2R ON T1.DocEntry = T2R.DocNum

LEFT JOIN OINV T2 ON T2R.DocEntry = T2.DocEntry -- For Incoming Payments

LEFT JOIN OCTG T3 ON T0.GroupNum = T3.GroupNum

WHERE

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

ORDER BY

T1.DocDate DESC, T0.CardCode;

Solutions

Empowering businesses with ERP expertise and training.

Support

Growth

erpfactory@outlook.com