SELECT papf.

person_id,
papf.person_number EMPLOYEE_ID,
ppn.first_name,
ppn.middle_names,
ppn.last_name,
ppn.title,
pps.date_start HIRE_DATE,
pps.actual_termination_date TERMINATION_DATE,
paam.assignment_type,
paam.employee_category,
past.assignment_status_code EMPLOYEE_STATUS,
pps.attribute1 car_allowance,
pea.email_address,
papf_s.person_number
manager_person_number,
Greatest(papf.creation_date, paam.creation_date) creation_date,
Greatest(papf.last_update_date, paam.last_update_date) last_update_date,
papf.effective_start_date per_eff_start_date
,
papf.effective_end_date
per_eff_end_date,
paam.effective_start_date
assgn_eff_start_date,
paam.effective_end_date assgn_eff_end_date
,
hio.source_system_id
wrel_system_id,
hio_assgn.source_system_id assgn_system_id,
hio_wt.source_system_id wt_system_id,
hio_asgnsup.source_system_id assgnsup_system_id
,
xep.name
legal_employer,
dep.name hr_department,
pjf.job_code,
bu.name business_unit,
Decode(gcc.segment1, NULL, NULL,
gcc.segment1
|| '.'
||gcc.segment2
|| '.'
||gcc.segment3
|| '.'
||gcc.segment4
|| '.'
||gcc.segment5
|| '.'
||gcc.segment6
|| '.'
|| gcc.segment7) expense_account,
mgr_info.mgr_first_name,
mgr_info.mgr_last_name,
mgr_info.mgr_email_address,
mgr_info.mgr_person_id,
mgr_info.mgr_person_number,
pasf.manager_id manager_person_id,
paam.assignment_id paam_assignment_id
FROM per_all_people_f papf,
per_person_names_f ppn,
per_all_assignments_m paam,
per_all_assignments_m paam_wt,
per_periods_of_service pps,
per_email_addresses pea,
per_assignment_status_types past,
per_assignment_supervisors_f pasf,
per_all_assignments_m paam_s,
per_all_people_f papf_s,
hrc_integration_key_map hio,
hrc_integration_key_map hio_assgn,
hrc_integration_key_map hio_wt,
hrc_integration_key_map hio_asgnsup,
hr_all_organization_units_f haou,
xle_entity_profiles xep,
hr_all_organization_units_tl dep,
hr_all_organization_units_tl bu,
per_jobs_f pjf,
gl_code_combinations gcc,
(SELECT ppos.person_id,
Max(ppos.date_start) date_start
FROM per_periods_of_service ppos
GROUP BY ppos.person_id) ppos,

(select mgr_papf.person_number mgr_person_number, mgr_papf.person_id

mgr_person_id, mgr_names.first_name mgr_first_name, mgr_names.last_name
mgr_last_name, mgr_email.email_address mgr_email_address

from per_all_people_f mgr_papf, per_person_names_f mgr_names,

per_email_addresses mgr_email

where 1=1 --mgr_papf.person_id = pasf.manager_id

and mgr_names.person_id = mgr_email.person_id
and mgr_names.person_id = mgr_papf.person_id

and mgr_names.name_type='GLOBAL'

and (trunc(sysdate) between trunc(mgr_papf.effective_start_date) and

trunc(mgr_papf.effective_end_date))
and (trunc(sysdate) between trunc(mgr_names.effective_start_date) and
trunc(mgr_names.effective_end_date))
and (trunc(sysdate) between trunc(mgr_email.date_from) and
trunc(nvl(mgr_email.date_to,sysdate)))

) mgr_info

WHERE papf.person_id = ppn.person_id

AND papf.person_id = paam.person_id
AND paam.work_terms_assignment_id = paam_wt.assignment_id
AND papf.person_id = pps.person_id
AND pasf.manager_id = mgr_info.mgr_person_id(+)
AND paam.period_of_service_id = pps.period_of_service_id
AND papf.person_id = pea.person_id(+)
AND pea.email_type(+) = 'W1'
AND paam.assignment_status_type_id = past.assignment_status_type_id(+)
AND paam.assignment_id = pasf.assignment_id (+)
AND pasf.manager_assignment_id = paam_s.assignment_id(+)

AND paam_s.person_id = papf_s.person_id(+)

AND pps.period_of_service_id = hio.surrogate_id
AND paam.assignment_id = hio_assgn.surrogate_id
AND paam_wt.assignment_id = hio_wt.surrogate_id
AND pasf.assignment_supervisor_id = hio_asgnsup.surrogate_id(+)
AND paam.legal_entity_id = haou.organization_id
AND haou.legal_entity_id = xep.legal_entity_id
AND paam.organization_id = dep.organization_id(+)
AND paam.business_unit_id = bu.organization_id(+)
AND paam.job_id = pjf.job_id(+)
AND paam.default_code_comb_id = gcc.code_combination_id (+)
AND hio.object_name = 'PeriodOfService'
AND hio_assgn.object_name = 'Assignment'
AND hio_wt.object_name = 'Assignment'
AND hio_asgnsup.object_name(+) = 'AssignmentSupervisor'
AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND Trunc(SYSDATE) BETWEEN ppn.effective_start_date AND
ppn.effective_end_date

---AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

--- paam.effective_end_date

---AND Trunc(SYSDATE) BETWEEN paam_wt.effective_start_date AND

--- paam_wt.effective_end_date

AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date(+) AND

pasf.effective_end_date(+)

---AND Trunc(SYSDATE) BETWEEN paam_s.effective_start_date(+) AND

--- paam_s.effective_end_date(+)

AND Trunc(SYSDATE) BETWEEN papf_s.effective_start_date(+) AND

papf_s.effective_end_date(+)
AND Trunc(SYSDATE) BETWEEN haou.effective_start_date AND
haou.effective_end_date
AND Trunc(SYSDATE) BETWEEN pjf.effective_start_date(+) AND
pjf.effective_end_date(+)
AND pps.date_start = ppos.date_start
AND pps.person_id = ppos.person_id
AND ppn.name_type = 'GLOBAL'
AND paam.primary_flag = 'Y'
AND pps.primary_flag = 'Y'
AND dep.LANGUAGE(+) = 'US'
AND bu.LANGUAGE(+) = 'US'
--and papf.person_number ='361943'
and papf.person_number='678332'

---AND paam.effective_latest_change = 'Y'

---AND paam.work_terms_assignment_id IS NOT NULL

AND ( papf.last_update_date BETWEEN

Nvl(:p_start_date, papf.last_update_date) AND
Nvl(:p_end_date, papf.last_update_date)
---OR paam.last_update_date BETWEEN
--- Nvl(:p_start_date, paam.last_update_date)
--- AND
--- Nvl(:p_end_date, paam.last_update_date)
OR pps.last_update_date BETWEEN
Nvl(:p_start_date, pps.last_update_date)
AND
Nvl(:p_end_date, pps.last_update_date)
OR ppn.last_update_date BETWEEN
Nvl(:p_start_date, ppn.last_update_date)
AND
Nvl(:p_end_date, ppn.last_update_date)
OR pea.last_update_date BETWEEN
Nvl(:p_start_date, pea.last_update_date)
AND
Nvl(:p_end_date, pea.last_update_date) )