SELECT sfr.sfrstcr_term_code as term_code,
stv.stvterm_desc as term_desc,
stv.stvterm_start_date as term_start_date,
stv.stvterm_end_date as term_end_date,
ptrm.sobptrm_start_date as sobptrm_start_date,
ptrm.sobptrm_end_date as sobptrm_end_date,
sfr.sfrstcr_crn as crn,
sfr.sfrstcr_levl_code as levl_code,
sfr.sfrstcr_credit_hr as credit_hr,
sfr.sfrstcr_grde_code as grde_code,
ssb.ssbsect_subj_code as subj_code,
ssb.ssbsect_crse_numb as crse_numb,
ssb.ssbsect_seq_numb as seq_numb,
NVL(ssb.ssbsect_crse_title, scbcrse_title) as crse_title,
sfr.sfrstcr_rsts_code as rsts_code
FROM stvterm stv,
sobptrm ptrm,
scbcrse scb,
ssbsect ssb,
sfrstcr sfr
WHERE sfr.sfrstcr_pidm = ?
<sql:param value="$
\{model.pidm}
" />
AND stvterm_code = ?
<sql:param value="$
\{term.stvterm_code}
" />
AND (sfr.SFRSTCR_ERROR_FLAG \!= 'F' OR sfr.SFRSTCR_ERROR_FLAG IS NULL)
AND sfr.sfrstcr_term_code = stv.stvterm_code
AND sfr.sfrstcr_rsts_code NOT IN ('DD') \--Dont get dropped or withdrawn courses
AND sfr.sfrstcr_term_code = ptrm.sobptrm_term_code
AND sfr.sfrstcr_ptrm_code = ptrm.sobptrm_ptrm_code
AND sfr.sfrstcr_crn = ssb.ssbsect_crn
AND sfr.sfrstcr_term_code = ssb.ssbsect_term_code
AND ssb.ssbsect_subj_code = scb.scbcrse_subj_code
AND ssb.ssbsect_crse_numb = scb.scbcrse_crse_numb
AND scb.scbcrse_eff_term = (
SELECT max(scb1.scbcrse_eff_term)
FROM scbcrse scb1
WHERE scb1.scbcrse_subj_code = ssb.ssbsect_subj_code
AND scb1.scbcrse_crse_numb = ssb.ssbsect_crse_numb
AND scb1.scbcrse_eff_term <= sfr.sfrstcr_term_code
)
ORDER BY sfr.sfrstcr_term_code,
ssb.ssbsect_subj_code,
ssb.ssbsect_crse_numb
|