Query for getting SIT Details for the Employees

SELECT
papf.employee_number,
papf.full_name,
pac.segment1,
pac.segment2,
pac.segment3,
pac.segment4,
pac.segment5
FROM
per_all_people_f papf,
fnd_id_flex_structures_tl fifs,
per_analysis_criteria pac,
per_person_analyses ppa
WHERE
papf.business_group_id                 = P_BUSINESS_GROUP_ID
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.current_employee_flag         = 'Y'
AND UPPER(fifs.id_flex_structure_name) = UPPER('Passport')
AND fifs.id_flex_num = pac.id_flex_num
AND ppa.id_flex_num = fifs.id_flex_num
AND ppa.analysis_criteria_id = pac.analysis_criteria_id
AND ppa.person_id = papf.person_id;

Query to get the Competence Details for the PERFORMANCE level

SELECT
pc.name,
prl.step_value
FROM
per_appraisal_templates     pat,
per_assessments    pass,
per_competence_elements    pce,
per_competence_elements    pce1,
per_competences    pc,
per_rating_levels    prl
WHERE
pat.appraisal_template_id = <P_APPRAISAL_TEMPLATE_ID>
AND pat.assessment_type_id = pass.assessment_type_id
AND pass.appraisal_id = <P_APPRAISAL_ID>                        
AND pce.assessment_type_id = pat.assessment_type_id
AND UPPER(pce.type) = UPPER('ASSESSMENT_GROUP')
AND pce1.parent_competence_element_id = pce.competence_element_id
AND pce1.assessment_id = pass.assessment_id          
AND UPPER(pce1.type) = UPPER('ASSESSMENT')
AND pc.competence_id = pce1.competence_id            
AND pce1.rating_level_id = prl.rating_level_id; 

Query for getting the Competences attached to an Appraisal:

SELECT
pc.name,
prl.step_value
FROM
per_appraisal_templates     pat,
per_assessments    pass,
per_competence_elements    pce,
per_competence_elements    pce1,
per_competences    pc,
per_rating_levels    prl
WHERE
pat.appraisal_template_id = <P_APPRAISAL_TEMPLATE_ID>
AND pat.assessment_type_id = pass.assessment_type_id
AND pass.appraisal_id = <P_APPRAISAL_ID>                        
AND pce.assessment_type_id = pat.assessment_type_id
AND UPPER(pce.type) = UPPER('ASSESSMENT_GROUP')
AND pce1.parent_competence_element_id = pce.competence_element_id
AND pce1.assessment_id = pass.assessment_id
AND UPPER(pce1.type) = UPPER('ASSESSMENT')
AND pc.competence_id = pce1.competence_id            
AND pce1.proficiency_level_id = prl.rating_level_id; 

Query for getting the Competences attached to an Assessment Template

SELECT
pce.group_competence_type,
pc.name,
pce1.attribute1
FROM
per_competence_elements     pce,
per_competence_elements     pce1,
per_competences             pc
WHERE
pce.assessment_type_id = <P_ASSESSMENT_TYPE_ID>
AND UPPER(pce.type) = UPPER('ASSESSMENT_GROUP')
AND pce1.parent_competence_element_id = pce.competence_element_id
AND UPPER(pce1.type) = UPPER('ASSESSMENT_COMPETENCE')
and pce1.competence_id = pc.competence_id;

Query for getting all the Flex Values for a given Flex Structure/Segment Name:

SELECT
ffvv.flex_value
FROM
fnd_id_flex_structures fifs,
fnd_id_flex_segments fifs1,
fnd_flex_values_vl ffvv
WHERE
UPPER(fifs.id_flex_structure_code) = UPPER('UK_JOBS')
AND fifs.id_flex_num = fifs1.id_flex_num
AND UPPER(fifs1.segment_name) = UPPER('Job Name')
AND fifs1.flex_value_set_id = ffvv.flex_value_set_id;

The following Query is for getting all the Flex Values for a given Flex
Value Set:

SELECT
ffvv.flex_value
FROM  
fnd_flex_values_vl ffvv,
fnd_flex_value_sets ffvs  
WHERE
ffvv.flex_value_set_id    = ffvs.flex_value_set_id
AND UPPER(ffvs.flex_value_set_name) = UPPER('UK_JOB_NAME');

Query for getting Applicant Details.

SELECT
papf.person_id,
papf.applicant_number,
papf.full_name,
papf.date_of_birth,
papf.nationality,
papf.marital_status,
paaf.organization_id,
paaf.location_id,
paaf.supervisor_id,
pe.type,
pe.location_id,
pe.date_start
FROM
per_all_people_f papf,
per_all_assignments_f paaf,
per_events pe
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_applicant_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND papf.person_id = paaf.person_id
AND paaf.primary_flag = 'Y'
AND pe.assignment_id = paaf.assignment_id;

Query to lists the Grades, their present and next Grade Step, and their corresponding values.

SELECT   DISTINCT
         papf.employee_number    staffno,
         pgrf.value              basic,
         pgrf1.value             basic1,
         pg.name                 grade,
         psp.spinal_point        grade_step,
         psp1.spinal_point       grade_step1
FROM  
per_all_people_f                papf,
per_all_assignments_f paaf,
         per_grades                      pg,
         per_spinal_point_placements_f   psppf,
         per_spinal_point_steps_f        pspsf,
         per_spinal_point_steps_f        pspsf1,
         per_spinal_points               psp,
         per_spinal_points               psp1,
         per_grade_spines_f              pgsf,
         pay_grade_rules_f               pgrf,
         pay_grade_rules_f               pgrf1
WHERE
papf.business_group_id =  P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
         AND TRUNC(SYSDATE)  BETWEEN papf.effective_start_date AND papf.effective_end_date
         AND papf.person_id = paaf.person_id
         AND TRUNC(SYSDATE)  BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
         AND paaf.grade_id = pg.grade_id
         AND paaf.assignment_id = psppf.assignment_id
         AND TRUNC(SYSDATE)  BETWEEN psppf.effective_start_date AND psppf.effective_end_date
         AND psppf.step_id = pspsf.step_id
         AND pspsf.spinal_point_id = psp.spinal_point_id
         AND pspsf1.spinal_point_id = psp1.spinal_point_id
         AND TRUNC(SYSDATE)   BETWEEN pspsf.effective_start_date AND pspsf.effective_end_date
         AND psp.parent_spine_id = pgsf.parent_spine_id
         AND psp1.parent_spine_id = pgsf.parent_spine_id
         AND TRUNC(SYSDATE)  BETWEEN pgsf.effective_start_date AND pgsf.effective_end_date
         AND psp.spinal_point_id = pgrf.grade_or_spinal_point_id
         AND psp1.spinal_point_id = pgrf1.grade_or_spinal_point_id
         AND (psppf.step_id !=  NVL(paaf.special_ceiling_step_id,pgsf.ceiling_step_id)
         AND psppf.auto_increment_flag = 'Y'              
AND (psp1.sequence = (SELECT
MIN(psp2.sequence)
 FROM
per_spinal_points           psp2,
per_spinal_point_steps_f    pspsf2
 WHERE
pspsf.grade_spine_id    = pspsf.grade_spine_id
AND   pspsf2.spinal_point_id  = psp2.spinal_point_id
AND   TRUNC(SYSDATE)  BETWEEN pspsf2.effective_start_date AND pspsf2.effective_end_date
AND   psp2.sequence > psp.sequence))
         OR  psppf.auto_increment_flag = 'N'
         AND psp1.sequence = psp.sequence
         OR  psppf.step_id = NVL(paaf.special_ceiling_step_id,pgsf.ceiling_step_id)
         AND psp1.sequence = psp.sequence)
         AND TRUNC(SYSDATE) BETWEEN pgrf1.effective_start_date AND pgrf1.effective_end_date;

Query for getting Pay Scale Details for the Employees.

SELECT
papf.employee_number,
pg.name,
hrfg.grade_step,
pgrf.value
FROM
per_all_people_f papf,
per_all_assignments_f paaf,
pay_grade_rules_f pgrf,
        per_spinal_point_steps_f pgspsf,
        per_spinal_points psp,
        hrfg_grade_steps hrfg,
        per_grade_spines_f pgsf,
        per_grades pg
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
AND pg.name = paaf.grade_id
        AND hrfg.grade_id = pg.grade_id
        AND hrfg.grade_step_id = pgspsf.step_id
        AND TRUNC(SYSDATE) BETWEEN pgspsf.effective_start_date AND pgspsf.effective_end_date
        AND psp.spinal_point_id = pgspsf.grade_spine_id
        AND pgsf.grade_spine_id = pgspsf.grade_spine_id
        AND hrfg.grade_id = pgsf.grade_id
        AND TRUNC(SYSDATE) BETWEEN pgsf.effective_start_date AND pgsf.effective_end_date
        AND pgspsf.spinal_point_id = pgrf.grade_or_spinal_point_id
        AND TRUNC(SYSDATE) BETWEEN pgrf.effective_start_date AND pgrf.effective_end_date;

Query for getting Address Details for the Employees.

SELECT
papf.employee_number,
pa.style,
pa.address_line1,
pa.address_line2,
pa.address_line3,
pa.addresss_type,
pa.country,
pa.postal_code,
pa.telephone_number_1,
pa.town_or_city
FROM
per_all_people_f papf,
per_addresses pa
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = pa.person_id;

Query for getting Phone Details for the Employees.

SELECT
papf.employee_number,
pp.phone_type,
pp.phone_number
FROM
per_all_people_f papf,
per_phones pp
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = pp.parent_id;