OIC ERP - Send custom Invoice report using Ess Job call and BI bursting report

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:

1. Create Data models
2. Add BI bursting with email delivery channel.
3. Create RTF template using msword BI publisher plugin.
4. Upload the RTF template and create BI report
5. Create ESS job and call the BI report as reporting id.
6. 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:

https://soalicious.blogspot.com/2024/01/erp-create-rtf-file-based-on-data-model.html

https://soalicious.blogspot.com/2022/12/erp-how-to-create-rtf-template-from.html

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"

OIC ERP - Invoice BI Report SQL query to get custom detailed report

Usecase: Here, we will see the BI report SQL query which we can use to generate custom detailed invoice report like below:

• Source name
• Total number of invoices received
• Total amount of invoices received
• Total number of invoices loaded from staging table
• Total amount of invoices loaded from staging table
• Total number of of invoices not loaded from staging table
• Total amount of invoices not loaded from staging table
• Total number of errored invoices
• Total amount of erroed invoices

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

From DUAL

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

 )

Select source_name column1 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

Create data model and report using edi etext template

Sample template:

https://soalicious.blogspot.com/2023/11/etext-edi-template.html

Similarly, we can do the same custom report for FA using fa_mass_additions , EX for exm_credit_card_trxns , CM using ce_statement_headers_int tables etc.

OIC ERP - Get Source Name for invoice callback | Fixed asset callback

Usecase: Here, we will see how to get source name for which the callback event we received once the FBDI import completed to ERP. Here, we will show couple of examples like soure names for invoice or fixed asset callbacks etc.

High level steps:

1. Create a BI report data model based on the below sql query.
2. Create an EDI delimiter based eText BI template and create a BI Report.
3. Create a BI report invoke soap connection in OIC
4. In callback integration - invoke the BI report soap connection and pass the load request id parameter name value and the report path name etc.
5. Decode the base 64 response and remove carriage return or next line using custom Javascript and store it into a variable for further use.

BI report SQL queries:

Select distinct source from ap_invoices_interface where 1 = 1 and load_request_id = :p_load_request_id

Union all

Select distinct aii1.feeder_system_name from fa_mass_additions aii1 where aii1.load_request_id = :p_load_request_id

Union all

Select distinct bank_name from ce_statement_headers_int csh where 1=1 and csh.load_request_id = :p_load_request_id

Union all

Select distinct 'ServiceNowUpdates' from fa_descriptive_details_int where 1=1 and 

load_request_id = :p_load_request_id

Union all

Select distinct 'ServiceNowTransfer' from fa_transfers_t where 1=1 and 

load_request_id = :p_load_request_id

Union all

Select distinct 'ServiceNowRetirements' from fa_retirements_t where 1=1 and 

load_request_id = :p_load_request_id

Detailed with screenshots:

Step1: Create Data model.

Home > Tools > Reports & Analytics >Browse Catalog > create > data model > click + sign > SQL Query > provide the SQL query

Checked the box to create the parameter.

Data model created.

Provide a default value and make it mandatory

See the structure.

Click on data and provide valid load request id and view

Save the sample data

Export the data to see the xml structure.

Save

Step2: Create a BI report. Create a edi (.rtf) template and upload.

Save

View Report.

Sample edi (comma separated template) etext template.

To know how to create eText Delimiter based rtf template , follow my blog:

https://soalicious.blogspot.com/2023/11/etext-edi-template.html

Step3: Create a SOAP connection to call the BI report

Step4: Configure BI report invoke and decode base64 and store it into a variable.

Sometime while we decode the base64 BI report response, it can have a next line or carriage return with the response, in that case , we need to create a custom javascript and remove them. Follow my below blog for the js code:

https://soalicious.blogspot.com/2021/03/oic-javascript-to-remove-carriage-return.html

OIC - ERP - Payables invoice callback - Custom report

Usecase: Here, we will customize the payable invoice callback and get a detailed report from ERP.

Implmentation steps:

1. Subscribe the callback
2. Check if jobname ="Load Interface File for Import" then 
1. save the load request id and load status 
2. Call a BI report to get the Source name based on Load request id (https://soalicious.blogspot.com/2024/01/oic-erp-get-source-name-for-invoice.html)
3. Update path : Fetch FTP or S3 bucket path details from where we need to download the processed zip file or achive the processed file. We will use a simple Lookup like OIC_Interface_Path.dvm and store source name and path  and from integration, fetch the path based on source name (fetched from previous step) using lookup() function.
4. Get the Processed zip file from FTP or S3 Bucket processing directory invoking a child integration. (https://soalicious.blogspot.com/2023/11/oic-get-files-from-aws-s3-rest-service.html)
1. Provide directory name
2. filePattern as $loadRequestId
3. Unzip flag = N
5. Check Load status
1. If Load status != SUCCEEDED then Archive the source zip file calling a child integration. With .Failed appended file name. (https://soalicious.blogspot.com/2023/12/oic-create-resuable-integration-to.html)
2. Otherwise, if Jobname ="Import Payables Invoices" ans status = SUCCEEDED then, 
1. Validate the AP Invoice: Call ESS job and feed inputs parameters like business unit id, source, ledger id etc. JobPackageName: /oracle/apps/ess/financials/payables/invoices/trandactions/ and jobDefinitionName: APXAPRVL
2. Monitor the AP invoice : Call a child integration and pass ess job run request id and get the status (https://soalicious.blogspot.com/2024/01/oic-erp-monitor-progress-of-ess-job-and.html)
3. Initiate invoice approval: Call ESS job and feed inputs parameters like business unit id, source etc. JobPackageName: /oracle/apps/ess/financials/payables/invoices/trandactions/ and jobDefinitionName: APXIAWRE
4. Archive the file.
6. Get detailed BI report file (https://soalicious.blogspot.com/2024/01/oic-erp-invoice-bi-report-sql-query-to.html)
7. Save the report to a ftp or S3 directory for further processing.
8. Send a Notification with the report. Or we can call ESS job and send mail over BI bursting (https://soalicious.blogspot.com/2024/01/oic-erp-send-custom-invoice-report.html)