Quote from Administrator on April 8, 2022, 2:25 pm

This is a fantastic query/report that lists all Quotes, Orders, Deliveries, AR Invoices, Credit Memos and Returns in a single report. I use this report for analytics, especially with the Boyum Dashboards!

You can easily add prompts for a specific customer, document type, etc.

Enjoy!!!

SELECT 'Quote' "Document", T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T1."ItemCode", T1."Dscription", T1."Quantity", T1."Price", T1."LineTotal",
CASE
WHEN T0."DocStatus" = 'O' THEN 'Open'
ELSE 'Closed'
END "Status" FROM OQUT T0 INNER JOIN QUT1 T1 ON T0."DocEntry" = T1."DocEntry"
UNION ALL
SELECT 'Order' "Document", T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T1."ItemCode", T1."Dscription", T1."Quantity", T1."Price", T1."LineTotal",
CASE
WHEN T0."DocStatus" = 'O' THEN 'Open'
ELSE 'Closed'
END "Status" FROM ORDR T0 INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
UNION ALL
SELECT 'Delivery' "Document", T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T1."ItemCode", T1."Dscription", T1."Quantity", T1."Price", T1."LineTotal",
CASE
WHEN T0."DocStatus" = 'O' THEN 'Open'
ELSE 'Closed'
END "Status" FROM ODLN T0 INNER JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry"
UNION ALL
SELECT 'AR Invoice' "Document", T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T1."ItemCode", T1."Dscription", T1."Quantity", T1."Price", T1."LineTotal",
CASE
WHEN T0."DocStatus" = 'O' THEN 'Open'
ELSE 'Closed'
END "Status" FROM OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
UNION ALL
SELECT 'AR Credit' "Document", T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T1."ItemCode", T1."Dscription", T1."Quantity", T1."Price", T1."LineTotal",
CASE
WHEN T0."DocStatus" = 'O' THEN 'Open'
ELSE 'Closed'
END "Status" FROM ORIN T0 INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
UNION ALL
SELECT 'Returns' "Document", T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T1."ItemCode", T1."Dscription", T1."Quantity", T1."Price", T1."LineTotal",
CASE
WHEN T0."DocStatus" = 'O' THEN 'Open'
ELSE 'Closed'
END "Status" FROM ORDN T0 INNER JOIN RDN1 T1 ON T0."DocEntry" = T1."DocEntry" ORDER BY T0."DocDate" DESC