Existing Supplier with Pendings Transaction Cancelation in EBS R12

Existing Supplier with all Transactions Cancel :
=======================================

CREATE OR REPLACE procedure APPS.mpfl_iou_cancelation
as

    l_verify_flag char(1);
    l_org_id hr_operating_units.organization_id%type;
    l_error_message varchar2(3000);
  --  l_party_id number;
    --l_vendor_id number;


    cursor h_invoice is
   
    select distinct ih.vendor_id  , s.party_id  from mpfl_iou_holders ih, ap_suppliers s  where --ih.vendor_id =25645--in (130717,25695)
     ih.vendor_id=s.vendor_id and ih.FLAG=0 ;

cursor l_invoice (p_vendor_id varchar2) is
    select
    vendor_id,vendor_name, invoice_id,gl_date,invoice_num, invoice_amount, amount_paid,  adjusted_amount,  due_for_adjustment,
    decode(due_for_adjustment,0,'ADJUSTED','UNADJUSTED') status
     from (
    select v.vendor_id, v.vendor_name, ai.invoice_id,ai.gl_date,ai.invoice_num,  ai.invoice_amount, ai.amount_paid,  sum( al.amount) adjusted_amount,
    (ai.invoice_amount+ sum( al.amount)) due_for_adjustment
    from ap_invoices_all ai,po_vendors v, ap_invoice_lines_all al where ai.org_id=148 -- and ai.invoice_type_lookup_code='PREPAYMENT'
    and ai.vendor_id=v.vendor_id -- and ai.gl_date>='01-NOV-2016' and ai.cancelled_date is null
    and v.vendor_id=p_vendor_id
    and ai.invoice_id=al.prepay_invoice_id(+) --and  al.prepay_invoice_id =484061
    group by  v.vendor_id, v.vendor_name, ai.invoice_amount,ai.invoice_id, ai.amount_paid, ai.gl_date, ai.invoice_num
    )
   where (due_for_adjustment<>0 or due_for_adjustment is null)
    order by status ;

begin

for h1 in h_invoice loop

    l_verify_flag := 'Y';
    l_error_message := null;


begin

for l1 in l_invoice (h1.vendor_id) loop

begin

update  ap_suppliers s set
 vendor_name='XXX-M', vendor_name_alt='XXX-M'|| '-'|| vendor_name_alt,tca_sync_vendor_name='XXX-M',
end_date_active='31-OCT-2016', hold_all_payments_flag='Y',hold_future_payments_flag='Y' , hold_flag='Y', hold_by='1110', hold_date='31-OCT-2016', hold_unmatched_invoices_flag='Y',
 enabled_flag='N'  where s.vendor_id=l1.vendor_id ;

--update   po_vendors p set VENDOR_NAME='XXX-M.',vendor_name_alt='XXX-M'|| '-'|| vendor_name_alt, end_date_active='31-OCT-2016',enabled_flag='N', hold_all_payments_flag='Y',hold_future_payments_flag='Y', hold_flag='Y', hold_unmatched_invoices_flag='Y' where p.vendor_id=25695-l1.vendor_id ;

update ap_supplier_sites_all t set hold_all_payments_flag='Y',hold_future_payments_flag='Y' where t.vendor_id =l1.vendor_id  and t.org_id in (148);

update ap_invoices_all set prepay_flag=null,payment_status_flag='Y'  where invoice_id =l1.invoice_id  and vendor_id=l1.vendor_id  and org_id=148;

update hz_parties z set status='I', PARTY_NAME='XXX-M'  where z.party_id=h1.party_id;

update mpfl_iou_holders
set flag =1
where VENDOR_ID = l1.vendor_id;

commit;

exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Data not migrated into ap_invoices_interface table...' ;

update mpfl_iou_holders
set flag =0
where VENDOR_ID = l1.vendor_id;
commit;

end;
end loop;
--commit;
end;
end loop; ---end of header loop
end;
/

exec APPS.mpfl_iou_cancelation();