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