with cca as (
  select distinct
  s.id as studentid,
  ec.name,
  yd.abbreviation

  from students s
  inner join cc d on d.studentid = s.id
  inner join sections e on d.sectionid = e.id
  inner join u_sections_cca ec on e.dcid = ec.sectionsdcid
  inner join section_meeting em on e.id = em.sectionid
  inner join cycle_day yd on em.cycle_day_letter = yd.letter and em.schoolid = yd.schoolid and em.year_id = yd.year_id

  where
  e.schoolid = :CCASchoolID
  and e.termid in (:TermIDs)
)

select
s.last_name,
s.first_name,
s.home_room,
ps_customfields.getstudentscf(s.id,'bus_route_pm') as bus_route_pm,
cca.abbreviation,
cca.name,
ac.description

from students s
left outer join cca cca on s.id = cca.studentid
left outer join attendance a on s.id = a.studentid and s.schoolid = a.schoolid and a.att_date = trunc(sysdate)
left outer join attendance_code ac on a.attendance_codeid = ac.id

where
s.enroll_status = 0