Saturday 1 November 2014

SQL to find 3 level Supervisors of Employee in Oracle HRMS

SELECT
  papf.last_update_date
,papf.first_name
,papf.employee_number
, papf.person_id
,papf.full_name
, papf1.person_id
,Papf1.full_name sup1
, papf2.person_id
, Papf2.full_name sup2
, papf3.person_id
, Papf3.full_name sup3
FROM
  apps.per_all_people_f papf
,apps.per_all_assignments_f paaf
,apps.per_all_people_f papf1
,apps.per_all_assignments_f paaf1
,apps.per_all_people_f papf2
,apps.per_all_assignments_f paaf2
,apps.per_all_people_f papf3
,apps.per_all_assignments_f paaf3
WHERE
  papf.employee_number IN ('12509')
AND papf.person_id      = paaf.person_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 paaf.supervisor_id = papf1.person_id
AND papf1.person_id    = paaf1.person_id
AND sysdate BETWEEN papf1.effective_start_date AND papf1.effective_end_date
AND sysdate BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND paaf1.supervisor_id = papf2.person_id
AND papf2.person_id     = paaf2.person_id
AND sysdate BETWEEN papf2.effective_start_date AND papf2.effective_end_date
AND sysdate BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND paaf2.supervisor_id = papf3.person_id
AND papf3.person_id     = paaf3.person_id
AND sysdate BETWEEN papf3.effective_start_date AND papf3.effective_end_date
AND sysdate BETWEEN paaf3.effective_start_date AND paaf3.effective_end_date
ORDER BY
  papf.first_name
,papf.employee_number;

No comments:

Post a Comment