The Oracle Fusion Cloud Quirks Guide

-- 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 four delivered ways data leaves Fusion Cloud:
--   BICC  - bulk extracts of public view objects (PVOs) to object storage;
--           the analytics path, and what this reference's extract map covers
--   OTBI  - real-time subject-area queries; operational reporting, not bulk
--   BIP   - operational reports with SQL-shaped data models; small volumes
--   REST  - transactional APIs; integration, not analytics
-- If a table has no documented PVO, its Extract access panel is absent here
-- and OTBI/BIP are the fallbacks - see the extract map notes per table.

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

-- 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.
--
-- None of this wave's foundation/product/inventory tables carries ORG_ID -
-- the BU appears under explicit names instead. Enumerate business units:
SELECT
  bu.BU_ID,
  bu.BU_NAME,
  bu.STATUS
FROM FUN_ALL_BUSINESS_UNITS_V bu;

-- Map each inventory org to its business unit (the org's BU id is a named
-- column, not a stripe):
SELECT
  iop.ORGANIZATION_ID,
  iop.ORGANIZATION_CODE,
  iop.BUSINESS_UNIT_ID,
  bu.BU_NAME
FROM INV_ORG_PARAMETERS iop
JOIN FUN_ALL_BUSINESS_UNITS_V bu
  ON bu.BU_ID = iop.BUSINESS_UNIT_ID;

-- When the order-to-cash and procure-to-pay waves land their _ALL tables,
-- the generated SQL here will anchor t.ORG_ID = <business_unit_id> - the
-- value comes from BU_ID above, never from an EBS operating-unit 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).

-- _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 date-effective table - swap it for the as-of
-- pattern when you need history. 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.