Below query can be used to fetch value for any Special Information Type (SIT) in Oracle Apps HRMS.
SELECT
papf.employee_number,
papf.full_name,
ppos.actual_termination_date,
ppa.date_from,
ppa.date_to,
fifsv.id_flex_num,
(
SELECT
fifsv1.segment_name
|| ' : '
|| pac.segment1
FROM
per_analysis_criteria pac1,
apps.fnd_id_flex_segments_vl fifsv1
WHERE
pac1.analysis_criteria_id = pac.analysis_criteria_id
AND fifsv1.id_flex_num = pac.id_flex_num
AND fifsv1.application_column_name = 'SEGMENT1'
) segment1_value,
(
SELECT
fifsv1.segment_name
|| ' : '
|| pac.segment2
FROM
per_analysis_criteria pac1,
apps.fnd_id_flex_segments_vl fifsv1
WHERE
pac1.analysis_criteria_id = pac.analysis_criteria_id
AND fifsv1.id_flex_num = pac.id_flex_num
AND fifsv1.application_column_name = 'SEGMENT2'
) segment2_value,
(
SELECT
fifsv1.segment_name
|| ' : '
|| pac.segment3
FROM
per_analysis_criteria pac1,
apps.fnd_id_flex_segments_vl fifsv1
WHERE
pac1.analysis_criteria_id = pac.analysis_criteria_id
AND fifsv1.id_flex_num = pac.id_flex_num
AND fifsv1.application_column_name = 'SEGMENT3'
) segment3_value,
(
SELECT
fifsv1.segment_name
|| ' : '
|| pac.segment4
FROM
per_analysis_criteria pac1,
apps.fnd_id_flex_segments_vl fifsv1
WHERE
pac1.analysis_criteria_id = pac.analysis_criteria_id
AND fifsv1.id_flex_num = pac.id_flex_num
AND fifsv1.application_column_name = 'SEGMENT4'
) segment4_value
FROM
fnd_id_flex_structures_vl fifsv,
per_analysis_criteria pac,
per_person_analyses ppa,
per_all_people_f papf,
per_periods_of_service ppos
where fifsv.ID_FLEX_STRUCTURE_NAME = <SIT Title from the above screenshot>
AND fifsv.id_flex_num = pac.id_flex_num
AND ppa.person_id = papf.person_id
AND pac.analysis_criteria_id = ppa.analysis_criteria_id
AND ppos.person_id = papf.person_id
AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
;
The above query is designed to get values for 4 segments. The query can be modified by just adding the highlighted select column to fetch any number of segments.
For e.g. if you need to fetch value for the 5th segment, add below section in the select clause.
(
SELECT
fifsv1.segment_name
|| ' : '
|| pac.segment5
FROM
per_analysis_criteria pac1,
apps.fnd_id_flex_segments_vl fifsv1
WHERE
pac1.analysis_criteria_id = pac.analysis_criteria_id
AND fifsv1.id_flex_num = pac.id_flex_num
AND fifsv1.application_column_name = 'SEGMENT5'
) segment5_value
For more such queries, kindly drop a comment in the comment section.