Page History

select sgbstdn_coll_code_1           college_code,
       sgbstdn_program_1             program,
       sgbstdn_styp_code             student_type,
       sgbstdn_term_code_admit || ' (' ||
         stvterm_desc || ')'         admit_term,
       spriden_id                    banner_id,
       spriden_last_name             last_name,
       spriden_first_name            first_name,
       spriden_mi                    middle_name,
       sgbstdn_resd_code || ' (' ||
         stvresd_desc || ')'         residency,
       (select sum(sfrstcr_credit_hr)
        from sfrstcr
        where sfrstcr_term_code = '201310'
        and sfrstcr_rsts_code in ('RE', 'RW')
        and sfrstcr_pidm = tzvacum_pidm)
                                     fall_credits,
       (select sum(tbraccd_balance)
        from tbraccd
        where tbraccd_pidm = tzvacum_pidm)
                                     balance,
       nvl((select sum(tbrmemo_amount)
        from tbrmemo
        where tbrmemo_detail_code = 'ZEFA'
        and tbrmemo_pidm = tzvacum_pidm),0)
                                     financial_aid,
       nvl((select sum(tbrmemo_amount)
        from tbrmemo
        where tbrmemo_detail_code = 'TMCM'
        and tbrmemo_pidm = tzvacum_pidm),0)
                                     payment_plan_memo,
       nvl((select sum(tbrmemo_amount)
        from tbrmemo
        where tbrmemo_detail_code not in ('ZEFA', 'TMCM')
        and tbrmemo_pidm = tzvacum_pidm),0)
                                     other_memos,
       tzvacum_rbal_less_auth_memo   net_balance_due,
       nvl((select distinct 'Y'
            from tbraccd
            where tbraccd_pidm = tzvacum_pidm
            and tbraccd_detail_code in ('ACH', 'CAP', 'CCHK', 'CCP', 'DPP',
                                        'LBX', 'LCKB', 'PAMX', 'PC33', 'PCHK',
                                        'PCSH', 'PDIS', 'PMCV', 'PMOR',
                                        'PWIR', 'TMAC', 'TMCC', 'TMGP',
                                        'TMPP')),'N')
                                     made_payments,
       nvl((select distinct 'Y'
            from rrrareq
            where rrrareq_aidy_code = '1213'
            and rrrareq_treq_code = 'APPEAL'
            and rrrareq_pidm = tzvacum_pidm),'N')
                                     fa_appeal,
       nvl((select distinct 'Y'
            from rprawrd
            where rprawrd_aidy_code = '1213'
            and rprawrd_awst_code = 'OFRD'
            and rprawrd_pidm = tzvacum_pidm),'N')
                                     fa_awards_outstanding,
       twgbwses_last_access          last_self_service_access,
       nvl((select distinct 'Y'
            from gprxref
            where gprxref_person_pidm = tzvacum_pidm
            and gprxref_retp_code = 'PARENT'),'N')
                                     set_up_proxy,
       nvl((select distinct 'Y'
            from tmp_drew_cw_parent_access
            where tzvacum_pidm = banner_pidm),'N')
                                     set_up_cw_parent_access,
       (select listagg(goremal_email_address, '; ')
         within group (order by goremal_email_address)
        from goremal
        where goremal_pidm = sgbstdn_pidm
        and goremal_emal_code = 'DREW'
        and goremal_status_ind ='A') drew_email,
       (select listagg(goremal_email_address, '; ')
         within group (order by goremal_email_address)
        from goremal
        where goremal_pidm = sgbstdn_pidm
        and goremal_emal_code = 'PER'
        and goremal_status_ind = 'A')
                                     personal_email,
       (select listagg(sprtele_phone_area || '-' ||
                       sprtele_phone_number || ' (' ||
                       stvtele_desc || ')', '; ')
         within group (order by sprtele_seqno)
        from sprtele
        join stvtele
          on sprtele_tele_code = stvtele_code
        where sprtele_status_ind is null
        and sprtele_tele_code not in ('CM', 'ES', 'FAX', 'FXAP', 'FXBU', 'FXPO',
                                      'I2', 'MA', 'MOEM', 'PO', 'RH', 'SE')
        and tzvacum_pidm = sprtele_pidm)
                                    phones
from tzvacum a
join sfbetrm
  on tzvacum_pidm = sfbetrm_pidm
join sgbstdn sa
  on tzvacum_pidm = sgbstdn_pidm
join spriden
  on tzvacum_pidm = spriden_pidm
  and spriden_change_ind is null
left outer join stvterm
  on sgbstdn_term_code_admit = stvterm_code
left outer join stvresd
  on sgbstdn_resd_code = stvresd_code
left outer join twgbwses
  on tzvacum_pidm = twgbwses_pidm
where tzvacum_tran_number = (select max(b.tzvacum_tran_number)
                             from tzvacum b
                             where a.tzvacum_pidm = b.tzvacum_pidm)
and sgbstdn_term_code_eff = (select max(sb.sgbstdn_term_code_eff)
                             from sgbstdn sb
                             where sa.sgbstdn_pidm = sb.sgbstdn_pidm
                             and sb.sgbstdn_term_code_eff <= sfbetrm_term_code)
and tzvacum_rbal_less_auth_memo >= 1000
and sfbetrm_term_code = '201310'
and sfbetrm_tmst_code != 'WD'
order by sgbstdn_coll_code_1, sgbstdn_program_1, tzvacum_rbal_less_auth_memo desc;