🧾 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:
- Generates the FBDI ZIP file
- Uploads the file to UCM
- Submits an ESS Job (e.g., "Load Interface File for Import")
- Monitors the ESS job status
- 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:
- Read source data
- Transform and generate FBDI .zip file
- Upload to UCM using ERP Cloud Adapter
- Submit ESS Job using
submitESSJobRequest
- Capture
requestId from the response
- 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
🔁 Step 4: Implement Polling Logic using While Loop
Condition:
status != "SUCCEEDED" AND status != "ERROR" AND loopCount < 20
Inside the loop:
- Call
getESSJobStatus via ERPIntegrationService SOAP connection
- Parse response:
<JobStatus>SUCCEEDED</JobStatus>
<Message>Completed successfully</Message>
- Assign status to local variable
- Wait for 1 minute (use Wait activity)
- Increment
loopCount += 1
🧠 Step 5: Decision Based on Status
After exiting the loop, check if:
status == "SUCCEEDED": proceed with business logicstatus == "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:
- .log file (execution log)
- .out file (output message, summary of load)
- .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} |
