select sgbstdn_program_1, spriden_id, spriden_last_name, spriden_first_name, spriden_mi, tzvacum_rbal_less_auth_memo, (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') per_email, (select listagg(gpbprxy_email_address, '; ') within group (order by gpbprxy_email_address) email from (select distinct gprxref_person_pidm, gpbprxy_email_address from gpbprxy join gprxref on gpbprxy_proxy_idm = gprxref_proxy_idm join gprauth on gpbprxy_proxy_idm = gprauth_proxy_idm and gprxref_person_pidm = gprauth_person_pidm where gprxref_retp_code = 'PARENT' and gprauth_page_name in ('bwskoacc.P_ViewAcctTerm', 'bwskoacc.P_ViewAcct') and gprauth_auth_ind = 'Y') pe where gprxref_person_pidm = sgbstdn_pidm) proxy_email from sgbstdn sa join sfbetrm on sgbstdn_pidm = sfbetrm_pidm join spriden on sgbstdn_pidm = spriden_pidm and spriden_change_ind is null join tzvacum a on sgbstdn_pidm = tzvacum_pidm where 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 sgbstdn_stst_code = 'AS' and sfbetrm_term_code = '201310' and tzvacum_tran_number = (select max(b.tzvacum_tran_number) from tzvacum b where a.tzvacum_pidm = b.tzvacum_pidm) and not exists (select 1 from tbraccd where tbraccd_pidm = sgbstdn_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')) and tzvacum_rbal_less_auth_memo > 0 order by sgbstdn_program_1, tzvacum_rbal_less_auth_memo desc;