Current Employee Validation Query in R12

Current Employee validation Query in R12


select distinct papf.person_id
  --into l_person_id
   from per_all_people_f papf ,
  per_all_assignments_f paa   ,
  per_assignment_status_types pas
  where papf.person_id            = paa.person_id
and paa.primary_flag              = 'Y'
and paa.assignment_type           = 'E'
and pas.user_status              IN ('Livex','Active Assignment','Active Contingent Assignment')
and paa.assignment_status_type_id =pas.assignment_status_type_id
and papf.current_employee_flag    ='Y'
and (papf.employee_number         =rec_key_members.employee_number
or papf.npw_number                =rec_key_members.employee_number)
and paa.primary_flag              = 'Y'
and paa.assignment_type          IN('E','C')
  --and     trunc(sysdate) between  papf.effective_start_date and papf.effective_end_date
  --and     trunc(sysdate) between  paa.effective_start_date and paa.effective_end_date
and trunc(:customer_start_date) between trunc(paa.effective_start_date) and trunc(paa.effective_end_date)
and paa.position_id is not null
and exisTS
  (select 'Y'
     from per_person_types ppt
    where ppt.person_type_id      = papf.person_type_id
  and ppt.active_flag             ='Y'
  and ppt.system_person_type NOT IN ('EX_EMP','EX_CWK','OTHER')
  );