OIC - Cash Actual and Forecast Cost Data Transfer from ERP to GTMS

Working...

🧩 Use Case: Cash Actual and Forecast Cost Data Transfer from ERP to GTMS

🎯 Objective

Automate extraction of Cash Actual and Forecast Cost data from ERP CM (Cash Management) using ESS → BIP flow, and deliver transformed files to GTMS shared folder, followed by a trigger file.

Here we will learn the following key concepts in Oracle Integration Cloud (OIC):

1. Calling an ESS Job (ESS → BIP) from OIC – Learn how to trigger a report or process in ERP Cloud by invoking an ESS job that generates a BIP report with required parameters.

2. Subscribing to an Event – Understand how to configure OIC to listen for ERP bulk export events (triggered when the ESS job completes successfully) and automatically start the next integration flow.

3. Downloading File from ESS Job – Learn how to retrieve the BIP output file from ERP Cloud once the job completes, using the ERP adapter or REST API.

4. Updating Lookup for Sequence Number – Explore how to use an OIC lookup table to maintain and update a unique sequence number, which is later used in the output file name and trigger file.

5. Trigger File Concept – Understand the purpose of a trigger file, a small control file (e.g., .trg or .txt) sent along with the main data file to the target system (like GTMS) to signal that the main file is ready for processing.

---

⚙️ Solution Overview

The integration flow is divided into two main schedulers and one event-driven main service.

---

1️⃣ Scheduler Integration – Cash Actual Extract

Name: SCHED_CashActual_Extract

Purpose:
Trigger ERP ESS job to generate the Cash Actual Report via BIP with parameters.

Steps:

1. Read From Date and To Date parameters (scheduler inputs).
2. Invoke ERP Cloud Adapter → Submit ESS Job (for Cash Actual BIP report).
   • Set parameters like report name, date range.
3. Enable Event = Y in ESS payload for bulk export (so ERP emits event once report is ready).
4. Store metadata (ESS request ID, filename pattern, etc.).
5. End.

---

2️⃣ Scheduler Integration – Forecast Cost Extract

Name: SCHED_ForecastCost_Extract

Purpose:
Same as above, but for Forecast Cost BIP report.

Steps:

1. Read From Date and To Date parameters.
2. Submit ESS job for Forecast Cost BIP report (with event enable = Y).
3. ERP emits event on report completion.
4. Store metadata if required.

---

3️⃣ Event-Driven Main Service – ERP Event Handler

Name: EVT_ERP_BulkExport_Handler

Trigger:
ERP Bulk Export Event (emitted when ESS job completes with event-enabled flag).

Purpose:
Process the exported BIP file, transform, send to GTMS folder, generate trigger file, and update lookup.

Steps:

1. Subscribe to ERP event (ERP → OIC via ERP Cloud Adapter).
2. Download file from ERP content repository (using ERP REST API or File Download endpoint).
3. Get sequence number from OIC Lookup (e.g., SEQ_LOOKUP).
   • This number is used in both output filename and trigger file content.
4. Transform ERP report data to GTMS file structure (using XSLT or Stage File mapping).
5. Write output file to GTMS shared folder (via FTP/SFTP adapter).
   • Example filename:

     CashForecast_<SeqNum>_<YYYYMMDD>.csv
     

6. Generate trigger file (e.g., .TRG or .TXT) with content containing sequence number or status.
   • Example content:

     FILE_NAME=CashForecast_102_20251022.csv
     STATUS=SUCCESS
     

7. Upload trigger file to the same GTMS folder.
8. Update lookup (increment sequence number) to mark completion.
9. Send optional notification or audit log update.

---

🧱 Components Used

Component	Type	Description
Scheduler	Integration	Triggers ESS jobs for Cash Actual & Forecast
ERP Adapter	Connection	Used for ESS job submission and file download
Lookup	Configuration	Stores and updates sequence number
FTP/SFTP Adapter	Connection	For GTMS shared folder file delivery
Event Subscription	Mechanism	ERP Bulk Export event triggers main flow
XSLT Mapping	Transformation	To convert ERP BIP file to GTMS expected format

---

🪜 End-to-End Flow Summary

1. Scheduler 1 & 2 trigger ESS jobs for each report (Cash Actual & Forecast Cost).
2. ESS jobs generate BIP reports and emit events (eventEnabled=Y).
3. Main Event Integration is triggered → downloads BIP output.
4. Transforms and uploads the data file to GTMS shared folder.
5. Retrieves sequence from lookup, updates it post success.
6. Creates trigger file, uploads it to the GTMS folder.
7. End.

---

🧭 High-Level Architecture Diagram

             +--------------------+
             |  Scheduler 1       |
             | (Cash Actual)      |
             +---------+----------+
                       |
                       | Submit ESS Job (BIP)
                       v
             +--------------------+
             |  Oracle ERP Cloud  |
             |  (ESS + BIP Report)|
             +---------+----------+
                       |
                       | Event (Bulk Export)
                       v
             +-----------------------------+
             |  OIC Main Event Integration |
             |  (EVT_ERP_BulkExport_Handler)|
             +---------+----------+---------+
                       |          |
               Download File     Lookup Sequence
                       |          |
                       v          v
             +----------------------------+
             |  Transform (XSLT Mapping)  |
             +-------------+--------------+
                           |
                 Upload to GTMS Folder
                           |
                           v
             +----------------------------+
             | Generate & Send Trigger File|
             +-------------+--------------+
                           |
                     Update Lookup
                           |
                           v
                    Integration Success

---

✅ Error Handling & Retry Strategy

• ESS job failure: Capture and notify via email/log.
• File download error: Retry with exponential delay (3 attempts).
• Lookup update failure: Rollback or queue update post success.
• GTMS upload failure: Use stage file retry or fault handler to reattempt.