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