-- populations for alternate PIN process.
with
students as (select *
from sgbstdn s
join stvstst
on sgbstdn_stst_code = stvstst_code
where stvstst_reg_ind = 'Y'
and sgbstdn_term_code_eff = (select max(ms.sgbstdn_term_code_eff)
from sgbstdn ms
where ms.sgbstdn_pidm = s.sgbstdn_pidm
and ms.sgbstdn_term_code_eff <= '&term')),
programs as (select *
from sorlcur p
where sorlcur_lmod_code = 'LEARNER'
and sorlcur_cact_code = 'ACTIVE'
and sorlcur_current_cde = 'Y'
and sorlcur_term_code = (select max(mp.sorlcur_term_code)
from sorlcur mp
where mp.sorlcur_pidm = p.sorlcur_pidm
and mp.sorlcur_lmod_code = 'LEARNER'
and mp.sorlcur_term_code <= '&term'))
select *
from students
join programs
on sgbstdn_pidm = sorlcur_pidm
join stvterm
on stvterm_code = '&term'
-- those that do not already have an alternate PIN
where sgbstdn_pidm not in (select sprapin_pidm
from sprapin
where sprapin_term_code = stvterm_code)
and -- populations who get alternate PINs
( -- CLA
( stvterm_code not like '____20' and sorlcur_levl_code = 'UG' and
stvterm_code != sgbstdn_term_code_admit )
or
( stvterm_code not like '____20' and sorlcur_levl_code = 'UG' and
nvl(stvterm_housing_start_date, stvterm_start_date) <= sysdate )
or
-- Theo
( ( stvterm_code like '____10' or stvterm_code like '____20' or
stvterm_code like '____30') and
( sorlcur_levl_code = 'PM' or sorlcur_levl_code = 'TM' or
sorlcur_levl_code = 'TP') and
sorlcur_program != 'NDCRM' and
stvterm_code != sgbstdn_term_code_admit )
or
( ( stvterm_code like '____10' or stvterm_code like '____20' or
stvterm_code like '____30') and
( sorlcur_levl_code = 'PM' or sorlcur_levl_code = 'TM' or
sorlcur_levl_code = 'TP') and
sorlcur_program != 'NDCRM' and
nvl(stvterm_housing_start_date, stvterm_start_date) <= sysdate )
or
-- Graduate
( sorlcur_levl_code = 'GD' and sorlcur_program = 'PHD' )
or
( sorlcur_levl_code = 'GM' and sorlcur_program in ('MA', 'MAT', 'MFA') )
);
-- random numbers
select trunc(dbms_random.value(100000, 999999)) num from dual;
|