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,
nvl((select distinct 'Y'
from tmp_drew_cw_parent_access
where tzvacum_pidm = banner_pidm),'N')
set_up_cw_parent_access,
(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;
|