Initial student cleanup - draft

create global temporary table tmp_drew_clean_students
  on commit preserve rows
  as
select distinct sgbstdn_pidm, sgbstdn_term_code_eff
from sgbstdn a
where sgbstdn_stst_code = 'AS'
and a.sgbstdn_term_code_eff = (select max(b.sgbstdn_term_code_eff)
                                   from sgbstdn b
                                   join stvterm
                                     on b.sgbstdn_term_code_eff = stvterm_code
                                   where stvterm_start_date <= sysdate
                                   and b.sgbstdn_pidm = a.sgbstdn_pidm)
and (select max(shrttrm_term_code)
     from shrttrm
     where shrttrm_pidm = sgbstdn_pidm) <= '201110'
and sgbstdn_term_code_eff <= '201110';

insert into tmp_drew_clean_students
select distinct sgbstdn_pidm, sgbstdn_term_code_eff
from sgbstdn a
where sgbstdn_stst_code = 'AS'
and a.sgbstdn_term_code_eff = (select max(b.sgbstdn_term_code_eff)
                                   from sgbstdn b
                                   join stvterm
                                     on b.sgbstdn_term_code_eff = stvterm_code
                                   where stvterm_start_date <= sysdate
                                   and b.sgbstdn_pidm = a.sgbstdn_pidm)
and sgbstdn_pidm not in (select shrttrm_pidm from shrttrm)
and sgbstdn_term_code_eff <= '201110';

commit;

select count(distinct sgbstdn_pidm) from tmp_drew_clean_students;
select * from tmp_drew_clean_students;

create global temporary table tmp_clean_sgbstdns
  on commit preserve rows
  as
select *
from sgbstdn a
where exists (select 1
              from tmp_drew_clean_students b
              where a.sgbstdn_pidm = b.sgbstdn_pidm
              and a.sgbstdn_term_code_eff = b.sgbstdn_term_code_eff);

select * from tmp_clean_sgbstdns;

update tmp_clean_sgbstdns
set sgbstdn_stst_code = 'WD',
    sgbstdn_leav_code = 'L',
    sgbstdn_leav_from_date = sysdate,
    sgbstdn_leav_to_date = '31-DEC-2099',
    sgbstdn_data_origin = 'AutoReg',
    sgbstdn_user_id = 'CLEANUP',
    sgbstdn_term_code_eff = '201210';

insert into sgbstdn
select * from tmp_clean_sgbstdns;

commit;

create global temporary table tmp_drew_clean_loa
  on commit preserve rows
  as 
select distinct sgbstdn_pidm, sgbstdn_term_code_eff
from sgbstdn a
where sgbstdn_stst_code = 'LA'
and a.sgbstdn_term_code_eff = (select max(b.sgbstdn_term_code_eff)
                                   from sgbstdn b
                                   join stvterm
                                     on b.sgbstdn_term_code_eff = stvterm_code
                                   where stvterm_start_date <= sysdate
                                   and b.sgbstdn_pidm = a.sgbstdn_pidm)
and sgbstdn_term_code_eff <= '201030'
and (sgbstdn_leav_to_date is null or sgbstdn_leav_to_date <= sysdate);

commit;

create global temporary table tmp_clean_sgbstdns_loa
  on commit preserve rows
  as
select *
from sgbstdn a
where exists (select 1 
              from tmp_drew_clean_loa b
              where a.sgbstdn_pidm = b.sgbstdn_pidm
              and a.sgbstdn_term_code_eff = b.sgbstdn_term_code_eff);
              
commit;

update tmp_clean_sgbstdns_loa
set sgbstdn_stst_code = 'WD',
    sgbstdn_leav_code = 'L',
    sgbstdn_leav_from_date = sysdate,
    sgbstdn_leav_to_date = '31-DEC-2099',
    sgbstdn_data_origin = 'AutoReg',
    sgbstdn_user_id = 'CLEANUP_LOA',
    sgbstdn_term_code_eff = '201210';
    
commit;

insert into sgbstdn
select * from tmp_clean_sgbstdns_loa;

commit;