SELECT f.person_id person_id, f.effective_start_date,
f.effective_end_date,f.employee_number, f.full_name employee_name,gcc1.segment3 "EMP DEPT", f.email_address,
f2.full_name manager_name1,gcc2.segment3 "MGR1 DEPT", f3.full_name manager_name2,gcc3.segment3 "MGR2 DEPT",
f4.full_name manager_name3,gcc4.segment3"MGR3 DEPT", f5.full_name manager_name4,gcc5.segment3"MGR4 DEPT",
f6.full_name manager_name5,gcc6.segment3"MGR5 DEPT",
f7.full_name manager_name6
FROM per_all_people_f f,
per_assignments_f pera,
apps.per_all_people_f f2,
apps.per_all_people_f f3,
per_assignments_f pera1,
apps.per_all_people_f f4,
per_assignments_f pera2,
apps.per_all_people_f f5,
per_assignments_f pera3,
apps.per_all_people_f f6,
per_assignments_f pera4,
apps.per_all_people_f f7,
per_assignments_f pera5,
apps.per_all_people_f f8,
per_assignments_f pera6,
gl_code_combinations gcc1,
gl_code_combinations gcc2,
gl_code_combinations gcc3,
gl_code_combinations gcc4,
gl_code_combinations gcc5,
gl_code_combinations gcc6
WHERE 1 = 1
AND NVL (pera.supervisor_id, -99) = NVL (f2.person_id(+), -99)
AND NVL (f.person_id, 1) = NVL (pera.person_id(+), 1)
-- and f.person_id = &person_id -- Pass person_id
AND NVL (pera.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (pera.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (f.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f2.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (f2.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f3.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (f3.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f2.person_id, 1) = NVL (pera1.person_id(+), 1)
AND NVL (pera1.supervisor_id, -99) = NVL (f3.person_id(+), -99)
AND NVL (pera1.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (pera1.effective_end_date, SYSDATE + 1) > SYSDATE
and pera.default_code_comb_id = gcc1.code_combination_id
------cond for lvl 3
AND NVL (f4.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (f4.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f3.person_id, 1) = NVL (pera2.person_id(+), 1)
AND NVL (pera2.supervisor_id, -99) = NVL (f4.person_id(+), -99)
AND NVL (pera2.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (pera2.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f3.person_id, 1) = NVL (pera2.person_id, 1)
AND NVL (pera2.supervisor_id, -99) = NVL (f4.person_id, -99)
and pera1.default_code_comb_id = gcc2.code_combination_id
------cond for lvl 4
AND NVL (f5.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (f5.effective_end_date, SYSDATE + 1) > SYSDATE
AND f4.person_id = pera3.person_id(+)
AND NVL (pera3.supervisor_id, -99) = NVL (f5.person_id(+), -99)
AND NVL (pera3.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (pera3.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f4.person_id, -1) = NVL (pera3.person_id, -1)
AND NVL (pera3.supervisor_id, -99) = NVL (f5.person_id, -99)
and pera2.default_code_comb_id = gcc3.code_combination_id
------cond for lvl 5
AND NVL (f6.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (f6.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f5.person_id, 1) = NVL (pera4.person_id(+), 1)
AND NVL (pera4.supervisor_id, -99) = NVL (f6.person_id(+), -99)
AND NVL (pera4.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (pera4.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f5.person_id, 1) = NVL (pera4.person_id, 1)
AND NVL (pera4.supervisor_id, -99) = NVL (f6.person_id, -99)
and pera3.default_code_comb_id = gcc4.code_combination_id
-----cond for lvl VI
AND NVL (f7.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (f7.effective_end_date, SYSDATE + 1) > SYSDATE
AND f6.person_id = pera5.person_id(+)
AND NVL (pera5.supervisor_id, -99) = NVL (f7.person_id(+), -99)
AND NVL (pera5.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (pera5.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f6.person_id, 1) = NVL (pera5.person_id, 1)
AND NVL (pera5.supervisor_id, -99) = NVL (f7.person_id, -99)
and pera4.default_code_comb_id = gcc5.code_combination_id
-----cond for lvl VII
AND NVL (f8.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (f8.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f7.person_id, -99) = NVL (pera6.person_id(+), -99)
AND NVL (pera6.supervisor_id, -99) = NVL (f8.person_id(+), -99)
AND NVL (pera6.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (pera6.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (f7.person_id, 1) = NVL (pera6.person_id, 1)
AND NVL (pera6.supervisor_id, -99) = NVL (f8.person_id, -99)
and pera5.default_code_comb_id = gcc6.code_combination_id;
