OIC ERP - BIP Report Bursting using Email Delivery Channel

Usecase: Here, we will see how to burst a BIP report using Email Delivery channel.

Highlevel steps:

1. Create a BIP data model and email busting query.
2. Create a BIP report based on step1 data model.
3. Create an ESS job to call the BIP report anx add properties
4. Run the ESS job from Scheduled processes section
5. OIC - Create an Oracle ERP Cloud connection
6. OIC - Create an integration and call the erp adpater and ErpIntegrationService and run the ESS job
7. ERP Email delivery channel configuration

Bursting query used:

Select

'00121' as "KEY",

'Template' TEMPLATE,

'en-US' LOCALE,

'CSV' OUTPUT_FORMAT,

'CONTROL_REPORT' || to_char(SYSDATE, 'yyyymmdd') OUTPUT_NAME,

'EMAIL' DEL_CHANNEL,

'ReceiverEmail@gmail.com' PARAMETER1,

NULL PARAMETER2,

'SenderEmail' PARAMETER3,

'Import Invoice Control Report for Source : '|| :p_source || to_char(SYSDATE,'yyyymmdd') PARAMETER4,

'Please find attached Control Report' PARAMETER5,

'true" PARAMETER6,

NULL PARAMETER7

FROM DUAL

Detailed steps with Screenshots:

Step1: Create Data model and bursting query.

Navigation >> Tools >> Reports & Analytics >> Browse Catalog >> Create >> Data Model >> SQL Query >> provide a name, data source as Application_FSCM and put the SQL query >> ok >> add the Parameter required >> under bursting -- add the split by and delivery by and SQL Query for bursting.

Step2: Create the BIP report

From that created data model >> create report

Save the report under Custom folders

Then edit the report >> view a list >> change the output formats and default format as required >> save

Open the report and test.

Step3: Create an ESS job and properties

Navigation >> Tools >> My Enterprise >> Setup and Maintenance >> search tasks with Manage Enterprise Scheduler Job Definitions and Job sets for Financial, SCM and..>>  click on (+) button to create new ESS job

Step4 : Run ESS job from scheduled processes

Navigation >> Tools >> Scheduled Processes >> Schedule New Process >> search the ESS job >> provide the parameter values >> submit the job >>once succeeded, we will receive an email containing the BIP report.

Step 5 & 6: Call the created ess job from OIC. Follow my below blog for the steps.

https://soalicious.blogspot.com/2023/04/oic-erp-how-to-submit-ess-job-and-get.html

Step 7: Emily delivery channel config.

Database - What is the use of Decode function in SQL?

In Oracle, Decode function allows us to add procedural if-then-else logic to the query. Decode compares the expression to each search value one by one, If expression is equal to a search, then the corresponding result is returned by the Oracle database. If a match is not found , then the default is returned.

Example:

Select bank_name, decode(bank_id l, 001, 'SBI', 002, 'ICICI',003,'Axis',Gateway) result from banks;

This above statement is equivalent to if-then-else statement as below:

IF bank_id = 001 THEN

result := 'SBI';

ELSIF bank_id = 002 THEN

result :='ICICI';

ELSIF bank_id =003 THEN

result := 'Axis';

ELSE

result := 'Gateway';

END IF

OIC - ERP - Download ESS Job's output and log file using ErpIntegrationService web service in Oracle Integration

ErpIntegrationService web service can be used to start an ESS job in Fusion Applications, to monitor status and to download the output and log file. 

In order to retrieve the output and log file of an ESS request following two operations are available:

1. downloadESSJobExecutionDetails
2. downloadExportOutput

Usecase : 

Here, we will see how to download ESS job's output and log files using ErpIntegraionService and Oracle ERP Cloud connection in Oracle Integration.

Detailed steps with screenshots:

Step1:  create an Oracle ERP Cloud connection.

https://soalicious.blogspot.com/2023/03/oic-create-erp-cloud-adapter-connection.html

Step2: create an Integration to call the oracle ERP adapter and configure. Once get the response, call a sftp connecrion and map the response content or .zip file to a location.

Similarly we can call the downloadExportOutput operation, it will give us the same response.

Note: 

In real project scenario, we can do the following:

• Check the ESS job whether it is succes (operation: getESSJobStatus)
• If success, download the logs and output using operation : downloadESSJobExecutionDetails
• Write the response contents to a stage location.
• Unzip the files using stage
• Write the exact file needed to a sftp location or mail as attachment or use as enrichment to a map.

Following filetype predicate can be put to separate the file read.

Oracle ERP - How to download ESS job's output and log file using ErpIntegrationService web service with SOAP UI?

ErpIntegrationService web service can be used to start an ESS job in Fusion Applications, to monitor status and to download the output and log file. 

In order to retrieve the output and log file of an ESS request following two operations are available:

1. downloadESSJobExecutionDetails
2. downloadExportOutput

As a response when invoking the web service with any of these operations, a payload containing a list of document id, file name, content type, file title, author, security group and account, file content is returned. In this payload the file content is contained as an attachment.

Steps with Screenshots:

First fetch the wsdl url from ERP.

Navigation >> Tools >> Developer connect >> search with ERP Integration Service >> click the service and select the wsdl

https://<erp public host>/fscmService/ErpIntegrationService?WSDL

Open the SOAP UI >> New Soap Project >> provide the wsdl url and name >> select operation , here downloadESSJobExecutionDetails >> Request 1

Provide the request id in the payload >> auth >> add new authorizaton >> basic >> provide user and pwd

Run >> select attachments tab in the response window and select the attachement >> select the button which "Exports the selected attachment to a file" >> save with .zip extension at the desired location.

Oracle ERP supplier Callback BI report SQL for control report

Once we have done the FBDI import, we can generate a BI report to show which details successfully processed or rejected and etc.

Here, showing supplier sample sql query for such. We can improve more to the query as per required.

Sample sql query:

With Details 

AS

(Select 'Source: supplier : ' || (select distinct psi.vebdor_id from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id) AS source_name,

'Total number of Suppliers received: ' || (select to_char(count(vendor_id)) AS vendor_id from poz_suppliers psi where psi.load_request_id = :p_load_request_id) AS count_rcv,

'Total number of Suppliers loaded from staging table: ' || (select count(vendor_id) from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id and psi.status = 'PROCESSED') AS count_loaded,

'Total number of Suppliers not loaded from staging table: ' || (select count(Vendor_id) from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id and nvl(psi.status, 'REJECTED')= 'REJECTED') AS count_ignored,

'Total number of errored Suppliers: ' || (select count(vendor_id) from poz_suppliers.int psi where psi.load_request_id = :p_load_request_id and psi.status = 'REJECTED') AS count_rejected,

From dual

Select source_name columnsl1 from details

Union all

Select count_rcv from details

Union all

Select count_loaded from details

Union all

Select count_ignored from details

Union all

Select count_rejected from details

BI report generated:

Data from poz_suppliers_int for a load request id: