col inst for a30
col units for a8
col campus for a6
col end_date for a10
col enrl for 9999
col missing for 9999999

compute count of crn on campus
compute sum of missing on campus
compute sum of missing on report
break on campus skip 1 on report

accept p_term prompt 'Please enter the Term Code: '

select acs.crn_key crn,
  acs.coll_code coll,
  acs.subj_code subj,
  acs.crse_number crs,
  acs.seq_number_key sec,
  acs.camp_code campus,
  acs.primary_instructor_last_name||', '||acs.primary_instructor_first_name inst,
  rtrim(acs.credit_hours_low||' '||acs.credit_hours_ind||' '||acs.credit_hours_high) units,
  to_char(ssbsect_ptrm_end_date, 'MM/DD/YYYY') end_date,
  acs.actual_enrollment enrl,
  count(*) missing
from baninst1.as_catalog_schedule acs,
  ssbsect,
  sfrstcr,
  stvrsts
where acs.term_code_key='&p_term'
and acs.ssts_code<>'C'
and acs.crn_key=ssbsect_crn
and ssbsect_term_code=acs.term_code_key
and ssbsect_gradable_ind='Y'
and trunc(sysdate)>=trunc(ssbsect_ptrm_end_date)
and acs.crn_key=sfrstcr_crn
and sfrstcr_term_code=acs.term_code_key
and sfrstcr_rsts_code=stvrsts_code
and stvrsts_incl_sect_enrl='Y'
and stvrsts_gradable_ind='Y'
and nvl(lmu_f_get_final_grade(sfrstcr_pidm, sfrstcr_crn, sfrstcr_term_code), 'NR') in ('NR', 'NG')
group by acs.crn_key,
  acs.coll_code,
  acs.subj_code,
  acs.crse_number,
  acs.seq_number_key,
  acs.camp_code,
  acs.primary_instructor_last_name||', '||acs.primary_instructor_first_name,
  rtrim(acs.credit_hours_low||' '||acs.credit_hours_ind||' '||acs.credit_hours_high),
  to_char(ssbsect_ptrm_end_date, 'MM/DD/YYYY'),
  acs.actual_enrollment
order by campus,
  coll,
  upper(acs.primary_instructor_last_name||', '||acs.primary_instructor_first_name),
  subj,
  crs,
  sec
/

clear breaks
clear computes