Employee Superviser Query

SELECTasg.organization_id,

       emp.employee_number,

       emp.full_name emp_name,

       pos.NAMEPOSITION,

       sup.employee_number sup_emp_no,

       sup.full_name sup_name,

       sup_pos.NAMEsup_pos,

       sppt.user_person_type,

       emp.effective_start_date emp_esd,

       emp.effective_end_date emp_eed,

       asg.effective_start_date asg_esd,

       asg.effective_end_date asg_eed,

       sup.effective_start_date sup_esd,

       sup.effective_end_date sup_eed,

       sup_asg.effective_start_date sup_asg_esd,

       sup_asg.effective_end_date sup_asg_eed,

       sptu.effective_start_date sptu_esd,

       sptu.effective_end_date sptu_eed

  FROMper_all_people_f emp,

       per_all_assignments_f asg,

       per_all_positions pos,

       per_all_people_f sup,

       per_all_assignments_f sup_asg,

       per_all_positions sup_pos,

       per_person_type_usages_f sptu,

       per_person_types sppt

 WHEREasg.person_id = emp.person_id

   ANDpos.position_id(+)= asg.position_id

   ANDsup.person_id(+) = asg.supervisor_id

   ANDsup_asg.person_id(+)= sup.person_id

   ANDsup_pos.position_id(+)= sup_asg.position_id

   ANDsptu.person_id = emp.person_id

   ANDsppt.person_type_id =sptu.person_type_id

   AND emp.person_type_id = 1120