SELECT
papf.person_number,
papf.person_id,
ppnf.name_type,
ppnf.display_name,
ppnf.first_name,
ppnf.last_name,
(
SELECT
pptt.user_person_type
FROM
per_person_types_tl pptt
WHERE
1 = 1
AND pptt.language = 'US'
AND pptt.person_type_id = paam.person_type_id
) person_type,
haou.name departmentname,
haou2.name legal_employer
FROM
per_person_names_f ppnf,
per_all_people_f papf,
per_all_assignments_m paam,
hr_all_organization_units haou,
hr_all_organization_units haou2
WHERE
1 = 1
AND trunc(sysdate) BETWEEN trunc(ppnf.effective_start_date) AND trunc(ppnf.effective_end_date)
AND ppnf.name_type = 'GLOBAL'
AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)
AND ppnf.person_id = papf.person_id
AND papf.person_id = paam.person_id
AND paam.effective_latest_change = 'Y'
AND paam.assignment_type = 'E'
AND paam.primary_assignment_flag = 'Y'
AND trunc(sysdate) BETWEEN trunc(paam.effective_start_date) AND trunc(paam.effective_end_date)
AND paam.organization_id = haou.organization_id
AND haou2.organization_id = paam.legal_entity_id
ORDER BY
papf.person_number