OIC - Automating GL FBDI Import and Monitoring with OIC Callback, Twilio Email, and DataDog

Use Case Overview

The objective of this integration is to automate the General Ledger (GL) FBDI file import process from Oracle Object Storage into Oracle ERP Cloud using Oracle Integration Cloud (OIC).

The solution not only performs the end-to-end data load but also:

• Monitors the import execution status through OIC callback,
• Sends detailed status emails using Twilio Email Service, and
• Logs any failed import details into DataDog for proactive monitoring and troubleshooting.

Note: The source system provides the CSV file in an FBDI-compatible format, so we do not recreate or generate the FBDI CSV file within the integration.

---

Integration Architecture Overview

The entire flow is structured into three orchestrated integrations:

1. Scheduler Integration

• Runs on a predefined schedule (e.g., hourly or daily).
• Triggers the Main Integration that performs the file listing and orchestration

2. Main Integration

• Connects to Oracle Object Storage and lists available files in the input bucket (pending GL extracts).
• For each file found, it:
• Passes the file name and metadata to the Child Integration using a For-Each loop.
• Ensures parallel or sequential invocation as per configuration (typically sequential for control).

3. Child Integration

• Responsible for the core business logic of importing the GL data into ERP.
• Steps include:
1. Download the source file from Object Storage.
2. Generate a properties file (metadata required for import).
3. Create a zip file combining the data file and the properties file — this forms the FBDI.zip.
4. Invoke ERP Bulk Import Service (Load Interface File for Import) to upload the FBDI.zip.
5. Archive the FBDI zip file to internal OIC folder for support help.
6. Rename the processed file with Processed_ prefix.
7. If any error, logs to datadog from global fault.

Properties file map: 

ERP bulk import:

---

Callback Integration – Monitoring and Notification

The callback performs the following major actions:

Receive business events:

Once ERP finishes processing the submitted FBDI file—regardless of success or failure—we will subscribe to the ERP Integration Inbound Event and apply a filter condition to identify and process only the corresponding GL import event.

Assign required details

Assign the required details into the variable so that we can use them further.

Implement a Switch action to differentiate between Success, Load Error, and Import Error scenarios, and store the corresponding Process Request ID for each case.

Download the ESS job execution details

• Uses the Request ID stored from the previous step and download the logs zip file.
• Unzip the excution details
• List all the execution files .log, .txt etc.

Call twilio common service for email:

add 3 repeat nodes and use for Success, load error and import error scenarios:

Success Scenario

If the status = SUCCEEDED:

• Downloads the .txt file generated from the “Import Journal: Child” process.
  Example: 3503927.txt
• Uses Twilio Email Service to send the success email with the attached .txt file to the mail distribution list.

Error Scenario

If the summary status is not succeeded:

• The integration downloads relevant log and data files to capture failure points.

Import Failure Error

• Attaches the .txt file from the “Import Journal: Child” process.
  Example: 3503927.txt

Load File Error

• Attaches:
  • The .csv file from “Load Interface File for Import”
    Example: GLExtract_20250825.csv
  • The .log file from “Load File to Interface”
    Example: 3503909.log

All these files are attached and sent via Twilio Email Service to the mail group.

---

4. DataDog Logging

For every error or warning in the process:

• The integration pushes a structured JSON log entry into DataDog containing:
  • File name
  • ERP Request ID
  • ESS job name and status
  • Timestamp and failure message
• This enables real-time visibility into OIC integration health and ERP job-level outcomes.

---

End-to-End Flow Summary

Step	Component	Action
1	Scheduler	Triggers Main Integration periodically
2	Main Integration	Lists files in Object Storage and invokes Child Integration for each file
3	Child Integration	Downloads, zips, and imports file into ERP
4	ERP Bulk Import	Executes “Load Interface File for Import” and “Import Journal: Child” jobs
5	Callback Integration	Monitors job execution, downloads logs, and triggers Twilio email
6	DataDog	Receives failure logs for monitoring and alerting

---

Conclusion

This automated design using OIC, Twilio, and DataDog ensures:

• A fully hands-free, monitored GL import process,
• Automatic email alerts with detailed attachments,
• Real-time visibility into both success and failure events,
• And zero manual follow-up for ERP job status.

By modularizing the integration into scheduler, main, child, and callback components, the process becomes reliable, reusable, and easy to maintain.

Reference:

OIC - How to Send Email Using Twilio SendGrid API in Oracle Integration Cloud (OIC)