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;