OIC - ERP - Resolving ORA-01403: No Data Found During ERP Import Costs ESS Job

Overview

While executing the Import Costs ESS job in Oracle ERP, the process failed with the following error:

Error:

ORA-01403: no data found

This error can be misleading because it doesn't always indicate missing transactional data. In our case, the issue was caused by incorrect parameter values being passed to the ESS job from Oracle Integration Cloud (OIC).

Error Scenario

The Import Costs ESS job completed with Error status, and the completion text displayed:

ORA-01403: no data found

As shown below:

Root Cause

The integration was retrieving ESS job parameters from a lookup table. During troubleshooting, we found that one or more parameter values stored in the lookup were incorrect.

Since the ESS job received invalid parameters, it couldn't find the expected data and returned the ORA-01403: no data found error.

Resolution

The issue was resolved by following these steps:

1. Reviewed the parameters passed to the Import Costs ESS job.
2. Compared them with the expected ERP values.
3. Identified incorrect values in the OIC lookup.
4. Updated the lookup with the correct parameter values.
5. Re-ran the integration.
6. After updating the lookup, the ESS job completed successfully without any errors.

Key Learning

When an ESS job fails with ORA-01403: no data found, don't assume the source data is missing. Also verify:

• The parameters passed to the ESS job.
• Lookup values used by the integration.
• Parameter mapping in OIC.
• Whether the parameter values match the ERP configuration.

Troubleshooting Checklist

• ✅ Verify all ESS job parameters.
• ✅ Check OIC lookup values.
• ✅ Validate parameter mapping before submitting the ESS job.
• ✅ Compare successful and failed job parameters.
• ✅ Re-run the ESS job after correcting the parameters.

Conclusion

In this case, the root cause was incorrect parameter values stored in the OIC lookup, not missing ERP data. Updating the lookup with the correct values resolved the ORA-01403: no data found error, and the Import Costs ESS job executed successfully.

Tip: Whenever an ERP ESS job throws ORA-01403, reviewing the submitted parameters should be one of the first troubleshooting steps, especially if those parameters are dynamically fetched from an OIC lookup or configuration table.

OIC Accelerator Upgrade: Reviewing Merge Reports and Resolving Unmerged Extensions

Working...

When upgrading an Oracle Integration Cloud (OIC) Accelerator, Oracle automatically merges the latest accelerator changes with your customized extensions. While most changes are merged successfully, some extensions may require manual intervention due to conflicts or customizations.

The Merge Report provides a consolidated view of all merged and unmerged extensions, allowing developers to quickly identify and resolve any outstanding issues before completing the upgrade.

This blog explains how to access the Merge Report from the Integration Palette and review each unmerged extension.

Why Review the Merge Report?

After an accelerator upgrade, reviewing the Merge Report helps you:

• Verify which extensions were merged successfully.
• Identify extensions that require manual review.
• Understand the reason behind merge conflicts.
• Ensure all customizations are retained after the upgrade.
• Validate the integration before deployment.

Steps to Review the Merge Report

Step 1: Open the Upgraded Integration

Open the upgraded integration in the OIC Designer.

Step 2: Open the Integration Palette

From the left-side panel, open the Integration Palette.

Navigate to the Extensions section.

Step 3: View the Merge Report

Select Merge Report from the Integration Palette.

The report displays:

Successfully merged extensions

Partially merged extensions

Unmerged extensions requiring manual resolution

This provides an overview of the upgrade status for all extensions.

Step 4: Review Each Unmerged Extension

Expand each unmerged extension listed in the report.

For every extension, review:

• The affected integration component
• The merge status
• The reason for the conflict
• Any customizations that were not merged automatically

This helps determine the manual changes required.

Step 5: Resolve the Unmerged Changes

Open the corresponding extension and compare it with the original implementation.

Review components such as:

Mappings (XSLT)

Variables

Assign actions

Switch conditions

Invoke activities

Fault handlers

Connections and adapter configurations

Apply the required changes manually while preserving both the accelerator updates and your customizations.

Step 6: Validate and Test

Once all unmerged extensions have been reviewed and resolved:

• Validate the integration.
• Resolve any validation or mapping errors.
• Perform functional testing.
• Activate the integration after successful validation.

Best Practices

• Always review the Merge Report immediately after an accelerator upgrade.
• Resolve all unmerged extensions before deployment.
• Compare customizations carefully to avoid losing business logic.
• Validate the integration after every manual update.
• Test all impacted integration flows before promoting to higher environments.

Conclusion

The Merge Report is an essential tool during OIC Accelerator upgrades. It provides a centralized view of merged and unmerged extensions, enabling developers to quickly identify areas requiring manual attention. By reviewing each unmerged extension and validating the integration thoroughly, you can ensure a smooth upgrade while preserving existing customizations and minimizing deployment risks.

OIC - Oracle Integration Cloud (OIC) – Upload File to UCM, Submit ESS Job, and Monitor Job Status

A common Oracle ERP Cloud integration pattern is to upload a file to UCM, submit an ESS job, and then poll the ESS job status until it completes successfully. This pattern is widely used for payment processing, FBDI imports, bank acknowledgements, and other batch integrations.

Business Requirement

Suppose an external system generates a file (CSV/XML/TXT) that needs to be processed by Oracle ERP Cloud.

The integration should:

1. Upload the file into Oracle ERP Cloud UCM.
2. Submit the required ESS job.
3. Wait for the job to finish.
4. Check the ESS job status.
5. Continue processing only when the job completes successfully.

Integration Flow

External System

      │

      ▼

Receive File

      │

      ▼

Upload File to UCM

      │

      ▼

Receive Content ID

      │

      ▼

Submit ESS Job

      │

      ▼

Receive Request ID

      │

      ▼

Loop Until Completion

      │

      ▼

Get ESS Job Status

      │

      ▼

Success / Error Handling

Step 1 – Upload File to UCM

Configure the Oracle ERP Cloud Adapter and select:

Operation: File Upload to WebCenter (UCM)

Example values:

Account: FAFusionImportExport

UCM Folder: fin$/payments$/import$

After upload, Oracle returns a Content ID, which is required by many ESS jobs.

Step 2 – Submit ESS Job

Invoke another ERP Cloud Adapter.

Choose: Submit ESS Job

Provide:

Job Package Name: /oracle/apps/ess/financials/payments/fundsDisbursement/payments/

Job Definition Name: FDAckProcessing

Parameters: <referenceNumber>

Example ESS Job Path:

/oracle/apps/ess/financials/payments/fundsDisbursement/payments/FDAckProcessing

The adapter returns an ESS Request ID.

Example:

Request ID : 300000458912345

Step 3 – Wait Before Polling

Instead of checking immediately, add a Wait activity.

Example:

Wait 20–30 seconds

This avoids unnecessary API calls while Oracle begins processing the job.

Step 4 – Check ESS Job Status

Use a While scope.

Pseudo logic: While Status != SUCCEEDED

   Wait 20 Seconds

   Get ESS Job Status : Pass the Request ID returned during job submission.

End While

If the status becomes:

SUCCEEDED → Continue integration.

ERROR → Raise fault.

WARNING → Handle based on business requirement.

Typical statuses include:

Status Meaning

WAIT Waiting to start

READY Ready for execution

RUNNING Currently executing

SUCCEEDED Completed successfully

ERROR Failed

WARNING Completed with warnings

When to use Upload File to UCM:

You are uploading a payment file or bank file.

You need to run a specific ESS job yourself after the upload.

The file is not a standard Oracle FBDI import.

You need full control over the sequence (Upload → ESS Job → Poll Status).

Example:

Upload payment acknowledgment file.

Upload HSBC bank file.

Upload custom XML/CSV.

Upload a file and then run FDAckProcessing ESS job.

When to use Bulk Import:

You are importing Oracle ERP business data using FBDI.

Oracle ERP supports that object through the Bulk Import operation.

You want Oracle to manage the import process for that business object.

Examples:

Supplier Import

Customer Import

GL Journal Import

AP Invoice Import

Asset Import

Item Import

Conclusion

Uploading a file to UCM, submitting an ESS job, and continuously monitoring the job status is one of the most common Oracle Integration Cloud design patterns. By combining the ERP Cloud Adapter, Wait, and While activities, you can build a robust integration that ensures downstream processing starts only after the ERP job has successfully completed.

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.