Oracle HRMS Payroll Migration

In this article, I will explain how to migrate Special Information Types into Oracle HRMS using API.

Before I jump to give the script for migration of SIT, let me first explain the background so that you are able to troubleshoot the errors yourself during the data migration into oracle's special information types.

Question: How is the special information types configured?
Answer: A dedicated article has been written to explain the background and setup details for Special Information Types. This migration example uses the SIT created in article as linked here .


Question: I find the special information type tables confusing. Please can you explain?
Answer : The combination of Segments is stored in table per_analysis_criteria.
This combination is identified by analysis_criteria_id.
Next in table per_person_analyses, analysis_criteria_id is linked to the Person Id.
Effectively, this means that a given combination of segments can be assigned to various Person Records. This is fundamental to the nature of Key Flex Fields. This will get clearer at the very end of this article.

Question: Give me the example of the SIT, to which we will migrate values.
Answer: For this training exercise, we will assume following SIT exists in Oracle Apps.
SIT Name : XX Medical History Of Person
SIT Fields:
Medical Condition(Segment1)
Year of illness (Segment2)
Cured Now Flag [Yes/No] (Segment3)
Note: We configured & created this SIT in article as linked here .

Let’s migrate this data against the PERSON_ID which we migrated in Article(link here for Person Migration Article ).
Person Id = 134593 was created as a result of that migration.

Run, the below SQL, to migrate SIT Data
DECLARE
v_count INTEGER := 0;
n_object_version_number INTEGER;
n_analysis_criteria_id INTEGER;
n_person_analysis_id INTEGER;
n_pea_object_version_number INTEGER;
n_id_flex_num INTEGER;

BEGIN
SELECT fi.id_flex_num
INTO n_id_flex_num
FROM fnd_id_flex_structures_vl fi
WHERE (fi.id_flex_structure_code = 'XX Medical History of Person')
AND (application_id = 800)
AND (id_flex_code = 'PEA');

LOOP
BEGIN
---reset the variables here
n_object_version_number := NULL;
n_analysis_criteria_id := NULL;
n_person_analysis_id := NULL;
n_pea_object_version_number := NULL;

hr_sit_api.create_sit(p_person_id => 134593
,p_business_id => fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
,p_id_flex_num => n_id_flex_num
,p_effective_date => SYSDATE
,p_date_from => SYSDATE
,p_date_to => NULL
,p_segment1 => 'Influenza'
,p_segment2 => '2000'
,p_segment3 => 'Y'
,p_analysis_criteria_id => n_analysis_criteria_id
,p_person_analysis_id => n_person_analysis_id
,p_pea_object_version_number => n_pea_object_version_number);
dbms_output.put_line('Migrated SIT with n_analysis_criteria_id=>' ||
n_analysis_criteria_id);

v_count := v_count + 1;
IF MOD(v_count
,50) = 0
THEN
--do a commit for each 50 records during migration
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
--need to log error here
dbms_output.put_line('Exception ' || SQLERRM);
/* xx_error(p_migration_type => 'MIGRATION TYPE'
,p_error_message => SQLERRM
,p_resolution => 'Enter details manually'
,p_person_id => 134593);
*/
END;
EXIT; --in this case just one record
END LOOP;

COMMIT;
END;


Read further, if you love digging into the details...
Here is our SIT data, after migration, as seen from the screen.


Here are the values that we migrated against SIT in the Oracle HRMS tables.
SELECT fi.id_flex_num,
FROM fnd_id_flex_structures_vl fi
WHERE (fi.id_flex_structure_code = 'XX Medical History of Person')
AND (application_id = 800)
AND (id_flex_code = 'PEA');
=======Returns=======
ID_FLEX_NUM : 50522


SELECT analysis_criteria_id, segment1, segment2, segment3
FROM per_analysis_criteria
WHERE id_flex_num = 50522
=======RETURNS=======
ANALYSIS_CRITERIA_ID : 311542
SEGMENT1 : Influenza
SEGMENT2 : 2000
SEGMENT3 : Y


SELECT * FROM per_person_analyses WHERE person_id = 134593
=======RETURNS=======
ANALYSIS_CRITERIA_ID : 311542 --Note the same value as in previous SQL



Note that the combination of Medical Condition, Illness Year & Cured Flag is not directly attached to the PERSON_ID. Hence, if another of your employee was to have exactly the same medical illness, on the same date, and is also cured....then oracle will not create a new record in table per_analysis_criteria, as Oracle Flexfield Engine will reuse this combination of codes for other employee. Effectively the same principles apply to gl_code_combinations & code_combination_id.

Anil Passi

Add comment