ORACLE APPS R12 create Employee Address

 TABLE: XX_CREATE_EMP_ADDRESS

DECLARE 

  lrec_person_details    per_all_people_f%ROWTYPE;

  ln_object_version_num  NUMBER;

  ln_address_id          NUMBER;

  ln_exists              NUMBER;

  lv_employee_num        VARCHAR2(200);-- DEFAULT '123456';

  v_api_error   VARCHAR2(2000);

  CURSOR C1 IS

  

  SELECT *

    --INTO lrec_person_details

    FROM per_all_people_f papf

    , XX_CREATE_EMP_ADDRESS XXFIA

   WHERE  current_employee_flag = 'Y'

     AND 'FIA'||XXFIA.EMP_NUMBER = PAPF.EMPLOYEE_NUMBER

     AND PAPF.EMPLOYEE_NUMBER <> 'XX1442'

     AND SYSDATE BETWEEN effective_start_date AND effective_end_date; 

     

  

BEGIN 

FOR lrec_person_details IN C1 LOOP

  

     --SELECT * FROM PER_ADDRESSES WHERE PERSON_ID = 47856;

     

  BEGIN

  HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS

        (p_validate              => FALSE,

         p_effective_date        => lrec_person_details.effective_start_date,

         p_person_id             => lrec_person_details.person_id,

         p_primary_flag          => 'Y',

         p_style                 => 'AE',

         p_date_from             => lrec_person_details.effective_start_date, 

         --p_address_line1         => '11, TEST ADDRESS LINE',

         --p_address_line2         => NULL,

         p_address_line3         => lrec_person_details.ADDRES_LINE3,

         p_country               => 'AE',

         --p_postal_code           => '40400',

         --p_region_1              => 'SELANGOR',

         P_TOWN_OR_CITY          => lrec_person_details.TOWN_OR_CITY,

         p_address_id            => ln_address_id,

         p_object_version_number => ln_object_version_num

        );

   DBMS_OUTPUT.PUT_LINE('Address id: '||ln_address_id); 

  COMMIT;

   UPDATE  XX_CREATE_EMP_ADDRESS

   SET STATUS = 'done', MESSAGE = NULL

   WHERE 'FIA'||EMP_NUMBER = lrec_person_details.EMPLOYEE_NUMBER

   ;

   EXCEPTION WHEN OTHERS THEN

   v_api_error   := sqlerrm;

     UPDATE  XX_CREATE_EMP_ADDRESS

   SET STATUS = 'Error', Message = v_api_error

   WHERE 'FIA'||EMP_NUMBER = lrec_person_details.EMPLOYEE_NUMBER

   ;

   END;

   END LOOP;

   EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;