Guide to Building a Data Pipeline from Oracle Fusion Cloud and WMS Cloud to Your Data Warehouse

 Organizations often need to integrate data from Oracle Fusion Cloud (Financials + SCM) and Oracle WMS Cloud into a central data warehouse—either on-premise or in Oracle Cloud (ADW/ATP).

There are multiple approaches, but in this article I highlight one practical, scalable, and commonly adopted method using Oracle BICC, WMS Data Extracts, OIC, SFTP, and ADW/ATP.

For more details or help with implementation, feel free to reach out at: inquiry@bizinsightinc.com.

Step 1 — Data Extraction from Oracle SaaS & WMS Cloud

A. Extracting Data from Oracle SCM & Financials Cloud (Using BICC)
For Fusion Financials and SCM data, I configured Oracle BI Cloud Connector (BICC)—a built-in Fusion component that requires no additional license and is extremely simple to set up.

Why BICC?

• Fast and easy to configure
• Metadata + sample data available for each extract
• Supports both Full Loads & Incremental Loads (for most, not all views)
• You can switch back to a Full Load anytime—for example, to recover from data corruption in the warehouse

Considerations

• For EFF (Extensible Flex Field) extraction, additional setup is required
• Incremental extracts are not yet available for certain BICC views

Delivery Mechanism
BICC delivers extract files to UCM, where each extract produces:

• manifest.mf — the metadata and file listing
• .zip files — the actual data files, each containing one or more CSV files

In my setup, UCM retention was configured for 3 days.

B. Extracting Data from Oracle WMS Cloud
For Oracle WMS Cloud, I configured WMS Data Extract Jobs under the Job Schedules UI.
Examples: Orders, PO Receipts, Inventory, Wave data, Task data, etc.
Delivery Mechanism

• WMS extracts are delivered directly to an SFTP server
• Files are generated in CSV format (no ZIP)

These extracts run on schedule automatically and land on the SFTP server ready for consumption.

Step 2 — Consuming and Preparing Data Files

Once extract files have been generated, Oracle Integration Cloud (OIC) handles orchestration.

For Fusion BICC Extracts (From UCM):

A scheduled OIC integration performs the following steps:

• Connects to UCM
• Retrieves the latest manifest.mf files
• Downloads associated .zip files listed inside each manifest
• Unzips the files into the SFTP server, extracting all .csv files
• Archives the original .zip and .mf for audit and traceability

For WMS Extracts:

• No additional file preparation is required
• Data arrives in flat .csv format directly in SFTP

This simplifies the ingestion process significantly.

Step 3 — Loading Data into the Data Warehouse (ADW/ATP or On-Prem)

Multiple scheduled OIC jobs are configured—one per module (DOO, AR, AP, WMS, PO, Product, etc.).

Each job performs:

Example: DOO Module Load

• Read Order Header, Order Line, Fulfillment Line, EFF files from SFTP
• Transform & parse the CSV files
• Load data into the respective CUSTOM DOO schema/tables inside the Data Warehouse (ADW/ATP)

This modular approach provides:

• Simplified maintenance
• Clear separation of concerns
• Independent rerun/reprocess capability
• Support for incremental vs full loads

Step 4 — End-to-End Error Handling & Monitoring

A comprehensive error-handling framework was built inside OIC to manage:

• File errors
• Missing or corrupt CSV/Zips
• UCM download failures
• Data validation errors
• Database load issues

This framework includes:

• OIC Error Handlers
• Logging into ATP Error tables
• Automated notification emails
• Ability to reprocess failed files without manual intervention