Start a project

Serial numbers in stock by warehouse

Every serial number that is currently in stock, with item, warehouse, manufacturer serial and admission date.

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

Tables: OSRN, OSRQ, OITMUse: Stock counts, warranty look-ups and serial audits.

Tool

SELECT T0.ItemCode, T2.ItemName, T1.WhsCode, T0.DistNumber AS SerialNumber,
  T0.MnfSerial, T0.LotNumber, T0.InDate, T0.ExpDate
FROM OSRN T0
INNER JOIN OSRQ T1 ON T0.ItemCode = T1.ItemCode AND T0.SysNumber = T1.SysNumber
INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode
WHERE T1.Quantity > 0
ORDER BY T0.ItemCode, T1.WhsCode, T0.DistNumber
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

What this query answers

The warehouse team counts serial-managed items and support needs to know where a given serial number is. This returns every serial number that is currently in stock, with the item, the warehouse that holds it, the manufacturer serial number, the lot number and the admission and expiry dates, read from the serial master and its quantity per warehouse.

Sample output

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

ItemCodeItemNameWhsCodeSerialNumberMnfSerialLotNumberInDateExpDate
A00001Laser Printer 142001LP1420-000118HX882312026-08-14
A00001Laser Printer 142001LP1420-000119HX882322026-08-14
A00001Laser Printer 142002LP1420-000124HX882372026-09-02
A00009Handheld Scanner01HS-55102Z7-55102L2026-092026-09-202029-09-20

SQL Server vs SAP HANA

The two-column join (ItemCode and SysNumber) stays the same. Quote every column, for example T0."DistNumber" and T1."Quantity"; OSRN and OSRQ need no quotes because they are upper case.

Common errors and fixes

  • A serial number appears with no warehouse: OSRQ holds the quantity per warehouse. Without the join to OSRQ, the query lists every serial ever created, including sold ones. Keep Quantity > 0.
  • An older report reads OSRI and gives other numbers: This query uses OSRN (serial number master data) and OSRQ (serial quantity per warehouse), the serial tables documented in the SAP Business One SDK 10.0. Compare against those two tables first.

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 dedicated SAP B1 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.Batches expiring in the next 60 daysBatch numbers with stock that expire within 60 days, per warehouse.Inventory value by warehouseStock quantity multiplied by the item average cost, totalled per warehouse.Items below minimum stockInventory items whose on-hand quantity is below the minimum level set on the item.Slow-moving items (no sales in 90 days)Items with stock on hand that have not been invoiced in the last 90 days.Items with no price in a price listSales items that have a zero price in the chosen price list.

Table reference: OSRN table (Serial Numbers (master data)) · OSRQ table (Serial Number Quantities (per warehouse)) · OITM table (Items (item master data))

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 serial numbers in stock by warehouse 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 OSRN, OSRQ, OITM 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