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();
=======================================
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();
No comments:
Post a Comment