OIC ERP BI publisher call Retry logic Usecase 2

Use Case: 

We have OIC to ERP Bulk import Integration and once import gets completed, we are calling another sub-integration to get the callback from ERP. 

To get callback, we are calling BI publisher report in the subintegration and place the callback file in the SFTP directory. 

In production, we are observing 5 mins time limit exceeded error while invoking the client ERP BI service and integration gets failed and we are not receiving the Callback file. As a result, we need to manually generate the callback file from ERP for further processing.

What we do:

• One - Retry the ERP callback call atleast 5 times before it finally gets failed.
• Two - In every retry, we have to check if we receive the callback file in the SFTP server directory. That file also check loops five times.

Integration flow:

Steps in detail:

Step1: Take a assign and create a Variable ControlReportExists

p_controlReportExist : false

p_ExceptionCode: ""

p_ExceptionReason: ""

p_ExceptionDetails: ""

p_callBackCount: 0.0

Step2: Take a while and loop it for five times.

$p_controlReportExist: "false" and $p_callBackCount <=4.0

Step3: Take a scope and call the callback subintegration and assign the customized Exception code, reason and details.

Step4: Take a assign for initiate file check count

p_FileCheckCount =0

Step5: Take a while loop and loop it for 5 times to check the file exist in the SFTP directory.

$p_FileCheckCount <=4.0 and $p_controlReportExist="false"

Step6: Call FTP with list operation and required File pattern and direcrory details.

Step7: Take a switch and 

if ItemCount =0,

Assign,

p_controlReportExist= "false"

p_FileCheckCount=$p_FileCheckCount + 1.0

Wait 59 sec

Else,

p_controlReportExist= "true"

Step8: take a assign and increase the callback counter by 1.

p_callbackCount =$p_callbackCount + 1.0

Conclusion:

This retry logic will save manual work and tine and most cases(99%), it will work smoothly and gets the callback file.

OIC Techniques - Decode base64 encoded BI publisher report | Call an Oracle Fusion Applications Business Intelligence Publisher Report Synchronously

When the SOAP Adapter calls an Oracle Fusion Applications business intelligence publisher (BIP) report synchronously, the report data is always returned as a base64-encoded string in the response.

Parsing Techniques:

Option1: Recommended: Use the decodeBase64ToReference mapper function to convert the base64 string to a file reference and use the file reference in a stage file action/FTP Adapter read file operation scenario for further processing.

Option2: Alternative: Write the base64 string using an opaque schema to a file using a stage file action/FTP Adapter write file operation scenario, then read the file using a stage file action/FTP Adapter scenario for further processing.

Write the file with opaque schema:

https://soalicious.blogspot.com/2022/02/xsd-opaque-schema.html

Map the ERP BI base64 encoded response to Opaque element.

Read the file using Stage:

Note:

Upon calling a BIP report synchronously, the request sent to the BIP web service initiates report generation and sends a response back after the report is generated. This can cause latency issues in an Oracle integrarion environments.

OIC Invoke BI Publisher report with Name value parameters

Please read the following my privious blog for understanding how we can create BI Report and call from OIC:

https://soalicious.blogspot.com/2022/03/oic-how-to-call-and-read-bi-publisher.html

Screenshots steps for Name value params:

***Expand the "parameterNameValues" element

Expand "Item" and then enter your BI Report Parameter name in name* element 

Now expand values* and map or pass Parameter Values in Item element .

Parameter names and its values configuration completed

*Note : suppose you have multiple parameters for your BI Report then you have to repeat the Item tag and then pass the Parameter name and its Values.***

BI Report with a parameter

Call Maps from Integration:

parameterNameValue

reportAbsolutePath

sizeOfDataChunkDoenload = "-1"

Name

Values ->> Item

Parameter name in the BI report data model:

OIC | How to call and read BI publisher report in OIC Integration

High level steps:

1. Create a SOAP connection for the report service.
2. Create a schedule integration and drag and drop the created soap connection and select operation as runReport.
3. Map the required fields like reportAbsolutePath, NameValuesParameters etc.
4. DecodeBase64 ERP response to readable format. 

Read the follow blog to know how to create BI publisher report in Cloud ERP:

https://soalicious.blogspot.com/2022/03/cloud-erp-how-to-create-otbi-report.html

Steps in detail:

Create a SOAP connection for the BI report call.

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.1
3. WSDL URL Format : https://<your oracle cloud application instance domain>/xmlpserver/services/ExternalReportWSSService?WSDL
4. 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 a scheduled integration.

Drag and drop the created soap connection and probide endpoint name

Choose operation as runReport.

Keep it as default.

Edit the Map

Now let's do the MAPPINGS. We will pass all the required fields to invoke BI report ( like absolute Report Path, size of data chunk download, Report Parameters Name and its Value ,attribute template etc.)

In this case, I have used the following parameters:

ReportAbsolutePath: "/Custom/Integrations/Poc/InvoiceId_POC_REP.xdo"

sizeOfDadaChunkDownload: " -1"

You can also use the following paramters as per your requirement:

Expand the "parameterNameValues" element

Expand "Item" and then enter your BI Report Parameter name in name* element 

Now expand values* and map or pass Parameter Values in Item element .

Parameter names and its values configuration completed

*Note : suppose you have multiple parameters for your BI Report then you have to repeat the Item tag and then pass the Parameter name and its Values.

Take an assign action and decode the base64 reaponse to reference using decodeBase64ToReference() function.

Now take a stage activity and use that referenced variable and read it using a sample csv file.

Add tracking , save, activate and submit run.

From Monitoring