OIC - invoke BI control report and store in s3 bucket using rest connection.

Requirement:

Once the inbound file like policy, sub ledger or FAH processed to Oracle ERP, we get a IMPORT REQUEST ID. Based on the import id, we have to fetch the control file details and save it as text file to s3 bucket.

High level Solution steps:

• Create BI report invoke soap connection and aws S3 rest connection.
• Invoke the BI report for the control file
• Decode the response to base64 reference 
• Take an amazone s3 rest connection and configure to store the  control response to s3.

Detailed Implementation steps:

Create BI report invoke soap connection:

Here we will configure  " ExternalReprtWSSService WSDL URL " which can be used to run a report. The same connection we will use in below steps while developing integration.

1. Navigate to connection window and select SOAP adapter, give any meaningful name for connection.
2. Enter the ExternalReportWSSService WSDL URL  and Select the TLS Version. TLS Version depends on your ERP Cloud Application Instance. Mostly it is TLSv1.1WSDL URL Format : https://<your oracle cloud application instance domain>/xmlpserver/services/ExternalReportWSSService?WSDL
3. Choose Security policy as Username Password token and Enter the *Username and *Password of the Oracle Cloud Application Instance. Click SAVE and TEST the Connection.

Create AWS s3 rest invoke connection:

create a rest S3 connection using  following details:

Connection type: Rest api base url.

Connection URL: https://hostname

Security:  AWS Signature version 4 

Provide access key, secret key, 

AWS region:  like US West(Oregon) Region (us-west-2) 

Service Name: Amazon S3.

Invoke BI report :

Step1: Create a schedule orchestrated integration and specify a schedule parameter like importRequestID.

Step2: Next, drag and drop the BI report soap invoke connection and choose operation as runReport.

Step3: Next, open the mapper and map the following:

reportAbsolutePath: 

sizeOfDataChunkDownload: -1

ParameterNamesValues:

name : "P_IMPORT_REQUEST_ID"

values item: importRequestID.

Decode BI report response to base64 reference:

decodeBase64ToReference(reportBytes)

Invoke AWS s3 rest connection and configure:

Step1: Drag and drop your created AWS s3 rest connection and configure the following:

provide relative url as /{pathAndFileName}, 

verb put ,  

provide request payload as Binary and choose other media type and mention media type as "text/plain".

Step2: In the map, assign the following:

Template param: pathAndFilename : "/rootnode/inbound/poc/ctrl.txt"

Stream reference :  Base64 reference

OIC - substring a string using index-within-string() function.

Suppose we have a variable which contains a string like "a/b/c/d.txt;name=value" and we need to take the value before the searchstring(;) then that we can achieve following 2 ways:

1. Using substring-before():

substring-before($string,";")

2. Using substring() and index-within-string() functions;

substring($string,1,oraext:index-within-string($string,";")

This function(index-within-string()) returns the zero based index of the first occurance od search string within the input string. The function returns -1 if search string is not found.

OIC - Opaque schema for creating base64Binary file

Opaque_schema.xsd: this will be used in the stage write file adapter configuration.

<?xml version='1.0' encoding='UTF-8'?>

<schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema">

<element name="opaqueElement" type="base64Binary"/>

</schema>

OIC - Run a schedule integration now using rest api

Why to run schedule integration now:

• Creates an ad-hoc integration run for a scheduled integration. This is useful when you want to test you schedule integration.

Relative URI: /ic/api/integration/v1/integrations/{id}/schedule/jobs/

Scenario1: we will create a schedule service accepting few parameters and then run it now from postman tool.

Implementation steps:

Step1: Create a schedule integration and mention the schedule parameters as required. Maximum you can define 5 parameters. Here I have mentioned following 3 params:

p_email : ""

p_fileName : ""

p_reportPath: ""

Step2: add a logger activity and concat 3 these params to show in log testing time. Add tracking and save and activate it.

Step3: open POSTMAN and provide the following details.

Verb : Post

URL: https://oic_instance_hostname:port/ic/api/integration/v1/integrations/{id}/schedule/jobs

Here Id: identifiername|version = POC19_scheduleintegrat|01.00.0000

| = %7C

In authrization tab, add basic authrization and provide username and password.

In Body tab, choose raw type and select JSON and provide the below payload:

{

"action":"NOW",

"parameters": [{

"name": "p_reportPath",

"value": "report_path"

},{

"name": "p_fileName",

"value": "put_filename"

},{

"name": "p_email",

"value": "provide_emailId"

}]

}

Or we can also use following:

{

"runAsSchedule" : false,

"parameters": [{

"name": "p_reportPath",

"value": "report_path"

},{

"name": "p_fileName",

"value": "put_filename"

},{

"name": "p_email",

"value": "provide_emailId"

}]

}

Note: If you mention runAsSchedule : true then the integration should have a active schedule in simple or ical. Otherwise it will fail with:

HTTP 412 Precondition failed.

Can not submit the run as part of the schedule because its not active.

Click on Send button and check from OIC monitoring and check activity stream and you will see the data in logger.

Scenario 2: we will create another intergration and from there we will run the schedule integration now.

Implementation steps:

Step1: Create a schedule integration and mention the schedule parameters as required. Maximum you can define 5 parameters. Here I have mentioned following 3 params:

p_email : ""

p_fileName : ""

p_reportPath: ""

Step2: add a logger activity and concat 3 these params to show in log testing time. Add tracking and save and activate it.

Step3: create a Rest_OIC_Invoke connection with below details:

Role: Trigger and Invoke

Connection Type: Rest API Base URL

Connection URL: https://your_oic_instance_hostname/

Security: Basic Authetication and provide Username and Password.

And test and save it.

Step4: create an app driven integration and choose the created rest connection as trigger point and provide following details:

Endpoint name: callSchedileInt and next

Verb : post

URI: /call

And as we are not passing any params here so not configuring anything just next and save.

Step5: drag and drop the rest connection as invoke point and provide following details:

Verb: post

Relative URI: /ic/api/integration/v1/integrations/{id}/schedule/jobs/

Add  select option -Configure a request payload for this endpoint.

Next

Next

In request section, choose jason sample and click inline and add the following payload:

{

"action":"NOW",

"Parameters": [{

"name": "p_email",

"value": "email"

},{

"name": "p_fileName",

"value": "put_filename"

},{

"name": "p_reportPath",

"value": "yourreportpath"

}]

}

Next

Done

Step6: go to the mapping part and map the following:

Id: POC19_scheduleintegrat|01.00.0000

Action: NOW

Repeat node for parameters 3 times for name value pair:

Name: p_email 

Value: "youremail"

Name: p_fileName

Value: "yourfilename"

Name: p_reportPath

Value: "yourreportpath"

Step7: add tracking and save and activate and test

You can also call schedule integration which does not have any schedule parameters for that you have to use below json payload:

payload:

{

"action":"NOW"

}

OIC - get ESS JOB Status of a ESS import job

Here, I will show you how to get the ESS job status once the import job completed and you have the ess import request id.

Logic: 

we will create a loop and check the ess job status using the import request id whether the ess import job is successful or not(at each loop it will wait 59 sec). 

Follow the below steps:

Step1: Take an assign activity named like setImportLoopVariable and assign the following 2 variables:

I_inProgress : "true"

I_importRequestStatus : ""

Step2: take a while activity and add the following condition.

$I_inProgress = "true"

Step3: Take a wait activity and assign some time to complete the import ESS process lika value 59sec.

Step4: Drag and drop Erp cloud adapter and configure following:

Provide a name: essJobStatus

Select Query, Create, Update or delete information.

Select service: ErpIntegrationService

Operation: getESSJobStatus

Step5: Map the ess import request id to request id of the getEssJobstatus.

Step6: Take a assign named like updateProgress and do the following:

I_inProgress : returnStatus(result of getESSJobStatusResponse)

I_importRequestStatus: result of getESSJobStatusResponse.

Click here how to convert the status to boolean value:

oic-js-return-boolean-status

OIC - Convert a Scheduled Integration to a REST Adapter-Triggered Orchestration Integration

You can convert a scheduled integration to a REST Adapter-triggered, app driven orchestration integration. This eliminates the need to recreate the integration if you need to change the integration style from scheduled to app driven orchestration.

Restrictions:

• You cannot convert a scheduled integration to a REST Adapter-triggered integration with multiple verbs.
• You cannot convert a scheduled integration with assignments that have the fault data type.

Implementation steps:

Step1: In the left navigation pane, click Home > Integrations > Integrations > de activate and Open the scheduled integration you want to convert.

The integration canvas is displayed.

Right-click the schedule icon and select Convert to REST Trigger.

Menu is displayed with selections for Edit Schedule Definition, Edit Integration Properties, View Integration Properties, and Convert to REST Trigger.

Step2: A list of available REST Adapter triggers is displayed > Select the REST Adapter to use as the trigger connection in this integration. > This invokes the Adapter Endpoint Configuration Wizard. Configure the REST Adapter in the wizard.

When wizard configuration is complete, click Done.

A message is displayed.

Clicking done will perform conversion to REST trigger and will clear tracking information, delete any assignment variables with fault data types and update any impacted actions. Errors and warnings may occur which will require manual correction. Are you sure you want to continue?

Review the message, then click Convert.

Step3: A conversion progress bar is displayed. When complete, the converted integration is displayed in the canvas with the REST Adapter trigger connection.

The integration is displayed horizontally. Conversion errors identified by little red circles are displayed on the impacted actions in the integration.

Step4: Investigate any errors and manually correct the impacted actions or invokes. For example, any scheduled parameters in the scheduled integration are deleted during conversion to an app driven orchestrated integration. This results in errors in any actions that reference those schedule parameters (for example, an assign or logger action).

OIC - Why to avoid creating too many scheduled integrations

When there are too many scheduled integrations configured, instances can get backlogged waiting for resources to become available or previous integration runs to complete. This can cause processing delays where some instances are in a waiting state longer than they should be and schedules may not start at the scheduled time.

Best practice:

• Do not create too many scheduled integrations that are scheduled to run at the same time. 
• Use an asynchronous REST Adapter trigger instead of a scheduled trigger when an active schedule is not absolutely required.
• Do not create any long-running scheduled integrations (a scheduled integration that takes longer than one hour, for example, to complete). This blocks scheduler resources impacting other scheduled runs.
• Spread schedules out over time to avoid schedule clusters.
• You can convert a scheduled integration to an application-driven orchestrated, REST Adapter trigger-based integration.