EMPLOYEE COMPETENCY DETAILS FOR THE APPRAISALS

SELECT PAPF.EMPLOYEE_NUMBER,
            PAPF.GLOBAL_NAME                                         STAFF_NAME,
            papf.EMAIL_ADDRESS,
            TO_CHAR (papf.date_of_birth, 'DD-MON-YYYY')              date_of_birth,
            TO_CHAR (papf.start_date, 'DD-MON-YYYY')                 HIRE_DATE,
            hr_general.decode_lookup ('SEX', papF.SEX)               GENER,
            HR_PERSON_TYPE_USAGE_INFO.get_user_person_type (TO_DATE (SYSDATE),
                                                            PAPF.PERSON_ID)
               Employee_type,
            hr_general.decode_lookup ('MAR_STATUS', papF.marital_status)
               MARITICAL_STATUS,
            hr_general.decode_lookup ('NATIONALITY', papF.NATIONALITY)
               NATIONALITY,
            DEPT.name                     DEPARTMENT,
            HR_GENERAL.DECODE_GRADE (PAAF.GRADE_ID)                  GRADE,
            HR_GENERAL.DECODE_JOB (PAAF.JOB_ID)                      JOB,
            HR_GENERAL.DECODE_POSITION_CURRENT_NAME (PAAF.POSITION_ID) POSITION,
            HR_GENERAL.DECODE_PEOPLE_GROUP (PAAF.PEOPLE_GROUP_ID)
               PEOPLE_GROUP,
            HR_GENERAL.DECODE_LOCATION (PAAF.location_ID)            location,
            (SELECT PAPF1.EMPLOYEE_NUMBER
               FROM per_all_people_f papf1
              WHERE     PAPF1.PERSON_ID = PAR.APPRAISER_PERSON_ID
                    AND papf1.current_employee_flag = 'Y'
                    AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE
                                    AND PAPF1.EFFECTIVE_END_DATE)
               Main_APPRAISER_NUMBER,
            (SELECT PAPF1.GLOBAL_NAME
               FROM per_all_people_f papf1
              WHERE     PAPF1.PERSON_ID = paR.APPRAISER_PERSON_ID
                    AND papf1.current_employee_flag = 'Y'
                    AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE
                                    AND PAPF1.EFFECTIVE_END_DATE)
               Main_APPRAISER_NAME,
            pcv.name
               COPETENCE_NAME,
            prl.step_value || '-' || prl.name
               competence_rating,
            PAP.TYPE,
            PAR.APPRAISAL_ID,
            par.appraisee_person_id,
            par.appraisER_person_id,
            PA.ASSESSMENT_ID,
            PA.ASSESSMENT_TYPE_ID,
            PAR.APPRAISAL_DATE,
            PAR.APPRAISAL_PERIOD_START_DATE,
            PAR.APPRAISAL_PERIOD_END_DATE,
            PCE.COMPETENCE_ELEMENT_ID,
            pce.RATING_LEVEL_ID,
            pce.PROFICIENCY_LEVEL_ID,
            PAR.PLAN_ID,
            pce.COMPETENCE_ID,
            PAAF.ASSIGNMENT_ID
       FROM per_all_people_f               papf,
            per_appraisals                 par,
            per_assessments                pa,
            per_assessment_types           pap,
            per_competence_elements        PCE,
            PER_COMPETENCES_VL             pcv,
            PER_RATING_LEVELS              prl,
            per_all_assignments_f          paaf,
            hr_all_organization_units  dept
      WHERE     PAR.appraisee_person_id = papF.person_id
            AND par.appraisal_id = pa.appraisal_id
            AND pa.ASSESSMENT_TYPE_ID = pap.ASSESSMENT_TYPE_ID
            AND PA.ASSESSMENT_ID = PCE.ASSESSMENT_ID
            AND pce.COMPETENCE_ID = pcv.COMPETENCE_ID
            AND pce.rating_level_id = prl.rating_level_id
            AND papf.person_id = paaf.person_id
           AND paaf.organization_id =DEPT.organization_id
            AND paaf.primary_flag = 'Y'
            AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
            AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
                            AND PAPF.EFFECTIVE_END_DATE
            AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE
                            AND PAAF.EFFECTIVE_END_DATE
            --AND TO_CHAR (par.appraisal_date, 'YYYY') = '2017'
            AND pap.TYPE = 'COMPETENCE'
   --AND PAPF.EMPLOYEE_NUMBER = '1352'
   ORDER BY PAPF.EMPLOYEE_NUMBER;