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