0
0
WITH LatestManagerAssignment AS (
SELECT *
FROM (
SELECT a.*,
ROW_NUMBER() OVER (PARTITION BY a.person_id ORDER BY a.effective_start_date DESC, a.effective_sequence DESC) rn
FROM per_all_assignments_m a
WHERE a.assignment_status_type = 'ACTIVE'
AND a.primary_flag = 'Y'
AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date
) WHERE rn = 1
),
LatestEmployeeAssignment AS (
SELECT *
FROM (
SELECT a.*,
ROW_NUMBER() OVER (PARTITION BY a.person_id ORDER BY a.effective_start_date DESC, a.effective_sequence DESC) rn
FROM per_all_assignments_m a
WHERE a.assignment_status_type = 'ACTIVE'
AND a.primary_flag = 'Y'
AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date
) WHERE rn = 1
),
LatestPersonNames AS (
SELECT *
FROM (
SELECT n.*,
ROW_NUMBER() OVER (PARTITION BY n.person_id ORDER BY n.effective_start_date DESC) rn
FROM per_person_names_f n
WHERE n.name_type = 'GLOBAL'
AND SYSDATE BETWEEN n.effective_start_date AND n.effective_end_date
) WHERE rn = 1
),
LatestManagerPerson AS (
SELECT *
FROM (
SELECT p.*,
ROW_NUMBER() OVER (PARTITION BY p.person_id ORDER BY p.effective_start_date DESC) rn
FROM per_all_people_f p
WHERE SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
) WHERE rn = 1
),
LatestEmployeePerson AS (
SELECT *
FROM (
SELECT p.*,
ROW_NUMBER() OVER (PARTITION BY p.person_id ORDER BY p.effective_start_date DESC) rn
FROM per_all_people_f p
WHERE SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
) WHERE rn = 1
),
FinalResult AS (
SELECT
mgr.person_number AS manager_person_number,
mgr_name.display_name AS manager_name,
mgr_asg.assignment_status_type AS manager_assignment_status,
emp.person_number AS employee_person_number,
emp_name.display_name AS employee_name,
emp_asg.assignment_status_type AS employee_assignment_status,
ROW_NUMBER() OVER (PARTITION BY emp.person_number ORDER BY emp.person_number) AS emp_rn
FROM per_manager_hrchy_dn pmh
JOIN LatestManagerPerson mgr
ON pmh.manager_id = mgr.person_id
JOIN LatestPersonNames mgr_name
ON mgr.person_id = mgr_name.person_id
JOIN LatestManagerAssignment mgr_asg
ON mgr.person_id = mgr_asg.person_id
JOIN LatestEmployeePerson emp
ON pmh.person_id = emp.person_id
JOIN LatestPersonNames emp_name
ON emp.person_id = emp_name.person_id
JOIN LatestEmployeeAssignment emp_asg
ON emp.person_id = emp_asg.person_id
WHERE pmh.manager_type = 'LINE_MANAGER'
AND (
:manager_person_number IS NULL OR mgr.person_number IN (:manager_person_number)
)
AND (
:employee_person_number IS NULL OR emp.person_number IN (:employee_person_number)
)
)
SELECT
ROW_NUMBER() OVER (ORDER BY employee_person_number) AS sequence_number,
manager_person_number,
manager_name,
manager_assignment_status,
employee_person_number,
employee_name,
employee_assignment_status
FROM FinalResult
WHERE emp_rn = 1
ORDER BY employee_person_number
I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.