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: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

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.
Image
Image
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

Comments   

0 #1 hany marawan 2007-10-28 13:15
Can we have an example from org api.

Thanks
Quote
0 #2 ManishC 2007-11-28 10:31
Extremly good doc, brief and informative enough.
Quote
0 #3 ManishC 2007-11-28 10:33
can you please point me to a pdf document provided by oracle specifically for SITs
Quote
0 #4 Jay Kavia 2008-11-07 10:39
Excellent write up, thanks Anil.

The database structure and workings of SIT's finally make sense to me! Now onto understanding EITs...

Thanks , Jay
Quote
0 #5 Anil Passi 2008-11-07 10:45 Quote
0 #6 dalia 2010-01-27 07:08
i need to disable fields in sit after saving applicant data
Quote
0 #7 Rakesh 2011-08-21 03:24
Hi,
First of all thanks for a nice document.

I need to know is there any way to restrict SIT access to a particular user.

Thanks & Regards
Rakesh Mukundan
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

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner