OIC ERP BI Bursting using FTP delivery channel

UseCase : We will create a Bursting BI report and create a ESS job over the bursting BI and call the ESS job from OIC and save the report in the FTP delivery channel. HERE, we will create one document/single file.

Please follow my previous blog for BI Bursting about and details:

https://soalicious.blogspot.com/2022/03/oic-erp-bi-bursting.html

Bursting Query:

SELECT 'XX' AS "KEY",

'XXSupplierRecord' AS "TEMPLATE",

'en-US' AS "LOCALE",

'TEXT' AS "OUTPUT_FORMAT",

'FTP' AS "DEL_CHANNEL",

'AmazonS3' AS "PARAMETER1",

:p_destination AS "PARAMETER4",

'XXSUPPLIERREPORT_' || TO_CHAR(SYSDATE, 'YYYY-MM-DD_HHMISS') || '.csv'

AS "PARAMETER5",

'true' AS "PARAMETER6"

FROM DUAL

BI report Query:

Select 'XX' AS "KEY",

hp.party_name supplier_name,

poz.segment1 supplier_number,

poz.vendor_id supplier_id,

hp.email_address,

hp.country,

hp.city,

hp.status

from POZ_SUPPLIERS poz, HZ_PARTIES hp where 1=1 

And poz.party_id = hp.party_id

And rownum<=10;

Create BI Bursting Report:

Navigator >> Tools >> Reports and Analytics >> Browse catalog >> traverse Shared Folders -- Custom -- Integrarions -- Poc >> 

Create ESS job:

Navigator >> My Enterprise >> Setup and Maintenance >> search with Manage Enterprise in search tasks

Provided following details:

Name: XXSupplierREP_ESS

Path: /delta/

Application: Application Toolkit

Description: POC bursting report

Job Application name: FscmEss

JOb type: BIPJobType and bursting report

Report id: /Custom/Integratikns/Poc/XXSpplierREP1.xdo

Allow multiple pending submissions: True

Create a Paramter: p_destination.

Call From OIC:

JobPackageName: oracle/apps/ess/custom/delta

JobDefinitionName: XXSupplierREP_ESS

Parameter: Sftp path to save the file.

ERP delivery configuration:

Navigation # Tools >> Reports and Analytics >> Browse Catalog >> Administration 

Bursting for multiple files:

Sample bursting query for sending email:

Select a.Vendor_id KEY,

'Test_supp' TEMPLATE,

'en-US' LOCALE,

'PDF' OUTPUT_FORMAT,

'Supplier Details' OUTPUT_NAME,

'EMAIL' DEL_CHANNEL,

'test@gmail.com' PARAMETER1,

'test@gmail.com' PARAMETER2,

'bipublisher-report@oracle.com' PARAMETER3,

'Supplier Details' PARAMETER4,

'Please find the attached file' PARAMETER5,

'true' PARAMETER6,

'donotreply@oracle.com' PARAMETER7,

From poz_suppliers a

For more details follow oracle docs:

https://docs.oracle.com/cd/E21764_01/bi.1111/e18862/T527073T555155.htm#:~:text=About%20Bursting,-Bursting%20is%20a&text=For%20each%20block%20of%20the,element%20in%20the%20data%20model.

OIC ERP About BI Bursting

 About Bursting

• Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations.
•  The data for the report is generated by executing a query once and then splitting the data based on a "Key" value. For each block of the data, a separate document is generated and delivered.
• Using BI Publisher's bursting feature you can split a single report based on an element in the data model and deliver the report based on a second element in the data model.
•  Driven by the delivery element, you can apply a different template, output format, delivery method, and locale to each split segment of your report. Example implementations include:

• Invoice generation and delivery based on customer-specific layouts and delivery preference

• Financial reporting to generate a master report of all cost centers, splitting out individual cost center reports to the appropriate manager

• Generation of pay slips to all employees based on one extract and delivered via e-mail

What is the Bursting Definition?

A bursting definition is a component of the data model. After you have defined the data sets for the data model, you can set up one or more bursting definitions. When you set up a bursting definition, you define the following:

• The Split By element is an element from the data that will govern how the data is split. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_ID. The data set must be sorted or grouped by this element.
• The Deliver By element is the element from the data that will govern how formatting and delivery options are applied. In the invoice example, it is likely that each invoice will have delivery criteria determined by customer, therefore the Deliver By element may also be CUSTOMER_ID.
• The Delivery Query is a SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.

Defining the Query for the Delivery XML

The bursting query is a SQL query that you define to provide BI Publisher with the required information to format and deliver the report. BI Publisher uses the results from the bursting query to create the delivery XML.

The BI Publisher bursting engine uses the delivery XML as a mapping table for each Deliver By element. The structure of the delivery XML required by BI Publisher is as follows:

<ROWSET> <ROW> <KEY></KEY> <TEMPLATE></TEMPLATE> <LOCALE></LOCALE> <OUTPUT_FORMAT></OUTPUT_FORMAT> <DEL_CHANNEL></DEL_CHANNEL> <TIMEZONE></TIMEZONE> <CALENDAR></CALENDAR> <OUTPUT_NAME></OUTPUT_NAME> <SAVE_OUTPUT></SAVE_OUTPUT> <PARAMETER1></PARAMETER1> <PARAMETER2></PARAMETER2> <PARAMETER3></PARAMETER3> <PARAMETER4></PARAMETER4> <PARAMETER5></PARAMETER5> <PARAMETER6></PARAMETER6> <PARAMETER7></PARAMETER7> <PARAMETER8></PARAMETER8> <PARAMETER9></PARAMETER9> <PARAMETER10></PARAMETER10> </ROW> </ROWSET>

where

• KEY is the Delivery key and must match the Deliver By element. The bursting engine uses the key to link delivery criteria to a specific section of the burst data.

• TEMPLATE - is the name of the Layout to apply. Note that the value is the Layout name (for example, 'Customer Invoice'), not the template file name (for example, invoice.rtf).

• LOCALE - is the template locale, for example, 'en-US'.

• OUTPUT_FORMAT - is the output format. For a description of each type, see Setting the Output Types. Valid values are: HTML, PDFZ - zipped PDF, PDF, RTF, EXCEL, EXCEL2000, MHTM, PPT, PPTX - PowerPoint 2007, XSLFO, XML, CSV, ETEXT

• SAVE_OUTPUT - indicates whether to save the output documents to BI Publisher history tables that the output can be viewed and downloaded from the Report Job History page.

Valid values are 'true' (default) and 'false'. If this property is not set, the output will be saved.

• DEL_CHANNEL - is the delivery method. Valid values are:

• EMAIL
• FAX
• FILE
• FTP
• PRINT
• WEBDAV

Delivery Channel & PARAMETER Values:

Email

PARAMETER1: Email address

PARAMETER2: cc
PARAMETER3: From
PARAMETER4: Subject
PARAMETER5: Message body
PARAMETER6: Attachment value ('true' or 'false'). If your output format is PDF, you must set this parameter to "true" to attach the PDF to the e-mail.
PARAMETER7: Reply-To
PARAMETER8: Bcc
(PARAMETER 9-10 are not used)

File

PARAMETER1: Directory

PARAMETER2: File Name
(PARAMETER 3-10 are not used)

FTP and SFTP

PARAMETER1: Server name
PARAMETER2: Username
PARAMETER3: Password
PARAMETER4: Remote Directory
PARAMETER5: Remote File Name
PARAMETER6: Secure (set this value to 'true' to enable Secure FTP)
(PARAMETER 7-10 are not used)

Follow below blog for steps to create bursting bi report and call from ERP:

https://soalicious.blogspot.com/2022/03/oic-erp-bi-bursting-steps.html

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.