SAP Business One – Planned Production Order Shortages Query

This query uses only standard fields, and lists for any production order in Planned status, component shortages. The “AND T0.[Status] = ‘P'” can be changed to ” AND T0.[Status] = ‘R'” to show release production order shortages, or ” AND T0.[Status] <> ‘L'” to show Planned and Release component shortages in production.

SELECT T0.[DocNum], T0.[ItemCode], T1.[ItemCode], T2.[ItemName], T1.[PlannedQty], T2.[OnHand], T2.[OnHand] – T1.[PlannedQty] AS ‘Qty Short’, T0.[DueDate], T2.[OnOrder], T2.[LeadTime] FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE T2.[OnHand] – T1.[PlannedQty] < 0 AND T0.[Status] = ‘P’