SQL for Position Managment

This is a good sample for obtaining your current SUP and reports_to. (Based upon position)

SELECT a.emplid
, a.position_nbr
, a.reports_to
, b.name
, b.first_name
, b.last_name
, b.middle_name
, c.phone
, c.extension
FROM ps_job a
, ps_names b
, ps_personal_phone c
, ps_position_data d
WHERE a.emplid = b.emplid
AND a.empl_status NOT IN ('R','T')
AND a.hr_status = 'A'
AND a.effdt = (
SELECT MAX(a1.effdt)
FROM ps_job a1
WHERE a1.emplid = a.emplid
AND a1.empl_rcd = a.empl_rcd
AND a1.effdt <= sysdate)
AND a.effseq = (
SELECT MAX(a2.effseq )
FROM ps_job a2
WHERE a2.emplid = a.emplid
AND a2.empl_rcd = a.empl_rcd
AND a2.effdt = a.effdt )
AND a.emplid = c.emplid
AND c.phone_type = 'WORK'
AND b.name_type = 'PRI'
AND b.effdt = (
SELECT MAX(b_ed.effdt)
FROM ps_names b_ED
WHERE b_ed.emplid = b.emplid
AND b_ed.name_type = b.name_type
AND b_ed.effdt <= sysdate)
AND a.position_nbr = d.position_nbr
AND d.eff_status = 'A'
AND d.effdt = (
SELECT MAX(d_ed.effdt)
FROM ps_position_data d_ED
WHERE d_ed.position_nbr = d.position_nbr
AND d_ed.eff_status = d.eff_status
AND d_ed.effdt <= sysdate)
AND A.POSITION_NBR <> A.REPORTS_TO