Training profile history query in oracle learning management

/* Formatted on 12/4/2018 9:29:10 AM (QP5 v5.294) */
  SELECT DISTINCT
         employee_number                       "Staff Number",
         LTRIM (
               hr_general.decode_lookup ('TITLE', papf.title)
            || ' '
            || papf.first_name
            || ' '
            || papf.middle_names
            || ' '
            || papf.last_name)
            "Staff Name",
         HR_GENERAL.DECODE_GRADE (PAAF.GRADE_ID)"Grade"-- ,hr_general.DECODE_ORGANIZATION(paaf.organization_id) Department
         ,
         haou.attribute1                       "Department",
         oe.title                              "Course Title",
         oe.evt_information1                   "Vendor",
         oe.course_start_date                  "Course Start Date",
         oe.course_end_date                    "Course End Date",
         oe.evt_information2                   "Venue",
         oe.evt_information3                   "Institute University",
         oe.evt_information4                   "Training type",
         oe.evt_information5                   "remarks",
         odb.event_id,
         ODB.BOOKING_ID
    FROM ota_delegate_bookings   odb,
         ota_events              oe,
         ota_booking_status_types obst,
         per_all_people_f        papf,
         per_all_assignments_f   paaf,
         hr_all_organization_units haou
   WHERE     1 = 1
         AND paaf.organization_id = haou.organization_id
         AND odb.event_id = oe.event_id
         AND odb.booking_status_type_id = obst.booking_status_type_id
         AND odb.delegate_person_id = papf.person_id
         AND papf.person_id = paaf.person_id
         AND TO_DATE (oe.course_end_date) BETWEEN papf.effective_start_date
                                              AND papf.effective_end_date
         AND TO_DATE (oe.course_end_date) BETWEEN paaf.effective_start_date
                                              AND paaf.effective_end_date
         AND paaf.primary_flag = 'Y'
         --and papf.person_id =nvl( :p_employee_number,papf.person_id)
        and papf.employee_number='0987'
         --AND TO_CHAR(oe.course_start_date,'YYYY')=to_char(to_date(:P_DATE),'YYYY')
         /*and to_date(oe.course_start_date)>=to_date(:P_STARTDATE)
         and to_date(oe.course_end_date)<=to_date(:P_DATE) */
         --AND haou.attribute1 = NVL (:P_DEPARMENT, haou.attribute1)
ORDER BY 1, oe.course_start_date

Leave History details in HRMS

select paa.person_id,
       paat.name LeaveType,
       paat.name LeaveType,
       paa.absence_days Leave_Days,
       paa.date_start,
       paa.date_end,
       hr_general.decode_lookup('ABSENCE_REASON',paar.name) Absence_Reason,
       paa.attribute1 Exam_Status,
       paa.attribute2 Study_Status,
       hr_general.decode_lookup('YES_NO',paa.attribute3) Leave_Allowance,
       hr_general.decode_lookup('YES_NO',paa.attribute4) Leave_Salary,
NVL(abs( (SELECT DISTINCT SUM(TO_NUMBER(peev.screen_entry_value)) VALUE                       
                          FROM pay_element_entries_f peef,
                               pay_element_entry_values_f peev,
                               pay_element_links_f LINK,
                               pay_element_types_f TYPE,
                               pay_input_values_f input
                         WHERE TYPE.element_name = 'Annual Leave Adjustment'
                           AND input.NAME = 'Plan Days'
                           AND TYPE.business_group_id = 81
                           AND peef.assignment_id =PAAF.assignment_id
                           AND peev.element_entry_id IN (SELECT DISTINCT a.ELEMENT_ENTRY_ID                   
                          FROM pay_element_entries_f  a,
                               pay_element_entry_values_f  b,
                               pay_element_links_f  c,
                               pay_element_types_f  d,
                               pay_input_values_f e
                         WHERE d.element_name = 'Annual Leave Adjustment'
                           AND e.NAME ='Adjusted Leave Period'
                           AND b.screen_entry_value=PAA.ABSENCE_ATTENDANCE_ID
                           AND d.business_group_id = 81                         
                           AND a.assignment_id =PAAF.assignment_id
                           AND d.business_group_id = c.business_group_id
                           AND d.business_group_id =
                                                       e.business_group_id
                           AND a.element_entry_id = b.element_entry_id
                           AND a.element_link_id = c.element_link_id
                           AND c.element_type_id = d.element_type_id
                           AND d.element_type_id = e.element_type_id
                           AND b.input_value_id = e.input_value_id
                           AND paa.date_start
                                  BETWEEN d.effective_start_date
                                      AND d.effective_end_date
                           AND paa.date_start
                                  BETWEEN c.effective_start_date
                                      AND c.effective_end_date
                           AND paa.date_start
                                  BETWEEN a.effective_start_date
                                      AND a.effective_end_date
                           AND paa.date_start
                                  BETWEEN b.effective_start_date
                                      AND b.effective_end_date
                           AND paa.date_start
                                  BETWEEN e.effective_start_date
                                      AND e.effective_end_date)
                           AND TYPE.business_group_id = LINK.business_group_id
                           AND TYPE.business_group_id =
                                                       input.business_group_id
                           AND peef.element_entry_id = peev.element_entry_id
                           AND peef.element_link_id = LINK.element_link_id
                           AND LINK.element_type_id = TYPE.element_type_id
                           AND TYPE.element_type_id = input.element_type_id
                           AND peev.input_value_id = input.input_value_id
                           AND paa.date_start
                                  BETWEEN TYPE.effective_start_date
                                      AND TYPE.effective_end_date
                           AND paa.date_start
                                  BETWEEN LINK.effective_start_date
                                      AND LINK.effective_end_date
                           AND paa.date_start
                                  BETWEEN peef.effective_start_date
                                      AND peef.effective_end_date
                           AND paa.date_start
                                  BETWEEN peev.effective_start_date
                                      AND peev.effective_end_date
                           AND paa.date_start
                                  BETWEEN input.effective_start_date
                                      AND input.effective_end_date)),0) ADJUSTMENT_DAYS,
Case When paat.name='Annual Leave' Then paa.absence_days-nvl(abs( (SELECT DISTINCT sum(to_number(peev.screen_entry_value)) VALUE                       
                          FROM pay_element_entries_f peef,
                               pay_element_entry_values_f peev,
                               pay_element_links_f LINK,
                               pay_element_types_f TYPE,
                               pay_input_values_f input
                         WHERE TYPE.element_name = 'Annual Leave Adjustment'
                           AND input.NAME = 'Plan Days'
                           AND TYPE.business_group_id = 81
                           AND peef.assignment_id =PAAF.assignment_id
                           AND TYPE.business_group_id = LINK.business_group_id
                           AND TYPE.business_group_id =
                                                       input.business_group_id
                           AND peef.element_entry_id = peev.element_entry_id
                           AND peef.element_link_id = LINK.element_link_id
                           AND LINK.element_type_id = TYPE.element_type_id
                           AND TYPE.element_type_id = input.element_type_id
                           AND peev.input_value_id = input.input_value_id
                           AND peev.element_entry_id IN (SELECT DISTINCT a.ELEMENT_ENTRY_ID                   
                          FROM pay_element_entries_f  a,
                               pay_element_entry_values_f  b,
                               pay_element_links_f  c,
                               pay_element_types_f  d,
                               pay_input_values_f e
                         WHERE d.element_name = 'Annual Leave Adjustment'
                           AND e.NAME ='Adjusted Leave Period'
                           AND b.screen_entry_value=PAA.ABSENCE_ATTENDANCE_ID
                           AND d.business_group_id = 81                         
                           AND a.assignment_id =PAAF.assignment_id
                           AND d.business_group_id = c.business_group_id
                           AND d.business_group_id =
                                                       e.business_group_id
                           AND a.element_entry_id = b.element_entry_id
                           AND a.element_link_id = c.element_link_id
                           AND c.element_type_id = d.element_type_id
                           AND d.element_type_id = e.element_type_id
                           AND b.input_value_id = e.input_value_id
                           AND paa.date_start
                                  BETWEEN d.effective_start_date
                                      AND d.effective_end_date
                           AND paa.date_start
                                  BETWEEN c.effective_start_date
                                      AND c.effective_end_date
                           AND paa.date_start
                                  BETWEEN a.effective_start_date
                                      AND a.effective_end_date
                           AND paa.date_start
                                  BETWEEN b.effective_start_date
                                      AND b.effective_end_date
                           AND paa.date_start
                                  BETWEEN e.effective_start_date
                                      AND e.effective_end_date)
                           AND paa.date_start
                                  BETWEEN TYPE.effective_start_date
                                      AND TYPE.effective_end_date
                           AND paa.date_start
                                  BETWEEN LINK.effective_start_date
                                      AND LINK.effective_end_date
                           AND paa.date_start
                                  BETWEEN peef.effective_start_date
                                      AND peef.effective_end_date
                           AND paa.date_start
                                  BETWEEN peev.effective_start_date
                                      AND peev.effective_end_date
                           AND paa.date_start
                                  BETWEEN input.effective_start_date
                                      AND input.effective_end_date)),0) Else 0 End TOTAL_ANNUAL
from   per_absence_attendance_types paat,
       per_absence_attendances paa,
       per_abs_attendance_reasons paar,
per_all_assignments_f paaf
where  1=1
AND    paat.absence_attendance_type_id=paa.absence_attendance_type_id
and paa.person_id=paaf.person_id
AND PAAF.PRIMARY_FLAG='Y'
and paa.date_start between paaf.effective_start_date and paaf.effective_end_date
AND    paa.abs_attendance_reason_id =paar.abs_attendance_reason_id(+)
AND    paat.name =nvl(:p_leavetype,paat.name)
/*AND (paa.date_start BETWEEN fnd_date.canonical_to_date(:P_START_DATE) AND fnd_date.canonical_to_date(:P_END_DATE) OR
     paa.date_end BETWEEN fnd_date.canonical_to_date(:P_START_DATE) AND fnd_date.canonical_to_date(:P_END_DATE) OR
     (fnd_date.canonical_to_date(:P_START_DATE) < paa.date_start AND fnd_date.canonical_to_date(:P_END_DATE) > paa.date_end))*/
AND ((paa.date_start BETWEEN :P_START_DATE AND :P_END_DATE) OR
     (paa.date_end BETWEEN :P_START_DATE AND :P_END_DATE) OR
     (:P_START_DATE < paa.date_start AND :P_END_DATE > paa.date_end))
AND paa.person_id = :person_id
ORDER BY paat.name,paa.date_start

Performance Rating query

SELECT PAPF.EMPLOYEE_NUMBER,
         PAPF.GLOBAL_NAME                     STAFF_NAME,
         to_char(PAPF.START_DATE , 'DD-MON-YYYY') Hire_date,
         A.RATING_MEANING                     RATING,
         TO_CHAR (A.REVIEW_DATE, 'DD-MON-YYYY') REVIEW_DATE,
         A.CREATION_DATE,
         haou.name                            Department
    FROM PER_PERFORMANCE_REVIEWS_V a,
         per_all_people_f        papf,
         per_all_assignments_f   paaf,
         hr_all_organization_units haou
   WHERE     a.person_id = papf.person_id
         AND papf.person_id = paaf.person_id
         AND paaf.organization_id = haou.organization_id
         AND papf.business_group_id = 81
         AND papf.current_employee_flag = 'Y'
         and paaf.primary_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 PAPF.EMPLOYEE_NUMBER = NVL (:P_EMP_NO, PAPF.EMPLOYEE_NUMBER)
         AND TO_DATE (A.REVIEW_DATE) > TO_DATE ('30-JUN-2016')
         and to_date(PAPF.START_DATE)<=TO_DATE('30-JUN-2016')
        -- and PAPF.EMPLOYEE_NUMBER ='1376'
ORDER BY PAPF.EMPLOYEE_NUMBER

Costing Detail Query

select
paf.ASSIGNMENT_NUMBER, ppf.FULL_NAME ,pet.element_name,pca.CONCATENATED_SEGMENTS,
decode(pc.DEBIT_OR_CREDIT,’D’,pc.COSTED_VALUE) Debit,
decode(pc.DEBIT_OR_CREDIT,’C’,pc.COSTED_VALUE) Credit
from
per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions pav,
pay_payroll_actions ppa,
pay_costs pc,
PAY_COST_ALLOCATION_KEYFLEX pca,
pay_element_types_f pet,
pay_run_results prr,
pay_run_result_values prrv
where ppf.PERSON_ID=paf.PERSON_ID
and paf.ASSIGNMENT_ID=pav.ASSIGNMENT_ID
and paf.PRIMARY_FLAG=’Y’
and ppf.EMPLOYEE_NUMBER=:p_emp_no
and pav.PAYROLL_ACTION_ID=ppa.PAYROLL_ACTION_ID
and trunc(ppa.EFFECTIVE_DATE) between :p_start_date and :p_end_date
and pav.ASSIGNMENT_ACTION_ID=pc.ASSIGNMENT_ACTION_ID
and pc.COST_ALLOCATION_KEYFLEX_ID=pca.COST_ALLOCATION_KEYFLEX_ID
and pet.ELEMENT_TYPE_ID=prr.ELEMENT_TYPE_ID
and prr.RUN_RESULT_ID=prrv.RUN_RESULT_ID
and pc.RUN_RESULT_ID=prrv.RUN_RESULT_ID
and pc.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
and :p_end_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
and ppf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_people_f where person_id=ppf.PERSON_ID)
and paf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_assignments_f where assignment_id=paf.ASSIGNMENT_ID)

Employee assignment movement details query

/* Formatted on 12/31/2017 12:21:01 PM (QP5 v5.294) */
  SELECT ppf.employee_number,
         paaf.assignment_number,
         ppf.full_name,
         paaf.organization_id         current_org_id,
         paaf.effective_start_date    Curr_org_start_date,
         (SELECT name
            FROM hr_all_organization_units
           WHERE organization_id = paaf.organization_id)
            current_org_name,
         paaf_prev.effective_start_date prev_org_start_date,
         paaf_prev.organization_id    prev_org_id,
         (SELECT name
            FROM hr_all_organization_units
           WHERE organization_id = paaf_prev.organization_id)
            prev_org,
            (SELECT name
            FROM PER_POSITIONS
           WHERE POSITION_ID = paaf.POSITION_ID) CURRENT_POSITION,
           (SELECT name
            FROM PER_GRADES
           WHERE GRADE_ID = paaf.GRADE_ID) CURRENT_GRADE,
(SELECT name
            FROM PER_GRADES
           WHERE GRADE_ID = paaf_prev.GRADE_ID) PREVIOUS_GRADE  ,
(SELECT name
            FROM PER_POSITIONS
           WHERE POSITION_ID = paaf_prev.POSITION_ID) PREVIOUS_POSITION     ,
         hr_general.decode_lookup ('EMP_ASSIGN_REASON', PAAF.change_reason)
            MOVE_TYPE
    FROM per_all_assignments_f paaf,
         per_all_assignments_f paaf_prev,
         pay_people_groups   ppg,
         pay_people_groups   ppg_prev,
         per_all_people_f    ppf
   WHERE     paaf_prev.effective_end_date + 1 = paaf.effective_start_date
         AND paaf_prev.assignment_id = paaf.assignment_id
         AND paaf_prev.assignment_type = 'E'
         --and ppf.employee_number = '1134'
         AND paaf.assignment_type = 'E'
         AND paaf.organization_id <> paaf_prev.organization_id
         AND paaf.PEOPLE_GROUP_ID = ppg.people_group_id
         AND paaf_prev.PEOPLE_GROUP_ID = ppg_prev.people_group_id
         AND ppf.BUSINESS_GROUP_ID = 81
         AND paaf.effective_start_date BETWEEN ppf.effective_start_date
                                           AND ppf.effective_end_date
         AND paaf.person_id = ppf.person_id 
        -- AND    paaf_prev.effective_start_date >= :p_effective_date 
ORDER BY paaf_prev.effective_start_date DESC

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;

employee Competency Total score in oracle apps r12 hrms pms

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,
            SUM (TO_NUMBER (prl.step_value)) / COUNT (*)
               COMPETENCE_RATING,
            SUM (TO_NUMBER (prl.step_value)) / COUNT (*) * 0.4
               COMPETENCE_RATING_40_PER,
            PAP.TYPE,
            PAR.APPRAISAL_DATE,
            PAR.APPRAISAL_PERIOD_START_DATE,
            PAR.APPRAISAL_PERIOD_END_DATE,
            PAR.APPRAISAL_ID,
            par.appraisee_person_id,
            par.appraisER_person_id,
            PA.ASSESSMENT_ID,
            PA.ASSESSMENT_TYPE_ID,
            PAR.PLAN_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'
   GROUP BY papf.employee_number,
            papf.global_name,
            PAR.APPRAISAL_ID,
            PAR.appraisee_person_id,
            papF.person_id,
            APPRAISER_PERSON_ID,
            paR.APPRAISAL_DATE,
            paR.APPRAISAL_PERIOD_START_DATE,
            paR.APPRAISAL_PERIOD_END_DATE,
            paR.APPRAISAL_SYSTEM_STATUS,
            paR.PLAN_ID,
            papf.EMAIL_ADDRESS,
            papf.date_of_birth,
            papf.start_date,
            papF.SEX,
            PAPF.PERSON_ID,
            papF.marital_status,
            papF.NATIONALITY,
            DEPT.name,
            PAAF.GRADE_ID,
            PAAF.JOB_ID,
            PAAF.POSITION_ID,
            PAAF.PEOPLE_GROUP_ID,
            PAAF.location_ID,
            PAR.APPRAISER_PERSON_ID,
            PAP.TYPE,
            PA.ASSESSMENT_ID,
            PA.ASSESSMENT_TYPE_ID,
            PAR.PLAN_ID,
            PAAF.ASSIGNMENT_ID,
            PAR.APPRAISAL_DATE,
            PAR.APPRAISAL_PERIOD_START_DATE,
            PAR.APPRAISAL_PERIOD_END_DATE
   ORDER BY PAPF.EMPLOYEE_NUMBER;

Employee Objective Summary score in Oracle apps PMS R12

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,
            SUM (
               TO_NUMBER (
                  HR_APPRAISALS_UTIL_SS.get_objective_score (POE.OBJECTIVE_ID,
                                                             POE.APPRAISAL_ID)))
               OBJECTIVE_SCORE,
              SUM (
                 TO_NUMBER (
                    HR_APPRAISALS_UTIL_SS.get_objective_score (
                       POE.OBJECTIVE_ID,
                       POE.APPRAISAL_ID)))
            * 0.6
               Objective_scroe_60per,
            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
       FROM PER_APPRAISALS                 PAP,
            PER_OBJECTIVES                 POE,
            per_all_people_f               papf,
            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.appraisee_person_id = papF.person_id
            AND PAPF.PERSON_ID = PAAF.PERSON_ID
              AND paaf.organization_id =DEPT.organization_id
            --AND TO_CHAR (pap.appraisal_date, 'YYYY') = '2017'
            --AND pap.APPRAISAL_ID = 35103
            AND papf.current_employee_flag = 'Y'
            AND PAAF.PRIMARY_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 papf.employee_number = '1352'
   GROUP BY papf.employee_number,
            papf.global_name,
            PAP.APPRAISAL_ID,
            PAp.appraisee_person_id,
            papF.person_id,
            APPRAISER_PERSON_ID,
            pap.APPRAISAL_DATE,
            pap.APPRAISAL_PERIOD_START_DATE,
            pap.APPRAISAL_PERIOD_END_DATE,
            pap.APPRAISAL_SYSTEM_STATUS,
            pap.PLAN_ID,
            papf.EMAIL_ADDRESS,
            papf.date_of_birth,
            papf.start_date,
            papF.SEX,
            PAPF.PERSON_ID,
            papF.marital_status,
            papF.NATIONALITY,
            DEPT.name,
            PAAF.GRADE_ID,
            PAAF.JOB_ID,
            PAAF.POSITION_ID,
            PAAF.PEOPLE_GROUP_ID,
            PAAF.location_ID,
            pap.APPRAISER_PERSON_ID
   ORDER BY 1;

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;

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;