# SAP Business One Query: Released Production Orders with Component Shortages

> Source: https://netray.co/erp-help/sapb1-open-production-order-component-shortage-sql-query
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

Join OWOR (production order header), WOR1 (components) and OITW (stock per warehouse) to see, for every released production order, how much of each component is still to be issued and whether the warehouse can cover it. Run it in Tools > Queries > Query Manager, using double-quoted identifiers on HANA and the same query with minor syntax changes on SQL Server.

## Kind

how-to

## System

sap

## Erp

SAP Business One

## Module

Query Manager / Production

## Query

SAP Business One SQL query open production order component shortage

## Alternate Queries

- SAP B1 query production order components not issued
- SAP Business One OWOR WOR1 query
- SAP B1 shortage report released production orders
- SAP Business One query manager HANA production order

## Applies To

SAP Business One 9.x and 10.0, SQL Server and SAP HANA, Query Manager and Query Generator

## Steps

### How to build and run the query

**Items:**

- Open Tools > Queries > Query Manager and click New to start a query.
- Paste the SELECT below. On HANA keep the double-quoted table and column names. On SQL Server the quotes are optional.
- Filter OWOR.Status to 'R' for released orders. The status values are P planned, R released, L closed and C canceled.
- Filter WOR1.ItemType to 4 so you read item lines and not resource lines.
- Compute the open requirement as PlannedQty minus IssuedQty, and compare it with the warehouse available quantity from OITW.
- Save the query, then assign it to a menu or run it from the Query Manager when needed.
- Add a due-date filter or a parameter using [%0] so planners can narrow the report to a date range.

## Sections

### Tables that matter

**Body:**

OWOR is the production order header: DocEntry, DocNum, ItemCode of the finished product, PlannedQty, CmpltQty, Status and DueDate. WOR1 holds the lines: ItemCode of the component, PlannedQty, IssuedQty, wareHouse, and ItemType, which is 4 for stock items and 290 for resources.

OITW holds stock by item and warehouse. OnHand is physical stock, IsCommited is stock committed to open sales orders and production orders, and OnOrder is on open purchase and production orders. Available is usually computed as OnHand minus IsCommited plus OnOrder, but for a shortage view of what can be issued today, use OnHand minus IsCommited.

Note that Business One uses these exact names, including the unusual spelling IsCommited with one T. Copy it exactly or the query fails.

**Code:**

SELECT T0."DocNum" AS "Prod Order", T0."ItemCode" AS "FG", T0."DueDate",
       T1."ItemCode" AS "Component", T1."wareHouse" AS "Whs",
       T1."PlannedQty", T1."IssuedQty",
       (T1."PlannedQty" - T1."IssuedQty") AS "OpenQty",
       T2."OnHand", T2."IsCommited", T2."OnOrder"
FROM "OWOR" T0
INNER JOIN "WOR1" T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN "OITW" T2 ON T2."ItemCode" = T1."ItemCode" AND T2."WhsCode" = T1."wareHouse"
WHERE T0."Status" = 'R' AND T1."ItemType" = 4
  AND (T1."PlannedQty" - T1."IssuedQty") > T2."OnHand"
ORDER BY T0."DueDate", T0."DocNum"

### Reading the result

**Body:**

Each row is a component line that cannot be fully issued from the stock currently in its warehouse. The OnOrder column tells you whether a purchase order or a production order is already coming. If OnOrder is zero and OpenQty is large, you have a real shortage and need a purchase request.

The check compares one line against warehouse stock. If several production orders compete for the same component, each line may look covered on its own but the total is not. For a competing-demand view, group by component and warehouse and sum OpenQty across orders, then compare the sum with OnHand.

**Code:**

SELECT T1."ItemCode", T1."wareHouse",
       SUM(T1."PlannedQty" - T1."IssuedQty") AS "TotalOpen",
       MAX(T2."OnHand") AS "OnHand"
FROM "OWOR" T0
INNER JOIN "WOR1" T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN "OITW" T2 ON T2."ItemCode" = T1."ItemCode" AND T2."WhsCode" = T1."wareHouse"
WHERE T0."Status" = 'R' AND T1."ItemType" = 4
GROUP BY T1."ItemCode", T1."wareHouse"
HAVING SUM(T1."PlannedQty" - T1."IssuedQty") > MAX(T2."OnHand")

### Parameters, performance and permissions

**Body:**

Use [%0] style parameters in Query Manager for dates or item codes, for example T0."DueDate" <= '[%0]'. Business One prompts for the value when the query runs. Keep the filter on Status first so the join works on a small set of rows.

Queries run with the logged-in user's access to the data. A user with limited authorizations on warehouses or items may see fewer rows. For scheduled or external reporting, use a read-only database user or a view rather than Query Manager.

On SQL Server, you can drop the quotes and use the same SQL. Avoid writing to these tables directly. Changes to production orders, stock quantities and commitments should go through documents, the Service Layer or the DI API, because direct updates break internal consistency.

## Pitfalls

- Spelling IsCommited as IsCommitted, which does not exist in OITW.
- Forgetting ItemType = 4 and pulling resource lines into the stock comparison.
- Comparing against total stock across warehouses when the component line points to one warehouse.
- Reading each line in isolation while several orders compete for the same component.
- Updating OWOR, WOR1 or OITW directly instead of using documents.
- Including closed or canceled orders because the Status filter was left out.

## Ai Angle

An assistant grounded in SAP Business One, such as ERPray, builds this kind of query from a plain question like 'which released production orders are short of components this week?'. It uses OWOR, WOR1 and OITW, shows the SQL it ran so a planner can check it, and handles the competing-demand roll-up. It reads with a read-only account and respects B1 authorizations. If you prefer a fixed report, Query Manager is enough, and an assistant mainly helps when the questions change often.

## Faq

### What do the OWOR Status codes mean?

**Answer:** P is planned, R is released, L is closed and C is canceled. For a shortage report you usually want R, since planned orders do not yet commit stock and closed orders no longer need components.

### Which table holds production order components?

**Answer:** WOR1 holds component and resource lines, linked to OWOR by DocEntry. Use the ItemType column to separate stock items (4) from resources (290). IssuedQty on each line tracks how much has been issued so far.

### Does this query work on SAP HANA and SQL Server?

**Answer:** Yes, with small differences. HANA needs the double-quoted identifiers as written. SQL Server accepts them as well in Query Manager, and also allows unquoted names. Test date literals and any string functions on your platform.

### Can I use this query to create purchase requests?

**Answer:** The query only reads data. Use it to decide what to buy, then create requests or orders through the normal documents. MRP is the better tool when you want recommendations calculated for you across the whole item base.

## Search terms

sap business one production order query, sap b1 owor wor1, sap b1 query manager, sap b1 component shortage report, sap business one hana query, sap b1 oitw on hand committed, sap b1 released production orders, sap b1 sql query production, sap business one open production orders, sap b1 query generator

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