Oracle Apps Knowledge Sharing

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.

API: HR_PHONE_API.DELETE_PHONE

Tested Instance: R12.2

Table Impacted: PER_PHONES and PER_ALL_PEOPLE_F

Script:

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;