Position Query

SELECT    esd
         ,position_code
         ,department
         ,business_unit
         ,position_name
         ,full_part_time
         ,permanent_temporary_flag
         ,position_type
         ,fte
         ,head_count
         ,probation_period
         ,probation_period_unit_cd
         ,hiring_status
         ,job_code
         ,health_card
         ,MIN (valid_grade_code1) valid_grade_code1
         ,MIN (valid_grade_code2) valid_grade_code2
         ,MIN (valid_grade_code3) valid_grade_code3
FROM      (SELECT  TO_CHAR (hap.effective_start_date
                           ,'dd-mm-yyyy')
                      esd
                  ,hap.position_code position_code
                  ,haou.name department
                  ,haoub.name business_unit
                  ,hapt.name position_name
                  ,hap.full_part_time
                  ,hap.permanent_temporary_flag
                  ,hap.position_type position_type
                  ,hap.fte fte
                  ,hap.max_persons head_count
                  ,hap.probation_period probation_period
                  ,hap.probation_period_unit_cd probation_period_unit_cd
                  ,hap.hiring_status hiring_status
                  ,pj.job_code job_code
                  ,hap.attribute1 health_card
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 1
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code1
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 2
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code2
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 3
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code3
           FROM    hr_all_positions_f hap
                  ,hr_all_positions_f_tl hapt
                  ,hr_all_organization_units haoub
                  ,hr_all_organization_units haou
                  ,per_jobs_f pj
                  ,per_valid_grades_f pvg
                  ,per_grades_f pg
                  ,per_grades_f_tl pgft
           WHERE       1 = 1
                   AND TRUNC (SYSDATE) BETWEEN hap.effective_start_date AND hap.effective_end_date
                   AND hapt.position_id = hap.position_id
                   AND hapt.language = 'US'
                   AND TRUNC (SYSDATE) BETWEEN hapt.effective_start_date AND hapt.effective_end_date
                   AND haoub.organization_id = hap.business_unit_id
                   AND haou.organization_id = hap.organization_id
                   AND pj.job_id = hap.job_id
                   AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date AND pj.effective_end_date
                   AND pvg.position_id = hap.position_id
                   AND TRUNC (SYSDATE) BETWEEN pvg.effective_start_date AND pvg.effective_end_date
                   AND pg.grade_id = pvg.grade_id
                   AND TRUNC (SYSDATE) BETWEEN pg.effective_start_date AND pg.effective_end_date
                   AND pgft.grade_id = pg.grade_id
                   AND pgft.language = 'US'
                   AND TRUNC (SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date)
GROUP BY  esd
         ,position_code
         ,department
         ,business_unit
         ,position_name
         ,full_part_time
         ,permanent_temporary_flag
         ,position_type
         ,fte
         ,head_count
         ,probation_period
         ,probation_period_unit_cd
         ,hiring_status
         ,job_code
         ,health_card