SELECT b.pos_structure_version_id, 
       b.date_from, 
       (SELECT name 
        FROM   hr.hr_all_positions_f 
        WHERE  1 = 1 
               AND position_id = c.parent_position_id 
               AND Trunc(SYSDATE) BETWEEN effective_start_date AND 
                                          effective_end_date) 
       parent_position_name, 
       (SELECT papf.full_name 
        FROM   per_all_people_f papf, 
               per_all_assignments_f paaf 
        WHERE  1 = 1 
               AND papf.person_id = paaf.person_id 
               AND Trunc(SYSDATE) BETWEEN paaf.effective_start_date AND 
                                          paaf.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND 
                                          papf.effective_end_date 
               AND paaf.primary_flag = 'Y' 
               AND paaf.position_id = c.parent_position_id) 
       parent_position_holder, 
       (SELECT name 
        FROM   hr.hr_all_positions_f 
        WHERE  1 = 1 
               AND position_id = c.subordinate_position_id 
               AND Trunc(SYSDATE) BETWEEN effective_start_date AND 
                                          effective_end_date) 
       subordinate_position_name, 
       (SELECT papf.full_name 
        FROM   per_all_people_f papf, 
               per_all_assignments_f paaf 
        WHERE  1 = 1 
               AND papf.person_id = paaf.person_id 
               AND Trunc(SYSDATE) BETWEEN paaf.effective_start_date AND 
                                          paaf.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND 
                                          papf.effective_end_date 
               AND paaf.primary_flag = 'Y' 
               AND paaf.position_id = c.subordinate_position_id) 
       parent_position_holder 
FROM   per_position_structures a, 
       per_pos_structure_versions b, 
       per_pos_structure_elements c 
WHERE  1 = 1 
       AND a.name = '&position_hiersrchy_name' 
       /* Pass the Position hierarch name*/ 
       AND a.position_structure_id = b.position_structure_id 
       AND b.pos_structure_version_id = c.pos_structure_version_id 
ORDER  BY 1, 
          5, 
          6