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,
       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','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 ('FPPF','IDFE','DPPF','PTMS',
                          'JTMP','VTMP','ATMP','APRM','AWFC','ACNS', 'APSF',
                          'JPPF', 'DPSF', 'IPPF', 'PPSF', '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
 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
/