hrms person address migration

By Leon Bennett,2014-11-09 22:39
14 views 0
hrms person address migration

    HRMS Person Address Migration

    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


     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



     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



     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



     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




     END LOOP;




     x_errm := SQLERRM;

     dbms_output.put_line('Fatal Error ' || x_errm);

     /* log_error(-1

     ,'Fatal Error ' || x_errm);




    Results of Oracle HRMS Address Migration API

Please note the following:-

    1. This example demonstrates creation of Address for Employee named Anil Passi with Employee Number 90909090.

    We created this Person record in article accessed via link

    In your case, you will be looping through the Legacy Address data and calling the below Oracle HRMS API to create Employee.

    2. The working example below uses minimal data for migration. You may need to add additional columns. 3. Copy past the code below, and run in your environment to see this work. However, do not forget to change the business group name.

    4. For non-UK implementers, you will need to use non GB version of the API.

Report this document

For any questions or suggestions please email