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.