PL/SQL Code using API to end date User Name of terminated Employees in Oracle APPS

PL/SQL Code using API to end date User Name of terminated Employees in Oracle APPS

Hi Everyone, below PL/SQL Script can be directly used to end date user name in oracle apps for all the terminated (or Ex-employees) Employees, if it has not been end-dated.


-- ------------------------------------------------
-- API to end date User Name of terminated Employees in Oracle APPS
-- ------------------------------------------------
set serveroutput on

DECLARE

  CURSOR C_EX_EMP      ----Cursor to get the list of employees that has to be updated
  IS 
  select papf.person_id , c.actual_termination_date, papf.full_name, papf.EMPLOYEE_NUMBER, papf.email_address,  fu.USER_NAME 
  from per_all_people_f papf, fnd_user fu, per_periods_of_Service c 
  where  
    sysdate between papf.effective_start_date and effective_end_date 
    and papf.person_id=c.person_id
    and c.actual_termination_date is not null 
    and papf.person_type_id = 1155
    and fu.employee_id = papf.person_id
    and fu.END_DATE is null
    ORDER BY papf.LAST_UPDATE_DATE DESC
;

--    lc_user_name                           VARCHAR2(100)   := 'DARSHAN.V@OLACABS.COM';
--    ld_user_end_date                    VARCHAR2(100)  := TO_DATE('05-JUN-2018');
--    lc_email_address                     VARCHAR2(100)  := 'darshn.v@olacabs.com';

BEGIN

for rec_ex in C_EX_EMP
loop
   apps.fnd_user_pkg.updateuser
   (  x_user_name                        => rec_ex.USER_NAME,
      x_owner                            => NULL,
      x_end_date                         => rec_ex.actual_termination_date,
      x_employee_id                      =>rec_ex.person_id,
      x_email_address                    => rec_ex.email_address
   );
 DBMS_OUTPUT.put_line ('User ' || rec_ex.USER_NAME || ' is Updated successfully');

end loop;
 COMMIT;


EXCEPTION
    WHEN OTHERS THEN
                      ROLLBACK;
                      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
SHOW ERR;
----------

-----------------
-- API to end date User Name of terminated Employees in Oracle APPS

Leave a Reply