API to Create Phone

DECLARE

   l_phone_id                NUMBER := NULL;

   l_object_version_number   NUMBER := NULL;

   l_perent_table            VARCHAR2 (100) := 'PER_ALL_PEOPLE_F';

   l_phone_type              VARCHAR2 (1) := 'M'; -- Mobile

   l_err_msg                 VARCHAR2 (1000) := NULL;

   CURSOR c1

   IS

      SELECT t.ROWID, t.*, per.person_id

        FROM xxhr_phone_t t, per_people_x per

       WHERE     t.process_flag = 'N'

             AND TO_CHAR (t.employee_number) = per.employee_number;

BEGIN

   FOR i IN c1

   LOOP

      l_object_version_number := NULL;

      l_err_msg := NULL;

      l_phone_id := NULL;

      BEGIN

         hr_phone_api.create_or_update_phone (

            p_date_from               => SYSDATE,

            p_phone_type              => l_phone_type,

            p_phone_number            => i.phone_number,

            p_parent_id               => i.person_id,

            p_parent_table            => l_perent_table,

            p_effective_date          => SYSDATE,

            p_object_version_number   => l_object_version_number,

            p_phone_id                => l_phone_id);

         COMMIT;

         UPDATE xxhr_phone_t pos

            SET process_flag = 'Y', err_msg = NULL

          WHERE pos.ROWID = i.ROWID;

      EXCEPTION

         WHEN OTHERS

         THEN

            l_err_msg := SQLERRM;

            UPDATE xxhr_phone_t pos

               SET process_flag = 'N', err_msg = l_err_msg

             WHERE pos.ROWID = i.ROWID;

             COMMIT;

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

END;