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
