SAPFree generatorPDF, Excel, Word exportSAP Business OneDiscrete Manufacturing

SAP Business One Production Component Shortage SQL Generator

Planners in Business One often ask the same question: which released production orders cannot be completed because a component is short? This generator writes the query over production orders, their component rows and stock by warehouse.

Fill in

Production order status

R = Released, P = Planned. L = Closed, C = Canceled.

Due on or before

Component warehouse

Generated code

Download:

Shortage query (SQL Server syntax)

SELECT
  T0.DocNum,
  T0.ItemCode AS ParentItem,
  T0.DueDate,
  T1.ItemCode AS Component,
  T1.wareHouse AS Whs,
  T1.PlannedQty,
  T1.IssuedQty,
  (T1.PlannedQty - T1.IssuedQty) AS OpenQty,
  T2.OnHand,
  T2.IsCommited AS Committed,
  T2.OnOrder,
  (T2.OnHand - T2.IsCommited + T2.OnOrder) AS Available
FROM OWOR T0
INNER JOIN WOR1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN OITW T2 ON T2.ItemCode = T1.ItemCode AND T2.WhsCode = T1.wareHouse
WHERE T0.Status = 'R'
  AND T0.DueDate <= '2026-11-30'
  AND T1.ItemType = 4
  AND T1.wareHouse = '01'
  AND (T1.PlannedQty - T1.IssuedQty) > ISNULL(T2.OnHand, 0)
ORDER BY T0.DueDate, T0.DocNum;

WOR1.ItemType 4 selects item components (resources and text lines have other values). The query compares open quantity per order with on-hand quantity and ignores competition between orders for the same stock, so for a true multi-order shortage sum open quantity by component first. On SAP HANA, wrap identifiers in double quotes and replace ISNULL with IFNULL. Test in Query Manager before saving.

Plan-to-produce reporting for Business One

Optional: a short call about shortage and capacity reporting for your B1 plant.

Optional. The tool and every export work without this. No spam.

How the query works

OWOR is the production order header and WOR1 holds its rows. For each component row the query computes open quantity as planned minus issued, joins stock for the same item and warehouse from OITW, and keeps rows where the open quantity exceeds what is on hand. It also shows committed and on-order quantities so a planner can see whether a purchase order is already covering the gap.

Status R is released, P planned, L closed, C canceled
ItemType 4 filters out resources and text lines
Available = on hand - committed + on order
Filter on the due date to focus on the next weeks

Reading the output

A row with OpenQty above OnHand is a hard shortage at that warehouse today. If Available is still positive because of purchase orders, check the vendor due date against the production order due date. Sort by due date to put the most urgent first, and export to Excel to send a buyer the list.

Limits to know

The same stock may be needed by several orders, so a row-by-row comparison can understate a shortage. The standard MRP wizard and recommendation report handle netting across orders, and are the better source when MRP is set up. Use this query as a quick shop-floor check and a starting point for a custom report.

Frequently Asked Questions

Can I run this in Query Manager?

Yes on SQL Server editions, paste it into a new query in Query Manager and execute. On HANA editions the syntax needs double-quoted identifiers and IFNULL instead of ISNULL, otherwise it will fail to parse.

Why are phantom items missing?

Phantom bills are exploded into their own components when the production order is created, so you see the lower-level items on WOR1 rather than the phantom parent. Check the BOM type on the product tree if rows look different from expectation.

Does it handle backflush components?

Backflush components are issued at receipt, so IssuedQty stays zero until then. The query would show them as open. Add a filter on the issue method column of WOR1 if you want to exclude them.

Is this the same as the MRP recommendation?

No. MRP nets all demand and supply by time bucket and proposes orders. This query is a snapshot of order component rows against current warehouse stock.

If planners would rather ask which orders are at risk in plain language and get the query shown back, Netray can connect that to your Business One data, read-only.

Talk to an Expert Explore More Free Tools

Related Tools

SAP

More SAP tools

See all

Back to All Tools