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