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 !! :) 

EBS: Employee Performance Query

To get the Employee Performance Band.


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


Enjoy and Stay Well !! :)

EBS: New Employee Hire And Termination Query

1. To get the list of the employees who has joined in a specific month.


/* Formatted on 01/12/2015 11:16:54 (QP5 v5.126.903.23003) */
SELECT   DISTINCT
         papf.employee_number,
         papf.full_name,
         TO_CHAR (ppos.date_start, 'DD-MON-YYYY') date_start
  FROM   apps.per_all_people_f papf,
         per_all_assignments_f paaf,
         per_periods_of_service ppos
 WHERE   ppos.date_start BETWEEN TO_DATE ('01-OCT-2014', 'DD-MON-YYYY')
                             AND  TO_DATE ('31-OCT-2014', 'DD-MON-YYYY')
         AND papf.person_id = paaf.person_id
         AND papf.person_id = ppos.person_id


1. To get the list of the employees who has terminated in a specific month.

/* Formatted on 01/12/2015 11:17:55 (QP5 v5.126.903.23003) */
SELECT   DISTINCT
         papf.employee_number,
         papf.full_name,
         TO_CHAR (ppos.date_start, 'DD-MON-YYYY') date_start,
         TO_CHAR (ppos.actual_termination_date, 'DD-MON-YYYY')
            actual_termination_date
  FROM   apps.per_all_people_f papf,
         per_all_assignments_f paaf,
         per_periods_of_service ppos
 WHERE   ppos.actual_termination_date IS NOT NULL
         AND ppos.actual_termination_date BETWEEN TO_DATE ('01-OCT-2014',
                                                           'DD-MON-YYYY')
                                              AND  TO_DATE ('31-OCT-2014',
                                                            'DD-MON-YYYY')
         AND papf.person_id = paaf.person_id
         AND papf.person_id = ppos.person_id


Enjoy and Stay Well !! :)