SAP Business One Query: Released Production Orders with Component Shortages
SAP Business One SQL query open production order component shortage
Also searched as
- 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
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.
Applies to: SAP Business One 9.x and 10.0, SQL Server and SAP HANA, Query Manager and Query Generator
How to build and run the query
- 1Open Tools > Queries > Query Manager and click New to start a query.
- 2Paste the SELECT below. On HANA keep the double-quoted table and column names. On SQL Server the quotes are optional.
- 3Filter OWOR.Status to 'R' for released orders. The status values are P planned, R released, L closed and C canceled.
- 4Filter WOR1.ItemType to 4 so you read item lines and not resource lines.
- 5Compute the open requirement as PlannedQty minus IssuedQty, and compare it with the warehouse available quantity from OITW.
- 6Save the query, then assign it to a menu or run it from the Query Manager when needed.
- 7Add a due-date filter or a parameter using [%0] so planners can narrow the report to a date range.
Tables that matter
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.
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
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.
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
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.
Common 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.
How an ERP-grounded AI assistant handles this
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.
Frequently asked questions
What do the OWOR Status codes mean?
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?
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?
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?
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.
Related
SAP Business One MRP Wizard Returns No Recommendations: Checklist
When the MRP Wizard in SAP Business One produces an empty recommendation report, the cause is almost always data, not a bug: the item has Planning Method set to None, the scenario excludes the demand documents, the planning horizon is too short, or existing stock and orders already cover demand. Work through item planning data first, then the scenario, then the document selection.
Error fixFix SAP Business One 'Quantity falls below zero' (-2028) on Production Issue
SAP Business One raises 'Quantity falls below zero' (ODBC -2028) when a goods issue, including an Issue for Production, would push an item's on-hand quantity in a warehouse, or a batch or serial, below zero and negative inventory is not allowed. Fix it by receiving the stock first, correcting the warehouse or batch, or deliberately allowing negative stock for the right items.
How-toSAP SE16N vs SQVI: Table Browser vs Ad Hoc Query
SE16N is a single-table browser: fast filtering, formatted display, and export from one table at a time, with an optional edit function for authorized users. SQVI (Quick Viewer) builds ad hoc queries that join multiple tables without writing ABAP, and can be saved as a reusable report. Use SE16N for a quick look at one table; use SQVI when you need data joined across two or more tables.
Error fixSAP M7 021: Deficit of SL Stock Quantity
M7 021 fires during a goods issue, delivery, or confirmation when SAP checks the unrestricted-use (SL) stock for the exact plant, storage location and batch combination and finds less than what you are trying to post out. Fix the entry (batch, storage location, quantity) first; only enable negative stock or downgrade the message to a warning if the business genuinely needs to post ahead of a receipt.
Error fixSAP IDoc Status 51: Application Document Not Posted
Status 51 tells you the IDoc reached the application layer and failed to post, but the status text itself is not the error - the real cause sits in the status record's long text or the linked application log. Fix the underlying data or configuration, then reprocess the IDoc through BD87 rather than editing the status.
Error fixSAP Short Dump TIME_OUT: Maximum Runtime Exceeded
A TIME_OUT runtime error means a dialog (or RFC) work process ran longer than the maximum allowed runtime, controlled by profile parameter rdisp/max_wprun_time (600 seconds by default). The fix is almost always to move the long-running work to background processing or tune the underlying program, not to raise the timeout.
AI for ERPAI for SAP Business One, Without Sending Your Books to a Public API
Add AI to SAP Business One without sending customer data or financials to a public API. Service Layer and DI API integration, sized for an SMB budget.
AI for ERPAI Agents for SAP Production Planning, MRP, and Exception Triage
AI agents that triage SAP MD04 exceptions, explain predictive MRP shortages, and draft planned-order actions, grounded on your own SAP data, deployed on-prem.
Stuck on SAP Business One?
Talk to engineers who work inside SAP Business One every week, and who build private AI that answers these questions from your own ERP data.