OIC - "Using local-name() in XPath to Retrieve XML Data Without Namespace Prefix"

Use case: 

In this scenario, we will demonstrate how to retrieve the value of the ErrMsg field in XML using the local-name() XPath function, bypassing the need for a namespace prefix.

The following response was received from an endpoint where the namespace prefix is missing. Our objective is to retrieve the value of the ErrMsg field for further post processing logic:

<enb:ImportDataResponse xmlns:enb="enablon">

    <Response>

        <RowCount>2</RowCount>

        <RowSuc>2</RowSuc>

        <ErrMsg />

    </Response>

</enb:ImportDataResponse>

Solution:

We need to use xslt xpath function: local-name() as below:

$Invoke_SkillsAssessment_ImportData_Enablon//*[local-name()='Response']/*[local-name()='ErrMsg']

OIC - How to get the outbound ip address of your oracle integration 3 instance

In previous versions of Oracle Integration Cloud (OIC), obtaining the outbound IP address of your OIC instance required raising a service request with Oracle support. Once received, this IP address could be shared with third-party applications or service providers to whitelist your instance, ensuring seamless communication.

However, starting with version 3, the outbound IP address is readily available directly on the OIC instance's home page. This eliminates the need to raise a support request and simplifies the process of whitelisting, streamlining communication with third-party applications.

Navigation:

Go to Home page >> click user name profile >> about >> outbound ip.

OIC - Create Secret in OCI Vault to store your keys and access from OIC

Use Case:

In this section, we will demonstrate how to create a secret in Oracle Cloud Infrastructure (OCI) Vault for securely storing your keys. Additionally, we will outline the process for retrieving secret keys using the Secret OCI ID from the OCI Vault through Oracle Integration Cloud.

• In Oracle Cloud Infrastructure (OCI) Vault, a "secret" refers to confidential and sensitive information securely stored within the vault. This can include database passwords, SSH keys, SSL certificates, API keys, and other credentials that require protection from unauthorized access.
• OCI Vault enables effective management and control over these secrets within your Oracle Cloud environment. The service provides robust security features such as encryption at rest and in transit, access controls, auditing capabilities, and seamless integration with other OCI services.

Part1: Create secret in OCI Vault to store your keys.

Implementation steps:

1. Create vault
2. Create key
3. Create secret

Detailed screenshots:

Part2: Get the keys from vault using OIC integrations.

Implementation steps:

1. Create a Rest connection for the OCI vault using vault secret retrieval API, tenancy OCID, User OCID, Private Key with finger print and optional passphrase. Security policy as OCI Signature Version 1. Check this blog- how to get all the details: https://soalicious.blogspot.com/2022/09/oci-how-to-get-tenancy-ocid-user-ocid.html
2. Create a rest trigger to get the secret key
1. Resource url: /mydata/{secretId}
2. Get method
3. Query param: versionNumber
4. Json response as {"Name":"","Value":""}
3. Configure vault rest api invoke as below
1. Resource uri: /20190301/secretbundles/{secretId}
2. GET method
3. Add versionNumber Query param
4. Add json as response
5. Map the trigger secrect id and version number.
4. Map the content response to integration response. Use decodeBase64() function to decode the content.

Json response for oci vault api:

{

  "secretId": "",

  "timeCreated": "2021-08-30T05:33:13.530Z",

  "versionNumber": 1,

  "versionName": null,

  "secretBundleContent": {

    "contentType": "BASE64",

    "content": ""

  },

  "timeOfDeletion": null,

  "timeOfExpiry": null,

  "stages": ["CURRENT", "LATEST"],

  "metadata": null

}

Base URI Vault rest api formation:

https://secrets.vaults.<region>.oci.oraclecloud.com

Detailed steps with screenshots:

Step1: create a Vault Rest connection:

Step2: Create application integration to configure trigger

Step3: configure Rest vault api invoke

Step4: map the request payload of the invoke rest vault api.

Step5: map the vault response content(decoded) to integration response.

Reference:

https://medium.com/@bimands/create-secret-to-store-your-keys-and-access-from-oic-e23bf2613792

OIC - Error occurred while processing activity decryptsf4. Error: java.io.IOException: Error in decrypting the stream for file f:

Usecase: We have received an encrypted file from source and while trying to decrypt it using stage action, throwing below error:

The error message reads:

"Error occurred while processing activity decryptsf4. Error: java.io.IOException: Error in decrypting the stream for file f: /ocid1_integrationinstance_oc1_ap_singapore_1_amaaaaaalbz6auaavjgs3l7ic3amccbdcvgvjg3zohgwapphstnp4u2rpukq/PzDTInsnEe_TfH17CejA/in/t332/download/SourceName_HCM_PayrollResults_20240827_123456.csv"

Root cause: 

The root cause of the error seems to be a mismatch between the key pairs used for encryption and decryption. 

Specifically, the key pair used to encrypt the file and the key pair present in the Oracle Integration Cloud (OIC) certificate store for decryption are different. This mismatch is preventing the successful decryption of the file, leading to the IOException error. 

Solution:

To resolve this issue, ensure that the same key pair is used for both encrypting and decrypting the file.

OIC - iCal expression for scheduling a task to run at 4 PM on June 30th and December 31st each year in the Oracle Integration Cloud (OIC) scheduler?

 Usecase: "Can you provide an iCal expression for scheduling a task to run at 4 PM on June 30th and December 31st each year in the Oracle Integration Cloud (OIC) scheduler?"

Solution:

FREQ=YEARLY;BYMONTH=6,12;BYMONTHDAY=30,31;BYHOUR=16;BYMINUTE=0

OIC - Need to format a dateTime object with a three character month

Requirement: We need to create a file name where we need to populate Month's name as MMM like Sep, Oct etc.

Solution:

fn:concat('HCM_FTE_',ns96:format-dateTime(fn:current-dateTime(),'[MNn,*-3]-[Y001]'),'.csv')

Note: for Caps month like SEP. OCT , use as [MN,*-3]

OIC HCM - Designing HDL Load for External Worker Data business object

Working...

Usecase: external worker data hdl import to HCM.

Requirements  & Considerarions:

1. From source , it has sftp and we need to download encrypted sensitive data file
2. Save a back of the encrypted file in oic embeded file server as once file download or read the file.will.be deleted from source.
3. Poll the file from oic sftp embededded server and decrypt the file.
4. Transform to hdl specific pipe delimited file
5. Load to hcm.
6. Archive the encrypted file.

Design approach:

1. We will create 2 integrations
2. Schedule integration:
1. It will schedule at a particular time, and 
2. Invoke source sftp connection and download the file to stage and 
3. invoke OIC SFTP CONNECTION and save it.
4. Call the main integration
3. Main integration:
1. It will download the file.to stage.
2. Decrypt the file using stage and pgp key.
3. Read in segments using stage and transform to hdl file.
4. Invoke hcm adpater connection and submit hdl
5. Take a while loop and check hdl.status using hcm adapter
6. Once, success, archive the file. 
7. For error, throw error to global fault and send a notification or send to data dog or log etc.

We can also check hdl errors in details using below steps:

https://soalicious.blogspot.com/2024/09/oic-hcm-how-to-check-or-export-report.html

Details screenshot:

TBD