Usecase: Instead of directly send the custom invoice report using Notification activity, we can also alternatively call ess job and do BI report bursting and send the email notification.
Highlevel steps:
- Create Data models
- Add BI bursting with email delivery channel.
- Create RTF template using msword BI publisher plugin.
- Upload the RTF template and create BI report
- Create ESS job and call the BI report as reporting id.
- From integration, call the submitESSJobRequest of erp cloud adpater erpIntegrationService with load id and source name as input feeds.
Detailed steps:
Step1: Create Data Model:
Invoice Callback Custom BI report SQL query:
WITH details AS
(Select 'Source: AP : ' || (select DISTINCT aii1.source FROM ap_invoices_interface aii1 WHERE aii1.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 aii1 WHERE aii1.load_request_id = :p_load_request_id) AS count_rcv,
'Total dollar amount of invoices received: '|| (select NVL(SUM(invoice_amount),0) AS invoice_amnt FROM ap_invoices_interface aii1 WHERE aii1.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 aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'PROCESSED') AS count_loaded,
'Total dollar amount of invoices loaded from staging table: '|| (select NVL(SUM(invoice_amount),0) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'PROCESSED') AS amount_loaded,
'Total number of invoices not loaded from staging table: ' || (select COUNT(invoice_id) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and NVL(aii1.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 aii1 WHERE aii1.load_request_id = :p_load_request_id and NVL(aii1.status,'REJECTED')= 'REJECTED') AS amount_ignored,
'Total number of errored invoices: ' || (select COUNT(invoice_id) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status= 'REJECTED') AS count_rejected,
'Total dollar amount of errored invoices: '|| (select NVL(SUM(invoice_amount),0) FROM ap_invoices_interface aii1 WHERE aii1.load_request_id = :p_load_request_id and aii1.status = 'REJECTED') AS amount_rejected,
'00121' KEY
From DUAL
Where (select distinct aii1.source from ap_invoices_interface aii1 where aii1.load_request_id = :p_load_request_id and aii1.source = :p_source) is not null
)
Invoice error details SQL query:
Select Distinct aii.invoice_num, aii.invoice_date,aii.vendor_num,aii.invoice_amount,'00121' KEY from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.source= :p_source and aii.status = ' REJECTED'
Step2 : create email bursting
Bursting query used:
Select
'00121' as "KEY",
'Template' TEMPLATE,
'en-US' LOCALE,
'XLSX' 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
Step3: Create RTF template:
Step4: Create BI report.
Step5: create ESS job which will invoke this BI report.
Go to Home > My enterprise > Setup and Maintenance search with Manage Enterprise Schedule > click on Manage Enterprise Schuduler Job Definitions and job sets for financial, suppy chain management and related applicarions > click on create (+) button
Provide BI report absolute path to reporting id.
We can test/schedule the created ESS job from tools > Scheduled Processes > schedule new process
Search with ESS job name and provide required input feeds and submit.
Step6: From integration, call the submitessjob of erp cloud adpater erpIntegrationService with load id and source name as input feeds.
Jobdifinition: "Test_AP_Email_CTRL_ESS1"
JobPackageName: "oracle/apps/ess/custom/ess"