spapin process scratch

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