OIC - Update lookup from a CSV file

UseCase: Here, we will feed a lookup name as input and then based on the lookup name we will fetch the Lookup csv file from a location using FTP protocol and then call lookup rest api to update the the respective lookup.

Rest API details to update lookup:

API : /ic/api/integration/v1/lookups/{name}

Verb : PUT

Request Json payload:

{"columns":["rightnow","soap"],"rows":[{"rowData":["RN1","SOAP1"]},{"rowData":["RN2","SOAP2"]}],"name":"myLookup"}

Prerequisites:

• You should have a FTP connection to read the csv.
• You should have a ICS rest connection.

https://soalicious.blogspot.com/2021/08/oic-how-to-create-rest-ics-connection.html

Implementation steps:

Step1: We will feed lookup name.

URI: /update/{lookupName}

Step2: Read the file from a location using FTP and parse with a csv sample file schema.

In the map, concat lookup name with .csv

concat(lookupName,".csv")

Step3: Rest API URL configure to update Lookup.

Step4: Provide the sample Json request payload.

Step5: Map the lookup name and csv response to Lookup update rest request.

►Here haedcoded, "Country_Code" and "Country_Name" to each Columns.

►For each lookup row map to rows and map the code and name fields to each rowData.

►Map template lookup name to name.

►Map input template lookup name to rest request template parameter name.

The Integration flow looks below:

Before lookup update:

After update:

OIC - send Binary object as rest response

UseCase: Here, we will send .JPG object as input using rest trigger and fetch the file using FTP protocol and send as Rest binary response.

Note: we can also choose document, text or png type or other media types.

Implementations steps:

Step1: Take a rest trigger and configure. 

Verb: Get.

Add and review parameters for this endpoint checked

Configure this endpoint to trceive the response checked

Step2: Add Query paramter as Object Name

Step3: select the response payload format as Binary and choose media type as per requirement. Here, i take image/jpeg. 

Step4: Download the file using FTP and map the input object name.

Step5: Add the download response (file reference) to rest response stream reference.

The Integration looks as below:

Open the endpoint url with object name in the browser.

You can see the object in the browser.

OIC - Search Data from CSV file

Usecase: Here, I will show you how you can search employee information from a CSV file using empid.

High level steps:

• Add rest configure to fetch emp details based on id
• Read csv file using FTP connection
• Map the searching logic to show in rest response.

Implementation:

Step1: Add an App driven orchestratation integration style and add a rest connection and configure id template param and emp details as response.

Step2: Add FTP connection and read the file and use a sample csv file.

Step3: Map the fields as below:

Take XSLT view and add for each on top of Email and map for each source Emp.

Then add if block inside for each and add the condition csv file empid = template id

Map all the fields.

XSLT code view will be like that:

Now the integration flow looks like below

Step4: Add the tracking and save and activate and test

CSV file contents:

OIC - Business identifiers

Introduction:

• Business Identifiers helps to track the messages from monitoring dashboard very easily. 
• Suppose you are subscribing an event - supplier details from ERP and you want to track the meseages based on the unique supplier id or POid that you can achieve using this feature.
• Without enabling the tracking or business fields, you can not activate the integration.
• Max 3 identifiers can be assigned during design time. 
• One is primary identifier. 
• Others two are secondary or business identifiers.
• One error sign will be showing in the integration untill you add the tracking fields.

Add tracking and monitor:

Step1: Whether you have a soap or rest triggerd service, go to the actions and tracking and add the 3 fields, Here i added empid as primary and ename and dept fields as secondary fields.

Step2: Go to the monitoring and integrations. Empid will be added as primary identifier and ename and dept will be added in the business identifiers. 

OIC - Using Stage file to encrypt and decrypt files.

Use Case: Here, We will download a csv file using FTP connection and encrypt the file using Stage action and then write the file using FTP connection and then read the encrypted the file using FTP connection and decrypt the file using Stage and write again to file location using FTP.

Implementation steps:

Step1: First We must have imported the PGP private and public keys to settings certificates.

https://soalicious.blogspot.com/2021/08/oic-import-pgp-keys-to-use-in-stage.html

Step2: Download the file using FTP connection.

Step3: Encrypt the file using PGP public key using Stage. Provide folllwing details:

File reference : Downloaded file response ref

File Name: Any name of the encrypted file.

Output Directory: logical directory like /writeEncrypt

PGP key to encrypt: public key

Step4: Write the file to a location and map the stage encrypted response file ref to write file reference.

Step5: Read the file using FTP connection.

Step6: Drag and drop a Stage and decrypt the file. Provide the following details:

File Reference: Read file reference

File Name: any name for decryption.

Output Directory: logical one like /writeDecrypt

PGP key to decrypt: private key

Step7: Write the file using FTP and map the decrypted file response reference to write file reference.

The Integration flow will look like below:

Source CSV file/Decrypted file:

Encrypted file:

OIC - Import PGP keys to Setting Certificates to use in Stage file to encrypt and decrypt files.

Here I will show you how to import the PGP keys in settings Certificate section which will be later on used in Stage file for encrypting and decrypring files.

Import steps:

Public Key is used for Encryption and Private Key for decryption. In order to use encrypt/decrypt files we have to upload PGP keys in OIC.

To upload PGP Keys:

Step1: From OIC Home page→ Settings → Certificates page. Click Upload at the top of the page.

Step2: In the Upload Certificate dialog box, select the certificate type. Each certificate type enables Oracle Integration Cloud to connect with external services. 

PGP: Use this option for bringing PGP Certificates.

Public Key:

Enter Alias Name and Description

Select Type as PGP

Select Category as Public

Select PGP File, Click Browse and select the public key file to be uploaded

Select ASCII-Armor Encryption Format

Select Cipher Algorithm

Click Upload.

Private key:

Enter Alias Name and Description

Select Type as PGP

Select Category as Private

Select PGP File, Click Browse and select the private key file to be uploaded

Enter the PGP Private Key Password of the private key being imported.

Click Upload.

For more details of oic certificate management, follow my below blog:

https://soalicious.blogspot.com/2022/04/oic-certificate-management-different.html

OIC - Decrypt file using FTP download operation and read reference in stage activity

Use Case: Here, we will download (with decrypt) an encrypyed csv file without schema option  and then read file reference using stage read entire file option.

Implementation steps:

Step1: First create FTP connection following below blog:

https://soalicious.blogspot.com/2021/08/oic-create-sftp-connection-with-pgp.html

Step2: Create an orchestrated intwgration and drag and drop FTP connection and download the file with pgp encrypt option.

Step3: add the stage activity and choose the read the entire file option and provide the file reference from download response object.

Step4: provide the csv sample file for reading the reference.

Now the integration flow will like as below:

Save and test.

Encrypted csv file:

From Monitoring - view activity stream: