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;
/
  • September 26, 2018 | 20 views
  • Comments