To satisfy INC1581454.

faculty_roles.sql

/*
I am preparing a communication to send out to graduate faculty in the following colleges: 
CEHD (06GRD, 06DMS) 
CFANS (30GRD) 
CVM (03GRD) 
CSOM (02GRD) 

Would it be possible to query the Faculty Role List database to get a list of graduate faculty from the above colleges including
active faculty, name, program (college), and email address?
*/

with acad_plan_data as (
  select 
    acad_plan, 
    acad_plan_type, 
    acad_prog,
    descr 
  from 
    asr_warehouse_esup.ps_acad_plan_tbl 
  where 
    acad_prog in ('06GRD','06DMS','30GRD','03GRD','02GRD')
), all_rows as (
select /*csv*/
  acad_plan_data.acad_prog,
  responsibilities.acad_plan,
  acad_plan_data.acad_plan_type,
  acad_plan_data.descr,
  responsibilities.emplid,
  names.name,
  email.email_addr,
  roles.name role_name
from 
  responsibilities 
inner join acad_plan_data
  on responsibilities.acad_plan = acad_plan_data.acad_plan
inner join responsibility_roles rr
  on rr.responsibility_id = responsibilities.id
inner join roles on
  rr.role_id = roles.id
inner join asr_warehouse_esup.preferred_names names
  on responsibilities.emplid = names.emplid
inner join asr_warehouse_esup.cs_ps_email_addresses email
  on responsibilities.emplid = email.emplid
  and email.e_addr_type = 'UNIV'
where
  responsibilities.is_deleted != 1
  and 
  (
    responsibilities.expires_on is null 
    or
    responsibilities.expires_on > sysdate
  )
order by 
  acad_prog,
  acad_plan,
  acad_plan_type,
  name
)
select distinct acad_prog, acad_plan, emplid, name, email_addr from all_rows
order by acad_prog, acad_plan, name
;