OIC - Cloud ERP - What we can do after FBDI import | Callback oracle control file

Use case: After the FBDI import, We can create a BI report to show which transactions or records are received, loaded from interface table, failed or rejected transactions.

For instance here, we will see the BI sql query to fetch AP payable invoice related details to the import using the load request id.

Highlevel steps:

• Create a BI report with data model and SQL query to fetch the import details.
• Create an ExternalReportWSSService wsdl soap connection.
• Create an app driven integration and peform following:
• Subscribe the callback
• Check if summarystatus is succeeded
• Check if load to interface is succeeded
• Check if import process succeeded
• Assign all the paramters to variables required for BI call.
• Call the created BI report soap connection and map the params.
• Call a sftp connection to store the fetched BI report response to a location.

Step1: Create BI report steps: 

Sql query used:

With Details 

AS

(Select 'Source: AP : ' || (select distinct aii.source from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id) AS source_name,

'Total number of invoices received: ' || (select to_char(count(invoice_id)) AS invoice_id from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id) AS count_rcv,

'Total dollar amount of invoices received: ' || (select nvl(sum(invoice_amount),0) AS invoice_amt from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id) AS amount_rcv,

'Total number of invoices loaded from staging table: ' || (select count(invoice_id) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.status = 'PROCESSED') AS count_loaded,

'Total dollar amount of invoices loaded from staging table: ' || (select nvl(sum(invoice_amount),0) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.status = 'PROCESSED') AS amount_loaded,

'Total number of invoices not loaded from staging table: ' || (select count(invoice_id) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and nvl(aii.status, 'REJECTED')= 'REJECTED') AS count_ignored,

'Total dollar amount of invoices not loaded from staging table: ' || (select nvl(sum(invoice_amount),0) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and nvl(aii.status, 'REJECTED')= 'REJECTED') AS amount_ignored,

'Total number of errored invoices: ' || (select count(invoice_id) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.status = 'REJECTED') AS count_rejected,

'Total dollar amount of errored invoices: ' || (select nvl(sum(invoice_amount),0) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.status = 'REJECTED') AS amount_rejected

From dual

Where (select distinct aii.source from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id ) is not null)

Select source_name columnsl1 from details

Union all

Select count_rcv from details

Union all

Select amount_rcv from details

Union all

Select count_loaded from details

Union all

Select amount_loaded from details

Union all

Select count_ignored from details

Union all

Select amount_ignored from details

Union all

Select count_rejected from details

Union all

Select amount_rejected from details

Step2: create a wsdl ExternalReportWSSService connection:

Follow below my blog for "create connection" part:

https://soalicious.blogspot.com/2021/05/oic-invoke-erp-bi-report.html

Step3: call the BI report from OIC Integration:

Subscribe the callback.

Check if summarystatus is succeeded.

Check if load status is succeeded.

Check if import status is succeeded.

Assign all the required params for BI call.

varReportPath: 'Custom/OIC_Integrations/ERP_ALL_CTRL_Report.xdo'

varParamName1: 'p_load_request_id'

VarParamvalue1: /nssrcmpr:onEvent/inp1:/ErpImportBulkDataEvent/inp1:JOBS[inp1:JOBNAME = 'Load Interface File for Import']/inp1:REQUESTID

varSizeOfDataChunkDownload: '-1'

Call the BI soap adpater connection.

Operation: runReport.

Map the required details.

Call a sftp adapter to store the bi report response 

Overall calback flow

For more details regarding BI call params . Follow my below page:

https://soalicious.blogspot.com/2021/05/oic-invoke-erp-bi-report.html