with
-- phones sorted by pririty
phone as (select /*+ INLINE */ sprtele_pidm, sprtele_tele_code,
sprtele_phone_area, sprtele_phone_number,
row_number() over (partition by sprtele_pidm, sprtele_tele_code
order by sprtele_primary_ind,
sprtele_activity_date desc) pri
from sprtele
where sprtele_status_ind is null),
-- campus phones
cmphone as (select /*+ INLINE */ sprtele_pidm, sprtele_phone_area,
sprtele_phone_number
from phone
where sprtele_tele_code = 'CM'
and pri = 1)
select distinct spriden_id drew_id,
spriden_last_name last_name,
spriden_first_name first_name,
spriden_mi middle_name,
spbpers_pref_first_name pref_first_name,
dept_desc department,
sprtele_phone_area || sprtele_phone_number phone_number
from spriden
join gorirol
on spriden_pidm = gorirol_pidm
join cmphone
on spriden_pidm = sprtele_pidm
left outer join spbpers
on spriden_pidm = spbpers_pidm
left outer join v_drew_dir_departments
on spriden_pidm = pidm
where spriden_change_ind is null
and gorirol_role in ('EMPLOYEE', 'NON_DREW_FULL');
|