Query to get EIT and SIT in HRMS

Query to get EIT and SIT in HRMS

Query to get Extra Information Types (EIT) and Special Information Types (SIT) values from HRMS Module:

Say 'Certification Course Details', 'Visa Information'...etc are SITs in HRMS. Below example is used to get 'Certification Course Details' of employees:

SELECT   papf.employee_number, papf.full_name, ppt.user_person_type emptype,
         SUBSTR (hsck.concatenated_segments,
                 1,
                 INSTR (hsck.concatenated_segments, '|') - 1
                ) company_name,
         paaf.effective_start_date,
         TO_CHAR (TO_DATE (pac.segment2, 'YYYY/MM/DD HH24:MI:SS'),
                  'DD-MON-YYYY'
                 ) paid_date,
         pac.segment3 amount, pac.segment4 amnt_type, pac.segment1 course
    FROM per_all_people_f papf,
         per_all_assignments_f paaf,
         hr_soft_coding_keyflex hsck,
         per_person_analyses ppa,
         fnd_id_flex_structures fifs,
         per_special_info_types psit,
         per_analysis_criteria pac,
         per_person_types ppt
   WHERE paaf.person_id = papf.person_id
     AND ppt.person_type_id = papf.person_type_id
     AND pac.id_flex_num = fifs.id_flex_num
     AND fifs.id_flex_structure_code = 'Certification Course Details'
     AND ppt.person_type_id(+) = papf.person_type_id
     AND psit.id_flex_num = pac.id_flex_num
     AND ppa.person_id (+) = papf.person_id
     AND pac.analysis_criteria_id (+) = ppa.analysis_criteria_id
     AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id(+)
     AND paaf.assignment_type = 'E'
     AND ppt.user_person_type <> 'Ex-employee'
     AND papf.business_group_id = :p_business_group_id
     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
     AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
--and papf.effective_start_date between :p_from_date and :p_to_date
GROUP BY papf.full_name,
         papf.employee_number,
         ppt.user_person_type,
         paaf.effective_start_date,
         hsck.concatenated_segments,
         pac.segment2,
         pac.segment3,
         pac.segment4,
         pac.segment1;



Say 'Passport Details' is one the EITs in HRMS, then find the below query to get the passport information for particular employee

-- Data may have only in one field OR all fields
SELECT pei_information1,
       pei_information2,              
       pei_information3,
       pei_information4,
       pei_information5,
       pei_information6,
       pei_information7,
       pei_information8,
       pei_information9,
       pei_information10,
       pei_information11,
       pei_information12,
       pei_information13            
  FROM per_people_extra_info
 WHERE pei_information_category = 'Passport Details'    -- Could be any other EIT category
   AND person_id = :p_person_id;