OIC - Streamlined OIC Scheduled Integration for GL Fuel Cost Data Using ESS Job Callback and Event-Based File Processing Without Timeouts

Use Case

The organization needs to automate the extraction and delivery of GL Fuel Cost data generated from a BI Publisher report in Oracle Fusion ERP. The integration should be efficient, scalable, and must avoid long-running processes and timeouts within Oracle Integration Cloud (OIC).

The requirement includes:

• Scheduled trigger to initiate the process.
• Submission of the BI report using the Export ESS Job Wrapper.
• Callback-based integration to handle ESS job completion.
• Document download, decode, unzip, and CSV parsing.
• Transformation and delivery of the processed data to a target system.

---

Optimized Solution Approach (Without Timeout)

1. Scheduled Integration – Submit ESS Job Only

• Create a Scheduled Orchestration Integration in OIC.
• Trigger the Export ESS Job Wrapper using the ERP Cloud Adapter.
• Service: ErpIntegrationService
• Operation: exportBulkData
• Pass the following information:
•  JobName: "JobPackageName,JobDefinitionName"
• parameterList: concat(param1,param2)
• JobOptions:  EnableEvent= Y to ensure callback is published.
• Do not wait for completion in the same flow — this avoids long runtime and timeouts.

2. Callback Integration – Listen to ERP Event

• Create a separate App-Driven Integration.
• Use ERPIntegrationOutboundEvent as the trigger.
• Apply a filter condition on Job Name or Report Path to restrict to only GL Fuel Cost report completions.

3. Get Report Output via Document ID

• From the event payload, extract the Document ID.
• Call the GetDocumentForDocumentId operation using ERP Cloud Adapter.
• Retrieve the output as a base64-encoded ZIP.

4. Decode and Unzip File

• Use OIC's base64 decode function.
• Use Stage File – Unzip File action to extract files from the archive.
• Use a For-Each loop to iterate through unzipped files.

5. Parse CSV and Transform Data

• For files with .csv extension:
• Use Stage File – Read File to parse contents.
• Transform the data into the required target structure using Mapper.

6. Deliver Data to Target System

• Send the formatted data to the external target (via REST, FTP, etc.).
• Include proper error handling, logging, and retry logic for robustness.

---

Benefits of This Approach

• Avoids Timeout: Processing is split between scheduler and callback flow, preventing long execution.
• Event-Driven: Ensures OIC listens and responds only when the ESS job completes successfully.
• Modular Design: Easier to maintain and enhance.
• Optimized Performance: Resource-light scheduled flow, heavier processing is deferred to callback.

Ess job sample page: