Query - Accounting report listing all open AR Invoices and AP Invoices in a single report
Here is a simple but powerful SAP Business One accounting report that lists all open SAP Business One AR Invoices and all open SAP Business One AP Invoices in a single query/report.
SELECT T0.[DocDueDate] AS "Due Date",
'Receivables' AS "Type",
T0.[DocNum] AS "Doc. No.",
T0.[CardName] AS "BP Name",
T1.[ItemCode],
T1.[Dscription],
T1.[Quantity],
T1.[LineTotal]
FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[DocStatus] = 'O'
UNION ALL
SELECT T0.[DocDueDate] AS "Due Date",
'Payables' AS "Type",
T0.[DocNum] AS "Doc. No.",
T0.[CardName] AS "BP Name",
T1.[ItemCode],
T1.[Dscription],
T1.[Quantity],
T1.[LineTotal]
FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[DocStatus] = 'O'
ORDER BY T0.[DocDueDate]