Current Employee Validation Query in R12

Current Employee validation Query in R12


select distinct papf.person_id
  --into l_person_id
   from per_all_people_f papf ,
  per_all_assignments_f paa   ,
  per_assignment_status_types pas
  where papf.person_id            = paa.person_id
and paa.primary_flag              = 'Y'
and paa.assignment_type           = 'E'
and pas.user_status              IN ('Livex','Active Assignment','Active Contingent Assignment')
and paa.assignment_status_type_id =pas.assignment_status_type_id
and papf.current_employee_flag    ='Y'
and (papf.employee_number         =rec_key_members.employee_number
or papf.npw_number                =rec_key_members.employee_number)
and paa.primary_flag              = 'Y'
and paa.assignment_type          IN('E','C')
  --and     trunc(sysdate) between  papf.effective_start_date and papf.effective_end_date
  --and     trunc(sysdate) between  paa.effective_start_date and paa.effective_end_date
and trunc(:customer_start_date) between trunc(paa.effective_start_date) and trunc(paa.effective_end_date)
and paa.position_id is not null
and exisTS
  (select 'Y'
     from per_person_types ppt
    where ppt.person_type_id      = papf.person_type_id
  and ppt.active_flag             ='Y'
  and ppt.system_person_type NOT IN ('EX_EMP','EX_CWK','OTHER')
  );

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;

VENDOR OR SUPPLIER UPDATE SCRIPT IN R12

ap_suppliers update script :

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_rec_type;
cursor c_ven_l
is
select vendor_id from ap_suppliers where last_updated_by='-1';
/*
select av.vendor_id, aa.group_id,av.segment1 from ap_awt_groups aa,xxmb_ven_stg_bk1 xvs,ap_suppliers av
where aa.name=xvs.awt_group_name
and xvs.awt_group_name is not null
and av.segment1=xvs.vendor_number
and error_code='processed';
*/
/*select av.vendor_id, aa.group_id,av.segment1 from ap_awt_groups aa,xxmb_ven_stg xvs,ap_suppliers av
where aa.name=xvs.awt_group_name
and xvs.awt_group_name is not null
and av.segment1=xvs.vendor_number
and error_code='processed';
*/

begin



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);
--l_vendor_rec.segment1 := cv_ven_l.segment1;
l_vendor_rec.vendor_id := cv_ven_l.vendor_id;
--l_vendor_rec.awt_group_id :=cv_ven_l.group_id;
--l_vendor_rec.vendor_name := cv_ven_l.vendor_name;
--l_vendor_rec.start_date_active := sysdate;
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;
ap_vendor_pub_pkg.update_vendor (p_api_version      => 1.0,
p_init_msg_list    => 'f',
p_commit           => 'f',
x_return_status    => l_return_status,
p_validation_level     =>fnd_api.g_valid_level_full,
x_msg_count        => l_msg_count,
x_msg_data         => l_msg_data,
p_vendor_rec       => l_vendor_rec,
p_vendor_id        => cv_ven_l.vendor_id

);
dbms_output.put_line('cv_ven_l.vendor_id'||cv_ven_l.vendor_id);
dbms_output.put_line('l_msg_count'||l_msg_count);
dbms_output.put_line('l_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;