OSRN table in SAP Business One (Serial Numbers (master data))
OSRN is the SAP Business One table that stores serial number master data: one row per item and serial, with the serial number, manufacturer serial, warranty and expiry dates and status. Where the unit is stored is not here: quantities per warehouse are in OSRQ, joined on OSRQ.MdAbsEntry = OSRN.AbsEntry.
OSRN key fields
| Field | What it holds |
|---|---|
AbsEntry | Internal key of the serial. OSRQ.MdAbsEntry points to it. |
ItemCode | Item. Joins to OITM.ItemCode. |
SysNumber | Internal system number per item. With ItemCode it identifies the serial. |
DistNumber | The serial number users see. |
MnfSerial | Manufacturer serial number. |
LotNumber | Lot number. |
InDate | Admission date. |
GrntStart / GrntExp | Manufacturer warranty start and end dates. |
ExpDate | Expiration date. |
Status | 0 = available, 1 = unavailable, 2 = allocated. |
Location | Free-text location. |
CreateDate / UpdateDate | Creation and update dates. |
How OSRN relates to other tables
OSRN is the serial master and OSRQ holds one row per serial and warehouse with Quantity, CommitQty and CountQty. Join them on OSRQ.MdAbsEntry = OSRN.AbsEntry, or on ItemCode and SysNumber together. Only items with serial number management (OITM.ManSerNum = Y) have rows here.
Common joins
OSRN T0 INNER JOIN OSRQ T1 ON T1.MdAbsEntry = T0.AbsEntry (quantity per warehouse)OSRN T0 INNER JOIN OSRQ T1 ON T1.ItemCode = T0.ItemCode AND T1.SysNumber = T0.SysNumber (same join, by item and system number)OSRN T0 INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode (item master)
When you use the OSRN table
Use OSRN with OSRQ to list serials in stock per warehouse, to look up a serial for a warranty claim, to report serials whose manufacturer warranty ends soon, and to check that a scanned serial exists before a delivery is posted.
Example query: serial numbers in stock per warehouse with warranty end date
SQL Server
SELECT T0.ItemCode, T2.ItemName, T0.DistNumber AS SerialNumber, T0.MnfSerial, T0.GrntExp, T1.WhsCode FROM OSRN T0 INNER JOIN OSRQ T1 ON T1.MdAbsEntry = T0.AbsEntry INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode WHERE T1.Quantity > 0 ORDER BY T0.ItemCode, T0.DistNumber
SAP HANA (column names in double quotes)
SELECT T0."ItemCode", T2."ItemName", T0."DistNumber" AS "SerialNumber", T0."MnfSerial", T0."GrntExp", T1."WhsCode" FROM OSRN T0 INNER JOIN OSRQ T1 ON T1."MdAbsEntry" = T0."AbsEntry" INNER JOIN OITM T2 ON T2."ItemCode" = T0."ItemCode" WHERE T1."Quantity" > 0 ORDER BY T0."ItemCode", T0."DistNumber"
Paste either version in Tools → Queries → Query Generator. Need the other dialect for your own query? Use the SQL to HANA converter.
Common pitfall: OSRN alone lists serials that were already sold
Every serial ever received stays in OSRN, including units that were delivered long ago. To see what is in stock, join OSRQ and filter Quantity > 0. Joining on ItemCode only, without SysNumber or MdAbsEntry, multiplies the rows, because one item has many serials. See Stock by warehouse query.
Ready-made queries that use OSRN
- Stock by warehouse: On hand, committed, ordered and available quantity per item and warehouse.
Questions about the OSRN table
Where is the serial number stored in SAP Business One?
In OSRN.DistNumber, one row per item and serial. The manufacturer serial is in OSRN.MnfSerial.
How do I find in which warehouse a serial number is?
Join OSRQ on OSRQ.MdAbsEntry = OSRN.AbsEntry and read OSRQ.WhsCode where OSRQ.Quantity > 0.
What do the OSRN Status values mean?
0 = available, 1 = unavailable, 2 = allocated.
Related tables
OSRQ (Serial Number Quantities (per warehouse)) · OITM (Items (item master data)) · OBTN (Batch Numbers (master data)) · search all SAP Business One tables
Sources: SAP Business One documentation and SDK (DI API database tables reference), SAP Help Portal.
Ready to get started?
Tell us what you need. You get a clear technical answer, a fixed scope and a price, usually within 24 hours.