Introduction:

This Post illustrates the steps required to delete the employee records in Oracle EBS HRMS Module.

Script:

Step 1

–Create Table

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

Step 2

–Insert Data

insert into papf_16082018 values (53687,’215677′);
insert into papf_16082018 values (53689,’215696′);

commit;

Step 3

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;

Step 4

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 venkatesh.b@doyensys.com or use the comment section below to ask your questions

Share this post
Recent Posts

Leave a Comment

Start typing and press Enter to search