EBS: Employee Performance Rank

To get the Employee Performance Rank.


/* Formatted on 01/12/2015 11:52:31 (QP5 v5.126.903.23003) */
SELECT   papf.employee_number,
         papf.full_name,
         DECODE (papf.sex,
                 'M',
                 'Male',
                 'F',
                 'Female')
            Gender,
         (SELECT   MAX (ppos.DATE_START)
            FROM   per_periods_of_service ppos
           WHERE   ppos.person_id = paaf.person_id)
            start_date,
         (SELECT   haou.name
            FROM   hr_all_organization_units haou
           WHERE   haou.organization_id = paaf.organization_id)
            Department,
         (SELECT   ppd.segment3 Ref_Ind
            FROM   apps.per_positions pp, apps.per_position_definitions ppd
           WHERE   ppd.position_definition_id = pp.position_definition_id
                   AND pp.position_id = paaf.position_id)
            Ref_Ind,
         (SELECT   ppg.group_name
            FROM   pay_people_groups ppg
           WHERE   ppg.people_group_id = paaf.people_group_id)
            group_name,
         (hr_general.DECODE_GRADE (paaf.grade_id)) SG,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2011'), 'YYYY'))
            Ranking_2011,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2012'), 'YYYY'))
            Ranking_2012,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2013'), 'YYYY'))
            Ranking_2013
  FROM   apps.per_all_people_f papf, apps.per_all_assignments_f paaf
 WHERE       papf.person_id = paaf.person_id
         AND paaf.primary_flag = 'Y'
         AND papf.current_employee_flag = 'Y'
         AND papf.business_group_id = :business_group_id
         AND SYSDATE BETWEEN paaf.effective_start_date
                         AND  paaf.effective_end_date
         AND SYSDATE BETWEEN papf.effective_start_date
                         AND  papf.effective_end_date;



Enjoy and Stay Well !! :)