Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Oracle HRMS Payroll Migration
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 
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_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_id
INTO x_business_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.


Anil Passi

Comments   

0 #1 Hari 2007-02-23 00:00
Hi Anil
I am using hr_person_addre ss_api.create_p erson_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!
Quote
0 #2 Sharath Gajula 2007-08-05 17:22
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.
Quote
0 #3 a guest 2007-10-01 11:42
dear sharath
have you used Data Pump. pls. can use send me more information
Quote
0 #4 Himanshu Goyal 2008-01-14 05:11
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.
Quote
0 #5 ramkumar 2008-02-25 04:16
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.

re gards
ram
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner