Wednesday, April 29, 2015

Query To get List of Employees which has Supervisors

select papf.full_name "Employee Name",
papf1.full_name "Supervisor Name"
from per_all_people_f papf,
per_all_people_f papf1,
per_all_assignments_f paaf
where papf1.person_id = paaf.supervisor_id
and papf.person_id = paaf.person_id
and papf.employee_number is not null
and paaf.primary_flag = 'Y'
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(nvl(papf.effective_end_date, sysdate))
and trunc(sysdate) between trunc(papf1.effective_start_date) and trunc(nvl(papf1.effective_end_date, sysdate))
group by papf.full_name, papf1.full_name
order by 1 asc