Page History

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;