code to get the supervisor id and employee number for certain employee numbers

select ppf.employee_number,

---code to get supervisor's emp number

(select employee_number sup_number from per_all_people_f ppf

where ppf.person_id = paaf.supervisor_id

and TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date

) super_number,

paaf.last_update_date,

paaf.effective_start_date , paaf.effective_end_date

from per_all_assignments_f paaf, per_all_people_f ppf

where TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date

AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date

and paaf.last_update_date = (select max(paaf.last_update_date) from per_all_assignments_f paaf where paaf.person_id = ppf.person_id )--and ppf.employee_number = 2794

and ppf.person_type_id in (1120,1121)

AND ppf.person_id in ( select ppf.person_id from per_all_people_f ppf where ppf.employee_number IN (

2796,2794,2791,2797,2784,2799,2801,2802,2803,2806,2800,2815,2819,2818,2823,2824,2829,2825,2827,2836,2841,2833,2112,2830,

2843,2831,2845,2839,2832,2848,2828,2847,2844,2849,2846,2850,2852,2854,2853,2858,2855,2856,2851,2862,2863,2857

)

)

and paaf.person_id = ppf.person_id

order by ppf.employee_number