Tuesday, April 7, 2015

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;

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect