Skip to content

Green color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Technical Articles arrow HR and Payroll arrow Oracle HRMS Payroll Migration arrow Migrate Special Information Types in HRMS
This website has now moved to http://apps2fusion.com



Our OA Framework, BPEL Development & Apps DBA Trainings from USD 299 only [on weekends] . Click here for details.
Also see here fully verifiable feedbacks/testimonials


Migrate Special Information Types in HRMS | Print |  E-mail
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.
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.

Comments (3)add
Org API
written by hany marawan , October 28, 2007
Can we have an example from org api.

Thanks
Mr.
written by ManishC , November 28, 2007
Extremly good doc, brief and informative enough.
...
written by ManishC , November 28, 2007
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.

busy