Page History

"select sgbstdn_coll_code_1           college_code, 
       sgbstdn_program_1             program, 
       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, 
       (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"