Oracle EBS R12 Delete Employee

1.

 update apps.fnd_user set employee_id=null

 WHERE employee_id IN 

 (SELECT DISTINCT person_id

                         FROM per_assignments_x

                        WHERE payroll_id = 149

                        and person_id <> 11694)

                        ;

--2. 

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, assignment_number EMPLOYEE_number

FROM per_assignments_x where payroll_id = 199

--and person_id = 11694

;

--–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

);

dbms_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;