AP Invoice Interface Processing Scripts

-- Find rejected invoices with reasons
SELECT  (SELECT listagg(REF.REJECT_LOOKUP_CODE,' -- ') within group (order by  REF.parent_id asc ) 
                FROM  apps.ap_interface_rejections REF
               WHERE  ail.invoice_line_id  = REF.parent_id ) LINE_ERROR ,               
             (SELECT listagg(REF.REJECT_LOOKUP_CODE,' -- ') within group (order by  REF.parent_id asc ) 
                FROM  apps.ap_interface_rejections REF
               WHERE ai.invoice_id = REF.parent_id ) header_error ,                                                     
               AI.STATUS , 
               ai.invoice_id ,
               AI.invoice_num,
               ai.vendor_id,
               ai.vendor_site_id,
               ai.invoice_Date,
               ai.gl_date,
               ai.description,
               ail.project_id,
               ail.task_id,
               ail.expenditure_type ,
               ail.expenditure_organization_id
  FROM apps.ap_invoices_interface ai        ,
  AP.ap_invoice_lines_interface ail               
WHERE trunc(ai.creation_date) >= '01-JAN-2023'
    AND ai.invoice_num  LIKE 'OTT%'
    AND nvl(ai.status,'PENDING') != 'PROCESSED' 
    AND ail.invoice_id = ai.invoice_id 
  ORDER BY ai.creation_date ASC   ;
  
-- Update correct OU site IDs
update AP_INVOICES_INTERFACE INTF 
  Set INTF.VENDOR_SITE_ID =   (select PVSA.VENDOR_SITE_ID 
                                                         from AP_SUPPLIERS PV,
                                                                 AP_SUPPLIER_SITES_ALL PVSA  
                                                        where PV.VENDOR_ID = INTF.VENDOR_ID
                                                          and PV.VENDOR_ID =  PVSA.VENDOR_ID
                                                            and PVSA.ORG_ID = intf.org_id )     
where STATUS != 'PROCESSED' 
  and ORG_ID = 5983 
  and not exists ( select 1 
                               from AP_SUPPLIER_SITES_ALL PVSA 
                              where PVSA.VENDOR_SITE_ID = INTF.VENDOR_SITE_ID 
                               and pvsa.org_id = intf.org_id   )  ;
-- Set all unprocessed as Rejected
update AP_INVOICES_INTERFACE INTF 
  Set INTF.status = 'REJECTED' 
where STATUS != 'PROCESSED' 
  and ORG_ID = 5983  ;

-- Lines Data verification   
select *
 from AP_INVOICE_LINES_INTERFACE AIL 
 Where ail.invoice_id in 
(Select invoice_id   
   from AP_INVOICES_INTERFACE INTF 
where STATUS != 'PROCESSED' 
  and ORG_ID = 5983 ) ;
  
  
-- Update project details for sustainable city  
update AP_INVOICE_LINES_INTERFACE AIL 
   Set project_id = 185507 ,
         TASK_ID =    337583 ,
         EXPENDITURE_TYPE = 'Sales Comm.-External' ,
         expenditure_organization_id = 6763
 Where ail.invoice_id in 
(select INVOICE_ID   
   from AP_INVOICES_INTERFACE INTF 
where STATUS != 'PROCESSED' 
  and ORG_ID = 5983 ) ;
  
  
-- Remove duplicate invoices   
-- 1. Lines
Delete  AP_INVOICE_LINES_INTERFACE AIL 
 Where ail.invoice_id in 
(Select invoice_id   
   from AP_INVOICES_INTERFACE INTF 
WHERE STATUS != 'PROCESSED' 
  and org_id in (711,5983)
  and exists (select 1 from AP_INVOICES_ALL AIA 
                         WHERE AIA.INVOICE_NUM  = INTF.INVOICE_NUM 
                           AND aia.org_id = intf.org_id 
                           and aia.cancelled_date is null )  ) ;
-- 2. Headers  
Delete AP_INVOICES_INTERFACE INTF 
WHERE STATUS != 'PROCESSED' 
  AND org_id IN (711,5983)
  AND EXISTS (SELECT 1 FROM AP_INVOICES_ALL AIA 
                         WHERE AIA.INVOICE_NUM  = INTF.INVOICE_NUM 
                              AND aia.org_id = intf.org_id 
                            and aia.cancelled_date is null  );
                           
                           


--- usd invoice
update apps.ap_invoices_interface ai             
  set  ai.invoice_amount = 11740.94,
        ai.invoice_currency_code = 'USD',
        ai.payment_currency_code = 'USD' ,
        ai.exchange_rate_type = 'Corporate',
        ai.exchange_date = sysdate - 1  ,
        ai.gl_date = sysdate ,
        ai.exchange_rate =  3.6725,
        ai.status = '',
        ai.request_id = ''
where trunc(creation_date) >= '01-JAN-2023'
    AND invoice_num  LIKE 'TEST1'  ;