Oracle Apps Knowledge Sharing

API to load Employee Address in R12 (HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS)

In this post, we have given a sample script to associate an address to existing employee in R12.

Tested Instance: R12.2

API: HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS

TABLES:

per_all_people_f

per_addresses

 Query to Get list of defined Address Styles: 

SELECT *
      FROM fnd_descr_flex_contexts
     WHERE descriptive_flexfield_name  = 'Address Structure'
       AND descriptive_flex_context_code = :style
       AND enabled_flag                  = 'Y'
       AND application_id                = 800;
Script: 
SET SERVEROUTPUT ON;
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';
  
BEGIN 

  SELECT papf.*
    INTO lrec_person_details
    FROM per_all_people_f papf
   WHERE papf.employee_number = lv_employee_num
     AND current_employee_flag = 'Y'
     AND SYSDATE BETWEEN effective_start_date AND effective_end_date; 
     
  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                 => 'GENERIC',
         p_date_from             => SYSDATE, 
         p_address_line1         => '11, TEST ADDRESS LINE',
         p_address_line2         => NULL,
         p_address_line3         => NULL,
         p_country               => 'MY',
         p_postal_code           => '40400',
         p_region_1              => 'SELANGOR',
         p_address_id            => ln_address_id,
         p_object_version_number => ln_object_version_num
        );
   DBMS_OUTPUT.PUT_LINE('Address id: '||ln_address_id); 

   SELECT COUNT(1)
     INTO ln_exists
     FROM per_addresses
    WHERE address_id = ln_address_id
      AND person_id = lrec_person_details.person_id ;
   IF ln_exists > 0
   THEN 
     DBMS_OUTPUT.PUT_LINE('Address successfully attached to person');   
   ELSE 
     DBMS_OUTPUT.PUT_LINE('Address assignment failed'); 
   END IF;
END;