How to upload Employee Phone details in Oracle HRMS

I m only providing table and procedure definition.

You may create control file by yourself.

How to create control file for sql loader in Oracle

TABLE

CREATE TABLE DEV_EMPLOYEE_PHONE

(

  LINE_ID            NUMBER,

  EMP_CODE           VARCHAR2(150 BYTE),

  EMP_NAME           VARCHAR2(150 BYTE),

  PHONE_NUMBER       VARCHAR2(150 BYTE),

  TYPE               VARCHAR2(150 BYTE),

  EFFECTIVE_DATE     DATE,

  ERROR_DESCRIPTION  VARCHAR2(150 BYTE),

  PROCESS_FLAG       VARCHAR2(150 BYTE),

  OBJ_VER_NUMBER     NUMBER,

  PHONE_ID           NUMBER

)

PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.dev_create_emp_phone

IS

   CURSOR c_emp_ph

   IS

      SELECT line_id, emp_code, phone_number, TYPE, effective_date,

             process_flag, error_description, obj_ver_number, phone_id

        FROM dev_employee_phone;

       --WHERE NVL (process_flag, 'N') <> 'Y' AND error_description IS NULL;

   lc_c_emp_ph               c_emp_ph%ROWTYPE;

   l_phone_id                NUMBER;

   l_object_version_number   NUMBER;

   error_desc                VARCHAR2 (240);

   lv_ph_flag                CHAR (1);

   l_city                    VARCHAR2 (20);

   l_country                 VARCHAR2 (30);

   l_type                    VARCHAR2 (20);

   l_person_id               NUMBER;

BEGIN

   OPEN c_emp_ph;

   LOOP

      FETCH c_emp_ph

       INTO lc_c_emp_ph;

      EXIT WHEN c_emp_ph%NOTFOUND;

      BEGIN

---------------------------

--- Type Code

---------------------------

         SELECT lookup_code

           INTO l_type

           FROM hr_lookups

          WHERE lookup_type = 'PHONE_TYPE'

            AND UPPER (meaning) LIKE UPPER (TRIM (lc_c_emp_ph.TYPE));

---------------------------

--- Person ID

---------------------------

         BEGIN

            SELECT DISTINCT papf.person_id

                       INTO l_person_id

                       FROM per_all_people_f papf

                      WHERE papf.employee_number = lc_c_emp_ph.emp_code;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               lv_ph_flag := 'N';

               error_desc := 'No Such Employee';

            WHEN TOO_MANY_ROWS

            THEN

               lv_ph_flag := 'N';

               error_desc :=

                     'Same Employee Number is assigned to multiple employees';

         END;

         hr_phone_api.create_phone

                           (p_validate                   => FALSE,

                            p_phone_type                 => l_type,

                            p_phone_number               => lc_c_emp_ph.phone_number,

                            p_date_from                  => lc_c_emp_ph.effective_date,

                            p_effective_date             => lc_c_emp_ph.effective_date,

                            p_parent_id                  => l_person_id

                                                                       --, p_attribute_category        => 'Global Data Elements'

         ,

                            p_parent_table               => 'PER_ALL_PEOPLE_F',

                            p_phone_id                   => l_phone_id,

                            p_object_version_number      => l_object_version_number

                           );

         IF l_phone_id IS NOT NULL

         THEN

            lv_ph_flag := 'Y';

            error_desc := 'No Error';

         END IF;

         IF lv_ph_flag = 'Y'

         THEN

            UPDATE dev_employee_phone

               SET error_description = error_desc,

                   process_flag = lv_ph_flag,

                   phone_id = l_phone_id,

                   obj_ver_number = l_object_version_number

             WHERE emp_code = lc_c_emp_ph.emp_code

               AND line_id = lc_c_emp_ph.line_id;

         ELSE

            error_desc := error_desc || SQLERRM;

            UPDATE dev_employee_phone

               SET error_description = error_desc,

                   process_flag = 'N'

             WHERE emp_code = lc_c_emp_ph.emp_code

               AND line_id = lc_c_emp_ph.line_id;

         END IF;

         DBMS_OUTPUT.put_line ('Phone ID        : ' || l_phone_id);

      EXCEPTION

         WHEN OTHERS

         THEN

            lv_ph_flag := 'N';

            error_desc := error_desc || SQLERRM;

            UPDATE dev_employee_phone

               SET error_description = error_desc,

                   process_flag = lv_ph_flag

             WHERE emp_code = lc_c_emp_ph.emp_code

               AND line_id = lc_c_emp_ph.line_id;

            DBMS_OUTPUT.put_line (SQLERRM);

      END;

      lv_ph_flag := '';

      l_phone_id := '';

      l_object_version_number := '';

      error_desc := '';

  

   END LOOP;

   CLOSE c_emp_ph;

dbms_output.put_line('Phone ID        : '||l_phone_id);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

END;

/

Employee Address Creation

Leave a comment for any query.