How-toSAP Business OneDatabase / Reporting

SAP Business One tables list: the core tables and how the names work

Question

sap business one tables list

Also searched as

sap b1 table list
sap b1 oitm ordr table
sap business one database schema

Short answer

SAP Business One table names follow a pattern: O plus a short object name for header tables (OITM items, OCRD partners, ORDR sales orders), the same letters with a 1 for row tables (RDR1), and U_ prefixed columns or @ prefixed tables for user-defined objects. Read them for reports, but write only through the DI API or Service Layer.

Applies to: SAP Business One 10.0 on SQL Server and HANA. Column sets vary by version and localization; check the DI API reference.

Find the table you need

1In the client, open the form and turn on System Information (View > System Information), then hover over a field to see its table and field name.
2Open the DI API reference (Help Portal) for the object, which lists the table and fields for each business object.
3Query the metadata: INFORMATION_SCHEMA.COLUMNS on SQL Server, or SYS.TABLE_COLUMNS on HANA, for column names.
4Use SELECT with a TOP or LIMIT clause, never an unbounded query, in production hours.
5For reports, build a saved query or a Crystal layout. For external tools use a read-only database user or replica.

Core table list

Master data: OITM (items), OITW (item stock per warehouse), OCRD (business partners), OCRG (partner groups), OWHS (warehouses), OITB (item groups), OHEM (employees), OUSR (users). Sales: OQUT (quotations), ORDR (sales orders), ODLN (deliveries), OINV (A/R invoices), ORIN (credit memos). Purchasing: OPOR (purchase orders), OPDN (goods receipt PO), OPCH (A/P invoices). Each has a row table with the numeral 1: QUT1, RDR1, DLN1, INV1, POR1, PDN1, PCH1.

Production and inventory: OITT/ITT1 (BOM), OWOR/WOR1 (production orders), OIGN/IGN1 (goods receipt), OIGE/IGE1 (goods issue), OINM (inventory audit log). Finance: OJDT/JDT1 (journal entries), OACT (chart of accounts), OPRC (distribution rules). Metadata: CINF (company info), OADM (admin settings).

-- Top 10 items with stock and price list 1 price (SQL Server)

SELECT TOP 10 T0.ItemCode, T0.ItemName, T0.OnHand, T1.Price

FROM OITM T0 LEFT JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode AND T1.PriceList = 1

ORDER BY T0.OnHand DESC;

Naming conventions

A header table starts with O (for object): OITM, ORDR. Its rows live in a table where the O is dropped and a 1 is added: RDR1 for ORDR. Some objects have more tables with 2, 3 and so on for addresses, expenses or installments (RDR12 holds address data for sales orders, for example). Column names are typically CamelCase and keys like DocEntry (internal number) and DocNum (displayed number) are different: join on DocEntry, display DocNum.

User-defined fields are columns with a U_ prefix on the standard table, and user-defined tables appear with an @ prefix (shown as @TABLENAME). Add-ons often create their own tables with their own prefix. On HANA the schema is the company database name and identifiers are quoted in double quotes.

Safe use in reports and integrations

Reading is fine; use WITH (NOLOCK) on SQL Server only if you understand dirty reads, and prefer a replica or reporting copy for heavy queries. Never insert or update these tables yourself: posting rules, numbering, audit logs and linked tables would be bypassed and the SAP support position is clear on this.

For writes use the DI API or Service Layer, which validate the document and update all linked tables. For Service Layer questions see the related help pages.

Try it here, free

SAP B1 SQL Query Generator (OITM, ORDR, RDR1)

Open full tool

Fill in a few filters and get a read-only SQL query over OITM, ORDR and RDR1 to paste into the SAP B1 Query Manager.

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.

Common pitfalls

!Joining on DocNum instead of DocEntry.
!Writing to the tables directly and breaking numbering and the audit trail.
!Running unbounded queries on production during business hours.
!Forgetting the Canceled and DocStatus filters so cancelled documents appear in reports.
!Assuming column names are the same across versions and localizations.

How an ERP-grounded AI assistant handles this

Knowing table names is the barrier for most business users. A read-only assistant that already understands the Business One schema, such as ERPray, turns a question into a SQL query against these tables, shows the query, and runs it with the user's permissions. Keeping it on-prem means no schema or data leaves your servers.

See ERPray

Frequently asked questions

What does the O prefix mean in SAP Business One tables?

It marks the header or object table of a business object, such as OITM for items or ORDR for sales orders. The row table drops the O and adds 1, like RDR1 for sales order rows.

What is the difference between DocEntry and DocNum?

DocEntry is the internal unique key of a document, while DocNum is the number shown to users and may depend on the numbering series. Join tables on DocEntry and show DocNum on reports.

Can I update Business One tables with SQL?

You should not. Direct updates bypass validation, numbering and logging and can corrupt data and void support. Use the DI API or the Service Layer for writes.

How do I find the table behind a screen?

Turn on System Information in the View menu and hover over a field to see the table and field name, or check the DI API reference for the business object.

How-to

SAP Business One Query: Released Production Orders with Component Shortages

Join OWOR (production order header), WOR1 (components) and OITW (stock per warehouse) to see, for every released production order, how much of each component is still to be issued and whether the warehouse can cover it. Run it in Tools > Queries > Query Manager, using double-quoted identifiers on HANA and the same query with minor syntax changes on SQL Server.

Error fix

Fix SAP Business One Service Layer 'Invalid session' (code 301)

The Service Layer returns code 301 'Invalid session or session already timed out' when the B1SESSION cookie you send no longer maps to a live session. The usual causes are the default idle timeout (30 minutes), a missing or altered cookie, a load balancer sending the call to a different node because ROUTEID was dropped, or a restarted Service Layer. Fix it by keeping cookies on every call and re-logging in once on a 301.

How-to

SAP SE16N vs SQVI: Table Browser vs Ad Hoc Query

SE16N is a single-table browser: fast filtering, formatted display, and export from one table at a time, with an optional edit function for authorized users. SQVI (Quick Viewer) builds ad hoc queries that join multiple tables without writing ABAP, and can be saved as a reusable report. Use SE16N for a quick look at one table; use SQVI when you need data joined across two or more tables.

Error fix

SAP M7 021: Deficit of SL Stock Quantity

M7 021 fires during a goods issue, delivery, or confirmation when SAP checks the unrestricted-use (SL) stock for the exact plant, storage location and batch combination and finds less than what you are trying to post out. Fix the entry (batch, storage location, quantity) first; only enable negative stock or downgrade the message to a warning if the business genuinely needs to post ahead of a receipt.

Error fix

SAP IDoc Status 51: Application Document Not Posted

Status 51 tells you the IDoc reached the application layer and failed to post, but the status text itself is not the error - the real cause sits in the status record's long text or the linked application log. Fix the underlying data or configuration, then reprocess the IDoc through BD87 rather than editing the status.

Error fix

SAP Short Dump TIME_OUT: Maximum Runtime Exceeded

A TIME_OUT runtime error means a dialog (or RFC) work process ran longer than the maximum allowed runtime, controlled by profile parameter rdisp/max_wprun_time (600 seconds by default). The fix is almost always to move the long-running work to background processing or tune the underlying program, not to raise the timeout.

AI for ERP

AI for SAP Business One, Without Sending Your Books to a Public API

Add AI to SAP Business One without sending customer data or financials to a public API. Service Layer and DI API integration, sized for an SMB budget.

AI for ERP

A Private LLM Grounded on Your ERP Data

How a private LLM answers questions on your ERP data: RAG plus text-to-SQL, role-based permissions inherited from the ERP, and where each fits.

Stuck on SAP Business One?

Talk to engineers who work inside SAP Business One every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineer Browse ERP Help

All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

AI on your ERP

Integrations

Comparisons

Infor SyteLine vs SAP Business One for Small and Mid-Size ManufacturersInfor SyteLine vs SAP Business One: compare manufacturing depth, cost structure, partner ecosystem and upgrade path for small and mid-size discrete plants.

Software

SAP Business OneSAP Business One explained for small manufacturers: modules, Service Layer and DI API, deployment, limits, alternatives and where AI helps on top of it.

SAP Business One tables list: the core tables and how the names work

Find the table you need

Core table list

Naming conventions

Safe use in reports and integrations

Fill in

1. Stock on hand by item group (OITM)

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

3. Open demand against stock, by item

Tired of hand-writing B1 queries?

Common pitfalls

How an ERP-grounded AI assistant handles this

Frequently asked questions

Related

SAP Business One Query: Released Production Orders with Component Shortages

Fix SAP Business One Service Layer 'Invalid session' (code 301)

SAP SE16N vs SQVI: Table Browser vs Ad Hoc Query

SAP M7 021: Deficit of SL Stock Quantity

SAP IDoc Status 51: Application Document Not Posted

SAP Short Dump TIME_OUT: Maximum Runtime Exceeded

AI for SAP Business One, Without Sending Your Books to a Public API

A Private LLM Grounded on Your ERP Data

Stuck on SAP Business One?

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

AI on your ERP

Integrations

Comparisons

Software

Fill in

1. Stock on hand by item group (OITM)

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

3. Open demand against stock, by item

Tired of hand-writing B1 queries?