Start a project

Production orders by status

Planned and released production orders with planned, completed and rejected quantity, due date and days late.

By Zain Abbas, Founder & Lead SAP Business One / AI Engineer · Last updated

Tables: OWORUse: Daily production meetings and shop-floor follow-up.

Tool

SELECT T0.DocNum, T0.ItemCode, T0.ProdName,
  CASE T0.Status WHEN 'P' THEN 'Planned' WHEN 'R' THEN 'Released' END AS OrderStatus,
  T0.PlannedQty, T0.CmpltQty, T0.RjctQty, T0.PlannedQty - T0.CmpltQty AS RemainingQty,
  T0.Warehouse, T0.PostDate, T0.DueDate,
  CASE WHEN T0.DueDate < GETDATE() THEN DATEDIFF(day, T0.DueDate, GETDATE()) ELSE 0 END AS DaysLate
FROM OWOR T0
WHERE T0.Status IN ('P', 'R')
ORDER BY T0.DueDate, T0.DocNum
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

What this query answers

The production planner starts the day with one list of work that is not finished: which orders are planned or released, how much is done, how much is still to make and which are late. This returns every planned or released production order with planned, completed and rejected quantity, the due date and the days past due.

Sample output

Illustrative demo values. Your columns are the same; the numbers come from your company.

DocNumItemCodeProdNameOrderStatusPlannedQtyCmpltQtyRjctQtyRemainingQtyWarehousePostDateDueDateDaysLate
301P10001Assembled WorkstationReleased5032118012026-10-282026-11-055
304P10003Cable KitReleased40040000012026-11-022026-11-082
307P10002Printer StandPlanned12000120022026-11-062026-11-140
309P10001Assembled WorkstationPlanned250025012026-11-092026-11-200

SQL Server vs SAP HANA

CASE works the same on HANA. The DATEDIFF becomes DAYS_BETWEEN(T0."DueDate", CURRENT_TIMESTAMP), and the status values 'P' and 'R' stay as they are.

Common errors and fixes

  • Closed orders do not show: That is by design. OWOR.Status is P (planned), R (released), L (closed) or C (cancelled); the query keeps P and R. Add 'L' to the IN list to see closed orders too.
  • An order is finished but still released: Completed quantity can reach the planned quantity while the order stays released until someone closes it. RemainingQty = 0 marks those orders for closing.

Need this report built into SAP Business One?

A saved query answers the question once. We do custom SAP Business One development: add-ons, integrations and reports, from the database to the screen. Or hire a remote SAP Business One developer for the whole job, fixed price per phase.

Inventory

Related queries

Stock by warehouseOn hand, committed, ordered and available quantity per item and warehouse.Items below minimum stockInventory items whose on-hand quantity is below the minimum level set on the item.Open purchase orders by vendorOpen purchase order lines with quantity still to receive, grouped by vendor.Serial numbers in stock by warehouseEvery serial number that is currently in stock, with item, warehouse, manufacturer serial and admission date.Inventory value by warehouseStock quantity multiplied by the item average cost, totalled per warehouse.Slow-moving items (no sales in 90 days)Items with stock on hand that have not been invoiced in the last 90 days.

Table reference: OWOR table (Production Order (header))

How it works

Three steps, no signup

1

Copy the right version

Use the SQL Server tab for MS SQL databases and the HANA tab for SAP B1 on HANA.

2

Open Query Generator

In SAP Business One go to Tools → Queries → Query Generator, paste and click Execute.

3

Save and share

Click Save, pick a category and give the query a name. Your team can run it from Query Manager.

FAQ

Questions about this tool

How do I run the production orders by status query in SAP Business One?

Copy the version that matches your database, open Tools → Queries → Query Generator, paste it in the SQL box and click Execute. Save it to reuse it later.

Which tables does this query use?

It reads OWOR from the standard SAP Business One schema. No user-defined fields are needed.

Can this be turned into a dashboard or alert?

Yes. We turn queries into Crystal reports, Excel reports that refresh themselves, SAP B1 alerts or WhatsApp messages for managers.

More free tools

Built for SAP Business One teams

ERP selector: which ERP suits you?Answer 15 questions and compare ERPs on processes, size, budget, hosting and your country’s rules, with every claim sourced.SAP B1 dashboard builderDrag and drop SAP B1 dashboards for Sales, Purchasing, Inventory, Receivables and Finance with click-to-filter. Works offline, no developer needed.SQL to HANA query converterPaste a SQL Server query and get the SAP HANA version, with every change explained.SAP B1 table finderSearch any table: what it stores, its line tables and its key fields.

Need this inside SAP Business One?

We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.

Start a project Chat on WhatsApp