Employee with supervisor and Org detail Query

/* Formatted on 7/22/2014 10:30:06 AM (QP5 v5.115.810.9015) */
SELECT p.full_name,
       p.employee_number employee_number,
       p.last_name last_name,
       p.first_name first_name,
       p.original_date_of_hire hire_date,
       p.date_of_birth dob,
       p.sex gender,
       p.email_address email_address,
       (SELECT name
        FROM apps.hr_all_organization_units
        WHERE organization_id = p.business_group_id)
          organization,
       (SELECT location_code
        FROM apps.hr_locations_all_tl
        WHERE location_id = a.location_id AND language = USERENV ('LANG'))
          location_name,
       'A' status_flag,
       (SELECT concatenated_segments
        FROM apps.gl_code_combinations_kfv
        WHERE code_combination_id = a.default_code_comb_id)
          expense_account,
       (SELECT papf1.full_name supervisor_name
        FROM apps.per_all_people_f papf,
             apps.per_all_assignments_f paaf,
             apps.per_all_people_f papf1
        WHERE     papf.person_id = paaf.person_id
              AND paaf.primary_flag = 'Y'
              AND paaf.assignment_type = 'E'
              AND paaf.supervisor_id = papf1.person_id
              AND papf1.current_employee_flag = 'Y'
              AND papf.business_group_id = paaf.business_group_id
              AND SYSDATE BETWEEN papf.effective_start_date
                              AND  papf.effective_end_date
              AND SYSDATE BETWEEN paaf.effective_start_date
                              AND  paaf.effective_end_date
              AND SYSDATE BETWEEN papf1.effective_start_date
                              AND  papf1.effective_end_date
              AND papf.employee_number = p.employee_number
              AND papf.person_id = p.person_id)
          supervisor
FROM apps.per_all_people_f p,
     apps.per_all_assignments_f a,
     apps.pay_people_groups ppg,
     apps.hr_all_positions_f hap
WHERE     p.person_id = a.person_id
      AND SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
      AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date
      AND a.people_group_id = ppg.people_group_id
      AND hap.position_id(+) = a.position_id