Lets test with hr_person_address_api
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*/
Lets say you wish to migrate a person address record of following data in Oracle HRMS
Address Line 1 : Martket Stree
Address Line 2 : London
Date FROM : SYSDATE - 1
Employee number : 90909090
Person Id : 134593
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;
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;
staging table is a table where data is held temporarily for transformation, enrichment and validation before it is moved to the final destination. Staging tables are typically used for conversion and interfaces.
- Transformation includes activities such as changing formats and data types, e.g. the character string "23-SEP-2006" into the date September 23 2006; mapping one value to another, e.g. from 1 to "M".
- Enrichment is adding new data to the records that were not available in the source.
- Validation is checking that the values in fields in a record are valid and that the record is internally consistent. --replace this with your actual source table
