set linesize 250
col inst for a25
col camp for a4

accept p_term prompt 'Please enter the Term Code: '

select acs.term_code_key term,
  acs.crn_key crn,
  acs.coll_code coll,
  acs.subj_code subj,
  acs.crse_number crs,
  acs.seq_number_key sec,
  acs.camp_code camp,
  acs.primary_instructor_last_name||', '||acs.primary_instructor_first_name instructor,
  spriden_id id,
  spriden_last_name||', '||spriden_first_name||' '||spriden_mi student,
  lmu_f_get_final_grade(sfrstcr_pidm, sfrstcr_crn, sfrstcr_term_code) grade,
  sfrstcr_gmod_code gmod,
  sfrstcr_rsts_code rsts,
  to_char(sfrstcr_rsts_date, 'MM/DD/YYYY') rdate
from baninst1.as_catalog_schedule acs,
  ssbsect,
  sfrstcr,
  stvrsts,
  spriden
where acs.term_code_key='&p_term'
and acs.ssts_code<>'C'
and acs.crn_key=ssbsect_crn
and acs.term_code_key=ssbsect_term_code
and ssbsect_gradable_ind='Y'
and acs.crn_key=sfrstcr_crn
and acs.term_code_key=sfrstcr_term_code
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')
and sfrstcr_pidm=spriden_pidm(+)
and spriden_change_ind is null
order by camp,
  coll,
  instructor,
  crn,
  student
/

set linesize 100