Job Query

SELECT   effective_start_date
        ,effective_end_date
        ,job_code
        ,job_name
        ,job_family_name
        ,arabic_name
        ,deployment_indicator
        ,set_name
        ,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 (pj.effective_start_date
                         ,'DD-Mon-YYYY'
                         ,'NLS_DATE_LANGUAGE=AMERICAN')
                    effective_start_date
                ,TO_CHAR (pj.effective_end_date
                         ,'DD-Mon-YYYY'
                         ,'NLS_DATE_LANGUAGE=AMERICAN')
                    effective_end_date
                ,pj.job_code job_code
                ,pjj.name job_name
                ,RANK ()
                    OVER (PARTITION BY pj.job_id
                          ORDER BY
                             LPAD (pg.grade_code
                                  ,10
                                  ,'0'))
                    seq_no
                ,pg.grade_code grade_code
                ,CASE
                    WHEN RANK ()
                            OVER (PARTITION BY pj.job_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 pj.job_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 pj.job_id
                                  ORDER BY
                                     LPAD (pg.grade_code
                                          ,10
                                          ,'0')) = 3
                    THEN
                       pg.grade_code
                    ELSE
                       ''
                 END
                    valid_grade_code3
                ,pjf.job_family_name job_family_name
                ,pj.attribute2 arabic_name
                ,pj.attribute1 deployment_indicator
                , (SELECT set_name
                   FROM   fnd_setid_sets_vl
                   WHERE  set_id = pj.set_id)
                    set_name
          FROM   per_job_family_f_tl pjf
                ,per_grades pg
                ,per_valid_grades_f pvg
                ,per_jobs_f pj
                ,per_jobs pjj
          WHERE  pjf.job_family_id = pj.job_family_id
                 AND pjf.language = 'US'
                 AND TRUNC (SYSDATE) BETWEEN pjf.effective_start_date
                                         AND pjf.effective_end_date
                 AND pg.grade_id = pvg.grade_id
                 AND TRUNC (SYSDATE) BETWEEN pvg.effective_start_date
                                         AND pvg.effective_end_date
                 AND pvg.job_id = pj.job_id
                 AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date
                                         AND pj.effective_end_date
                 AND pj.job_id = pjj.job_id)
GROUP BY effective_start_date
        ,effective_end_date
        ,job_code
        ,job_name
        ,job_family_name
        ,arabic_name
        ,deployment_indicator
        ,set_name
ORDER BY job_name