BIP – Query to get worker Parent Position

Select  DISTINCT papf_emp.person_number 				 				employee_number,
        ppnf.first_name||' '||ppnf.last_name 				employee_name,
		(select hapft.name
		   from hr_all_positions_f hapf, HR_ALL_POSITIONS_F_TL hapft
          WHERE hapf.position_id = paam.position_id
            AND hapf.position_id = hapft.position_id
	        AND trunc(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
	        AND trunc(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date) employee_position,
		(select hapft.name
		   from hr_all_positions_f hapf, hr_all_positions_f_tl hapft,per_position_hierarchy_f pphf
          WHERE hapf.position_id = pphf.parent_position_id
		    AND pphf.position_id=paam.position_id
            AND hapf.position_id = hapft.position_id
	        AND trunc(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
			AND trunc(SYSDATE) BETWEEN pphf.effective_start_date AND pphf.effective_end_date
	        AND trunc(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date) employee_position_parent
   from per_all_assignments_m paam,
		per_all_people_f papf_emp,
		per_person_names_f ppnf
  where papf_emp.person_id = paam.person_id
	and papf_emp.person_id = ppnf.person_id
	and trunc(sysdate) between papf_emp.effective_start_date and papf_emp.effective_end_date
    and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
    and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
	and ppnf.name_type ='GLOBAL'
	and paam.assignment_type ='E'
	and paam.assignment_status_type = 'ACTIVE'
	--and paam.position_id = 300000009196111
order by papf_emp.person_number