NameConnector Query

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');