Oracle Fusion - SQL - AP Invoice Interface

Status of Interface by load_request_id ========================================= 

SELECT aii.load_request_id 

     , aii.status 

     , aii.operating_unit 

     , aii.source 

     , aii.invoice_type_lookup_code 

     , count(distinct aii.invoice_id) inv_count 

     , count(distinct aili.invoice_line_id) line_count 

  FROM ap_invoices_interface aii 

     , ap_invoice_lines_interface aili 

 WHERE aii.invoice_id = aili.invoice_id 

   AND aii.source = nvl(:p_source,aii.source) 

   AND aii.operating_unit = nvl(:p_operating_unit,aii.operating_unit)

   AND aii.load_request_id = nvl(:p_load_request_id,aii.load_request_id) 

 GROUP BY aii.status 

        , aii.operating_unit 

        , aii.source 

        , aii.invoice_type_lookup_code 

        , aii.load_request_id 

 order by 1,2,3,4,5 

AP Invoice Interface Errors 

=========================== 

SELECT apr.load_request_id

      ,apr.parent_table

      ,apr.reject_lookup_code

      ,count(1) reject_count 

  FROM ap_interface_rejections apr

     , ap_invoices_interface inv 

 WHERE apr.parent_id = inv.invoice_id 

   AND inv.status='REJECTED' 

   AND inv.source = nvl(:p_source,inv.source) 

   AND inv.load_request_id = nvl(:p_load_request_id,inv.load_request_id) 

 GROUP BY 

        apr.load_request_id

       ,apr.parent_table

       ,apr.reject_lookup_code 

UNION 

SELECT apr.load_request_id

      ,apr.parent_table

      ,apr.reject_lookup_code

      ,count(1) reject_count 

  FROM ap_interface_rejections apr

     , ap_invoices_interface inv

     , ap_invoice_lines_interface ln 

 WHERE apr.parent_id =ln.invoice_line_id 

   AND ln.invoice_id= inv.invoice_id 

   AND inv.status='REJECTED' 

   AND inv.source = nvl(:p_source,inv.source) 

   AND inv.load_request_id = nvl(:p_load_request_id,inv.load_request_id) 

 GROUP BY apr.load_request_id

         ,apr.parent_table

         ,apr.reject_lookup_code

Purge Interface Tables using program "Purge Interface Tables"

=======================================================

=> Import Payables Invoices

=> Load Request ID

"Load Interface File for Import" Program to load FBDI File

=======================================================

=> Import Payables Invoices

=> ZIP file created by FBDI Template

"Import Payables Invoices" program to load AP invoices from interface to Main tables

=======================================================