Wednesday 26 September 2018

Script to delete the employee record using API in oracle


--Create Table

create table papf_16082018 (person_id number, employee_number varchar2(10));

--Insert Data

insert into papf_16082018 values (53687,'215677');


commit;

update apps.fnd_user set employee_id=null where employee_id in
                (select distinct p.person_id from apps.per_all_people_f p,papf_16082018 i where p.employee_number=i.employee_number);

commit;
               
set serveroutput on;
DECLARE
  -- Input Variables
  l_validate BOOLEAN    := FALSE;
  l_effective_date DATE := sysdate;
  l_person_id                 NUMBER    := 0;
  l_perform_predel_validation BOOLEAN   := FALSE;
  -- Output Variables
  l_person_org_manager_warning VARCHAR2(2000);
BEGIN

--Capture in Cursor
declare   
Cursor res is
  SELECT person_id,employee_number from papf_16082018;

--Start Loop

 BEGIN
  FOR rc in res
    LOOP
                BEGIN
        --API Update
                                --  Calling API HR_PERSON_API.DELETE_PERSON
                                                hr_person_api.delete_person(p_validate         => l_validate ,
                              p_effective_date             => l_effective_date ,
                              p_person_id                  => rc.person_id ,
                              p_perform_predel_validation  => l_perform_predel_validation ,
                              p_person_org_manager_warning => l_person_org_manager_warning );
                                                ddbms_output.put_line('Employee deleted successfully. Person ID ' || rc.person_id || ' Employee Number ' || rc.employee_number);
                                                COMMIT;
                EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Unable to delete Employee : '||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    END;       
    END LOOP;
               
 DBMS_OUTPUT.put_line ('Process Completed');
END;


EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error : ' || sqlerrm);
END;
/

2 comments: