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

Sample REST api call to Pack Inventory in Oracle WMS Cloud

 WMS REST api - /wms/lgfapi/v10/repack/pack_inventory

The Pack Inventory API allows you to pack inventory based on the source OBLPN (from OBLPN), item, quantity and the destination OBLPN (to OBLPN) information

END POINT : https://<wms instance url>/wms/lgfapi/v10/repack/pack_inventory

Operation : POST

{

    "facility_id__code": "000",

    "company_id__code": "BIZ",

    "restrict_multiorder_combine_flg": false,

    "from_oblpn_nbr": "BIZ0000004784",

    "item_alternate_code": "AS54888",

    "qty": 1,

    "to_oblpn_nbr": "OBARC0000004786",

    "packing_station_barcode": "PACK001",

    "short_flg": false

}

Notes

• Optional parameters (such as batch_nbr, expiry_date, and to_oblpn_type) can be omitted if not applicable.
• Ensure that the qty does not exceed available inventory in the source OBLPN.

Sample REST api call to Close LPN in Oracle WMS Cloud

WMS REST API - /pick_pack/close_lpn 

This API is used to close an LPN (License Plate Number) after the picking and packing process is complete in Oracle WMS Cloud.

Majority of time LPN is normally triggered by warehouse RF operations, but many organizations automate it using WMS REST API + OIC.

Below is Use case why there is need to Manually Close LPN

• You have an outbound wave generating pick tasks.
• Items are picked and placed into OBLPNs (cartons).
• Instead of RF scanning to close each carton, the business wants:
• A carton to auto-close when all expected quantities are picked.
• A batch job (via OIC) to auto-close all LPNs in PACKING status.
• Automation Benefit
• Removes manual RF step → speeds throughput → reduces labor cost.

In such Scenario , A schedule OIC job trigger and call WMA REST API to close LPNs, below is Sample REST api call to close LPN

END POINT : https://<wms instance url>/pick_pack/close_lpn 

Operation : POST

{ "facility_id":1, "company_id":1, "to_container_nbr":"BIZ0000003881", "close_container_status":"picked", "short_on_close_flg": true, "async_flg": true }

Sample REST api call to run Manual Wave in Oracle WMS Cloud

The Run Manual API allows you to invoke a manual wave. We have use case where customer would like to create and process Sales Order and Order Management cloud and then would like to trigger Manual Wave in WMS. Once Order is ready for they are invoking OIC flow , and that in turn call WMS rest API
/wms/lgfapi/v10/entity/wave_template/run_manual

Sample Payload to create Manual Wave
https://<wms instance url>/wms/lgfapi/v10/entity/wave_template/run_manual/
Operation : POST

{

  "parameters": {

  "facility_id": 1,

  "template_name": "Order Based Replen" //"Mixed Order Picking"

  },

  "options": {

  "order_dtl_id_list": [5426]

  }

}

In option section of API , you can provide Order information either by providing one or list of

Order_detail_ID ( as shown in above example) , or provide a or list of list of

order number / sequence number pairs