Oracle HRMS Payroll Migration

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;
/

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.

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel