Monday, February 25, 2013

Query to display Compentencies related to Employee

Query to display Compentencies related to Employee in Performance Appraisals

SELECT pap.person_id, pap.full_name appraiser, pc.NAME compentency,
       NVL2 (prl1.NAME,
             prl1.step_value || '-' || prl1.NAME,
            ) AS emp_prof_level,
       pce.comments, prl1.step_value
  FROM per_competence_elements pce,
       per_rating_levels_vl prl1,
       per_rating_levels_vl prl2,
       per_rating_levels_vl prl3,
       per_all_people_f pap,
       per_competences pc
 WHERE pce.proficiency_level_id = prl1.rating_level_id(+)
   AND pce.rating_level_id = prl2.rating_level_id(+)
   AND pce.weighting_level_id = prl3.rating_level_id(+)
   AND object_id = pap.person_id
   AND pc.competence_id = pce.competence_id
   AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date AND effective_end_date
   AND pce.object_name = 'ASSESSOR_ID'
   AND pce.object_id IN (
          SELECT perp.person_id
            FROM per_participants perp, per_assessments pass
           WHERE perp.participation_in_id = pass.appraisal_id
             AND perp.participation_in_table = 'PER_APPRAISALS'
             AND perp.participation_in_column = 'APPRAISAL_ID'
             AND pass.assessment_id = pce.assessment_id
             AND pass.appraisal_id =
                               ---(Appraisal id receieved from previous query)

1 comment:

lee woo said...

Sincerity and competence is a strong combination. In politics, it is everything. See the link below for more info.