SQL query to get SIT values for employees in Oracle Apps HRMS

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.