EBS Reference
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 what a first extract gets wrong.
Free & ungated · R12.1 / 12.2 · Last verified September 2026
104
Tables
12
Modules
10
Quirks
How this is scoped
- Scope: the full supply-chain core — foundation, items, inventory, order-to-cash, procure-to-pay, manufacturing, planning, customers, and financials — with an extraction guide for landing it all in a lakehouse.
- 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 September 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
- BOM_STRUCTURES_BThe bill-of-material header — one bill per assembly item, organization, and alternate designator (manufacturing or engineering), with the common-bill pointer that lets organizations share one structure
- CST_ITEM_COSTSThe item cost header — one row per item, organization, and cost type, carrying the total unit cost and its five cost-element buckets plus this-level/previous-level splits
- WIP_DISCRETE_JOBSDiscrete job detail — status, start/completed/scrapped quantities, scheduled vs actual dates, accounting class, and the BOM/routing the job was built from; the workhorse of job-level manufacturing analytics
- MSC_SUPPLIESEvery supply row in a plan — existing purchase orders, jobs, on-hand, and in-transit alongside the planner's recommended planned orders; the supply side of ASCP plan output
- HZ_CUST_ACCOUNTSThe customer account — the commercial relationship layered over a party, and the id order headers actually carry as SOLD_TO_ORG_ID; one party, many accounts
- RA_CUSTOMER_TRX_ALLReceivables transaction headers — invoices, credit and debit memos, chargebacks, and deposits share the table, classified by transaction type; the revenue-document anchor of order-to-cash
- AP_INVOICES_ALLSupplier invoice headers — standard invoices, credit/debit memos, prepayments, and expense reports share the table by type, with amounts, payment status, and the liability account
- 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.
Read all 10 quirksGetting EBS into your lakehouse
The extraction guide covers the supported paths — Oracle GoldenGate log-based CDC, the Beta Lakeflow Connect Oracle CDC connector, Lakeflow Connect query-based ingestion (generally available), and Lakehouse Federation — plus batch JDBC watermark pulls, 12.2’s online-patching editioning layer, and an incremental MERGE pattern.
Read the extraction guideBrowse 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
- BOM & CostingBills of material, routings, and the item cost records manufacturing and margin analytics draw from12 tablesSupply chain
- Work in ProcessDiscrete jobs, material requirements, and operation records for shop-floor execution7 tablesSupply chain
- PlanningMRP/ASCP plan output — planned orders, recommendations, and the supply/demand entities behind them8 tablesSupply chain
- Customers (TCA)The trading community model — parties, customer accounts, and sites — that order and receivables records resolve to6 tables
- FinancialsReceivables, payables, and general-ledger journal records the supply-chain subledgers post into9 tables
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.
Show all 21 sources
Primary sources
- Oracle eTRM mirror (12.1.1 / 12.2.2) (opens in new tab) — the public column-level source this catalog's table structures are checked against.
- Oracle E-Business Suite eTRM User's Guide (R12.2) (opens in new tab) — Oracle's first-party description of the eTRM the community mirror above reproduces.
- Oracle E-Business Suite R12.2 Documentation Web Library (opens in new tab) — the official guides behind module scoping and behavior claims.
- Oracle Inventory User's Guide (R12.2) (opens in new tab) — 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) (opens in new tab) — the _ALL / ORG_ID striping model and the uninitialized-session caveat behind the #two-orgs quirk.
- EBS Developer's Guide — Record History (WHO) (opens in new tab) — 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) (opens in new tab) — 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) (opens in new tab) — receipt source and destination semantics behind the receiving tables.
- Oracle Purchasing User's Guide — Document Control (R12.2) (opens in new tab) — the approval and closure status model behind the purchasing tables.
- Oracle Bills of Material User's Guide (R12.2) (opens in new tab) — structures, components, routings, and effectivity semantics behind the BOM tables.
- Oracle Work in Process User's Guide (R12.2) (opens in new tab) — job statuses, shop-floor steps, and material control behind the WIP tables (status names; the numeric codes are deliberately left undecoded).
- Oracle MRP User's Guide (R12.2) (opens in new tab) — forecast consumption and schedule relief semantics behind the MRP tables.
- Oracle ASCP Implementation and User's Guide — Planner Workbench (R12.2) (opens in new tab) — plan output semantics behind the MSC tables: supply/demand, pegging, horizontal and vertical plans.
- Oracle Trading Community Architecture User Guide — Overview (R12.2) (opens in new tab) — the party/account/site model behind the customers module and the #tca-layers quirk, in Oracle's own definitions.
- Oracle Trading Community Architecture Technical Implementation Guide (R12.2) (opens in new tab) — the PL/SQL and Java API surface over the TCA customer model.
- EBS Concepts — Architecture (R12.2) (opens in new tab) — the database-tier description behind the extraction guide's base-table rule.
- EBS Concepts — Patching and Utilities (R12.2) (opens in new tab) — 12.2 online patching and the run/patch edition model behind the extraction guide.
- Oracle GoldenGate for Distributed Applications and Analytics — Databricks (opens in new tab) — the official GoldenGate Databricks target the extraction guide describes.
- Databricks Lakeflow Connect — Oracle CDC connector FAQ (opens in new tab) — the Beta Oracle connector and its EBS-as-supported-source statement.
- Databricks Lakeflow Connect — Query-based connectors (opens in new tab) — the scheduled query-based ingestion alternative, generally available since May 2026; its cursor limits are on the connector reference page cited in the extraction guide.
- Databricks Lakehouse Federation — Oracle (opens in new tab) — the governed federated-read path for an accessible EBS database.