# SAP Business One Production Component Shortage SQL Generator

> Source: https://netray.co/tools/sap-business-one-production-component-shortage-sql
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Pick a status, due date and warehouse and get a query that shows components short against on-hand stock for open production orders.

## Category

sap

## Erp

SAP Business One

## Industry

Discrete Manufacturing

## Audience

- Production planner
- B1 consultant
- Report writer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Production order status

**Key:** status

**Type:** select

**Options:**

- R
- P

**Default:** R

**Help:** R = Released, P = Planned. L = Closed, C = Canceled.

#### Due on or before

**Key:** due

**Type:** date

**Default:** 2026-11-30

#### Component warehouse

**Key:** whs

**Type:** text

**Default:** 01

**Blocks:**

#### Shortage query (SQL Server syntax)

**Body:** 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 = '{{status}}'
  AND T0.DueDate <= '{{due}}'
  AND T1.ItemType = 4
  AND T1.wareHouse = '{{whs}}'
  AND (T1.PlannedQty - T1.IssuedQty) > ISNULL(T2.OnHand, 0)
ORDER BY T0.DueDate, T0.DocNum;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How the query works

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Can I run this in Query Manager?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

sap b1 production order shortage query, sap business one owor wor1 query, sap b1 component shortage report, sap b1 production order sql, sap business one query manager production, sap b1 missing components, sap b1 wor1 issuedqty, sap b1 material shortage

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json