-- build table for load
create table drewmgr.t_drew_tmp_load_csem (
  idno          number,
  csem_crn      varchar2(5),
  cmhr_crn      varchar2(5),
  spriden_id    varchar2(9),
  spriden_pidm  number(9),
  advr_pidm     number(9),
  csem_rsts     varchar2(2),
  chmr_rsts     varchar2(2)
);

-- now load this table from Steve Kass file.

-- format SPRIDEN_IDs
update drewmgr.t_drew_tmp_load_csem
set spriden_id = to_char(idno, 'FM000000000');

-- get PIDMs
update drewmgr.t_drew_tmp_load_csem l
set spriden_pidm = (select spriden_pidm from spriden s
                    where l.spriden_id = s.spriden_id
                    and s.spriden_change_ind is null);
                    
-- get advisor PIDMs
update drewmgr.t_drew_tmp_load_csem l
set advr_pidm = (select sirasgn_pidm from sirasgn s
                 where sirasgn_crn = csem_crn
                 and sirasgn_term_code = '201310'
                 and sirasgn_primary_ind = 'Y');

-- get registration status for CSEM
update drewmgr.t_drew_tmp_load_csem l
set csem_rsts = (select sfrstcr_rsts_code from sfrstcr s
                 where sfrstcr_pidm = spriden_pidm
                 and sfrstcr_crn = csem_crn
                 and sfrstcr_pidm = spriden_pidm
                 and sfrstcr_term_code = '201310');

-- get registration status for CMHR
update drewmgr.t_drew_tmp_load_csem l
set chmr_rsts = (select sfrstcr_rsts_code from sfrstcr s
                 where sfrstcr_pidm = spriden_pidm
                 and sfrstcr_crn = cmhr_crn
                 and sfrstcr_pidm = spriden_pidm
                 and sfrstcr_term_code = '201310');
    
commit;

insert into sgradvr (sgradvr_pidm, sgradvr_term_code_eff, sgradvr_advr_pidm, sgradvr_advr_code, 
                     sgradvr_prim_ind, sgradvr_activity_date)
select spriden_pidm      sgradvr_pidm,
       '201310'          sgradvr_term_code_eff,
       advr_pidm         sgradvr_advr_pidm,
       'ACAD'            sgradvr_advr_code,
       'Y'               sgradvr_prim_ind,
       sysdate           sgradvr_activity_date
from drewmgr.t_drew_tmp_load_csem
where spriden_pidm is not null
and advr_pidm is not null
and not exists (select 1 from sgradvr
                where sgradvr_pidm = spriden_pidm
                and sgradvr_advr_pidm = advr_pidm);
            
commit;

-- queries for the registrar
-- people registered and with advisor
select s.spriden_id, s.spriden_last_name, s.spriden_first_name, t.csem_crn, t.cmhr_crn, 
       t.csem_rsts, t.chmr_rsts, a.spriden_id advisor_id, a.spriden_last_name advisor_last,
       a.spriden_first_name advisor_first
from drewmgr.t_drew_tmp_load_csem t
join spriden s
  on t.spriden_pidm = s.spriden_pidm
  and s.spriden_change_ind is null
join spriden a
  on t.advr_pidm = a.spriden_pidm
  and a.spriden_change_ind is null
where csem_rsts is not null
and chmr_rsts is not null
and advr_pidm is not null
order by csem_crn, cmhr_crn, spriden_last_name;

-- people who are not registered
select s.spriden_id, s.spriden_last_name, s.spriden_first_name, t.csem_crn, t.cmhr_crn, 
       t.csem_rsts, t.chmr_rsts
from drewmgr.t_drew_tmp_load_csem t
join spriden s
  on t.spriden_pidm = s.spriden_pidm
  and s.spriden_change_ind is null
where csem_rsts is null
or chmr_rsts is null;

-- people who are missing advisor
select s.spriden_id, s.spriden_last_name, s.spriden_first_name, t.csem_crn, t.cmhr_crn, 
       t.csem_rsts, t.chmr_rsts
from drewmgr.t_drew_tmp_load_csem t
join spriden s
  on t.spriden_pidm = s.spriden_pidm
  and s.spriden_change_ind is null
where advr_pidm is null;
  • No labels