<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
This website has now moved to http://apps2fusion.com





HRMS Person Address Migration | Print |  E-mail
Written by Anil Passi   
Friday, 17 November 2006
In this article, I will explain how to migrate or interface Addresses for Employees / People  in Oracle HRMS application.




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
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.


Comments (5)add
...
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
You must be logged in to a comment. Please register if you do not have an account yet.

busy