-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Home |
Technical Articles |
Training Articles |
Receive Email for New Articles |
Contributors |
Apps Book |
Migrate Special Information Types in HRMS | | Print | |
Written by Anil Passi | |
Wednesday, 15 November 2006 | |
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_group_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. Comments
(3)
![]()
Extremly good doc, brief and informative enough.
can you please point me to a pdf document provided by oracle specifically for SITs
You must be logged in to a comment. Please register if you do not have an account yet.
|
Thanks