Oracle Cloud ERP – Query to get HRMS Employee Display Name, First Name, Last Name, Person Number, Employee Type

--Employee Data -- Date Effective
SELECT * FROM PER_ALL_PEOPLE_F PAPF
WHERE 1=1
AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)

--Employee Person Name --Date Effective
SELECT * FROM per_person_names_f ppnf
WHERE 1=1
AND trunc(sysdate) BETWEEN trunc(ppnf.effective_start_date) AND trunc(ppnf.effective_end_date)
AND ppnf.name_type = 'GLOBAL'

--Query to get Employee Assignments
SELECT * FROM per_all_assignments_m paam

--Query to get HRMS Employee Display Name, First Name, Last Name, Person Number, Employee Type
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
FROM
per_person_names_f ppnf,
per_all_people_f papf,
per_all_assignments_m paam
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)
ORDER BY papf.person_number