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: