VENDOR SITE UPDATE SCRIPT OR SUPPLIER SITE UPDATE SCRIPT IN R12

vendor site update script or supplier site update script in R12

procedure update_api
is
l_msg_data                   varchar2 (20000);
l_return_status              varchar2 (100);
l_msg_count                  number;
l_party_id  number;
l_vendor_rec                 ap_vendor_pub_pkg.r_vendor_site_rec_type;
vendor_site_id number         :=null;
cursor c_ven_l
is
/*
select av.vendor_id,avs.vendor_site_id, avs.org_id,bb.prepay_code_combination_id,av.segment1,avs.vendor_site_code from xxmb_ven_site_stg_bk2 aa,xxmb_ven_site_stg bb, gl_code_combinations_kfv cc,ap_suppliers av, ap_supplier_sites_all avs,hr_operating_units hou
where aa.vendor_number=bb.vendor_number
and aa.employee_number=bb.employee_number
and aa.error_code='processed'
and aa.vendor_site_code=bb.vendor_site_code
and aa.operating_unit_name=bb.operating_unit_name
and bb.prepay_code_combination=cc.concatenated_segments
and bb.prepay_code_combination_id=cc.code_combination_id
and bb.vendor_number=av.segment1
and bb.operating_unit_name= hou.name
and hou.organization_id= avs.org_id
and av.vendor_id=avs.vendor_id;
*/
select av.vendor_id,avs.vendor_site_id, aa.group_id,av.segment1,avs.vendor_site_code, avs.org_id,xvs.awt_group_name,avs.awt_group_id
from ap_awt_groups aa, xxmb_ven_site_stg_bk1 xvs,ap_suppliers av,ap_supplier_sites_all avs,hr_operating_units hou
where aa.name=xvs.awt_group_name
and xvs.operating_unit_name= hou.name
and avs.org_id= hou.organization_id
and xvs.awt_group_name is not null
and av.segment1=xvs.vendor_number
and av.vendor_id=avs.vendor_id
and avs.vendor_site_code=xvs.vendor_site_code
and error_code='processed'
--and a
and avs.awt_group_id is  null
--and avs.org_id=90
--and xvs.vendor_number=16810
order by 4;
/*
select av.vendor_id,avs.vendor_site_id, aa.group_id,av.segment1,avs.vendor_site_code, avs.org_id,xvs.awt_group_name,avs.awt_group_id
from ap_awt_groups aa,xxmb_ven_site_stg xvs,ap_suppliers av,ap_supplier_sites_all avs,hr_operating_units hou
where aa.name=xvs.awt_group_name
and xvs.operating_unit_name= hou.name
and avs.org_id= hou.organization_id
and xvs.awt_group_name is not null
and av.segment1=xvs.vendor_number
and av.vendor_id=avs.vendor_id
and avs.vendor_site_code=xvs.vendor_site_code
and error_code='processed'
--and avs.org_id=90
--and xvs.vendor_number=16810
order by 4;
--and avs.vendor_site_id=11600;
*/
begin

--mo_global.set_policy_context('s',90);

for cv_ven_l in c_ven_l
loop
l_return_status := null;
l_msg_count := null;
l_msg_data := null;
--  l_vendor_id := null;

l_party_id := null;
l_vendor_rec := null;
--dbms_output.put_line ('updating vendor  ' || cv_ven_l.segment1);
--dbms_output.put_line('updating vendor  ' || cv_ven_l.segment1||'cv_ven_l.org_id'||cv_ven_l.org_id||'cv_ven_l.vendor_site_code'||cv_ven_l.vendor_site_code);
--l_vendor_rec.vendor_id=cv_ven_l.vendor_id;
--l_vendor_rec.vendor_site_id=cv_ven_l.vendor_site_id;
--l_vendor_rec.segment1 := 23055--cv_ven_l.segment1;
--l_vendor_rec.vendor_name := cv_ven_l.vendor_name;
--l_vendor_rec.start_date_active := sysdate;
l_vendor_rec.vendor_id:=cv_ven_l.vendor_id;--3174;
l_vendor_rec.vendor_site_id:=cv_ven_l.vendor_site_id;--2762;
--l_vendor_rec.prepay_code_combination_id:=cv_ven_l.prepay_code_combination_id;
l_vendor_rec.awt_group_id:=cv_ven_l.group_id;
l_vendor_rec.allow_awt_flag:='y';
--l_vendor_rec.org_id:=88;--cv_ven_l.org_id;
--l_vendor_rec.vendor_site_code:=cv_ven_l.vendor_site_code;
--l_vendor_rec.phone:='9822310522';
vendor_site_id:=cv_ven_l.vendor_site_id;

fnd_global.apps_initialize(user_id => fnd_global.user_id,resp_id => fnd_global.resp_id,resp_appl_id => fnd_global.resp_appl_id);
fnd_msg_pub.initialize;
mo_global.init ('sqlap');

ap_vendor_pub_pkg.update_vendor_site
(p_api_version      => 1.0,
p_init_msg_list     => 'f',
p_commit            => 'f',
p_validation_level  => fnd_api.g_valid_level_full,
x_return_status     => l_return_status,
x_msg_count         => l_msg_count,
x_msg_data          => l_msg_data,
p_vendor_site_rec   => l_vendor_rec,
p_vendor_site_id    => vendor_site_id
--p_calling_prog    => l_party_id

);
--dbms_output.put_line ('c_ven_l'||cv_ven_l.segment1);
--dbms_output.put_line ('c_ven_l'||cv_ven_l.vendor_site_code);
--dbms_output.put_line ('c_ven_l'||cv_ven_l.org_id);
dbms_output.put_line ('c_ven_l'||cv_ven_l.vendor_site_id);
dbms_output.put_line ('x_msg_count'||l_msg_count);
dbms_output.put_line ('x_msg_data'||l_msg_data);
dbms_output.put_line ('l_return_status'||l_return_status);
commit;
end loop;
end update_api;


procedure update_main (
errbuf         out      varchar2,
retcode        out      varchar2
)
is
begin
update_api;
end update_main;