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.

End to End Securing Data in Oracle Integration Cloud (OIC) with OCI Vault: AES for Encryption & Decryption & RSA for Signing and verification | Implement message-level encryption in Oracle Integration using OCI Vault

Scenario:

A company is integrating Oracle Integration Cloud (OIC) with an external financial system to exchange sensitive payroll data securely. The external system encrypts and signs the data before sending it to OIC. OIC must decrypt and verify the data, process it, then encrypt and sign the response before sending it back.

Solution Approach

1. Incoming Data: Decrypt & Verify in OIC

1. The source system encrypts the data using AES-256 (symmetric encryption) and signs it using an RSA private key.
2. OIC receives the encrypted and signed payload.
3. OIC retrieves the AES decryption key and RSA public key from OCI Vault.
4. OIC decrypts the data using AES and verifies the signature using RSA.

2. Outgoing Data: Encrypt & Sign in OIC

1. After processing the data, OIC encrypts the response using AES-256.
2. OIC signs the encrypted data using an RSA private key.
3. The response is sent back to the source system.
4. The source system retrieves the AES key to decrypt the data and verifies the RSA signature using OIC’s public key.

Implementation in OIC with OCI Vault

1. Key Management in OCI Vault

• Store the AES encryption/decryption key for data security.
• Store RSA key pairs (private for signing, public for verification).
• Use OCI Vault’s Key Management APIs to securely fetch keys.

2. OIC Integration Implementation

• Configure REST connection to OCI Vault for key retrieval.
• Use AES encryption/decryption logic in OIC.
• Implement RSA signing and verification in OIC using the retrieved keys.

Detailed steps with screenshots:

Here we will create two common service which will perform the following:

1. Encryption service: It will perform encrypt and signing.
2. Decryption service: It will perform verify and decrypt.

Rest APIs used:

Encrypt: post /20180608/encrypt

Decrypt: post /20180608/decrypt

Sign: post /20180608/sign

Verify: post /20180608/verify

Rest Connection:

Encrypt and signing:

Integration flow:

Rest trigger:

Encrypt:

Signing:

Decrypt and Verify:

Integration flow

Rest Trigger

Verify:

Decrypt:

Reference:

https://docs.oracle.com/en/solutions/oic-message-level-encryption/index.html

https://docs.oracle.com/en-us/iaas/api/#/en/key/release/EncryptedData/Encrypt

Signing vs Verification keys

When it comes to signing and verifying, the distinction lies in how public and private keys are used in asymmetric cryptography (like RSA or ECC). Here's the breakdown:

Signing

Purpose: To prove the authenticity of the data and the identity of the signer.

 Key Used: Private Key.

Explanation: When you "sign" something (like a document or message), you use your private key to create a signature. This ensures that only you, the holder of the private key, could have signed it, guaranteeing the data's integrity and the signer's identity.

Verifying

Purpose: To confirm that the data hasn't been altered and was indeed signed by the entity claiming to have signed it.

Key Used: Public Key.

Explanation: When you "verify" a signature, you use the public key of the signer to check the signature's validity. The public key allows anyone to verify that the signature matches the signed data, but it doesn't let anyone create a sig nature themselves.

Where to Use Each Key:

 Private Key: Used when signing. It should be kept secure and never shared, as anyone with access to the private key could sign data as though they were you.

Public Key: Used when verifying a signature. This key is shared publicly, allowing others to confirm the authenticity of the signed data without compromising security.

In summary:

1. Sign with your private key.
2. Verify with the public key.

Mutual Signing and Verification Between Two Parties (A & B)

1. A → B (Signed Message)

A signs the message using A’s private key.

B verifies the message using A’s public key.

2. B → A (Signed Response)

B signs the response using B’s private key.

A verifies the response using B’s public key.

OIC - Extract Microsoft 365 Outlook Email Attachments and upload them to OCI Object storage

Use Case:

A client has a requirement to automate the processing of email attachments received in their Microsoft 365 Outlook inbox. Currently, they manually download the attachments from emails and upload them to Oracle Cloud Infrastructure (OCI) Object Storage for archival and further processing. This process is time-consuming and prone to errors.

The client needs a solution where the attachments are automatically extracted from specific emails (based on criteria like sender, subject, or date) and uploaded to a designated OCI Object Storage bucket. This ensures seamless and timely processing of files while reducing manual effort and improving efficiency.

Design steps:

We need to follow these steps to implement the solution:

1. Configure the Mailbox: Set up the mailbox and create a connection in OIC using the Microsoft Office 365 Outlook adapter.

2. Set Up Object Storage Connection: Establish a connection to OCI Object Storage in OIC.
3. Design a Scheduled Integration: Create a scheduled integration in OIC and implement the following steps:
1. Fetch emails from the configured mailbox.
2. Read the email messages.
3. Extract the attachments from the emails.
4. Upload the extracted attachments to OCI Object Storage.

Integration flow:

Steps involved in integration flow:

Step1: Fetch the email messages from the configured mailbox

Select method: Get Messages

Step2: Loop over the fetched Email messages and check for email messages having attachments

Step3: Fetch the email attachments using the method: "Get an Attachment Collection" of the MS outlook adapter.

Map the message id template parameter to fetch the attachments of the email message.

Step4: Loop over the fetched attachments and upload each attachment to object storage bucket using the PUT operation of the Rest API.

Step5: Map the input content file and object name. Decode to stream reference using decodeBase64ToReference() function. Bucket name, namespace name can be passed from a lookup. 

Configure Microsoft office 365 outlook adapter connection in OIC:

Create a connection to the Object Storage

Either use the REST endpoint using the Object Storage Service API — https://docs.oracle.com/en-us/iaas/api/#/en/objectstorage/20160918/ 

you can follow my blog

https://soalicious.blogspot.com/2022/08/oic-how-to-use-oci-object-storage-from.html

or configure your Oracle Integration instance using the steps in this link — https://docs.oracle.com/en/cloud/paas/application-integration/integrations-user/add-actions-app-driven-orchestration-integration.html#GUID-822226B0-B8EB-42E0-B053-8D844D2F45DB to access Object Storage using OCI Object Storage Action.

For mailbox setup follow this below Medium post: It has involved two steps:

1. Create a microsoft office 365 outlook account with custom domain
2. Register the application in Azure to create client Id and client secret.

https://lavanya-siliveri.medium.com/oracle-integration-gen3-configure-microsoft-office-365-outlook-adapter-624ca6a63a24