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;