hr_employee_api.create_employee API is used to create new employee with other important information as per the requirement.

hr_employee_api.create_employee

(p_hire_date                      => TO_DATE (’12-AUG-2023′),

p_business_group_id=>fnd_profile.value_specific(‘PER_BUSINESS_GROUP_ID’)

p_last_name                      => ‘ P_EMP_LAST_NAME ‘,

p_first_name                     => ‘ P_EMP_FIRST_NAME ‘,

p_middle_names                   => NULL,

p_sex                            => ‘M’,

p_national_identifier            => ‘256-54-575′,

p_date_of_birth                  => TO_DATE (’05-NOV-1990’),

p_known_as                       => ‘ P_DUMMY ‘,

p_employee_number                => lc_employee_number,

p_person_id                      => ln_person_id,

p_assignment_id                  => ln_assignment_id,

p_per_object_version_number      => ln_object_ver_number,

p_asg_object_version_number      => ln_asg_ovn,

p_per_effective_start_date       => ld_per_effective_start_date,

p_per_effective_end_date         => ld_per_effective_end_date,

p_full_name                      => lc_full_name,

p_per_comment_id                 => ln_per_comment_id,

p_assignment_sequence            => ln_assignment_sequence,

p_assignment_number              => lc_assignment_number,

p_name_combination_warning       => lb_name_combination_warning,

p_assign_payroll_warning         => lb_assign_payroll_warning,

p_orig_hire_warning              => lb_orig_hire_warning);

 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.