OIC - Designing a Reusable Callback Integration for Multiple FBDI Uploads in Oracle Integration Cloud (OIC)

🧾 Use Case Overview

In most Oracle Fusion implementations, File-Based Data Import (FBDI) is a widely used approach to load master and transactional data into Fusion Cloud. Each business object (like Employees, Items, Customers, Daily Rates, etc.) has a unique FBDI template and requires an integration that:

1. Generates the FBDI ZIP file
2. Uploads the file to UCM
3. Submits an ESS Job (e.g., "Load Interface File for Import")
4. Monitors the ESS job status
5. Performs post-processing on success/failure

When you’re handling multiple business objects, step 4 and 5 are usually the same across integrations. Repeating this logic in every flow makes it:

• Redundant
• Hard to maintain
• Prone to errors

👉 So why not reuse this logic?

---

🎯 Goal

To create one common callback integration in OIC that can be invoked from any FBDI integration to:

• Poll the ESS Job status
• Handle success/failure
• Perform downstream processing based on the business object

---

🧱 Architecture Overview

[ FBDI Integration: Employees     ] \
[ FBDI Integration: Items         ]  \
[ FBDI Integration: Daily Rates   ]   --> [ 🔁 Common Callback Integration ]
[ FBDI Integration: Customers     ]  /

Each main FBDI flow:

• Ends by calling the Common Callback Integration
• Sends a payload with:
  • requestId (ESS Job ID)
  • businessObject (like "EMPLOYEES")
  • fileName, submittedBy, etc.

---

🧰 Prerequisites

• Oracle Integration Cloud Gen 2/3
• ERP Cloud Adapter and SOAP connection to ERPIntegrationService
• Basic understanding of:
  • FBDI process
  • ESS Jobs in Fusion
  • While/Switch activities in OIC

---

🧭 Detailed Implementation Steps

---

Step 1: FBDI Integration Flow (Example: Daily Rates)

This is your normal FBDI flow:

1. Read source data
2. Transform and generate FBDI .zip file
3. Upload to UCM using ERP Cloud Adapter
4. Submit ESS Job using submitESSJobRequest
5. Capture requestId from the response
6. Call Common Callback Integration with a payload:

{
  "requestId": "456789",
  "businessObject": "DAILY_RATES",
  "fileName": "DailyRates_20250618.zip",
  "submittedBy": "ManojKumar"
}

---

Step 2: Create the Common Callback Integration

Integration Type: App-Driven Orchestration
Trigger: REST Adapter (POST operation)

📥 Input JSON Schema:

{
  "requestId": "string",
  "businessObject": "string",
  "fileName": "string",
  "submittedBy": "string"
}

---

Step 3: Parse and Assign Variables

• Assign requestId, businessObject, and other fields to local variables.
• Initialize:

  status = ""
  loopCount = 0
  

---

🔁 Step 4: Implement Polling Logic using While Loop

Condition:

status != "SUCCEEDED" AND status != "ERROR" AND loopCount < 20

Inside the loop:

1. Call getESSJobStatus via ERPIntegrationService SOAP connection
2. Parse response:

   <JobStatus>SUCCEEDED</JobStatus>
   <Message>Completed successfully</Message>
   

3. Assign status to local variable
4. Wait for 1 minute (use Wait activity)
5. Increment loopCount += 1

---

🧠 Step 5: Decision Based on Status

After exiting the loop, check if:

• status == "SUCCEEDED": proceed with business logic
• status == "ERROR": log failure and send notification

---

🧪 Step 6: Use Switch for Business Object-Specific Logic

Switch on businessObject:
├── "DAILY_RATES"   → Call Daily Rates post-processing
├── "EMPLOYEES"     → Call Employees HDL flow
├── "ITEMS"         → Write data to DB or update flag
├── "CUSTOMERS"     → Trigger BIP report / send confirmation

Use Local Integration Calls or inline logic as needed.

---

Step7: Output we Can Fetch After getESSJobStatus

When getESSJobStatus completes, the response includes a reportFile or document ID that points to the output/log files. We can fetch:

1. .log file (execution log)
2. .out file (output message, summary of load)
3. .csv error file (for rows that failed)

Call getESSJobExecutionDetails (Optional)

We can invoke another operation (if available) to get details of the child job, if the job is a job set or composite.

Alternative Approach (Preferred):

Use ERPIntegrationService.downloadESSJobExecutionDetails or UCM file download API to download the .log and .out files using requestId.

Use UCM Web Service to Download Files

Once the ESS job runs, output files are stored in UCM. We can Call ERPIntegrationService > downloadExportOutput

Input: requestId >> You’ll get a base64 file content >> Parse it or store it in DB or FTP for audit

Or use WebCenter Content API (UCM API) to list files using requestId and download

Sample Output from .out File (Import Summary)

Total Records Read: 100  

Successfully Imported: 95  

Failed Records: 5  

Log File: import_daily_rates.log

📧 Step 8: Optional Email Notification

Send an email with:

• ESS Job Result
• File name
• Business object
• Message or error (if failed)

---

📂 Sample getESSJobStatus Request Payload (SOAP)

<typ:getESSJobStatusRequest>
   <typ:requestId>456789</typ:requestId>
</typ:getESSJobStatusRequest>

Sample Response:

<typ:getESSJobStatusResponse>
   <typ:JobStatus>SUCCEEDED</typ:JobStatus>
   <typ:Message>Completed successfully</typ:Message>
</typ:getESSJobStatusResponse>

---

🚨 Error Handling Strategy

• If ESS Job fails (ERROR), log:
  • requestId
  • businessObject
  • error message
• Store in DB or call a notification integration
• Enable retry if needed

---

💡 Best Practices

• Set a polling limit (e.g., 20 retries = ~20 mins)
• Avoid infinite loops
• Use consistent naming conventions for businessObject
• Create reusable sub-integration flows for downstream processing
• Add logging and tracking (e.g., via ATP/Logging framework)

---

🚀 Enhancements We Can Add

• Add DB persistence for incoming callback metadata
• Scheduled Integration to recheck failed jobs
• Audit dashboard for all FBDI callbacks
• Notify users in MS Teams / Slack using Webhook

---

✅ Conclusion

Building a common callback integration for all FBDI flows:

• Makes your integrations modular and maintainable
• Reduces redundancy
• Centralizes your error handling and monitoring

This pattern can be extended to HCM Extracts, BIP report monitoring, and ESS job chains as well.

---

📦 Sample Naming Suggestions

Artifact	Name
Integration	INT_COMMON_ESS_CALLBACK
SOAP Connection	ERPIntegrationServiceSOAP
Variable: requestId	varRequestId
Variable: loop counter	varLoopCount
Email Subject	FBDI ${businessObject} - Job ${status}