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