This query selects the PERSON_NUMBER from PER_ALL_PEOPLE_F for individuals classified as employees. It joins PER_ALL_ASSIGNMENTS_M to ensure the person has a primary assignment with an ACTIVE assignment status. The query also joins PER_PERSON_TYPES to filter records where the SYSTEM_PERSON_TYPE is ‘EMP’, ensuring that only employee records are returned. Additionally, PER_PERSON_NAMES_F is joined to ensure the person has a GLOBAL name record. Effective date filters using SYSDATE are applied across the person, assignment, and name tables to ensure that all returned records are currently valid.
SQL Query Section
SELECT
papff1.person_number
FROM
per_all_people_f papff1 ,
per_all_assignments_m paamm2 ,
per_person_types pptt3 ,
per_person_names_f ppnff4
WHERE
sysdate between nvl(papff1.effective_start_date, sysdate - 1) and nvl(papff1.effective_end_date, sysdate + 1)
and papff1.person_id = paamm2.person_id
and paamm2.primary_flag = 'Y'
and paamm2.assignment_status_type = 'ACTIVE'
and sysdate between nvl(paamm2.effective_start_date, sysdate - 1) and nvl(paamm2.effective_end_date, sysdate + 1)
and paamm2.person_type_id = pptt3.person_type_id
and pptt3.system_person_type = 'EMP'
and papff1.person_id = ppnff4.person_id
and ppnff4.name_type = 'GLOBAL'
and sysdate between nvl(ppnff4.effective_start_date, sysdate - 1) and nvl(ppnff4.effective_end_date, sysdate + 1) Output Results
