Oracle Apps Knowledge Sharing

set serveroutput on;

DECLARE

  ln_phone_id                 PER_PHONES.PHONE_ID%TYPE;

  ln_object_version_number    PER_PHONES.OBJECT_VERSION_NUMBER%TYPE;

  ld_start_date               DATE;

  ln_person_id                NUMBER;

  lv_emp_num                  VARCHAR2(100);

  position_                   NUMBER;

  routine_                    VARCHAR2(2000);

 

  CURSOR cur_emp_data

  IS

  -- This part can be changed with different query with staging table

  SELECT 123456789 telephone_number,

         'EMP123456' employee_number

    FROM dual;

BEGIN

    position_ :=1200;

    routine_  := 'UPDATE_EMPLOYEE_PHONE_RECORD';

    FOR rec_emp_data IN cur_emp_data

    LOOP

      SELECT effective_start_date,

             person_id

        INTO ld_start_date,

             ln_person_id

        FROM per_all_people_f

       WHERE employee_number = rec_emp_data.employee_number

         AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

      

       BEGIN

         position_                := 1210;

         ln_phone_id              := NULL;

         ln_object_version_number := NULL;

        

         BEGIN

           SELECT phone_id,

                  object_version_number

             INTO ln_phone_id,

                  ln_object_version_number

             FROM per_phones

            WHERE parent_id = ln_person_id

              AND date_from = ld_start_date;

        EXCEPTION

          WHEN OTHERS THEN

            NULL;

        END;

        

         hr_phone_api.create_or_update_phone

        (p_date_from              => ld_start_date,

         p_phone_type             => 'W1',

         p_phone_number           => rec_emp_data.telephone_number,

         p_parent_id              => ln_person_id,

         p_parent_table           => 'PER_ALL_PEOPLE_F',

         p_effective_date         =>  SYSDATE,

         p_phone_id               => ln_phone_id,

         p_object_version_number  => ln_object_version_number

        );

         position_ :=1220;

        

         DBMS_OUTPUT.PUT_LINE('ln_person_id : '||ln_person_id );

         DBMS_OUTPUT.PUT_LINE('ln_phone_id : '||ln_phone_id );

         DBMS_OUTPUT.PUT_LINE('rec_emp_data.telephone_number : '||rec_emp_data.telephone_number );

        

         COMMIT;

         DBMS_OUTPUT.PUT_LINE('API : create_or_update_phone successfull for employee : '||rec_emp_data.employee_number);

         position_ :=1230;        

       EXCEPTION

         WHEN OTHERS THEN             

            DBMS_OUTPUT.PUT_LINE('Others Error in API : create_or_update_phone for employee : ' ||

                                 rec_emp_data.employee_number|| '  SQLERRM:  ' || SQLERRM

                                );

      

       END;

    END LOOP;   

    position_ :=1250;

  EXCEPTION

    WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('Others Error in update_employee_phone_number on position_ ' ||

                    position_ || '. SQLERRM:  ' || SQLERRM);

ROLLBACK;

END;