Outgoing 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 designed for outgoing payments to vendors. For incoming payments from customers, adjust the query to use ORCT for customer payments and link it to OINV for sales invoices. • You can customize the SELECT clause to include additional details, such as currency, payment methods, or bank details if required.

11/14/20241 min read

SELECT

T0.CardCode AS 'Vendor Code',

T0.CardName AS '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 'Linked Purchase Invoice Number',

T2.DocDate AS 'Invoice Date',

T2.DocTotal AS 'Invoice Amount',

T3.PymntGroup AS 'Payment Terms'

FROM

OCRD T0

INNER JOIN OVPM T1 ON T0.CardCode = T1.CardCode -- Link to outgoing payment

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

LEFT JOIN OPCH T2 ON T2R.DocEntry = T2.DocEntry -- Get purchase 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