Oracle ERP supplier Callback BI report SQL for control report

Once we have done the FBDI import, we can generate a BI report to show which details successfully processed or rejected and etc.

Here, showing supplier sample sql query for such. We can improve more to the query as per required.

Sample sql query:

With Details 

AS

(Select 'Source: supplier : ' || (select distinct psi.vebdor_id from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id) AS source_name,

'Total number of Suppliers received: ' || (select to_char(count(vendor_id)) AS vendor_id from poz_suppliers psi where psi.load_request_id = :p_load_request_id) AS count_rcv,

'Total number of Suppliers loaded from staging table: ' || (select count(vendor_id) from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id and psi.status = 'PROCESSED') AS count_loaded,

'Total number of Suppliers not loaded from staging table: ' || (select count(Vendor_id) from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id and nvl(psi.status, 'REJECTED')= 'REJECTED') AS count_ignored,

'Total number of errored Suppliers: ' || (select count(vendor_id) from poz_suppliers.int psi where psi.load_request_id = :p_load_request_id and psi.status = 'REJECTED') AS count_rejected,

From dual

Select source_name columnsl1 from details

Union all

Select count_rcv from details

Union all

Select count_loaded from details

Union all

Select count_ignored from details

Union all

Select count_rejected from details

BI report generated:

Data from poz_suppliers_int for a load request id: