Temporary table to hold Pat's spreadsheet with extra columns:
describe tmp_transfer_load Name Null Type ------------------- ---- -------------- AIMS_ID VARCHAR2(200) STU_NAME VARCHAR2(200) CLASS VARCHAR2(200) S_LEVEL VARCHAR2(200) CEEB VARCHAR2(200) CEEB_NAME VARCHAR2(2048) TITLE VARCHAR2(200) HOURS NUMBER GRADE VARCHAR2(200) OLD_SUBJ_COURSE VARCHAR2(200) NEW_AIMS_SUBJ VARCHAR2(200) NEW_AIMS_CRSE_NUMB VARCHAR2(200) ATT1 VARCHAR2(200) ATT2 VARCHAR2(200) ATT3 VARCHAR2(200) PIDM NUMBER(9) AIMS_TERM VARCHAR2(10) SBGI_CODE VARCHAR2(6) BANNER_TERM VARCHAR2(6) BANNER_GRADE VARCHAR2(6) NEW_AIMS_CRSE_NUMB2 VARCHAR2(10) SPLIT_CREDITS NUMBER
SQL to load transfer work
-- institutions
insert into shrtrit (shrtrit_pidm, shrtrit_seq_no, shrtrit_sbgi_code,
shrtrit_official_trans_ind, shrtrit_trans_date_rcvd,
shrtrit_activity_date)
select pidm shrtrit_pidm,
row_number() over (partition by pidm
order by sbgi_code) shrtrit_seq_no,
sbgi_code shrtrit_sbgi_code,
'Y' shrtrit_official_trans_ind,
sysdate shrtrit_trans_date_rcvd,
sysdate shrtrit_activity_date
from (select distinct pidm, sbgi_code
from tmp_transfer_load);
-- periods
insert into shrtram (shrtram_pidm, shrtram_trit_seq_no, shrtram_seq_no, shrtram_levl_code,
shrtram_attn_period, shrtram_term_code_entered, shrtram_acceptance_date,
shrtram_activity_date)
select shrtrit_pidm shrtram_pidm,
shrtrit_seq_no shrtram_trit_seq_no,
row_number() over (partition by pidm, sbgi_code
order by banner_term) shrtram_seq_no,
s_level shrtram_levl_code,
stvterm_desc shrtram_attn_period,
banner_term shrtram_term_code_entered,
sysdate shrtram_acceptance_date,
sysdate shrtram_activity_date
from shrtrit
join (select distinct pidm, sbgi_code, s_level, banner_term
from tmp_transfer_load) t
on pidm = shrtrit_pidm
and sbgi_code = shrtrit_sbgi_code
join stvterm
on banner_term = stvterm_code;
-- transfer institution courses
insert into shrtrcr (shrtrcr_pidm, shrtrcr_trit_seq_no, shrtrcr_tram_seq_no, shrtrcr_seq_no,
shrtrcr_trans_course_name, shrtrcr_trans_course_numbers, shrtrcr_trans_credit_hours,
shrtrcr_trans_grade, shrtrcr_trans_grade_mode, shrtrcr_levl_code,
shrtrcr_term_code, shrtrcr_tcrse_title, shrtrcr_activity_date,
shrtrcr_program)
select shrtram_pidm shrtrcr_pidm,
shrtram_trit_seq_no shrtrcr_trit_seq_no,
shrtram_seq_no shrtrcr_tram_seq_no,
row_number() over (partition by pidm, sbgi_code, s_level, banner_term
order by new_aims_subj, new_aims_crse_numb, banner_grade, hours)
shrtrcr_seq_no,
'TR' shrtrcr_trans_course_name,
null shrtrcr_trans_course_numbers,
hours shrtrcr_trans_credit_hours,
banner_grade shrtrcr_trans_grade,
'T' shrtrcr_trans_grade_mode,
s_level shrtrcr_levl_code,
banner_term shrtrcr_term_code,
substr(title, 1, 30) shrtrcr_tcrse_title,
sysdate shrtrcr_activity_date,
'.......' shrtrcr_program
from shrtram
join shrtrit
on shrtram_pidm = shrtrit_pidm
and shrtram_trit_seq_no = shrtrit_seq_no
join tmp_transfer_load
on pidm = shrtrit_pidm
and sbgi_code = shrtrit_sbgi_code
and banner_term = shrtram_term_code_entered
and s_level = shrtram_levl_code
where hours is not null;
-- equivs
-- course and subject code 1
insert into shrtrce (shrtrce_pidm, shrtrce_trit_seq_no, shrtrce_tram_seq_no,
shrtrce_seq_no, shrtrce_trcr_seq_no, shrtrce_term_code_eff,
shrtrce_levl_code, shrtrce_subj_code, shrtrce_crse_numb,
shrtrce_crse_title, shrtrce_credit_hours, shrtrce_grde_code,
shrtrce_gmod_code, shrtrce_count_in_gpa_ind, shrtrce_activity_date)
select shrtram_pidm shrtrce_pidm,
shrtram_trit_seq_no shrtrce_trit_seq_no,
shrtram_seq_no shrtrce_tram_seq_no,
1 shrtrce_seq_no,
row_number() over (partition by pidm, sbgi_code, s_level, banner_term
order by new_aims_subj, new_aims_crse_numb, banner_grade, hours)
shrtrce_trcr_seq_no,
banner_term shrtrce_term_code_eff,
s_level shrtrce_levl_code,
new_aims_subj shrtrce_subj_code,
new_aims_crse_numb shrtrce_crse_numb,
substr(title, 1, 30) shrtrce_crse_title,
nvl(split_credits, hours) shrtrce_credit_hours,
banner_grade shrtrce_grde_code,
'T' shrtrce_gmod_code,
'Y' shrtrce_count_in_gpa_ind,
sysdate shrtrcr_activity_date
from shrtram
join shrtrit
on shrtram_pidm = shrtrit_pidm
and shrtram_trit_seq_no = shrtrit_seq_no
join tmp_transfer_load
on pidm = shrtrit_pidm
and sbgi_code = shrtrit_sbgi_code
and banner_term = shrtram_term_code_entered
and s_level = shrtram_levl_code
where hours is not null;
-- course and subject code 2
insert into shrtrce (shrtrce_pidm, shrtrce_trit_seq_no, shrtrce_tram_seq_no,
shrtrce_seq_no, shrtrce_trcr_seq_no, shrtrce_term_code_eff,
shrtrce_levl_code, shrtrce_subj_code, shrtrce_crse_numb,
shrtrce_crse_title, shrtrce_credit_hours, shrtrce_grde_code,
shrtrce_gmod_code, shrtrce_count_in_gpa_ind, shrtrce_activity_date)
select shrtram_pidm shrtrce_pidm,
shrtram_trit_seq_no shrtrce_trit_seq_no,
shrtram_seq_no shrtrce_tram_seq_no,
2 shrtrce_seq_no,
row_number() over (partition by pidm, sbgi_code, s_level, banner_term
order by new_aims_subj, new_aims_crse_numb, banner_grade, hours)
shrtrce_trcr_seq_no,
banner_term shrtrce_term_code_eff,
s_level shrtrce_levl_code,
new_aims_subj shrtrce_subj_code,
new_aims_crse_numb2 shrtrce_crse_numb,
substr(title, 1, 30) shrtrce_crse_title,
nvl(split_credits, hours) shrtrce_credit_hours,
banner_grade shrtrce_grde_code,
'T' shrtrce_gmod_code,
'Y' shrtrce_count_in_gpa_ind,
sysdate shrtrcr_activity_date
from shrtram
join shrtrit
on shrtram_pidm = shrtrit_pidm
and shrtram_trit_seq_no = shrtrit_seq_no
join tmp_transfer_load
on pidm = shrtrit_pidm
and sbgi_code = shrtrit_sbgi_code
and banner_term = shrtram_term_code_entered
and s_level = shrtram_levl_code
where hours is not null
and new_aims_crse_numb2 is not null;
-- attributes 1
insert into shrtatt (shrtatt_pidm, shrtatt_trit_seq_no, shrtatt_tram_seq_no,
shrtatt_trce_seq_no, shrtatt_trcr_seq_no, shrtatt_attr_code,
shrtatt_activity_date)
select shrtram_pidm,
shrtram_trit_seq_no,
shrtram_seq_no,
1,
trcr_seq_no,
att1,
sysdate
from (
select shrtram_pidm,
shrtram_trit_seq_no,
shrtram_seq_no,
row_number() over (partition by pidm, sbgi_code, s_level, banner_term
order by new_aims_subj, new_aims_crse_numb, banner_grade, hours)
trcr_seq_no,
att1,
att2
from shrtram
join shrtrit
on shrtram_pidm = shrtrit_pidm
and shrtram_trit_seq_no = shrtrit_seq_no
join tmp_transfer_load
on pidm = shrtrit_pidm
and sbgi_code = shrtrit_sbgi_code
and banner_term = shrtram_term_code_entered
and s_level = shrtram_levl_code
where hours is not null
)
where att1 is not null;
-- attributes 2
insert into shrtatt (shrtatt_pidm, shrtatt_trit_seq_no, shrtatt_tram_seq_no,
shrtatt_trce_seq_no, shrtatt_trcr_seq_no, shrtatt_attr_code,
shrtatt_activity_date)
select shrtram_pidm,
shrtram_trit_seq_no,
shrtram_seq_no,
1,
trcr_seq_no,
att2,
sysdate
from (
select shrtram_pidm,
shrtram_trit_seq_no,
shrtram_seq_no,
row_number() over (partition by pidm, sbgi_code, s_level, banner_term
order by new_aims_subj, new_aims_crse_numb, banner_grade, hours)
trcr_seq_no,
att1,
att2
from shrtram
join shrtrit
on shrtram_pidm = shrtrit_pidm
and shrtram_trit_seq_no = shrtrit_seq_no
join tmp_transfer_load
on pidm = shrtrit_pidm
and sbgi_code = shrtrit_sbgi_code
and banner_term = shrtram_term_code_entered
and s_level = shrtram_levl_code
where hours is not null
)
where att2 is not null;
-- get rid of summary hours shown on SHAPCMP
delete from shrtgpa
where shrtgpa_term_code = '000000'
and shrtgpa_gpa_type_ind = 'T'
and shrtgpa_pidm in (select pidm from tmp_transfer_load);
commit;