OIC - ERP - Automated Payment File Transmission Using ERP PR Process, Manage Transmission Configuration & OIC

🔎 Overview

In Oracle ERP Cloud, the Payment Process Request (PR) generates payment files (ACH/RTGS/FPS/TT, etc.).

Using Manage Transmission Configurations, ERP securely sends the payment file to an OIC SFTP server. An event is generated, which OIC subscribes to. OIC then downloads, transforms, and forwards the file to the target bank or external system.

This architecture ensures secure, automated, and event-driven payment file processing.

📌 Use Case

🎯 Business Scenario

• Organization runs Payment Process Request (PR) in ERP.
• Payment file must be transmitted to bank in specific format.
• File transformation required before sending to bank.
• Need automated, event-driven, secure integration.

🔁 End-to-End Flow

1. PR process runs in ERP.
2. ERP generates payment file.
3. Manage Transmission Configuration pushes file to OIC SFTP.
4. ERP raises transmission event.
5. OIC subscribes to event.
6. OIC downloads file from SFTP.
7. OIC transforms file (mapping/formatting).
8. OIC sends file to target bank SFTP/API.

✅ Solution Architecture & Steps

🟢 Step 1: Configure Payment Process Request (PR) in ERP

Navigate to:

Navigate to: Setup and Maintenance →Manage Payement Process Profiles

Ensure payment format is configured.

Associate transmission configuration with payment format.

✔ Validate payment file is generated successfully.

🟢 Step 2: Configure Manage Transmission Configuration in ERP

Navigate to: Setup and Maintenance → Manage Transmission Configurations

Configure:

Protocol → Secure File Transfer Protocol (SFTP)

Host → OIC SFTP endpoint

Port → 22

Username/Authentication

Remote Directory

Assign this configuration to payment format.

✔ Test connection.

🟢 Step 3: File Transmission from ERP to OIC SFTP

Once PR completes:

ERP automatically transmits file to OIC SFTP.

Transmission status updates to “Completed”.

An ERP business event is generated.

Navigations >> Payables >> Payments >> task >> submit payment process request >> choose which payement profile option.

🟢 Step 4: OIC Event Subscription

In Oracle Integration Cloud:

Create integration using ERP Adapter (Business Event Trigger)

Subscribe to Payment Transmission event

Event contains metadata such as:

File name

Transmission status

Payment reference

Filtering based on Payment profiles:

🟢 Step 5: Download File from OIC SFTP

Inside main OIC orchestration:

Use SFTP Adapter

Read file from inbound directory

Store file in staging variable

🟢 Step 6: Transform File

Use Mapper

Convert ERP format to Bank-specific format

Apply:

Header changes

Footer calculation

Field reformatting

Encoding changes (if required)

🟢 Step 7: Send to Target System

Based on bank requirement:

Push via SFTP

OR

Call Bank API

OR

Send via secure socket

Log response and update tracking.

🏗️ Architecture Diagram (Logical View)

ERP PR

⬇

Manage Transmission Config (SFTP)

⬇

OIC SFTP

⬇

ERP Event

⬇

OIC Event Subscription

⬇

Main OIC Orchestration

⬇

Transform

⬇

Target Bank

🔐 Security Considerations

✔ Use SSH key-based authentication

✔ Restrict IP whitelisting

✔ Use separate inbound/outbound directories

✔ Enable encryption if bank requires PGP

✔ Mask sensitive payment data in logs

🏆 Benefits

Fully automated payment transmission

Event-driven architecture

Secure file handling

Flexible transformation capability

Reduced manual intervention

Clear monitoring in ERP & OIC

🔚 Conclusion

By integrating Payment Process Request → Manage Transmission Configuration → OIC Event Subscription → Transformation → Bank Delivery, organizations can build a robust and secure payment automation framework.

This approach ensures scalability, traceability, and compliance with banking standards.

OIC ERP - Manual Purge of Interface Tables for Import Costs in Oracle ERP Cloud

🔎 Overview

When running Import Costs in Oracle ERP Cloud (Cost Management), interface tables store staging data. If not cleaned regularly, they grow large and affect performance.

This guide explains how to manually purge interface tables directly from ERP (no automation, no OIC).

📌 When to Manually Purge?

• After successful Import Costs completion
• When interface table size becomes large
• When facing slow import performance
• During period-end cleanup

✅ Steps to Manually Purge from ERP

Step 1️⃣: Navigate to Scheduled Processes

Go to Tools → Scheduled Processes

Step 2️⃣: Click “Schedule New Process”

Click Schedule New Process

Search for: Purge Interface Tables

Select and click OK

Step 3️⃣: Enter Required Parameters

• Import process name
• Start Load Request Id
• End Load Request Id

Step 4️⃣: Submit the Process

• Click Submit
• Monitor the job status
• Wait for status = Succeeded

Step 5️⃣: Validate

Run Import Costs again (if required)

Confirm performance improvement

Ensure no business impact

⚠️ Precautions

❌ Do NOT purge unprocessed or error records

❌ Do NOT purge current-day active imports

✔ Always coordinate with Functional Team

✔ Prefer non-business hours for purge

🎯 Result

Manual purge helps:

Reduce interface table size

Improve Import Costs performance

Maintain ERP stability

Avoid long-running ESS jobs

OIC - Initiate Twilio SendGrid Message Export & Identify Target Email

🔎 Use Case

Trigger SendGrid email activity export via API and check:

Which email will receive the CSV

Which API key (token) is being used

🚀 API Call Endpoint

POST https://api.sendgrid.com/v3/messages/download

Authorization

Bearer <SendGrid API Key>

Sample Body

Json

{

  "query": "last_event_time BETWEEN TIMESTAMP \"2026-02-01T00:00:00Z\" AND TIMESTAMP \"2026-02-06T23:59:59Z\""

}

✅ Response

Json

{

  "status": "pending",

  "message": "An email will be sent to qa@abc.com.hk when the CSV is ready to download."

}

HTTP Status: 202 Accepted

📬 What This Means

202 → Export request accepted.

The response message clearly shows which email will receive the CSV.

The Bearer Token (API Key) used determines:

The SendGrid account/subuser

The email that gets the notification

Permission scope

🏢 OIC Tip

Store API key in Vault, log the response, and extract the email dynamically for tracking.

Simple, automated, and no need to check SendGrid UI manually. 🚀

OIC - Using OCI Functions to Resolve Redirect URLs for Oracle Integration Cloud (OIC)

Introduction

Oracle Integration Cloud (OIC) is great at orchestrating APIs, but it has a limitation when dealing with HTTP redirects. Many external services return shortened or deflected URLs that internally redirect multiple times before reaching the final destination URL.

OIC REST Adapter does not reliably expose the final redirected URL in such cases. This becomes a blocker when downstream logic depends on extracting values (like UUIDs, tokens, or IDs) from the final URL.

To solve this, we can use OCI Functions as a lightweight middleware to resolve redirects and return the final URL back to OIC.

Problem Statement (Use Case)

OIC invokes an external API (for example: email body from Microsoft Graph, SendGrid, or any notification service). The response contains a short or redirected URL. OIC needs the final resolved URL to:

Extract a UUID or parameter

Call another API

Perform validation or tracking

OIC REST Adapter does not provide the final redirected URL easily.

Solution Overview

We introduce OCI Functions between OIC and the redirected URL.

High-Level Flow

OIC extracts the redirected/short URL from API response.

OIC calls an OCI Function, passing the URL as input.

OCI Function:

Follows HTTP redirects

Resolves the final destination URL

OCI Function returns the final URL to OIC.

OIC continues processing (UUID extraction, API calls, etc.).

Architecture Diagram (Logical):

OIC → OCI Function → External Redirect URL

      ↑                     ↓

      └──── Final URL ──────┘

OCI Function Implementation

You can use Java, Node.js or Python. Below I have used Java code.

package com.clp.fn;

import java.net.URI;

import java.net.URLDecoder;

import java.net.http.HttpClient;

import java.net.http.HttpRequest;

import java.net.http.HttpResponse;

import java.nio.charset.StandardCharsets;

import java.time.Duration;

import java.util.Optional;

import java.util.logging.Level;

import java.util.logging.Logger;

public class SendGridRedirectResolver {

private static final Logger logger =

Logger.getLogger (SendGridRedirectResolver.class.getName());

private static final int MAX_REDIRECTS = 10;

private static final HttpClient CLIENT = HttpClient.newBuilder()

.connectTimeout (Duration.ofSeconds (60))

.followRedirects (HttpClient.Redirect.NEVER)

.build();

//DTOS (Modern Java Records)

public static class Input {

public String input_url;

}

public static class Result {

public String output_url;

public String uuid;

public String executionInfo;

}

// Entry method

public Result handleRequest (Input input) {

Result result = new Result();

try {

logger.log(Level.INFO, "OIC-Input URL: {0}", input.input_url);

System.out.println("OIC-Input URL: " + input.input_url);

String decodedUrl = extractFromUrlDefense (input.input_url);

logger.log (Level.INFO, "OIC-Decoded SendGrid URL: (0)", decodedUrl); 

System.out.println("OIC-Decoded SendGrid URL : " + decodedUrl);

String finalUrl = resolveFinalUrl (decodedUrl);

logger.log (Level.INFO, "OIC-Final URL: (0)", finalUrl);

System.out.println("OIC-Final URL:: " + finalUrl);

result.output_url = finalUrl;

result.uuid = "";

result.executionInfo = "SUCCESS";

return result;

} catch (Exception e) {

result.executionInfo = e.getMessage();

logger.log (Level.SEVERE, "Processing failed", e);

System.out.println("OIC- Exception: "+ e);

return result;

}

}

// Core Logic

private static String extractFromUrlDefense (String url) {

int start = url.indexOf("/__");

int end = url.indexOf("__;");

if (start < 0 || end < 0 || end <= start + 3) {

throw new IllegalArgumentException("Invalid urldefense URL");

}

String encoded = url.substring(start + 3, end);

return URLDecoder.decode (encoded, StandardCharsets.UTF_8);

}

private static String resolveFinalUrl (String url) throws Exception {

URI current = URI.create(url);

for (int i = 0; i < MAX_REDIRECTS; i++) {

HttpRequest request = HttpRequest.newBuilder (current) .timeout(Duration.ofSeconds (60))

.GET()

.build();

HttpResponse<Void> response =

CLIENT.send(request, HttpResponse.BodyHandlers.discarding());

int status = response.statusCode();

if (status >= 300 && status < 400) {

Optional<String> location =

response.headers().firstValue("Location");

if (location.isEmpty()) {

throw new RuntimeException("Redirect without Location header");

}

current = current.resolve(location.get());

} else {

return current.toString();

}

}

throw new RuntimeException("Too many redirects");

}

// Standalone Test

public static void main(String[] args) {

String urlDefense =

"Paste your redirect url here";

Input input = new Input();

input.input_url = urlDefense;

SendGridRedirectResolver resolver = new SendGridRedirectResolver();

Result result = resolver.handleRequest(input);

System.out.println("output_url:" + result.output_url);

System.out.println("uuid:" + result.uuid);

}

}

Code link:

https://drive.google.com/file/d/1rXIeIGJ7QjhfUBau5OwXDsye4EKUYo17/view?usp=drivesdk

Snap of codes:

Deploying OCI Function

• Create an OCI Function Application
• Deploy the function using Fn CLI
• Expose it via OCI API Gateway
• Secure it using:
• OCI IAM
• API Key or OCI Resource Principal
• Calling OCI Function from OIC

Steps in OIC

Create a REST Adapter integration

Configure API Gateway endpoint

Pass the redirected URL as request payload

Receive response:

Json

{

  "finalUrl": "https://example.com/path/uuid/12345"

}

Extracting Required Data in OIC

Once OIC receives the final URL:

Extract UUID or parameters

Continue downstream orchestration

Example:

substring-after(finalUrl, '/uuid/')

Benefits of This Approach

✅ Overcomes OIC redirect limitations

✅ Clean separation of concerns

✅ Serverless and cost-effective

✅ Reusable across multiple integrations

✅ Easy to maintain and enhance

Real-World Scenarios

Email tracking links (SendGrid, Twilio)

Microsoft Graph email body links

Payment gateway redirect URLs

Identity verification flows

Short URL expansion

Conclusion

Using OCI Functions as a redirect resolver is a simple yet powerful pattern when working with OIC. It keeps integrations clean, avoids complex workarounds, and provides full control over HTTP behavior.

If your OIC flow depends on the final destination URL, this approach is highly recommended.

Reference online tool java code run:

https://www.onlinegdb.com/online_java_compiler

OIC - OIC Deployment Challenge: How to Avoid Manually Deselecting Integrations Every Time

Problem Statement

While creating a Deployment in Oracle Integration Cloud (OIC), the system automatically selects all integrations by default.

In real projects, we usually need to:

Deploy only a subset of integrations

Exclude unused, POC, or unrelated flows

However, OIC forces us to manually deselect dozens of integrations every single time, which is:

❌ Annoying

❌ Time-consuming

❌ Error-prone (easy to miss one)

This becomes especially painful when:

The environment has 50+ integrations

Frequent exports are required (DEV → TEST → PROD)

Use Case

You want to export only specific integrations from OIC for:

Environment promotion

Backup

Partial deployment

But during Create Deployment, OIC:

Auto-selects all integrations

Requires manual deselection every time

Observed Limitation in OIC

OIC does not provide an option like:

“Select none by default”

“Remember previous selection”

Latest versions are always auto-selected

The Workaround (Proven & Simple) ✅

Here’s the trick that saves a LOT of time 👇

Solution Steps

Step 1: Create a Deployment (Initial Attempt)

• Navigate to Integrations → Packages / Deployments 
• Click Create Deployment
• Enter:
• Name
• Identifier
• Proceed to Choose Integrations
• You’ll see all integrations selected by default

Step 2: Do NOT Save — Discard Instead ❌

• Click Back
• OIC prompts:
• “There are unsaved changes. Do you want to save before exiting?”
• Click Discard

👉 This is the key step

Step 3: Reopen the Same Deployment 🧠

Open the same deployment again

Go to Choose Integrations

✨ Magic happens here:

All integrations are now deselected

You start with a clean slate

Step 4: Select Only Required Integrations ✅

Select only the needed integrations

Click Save

Deployment is now:

Clean

Accurate

Ready for export

Result / Benefits

✅ No more bulk deselection

✅ Faster deployment creation

✅ Reduced human error

✅ Much smoother export process

✅ Works reliably across environments

Key Takeaway

Although OIC doesn’t natively support selective deployment defaults, this discard-and-reopen workaround is a practical and efficient solution for real-world projects.

Microsoft Excel - Working with Excel Text-Based Function

📊 Working with Excel Text-Based Functions

A Practical Guide to LEFT, RIGHT, MID, LEN, SEARCH & CONCAT excel functions

In day-to-day work, Excel is not just about numbers. Very often we deal with text data—emails, IDs, file names, codes, descriptions, etc.

Microsoft Excel provides powerful text-based functions to extract, search, and combine text efficiently.

In this blog, we’ll explore the most commonly used Excel text functions with simple examples and real-life use cases.

🔹 1. LEFT() Function

Purpose: Extracts a specified number of characters from the left side of a text string.

Syntax: LEFT(text, num_chars)

Example:

=LEFT("ORACLEOIC", 6)

Result: ORACLE

Use Case:

Extract country codes

Get prefixes from employee IDs

Read file name initials

🔹 2. RIGHT() Function

Purpose: Extracts characters from the right side of a text string.

Syntax: RIGHT(text, num_chars)

Example:

=RIGHT("INV_2026", 4)

Result: 2026

Use Case:

Extract year from invoice numbers

Get last digits of mobile numbers

Read file extensions

🔹 3. MID() Function

Purpose: Extracts text from the middle of a string.

Syntax: MID(text, start_num, num_chars)

Example:

=MID("EMP-12345-IND", 5, 5)

Result: 12345

Use Case:

Extract employee or order IDs

Parse structured codes

Read values between delimiters

🔹 4. LEN() Function

Purpose:

Returns the total number of characters in a text string (including spaces).

Syntax: LEN(text)

Example:

=LEN("Excel Functions")

Result: 15

Use Case:

Validate text length

Dynamically calculate MID / RIGHT values

Detect extra spaces

🔹 5. SEARCH() Function

Purpose:

Finds the position of a character or word within text (not case-sensitive).

Syntax: SEARCH(find_text, within_text)

Example:

=SEARCH("@", "user.name@gmail.com")

Result: 10

Use Case:

Locate special characters (@, -, _)

Split emails or file names

Dynamic text extraction

🔹 6. CONCAT / CONCATENATE Function

Purpose:

Joins multiple text strings into one.

Syntax (Modern Excel): CONCATENATE(text1, text2, ...)

Example:

=CONCATENATE("Oracle", " ", "Integration", " ", "Cloud")

Result:

Oracle Integration Cloud

Use Case:

Combine first & last names

Build dynamic messages

Create file names or IDs

🔹 7. Real-Life Combined Example (Most Important)

🎯 Extract Domain Name from Email ID

Email: john.doe@company.com

Formula:

=MID(A1, SEARCH("@", A1) + 1, LEN(A1))

Result:

company.com

👉 This example shows the real power of Excel, where multiple text functions work together.

✅ Why These Functions Matter

Save manual effort

Avoid data errors

Make formulas dynamic

Essential for reporting, automation & integration work

Whether you’re working in finance, HR, IT, ERP, or integrations, these text functions are absolute must-knows.

OIC - How to Schedule an OIC Integration to Run at 9:15 AM for the First 10 Days of Every Month Using iCal

🔍 Use Case

In Oracle Integration Cloud (OIC), there are scenarios where an integration must run only during a specific part of the month, instead of daily or monthly as a whole.

Example Scenario:

Run a scheduled integration

Execute every day at 9:15 AM

Only for Day 1 to Day 10 of each month

Used for:

Early-month payroll validations

Monthly reports generation

First-10-days billing or reconciliation jobs

The Simple scheduler does not support such complex patterns, so we use iCal expressions.

🛠️ Solution Approach (Using iCal Recurrence)

Oracle OIC supports iCal-based scheduling, which allows precise control over:

Frequency

Monthly - 1 to 10 days

Time - 9:15 AM

✅ iCal Expression to Use

FREQ=MONTHLY;BYMONTHDAY=1,2,3,4,5,6,7,8,9,10;BYHOUR=9;BYMINUTE=15;

🧭 Step-by-Step Configuration in OIC

Go to Integrations >> Schedules >> Open your scheduled integration >> Click Edit Schedule >> Select Define Recurrence → iCal >> Paste the iCal expression:

FREQ=MONTHLY;BYMONTHDAY=1,2,3,4,5,6,7,8,9,10;BYHOUR=9;BYMINUTE=15;

Click Validate Expression

Set:

Start Date (e.g., beginning of the month)

Time Zone (important for correct execution)

Save and Start the Schedule

⏱️ Execution Behavior

Runs daily at 9:15 AM

Executes only from 1st to 10th day

Automatically repeats every month

No manual intervention required