Script to Update the Employee(per_all_people_f) and User(fnd_user) Email address through API from Backend in EBS


If you know username find the user employee id From below
 
select * from fnd_user where 1=1
and username='HSINGH'


Find the value from per_all_people_f

select * from per_all_people_f where 1=1
and person_id = 8426;


SELECT * FROM APPLSYS.WF_LOCAL_ROLES WHERE 1=1
AND ORIG_SYSTEM_ID = 8426;


select * from fnd_user where 1=1
and employee_id = 8426;



Details:

select
ppf.person_id,
ppf.last_name,
ppf.full_name,
ppf.email_address emp_email,
rol.name,
rol.email_address rol_email,
usr.user_id,
usr.email_address usr_email
from
per_all_people_f ppf,
applsys.wf_local_roles rol,
fnd_user usr
where 1=1
and ppf.person_id = rol.orig_system_id
and rol.name = usr.user_name
and ppf.person_id = usr.employee_id
and sysdate between effective_start_date and effective_end_date
and person_id = 8426
order by full_name;


API to update

declare
l_employee_number per_people_f.employee_number%TYPE;
l_person_id per_people_f.person_id%TYPE := 8426;
l_per_object_version_number per_people_f.object_version_number%TYPE;
v_effective_start_date date;

v_effective_end_date date;
v_full_name varchar2(100);
v_comment_id number;
v_name_combination_warning boolean;
v_assign_payroll_warning boolean;
v_orig_hire_warning boolean;
begin

Select object_version_number ,employee_number
into l_per_object_version_number ,l_employee_number
from per_people_f
where person_id = l_person_id ;

hr_person_api.update_person(
p_validate => FALSE
,p_effective_date => to_date('10-APR-2023','DD-MON-YYYY')
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_email_address => 'hsingh@funoracleapps.com'
,p_effective_start_date => v_effective_start_date
,p_effective_end_date => v_effective_end_date
,p_full_name => v_full_name
,p_comment_id => v_comment_id
,p_name_combination_warning => v_name_combination_warning
,p_assign_payroll_warning => v_assign_payroll_warning
,p_orig_hire_warning => v_orig_hire_warning
);

commit;
end;
/





If you like please follow and comment