Employee Supervisor based on Position Hierarchy

select   psv.position_structure_id,
         pps.name hierarchy_name,
         curr_person.person_id curr_person_id,
         curr_person.employee_number curr_colleague_number,
           curr_person.full_name as curr_colleague_name,
           str.subordinate_position_id as curr_pos_id,
           pos.name as curr_pos_name,
           next_person.person_id supervisor_person_id,
           next_person.employee_number supervisor_collegue_num,
           next_person.full_name as supervisor_name,
           str.parent_position_id as supervisor_pos_id,
           next_pos.name as supervisor_pos_name
    FROM   per_all_people_f curr_person,--fnd_user cur_user,
           per_pos_structure_elements str,
           per_pos_structure_versions psv,
           per_all_positions pos,
           per_all_positions next_pos,
           per_all_assignments_f cur_assign,
           per_all_people_f next_person,
           per_position_structures pps,
           per_all_assignments_f next_assign
   WHERE   str.pos_structure_version_id = psv.pos_structure_version_id
           AND TRUNC (SYSDATE) BETWEEN psv.date_from
                                   AND  NVL (psv.date_to, SYSDATE)
           AND pos.position_id = str.subordinate_position_id
           AND next_pos.position_id = str.parent_position_id
           and cur_assign.position_id = pos.position_id
           and cur_assign.person_id = curr_person.person_id
           and trunc(sysdate) between curr_person.effective_start_date and curr_person.effective_end_date
           and trunc(sysdate) between cur_assign.effective_start_date and cur_assign.effective_end_date
           and trunc(sysdate) between next_person.effective_start_date and next_person.effective_end_date
           and trunc(sysdate) between next_assign.effective_start_date and next_assign.effective_end_date
           and next_assign.position_id  = next_pos.position_id
           and next_assign.person_id = next_person.person_id
           AND  psv.position_structure_id = pps.position_structure_id