You can use API HR_PERSON_API.DELETE_PERSON to delete an employee(person) and the associated person related records.
DECLARE -- Input Variables l_validate BOOLEAN := FALSE; l_effective_date DATE := sysdate; l_person_id NUMBER := 123048; l_perform_predel_validation BOOLEAN := FALSE; -- Output Variables l_person_org_manager_warning VARCHAR2(2000); BEGIN -- 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 => l_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'); -- EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error : ' || sqlerrm); END; /
Some more information on the API parameters
When this parameter is set to TRUE, the API performs a check to see if any data in addition to that set up by default, e.g. data in per_all_people_f, per_all_assignments_f, per_periods_of_service exists for the person. If no additional data exists, then the API cascade deletes the person data. If any additional data exists for this person in any non-HRMS tables, then this person will not be deleted irrespective of whether only default data exists. When this parameter is set to FALSE, the API cascade deletes all data held in HRMS tables for this person, provided no additional data exists in any non-HRMS tables.
If the person being deleted is an organization manager, then a warning message will be returned otherwise no value will be returned.
- Employee and fnd_user link must be removed by removing the person in users form
- No Payroll should be processed on employee till-date
If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from ‘Employee’ to ‘Applicant’ and then use the above API again to purge the record.
Source: Arun Rathod’s blog