Query - Shipment Performance Report
This is an SAP Business One Shipment Performance Report that also lists any remaining back orders as well as displaying if product shipped was Made from Stock or Made to Order, all by Customer Group for a starting Sales Order Post Date. This is a fantastic query to save as a Boyum Usability Universal Function as well to provide 3 level grouping as can be seen in the picture below.
Copy and paste this query into your SAP Business One Query Generator.
SELECT T2."GroupName" "Group",
T0."CardName" "Customer",
T0."DocDate" "Post Date",
CASE
WHEN T3."ShipDate" = T5."ShipDate" THEN 'On-Time'
WHEN T3."ShipDate" > T5."ShipDate" THEN 'Early'
WHEN T3."ShipDate" < T5."ShipDate" THEN 'Late'
ELSE 'Not Shipped'
END AS "Status",
T0."DocNum" "Order No",
T0."DocDueDate" "Due Date",
T5."ShipDate" "Ship Date",
CASE
WHEN LENGTH(T6."OriginNum") > 1 THEN 'Made to Order'
ELSE 'Made from Stock'
END AS "Provisioning",
T3."ItemCode" "Item",
T3."Dscription" "Item Desc",
T3."Quantity" "Qty Ord",
T3."OpenQty" "Qty Backordered",
T4."OnHand" "Qty In Stock",
T4."IsCommited" "Qty Allocated",
T4."OnOrder" "Qty On Order" FROM ORDR T0 INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode"
INNER JOIN OCRG T2 ON T1."GroupCode" = T2."GroupCode"
INNER JOIN RDR1 T3 ON T0."DocEntry" = T3."DocEntry"
INNER JOIN OITM T4 ON T3."ItemCode" = T4."ItemCode"
LEFT JOIN DLN1 T5 ON T3."TrgetEntry" = T5."DocEntry"
LEFT JOIN OWOR T6 ON T0."DocNum" = T6."OriginNum"
WHERE T5."DocEntry" > 1 AND
T5."ShipDate" IS NOT NULL
AND T0."DocDate" >=[%0]