The Oracle Fusion Cloud Extraction Guide

Fusion Cloud is the one ERP on this site where the extraction question is not “how do I connect” but “which delivered surface do I use” — the SaaS database has no customer-facing SQL path at all (the quirks guide's #no-sql is the long version). Data leaves through four delivered surfaces: BICC (scheduled bulk extracts of view objects to storage), OTBI (real-time subject-area queries), BIP (operational reports), and REST APIs.

For analytics volume, BICC is the path — Oracle's own SCM integration guidance calls it the best option for exporting bulk data to downstream warehouses. OTBI and BIP can move small reference sets, but they are reporting tools with row limits and session semantics, not pipelines. That is why every table page in this reference carries an Extract access panel naming the BICC data store (PVO) that reaches it: the table name alone is not an access path.

BICC organizes extractable content as offerings containing data stores — BI view objects, the PVOs this reference maps. In the BICC Console you enable stores per offering, choose which attributes each store extracts, and schedule jobs (once, hourly, daily, weekly, monthly) — BICC brings its own scheduler.

Full vs incremental: the first run of a store is a full extract; after that, BICC extracts changes using the store's incremental key columns— in practice last-update timestamps — compared against a stored per-store last-extract date. Resetting that date forces a full re-extract. An “Initial Extract Date” option filters a full extract to rows created after a cutoff, on columns the store flags as creation dates — the supported way to skip decades of history on the first pull.

Two caveats belong in every design. First, the official late-data mitigation is the prune time preference: each incremental re-extracts a configurable window beforethe last extract date (default 24 hours) so cross-object dependencies land consistently — meaning re-delivered rows are normal, and your load must be idempotent (see #incremental). Second, practitioner experience — not the BICC book — reports that some attribute changes (descriptive flexfields are the usual example) don't bump the underlying last-update timestamp; schedule periodic full refreshes for stores where that risk matters. Flexfield attributes in general appear in extracts only when the flexfields are BI-enabled and the BICC metadata has been refreshed.

BICC delivers to UCM(Oracle's content server inside Fusion) or to cloud object storage (OCI Object Storage, plus the legacy Oracle Storage Service). Each run lands zipped CSV data files per store, an .mdcsv metadata file describing columns and data types, and a MANIFEST.MFfile listing the batch with MD5 checksums. UCM is the default but the worse pipeline citizen: files must be pulled by document id via the manifest, and Oracle recommends expiring processed files after 30 days so UCM storage doesn't fill — object storage is the natural lakehouse landing zone. One book warning worth repeating: configuring the same data store in multiple schedules with different storage targets loses incremental data.

On the Databricks side, Auto Loader incrementally ingests files from cloud object storage — its documented sources are S3, ADLS, GCS, Azure Blob, and Unity Catalog volumes. OCI Object Storage is not on that list: Oracle documents an S3-compatibility API for OCI, but Databricks does not document OCI as a source — so either read OCI through its S3-compatible endpoint (test it yourself; nobody certifies the pairing), or copy files into a natively supported store, or upload into a Unity Catalog volume. Land the raw files into a bronze schema under Unity Catalog — the source-aligned layer every snippet on this site points its <catalog>.<schema> placeholders at.

A BICC extract speaks the view object's language, not the table's: file names embed the full dotted PVO key, and the data is attribute-shaped (InventoryItemId, sometimes entity-prefixed) rather than column-shaped (INVENTORY_ITEM_ID). Oracle's own mapping artifacts are the .mdcsv metadata file delivered with each batch and the VO-to-database lineage spreadsheets the BICC documentation points to — use those, not camel-case intuition, because PVOs rename, prefix, and join in attributes freely (quirks #pvo-drift).

Do the rename exactly once, at the bronze-to-silver boundary, so silver speaks the documented table schema this reference (and Oracle's Tables and Views books) describe. Keep the last-update timestamp through the rename — it is the watermark column the incremental pattern below keys on.

-- BICC files are shaped by the VIEW OBJECT, not the table: attribute
-- namespace, VO-derived file names, and the .mdcsv metadata file describing
-- the columns. Map names back to Oracle's documented table columns ONCE, at
-- the bronze-to-silver boundary, from the store's attribute list — then
-- everything downstream (including this site's boilerplate SQL) matches the
-- Tables and Views books. See quirks #pvo-drift for the drift traps.
CREATE OR REPLACE VIEW <catalog>.<schema>.INV_MATERIAL_TXNS AS
SELECT
  TransactionId          AS TRANSACTION_ID,
  InventoryItemId        AS INVENTORY_ITEM_ID,
  OrganizationId         AS ORGANIZATION_ID,
  TransactionTypeId      AS TRANSACTION_TYPE_ID,
  PrimaryQuantity        AS PRIMARY_QUANTITY,
  TransactionDate        AS TRANSACTION_DATE,
  LastUpdateDate         AS LAST_UPDATE_DATE   -- keep the watermark column
FROM <catalog>.<bronze_schema>.inv_material_txns_raw;

Two facts drive the load pattern. BICC incrementals overlap by design — the prune window re-delivers rows near the last extract date — so the load must be an idempotent upsert keyed on the primary key, deduped to the latest change per key. And incremental data extracts are delete-blind — a purged or hard-deleted row simply stops arriving. The delivered answer is the Active Primary Key Extract job type, which lands the full set of live primary keys as .pecsv files; anti-joining your lakehouse copy against that key set is how deletions are found.

The skeleton below keys on TRANSACTION_IDagainst the material-transaction ledger and orders the dedupe by the last-update timestamp — swap in each table's key from its page here. Every generated snippet on this site offers the same watermark column as an optional filter for exactly this reason.

-- Upsert a landed BICC incremental batch into the lakehouse copy. BICC
-- incrementals are driven by per-store incremental key columns (in practice
-- last-update timestamps) against a stored last-extract date, and the prune
-- window re-delivers rows on purpose — MERGE keyed on the primary key makes
-- re-delivery idempotent.
MERGE INTO <catalog>.<schema>.INV_MATERIAL_TXNS AS tgt
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY TRANSACTION_ID
      ORDER BY LAST_UPDATE_DATE DESC
    ) AS rn
    FROM <catalog>.<staging_schema>.inv_material_txns_batch
  ) WHERE rn = 1
) AS src
ON tgt.TRANSACTION_ID = src.TRANSACTION_ID
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

-- Deletes arrive separately: incremental data extracts are delete-blind.
-- Schedule an Active Primary Key Extract, land its .pecsv key file, and
-- anti-join — a key missing from the live-key set is a deleted row:
DELETE FROM <catalog>.<schema>.INV_MATERIAL_TXNS AS t
WHERE NOT EXISTS (
  SELECT 1
  FROM <catalog>.<staging_schema>.inv_material_txns_pk AS k
  WHERE k.TRANSACTION_ID = t.TRANSACTION_ID
);

The delivered views this catalog marks as views — the business-units view (FUN_ALL_BUSINESS_UNITS_V), the cost-orgs view (CST_COST_ORGS_V), and the FND lookup view (FND_LOOKUP_VALUES) — are query conveniences, not extraction targets; their table pages steer you to the base data or the delivered store that fronts it. Interface and staging tables are transient by design. OTBI and BIP are reporting surfaces — using them as bulk pipelines trades row limits and timeouts for no benefit once BICC is set up.

Operationally: don't configure the same data store in two schedules with different storage targets (the book's own incremental-loss warning); don't watermark-pull small reference and translation stores — schedule them as periodic full extracts and snapshot them; and don't assume flexfield attributes arrive at all until they are BI-enabled and the BICC metadata is refreshed. When a table here has no Extract access panel, that absence is a verified verdict, not an omission — plan OTBI/BIP or a different grain for those.