SAPFree referencePDF, Excel, Word exportSAP Business OneAll SAP Business One tools

SAP Business One Item, Warehouse and Batch Columns Reference

This is a column-level reference for the SAP Business One tables behind items, warehouse stock, price lists and batch and serial tracking. It complements the table-level B1 tables reference and applies to SAP Business One 10.0 on SQL Server and SAP HANA.

Download:

Table	Column	Data meaning	Example	Notes
OITM	ItemCode	Item number (primary key)	A00001	HANA: "OITM"."ItemCode" in double quotes, case-sensitive.
OITM	ItemName	Item description	Hex bolt M8 x 30	Foreign name is FrgnName.
OITM	ItmsGrpCod	Item group code	101	Join OITB.ItmsGrpCod for ItmsGrpNam.
OITM	InvntItem	Inventory item flag	Y	N for service or non-stock items; they have no stock.
OITM	SellItem	Sales item flag	Y	Y or N.
OITM	PrchseItem	Purchase item flag	Y	Y or N.
OITM	OnHand	In stock, all warehouses	120	Sum of OITW.OnHand. Inventory UoM.
OITM	IsCommited	Committed by open sales orders and other demand, all warehouses	30	Spelled with one t: IsCommited.
OITM	OnOrder	Ordered from vendors or production, all warehouses	50	Open purchase orders and production orders.
OITM	EvalSystem	Valuation method	A	A moving average, S standard, F FIFO, B serial/batch.
OITM	AvgPrice	Item cost (company-level)	2.35	Use OITW.AvgPrice when warehouse-level costing is on.
OITM	ManBtchNum	Managed by batches	Y	Batch details in OBTN and OBTQ.
OITM	ManSerNum	Managed by serial numbers	N	Serial details in OSRN and OSRQ.
OITM	DfltWH	Default warehouse	01	May be empty; then the company default applies.
OITM	validFor / frozenFor	Active and inactive flags	Y / N	Combined with validFrom, validTo, frozenFrom, frozenTo dates.
OITM	InvntryUom	Inventory unit of measure text	EA	With UoM groups see UgpEntry and UGP1.
OITM	LastPurPrc	Last purchase price	2.40	LastPurCur holds its currency.
OITM	U_<name>	User-defined field	U_Grade	UDF definitions in CUFD.
OITW	ItemCode, WhsCode	Item and warehouse (key)	A00001, 01	One row per item and warehouse.
OITW	OnHand	In stock in this warehouse	80	Use OITW, not OITM, for stock by warehouse.
OITW	IsCommited	Committed in this warehouse	20	Same spelling as OITM.
OITW	OnOrder	Ordered into this warehouse	50	Available = OnHand - IsCommited + OnOrder.
OITW	AvgPrice	Warehouse item cost	2.31	Meaningful when costing by warehouse is enabled.
OITW	MinStock, MaxStock	Min and max inventory level	10, 200	Used by the inventory level alerts.
OITW	Locked	Warehouse locked for the item	N	Y blocks transactions for that item in that warehouse.
ITM1	ItemCode, PriceList	Item and price list number (key)	A00001, 1	One row per item per price list.
ITM1	Price, Currency	Price and its currency	4.50, USD	Join OPLN on PriceList = ListNum.
OPLN	ListNum, ListName	Price list number and name	1, Base Price	ListName is what users see.
OPLN	BASE_NUM, Factor	Base price list and factor	1, 1.2	Derived lists are base price times factor.
OINM	TransType, CreatedBy, BASE_REF	Object type, DocEntry and DocNum of the source document	15, 1234, 5001	Legacy inventory audit table. From 9.0 the valuation engine writes OIVL and IVL1; check how OINM is populated on your version.
OINM	ItemCode, Warehouse, DocDate, InQty, OutQty	Item, warehouse, posting date and quantity in or out	A00001, 01, 2026-10-01, 0, 5	Stock at a date = SUM(InQty - OutQty) up to that date.
OITL	LogEntry	Inventory transaction log key	8812	Header of the batch and serial log.
OITL	ItemCode, DocEntry, DocType, LocCode	Item, document entry, object type and warehouse	A00001, 77, 15, 01	DocType 15 is a delivery, 13 an A/R invoice, 20 a goods receipt PO.
OITL	DocQty, StockEff	Quantity and stock effect flag	-5, 1	StockEff 1 means the line affects stock.
ITL1	LogEntry, SysNumber, Quantity	Batch or serial line of the log entry	8812, 3, -5	Join ITL1.LogEntry = OITL.LogEntry.
ITL1	MdAbsEntry	Link to the batch or serial master record	412	Join OBTN.AbsEntry (batches) or OSRN.AbsEntry (serials).
OBTN	ItemCode, SysNumber, DistNumber	Item, internal number and batch number	A00001, 3, LOT-2610-07	DistNumber is the batch users see.
OBTN	ExpDate, MnfDate	Expiry and manufacturing date	2027-04-30, 2026-10-02	Use for FEFO and expiry reports.
OBTQ	ItemCode, WhsCode, SysNumber, Quantity	Batch quantity per warehouse	A00001, 01, 3, 45	Join OBTN on ItemCode and SysNumber, or MdAbsEntry = OBTN.AbsEntry.
OSRN	ItemCode, SysNumber, DistNumber	Serial number master	B10000, 15, SN-000915	MnfSerial holds the manufacturer serial.
OSRQ	ItemCode, WhsCode, SysNumber, Quantity	Serial quantity per warehouse (0 or 1)	B10000, 01, 15, 1	Quantity 1 means the serial is in that warehouse.
OBIN / OIBQ	AbsEntry, BinCode / ItemCode, BinAbs, OnHandQty	Bin locations and item quantity per bin	5, 01-A-01 / A00001, 5, 12	Only used when bin locations are enabled for the warehouse.

Column names follow the SAP Business One 10.0 SDK database table reference. On SAP HANA, table and column names are case-sensitive and must be in double quotes; on SQL Server they are not. Check the stock audit tables (OINM, OIVL) on your version before building valuation reports.

Get a stock query for your B1 database

Tell us what report you need and whether you run SQL Server or HANA, and we will send a tested query on these tables.

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

Was this tool useful?

How to use it

Search by table or column name. Each row gives the meaning, an example value and the trap to avoid. Columns are spelled exactly as in the database, including IsCommited with one t and ItmsGrpCod. On SQL Server write T0.ItemCode; on HANA write T0."ItemCode", because HANA keeps the mixed-case names and needs double quotes. To see the database column behind a field in the client, turn on View > System Information and hover over the field: the status bar shows table and column.

OITM: company-level totals per item.
OITW: the same quantities per warehouse.
OITL and ITL1: the batch and serial transaction log.

The availability formula

B1 shows Available as In Stock minus Committed plus Ordered, which in table terms is OnHand - IsCommited + OnOrder. Use OITW for a warehouse and OITM for the company total. Committed comes from open sales orders and other demand, and Ordered from open purchase orders and production orders. The formula is the same one the Inventory Status report and the item master availability use, so a query built on it should match the screen. If it does not, check for items with InvntItem = 'N', which carry no stock.

Worked example: batches with expiry by warehouse

To list batches on hand with expiry dates, join OBTQ to OBTN on ItemCode and SysNumber (or OBTQ.MdAbsEntry = OBTN.AbsEntry) and filter OBTQ.Quantity > 0. Add OITM for the item name and filter OITM.ManBtchNum = 'Y'. Sort by ExpDate for a FEFO list. To trace where a batch went, start from OBTN.AbsEntry, join ITL1 on MdAbsEntry, then OITL on LogEntry: OITL gives the DocType and DocEntry of each document, and StockEff = 1 limits the result to lines that moved stock.

Version differences and gotchas

OINM is the classic inventory audit table and many old reports use it. From release 9.0 the inventory valuation engine writes OIVL and IVL1, so confirm on your system how OINM is filled before you trust a valuation report built on it; quantities by date are usually still fine. OITM.AvgPrice is wrong for reporting when costing by warehouse is on: use OITW.AvgPrice. Bin tables OBIN and OIBQ are only filled for warehouses with bin locations enabled. User-defined fields carry the U_ prefix.

Frequently Asked Questions

What is the OITM table in SAP B1?

OITM is the item master data table. It holds the item code, name, group, inventory, sales and purchase flags, valuation method, batch and serial flags, and company-level stock totals OnHand, IsCommited and OnOrder. Stock per warehouse is in OITW.

What is the OINM table in SAP B1?

OINM is the inventory audit table: one row per stock movement with TransType (object type), CreatedBy (DocEntry), BASE_REF (DocNum), Warehouse, DocDate, InQty and OutQty. From 9.0 the valuation engine uses OIVL and IVL1, so check your version before using OINM for values.

What is the OITL table in SAP B1?

OITL is the inventory transaction log for batch and serial items: one row per document line with ItemCode, DocEntry, DocType, LocCode, DocQty and StockEff. ITL1 holds the batch or serial lines, linked to OBTN or OSRN through MdAbsEntry.

Which table stores price lists in SAP B1?

OPLN holds the price list header (ListNum, ListName, BASE_NUM, Factor). ITM1 holds the item prices with ItemCode, PriceList, Price and Currency. Join ITM1.PriceList = OPLN.ListNum.

If you want your team to ask SAP Business One stock and batch questions in plain English on your own server, Netray can set up that private AI layer.

Talk to an Expert Explore More Free Tools