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)

Microsoft Excel - Importing and exporting data

1. Importing Data into Microsoft Excel

Excel allows you to import data from various sources such as text files, databases, and the web.
Steps:

1. Open Excel and go to the Data tab.
2. Click Get Data → choose the source (e.g., From File, From Database, From Web).
3. Browse and select your file or connection.
4. Preview the data and click Load to import it into Excel.

---

2. Importing Data from Text Files

You can import .txt or .csv files containing delimited data (commas, tabs, etc.).
Steps:

1. Go to Data → Get Data → From File → From Text/CSV.
2. Choose the file and click Import.
3. In the preview window, choose the correct delimiter (comma, tab, etc.).
4. Click Load or Transform Data to edit before loading.

---

3. Importing Data from Microsoft Access

Use this option to connect Excel with an Access database and pull tables or queries.
Steps:

1. Go to Data → Get Data → From Database → From Microsoft Access Database.
2. Select the Access file (.accdb or .mdb).
3. Pick the table or query you want to import.
4. Click Load to bring the data into Excel.

---

4. (New Version) Import Data from Text Files into Excel

The latest Excel versions (Office 365, Excel 2021+) use Power Query for importing.
Steps:

1. Go to Data → Get Data → From File → From Text/CSV.
2. Power Query opens automatically — preview and format data.
3. Choose Load to sheet or Load to Data Model for advanced analysis.

---

5. (New Version) Import Data from a Database into Excel

You can import directly from SQL Server, Oracle, or other supported databases.
Steps:

1. Go to Data → Get Data → From Database.
2. Select the database type (e.g., SQL Server).
3. Enter the server name, database, and credentials.
4. Select the tables or views to import, then click Load.

---

6. Microsoft Excel Legacy Import Options for New Excel Versions

Newer Excel versions still support legacy wizards for backward compatibility.
Steps:

1. Go to Data → Get Data → Legacy Wizards → From Text (Legacy).
2. Choose your file and follow the step-by-step import wizard.
3. Use this when older formatting or delimiters don’t work properly in Power Query.

---

7. Exporting Data to a Text File

You can export your Excel worksheet data as a .txt or .csv file for use in other applications.
Steps:

1. Go to File → Save As.
2. Choose a location and select Save as type → CSV (Comma delimited) or Text (Tab delimited).
3. Click Save.
4. Excel will warn that only the active sheet is saved — click OK to confirm.

Microsoft Excel - Excel Data Validation

1. Understanding the Need for Excel Data Validation

Data Validation in Excel helps control the type of data entered into a cell.
It ensures data accuracy, consistency, and prevents invalid or incorrect entries.
For example, you can restrict users to enter only numbers, dates, or values from a predefined list.

Use Cases:

• Allow only whole numbers between 1 and 100.
• Accept only dates within a project timeline.
• Limit entries to department names like HR, Finance, Sales.

---

2. Creating an Excel Data Validation List

A drop-down list allows users to pick from set options rather than typing manually.

Steps:

1. Select the cell or range.
2. Go to Data → Data Validation → Settings tab.
3. In Allow, choose List.
4. In Source, type options separated by commas (e.g., Yes,No,Maybe) or refer to a cell range (=$A$1:$A$3).
5. Click OK.

Result:
A drop-down list appears, ensuring only predefined values can be selected.

---

3. Excel Decimal Data Validation

You can restrict entries to specific decimal ranges.

Steps:

1. Select cells → Data → Data Validation.
2. Choose Decimal under Allow.
3. In Data, choose a condition like between.
4. Enter the minimum and maximum values (e.g., 0 and 100).

Example:
Only values between 0.5 and 9.99 are accepted.

---

4. Adding a Custom Excel Data Validation Error

You can show a custom message when users enter invalid data.

Steps:

1. After setting validation rules, open the Error Alert tab.
2. Check Show error alert after invalid data is entered.
3. Choose a Style:
   • Stop (prevents invalid entry)
   • Warning (allows entry after warning)
   • Information (just informs user)
4. Add a Title and Message (e.g., “Invalid Entry: Please enter numbers only.”)

---

5. Dynamic Formulas by Using Excel Data Validation Techniques

You can make dynamic drop-downs that change based on another cell’s selection.

Example:

• Cell A1: Country (India, USA)
• Cell B1: City (changes based on country)

Steps:

1. Create lists for each country (e.g., IndiaList, USAList) and define named ranges.
2. In Cell B1 → Data Validation → Allow: List.
3. In Source, enter formula:

   =INDIRECT(A1)
   

4. Now, when “India” is selected in A1, only Indian cities appear in B1.