The Oracle Fusion Cloud Quirks Guide
Fusion Cloud looks familiar to anyone who knows Oracle EBS — and that familiarity is exactly what makes a first extract wrong. The database is real but unreachable by SQL, the extract files carry different column names than the documentation, a column that meant one thing in EBS means another here, and the org master multiplies on date-effectivity. This guide is the shortcut past every one of those, with copy-ready Databricks SQL. Last verified September 2026.
No SQL path to the database
Fusion Cloud is SaaS. The tables in this reference are real, documented objects — but Oracle does not hand you a connection string. Every row that reaches your lakehouse travels through a delivered surface: BICC (the BI Cloud Connector — scheduled bulk extracts of public view objects to object storage), the Data Extraction tool (Oracle’s newer bulk path off a replicated read-optimized store — it speaks business objects and extraction views, not PVOs, so this reference’s extract map covers BICC only), OTBI (real-time subject-area queries), Analytics Publisher (reporting; Oracle marks direct bulk extraction unsupported, with a custom ESS BIPJobType job as the documented exception), or REST APIs.
Existing BICC estates still extract a PVO (public view object) on a schedule, full or incremental, and lands files you convert to Delta. That is why every table page here carries an Extract access panel naming the PVO and OTBI subject areas that reach it — the table name alone is not an access path. For new bulk-extraction designs, Oracle’s current guidance directs teams to the Data Extraction tool; use Oracle’s BICC-to-business-object mapping when migrating rather than treating this BICC map as a DET crosswalk.
The flip side: a documented table with no documented PVO — 5 tables in the current catalog — has no mapped BICC path here. Plan on OTBI, another supported grain, or Oracle’s narrowly documented Publisher/ESS exception, and treat each gap as a design input.
3 parameters not filled: <catalog>, <schema>, <inventory_org_id>
-- Everything on this site reads the LANDED copy of a table — the Delta (or
-- CSV-then-Delta) output of a BICC extract in your own lakehouse. There is
-- no connection string to the Fusion database itself.
SELECT
t.TRANSACTION_ID,
t.INVENTORY_ITEM_ID,
t.ORGANIZATION_ID,
t.PRIMARY_QUANTITY,
t.TRANSACTION_DATE
FROM <catalog>.<schema>.INV_MATERIAL_TXNS t
WHERE t.ORGANIZATION_ID = <inventory_org_id>;
-- The five delivered ways data leaves Fusion Cloud:
-- BICC - bulk extracts of public view objects (PVOs) to object storage;
-- what this reference's extract map covers
-- DET - the Data Extraction tool, Oracle's newer bulk path off a
-- replicated read-optimized store; business objects and
-- extraction views, NOT PVOs - the extract map is BICC-only
-- OTBI - real-time subject-area queries; operational reporting, not bulk
-- BIP - reporting only; direct bulk extraction is unsupported. If BICC
-- or REST cannot work, Oracle documents a custom ESS BIPJobType job
-- REST - transactional APIs; integration, not analytics
-- If a table has no documented PVO, its Extract access panel is absent here
-- and OTBI or another supported grain is the fallback. Publisher is not an
-- unrestricted bulk path; use Oracle's ESS exception pattern only when needed.BICC headers are PVO attributes, not columns
A BICC extract file is shaped by the ViewObject, not the table: headers arrive as camel-case attribute names (InventoryItemId), sometimes prefixed by their entity object, while Oracle’s Tables and Views documentation — and every snippet on this site — speaks in column names (INVENTORY_ITEM_ID).
Map the names back once, at the bronze-to-silver boundary, and do it from the data store’s documented attribute list rather than by de-camel-casing on instinct — a PVO can rename, prefix, or join in attributes that have no counterpart on the base table.
Every generated SQL header on this site carries the reminder: if your landed data still shows PVO attribute headers, map names first.
2 parameters not filled: <catalog>, <schema>
-- BICC extracts are named and shaped by the PVO, not the table. The file for
-- FscmTopModelAM.ScmExtractAM.InvBiccExtractAM.InventoryOnhandExtractPVO
-- arrives with ViewObject ATTRIBUTE headers - InventoryItemId,
-- OrganizationId, PrimaryTransactionQuantity - not the table's column names.
-- Map names back to the documented columns once, in bronze-to-silver, so
-- everything downstream (including this site's boilerplate SQL) matches
-- Oracle's table documentation.
CREATE OR REPLACE VIEW <catalog>.<schema>.INV_ONHAND_QUANTITIES_DETAIL AS
SELECT
OnhandQuantitiesId AS ONHAND_QUANTITIES_ID,
InventoryItemId AS INVENTORY_ITEM_ID,
OrganizationId AS ORGANIZATION_ID,
SubinventoryCode AS SUBINVENTORY_CODE,
PrimaryTransactionQuantity AS PRIMARY_TRANSACTION_QUANTITY,
DateReceived AS DATE_RECEIVED
FROM <catalog>.<schema>.inventory_onhand_extract_raw;
-- Two more drift traps:
-- 1. Some PVO attributes are prefixed by their entity object (a PK can
-- arrive as InvOnhandQuantityPEOOnhandQuantitiesId) - check the data
-- store's attribute list, not just camel-case intuition.
-- 2. A PVO can join several tables - extra attributes in the file are not
-- extra columns on the base table._ALL now means business unit
EBS practitioners know ORG_ID as the operating unit. Fusion kept the column name and the _ALL table suffix but changed the concept underneath: the stripe is now the business unit. A migrated filter list, a copied join, or an old runbook that says “operating unit” will type-check perfectly and filter wrongly.
The shift shows up in two shapes. Order management carries the stripe literally: DOO_HEADERS_ALL, DOO_LINES_ALL, and DOO_FULFILL_LINES_ALL (and the shipment line table) have an ORG_ID documented as the business unit, and the generated SQL anchors it to a <business_unit_id> placeholder. Procurement dropped the pattern entirely: PO_HEADERS_ALL and its family stripe by named BU columns — PRC_BU_ID (procurement BU), REQ_BU_ID (requisitioning BU) — with no ORG_ID at all.
Either way, the id domain is BU_ID from FUN_ALL_BUSINESS_UNITS_V — reading these columns as EBS operating units is the migration’s most durable habit, and it is wrong here. The foundation tables map inventory orgs to BUs through BUSINESS_UNIT_ID on the org parameters.
1 parameter not filled: <business_unit_id>
-- In EBS, ORG_ID meant the operating unit. In Fusion the same column name
-- (and the _ALL suffix on the tables that carry it) means the BUSINESS UNIT.
-- Same name, different concept - migrated filter lists do not carry over.
--
-- The order-management tables carry the stripe literally - anchor it:
SELECT
ord.ORDER_NUMBER,
ord.STATUS_CODE,
ord.ORDERED_DATE
FROM DOO_HEADERS_ALL ord
WHERE ord.ORG_ID = <business_unit_id> -- a BU_ID, never an EBS operating-unit id
AND ord.SUBMITTED_FLAG = 'Y';
-- The BU id domain comes from the business-units view:
SELECT
bu.BU_ID,
bu.BU_NAME,
bu.STATUS
FROM FUN_ALL_BUSINESS_UNITS_V bu;
-- Procurement makes the same shift a different way: no ORG_ID at all -
-- NAMED BU columns instead (PRC_BU_ID procurement BU, REQ_BU_ID
-- requisitioning BU). Filter the one you mean:
SELECT
po.SEGMENT1 AS po_number, -- unique only with the document type plus
-- SOLDTO_LE_ID (STANDARD orders) or PRC_BU_ID
-- (other types); join on the header id
po.DOCUMENT_STATUS
FROM PO_HEADERS_ALL po
WHERE po.PRC_BU_ID = <business_unit_id>;
-- And the inventory foundation maps orgs to BUs through a named column too:
SELECT
iop.ORGANIZATION_ID,
iop.ORGANIZATION_CODE,
bu.BU_NAME
FROM INV_ORG_PARAMETERS iop
JOIN FUN_ALL_BUSINESS_UNITS_V bu
ON bu.BU_ID = iop.BUSINESS_UNIT_ID;Item-org striping and the master org
The item master keeps EBS’s shape: one row per item per inventory organization (ORGANIZATION_ID), with a master-org row holding the definition and child-org rows carrying overrides. Which org is the master is configuration — it lives in INV_ORG_PARAMETERS.MASTER_ORGANIZATION_ID, not on the item. Every item-level join carries both INVENTORY_ITEM_ID and ORGANIZATION_ID, always.
What changed in the migration: the item number left the key flexfield for a real ITEM_NUMBER column, and the description left the base table entirely — it exists only on EGP_SYSTEM_ITEMS_TL, so any readable item list is a language-filtered join. Extended and user-defined attributes live in the EGO extensible-flexfield tables, driven by the item class — a whole product family this catalog doesn’t cover yet.
One striping surprise inside the same product: item structures (EGP_STRUCTURES_B, EGP_COMPONENTS_B) carry no item or org id columns at all — the linkage hides in text-typed object/key columns, which is why this reference draws no structure-to-item join edge.
2 parameters not filled: <language>, <inventory_org_id>
-- Items repeat per inventory organization: the master-org row is the
-- definition, child-org rows carry org-level overrides. WHICH org is the
-- master is configuration - read it from the org parameters, not the item.
SELECT
iop.ORGANIZATION_ID,
iop.ORGANIZATION_CODE,
iop.MASTER_ORGANIZATION_ID -- the item-master org this org points to
FROM INV_ORG_PARAMETERS iop;
-- Every item-level join carries BOTH columns - the item id alone fans out
-- across every org that holds the item:
SELECT
itm.ITEM_NUMBER, -- a real column in Fusion, not a flexfield
tl.DESCRIPTION,
ohd.PRIMARY_TRANSACTION_QUANTITY
FROM EGP_SYSTEM_ITEMS_B itm
JOIN EGP_SYSTEM_ITEMS_TL tl
ON tl.INVENTORY_ITEM_ID = itm.INVENTORY_ITEM_ID
AND tl.ORGANIZATION_ID = itm.ORGANIZATION_ID
AND tl.LANGUAGE = '<language>' -- one language or rows multiply
JOIN INV_ONHAND_QUANTITIES_DETAIL ohd
ON ohd.INVENTORY_ITEM_ID = itm.INVENTORY_ITEM_ID
AND ohd.ORGANIZATION_ID = itm.ORGANIZATION_ID -- always both columns
WHERE itm.ORGANIZATION_ID = <inventory_org_id>;
-- What moved where in the migration: the item number left the key flexfield
-- for a real ITEM_NUMBER column, the description left the base table for the
-- _TL companion, and extended/user-defined attributes live in the EGO
-- extensible-flexfield tables (item-class driven - not yet cataloged here).Date-effective _F tables
Tables suffixed _F are date-effective: one row per entity per effectivity window, with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE in the primary key. The org master this wave catalogs (HR_ALL_ORGANIZATION_UNITS_F) is the canonical example — an org renamed twice carries three rows, and an unfiltered join triples every fact it touches.
Filter to the current row for state-of-today reporting, or pin the window to the fact’s own date for as-of history — the snippet shows both. The generated SQL on this site emits the current-row anchor as an active filter on every table this reference flags as date-effective, so the default copy-paste is safe there. A few cataloged tables carry Oracle effectivity columns this catalog does not yet author; their table notes say so, and on those you pin the window yourself.
Watch the double multiplication: the date-effective org master’s name lives on a translation companion that is itself date-effective and language-striped — a name lookup that forgets either filter multiplies twice.
1 parameter not filled: <inventory_org_id>
-- _F tables are date-effective: one row per entity per effectivity window,
-- and the window dates are part of the primary key. Joining without a window
-- filter multiplies rows by history.
SELECT
org.ORGANIZATION_ID,
org.ORGANIZATION_CODE,
org.LEGAL_ENTITY_ID
FROM HR_ALL_ORGANIZATION_UNITS_F org
WHERE CURRENT_DATE BETWEEN org.EFFECTIVE_START_DATE AND org.EFFECTIVE_END_DATE;
-- As-of joins pin the window to the fact's date instead of today:
SELECT
txn.TRANSACTION_ID,
txn.TRANSACTION_DATE,
org.ORGANIZATION_CODE
FROM INV_MATERIAL_TXNS txn
JOIN HR_ALL_ORGANIZATION_UNITS_F org
ON org.ORGANIZATION_ID = txn.ORGANIZATION_ID
AND txn.TRANSACTION_DATE BETWEEN org.EFFECTIVE_START_DATE
AND org.EFFECTIVE_END_DATE
WHERE txn.ORGANIZATION_ID = <inventory_org_id>;
-- The generated SQL on this site emits the CURRENT_DATE window anchor as an
-- ACTIVE filter on every table this reference flags as date-effective - swap
-- it for the as-of pattern when you need history. A few cataloged tables carry
-- Oracle effectivity columns the catalog does not author; their table notes
-- say so, and there you pin the window yourself. Note the org NAME is not on
-- this table; it lives on the date-effective translation companion, so a name
-- lookup filters both the window and LANGUAGE.Reference data sets (SET_ID)
Fusion partitions reference data with a mechanism EBS never had: reference data sets. A set is a bucket of reference rows; business units subscribe to a set per reference-data object, and a seeded enterprise-wide set carries shared defaults. Tables under this regime carry a SET_ID column — often inside their unique key.
The migration trap hides in the naming: the TCA account-site tables (HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL) keep their EBS _ALL suffix but carry no ORG_ID — the operating-unit stripe became SET_ID. A migrated filter list expecting an org column finds nothing, and an extract that ignores the set column can double-load codes that exist in several sets.
Where you’ll meet it in this catalog: the FND lookup view’s full key carries SET_ID (set-enabled lookup types resolve codes per set at runtime, via a determinant), cost elements are unique per set + code, and the two TCA tables above. For most decodes you can work at LOOKUP_TYPE + LOOKUP_CODE + LANGUAGE — but when a “duplicate” code appears, the set stripe is why.
2 parameters not filled: <lookup_type>, <language>
-- Reference data in Fusion stripes by SET_ID (reference data set), not by
-- business unit. BUs subscribe to a set per reference-data object, and a
-- seeded enterprise-wide set carries the defaults - so a set-striped table
-- holds rows for EVERY set, and ignoring the column multiplies or mismatches.
--
-- The TCA _ALL tables are the migration trap: the EBS operating-unit stripe
-- became SET_ID, and there is no ORG_ID on them at all.
SELECT
site.CUST_ACCT_SITE_ID,
site.CUST_ACCOUNT_ID,
site.SET_ID, -- reference data set, NOT an operating unit
su.SITE_USE_CODE
FROM HZ_CUST_ACCT_SITES_ALL site
JOIN HZ_CUST_SITE_USES_ALL su
ON su.CUST_ACCT_SITE_ID = site.CUST_ACCT_SITE_ID;
-- The FND lookup view carries the same stripe in its full key. Most decodes
-- can ignore it - but a code that seems duplicated is set-striped:
SELECT
flv.LOOKUP_TYPE,
flv.LOOKUP_CODE,
flv.SET_ID,
flv.MEANING
FROM FND_LOOKUP_VALUES flv
WHERE flv.LOOKUP_TYPE = '<lookup_type>'
AND flv.LANGUAGE = '<language>';
-- Cost elements stripe the same way - their unique key is SET_ID + code:
SELECT
ce.COST_ELEMENT_ID,
ce.SET_ID,
ce.COST_ELEMENT_CODE
FROM CST_COST_ELEMENTS_B ce;User-definable status rows
EBS status columns were fixed code lists you could memorize. Fusion frequently replaces them with user-definable status rows: manufacturing work orders point at a statuses dimension where each site-defined status maps to a seeded system status (unreleased, released, on hold, completed, closed). Two sites — or the same site a year apart — can hold different status rows for the same system state.
The modeling consequence: never hard-code work-order status literals. Land the statuses dimension next to the work orders and group by its system-status column — that’s the level that is stable across sites and releases. The same seeded-plus-user pattern appears in inventory, where site-defined transaction types share the type table with seeded ones behind USER_DEFINED_FLAG.
Order management is the contrasting shape: there is no status table at all. Configurable status rules compute the STATUS_CODE values on orders and fulfillment lines, so the value list itself is site-specific configuration — treat any dashboard status mapping as something to verify per implementation, not copy from a reference.
2 parameters not filled: <inventory_org_id>, <business_unit_id>
-- EBS status columns held fixed code lists. Fusion often replaces them with
-- USER-DEFINABLE STATUS ROWS: the work order's status is a pointer into a
-- statuses dimension (not cataloged here) where each site-defined row maps
-- to a seeded SYSTEM status - filtering literal codes misses rows.
SELECT
wo.WORK_ORDER_NUMBER,
wo.WORK_ORDER_STATUS_ID -- a row in a statuses dimension, not a code
FROM WIE_WORK_ORDERS_B wo
WHERE wo.ORGANIZATION_ID = <inventory_org_id>;
-- Land the statuses dimension alongside and group by its SYSTEM status
-- column - the user-defined layer is site-specific and multiplies per site.
-- Inventory repeats the pattern one level down: seeded and user-defined
-- transaction types share one table, flagged apart:
SELECT
tt.TRANSACTION_TYPE_ID,
tt.USER_DEFINED_FLAG -- 'Y' marks site-defined types
FROM INV_TRANSACTION_TYPES_B tt;
-- Order management is the CONTRASTING pattern: no status table at all.
-- STATUS_CODE values come from configurable status rules, so treat the
-- code list as site-specific configuration, not a fixed enum:
SELECT
fl.FULFILL_LINE_ID,
fl.STATUS_CODE
FROM DOO_FULFILL_LINES_ALL fl
WHERE fl.ORG_ID = <business_unit_id>;Counters vs event ledgers
Fusion stores quantity truth twice, in two shapes: maintained counters — balance columns the applications update in place — and event ledgers — insert-only tables where corrections write new rows. Every procure-to-pay and shop-floor pipeline eventually confuses the two, and the result is silent double-counting.
The PO schedule is the canonical counter: its received, accepted, rejected, and billed quantity buckets are running totals maintained by Receiving and Payables. The receiving transaction ledger is the canonical event store: the dictionary itself guarantees a row is never updated after insert — corrections chain to their parent row instead. Summing counter columns over time series, or counting ledger rows without netting reversals, both overstate.
The rule: report state from counters, report history from ledgers, and reconcile one against the other before trusting either. Work orders repeat the pattern (summary quantity columns on operations, events in the two shop-floor ledgers), and so do fulfillment lines with their _QTY counters.
1 parameter not filled: <inventory_org_id>
-- Fusion keeps TWO kinds of quantity truth side by side: maintained
-- COUNTERS (balances updated in place) and EVENT LEDGERS (insert-only
-- rows). Summing a counter over time double-counts; counting ledger rows
-- without netting reversals overstates.
--
-- Counters: the PO schedule's buckets are running totals maintained by
-- Receiving and Payables - balances as of now, not events:
SELECT
pll.LINE_LOCATION_ID,
pll.QUANTITY,
pll.QUANTITY_RECEIVED, -- maintained counter ("until today")
pll.QUANTITY_BILLED -- maintained by Payables invoice matching
FROM PO_LINE_LOCATIONS_ALL pll;
-- Events: the receiving ledger is insert-only - a correction writes a NEW
-- row chained by PARENT_TRANSACTION_ID; the original row never changes:
SELECT
rt.TRANSACTION_ID,
rt.TRANSACTION_TYPE,
rt.PARENT_TRANSACTION_ID,
rt.QUANTITY
FROM RCV_TRANSACTIONS rt
WHERE rt.ORGANIZATION_ID = <inventory_org_id>;
-- The same split runs everywhere: work order operations carry COMPLETED /
-- SCRAPPED summary counters while events live in the two shop-floor
-- ledgers, and fulfillment lines carry FULFILLED_QTY / SHIPPED_QTY
-- counters. Pick the grain deliberately, and reconcile counter to ledger
-- before trusting either.Customers are four layers deep (TCA)
Fusion keeps the EBS trading-community model nearly intact: a party (the real entity) carries customer accounts (commercial relationships), accounts have account sites (an account’s use of an address), and sites carry site uses (bill-to, ship-to, statements). Documents point at the bottom of the stack: AR transactions carry account ids and site-use ids — a readable customer name is always a multi-hop join up to the party.
The address itself rides a parallel spine — party site → location — so one physical address is stored once and shared. Two traps carry over from EBS: account ids are not party ids (one party can hold several accounts), and the *_SITE_USE_ID columns on documents are TCA ids, not organizations.
One Fusion twist worth knowing: suppliers live in the same model. The supplier master carries no name column at all — the supplier name lives on its TCA party, which is why this catalog’s supplier pages steer every name lookup through PARTY_ID. And the striping quirk above (#set-id) applies to the two account-site layers, which are set-striped, not BU-striped.
1 parameter not filled: <business_unit_id>
-- Fusion customers are four layers deep: party -> customer account ->
-- account site -> site use. Documents point at the BOTTOM layers - the
-- account and its site uses - never at the party directly.
SELECT
p.PARTY_NAME,
ca.ACCOUNT_NUMBER,
site.CUST_ACCT_SITE_ID,
su.SITE_USE_CODE
FROM HZ_PARTIES p
JOIN HZ_CUST_ACCOUNTS ca
ON ca.PARTY_ID = p.PARTY_ID
JOIN HZ_CUST_ACCT_SITES_ALL site
ON site.CUST_ACCOUNT_ID = ca.CUST_ACCOUNT_ID
JOIN HZ_CUST_SITE_USES_ALL su
ON su.CUST_ACCT_SITE_ID = site.CUST_ACCT_SITE_ID;
-- The address rides a parallel spine: party site -> location.
SELECT
ps.PARTY_SITE_ID,
loc.ADDRESS1,
loc.CITY,
loc.COUNTRY
FROM HZ_PARTY_SITES ps
JOIN HZ_LOCATIONS loc
ON loc.LOCATION_ID = ps.LOCATION_ID;
-- Resolving an invoice to a readable customer name crosses the layers:
SELECT
trx.TRX_NUMBER,
p.PARTY_NAME
FROM RA_CUSTOMER_TRX_ALL trx
JOIN HZ_CUST_ACCOUNTS ca
ON ca.CUST_ACCOUNT_ID = trx.BILL_TO_CUSTOMER_ID
JOIN HZ_PARTIES p
ON p.PARTY_ID = ca.PARTY_ID
WHERE trx.ORG_ID = <business_unit_id>;
-- Suppliers share the top of the model: the supplier master has NO name
-- column - the supplier name lives on its party.
SELECT
s.VENDOR_ID,
p.PARTY_NAME AS supplier_name
FROM POZ_SUPPLIERS s
JOIN HZ_PARTIES p
ON p.PARTY_ID = s.PARTY_ID;