Monday, October 12, 2020

API to delete an Employee Phone Number details from PER_PHONES (HR_PHONE_API.DELETE_PHONE)







            In this post, we have a given an script to delete the phone record details from Oracle Application. This comes handy during operating unit deactivation and need to delete all sensitive details of the employees from database.


Tested Instance: R12.2



SET SERVEROUTPUT ON; DECLARE CURSOR cur_phone_details (cv_emp_number VARCHAR2) IS SELECT pp.phone_id, pp.object_version_number, pp.phone_number FROM per_phones pp, per_all_people_f papf WHERE papf.person_id = pp.parent_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND pp.parent_table = 'PER_ALL_PEOPLE_F' AND papf.employee_number = cv_emp_number; lv_employee_number VARCHAR2(200) DEFAULT '123456'; ln_count NUMBER; BEGIN FOR phone_rec in cur_phone_details (lv_employee_number) LOOP ln_count := 0; HR_PHONE_API.DELETE_PHONE ( p_validate => FALSE, p_phone_id => phone_rec.phone_id, p_object_version_number => phone_rec.object_version_number ); SELECT COUNT(1) INTO ln_count FROM per_phones pp WHERE phone_id = phone_rec.phone_id; IF ln_count > 0 THEN DBMS_OUTPUT.PUT_LINE('Phone: '||phone_rec.phone_number||' Status: Failed!!'); ELSE DBMS_OUTPUT.PUT_LINE('Phone: '||phone_rec.phone_number||' Status: Success!!'); END IF; END LOOP; END;

0 Responses to “API to delete an Employee Phone Number details from PER_PHONES (HR_PHONE_API.DELETE_PHONE)”

Post a Comment


The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.