OIC - Oracle Integration Cloud (OIC): Configuring BI Publisher Data Model to Return DATA_DS, Empty XML Tags, and CSV Output

When integrating Oracle ERP Cloud reports with Oracle Integration Cloud (OIC), you may encounter situations where BI Publisher omits empty XML elements. This can create issues in OIC because expected XML nodes are missing, making mappings and XPath expressions fail.

This blog explains how to configure your BI Publisher Data Model so that:

The response contains the <DATA_DS> root tag instead of only column headers.

Blank fields are returned as empty XML tags (for example, <STATUS/>) instead of being omitted.

CSV output is enabled if required.

Problem Statement

While calling a BI Publisher report from OIC:

The XML response may contain only headers or omit the <DATA_DS> section when no data is returned.

Columns with NULL values are not included in the XML payload.

Missing XML elements can cause integration mappings, stage file processing, or downstream applications to fail.

Solution

Open the BI Publisher Data Model and configure the following properties.

1. Enable CSV Output

Check:

✅ Enable CSV Output

This allows the report to generate CSV output when required by downstream integrations.

2. Include Empty XML Tags

Under XML Output Options, enable:

✅ Include Empty Tags for Null Elements

✅ Include Open & Close Tags

These settings ensure that if a database column contains a NULL value, BI Publisher still generates the XML element.

Before

<DATA_DS>

   <G_1>

      <REQUEST_ID>12345</REQUEST_ID>

   </G_1>

</DATA_DS>

After

<DATA_DS>

   <G_1>

      <REQUEST_ID>12345</REQUEST_ID>

      <STATUS/>

      <ERROR_MESSAGE/>

   </G_1>

</DATA_DS>

This makes XML parsing in OIC much more reliable.

Why This Is Important

Using these options provides several benefits:

Ensures a consistent XML structure.

Prevents XPath and mapper failures in OIC.

Simplifies Stage File and XML parsing.

Guarantees expected tags exist even when values are NULL.

Improves compatibility with downstream systems expecting fixed XML schemas.

Configuration Screenshot

The following BI Publisher Data Model settings should be enabled:

Enable CSV Output

Include Empty Tags for Null Elements

Include Open & Close Tags

These options are available under the Properties page of the Data Model, as shown in your screenshot.

Conclusion

If your OIC integration depends on consistent XML elements, configuring the BI Publisher Data Model correctly is essential. By enabling CSV Output and selecting Include Empty Tags for Null Elements along with Include Open & Close Tags, BI Publisher always returns a predictable XML payload with the <DATA_DS> structure and empty tags for NULL values. This greatly improves integration stability and reduces mapping errors in Oracle Integration Cloud.

OIC – Automate OIC Observability Instance Report and Send via Email

Requirement

Build an Oracle Integration Cloud (OIC) integration that fetches OIC Observability / Integration Instance details for a given From Date and To Date, generates a report file, and sends it over email as an attachment.

Use Case

Operations/support teams often need execution details for a specific date range. Instead of manually downloading instance reports from monitoring, this integration automates the process.

Solution Flow

REST Trigger (Input Dates)

→ Call OIC Factory/Monitoring REST API

→ Fetch Instance Details

→ Write Response into File

→ Send Notification Email with Attachment

Step 1: Create REST Trigger Integration

Create an App Driven Orchestration.

Configure trigger request payload:

{

  "MyId": "101",

  "Email": "test@test.com",

  "Query": "{startdate:'2026-01-14 10:00:00',enddate:'2026-01-15 10:00:00'}"

}

Input Parameters:

MyId – Request identifier

Email – Recipient email

Query – Date filter containing:

startdate

enddate

Step 2: Configure OIC Factory / Monitoring Service

Add REST Invoke.

Example configuration:

Endpoint Name

GetOICInstances

Relative URI

/ic/api/integration/v1/monitoring/integrations

Method:

GET

Enable:

Add and review parameters

Configure endpoint to receive response

Step 3: Map Query Parameters

Map the incoming Query field to REST query parameter.

Example:

startdate → request input

enddate → request input

This allows runtime filtering of observability/instance records.

Step 4: Invoke Monitoring API

Construct request dynamically.

Example:

?fromDate=2026-01-14T10:00:00

&toDate=2026-01-15T10:00:00

Expected output:

Integration Name

Instance Id

Status

Start Time

End Time

Execution details

Sample Response:

{

  "items": [

    {

      "code": "CUSTOMER_COMMON_01|DVS_RTP|01.0.0000",

      "projectCode": "CUSTOMER_COMMON_01",

      "integrationName": "DVS_RTP",

      "integrationId": "DVS_RTP",

      "integrationVersion": "01.0.0000",

      "scheduled": false,

      "flowStatus": "ACTIVATED",

      "noOfMsgs": 120,

      "noOfMsgsSuccess": 115,

      "noOfMsgsFailed": 3,

      "noOfMsgsAborted": 2,

      "lastUpdated": "2026-06-30T07:45:22.000+0000"

    },

    {

      "code": "COMMON|CLP_COMMON_GET|01.0.0001",

      "projectCode": "COMMON",

      "integrationName": "CLP_COMMON_GET",

      "integrationId": "CLP_COMMON_GET",

      "integrationVersion": "01.0.0001",

      "scheduled": true,

      "flowStatus": "ACTIVATED",

      "noOfMsgs": 580,

      "noOfMsgsSuccess": 570,

      "noOfMsgsFailed": 10,

      "noOfMsgsAborted": 0,

      "lastUpdated": "2026-06-30T08:15:30.000+0000"

    }

  ],

  "totalResults": 2,

  "limit": 100,

  "offset": 0,

  "hasMore": false

}

Step 5: Write Response to Stage File

Add Stage File → Write File.

Example:

Directory:

/tmp

File:

OIC_Instance_Report.json

Write API response into file.

You may also convert JSON → CSV before writing if business users prefer Excel-friendly output.

Step 6: Send Email Notification

Use Notification Action.

Configure:

To → Email input

Subject

OIC Observability Instance Report

Body:

Please find attached OIC instance execution report for requested date range.

Attach generated file from Stage File.

Benefits

Removes manual monitoring effort

Easy reporting for support teams

Date-driven execution

Reusable utility integration for observability reporting

You can additionally enhance this by:

Generating CSV instead of JSON

Scheduling via Scheduled Integration

Uploading reports to UCM/Object Storage before emailing

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/rest-api/op-ic-api-integration-v1-monitoring-integrations-get.html?source=%3Aow%3Ams%3Apt%3A%3A&utm_source=chatgpt.com 

OIC – ERP Event Subscriber Adapter Error: java.io.IOException: Error retrieving access token

Overview

While configuring the Oracle ERP Event Subscriber Adapter in OIC, an error occurred when opening the adapter configuration page.

Observed Error:

GENERIC - java.io.IOException: Error retrieving access token

The issue appeared while subscribing to the ERP event:

Subscribed Event: Payment File Created

Error Snapshot

Oracle ERP Cloud Trigger → Error retrieving access token

Root Cause Analysis

After investigation, we observed that the ERP connection referenced by the integration was not using a local project connection.

Instead, the adapter was pointing to an ERP connection shared from another common project.

Because of this:

Access token generation failed during adapter initialization.

Event metadata could not be loaded.

ERP Event Subscriber configuration screen returned the generic access token error.

Resolution

The issue was resolved by creating and using a local ERP connection inside the current OIC project.

Steps Followed

Open the OIC project where the integration exists.

Create a new Oracle ERP Cloud Connection locally.

Configure authentication and test connectivity.

Replace the shared/common project connection with the newly created local connection.

Reopen the ERP Event Subscriber Adapter configuration.

Result

After switching to the local ERP connection:

ERP Event Subscriber opened successfully.

Event metadata loaded correctly.

Subscription configuration completed without errors.

Key Takeaway

When configuring ERP Event Subscriber Adapter in OIC, if you encounter:

java.io.IOException: Error retrieving access token

verify whether the integration is consuming an ERP connection from another project. Creating and using a local ERP connection may resolve the issue quickly.

OIC – Handling Binary API Response Using following-sibling to Extract Holiday Dates and Update Lookup

 Requirement

We had a requirement in Oracle Integration Cloud (OIC) to maintain a centralized Holiday Lookup.

The source system exposed a Holiday API, but the response was returned in a binary / non-readable format instead of structured JSON/XML.

To process this data, we implemented a common utility flow:

API Response → Read as text line by line → Extract DTSTART dates → Write dates into file → Update OIC Lookup

Solution Overview

Step 1: Call Holiday API

Invoke the external API and receive the holiday calendar response.

The response was received as opaque/binary content, so direct mapping was not possible.

Step 2: Read Binary Response Using Stage File

Use Stage File → Read Entire File / Read in Segments.

Configure:

Read file as text

Read record line by line

This converts the binary payload into readable rows.

Example response:

Plain text

BEGIN:VEVENT

DTSTART:20260101

SUMMARY:New Year

END:VEVENT

BEGIN:VEVENT

DTSTART:20260126

SUMMARY:Republic Day

END:VEVENT

Step 3: Use following-sibling to Fetch DTSTART

While iterating through records, use XPath following-sibling to access the next node and extract only holiday dates.

Sample expression:

Xpath

normalize-space(

 replace(

   replace(

     following-sibling::ns31:ReadRecord[1.0]/ns31:Data,

     '"',

     ''

   ),

   ';',

   ''

 )

)

Explanation

following-sibling::ReadRecord[1] → Reads the next line after current record

replace() → Removes unwanted characters

normalize-space() → Cleans spaces

This helps extract values after detecting DTSTART.

Output:

Plain text

20260101

20260126

Step 4: Write Dates into File

Use Stage File – Write File action.

Generated output:

Csv

HOLIDAY_DATE

20260101

20260126

Step 5: Update OIC Lookup

Read the generated file and update the centralized Holiday Lookup.

Lookup example:

HolidayDate

IsHoliday

20260101

Y

20260126

Y

Final Flow

Plain text

Scheduler

   ↓

Call Holiday API

   ↓

Read Opaque/Binary Response

   ↓

Read Records Line by Line

   ↓

Use following-sibling to Extract DTSTART

   ↓

Write Stage File

   ↓

Update Lookup

Benefits

Reusable common holiday service

Centralized holiday maintenance

Avoids duplicate holiday validation logic

Supports binary/non-readable API responses efficiently in OIC

This approach allowed us to convert an unreadable API response into usable holiday dates and maintain a common lookup for all downstream integrations.

OIC Common Utility Service – Holiday Calendar Validation Using Dynamic Lookup Update

Overview

In many Oracle Integration Cloud (OIC) implementations, multiple integrations need to validate whether a given date is a holiday. Instead of maintaining holiday logic separately in every integration, we created a reusable common utility framework.

The solution contains two common services:

• Holiday Lookup Update Service – Updates the Holiday Lookup dynamically using OIC REST API.
• Holiday Validation Service – Accepts a date and validates whether the date exists in the Holiday Lookup and returns Yes/No.

This approach centralizes holiday management and improves reusability across integrations.

Requirement

We needed a common mechanism where:

Holiday dates are maintained centrally in an OIC Lookup.

Holiday lookup values can be updated dynamically without manual UI updates.

Multiple integrations can call a common validation service.

Validation response returns whether the passed date is a holiday.

Solution Architecture

 Flow1: Source System / Scheduler

          ▼

Common Service 1 – Update Holiday Lookup

          ▼

OIC Lookup (HOLIDAY_LIST)

 Flow2: Individual integrations    

          ▼

Common Service 2 – Validate Holiday

          ▼

Return IsHoliday to caller Integrations

Service 1 – Update Holiday Lookup

Purpose: Updates holiday dates into an OIC Lookup using OIC REST API.

Flow

Trigger

 ↓

Prepare Holiday Payload

 ↓

REST Invoke – Update Lookup API

 ↓

Return Success Response

REST Configuration 

Relative Resource URI: /ic/api/integration/v1/projects/{projectId}/lookups/{name}

Method : PUT

Request Payload Enabled ✔ Configure request payload

No response configuration required.

The API supports updating lookup values inside a project using PUT operation. 

Oracle Docs:

https://docs.oracle.com/en/cloud/paas/application-integration/rest-api/api-integrations-projects-lookups.html

Sample Request Payload

JSON

{

  "name": "HOLIDAY_LIST",

  "columns": [

    "DATE",

    "ISHOLIDAY"

  ],

  "rows": [

    {

      "rowData": [

        "2026-01-01",

        "YES"

      ]

    },

    {

      "rowData": [

        "2026-12-25",

        "YES"

      ]

    }

  ]

}

This updates the lookup with holiday dates and flags. 

Service 2 – Holiday Validation Common Service

Purpose: Reusable service consumed by other integrations.

Request

JSON

{

  "date":"2026-12-25"

}

Validation Logic

Receive Date

 ↓

Read HOLIDAY_LIST Lookup

 ↓

Search Matching Date

 ↓

If Found → YES , Else → NO

Sample Response

Holiday:

JSON

{

 "isHoliday":"YES"

}

Non-Holiday:

JSON

{

 "isHoliday":"NO"

}

Value check from lookup logic:

<xsl:value-of xml:id="id_43" select='dvm:lookupValue( "Common_HK_Holiday_Lookup", "HKHoliday", /nstrgmpr:execute/ns14:request-wrapper/ns14:Request/ns14:Date, "IsHoliday","NO")'/>

Benefits

• Centralized holiday management
• Reusable across integrations
• No manual lookup maintenance
• Faster onboarding of new integrations
• Cleaner architecture with utility services

SOAPUI - SoapUI to OIC Service Call using OAuth 2.0

Overview

This setup allows SoapUI to invoke an Oracle Integration Cloud (OIC) REST integration securely using OAuth 2.0 ( example, Client Credentials) authentication.

Supported Version

OAuth 2.0 support is available in SoapUI Open Source 5.x and later versions. SoapUI provides built-in OAuth 2.0 authorization profiles and supports standard grant types such as:

Authorization Code

Client Credentials

Password Credentials

Implicit Grant

Setup Steps

Configure OIC Integration Security:

Open OIC: Integrations → Open Integration → Trigger → Security

Select: OAuth 2.0

Configure:

Activate the integration.

Generate Access Token in SoapUI

1. Open SoapUI: Request → Auth → Add Authorization → OAuth 2.0
2. Enter: Value
1. Grant Type
2. Client Credentials
3. Access Token URL
4. OAuth Token URL
5. Client ID
6. Client Secret
3. Click: Get Access Token

Note: In the Scope, there should not be any double quotes.

Conclusion

Using SoapUI (5.x and later) with OAuth 2.0 support makes it easy to securely test and invoke OIC REST/SOA services without custom token handling. By configuring the OAuth 2.0 authorization profile, generating the access token, and attaching it to the request, service testing becomes faster, reusable, and aligned with modern authentication standards. For better compatibility and stability, using SoapUI 5.7.0 or later is recommended.

OIC - Automated HSBC Payment Acknowledgement Processing in Oracle Integration Cloud (OIC)

Working...

Overview

In many enterprise payment integrations, payment files generated from Oracle ERP are transmitted to banking partners such as HSBC for payment execution. After processing the payment file, HSBC generates an acknowledgement (ACK) file that must be retrieved and loaded back into Oracle ERP for reconciliation and audit purposes.

This blog describes an OIC-based solution that automates the end-to-end retrieval and processing of HSBC payment acknowledgement files.

Business Requirement

When Oracle ERP sends a payment file to HSBC, the treasury team requires confirmation that the file has been successfully received and processed by the bank.

The solution should:

Retrieve the payment reference number from ERP.

Call HSBC APIs using the reference number.

Download the payment acknowledgement file.

Upload the acknowledgement file into ERP UCM.

Submit an ESS job to import/process the file.

Validate successful processing.

Re-query ERP to confirm the final acknowledgement status.

Solution Architecture

High-Level Flow

OIC Scheduler

      |

      V

Call ERP BIP Report

      |

      V

Get Payment Reference Number

      |

      V

Call HSBC Acknowledgement API

      |

      V

Download ACK File

      |

      V

Upload File to ERP UCM

      |

      V

Submit ESS Job

      |

      V

Monitor ESS Job Status

      |

      V

Call ERP BIP Report Again

      |

      V

Validate Final Processing Status

Integration Design

Step 1: Scheduled Integration Trigger

A scheduled OIC integration runs periodically to identify payment files awaiting acknowledgement processing.

The scheduler frequency can be configured based on business requirements.

Step 2: Retrieve Payment Reference from ERP

The integration invokes a BI Publisher (BIP) report in Oracle ERP.

The report returns:

Payment Process Request (PPR)

Payment Batch Details

Payment Reference Number

Bank Reference Information

The reference number returned by the report is used as the primary identifier for HSBC API calls.

Step 3: Invoke HSBC Acknowledgement API

Using the payment reference number, OIC invokes the HSBC acknowledgement API.

Sample request:

{

  "paymentReference": "REF123456789"

}

HSBC returns:

Processing status

Acknowledgement file

Reference details

Response codes

Step 4: Download Acknowledgement File

If the acknowledgement is available, OIC retrieves the ACK file from HSBC.

Depending on HSBC implementation, the file may be:

XML

CSV

TXT

CAMT format

The file content is stored temporarily within the integration flow.

Step 5: Upload File to ERP UCM

The acknowledgement file is uploaded to Oracle ERP Universal Content Management (UCM).

Typical upload details:

Account: fin$/payables/import$

Document Name

Security Group

Content Type

The UCM document ID is captured for downstream processing.

Step 6: Submit ESS Job

After successful upload, OIC invokes the ERP ESS web service to process the acknowledgement file.

Typical ESS parameters include:

UCM Document ID

File Name

Import Process Name

The ESS Job ID is captured for monitoring.

Step 7: Monitor ESS Job Status

OIC continuously polls the ESS job status until one of the following conditions is reached:

SUCCEEDED

ERROR

WARNING

This can be implemented using:

Wait activity

Scope

While loop

Retry framework

Step 8: Re-Validate Using BIP Report

Once the ESS job completes successfully, OIC invokes the BIP report again.

This validation step confirms:

Acknowledgement processed successfully

Payment status updated

ERP records reconciled correctly

The report serves as a final verification layer before completing the integration.

Error Handling

The integration includes robust exception handling for:

HSBC API Failures

Authentication issues

Network failures

Invalid references

Timeout errors

UCM Upload Failures

Invalid content

UCM connectivity issues

Security permission failures

ESS Processing Failures

Import validation errors

Incorrect file format

ERP processing exceptions

Notifications can be sent through email or monitoring dashboards for failed transactions.

Benefits

End-to-End Automation

Eliminates manual retrieval and upload of acknowledgement files.

Improved Reconciliation

Ensures ERP reflects the actual bank processing status.

Faster Processing

Acknowledgements are processed automatically and available to finance teams sooner.

Enhanced Auditability

Provides complete traceability from payment initiation to acknowledgement confirmation.

Conclusion

This OIC integration automates the complete HSBC payment acknowledgement lifecycle by combining Oracle ERP BI Publisher reports, HSBC APIs, UCM file uploads, and ESS job processing. The additional validation step using a second BIP report ensures that acknowledgement files are successfully processed and reflected in ERP, providing a reliable and auditable payment reconciliation process.