Script to delete the employee record using API in oracle Apps
Introduction
This Post illustrates the steps required to delete the employee record using API in Oracle EBS R12
Script to delete the employee record using API
–-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;
/
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
