Automating AR Invoice Processing from On-Premise Systems to Oracle Fusion Receivables Using OIC and OCI

Automating AR Invoice Processing from On-Premise Systems to Oracle Fusion Receivables Using OIC and OCI

Many organizations continue to operate critical billing systems on-premise while adopting Oracle Fusion Receivables for modern, cloud-based financial operations. A common challenge in such hybrid environments is automating Accounts Receivable (AR) invoice creation—including invoice attachments—without manual intervention, while ensuring data accuracy, traceability, and operational resilience.

This blog presents a scalable and governed integration architecture that automates AR invoice processing from an on-premise system into Oracle Fusion Receivables, using Oracle Integration Cloud (OIC) and Oracle OCI Object Storage. The solution is designed for high reliability, auditability, and ease of support—without introducing custom code in the integration layer.

Business Scenario

The on-premise system generates customer invoices after internal approval. For each invoice:

1. A CSV file contains invoice header and line details
2. A PDF file represents invoice attachments (supporting documents)
3. Both files share the same base name and are placed on an SFTP server

The business requirement is to:

1. Automatically ingest these files.
2. Create or update invoices in Oracle Fusion Receivables
3. Attach the PDF document to the corresponding invoice
4. Maintain full audit visibility and error traceability
5. Archive processed files for compliance and reconciliation

 High-Level Architecture Overview

The solution is built around three logical process flows, each with a clear responsibility:

1.      Invoice File Generation (Out of Scope)
The on-premise system produces approved invoice files and places them on SFTP.

2.      Inbound File Ingestion (OIC → OCI Object Storage)
OIC securely transfers invoice files from SFTP into OCI Object Storage.

3.      Invoice Processing and Posting (OIC → Fusion AR)
OIC validates, processes, posts invoices into Fusion Receivables, attaches documents, and archives files.

This layered approach cleanly separates file movement, business processing, and system-of-record updates, improving reliability and supportability.

Flow 1: File Generation on On-Premise System (Out of Scope)

Once an invoice is approved in the on-premise system:

• A structured CSV file is generated with invoice details
• A corresponding PDF file is created as an attachment
• Both files are placed on a secured SFTP server

This upstream process is assumed to be stable and is not modified as part of this design.

Flow 2: Secure File Transfer to OCI Object Storage

Scheduled OIC Job: File Ingestion :A scheduled OIC integration runs at regular intervals to:

• Connect securely to the customer SFTP server
• Identify completed invoice files (CSV and PDF pairs)
• Transfer files into OCI Object Storage, which acts as a durable and scalable staging layer

Why OCI Object Storage?

• Using OCI Object Storage provides several advantages:
• Decouples file ingestion from invoice processing
• Eliminates dependency on SFTP availability during processing
• Enables retry, replay, and controlled processing
• Supports long-term archiving and compliance

Files are stored in a structured layout (for example, inbound and archive areas), ensuring traceability and operational clarity. 

Flow 3: Invoice Creation and Attachment in Oracle Fusion Receivables

Scheduled OIC Job: Invoice Processing

A second scheduled OIC integration performs the core business processing:

1. File Discovery and Validation

• Identifies ready-to-process invoice files in OCI Object Storage.
• Ensures required invoice data and attachment files are present

2. Audit and Tracking

Before invoking Fusion APIs, invoice details are recorded in an ATP-based audit table.
This table serves as a single source of truth for:

• Invoice processing status
• Success or failure details
• Fusion transaction identifiers
• Error messages for operational follow-up

This ensures full end-to-end traceability, a key requirement for finance teams.

3. Invoice Existence Check

The integration verifies whether the invoice already exists in Oracle Fusion Receivables:

• If it exists, the invoice is treated as an update
• If it does not exist, a new invoice is created

This design supports both new invoices and corrections without manual intervention.

4. Invoice Creation or Update

Validated invoice data from the CSV file is mapped to Fusion Receivables and submitted using standard Oracle APIs.

On completion:

• Success responses update the audit table with Fusion transaction IDs
• Errors are logged with detailed messages for analysis and reprocessing

5. Attachment Handling

Once the invoice is successfully created or updated

• The corresponding PDF file is attached to the invoice in Fusion Receivables
• Attachment success or failure is independently tracked

This separation ensures that invoice posting is not blocked by attachment issues.

Leveraging OCI Object Storage Through Oracle Integration Cloud

A key enabler of this solution is the native integration between Oracle Integration Cloud (OIC) and OCI Object Storage, which allows Object Storage to act as a controlled, resilient processing layer rather than a simple file repository.
Instead of processing invoices directly from SFTP, OCI Object Storage is intentionally positioned as an intermediate orchestration layer between file ingestion and Fusion Receivables processing.

At runtime, OIC interacts with the OCI Storage bucket to:

• List available invoice files waiting to be processed
• Identify valid invoice file pairs (CSV and PDF with matching names)
• Ensure files are complete and ready for processing

This allows OIC to act as a traffic controller, deciding when and what to process rather than reacting blindly to file arrivals.

Object Retrieval for Business Processing

Once a valid invoice file is identified:

• OIC retrieves the invoice data file from the OCI bucket for validation and invoice creation
• The PDF attachment is retrieved only after the invoice is successfully created in Fusion Receivables

This staged access ensures:

• Invoice creation is not blocked by attachment availability
• Partial failures do not corrupt business transactions

 Object Storage as a State Boundary

OCI Object Storage acts as a state boundary in the integration:

• Files in the inbound bucket represent pending work
• Files moved to the archive bucket represent completed work
• Files remaining due to errors represent exceptions requiring attention

This design eliminates ambiguity and simplifies operational support.

 Archival and Cleanup Strategy

After successful processing:

• Both CSV and PDF files are moved to an OCI Archive bucket
• Files are retained for audit, reconciliation, and compliance purposes
• Original inbound files are removed to prevent duplicate processing

This controlled archival approach supports:

• Regulatory compliance
• Operational transparency
• Simplified reprocessing if required

 Error Handling and Reprocessing

The solution is designed with operational resilience in mind:

• All failures are captured in the audit table with meaningful messages
• Functional errors (data issues) are separated from technical errors
• Invoices can be reprocessed without re-uploading files
• Support teams can track invoice status without accessing OIC directly

This significantly reduces support effort and improves SLA compliance.

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 }