Oracle HR – Script for Adding phone details for Employees

 The requirement was to add phone details ( Work phone number, work mobile number) a list of employees.

We have implemented this using hr_phone_api

Process Steps:

1. Create a temp table

CREATE TABLE load_phone_temp
AS
SELECT parent_id,phone_number work_phone,phone_number mobile_phone
  FROM per_phones
 WHERE 1=2;

2.Load data from excel workbook to temp table using sql loader. Sample control file below.

LOAD DATA
 INFILE *
 REPLACE INTO TABLE load_phone_temp
 FIELDS TERMINATED BY "," 
 ( parent_id, work_phone,mobile_phone )
 begindata
111,2222222,33333333
 
sqlldr control = "control_file_path" user_id=userid/password@DBServicename

3.Run the script below which picks up records from temp table and creates contact details for employees

/* Formatted on 2014/06/23 21:48 (Formatter Plus v4.8.8) */
CONNECT apps/&1
SET serveroutput on
 
DECLARE
   ln_work_phone_id           per_phones.phone_id%TYPE;
   ln_mobile_phone_id         per_phones.phone_id%TYPE;
   ln_object_version_number   per_phones.object_version_number%TYPE;
 
   CURSOR c_phone_tmp
   IS
      SELECT *
        FROM load_phone_temp;
 
   l_person_id                load_phone_temp.parent_id%TYPE;
   l_count                    NUMBER                                  := 0;
   l_loc                      NUMBER                                  := 0;
BEGIN
   l_loc := 1;
 
   FOR r_phone_tmp IN c_phone_tmp
   LOOP
      l_loc := 2;
      DBMS_OUTPUT.put_line (' Employee Number ' || r_phone_tmp.parent_id);
      l_count := l_count + 1;
      l_loc := 2;
 
      BEGIN
         SELECT person_id
           INTO l_person_id
           FROM per_all_people_f
          WHERE employee_number = r_phone_tmp.parent_id
            AND TRUNC (SYSDATE) BETWEEN effective_start_date
                                    AND effective_end_date;
 
         l_loc := 3;
 
         BEGIN
            IF r_phone_tmp.work_phone IS NOT NULL
            THEN
               --DBMS_OUTPUT.put_line ('Inside work phone');
 
               -- Create or Update Employee Phone Detail
-- -----------------------------------------------------------
               l_loc := 4;
               hr_phone_api.create_phone
                         (                   -- Input data elements
                                             -- -----------------------------
                          p_date_from                  => TRUNC (SYSDATE),
                          p_phone_type                 => 'W1',
                          p_phone_number               => r_phone_tmp.work_phone,
                          p_parent_id                  => l_person_id,
                          p_parent_table               => 'PER_ALL_PEOPLE_F',
                          p_effective_date             => TRUNC (SYSDATE),
-- Output data elements
-- --------------------------------
                          p_phone_id                   => ln_work_phone_id,
                          p_object_version_number      => ln_object_version_number
                         );
               DBMS_OUTPUT.put_line ('work phone id ' || ln_work_phone_id);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (   'Employee Number -> '
                                     || r_phone_tmp.parent_id
                                     || 'Error->'
                                     || SQLERRM
                                    );
         END;
 
         l_loc := 5;
 
         BEGIN
            IF r_phone_tmp.mobile_phone IS NOT NULL
            THEN
         --DBMS_OUTPUT.put_line ('Inside work mobile phone');
-- Create or Update Employee Phone Detail
-- -----------------------------------------------------------
               l_loc := 6;
               hr_phone_api.create_phone
                         (                   -- Input data elements
                                             -- -----------------------------
                          p_date_from                  => TRUNC (SYSDATE),
                          p_phone_type                 => 'WM',
                          p_phone_number               => r_phone_tmp.mobile_phone,
                          p_parent_id                  => l_person_id,
                          p_parent_table               => 'PER_ALL_PEOPLE_F',
                          p_effective_date             => TRUNC (SYSDATE),
-- Output data elements
-- --------------------------------
                          p_phone_id                   => ln_mobile_phone_id,
                          p_object_version_number      => ln_object_version_number
                         );
               DBMS_OUTPUT.put_line (   'Location '
                                     || l_loc
                                     || 'mobile phone id '
                                     || ln_mobile_phone_id
                                    );
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (   'Employee Number -> '
                                     || r_phone_tmp.parent_id
                                     || 'Error->'
                                     || SQLERRM
                                    );
         END;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Employee Number -> '
                                  || r_phone_tmp.parent_id
                                  || 'Error->'
                                  || SQLERRM
                                 );
      END;
   END LOOP;
 
   DBMS_OUTPUT.put_line ('Total No Of records processed -> ' || l_count);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (l_loc || ' ' || SQLERRM);
END;
/
 
SHOW ERR;

To check different phone types an employee can have, please check HR_LOOKUPS table for lookup type ‘PHONE_TYPE’

4. Word of caution – Though I have tested and implemented this, would suggest you test thoroughly before implementing it in LIVE.