"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"