The Oracle EBS Quirks Guide
Oracle EBS models data in ways that make a first extract wrong in exactly the ways that look right — two different columns that share a nickname, identifiers that are configuration, not schema, audit stamps that tempt you as a watermark, translation pairs that multiply your rows, and balances that are really receipt slices. This guide is the shortcut past every one of those, with copy-ready Databricks SQL. Last verified August 2026.
ORG_ID vs ORGANIZATION_ID — two different orgs
EBS has two org concepts that share a nickname and nothing else. ORG_ID is the operating unit — the financial and transactional scope. Tables suffixed _ALL (order, purchasing, and receivables transaction tables) are striped by it. ORGANIZATION_ID is the inventory organization — the plant or warehouse. Item and inventory tables carry it instead.
The #1 join bug: filtering ORGANIZATION_ID with an operating-unit id, or the reverse. The query returns nothing, or worse, returns a plausible-looking wrong subset.
Inside an applications session, MOAC row-level security silently filters the _ALL synonyms to your operating units. A raw lakehouse extract has no session — a landed _ALL table carries every operating unit's rows. Filter explicitly.
Not every master is striped: the supplier master (AP_SUPPLIERS) is global — supplier sites (AP_SUPPLIER_SITES_ALL) are the ORG_ID-striped side (see #moac). And watch the naming trap: HR_ALL_ORGANIZATION_UNITS's _ALL means all business groups, not ORG_IDstriping — it's the master list of every org unit of every classification. Enumerate operating units through HR_OPERATING_UNITS and inventory orgs through ORG_ORGANIZATION_DEFINITIONS or MTL_PARAMETERS.
-- Map each inventory org to the operating unit it belongs to.
SELECT
ood.ORGANIZATION_ID,
ood.ORGANIZATION_CODE,
ood.OPERATING_UNIT -- the ORG_ID of the owning operating unit
FROM ORG_ORGANIZATION_DEFINITIONS ood;
-- Anchor item-level reads on ORGANIZATION_ID (the inventory org) — never on
-- ORG_ID, which is a different partition entirely.
SELECT
msi.INVENTORY_ITEM_ID,
msi.SEGMENT1 AS item_number,
ooh.PRIMARY_TRANSACTION_QUANTITY
FROM MTL_SYSTEM_ITEMS_B msi
JOIN MTL_ONHAND_QUANTITIES_DETAIL ooh
ON ooh.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND ooh.ORGANIZATION_ID = msi.ORGANIZATION_ID -- always both columns
WHERE msi.ORGANIZATION_ID = <inventory_org_id>;
-- Supplier data splits across the two shapes: the supplier master
-- (AP_SUPPLIERS) is global, not striped by either org column, but its
-- SITES (AP_SUPPLIER_SITES_ALL) are ORG_ID-striped — one site row per
-- operating unit. PO_VENDORS is a compatibility view over the supplier
-- master, not a table of its own — see #moac.1 parameter not filled: <inventory_org_id>
Key flexfields: SEGMENT1..n
The identifiers users see on screen are key-flexfield segment concatenations, stored as generic SEGMENT1…SEGMENTn columns whose meaning is defined in FND setup — not in the schema. The item number is the System Items flexfield over MTL_SYSTEM_ITEMS_B, most often SEGMENT1alone — but that's a configuration choice, not a law. The account string is the Accounting flexfield over GL_CODE_COMBINATIONS (SEGMENT1…SEGMENT30, chart-dependent). Categories and stock locators are flexfields too (MTL_CATEGORIES_B, MTL_ITEM_LOCATIONS).
Segment meanings differ per environment. Verify against your own flexfield setup before joining — never copy another site's segment mapping. Valid values and their descriptions live in FND_FLEX_VALUE_SETS and FND_FLEX_VALUES — the description lives on the translation side, not the base row.
-- The item number is the System Items key flexfield — SEGMENT1 in most
-- single-segment configurations, but verify against your own flexfield setup
-- before assuming that holds.
SELECT
msi.INVENTORY_ITEM_ID,
msi.SEGMENT1 AS item_number -- configuration-defined; don't copy this mapping blind
FROM MTL_SYSTEM_ITEMS_B msi
WHERE msi.ORGANIZATION_ID = <inventory_org_id>;
-- The accounting flexfield concatenates up to 30 segments — build the string
-- yourself; there is no single "account number" column.
SELECT
gcc.CODE_COMBINATION_ID,
CONCAT_WS('-', gcc.SEGMENT1, gcc.SEGMENT2, gcc.SEGMENT3, gcc.SEGMENT4) AS account_string
FROM GL_CODE_COMBINATIONS gcc;
-- Segment value descriptions live on the flexfield value tables, not on the
-- base row:
-- FND_FLEX_VALUE_SETS (one row per value set)
-- FND_FLEX_VALUES (the individual segment values within a set)1 parameter not filled: <inventory_org_id>
WHO columns: audit stamps on every table
Nearly every table carries CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN. Tables touched by concurrent programs add REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and PROGRAM_UPDATE_DATE.
LAST_UPDATE_DATEis the obvious incremental watermark, and it mostly works — but know its failure modes. It is not reliably indexed on transaction tables, so filtering on it can mean a full scan on a big extract. Batch processes bulk-stamp rows, so “changed since yesterday” can include rows with no real business change. And a hard delete never stamps anything, so a delete-only extract needs a different signal entirely.
The generated SQL on this site omits WHO columns from SELECT lists and ships LAST_UPDATE_DATE as an optional filter — pair it with a periodic full refresh rather than relying on it alone.
-- LAST_UPDATE_DATE is the obvious incremental watermark. Use it as an
-- optional filter, not a guarantee — batch jobs bulk-stamp rows, and a hard
-- delete never stamps anything.
SELECT
mmt.TRANSACTION_ID,
mmt.INVENTORY_ITEM_ID,
mmt.TRANSACTION_QUANTITY
FROM MTL_MATERIAL_TRANSACTIONS mmt
WHERE mmt.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>' -- optional; pair with a periodic full refresh
-- The generated SQL on this site omits WHO columns (CREATION_DATE,
-- CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and the
-- concurrent-program stamps REQUEST_ID / PROGRAM_ID / PROGRAM_APPLICATION_ID
-- / PROGRAM_UPDATE_DATE) from SELECT lists and ships the watermark as an
-- optional filter line instead.1 parameter not filled: <watermark>
_B / _TL / _V / _VL
Multilingual entities split into a _B base table (language-independent attributes) and a _TL translation table — one row per LANGUAGE, with SOURCE_LANG marking the original row a translation was derived from.
The _VLviews join base to translations filtered to the current session's language. They're session-dependent, so they don't exist for a lakehouse extract: land the _B and _TL tables and filter LANGUAGEto one code yourself — join a base row to every installed language's translation without that filter and every downstream row multiplies.
MTL_SYSTEM_ITEMS_B / MTL_SYSTEM_ITEMS_TL is the pair you'll hit first. FND_LOOKUP_VALUES is language-striped directly rather than split into a base and a _TL pair — its meanings simply repeat per language, so the same LANGUAGE filter applies there too.
-- Land the _B base table and its _TL companion separately, and filter
-- LANGUAGE yourself — the _VL views that do this filtering are session-
-- dependent and don't exist outside an applications session.
SELECT
b.INVENTORY_ITEM_ID,
b.SEGMENT1 AS item_number,
t.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_B b
JOIN MTL_SYSTEM_ITEMS_TL t
ON t.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID
AND t.ORGANIZATION_ID = b.ORGANIZATION_ID
AND t.LANGUAGE = 'US' -- one language code, or every row multiplies
WHERE b.ORGANIZATION_ID = <inventory_org_id>;
-- FND_LOOKUP_VALUES is language-striped directly — no separate base table.
SELECT
flv.LOOKUP_CODE,
flv.MEANING
FROM FND_LOOKUP_VALUES flv
WHERE flv.LOOKUP_TYPE = '<lookup_type>'
AND flv.LANGUAGE = 'US'2 parameters not filled: <inventory_org_id>, <lookup_type>
On-hand is receipt slices, not balances
MTL_ONHAND_QUANTITIES_DETAIL keeps FIFO receipt-level slices, not balances. One item, in one org, in one subinventory (and, where used, one locator, lot, or revision), can hold many rows — a row is a slice of history, not the current quantity.
SUM(PRIMARY_TRANSACTION_QUANTITY) at the grain your question actually needs — item × org × subinventory, adding locator, lot, or revision when the question calls for that level. DATE_RECEIVED orders the slices when you need oldest-first reasoning.
To reconcile a point-in-time balance rather than “as of now,” work from the transaction ledger, MTL_MATERIAL_TRANSACTIONS, instead — it carries the full movement history the on-hand table's current slices were built from.
-- One item/org/subinventory can hold many receipt-slice rows — sum them,
-- don't read a row as a balance.
SELECT
ooh.INVENTORY_ITEM_ID,
ooh.ORGANIZATION_ID,
ooh.SUBINVENTORY_CODE,
SUM(ooh.PRIMARY_TRANSACTION_QUANTITY) AS on_hand_qty
FROM MTL_ONHAND_QUANTITIES_DETAIL ooh
WHERE ooh.ORGANIZATION_ID = <inventory_org_id>
GROUP BY ooh.INVENTORY_ITEM_ID, ooh.ORGANIZATION_ID, ooh.SUBINVENTORY_CODE;
-- Lot-level variant — group by LOT_NUMBER too.
SELECT
ooh.INVENTORY_ITEM_ID,
ooh.ORGANIZATION_ID,
ooh.SUBINVENTORY_CODE,
ooh.LOT_NUMBER,
SUM(ooh.PRIMARY_TRANSACTION_QUANTITY) AS on_hand_qty
FROM MTL_ONHAND_QUANTITIES_DETAIL ooh
WHERE ooh.ORGANIZATION_ID = <inventory_org_id>
GROUP BY ooh.INVENTORY_ITEM_ID, ooh.ORGANIZATION_ID, ooh.SUBINVENTORY_CODE, ooh.LOT_NUMBER;
-- To reconcile a point-in-time balance, work from the transaction ledger
-- instead:
-- SELECT ... FROM MTL_MATERIAL_TRANSACTIONS WHERE TRANSACTION_DATE <= <as_of_date>2 parameters not filled: <inventory_org_id>, <as_of_date>
Decoding coded columns: FND_LOOKUP_VALUES
FND_LOOKUP_VALUESis the universal decode hub — EBS's answer to JDE's UDC table. Every seeded and user-defined code list lives here, keyed by LOOKUP_TYPE + LOOKUP_CODE + LANGUAGE (the full key adds VIEW_APPLICATION_ID and SECURITY_GROUP_ID, but that working key covers most decodes). Always filter LANGUAGE or a decode join multiplies rows. ENABLED_FLAG and date effectivity matter too — a code can exist and still be inactive.
Many ladders are site-extensible, so this reference never hardcodes a blog's guess at a CASE decode: it inlines only ladders verified against Oracle documentation and emits a pointer comment — like the one above — everywhere else.
Order and PO status codes are the canonical example. Oracle documents the status names, not the stored codes — and PO approval and closure statuses don't even decode through FND at all: Purchasing keeps its own lookup table for those. Treat every status column's decode path as a fact to verify against your instance, never something to assume from another table's pattern.
-- The worked decode every generated pointer comment on this site links back
-- to: MTL_SYSTEM_ITEMS_B.ITEM_TYPE via the ITEM_TYPE lookup type.
SELECT
msi.INVENTORY_ITEM_ID,
msi.SEGMENT1 AS item_number,
flv.MEANING AS item_type
FROM MTL_SYSTEM_ITEMS_B msi
LEFT JOIN FND_LOOKUP_VALUES flv
ON flv.LOOKUP_TYPE = 'ITEM_TYPE'
AND flv.LOOKUP_CODE = msi.ITEM_TYPE
AND flv.LANGUAGE = 'US' -- one language code, or every row multiplies
WHERE msi.ORGANIZATION_ID = <inventory_org_id>;
-- Generic template: swap in the lookup type a pointer comment names. This is
-- the order-line flow-status decode — LOOKUP_TYPE = 'LINE_FLOW_STATUS' is
-- Oracle-attested; most other status columns are not (verify before you copy
-- this pattern onto one).
SELECT
t.LINE_ID,
flv.MEANING AS flow_status
FROM <table> t
LEFT JOIN FND_LOOKUP_VALUES flv
ON flv.LOOKUP_TYPE = '<lookup_type>' -- e.g. 'LINE_FLOW_STATUS'
AND flv.LOOKUP_CODE = t.FLOW_STATUS_CODE
AND flv.LANGUAGE = 'US'
WHERE t.ORG_ID = <operating_unit_id>;4 parameters not filled: <inventory_org_id>, <table>, <lookup_type>, <operating_unit_id>
_ALL tables: MOAC striping in practice
With the order-to-cash and procure-to-pay tables in the catalog, the theory in #two-orgs becomes practice. A landed _ALL table carries every operating unit's rows — MOAC security filters only inside an applications session, never in a lakehouse extract. Filter ORG_ID explicitly in every query, and resolve it to a name through HR_OPERATING_UNITS.
Striping is uneven even within one product family: OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL are striped, but OE_PRICE_ADJUSTMENTS and OE_HOLD_DEFINITIONS are not — derive the operating unit through the order header when you need it there.
The supplier split is the teaching example. The supplier master (AP_SUPPLIERS) is global. Supplier sites (AP_SUPPLIER_SITES_ALL) carry one row per supplier per operating unit — that's where MOAC striping actually lands. PO_VENDORS is just a compatibility view over the supplier master, kept so the familiar name still resolves.
-- Order count per operating unit — a landed _ALL table carries every unit's
-- rows; MOAC security filters only inside an apps session, never an extract.
SELECT
hou.NAME AS operating_unit,
COUNT(*) AS order_count
FROM OE_ORDER_HEADERS_ALL ooh
JOIN HR_OPERATING_UNITS hou
ON hou.ORGANIZATION_ID = ooh.ORG_ID
GROUP BY hou.NAME;
-- The supplier split, worked: AP_SUPPLIERS is global; AP_SUPPLIER_SITES_ALL
-- carries one row per supplier per operating unit.
SELECT
s.VENDOR_ID, -- group on the id; names are display values, not keys
s.VENDOR_NAME,
COUNT(DISTINCT sites.ORG_ID) AS operating_unit_count
FROM AP_SUPPLIERS s
JOIN AP_SUPPLIER_SITES_ALL sites
ON sites.VENDOR_ID = s.VENDOR_ID
GROUP BY s.VENDOR_ID, s.VENDOR_NAME;Join on ids, not document numbers
Every join in this wave should run on the surrogate-key spine: HEADER_ID/LINE_ID on orders, DELIVERY_DETAIL_ID/DELIVERY_ID on shipping, the header, line, shipment-schedule, and distribution surrogate keys spanning PO_HEADERS_ALL down through PO_DISTRIBUTIONS_ALL on purchasing, and SHIPMENT_HEADER_ID/SHIPMENT_LINE_ID/TRANSACTION_ID on receiving.
Document numbers are display identifiers, not keys. ORDER_NUMBER is unique only together with type and version. A PO number repeats across operating units. RECEIPT_NUM repeats across receiving orgs. And on PO_HEADERS_ALL and PO_REQUISITION_HEADERS_ALL, SEGMENT1 is the document number — not a key flexfield, despite the column name (see #flexfields).
The shipping chain needs every hop: WSH_DELIVERY_DETAILS → WSH_DELIVERY_ASSIGNMENTS → WSH_NEW_DELIVERIES → WSH_DELIVERY_LEGS → WSH_TRIP_STOPS → WSH_TRIPS. Skip a hop and there's no path through. Drop-ship linkage runs through OE_DROP_SHIP_SOURCES, the only physical tie between order-to-cash and procure-to-pay.
-- The order-to-cash trace: order header -> line -> delivery detail ->
-- assignment -> delivery -> leg -> the pickup stop's actual departure.
SELECT
ooh.HEADER_ID,
ool.LINE_ID,
wdd.DELIVERY_DETAIL_ID,
wda.DELIVERY_ID,
wts.ACTUAL_DEPARTURE_DATE
FROM OE_ORDER_HEADERS_ALL ooh
JOIN OE_ORDER_LINES_ALL ool
ON ool.HEADER_ID = ooh.HEADER_ID
JOIN WSH_DELIVERY_DETAILS wdd
ON wdd.SOURCE_CODE = 'OE'
AND wdd.SOURCE_LINE_ID = ool.LINE_ID
JOIN WSH_DELIVERY_ASSIGNMENTS wda
ON wda.DELIVERY_DETAIL_ID = wdd.DELIVERY_DETAIL_ID
JOIN WSH_DELIVERY_LEGS wdl
ON wdl.DELIVERY_ID = wda.DELIVERY_ID
JOIN WSH_TRIP_STOPS wts
ON wts.STOP_ID = wdl.PICK_UP_STOP_ID
WHERE ooh.HEADER_ID = <header_id>;
-- Wrong way: looking up a PO by SEGMENT1 (the PO number) alone crosses
-- operating units, because the number repeats per OU. Pair it with ORG_ID
-- (and document type, if you're not already scoped to PO_HEADERS_ALL):
-- SELECT * FROM PO_HEADERS_ALL t
-- WHERE t.SEGMENT1 = '<po_number>' AND t.ORG_ID = <operating_unit_id>3 parameters not filled: <header_id>, <po_number>, <operating_unit_id>
Open quantities are arithmetic, not columns
No OPEN_QUANTITYcolumn exists anywhere in these flows — it's always arithmetic on running counters. On order lines, ORDERED_QUANTITY is already net of cancellations, so the open balance is ORDERED_QUANTITY − SHIPPED_QUANTITY; CANCELLED_QUANTITY is what was removed, and OPEN_FLAG is the cheap filter. On PO_LINE_LOCATIONS_ALL, it's QUANTITY − QUANTITY_RECEIVED − QUANTITY_CANCELLED — and QUANTITY_RECEIVED/QUANTITY_BILLED are running totals, not events.
The receipt-event truth lives in RCV_TRANSACTIONS, where one receipt writes multiple rows — a RECEIVE then a DELIVER, with corrections chained as separate signed rows through PARENT_TRANSACTION_ID. Filter TRANSACTION_TYPEbefore summing or received quantities double-count; the code values ship undecoded (see #lookups discipline above) — enumerate them from your own instance's lookup data.
Closure states (CLOSED_CODE) exist at header, line, and shipment level and are lookup codes, not enums — don't treat them as a fixed set without checking.
-- Open order-line quantity — arithmetic, not a column.
SELECT
ool.LINE_ID,
ool.ORDERED_QUANTITY - ool.SHIPPED_QUANTITY AS open_quantity
FROM OE_ORDER_LINES_ALL ool
WHERE ool.ORG_ID = <operating_unit_id>
AND ool.OPEN_FLAG = 'Y';
-- Open PO shipment quantity — the running counters on PO_LINE_LOCATIONS_ALL.
-- Filter price-break rows by SHIPMENT_TYPE (blanket price breaks share this
-- table with real shipments) before summing.
SELECT
pll.LINE_LOCATION_ID,
pll.QUANTITY - pll.QUANTITY_RECEIVED - pll.QUANTITY_CANCELLED AS open_quantity
FROM PO_LINE_LOCATIONS_ALL pll
WHERE pll.ORG_ID = <operating_unit_id>
AND pll.SHIPMENT_TYPE = '<shipment_type>' -- excludes price-break rows
AND pll.CANCEL_FLAG != 'Y';
-- Received quantity per receiving shipment line, from the event ledger — one
-- receipt is several rows; filter TRANSACTION_TYPE (e.g. the receive step)
-- before summing, or DELIVER rows double-count against RECEIVE rows.
SELECT
rt.SHIPMENT_LINE_ID,
SUM(rt.PRIMARY_QUANTITY) AS received_quantity
FROM RCV_TRANSACTIONS rt
WHERE rt.ORGANIZATION_ID = <inventory_org_id>
AND rt.TRANSACTION_TYPE = '<transaction_type>' -- e.g. the receive step
GROUP BY rt.SHIPMENT_LINE_ID;4 parameters not filled: <operating_unit_id>, <shipment_type>, <inventory_org_id>, <transaction_type>