with degree_program as (
            select max(sub_term.sgbstdn_term_code_eff) AS "Max Term", sub_term.sgbstdn_pidm
            from sgbstdn sub_term
            where sub_term.sgbstdn_term_code_eff <= :term_code
            group by sgbstdn_pidm
),

 degree as (

    select distinct s.sgbstdn_degc_code_1 AS "Degree Code", stvdegc_desc AS "Primary Degree", s.sgbstdn_term_code_eff AS "Admit Term", s.sgbstdn_pidm AS "PIDM", sgbstdn_camp_code, sgbstdn_coll_code_1, sgbstdn_majr_code_1
    from sgbstdn s
    join degree_program on s.sgbstdn_pidm = degree_program.sgbstdn_pidm
    and sgbstdn_term_code_eff = degree_program."Max Term"
    join stvdegc on stvdegc_code = s.sgbstdn_degc_code_1
)

SELECT DISTINCT 
       CASE WHEN INSTR(creds.MAX_ENR_CAMP, pri_camp.camp) > 0 
            THEN pri_camp.camp 
            ELSE (sfrstcr_camp_code || '?') END PRIMARY,
       sfrstcr_term_code TERM,
       spriden_id ID,
       spriden_last_name LAST_NAME,
       spriden_first_name FIRST_NAME,
       pri_camp.camp DEG_CAMP,
       stvcamp_desc AS "Campus Desc",
       stvcoll_desc AS "College",
       degree. "Primary Degree",
       stvmajr_desc AS "Primary Major",
       creds.MAX_ENR_CAMP MAX_ENR_CAMP,
       tb.TERM_BALANCE,
       (SELECT SUM(DECODE(tbbdetc_type_ind, 'C', tbraccd_amount, 
                                            'P', -tbraccd_amount, 
                                                 0))
          FROM tbbdetc, tbraccd
         WHERE tbraccd_pidm = sfrstcr_pidm
           AND tbbdetc_detail_code = tbraccd_detail_code
       ) NET_BALANCE,
       (SELECT DISTINCT LISTAGG(hld, ' ; ') WITHIN GROUP (ORDER BY hld)
          FROM ( SELECT DISTINCT sprhold_pidm,
                        sprhold_hldd_code || ': ' || sprhold_reason hld
                   FROM sprhold, sfrstcr
                  WHERE sfrstcr_term_code = :term_code
                    AND sfrstcr_pidm = creds.pidm
                    AND sprhold_pidm = sfrstcr_pidm
                    AND sprhold_hldd_code IN ('AR','BR','CO','FH','AM')
                    AND NVL(sprhold_to_date, SYSDATE) >= SYSDATE )) ACTIVE_HOLDS,
       (SELECT DISTINCT LISTAGG(pp, ', ') WITHIN GROUP (ORDER BY pp)
          FROM ( SELECT DISTINCT tbbdetc_desc pp
                   FROM tbbdetc, tbraccd
                  WHERE tbraccd_term_code = :term_code
                    AND tbraccd_pidm = creds.pidm
                    AND tbraccd_detail_code = tbbdetc_detail_code
                    AND tbraccd_detail_code IN ('APDP','APSF','DPDP','DPPF','DPSF','FPPF','IPDP','IPPF','JPPF','JSDF','PPDP','PPSF','VPDP','VPSF'))) PAY_PLAN,
       ( SELECT MAX('Y')
           FROM stvterm, rrrareq_a
          WHERE stvterm_code = :term_code
            AND rrrareq_aidy_code = stvterm_fa_proc_yr
            AND rrrareq_pidm = creds.pidm
            AND rrrareq_sat_ind = 'Y'
            AND rrrareq_treq_code LIKE 'DEFER_'
            AND rrrareq_trst_code IN ('S','1') ) VA_IND,
       (SELECT DISTINCT LISTAGG(tiv, ', ') WITHIN GROUP (ORDER BY tiv)
          FROM ( SELECT DISTINCT tbbdetc_desc tiv
                   FROM tbbdetc, tbraccd
                  WHERE tbraccd_term_code = :term_code
                    AND tbraccd_pidm = creds.pidm
                    AND tbraccd_detail_code = tbbdetc_detail_code
                    AND tbraccd_detail_code IN ('APEL','ASEO','ATCH',
                                  'ATGU','ADLB','ADLG','ADLP','ADLU'))) TITLE_IV_AID,
       (SELECT DISTINCT LISTAGG(tivd, ', ') WITHIN GROUP (ORDER BY tivd)
          FROM ( SELECT DISTINCT rrrareq_treq_desc tivd
                   FROM stvterm, rrrareq_a
                  WHERE stvterm_code = :term_code
                    AND rrrareq_aidy_code = stvterm_fa_proc_yr
                    AND rrrareq_pidm = creds.pidm
                    AND rrrareq_sat_ind = 'Y'
                    AND rrrareq_treq_code IN ('TIVPNI','TIVPPY')
                    AND rrrareq_trst_code = 'A' )) TIV,
       sfrstcr_camp_code CAMPUS_VAR
  FROM spriden,
       ( SELECT sfbetrm_pidm pidm,
                sgbstdn_camp_code camp
           FROM sgbstdn, sfbetrm
          WHERE sfbetrm_term_code = :term_code
            AND sgbstdn_pidm = sfbetrm_pidm
            AND EXISTS 
                ( SELECT 'X'
                    FROM stvrsts, sfrstcr
                   WHERE sfrstcr_pidm = sfbetrm_pidm
                     AND sfrstcr_term_code = sfbetrm_term_code
                     AND stvrsts_code = sfrstcr_rsts_code
                     AND stvrsts_incl_sect_enrl = 'Y' )
            AND sgbstdn_term_code_eff = 
                ( SELECT MAX(sgbstdn_term_code_eff)
                    FROM sgbstdn
                   WHERE sgbstdn_pidm = sfbetrm_pidm
                     AND sgbstdn_term_code_eff <= sfbetrm_term_code )) pri_camp,
       ( SELECT pidm,
                creds.camp,
                creds.hrs,
                creds.mxhrs,
                LISTAGG(creds.camp, ', ') WITHIN GROUP (ORDER BY creds.camp)
                OVER (PARTITION BY pidm) MAX_ENR_CAMP
           FROM ( SELECT sfrstcr_pidm pidm,
                         sfrstcr_camp_code camp,
                         SUM(sfrstcr_credit_hr) hrs,
                         FIRST_VALUE(SUM(sfrstcr_credit_hr)) 
                            OVER (PARTITION BY sfrstcr_pidm 
                                      ORDER BY SUM(sfrstcr_credit_hr) DESC) mxhrs
                    FROM stvrsts, sfrstcr
                   WHERE sfrstcr_term_code = :term_code
                     AND stvrsts_code = sfrstcr_rsts_code
                     AND stvrsts_voice_type IN ('R','W')
                   GROUP BY sfrstcr_pidm, sfrstcr_camp_code ) creds
          WHERE creds.hrs = creds.mxhrs ) creds,
       (SELECT tbraccd_pidm,
               SUM(DECODE(tbbdetc_type_ind, 'C', tbraccd_amount, 
                                            'P', -tbraccd_amount, 
                                                 0)) TERM_BALANCE
          FROM tbbdetc, tbraccd
         WHERE tbraccd_term_code = :term_code
           AND tbbdetc_detail_code = tbraccd_detail_code
         GROUP BY tbraccd_pidm
       ) tb, stvrsts, sfrstcr
         left join degree on degree."PIDM" = sfrstcr_pidm
         left join stvcamp on stvcamp_code = degree.sgbstdn_camp_code 
         left join stvmajr on stvmajr_code = degree.sgbstdn_majr_code_1
         left join stvcoll on stvcoll_code = degree.sgbstdn_coll_code_1
 WHERE sfrstcr_term_code = :term_code
   AND sfrstcr_camp_code LIKE UPPER(:camp_code)
   AND stvrsts_code = sfrstcr_rsts_code
   AND stvrsts_voice_type IN ('R','W')
   AND sfrstcr_pidm = tb.tbraccd_pidm
   AND creds.pidm = tb.tbraccd_pidm
   AND spriden_pidm = creds.pidm
   AND pri_camp.pidm = creds.pidm
   AND spriden_change_ind IS NULL
   AND CASE WHEN :mode_flag = 'A' THEN tb.TERM_BALANCE
            WHEN :mode_flag = 'N' AND tb.TERM_BALANCE < 0 THEN tb.TERM_BALANCE
            WHEN :mode_flag = 'P' AND tb.TERM_BALANCE > 0 THEN tb.TERM_BALANCE
            WHEN :mode_flag = 'Z' AND tb.TERM_BALANCE = 0 THEN tb.TERM_BALANCE
            ELSE NULL
       END IS NOT NULL
   AND creds.mxhrs = creds.hrs
ORDER BY CASE WHEN PRIMARY = sfrstcr_camp_code THEN 1
              WHEN PRIMARY = sfrstcr_camp_code || '?' THEN 2
              ELSE 3 END ASC, 
         ABS(tb.TERM_BALANCE) DESC,
         spriden_last_name,
         spriden_first_name