Queries

Identify new ISIR students

Runs weekly

Hard Coded Values:
rcrapp1_aidy_code
rcrapp1_vpdi_code
gurmail_vpdi_code
gurmail_letr_code
gurmail_date_printed (Start: First day ISIR is available, End: Last day ISIR is available)

Query Name: XXYY_EFAFSA

Description: XXYY EFAFSA Query

Folder Name: CM_Financial_Aid

Type: SQL Statement

Query Statement:
select distinct(spriden_id)
from rcrapp1, spriden
where rcrapp1_aidy_code = '2122'
and rcrapp1_vpdi_code = 'LEE'
and rcrapp1_infc_code = 'EDE'
and rcrapp1_seq_no =
     (
     select min(a.rcrapp1_seq_no)
     from rcrapp1 a
     where a.rcrapp1_aidy_code = rcrapp1_aidy_code
     and a.rcrapp1_vpdi_code = rcrapp1_vpdi_code
     and a.rcrapp1_infc_code = 'EDE'
     and a.rcrapp1_pidm = rcrapp1_pidm
     and a.rcrapp1_pidm = spriden_pidm
     )
and trunc(rcrapp1_create_date) >= trunc(SYSDATE-7)
and spriden_change_ind is null
and spriden_pidm = rcrapp1_pidm
and (upper(spriden_last_name) not like '%DO NOT USE%'
and upper(spriden_last_name) not like '%DNU%')
and spriden_change_ind is null
and spriden_pidm = rcrapp1_pidm
and not exists --Remove students who received EFAFSA letter for ISIR aid year
     (
     select gurmail_pidm
     from gurmail
     where gurmail_vpdi_code = 'LEE'
     and gurmail_letr_code in ('LEE_R_EFAFSA','LEE_R_LFAFSA')
     and trunc(gurmail_date_printed) >= '01-OCT-2020'
     and trunc(gurmail_date_printed) <= 30-JUN-2022'
     and gurmail_pidm = spriden_pidm
     )