— Person & Assignment Information
SELECT ppl.person_number,
       ppl.person_id,
       ppln.display_name,
       pps.date_start                                               hire_date,
       pps.actual_termination_date                                  term_date,
       paf.assignment_type,
       paf.assignment_number,
       paf.asg_information1                                         aca_ft,
       paf.action_code,
       paf.effective_start_date                                     paf_effective_start,
       paf.effective_end_date                                       paf_effective_end,
       paf.primary_flag,
       pni.national_identifier_number,
       le.name                                                      le_name,
       rep_est.name                                                 reporting_establishment,
       ppln_sup.full_name                                           supervisor_name,
       pps.worker_number,
       ppos_sup.worker_number                                       supervisor_worker_number,
       paf_sup.assignment_number                                    supervisor_asg,
       ppl_sup.person_number                                        supervisor_person_num,
       pps.adjusted_svc_date,
       org.name                                                     org_name,
       jobs.name                                                    job_name,
       paf.assignment_id,
       paf.assignment_name,
       paf.employee_category,
       paf.employment_category,
       hr_general.Decode_lookup(‘EMP_CAT’, paf.employment_category) asg_category,
       past.user_status                                             assignment_status,
       paf.assignment_status_type_id,
       To_char(pp.date_of_birth, ‘MM/DD/YYYY’)                      emp_dob
FROM   per_all_assignments_m paf,
       hr_all_organization_units org,
       per_all_people_f ppl,
       per_person_names_f_v ppln,
       per_jobs jobs,
       per_national_identifiers pni,
       hr_all_organization_units_vl rep_est,
       per_persons pp,
       per_assignment_status_types_vl past,
       per_periods_of_service pps,
       per_assignment_supervisors_f sup,
       per_all_assignments_f paf_sup,
       per_all_people_f ppl_sup,
       per_person_names_f_v ppln_sup,
       per_periods_of_service ppos_sup,
       per_legal_employers le
WHERE  1 = 1
       AND Trunc(SYSDATE) BETWEEN paf.effective_start_date (+) AND paf.effective_end_date (+)
       AND Trunc(SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date
       AND Trunc(SYSDATE) BETWEEN ppln.effective_start_date (+) AND ppln.effective_end_date (+)
       AND ppl.person_id = pp.person_id (+)
       AND ppl.person_id = paf.person_id(+)
       AND ppl.person_id = ppln.person_id (+)
       AND paf.job_id = jobs.job_id(+)
       AND paf.organization_id = org.organization_id (+)
       AND paf.assignment_status_type_id = past.assignment_status_type_id (+)
       AND ppl.person_id = pni.person_id (+)
       AND paf.assignment_type = ‘E’
       AND pni.national_identifier_type (+) = ‘SSN’
       AND paf.assignment_id = sup.assignment_id (+)
       AND sup.manager_assignment_id = paf_sup.assignment_id(+)
       AND paf_sup.person_id = ppl_sup.person_id(+)
       AND ppl_sup.person_id = ppln_sup.person_id(+)
       AND paf_sup.period_of_service_id = ppos_sup.period_of_service_id (+)
       AND Trunc(SYSDATE) BETWEEN sup.effective_start_date (+) AND sup.effective_end_date(+)
       AND Trunc(SYSDATE) BETWEEN paf_sup.effective_start_date (+) AND paf_sup.effective_end_date(+)
       AND Trunc(SYSDATE) BETWEEN ppl_sup.effective_start_date(+) AND ppl_sup.effective_end_date(+)
       AND Trunc(SYSDATE) BETWEEN ppln_sup.effective_start_date (+) AND
                                  ppln_sup.effective_end_date(+)
       AND Trunc(SYSDATE) BETWEEN le.effective_start_date (+) AND le.effective_end_date(+)
       AND paf.legal_entity_id = le.organization_id (+)
       AND le.status = ‘A’
       AND past.user_status NOT LIKE ‘%In%ctive%’
       AND paf.establishment_id = rep_est.organization_id (+)
       AND 1 = 1
ORDER  BY pps.date_start ASC,
          paf.last_update_date DESC,
          paf.effective_start_date ASC;