Developer ToolsFree generatorPDF, Excel, Word exportSAP Business OneDiscrete Manufacturing

SAP B1 SQL Query Generator: Items (OITM) and Sales Orders (ORDR)

Most day-to-day SAP Business One reporting questions are a join between a header table and its lines: items with stock, sales orders with open quantity. This generator produces three tested patterns that you can paste into the Query Manager or a Crystal Reports data source.

Fill in

Item group code (ItmsGrpCod)

Numeric group code from OITB.

Minimum on-hand to list

Sales orders from (DocDate)

Sales orders to (DocDate)

Customer code (CardCode)

Rows to return

Generated code

Download:

1. Stock on hand by item group (OITM)

SELECT TOP 100
  T0.ItemCode,
  T0.ItemName,
  T0.OnHand,
  T0.IsCommited AS Committed,
  T0.OnOrder,
  (T0.OnHand - T0.IsCommited + T0.OnOrder) AS Available,
  T0.AvgPrice
FROM OITM T0
WHERE T0.ItmsGrpCod = 100
  AND T0.OnHand >= 0
  AND T0.validFor = 'Y'
ORDER BY T0.ItemCode;

2. Open sales order lines for one customer (ORDR, RDR1)

SELECT
  T0.DocNum,
  T0.DocDate,
  T0.DocDueDate,
  T0.CardCode,
  T0.CardName,
  T1.LineNum,
  T1.ItemCode,
  T1.Dscription,
  T1.Quantity,
  T1.OpenQty,
  T1.LineTotal
FROM ORDR T0
INNER JOIN RDR1 T1 ON T1.DocEntry = T0.DocEntry
WHERE T0.CardCode = 'C20000'
  AND T0.DocDate >= '2026-01-01'
  AND T0.DocDate <= '2026-12-31'
  AND T0.DocStatus = 'O'
  AND T0.CANCELED = 'N'
  AND T1.LineStatus = 'O'
ORDER BY T0.DocDueDate, T0.DocNum, T1.LineNum;

3. Open demand against stock, by item

SELECT
  T1.ItemCode,
  T2.ItemName,
  SUM(T1.OpenQty) AS OpenDemand,
  MAX(T2.OnHand) AS OnHand
FROM ORDR T0
INNER JOIN RDR1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode = T1.ItemCode
WHERE T0.DocStatus = 'O'
  AND T0.CANCELED = 'N'
  AND T1.LineStatus = 'O'
  AND T0.DocDate >= '2026-01-01'
  AND T0.DocDate <= '2026-12-31'
GROUP BY T1.ItemCode, T2.ItemName
HAVING SUM(T1.OpenQty) > MAX(T2.OnHand)
ORDER BY SUM(T1.OpenQty) DESC;

Written for SQL Server editions of SAP Business One using T-SQL (TOP). On SAP HANA, replace TOP n with LIMIT n at the end, and wrap table and column names in double quotes, for example "OITM"."ItemCode". Table and column names above are standard B1 names, but check them in your version through the Query Generator tree or the DI API reference. Run queries as read-only reports and test on a non-production company first.

Tired of hand-writing B1 queries?

We can show how a read-only natural language layer answers B1 questions and displays the SQL it ran.

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

How to use the generator

Enter the item group, date range and customer you need. The three code blocks update with your values. Copy one into Tools, Queries, Query Manager (or Query Generator), save it and run it. For a user query that others will run, give it a clear name and put it in a query category. Replace the example group code with a real ItmsGrpCod, which you can read from table OITB. Keep the SELECT only. Writing to B1 tables with SQL bypasses business logic and is unsupported.

The tables behind the queries

OITM is the item master. OnHand is total stock across warehouses, IsCommited is quantity committed to sales orders and production, and OnOrder is on open purchase orders and production receipts. Available to promise is often shown as OnHand minus IsCommited plus OnOrder. ORDR is the sales order header and RDR1 holds its lines. DocStatus O means open and C closed on the header, and LineStatus does the same per line. Always filter out CANCELED = 'Y' orders to avoid double counting.

OITW holds stock per warehouse if you need it split
OCRD is the business partner master, joined on CardCode
OITB is the item groups table
ODLN and DLN1 hold deliveries, OINV and INV1 hold invoices

Reading the third query

The demand against stock query groups open sales order lines by item and compares total open quantity with on-hand. Items that show demand above stock are candidates for a purchase or production order. It is a quick shortage list, not MRP: it ignores incoming supply, lead times and substitutions. For a proper plan use the Material Requirements Planning wizard, which creates recommendations you can convert into documents.

Common mistakes

A few errors account for most wrong numbers in home-made B1 queries. Check each before you trust a result.

Joining on DocNum instead of DocEntry
Forgetting that DocNum can repeat across document series
Summing LineTotal across currencies without using the local currency columns
Ignoring cancelled documents and their cancellation documents
Using Quantity when you need OpenQty

Frequently Asked Questions

Where do I run these queries in SAP Business One?

Open Tools, Queries, Query Manager or Query Generator, paste the SQL and run it. You can save it as a user query, attach it to a field as a formatted search or use it as the data source for a report. Use a test company first.

Do these work on SAP HANA?

The logic does, the syntax needs a small change. Replace TOP n with LIMIT n, and wrap object names in double quotes. Date literals and string quoting follow HANA SQL rules. The notes under the tool list the main differences.

What does IsCommited mean in OITM?

IsCommited is the quantity committed to open sales orders and, in many set-ups, production order components. The spelling is as it appears in the B1 table. Subtract it from OnHand to see stock not already promised.

Can I update data with SQL in B1?

You should not. Direct updates skip B1 business logic, authorisations and audit trails, and can break support. Use the DI API, DTW or the Service Layer for changes, and keep SQL queries read-only.

If your team writes the same B1 queries every week, we can build a natural language layer that generates and shows the SQL read-only, with B1 roles respected. Ask us about it.

Talk to an Expert Explore More Free Tools

Related Tools

SAP

More Developer Tools tools

See all

Back to All Tools