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 five delivered surfaces: BICC (scheduled bulk extracts of view objects to storage), the Data Extraction tool (Oracle’s newer bulk path, reading a replicated read-optimized store — it speaks business objects and extraction views, not the PVOs this reference maps, so the extract map here applies to BICC only), OTBI (real-time subject-area queries), BIP (operational reports), and REST APIs.

Existing Fusion estates may still run BICC at analytics volume, and it is what this reference’s extract map documents. For new bulk-extraction work, follow Oracle’s current direction: as of the 26C integration guidance it labels BICC the legacy bulk-export option and directs bulk extraction to the newer Data Extraction tool, which reads a replicated, read-optimized copy rather than the transactional instance. Verify which is enabled on your pod before designing a pipeline. OTBI is a reporting surface with session and volume limits. Oracle explicitly calls direct Analytics Publisher bulk extraction unsupported. If BICC or REST cannot meet an exceptional Publisher use case, Oracle’s documented pattern is a custom ESSBIPJobType job with ERP Integration Service download operations — not an unrestricted report-as-pipeline fallback. 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 before the 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 can include zipped .csv application data,.mdcsv metadata, and .pecsv primary-key extracts. Manifest naming and contents depend on storage: UCM usesMANIFEST.MF with document ids, while Cloud Storage Service and OCI Object Storage use timestampedMANIFEST-*.MF files with uploaded filenames. 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. External-storage data filenames derive from the data store name with underscores replacing dots; UCM and job/global manifests follow their own documented forms. The data itself 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_ID against 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) — unless you use job-level extract metadata, which isolates a job’s refresh dates from the global ones and is the 26C book’s documented way to run overlapping extracts of one data store; 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.

One more non-path, now citable: Databricks ships a first-party Oracle connector in Lakeflow Connect, but it is a database-level LogMiner CDC connector, and Databricks’ own FAQ states it does not support Oracle Fusion (or similar SaaS applications) — there is no database port to point it at. It does not change anything on this page: delivered extract surfaces remain the only path.