This query selects the PERSON_NUMBER of a manager from PER_ALL_PEOPLE_F who is assigned as a LINE_MANAGER for an employee. It uses PER_ASSIGNMENT_SUPERVISORS_F to identify the reporting relationship between the employee and the manager. The query joins PER_PERSON_NAMES_F to ensure the manager’s GLOBAL name record is used and joins PER_ALL_ASSIGNMENTS_F to verify that the manager has an active employee assignment. Multiple effective date conditions ensure that all related records are valid as of 31-Dec-2023. The query also includes a NOT EXISTS condition to ensure only the latest active assignment record for the manager is considered, preventing duplicate results caused by multiple assignments. The result returns distinct manager PERSON_NUMBER values.
SQL Query Section
SELECT DISTINCT
MGR_PAPFF3.person_number
FROM
per_assignment_supervisors_f psff1 ,
PER_ALL_PEOPLE_F PAPF_EMPP2 ,
PER_ALL_PEOPLE_F MGR_PAPFF3 ,
PER_PERSON_NAMES_F MGR_PPNFF4 ,
per_all_assignments_f MGR_PAAFF5
WHERE
1 = 1
AND psff1.MANAGER_TYPE = 'LINE_MANAGER'
AND psff1.person_id = PAPF_EMPP2.person_id
AND MGR_PAPFF3.person_id = psff1.manager_id
AND MGR_PAPFF3.person_id = MGR_PPNFF4.person_id
AND MGR_PAPFF3.person_id = MGR_PAAFF5.person_id
AND MGR_PPNFF4.NAME_TYPE = 'GLOBAL'
AND TO_DATE('31/12/2023','DD/MM/YYYY') BETWEEN psff1.effective_start_date AND psff1.EFFECTIVE_END_DATE
AND TO_DATE('31/12/2023','DD/MM/YYYY') BETWEEN PAPF_EMPP2.effective_start_date AND PAPF_EMPP2.EFFECTIVE_END_DATE
AND TO_DATE('31/12/2023','DD/MM/YYYY') BETWEEN MGR_PAPFF3.effective_start_date AND MGR_PAPFF3.EFFECTIVE_END_DATE
AND TO_DATE('31/12/2023','DD/MM/YYYY') BETWEEN MGR_PPNFF4.effective_start_date AND MGR_PPNFF4.EFFECTIVE_END_DATE
AND TO_DATE('31/12/2023','DD/MM/YYYY') BETWEEN MGR_PAAFF5.effective_start_date AND MGR_PAAFF5.EFFECTIVE_END_DATE
AND MGR_PAAFF5.assignment_status_type = 'ACTIVE'
AND MGR_PAAFF5.assignment_type = 'E'
-- AND PAPF_EMPP2.person_id = :{PARAMETER.PERSON_ID}
AND NOT EXISTS (
SELECT 1
FROM per_all_assignments_f a2
WHERE a2.person_id = MGR_PAAFF5.person_id
AND a2.assignment_type = 'E'
AND a2.assignment_status_type = 'ACTIVE'
AND TO_DATE('31/12/2023','DD/MM/YYYY')
BETWEEN a2.effective_start_date AND a2.effective_end_date
AND a2.effective_start_date > MGR_PAAFF5.effective_start_date) Output Results
