Employee Data (Person#, original_hire_date, term_date, and rehire_date)

with 

  rehire as (

select to_Char(date_start, 'MM/DD/YYYY') rehire_date, 

   a.person_id

  from per_all_assignments_m a, per_periods_of_service b

     where 1=1

   and a.person_id = b.person_id

   and action_code ='REHIRE'

   and assignment_type ='E'

   and sysdate between a.effective_start_date and a.effective_end_date

   and actual_termination_date is null),

  term as (

select to_char(max(actual_termination_date), 'MM/DD/YYYY') term_date, 

   a.person_id

  from per_all_assignments_m a, per_periods_of_service b

where 1=1

   and a.person_id = b.person_id

   and assignment_type ='E'

  group by a.person_id),

  hire as (

select to_char(min(date_start), 'MM/DD/YYYY')  hire_date, 

   person_id

      from per_periods_of_service

where 1=1

  group by person_id)

select person_number, 

   rehire_date, 

   term_date, 

   hire.hire_date original_hire_date,

   user_status assignment_status

  from per_all_people_f a,

   rehire,

   term, 

   hire,

   per_all_assignments_m b,

   per_assignment_status_types_tl c

where rehire.person_id (+) = a.person_id

   and term.person_id (+) = a.person_id

   and hire.person_id = a.person_id

   and a.person_id = b.person_id

   and b.assignment_status_type_id = c.assignment_status_type_id

   and sysdate between b.effective_start_date and b.effective_end_date

   and b.assignment_type = 'E'