The Oracle EBS Quirks Guide

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 suffix is a strong hint, not a rule: WSH_DELIVERY_DETAILS has no _ALL suffix but carries both ORG_ID and ORGANIZATION_ID. Check the columns, not the name.

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_ID striping — 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.

Customers ride an even deeper version of this two-org confusion — see #tca-layers.

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 SEGMENT1 alone — 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.

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_DATE is 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.

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 _VL views join base to translations using the database session language. They can resolve in an ordinary SQL session, but they return only that session’s language and do not provide a deterministic all-language extract. For physical CDC or multilingual landing, use the _B and _TL tables and filter LANGUAGE to 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.

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.

FND_LOOKUP_VALUES is 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. Treat every status column’s decode path as a fact to verify against your instance, never something to assume from another table’s pattern.

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 reverse naming trap also exists: WSH_DELIVERY_DETAILS carriesORG_ID without an _ALL suffix.

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 site per operating unit — VENDOR_SITE_ID is the surrogate key, while site identity and ORG_ID define the business scope. 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.

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 stores every hop separately; none can be inferred from the delivery-detail row alone. The assignment row already carries DELIVERY_ID, so a leg and stop trace can join directly from WSH_DELIVERY_ASSIGNMENTS to WSH_DELIVERY_LEGS, as the SQL above does. Add WSH_NEW_DELIVERIES when you need delivery-header attributes such as confirm date, carrier, or waybill: WSH_DELIVERY_DETAILS → WSH_DELIVERY_ASSIGNMENTS → WSH_NEW_DELIVERIES → WSH_DELIVERY_LEGS → WSH_TRIP_STOPS → WSH_TRIPS. Skip a required 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.

No OPEN_QUANTITY column 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_TYPE before 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.

Customers model as four layers, not one row. A party (HZ_PARTIES) is who — a person, organization, or group, with no commercial context yet. An account (HZ_CUST_ACCOUNTS) is the commercial relationship layered over a party — one party can carry several accounts. An account site (HZ_CUST_ACCT_SITES_ALL) binds an account to a party site (an address) within one operating unit. A site use (HZ_CUST_SITE_USES_ALL) is the business purpose that address serves for that account — ship-to, bill-to, statements. Parties, accounts, party sites (HZ_PARTY_SITES), and locations (HZ_LOCATIONS) are global; account sites and site uses are the operating-unit-striped layers (see #two-orgs).

This completes the naming trap from #two-orgs: OE_ORDER_HEADERS_ALL.SOLD_TO_ORG_ID is a customer ACCOUNT id, and SHIP_TO_ORG_ID / INVOICE_TO_ORG_ID are site-USE ids — none of the three is an organization, despite the column name every one of them shares.

Suppliers ride the same model. AP_SUPPLIERS carries a party id, and AP_SUPPLIER_SITES_ALL carries a party site and a location — one trading-community address store serves both sides of the relationship, customer and supplier alike.

Caution: a party is not a customer. Expect several accounts per party, and count accounts — not parties — for a customer count. Never join or dedupe on PARTY_NAME; it’s a display value, not unique.