EMPLOYEE APPRAISAL SUGGEST OVERALL RATING

SELECT DISTINCT
          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,
          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,
         dept.name
             DEPARTMENT,
          TO_CHAR (PAR.APPRAISAL_DATE, 'DD-MON-YYYY')
             APPRAISAL_DATE,
          papf1.employee_number
             main_APPRAISER_number,
          papf1.global_name
             main_APPRAISER_name,
          PAR.COMMENTS
             MAIN_Appraiser_Comments,
          PAR.APPRAISEE_COMMENTS,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    5)
             key_strength,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    6)
             key_improvement,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    7)
             Training_Recommendation,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    8)
             Training_type,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    9)
             Questionnaire_Remarks,
          (SELECT TOTAL_SCORE
             FROM PER_ASSESSMENTS
            WHERE     ASSESSMENT_TYPE_ID = 1
                  AND PERSON_ID = PApf.PERSON_ID
                  AND APPRAISAL_ID = par.APPRAISAL_ID)
             Objective_score,
          (SELECT TOTAL_SCORE
             FROM PER_ASSESSMENTS
            WHERE     ASSESSMENT_TYPE_ID = 2
                  AND PERSON_ID = PApf.PERSON_ID
                  AND APPRAISAL_ID = par.APPRAISAL_ID)
             Competency_score,
              (SELECT TOTAL_SCORE
                 FROM PER_ASSESSMENTS
                WHERE     ASSESSMENT_TYPE_ID = 1
                      AND PERSON_ID = PApf.PERSON_ID
                      AND APPRAISAL_ID = par.APPRAISAL_ID)
            * 0.6
          +   (SELECT TOTAL_SCORE
                 FROM PER_ASSESSMENTS
                WHERE     ASSESSMENT_TYPE_ID = 2
                      AND PERSON_ID = PApf.PERSON_ID
                      AND APPRAISAL_ID = par.APPRAISAL_ID)
            * 0.4
             Suggest_overall_rating,
          (SELECT name
             FROM PER_RATING_LEVELS
            WHERE RATING_LEVEL_ID = par.OVERALL_PERFORMANCE_LEVEL_ID)
             Over_all_rating,
          (SELECT A.RATING_MEANING
             FROM PER_PERFORMANCE_REVIEWS_V a
            WHERE     a.person_id = papf.person_id
                  AND a.review_date BETWEEN TO_DATE (
                                                  '01-OCT-'
                                               || TO_CHAR (
                                                     par.APPRAISAL_PERIOD_END_DATE,
                                                     'YYYY'))
                                        AND TO_DATE (
                                                  '31-JAN-'
                                               || TO_CHAR (
                                                     ADD_MONTHS (
                                                        par.APPRAISAL_PERIOD_END_DATE,
                                                        +1),
                                                     'YYYY'))
                  AND ROWNUM = 1)
             PMS_APPROVED_rating,
          hl.meaning                                                 status,
          papf.person_id,
          PAAF.ASSIGNMENT_ID,
          PAR.APPRAISAL_ID,
          par.APPRAISEE_PERSON_ID,
          par.APPRAISER_PERSON_ID,
          par.APPRAISAL_PERIOD_START_DATE,
          par.APPRAISAL_PERIOD_END_DATE,
          ppmg.plan_name
     FROM per_appraisals                   par,
          hr_lookups                       hl,
          per_all_people_f                 papf,
          per_all_assignments_f            paaf,
          per_all_people_f                 papf1,
          PER_PERF_MGMT_PLANS              ppmg,
          hr_all_organization_units dept
    WHERE     pAr.APPRAISAL_SYSTEM_STATUS = hl.lookup_code
          AND papf.person_id = paaf.person_id
          AND par.APPRAISEE_PERSON_ID = papf.person_id
          AND par.APPRAISER_PERSON_ID = papf1.person_id
          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 SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE
                          AND PAPF1.EFFECTIVE_END_DATE
          AND PAPF.BUSINESS_GROUP_ID = 81
          AND paaf.primary_flag = 'Y'
          AND papf.CURRENT_EMPLOYEE_FLAG = 'Y'
          AND PAPF1.BUSINESS_GROUP_ID = 81
          AND papf1.CURRENT_EMPLOYEE_FLAG = 'Y'
          AND hl.lookup_type = 'APPRAISAL_SYSTEM_STATUS'
          --AND hl.meaning NOT IN ('Deleted')
          --AND hl.meaning in ('Planned')
          AND par.PLAN_ID = ppmg.PLAN_ID
         AND paaf.organization_id =DEPT.organization_id
   --AND par.PLAN_ID= 121
   --AND pa.ASSESSMENT_TYPE_ID = 1
   --and papf.employee_number='1352'
   UNION
   SELECT DISTINCT
          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,
          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,
          DEPT.name
             DEPARTMENT,
          TO_CHAR (PAR.APPRAISAL_DATE, 'DD-MON-YYYY')
             APPRAISAL_DATE,
          papf1.employee_number
             main_APPRAISER_number,
          papf1.global_name
             main_APPRAISER_name,
          PAR.COMMENTS
             MAIN_Appraiser_Comments,
          PAR.APPRAISEE_COMMENTS,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    5)
             key_strength,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    6)
             key_improvement,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    7)
             Training_Recommendation,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    8)
             Training_type,
          apps.QUUESITION_KEY_TRAI (par.APPRAISAL_ID,
                                    TO_CHAR (par.appraisal_date, 'YYYY'),
                                    9)
             Questionnaire_Remarks,
          (SELECT TOTAL_SCORE
             FROM PER_ASSESSMENTS
            WHERE     ASSESSMENT_TYPE_ID = 1
                  AND TOTAL_SCORE IS NOT NULL
                  AND PERSON_ID = PApf.PERSON_ID
                  AND APPRAISAL_ID = par.APPRAISAL_ID)
             Objective_score,
          (SELECT TOTAL_SCORE
             FROM PER_ASSESSMENTS
            WHERE     ASSESSMENT_TYPE_ID = 2
                  AND TOTAL_SCORE IS NOT NULL
                  AND PERSON_ID = PApf.PERSON_ID
                  AND APPRAISAL_ID = par.APPRAISAL_ID)
             Competency_score,
              (SELECT TOTAL_SCORE
                 FROM PER_ASSESSMENTS
                WHERE     ASSESSMENT_TYPE_ID = 1
                      AND TOTAL_SCORE IS NOT NULL
                      AND PERSON_ID = PApf.PERSON_ID
                      AND APPRAISAL_ID = par.APPRAISAL_ID)
            * 0.6
          +   (SELECT TOTAL_SCORE
                 FROM PER_ASSESSMENTS
                WHERE     ASSESSMENT_TYPE_ID = 2
                      AND TOTAL_SCORE IS NOT NULL
                      AND PERSON_ID = PApf.PERSON_ID
                      AND APPRAISAL_ID = par.APPRAISAL_ID)
            * 0.4
             Suggest_overall_raring,
          (SELECT name
             FROM PER_RATING_LEVELS
            WHERE RATING_LEVEL_ID = par.OVERALL_PERFORMANCE_LEVEL_ID)
             Over_all_rating,
          (SELECT A.RATING_MEANING
             FROM PER_PERFORMANCE_REVIEWS_V a
            WHERE     a.person_id = papf.person_id
                  AND a.review_date BETWEEN TO_DATE (
                                                  '01-OCT-'
                                               || TO_CHAR (
                                                     par.APPRAISAL_PERIOD_END_DATE,
                                                     'YYYY'))
                                        AND TO_DATE (
                                                  '31-JAN-'
                                               || TO_CHAR (
                                                     ADD_MONTHS (
                                                        par.APPRAISAL_PERIOD_END_DATE,
                                                        +1),
                                                     'YYYY'))
                  AND ROWNUM = 1)
             PMS_APPROVED_rating,
          hl.meaning                                                 status,
          papf.person_id,
          PAAF.ASSIGNMENT_ID,
          PAR.APPRAISAL_ID,
          par.APPRAISEE_PERSON_ID,
          par.APPRAISER_PERSON_ID,
          par.APPRAISAL_PERIOD_START_DATE,
          par.APPRAISAL_PERIOD_END_DATE,
          NULL
             PLAN_NAME
     FROM per_appraisals                   par,
          hr_lookups                       hl,
          per_all_people_f                 papf,
          per_all_assignments_f            paaf,
          per_all_people_f                 papf1,
           hr_all_organization_units dept
    WHERE     pAr.APPRAISAL_SYSTEM_STATUS = hl.lookup_code
          AND papf.person_id = paaf.person_id
          AND par.APPRAISEE_PERSON_ID = papf.person_id
          AND par.APPRAISER_PERSON_ID = papf1.person_id
          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 SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE
                          AND PAPF1.EFFECTIVE_END_DATE
          AND PAPF.BUSINESS_GROUP_ID = 81
          AND paaf.primary_flag = 'Y'
          AND papf.CURRENT_EMPLOYEE_FLAG = 'Y'
          AND PAPF1.BUSINESS_GROUP_ID = 81
          AND papf1.CURRENT_EMPLOYEE_FLAG = 'Y'
          AND hl.lookup_type = 'APPRAISAL_SYSTEM_STATUS'
          AND hl.meaning NOT IN ('Deleted', 'Planned')
          --AND hl.meaning in ('Planned')
          -- and to_char(par.appraisal_date,'YYYY')='2017'
          AND par.PLAN_ID IS NULL
          AND paaf.organization_id =DEPT.organization_id
   --AND pa.ASSESSMENT_TYPE_ID = 1
   --and papf.employee_number='1352'
   ORDER BY 1;