EBS: Learning Management Query

To get the Oracle Learning Management details.

/* Formatted on 01/12/2015 11:33:22 (QP5 v5.126.903.23003) */
  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;



Enjoy and Stay Well !! :)