Script to Update the Employee(per_all_people_f) and User(fnd_user) Email address through API

select * from per_all_people_f

where 1=1

and person_id = 1318

SELECT * FROM APPLSYS.WF_LOCAL_ROLES

WHERE 1=1

AND ORIG_SYSTEM_ID = 1318

select * from fnd_user

where 1=1

and employee_id = 1318

à==============================================================================

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_type_id = 9

--and person_id = 1318

order by full_name

à==============================================================================

DECLARE

  l_person_id                   per_people_f.person_id%TYPE := 1318;

  l_email_address               per_people_f.email_address%TYPE := 'RAJU@oracleapps88.com';

  l_employee_number             per_people_f.employee_number%TYPE;

  l_user_name                   fnd_user.user_name%TYPE;

  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,user_name

    into l_per_object_version_number ,l_employee_number,l_user_name

    from per_people_f,fnd_user

    where 1=1

    and person_id = employee_id

    and sysdate between effective_start_date and effective_end_date

    and person_id = l_person_id ;

   HR_PERSON_API.UPDATE_PERSON

    (

      p_validate                   => FALSE,

      p_effective_date             => TRUNC(SYSDATE), --TO_DATE ('06-APR-2015', '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              => l_email_address,

      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

    );

   FND_USER_PKG.UPDATEUSER

   (

      x_user_name                => l_user_name,

      x_owner                    => NULL,

      x_email_address            => l_email_address

   );

    DBMS_OUTPUT.PUT_LINE('Full Name : '|| v_full_name);

    DBMS_OUTPUT.PUT_LINE('User Name : '|| l_user_name);

    DBMS_OUTPUT.PUT_LINE('Email Address : '|| l_email_address);

    DBMS_OUTPUT.PUT_LINE('Effective start Date : '|| v_effective_start_date);

    DBMS_OUTPUT.PUT_LINE('Effective end Date : '|| v_effective_end_date);

    COMMIT;

EXCEPTION

WHEN OTHERS

  THEN DBMS_OUTPUT.PUT_LINE('Error Msg : '||SUBSTR(SQLERRM,1,200));

END;

à==============================================================================