OIC - How to retrieve previous node value using preceding-sibling in OIC XSLT

Retrieving the Previous Node Value in OIC XSLT: Ensuring RT Records Have D1 as the Previous Record

Use Case: RT Record Validation in Oracle Integration Cloud (OIC)

In Oracle Integration Cloud (OIC), performing sequence-based validation in XML transformations is essential to maintain data integrity. One such scenario requires verifying that every RT record is immediately preceded by a D1 record.

Validation Rules:

• If a record starts with "RT", the immediately preceding record must start with "D1".
• If an "RT" record does not have a preceding "D1", a structure validation error will be thrown to ensure data correctness.

This validation helps enforce the required sequence before further processing, preventing incorrect or incomplete data from being processed in OIC.

Json used for writing a validation messages using stage:

{

    "Validation" : [{

        "Message" : "XXXXXXXXXXXX"

    }]

}

Solution steps:

1. For each record
2. Check if it is record starting with RT.  substring(ns:23FileData, 1,2) = 'RT'
3. Check the preceding node not starting with D1,  substring ($ReadRawSourceFile/nsmpr0:Read Response/ns23:FileRowSet/ns23:FileRow[(ns23:FileData = current()/ns23:FileData)]/preceding-sibling::ns23:FileRow[1.0]/ns23:FileData, 1, 2) !="D1"
4. validation error message: dvm:lookupValue("File_Validation", "key","V25","ValidarionMessage","Invalid File")

Detailed screenshots:

Similarly, to get next sibling node. We can use below xslt construct.

"following-sibling::*[1]"

OIC - How to Retrieve Email Attachments Using Microsoft Graph API: Automating Payment File Processing

Retrieving Email Attachments Using Microsoft Graph API: Automating Payment File Storage in Object Storage

Use Case:
A finance team needs to automate the extraction of payment files from vendor emails in Outlook and securely store them in an object storage solution (e.g., AWS S3, Azure Blob Storage, or Oracle Object Storage). Using Microsoft Graph API, they can programmatically fetch email attachments and upload them to object storage for secure access, processing, and archival.

HighLevel steps:

1. Create a rest connection using Microsoft Graph APIs.
2. Get Email unread Messages filtering on Subject.
3. If no message with attachment found, throw a fault.
4. For each message, 
1. check if it has the attachment.
2. Get list of attachments
3. For each attachment,
1. Upload file to Object storage for further processing.

Graph APIs used:

Connection URL: https://graph.microsoft.com/v1.0

Scope: https://graph.microsoft.com/.default

Get messages: /users/{userPrincipalName}/messages

List attachments: /users/{userPrincipalName}/mailFolders/{mailFolderId}/messages/{messageId}/attachments

Upload to object storage: /n/{namespacename}/b/{bucketname}/o/{objectname}

Detailed steps with screenshots:

Create rest connection:

Get Email  unread Messages for a matching subject.

Mapping:

If no message with attachment found:

For each message check if it has attachment

Get list of attachments:

For each attachment, upload content to Object storage

Reference:

https://learn.microsoft.com/en-us/graph/api/message-get?view=graph-rest-1.0&tabs=http

OIC - Moving a File using FTP connection now throws IO operation failed error

Error usecase:

Moving a File using FTP connection now throws IO operation failed error.

The integration has been working for weeks, with a file move operation from source directory to target directory in SFTP connection, but now has started throwing the below error.

The directory permissions were checked and file owner and all seems correct.

ERROR

The I0 operation failed.

The I0 operation failed.

The "OPER [NOOP] [UseNativeRename (S->R, T->R)]" I0 operation for "<Directory>/<Filename>" failed.

Check the error stack and fix the cause of the error.

Solution:

The file was already present in the system and was not overwritten. 

However, select the overwrite option while configuring the adapter.

OIC - How to Skip OIC Schedule During Public Holidays

Skipping OIC Schedule During Public Holidays

Use Case:

In Oracle Integration Cloud (OIC), scheduled integrations run at predefined intervals. However, organizations may need to prevent these jobs from executing on public holidays to optimize resources and avoid unnecessary processing.

Example Scenario

A company has a daily OIC integration that syncs employee attendance data from an external system to Oracle HCM. Since no employees work on public holidays, the integration should be skipped on those days.

Solution Approach: Using an OIC Lookup Table

Steps to Implement:

1. Create a Lookup Table in OIC:

   • Go to Lookups in OIC and create a new lookup table.
   • Define two columns: HolidayDate and Flag
   • Populate the lookup with public holidays for the year.

2. Modify the Scheduled Integration:

   • Retrieve the current date in the integration.
   • Check the lookup table to see if today’s date exists.
   • If today is a holiday:
     • Log a message: "Skipping execution due to public holiday."
     • Exit the integration.
   • If today is not a holiday, proceed with execution.

3. Update the Lookup Table Annually:

   • At the start of each year, update the lookup table with the new list of public holidays.

Detailed screenshots:

If there is a need to run the integration ad hoc on a public holiday in certain cases, we can introduce a schedule parameter IsReprocess. If IsReprocess is set to "Y", the integration will run regardless of the holiday. Otherwise, it will check the lookup table and skip execution if today is a public holiday.

Conclusion

By using an OIC lookup table, scheduled integrations can dynamically check for public holidays and skip execution when necessary. This approach ensures efficient resource utilization and prevents unnecessary API calls.

OIC - Delete file from OCI Object storage bucket using rest connection

Business requirement:

In Oracle Integration Cloud (OIC), there is a need to automate the deletion of files from an OCI Object Storage bucket. This is required when files are no longer needed or after successful processing, ensuring optimized storage usage and compliance with data retention policies

Rest API endpoint for deleting an object in the object storage bucket:

DELETE https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket-name>/o/<file-name>

Detailed screenshots:

Object storage rest connection:

Adapter configuration

Map bucket name, namespace and object name

OIC - Automating SFTP File Listing in OIC Based on Dynamic Date Filters and Adhoc Runs for past date files

Use Case:

In Oracle Integration Cloud (OIC), we need to list files from an source SFTP server based on a specific filename pattern and a date in the format YYYYMMDD. This date should be passed as a parameter to support both:

1. Scheduled Runs – Using the current date dynamically.
2. Ad-hoc or Past Date Processing – Allowing users to specify any past date manually.

This flexibility ensures that integrations can process files efficiently, whether through automated schedules or on-demand executions.

Date validation condition:

 (((FileProcessingDate = 'YYYYMMDD') or (FileProcessingDate = '')) or (FileProcessingDate = format-dateTime(concat(substring(FileProcessingDate, 1, 4), '-', substring(FileProcessingDate, 5, 2), '-', substring(FileProcessingDate, 7, 2)), '[Y0001][M01][D01]')))

File pattern population logic:

When processingdate != 'YYYYMMDD' and  processingdate != ''

replace(lookupValue("Common_CCS_Interface_SFTP_Lookup", "IntegrationId", Var_InterfaceId, "SourceFileName", "CTFCLPMC*"), "YYYYMMDD", FileProcessingDate)

Otherwise:

replace(lookupValue("Common_CCS_Interface_SFTP_Lookup", "IntegrationId", Var_InterfaceId, "SourceFileName", "CTFCLPMC*"), "YYYYMMDD", format-dateTime(current-dateTime(), "[Y0001][M01][D01]"))

Solution Steps:

1. Create a Scheduled Orchestration integration pattern to enable both scheduled and manual triggers.
2. Define a schedule parameter fileprocesingDate (YYYYMMDD format) to accept the date dynamically.
3. Validation: add a throw new fault and put the skip condition, if invalid date, it will throw error. Skip conditions:
1. Procrssing date is YYYYMMDD 
2. Processing date is empty
3. File processing is in YYYYMMDD format. 
4. Configure the sftp adapter and choose list files operations and put the following branching to populate the right file name pattern.
1. When processingdate is not YYYYMMDD and not empty, take file name part and current date from the schedule parameter for adhoc run.
2. Otherwise, take the name and current date for automatic processing.
5. Take the list of files and process one by one using a for each loop
6. If no files found, send a notication or log an appropriate message.

Testing & Deployment

• Test with multiple scenarios:
  • Scheduled execution with the current date.
  • Manual execution with a specific past date.
  • Scenarios where no matching files exist.
• Deploy the integration and configure scheduling as per business needs.

Detailed screenshots:

OIC - Solution for 404 Not Found Error When Calling a Local Integration dynamically in OIC project

Issue: A 404 Not Found error occurs when dynamically calling a local integration within a project in OIC.

Error details:

CloudInvocationException (Fault Details:

 <n30:type/>

<n30:APIInvocationError xmlns:n30="http://xmlns.oracle.com/cloud/generic/rest/fault/collocatedics/LocalIntegration CallNextIntegration">

<n30:title/>

 <n30:detail/>

 <n30:errorCode/>

 <na:errorDetails>>

<n30:type>http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.5</ns0:type>

 <n30:title>Not Found</ns0:title>

<ns:errorCode>404</ns):errorCode>

<nao:errorPath>&lt;! [CDATA[Inbound JaxrsResponse(context=ClientResponse(method=POST, uri=http://rest-adapter:8080/ic/api/integration/vi/flows/rest/INT324_HCM_CCS_NEWHIR MAIN/1.0/NewHireEmpElecAllow/main, status=404, reason Not Found)}]] &gt;</n30:errorPath>

<n30:instance>&lt;! [CDATA[Please check the URL and try again. There are no currently active integrations serving /ic/api/integration/vi/flows/rest/INT324_HCM_CCS_NEWHIRMAIN/1.0/NewHireEmpElecAllow/main. The HTTP 404, 404 Not Found, and 404 error message is a Hypertext Transfer Protocol (HTTP) standard response code, in computer network communications, to indicate that the client was able to communicate with a given server, but the server could not find the resource that was requested. Carefully re-examine the target endpoint that is being called. )&gt;</n30:instance></nao:errorDetails></n50: APIInvocationError> ] 

Solution:

Ensure that the Project Code, Integration Code, and Version are correctly mapped in the invoke action using a Lookup.

Steps:

1. Open the integration where the local call is being made.
2. Use a Lookup to dynamically fetch the Project Code, Integration Code, and Version.
3. Ensure the endpoint URL is correctly formed using these values.
4. Verify that the lookup values match the actual integration details in the project.
5. Save, activate, and test the integration again.

This ensures dynamic mapping and avoids hardcoded values, resolving the 404 error.

OIC - Oauth initiation error at runtime - Access token is not available. Please re-initiate the oauth flow to obtain an access token | Provide consent issue.

Use Case: OAuth Authentication Failure in Oracle Integration Cloud (OIC)

Scenario:

An OIC integration flow that connects to an external REST API using OAuth authentication encounters an error:

Error Code: REST_AUTH_INIT_ERR

Error Message: "OAuth initiation error at runtime. Access token is not available. Please re-initiate the OAuth flow to obtain an access token. This indicates that OIC is unable to obtain or refresh the OAuth access token, leading to authentication failure.

Detailed Error Details:

<fault xmlns="http://xmlns.oracle.com/cloud/oic/gen3fault"> <trace Id>42c1b064a7ee002c7fd445e94f7429cc</traceId><requestId>IFIKKU95FTG26XUAYGUIOGA962D61JU2/03E977E96A93EE7EBDCE38C05CA6119E/3760E5ED3B4A4E18AA92084886501BC9</requestId><errorId/><flowId>UUzyG9_VEe-qZTGEjn7VoQ </flowld><errorType>InternalError</errorType><origin>tech-adapters-7569567778-z8qrn</origin><errorCode>REST_AUTH_INIT_ERR</errorCode><faultName> ns0: Server/faultName><retriable>false</retriable><reason>OAuth initiation error at runtime.</reason><details> <details minn="">Fault Details: <genericRestFault><errorCode>REST_AUTH_INIT_ERR</errorCode><error Path>Access token is not available. Please re-initiate the oauth flow to obtain an access token.</errorPath><instance>OAuth initiation error at runtime.</instance></genericRestFault></detailn></details></fault>

Solution steps:

1. Verify & Test Connection

Go to OIC Home → Integrations → Connections and test the REST connection.

If it fails, re-enter the Client ID, Client Secret, and Token URL.

2. Provide OAuth Consent

Log in to the OIC console using a user account with the necessary permissions to provide consent. Edit the connection causing the issue, click on Provide Consent, and it should display as authenticated.

3. Reactivate the integration.