EBS: Learning Management Query

EBS: Learning Management Query
To get the Oracle Learning Management details.


  SELECT   oe.title event_name,
           TO_CHAR (oe.course_start_date, 'DD-Mon-RRRR') stdate,
           TO_CHAR (oe.course_end_date, 'DD-Mon-RRRR') enddate,
           oe.course_start_time stime,
           oe.course_end_time etime,
           papf.email_address emp_mail,
           papf.full_name emp_name,
           papf1.email_address sup_mail,
           papf1.full_name sup_name,
           oe.evt_information2 class_location
    FROM   ota_events oe,
           ota_delegate_bookings odb,
           per_all_people_f papf,
           per_all_assignments_f paaf,
           per_all_people_f papf1
   WHERE       1 = 1
           AND odb.event_id(+) = oe.event_id
           AND papf.person_id = odb.delegate_person_id
           AND odb.internal_booking_flag = 'Y'
           AND paaf.primary_flag = 'Y'
           AND papf.current_employee_flag = 'Y'
           AND paaf.person_id = papf.person_id
           AND papf1.person_id = paaf.supervisor_id
           AND papf1.current_employee_flag = 'Y'
           AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                   AND  paaf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                   AND  papf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date
                                   AND  papf1.effective_end_date
ORDER BY   1;

EBS: Employee Performance Query

EBS: Employee Performance Query
To get the Employee Performance Band.



  SELECT   DISTINCT papf.employee_number,
                    papf.full_name,
                    ppr.RATING_MEANING,
                    ppr.review_date,
                    hrv.name
    FROM   PER_ALL_PEOPLE_F papf,
           PER_all_ASSIGNMENTS_F paaf,
           PER_PERFORMANCE_REVIEWS_V ppr,
           PER_PERIODS_OF_SERVICE_V pps,
           HR_ORGANIZATION_UNITS_V hrv
   WHERE       1 = 1
           AND papf.person_id = ppr.person_id
           AND PPS.PERSON_ID = papf.person_id
           AND Paaf.PERSON_ID = papf.person_id
           AND HRV.ORGANIZATION_ID = paaf.ORGANIZATION_ID
           AND PPS.ACTUAL_TERMINATION_DATE IS NULL
           AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                   AND  papf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                   AND  paaf.effective_end_date
           AND TO_CHAR (ppr.review_date, 'YYYY') = '2014'
--and  to_number(ppr.RATING_MEANING) between 0 and  0.8    -
ORDER BY   papf.employee_number