EMPLOYEE OBJECTIVE OR KPI DETAIL QUERY WITH LINE SCORE

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 = pap.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 = pap.APPRAISER_PERSON_ID
                    AND papf1.current_employee_flag = 'Y'
                    AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE
                                    AND PAPF1.EFFECTIVE_END_DATE)
               Main_APPRAISER_NAME,
            poe.name
               Objective_name,
            poe.WEIGHTING_PERCENT,
            PRL.STEP_VALUE || '-' || PRL.NAME                        RATING,
            TO_NUMBER (
               HR_APPRAISALS_UTIL_SS.get_objective_score (POE.OBJECTIVE_ID,
                                                          POE.APPRAISAL_ID))
               OBJECTIVE_Line_SCORE,
            poe.START_DATE
               Objective_START_DATE,
            PAP.APPRAISAL_ID,
            PAp.appraisee_person_id,
            papF.person_id,
            pap.APPRAISER_PERSON_ID,
            pap.APPRAISAL_DATE,
            pap.APPRAISAL_PERIOD_START_DATE,
            pap.APPRAISAL_PERIOD_END_DATE,
            pap.APPRAISAL_SYSTEM_STATUS,
            pap.PLAN_ID,
            poe.OBJECTIVE_ID,
            PPR.PERFORMANCE_RATING_ID,
            PPR.PERFORMANCE_LEVEL_ID,
            PRL.RATING_LEVEL_ID
       FROM PER_APPRAISALS                 PAP,
            PER_OBJECTIVES                 POE,
            per_all_people_f               papf,
            PER_PERFORMANCE_RATINGS        ppr,
            PER_RATING_LEVELS              PRL,
            PER_ALL_ASSIGNMENTS_F          PAAF,
            hr_all_organization_units dept
      WHERE                          --POE.OBJECTIVE_ID = PPR.OBJECTIVE_ID AND
           PAP  .APPRAISAL_ID = POE.APPRAISAL_ID
            AND PAP.APPRAISAL_ID = PPR.APPRAISAL_ID
            AND PAp.appraisee_person_id = papF.person_id
            AND PPR.PERFORMANCE_LEVEL_ID = PRL.RATING_LEVEL_ID
            AND POE.OBJECTIVE_ID = PPR.OBJECTIVE_ID
            AND PAPF.PERSON_ID = PAAF.PERSON_ID
            AND paaf.organization_id =DEPT.organization_id
            AND PAAF.PRIMARY_FLAG = 'Y'
            AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE
                            AND PAAF.EFFECTIVE_END_DATE
            -- AND TO_CHAR (pap.appraisal_date, 'YYYY') = '2017'
            --AND pap.APPRAISAL_ID = 35103
            AND papf.current_employee_flag = 'Y'
            AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
                            AND PAPF.EFFECTIVE_END_DATE
   -- AND papf.employee_number = '1352'
   ORDER BY papf.employee_number, pap.APPRAISAL_DATE;