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

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