This article uses the GB (United Kingdom) version of the API. The name of this API is hr_person_address_api.create_gb_person_address.

First lets begin with questions and answers....

Question : Where is Address data for people stored in Oracle HRMS?
Answer : It is stored in table named PER_ADDRESSES

Question : How does record in PER_ADDRESSES relate to Person?
Answer : This table has a column named PERSON_ID that joins to PER_ALL_PEOPLE_F

Question : Is PER_ADDRESSES date-tracked just like PER_ALL_PEOPLE_F?
Answer : No true date-tracking exists on this table, however it does have a column named DATE_FROM.

Lets say you wish to migrate a person address record of following data in Oracle HRMS
Address Line 1 : Woodlands Street
Address Line 2 : London
Date FROM : SYSDATE - 1
Employee number : 90909090
Person Id : 134593

DECLARE
  x_business_group_id NUMBER;
  x_person_id         NUMBER;
  x_address_id        NUMBER;
  x_obj_no            NUMBER;
  x_errm              VARCHAR2(100);

  CURSOR legacy_address_cursor IS
  /*replace the hard-coding with your source data*/
    SELECT '90909090' AS employee_number
          ,'Woodlands Street' AS addr_line1
          ,'London' AS addr_line2
          ,'SW1 1DB' AS post_code
          ,'07968875963' AS tel_no
          ,134593 AS person_id
           /*as you have already migrated this person*/
          ,trunc(SYSDATE) - 1 date_from
    /*you can make this to be the same as start date of person*/
    FROM dual
    --replace this with your actual source table
    ;
BEGIN

  SELECT business_group_id
  INTO x_business_group_id
  FROM per_business_groups
  WHERE NAME = 'Your Buss Group Name here or Setup Business Group';

  FOR p_record IN legacy_address_cursor
  LOOP
 
    BEGIN
   
      hr_person_address_api.create_gb_person_address(p_validate                => FALSE
                                                    ,p_effective_date          => SYSDATE
                                                    ,p_pradd_ovlapval_override => FALSE
                                                    ,p_person_id               => p_record.person_id
                                                    ,p_primary_flag            => 'Y'
                                                    ,p_date_from               => p_record.date_from
                                                    ,p_address_line1           => initcap(p_record.addr_line1)
                                                    ,p_address_line2           => initcap(p_record.addr_line2)
                                                    ,p_postcode                => p_record.post_code
                                                    ,p_country                 => 'GB'
                                                    ,p_telephone_number        => p_record.tel_no
                                                    ,p_address_id              => x_address_id
                                                    ,p_object_version_number   => x_obj_no
                                                    ,p_date_to                 => NULL
                                                    ,p_address_type            => NULL
                                                    ,p_comments                => NULL);
   
      dbms_output.put_line('Address for person_id=>' || p_record.person_id ||
                           ' has been loaded');
      --update the legacy source address table to change migration status of record
   
    EXCEPTION
      WHEN OTHERS THEN
     
        x_errm := SQLERRM;
        dbms_output.put_line('Error when migrating Address for person_id=>' ||
                             p_record.person_id || '  ' || x_errm);
     
      /*        log_error(p_record.person_id
                       ,x_errm);
      */
    END;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    x_errm := SQLERRM;
    dbms_output.put_line('Fatal Error ' || x_errm);
    /*        log_error(-1
                     ,'Fatal Error ' || x_errm);
    */
END;
/

Results of Oracle HRMS Address Migration API

Comments (5)

Subscribe to this comment's feed

...
written by Hari , February 23, 2007

Hi Anil
I am using hr_person_address_api.create_person_address to create person address. I cant save the record until i give county and country, though they are non mandatory fields to API. I was getting error Non-existent country. I am able to save the record after giving county and country. Can you explain why!

...
written by Sharath Gajula , August 05, 2007

Hi Anil,
Instead of user writing own procedure to call the API, I feel it would be good if they use Data Pump which is a powerful tool to migrate data from Legacy system to Oracle.

...
written by a guest , October 01, 2007

dear sharath
have you used Data Pump. pls. can use send me more information
alruwaished@hotmail.com

How to migrate the address data which is having the multiple business groups
written by Himanshu Goyal , January 14, 2008

I am told migrated the address data of 10 bg groups with one inbound program ,but the address style are different for different bg...I mean...for denmark PE_information 10--14 are are active and for others say 12---14 are required ....can we make an inbound program for all business groups.

Assignment conversion
written by ramkumar , February 25, 2008

Hi anil,

can please give an example on assignment conversion.All the examples you ar giving as hard coded ones.but the people neeed total conversion flow in HRMS without hardcoding.
and i request you to present one topic on error hadnling mechanism for conversions and interfaces.

regards
ram