Oracle EBS Reference Database
A searchable reference for Oracle E-Business Suite R12 tables, fields, and relationships. Built for supply chain and analytics practitioners extracting EBS data into a lakehouse — with a quirks guide for the two org columns (ORG_ID vs ORGANIZATION_ID), key flexfields, WHO columns, translation tables, and the on-hand grain that trip up every first EBS extract.
Free & ungated · R12.1 / 12.2 · Last verified August 2026
Coverage is building out module by module — 7 of 12 so far.
How this is scoped
- Scope: the supply-chain core an analyst reaches for most — foundation, item master, inventory, and now the full order-to-cash and procure-to-pay document flows (orders, shipping, purchasing, receiving) — building out module by module toward manufacturing, planning, customers, and financials.
- Verification: field names, keys, and structural facts are curated against the public eTRM mirror with corroboration from Oracle's R12 documentation; lookup ladders ship only when verified, and every description is original wording. Last verified August 2026.
- Built for analytics: relationships focus on the joins you'll actually write — header/line pairs, the ORGANIZATION_ID equality on every item-level join, language-filtered translation joins, and the FND lookup decode.
Frequently Referenced Tables
The workhorses of EBS supply-chain analytics — the item master, on-hand balances, and the foundation tables every other join resolves through.
- MTL_SYSTEM_ITEMS_BThe item master — one row per item per inventory organization, carrying identity (the SEGMENTn flexfield the user-visible item number concatenates from), status, unit of measure, and the control flags for every functional area; every balance, transaction, and order line joins back to it on INVENTORY_ITEM_ID + ORGANIZATION_ID
- MTL_ONHAND_QUANTITIES_DETAILThe on-hand balance detail — receipt-level slices of stock per item, org, subinventory, locator, lot, and revision, consumed in FIFO order as material issues
- MTL_MATERIAL_TRANSACTIONSThe material transaction ledger — one row per inventory movement or cost update, classified by transaction type, action, and source type, with quantity, date, and the document reference that caused it; the reconciliation backbone for every stock question
- OE_ORDER_LINES_ALLSales order lines at their most granular workflow unit — shipment splits, model options, included items, and configuration items each get their own row, individually statused and quantity-tracked through booking, shipping, and invoicing
- PO_HEADERS_ALLPurchasing document headers — one row per purchase order, agreement, quotation, or RFQ (seven document types share the table), with supplier, buyer, currency, approval status, and closure state; operating-unit striped
- WSH_DELIVERY_DETAILSThe atomic shipping line — one row per shippable unit of demand from order lines (or WIP/PO sources), plus container rows, tracking requested/picked/shipped quantities through pick release and ship confirm
- RCV_TRANSACTIONSThe receiving event ledger — one immutable row per receiving action (receive, inspect, transfer, deliver, correct, return), chained to its prior step by parent transaction id; the event-grain truth behind every receipt quantity
- MTL_PARAMETERSInventory organization parameters — one row per inventory org, holding the short org code, the master org it points to, and the org's costing method and locator/lot/serial control defaults; the org list every ORGANIZATION_ID resolves against
- MTL_LOT_NUMBERSThe lot master — definition, genealogy (parent lot), status, grade, and the origination/expiration/retest dates for every lot, keyed by item + organization + lot number
- MTL_RESERVATIONSFirm reservations tying a demand source (a sales order line, most commonly) to a supply source (on-hand or expected supply) for an item in an org, optionally pinned down to subinventory, locator, and lot
- GL_CODE_COMBINATIONSEvery chart-of-accounts segment combination (CCID) stored once — the account string behind each SEGMENTn set; inventory accounting distributions and every journal line reference accounts through CODE_COMBINATION_ID
- FND_LOOKUP_VALUESThe generic lookup table — every seeded and user-defined code list (lookup type + code) with its meaning and description, one row per code per language; the decode target for coded columns across every module (EBS's analog of JDE's UDC table)
The EBS quirks guide
The encoding and modeling traps that make a first EBS extract wrong in ways that look right. Each links to a worked Databricks SQL pattern.
Operating unit vs inventory org — two different org columns, and the #1 join bug in every first EBS extract.
Item numbers and account strings live in SEGMENT1..n columns whose meaning is configuration-defined.
LAST_UPDATE_DATE on every table — tempting watermark, bulk-stamped by batch jobs, not reliably indexed.
Base + translation table pairs; filter one LANGUAGE or rows multiply — and never extract the _VL views.
MTL_ONHAND_QUANTITIES_DETAIL rows are FIFO receipt slices — SUM them; a row is not a balance.
The universal decode hub for status and type codes — filter LANGUAGE, and never trust an unverified CASE ladder.
A landed _ALL table carries every operating unit's rows — MOAC security never filters a lakehouse extract.
Order numbers, PO numbers, and receipt numbers all repeat — the surrogate-key spine is the only safe join.
No OPEN_QUANTITY column exists anywhere — it's always ordered/shipped or received/cancelled arithmetic.
Browse by Module
Organized by functional area. Click a module to see its tables.
- FoundationLedgers, account combinations, the organization structure (business groups, operating units, inventory orgs), key-flexfield setup, and the generic FND lookup table every module decodes through9 tables
- Item & Product MasterThe item master and its satellites — descriptions, revisions, categories, cross-references, and units of measure — that every balance, order, and movement points back to9 tablesSupply chain
- InventoryInventory org parameters, subinventories and locators, on-hand balances, the material transaction ledger, lots, serials, reservations, supply/demand pictures, and move orders15 tablesSupply chain
- Order ManagementCustomer order headers and lines and their pricing and holds across the order-to-cash cycle10 tablesSupply chain
- Shipping & DeliveriesDelivery details, deliveries, and trip stops between order lines and the customer dock6 tablesSupply chain
- PurchasingPurchase orders, requisitions, and supplier records across the procure-to-pay cycle10 tablesSupply chain
- ReceivingReceipt headers, transactions, and the receiving supply between the supplier shipment and stores3 tablesSupply chain
Sources & further reading
Table structure and behavior are authoritative only at source. Use these references to verify before building on any claim in this reference.
- Oracle eTRM mirror (12.1.1 / 12.2.2) — the public column-level source this catalog's table structures are checked against.
- Oracle E-Business Suite R12.2 Documentation Web Library — the official guides behind module scoping and behavior claims.
- Oracle Inventory User's Guide (R12.2) — transactions, on-hand, lots and serials, move orders, the System Items flexfield, and the seeded item templates behind the verified ITEM_TYPE decode.
- EBS Concepts — Multiple Organization Architecture (R12.2) — the _ALL / ORG_ID striping model and the uninitialized-session caveat behind the #two-orgs quirk.
- EBS Developer's Guide — Record History (WHO) — the nine WHO audit columns and their concurrent-program linkage, the authority behind the #who-columns quirk.
- Order Management Implementation Manual — Line Statuses (R12.2) — the order and shipping status model behind the order-to-cash tables (status names; the stored codes are deliberately left undecoded).
- Oracle Purchasing User's Guide — Receiving (R12.2) — receipt source and destination semantics behind the receiving tables.
- Oracle Purchasing User's Guide — Document Control (R12.2) — the approval and closure status model behind the purchasing tables.