-- lower level insert into shrattr (shrattr_pidm, shrattr_term_code, shrattr_tckn_seq_no, shrattr_attr_code, shrattr_activity_date) select shrtckn_pidm, shrtckn_term_code, shrtckn_seq_no, 'LLVL', sysdate from shrtckn join shrtckl on shrtckl_pidm = shrtckn_pidm and shrtckl_term_code = shrtckn_term_code and shrtckl_tckn_seq_no = shrtckn_seq_no where shrtckl_levl_code = 'UG' and shrtckn_term_code <= '201230' and shrtckn_subj_code not like '%*' and regexp_like(shrtckn_crse_numb, '^[A-Z]*\d[A-Z]+$') and not exists (select 1 from shrattr where shrattr_pidm = shrtckn_pidm and shrattr_term_code = shrtckn_term_code and shrattr_tckn_seq_no = shrtckn_seq_no and shrattr_attr_code = 'LLVL'); -- intermediate level insert into shrattr (shrattr_pidm, shrattr_term_code, shrattr_tckn_seq_no, shrattr_attr_code, shrattr_activity_date) select shrtckn_pidm, shrtckn_term_code, shrtckn_seq_no, 'ILVL', sysdate from shrtckn join shrtckl on shrtckl_pidm = shrtckn_pidm and shrtckl_term_code = shrtckn_term_code and shrtckl_tckn_seq_no = shrtckn_seq_no where shrtckl_levl_code = 'UG' and shrtckn_term_code <= '201230' and shrtckn_subj_code not like '%*' and regexp_like(shrtckn_crse_numb, '^[A-Z]*\d\d[A-Z]+$') and not exists (select 1 from shrattr where shrattr_pidm = shrtckn_pidm and shrattr_term_code = shrtckn_term_code and shrattr_tckn_seq_no = shrtckn_seq_no and shrattr_attr_code = 'ILVL'); -- upper level insert into shrattr (shrattr_pidm, shrattr_term_code, shrattr_tckn_seq_no, shrattr_attr_code, shrattr_activity_date) select shrtckn_pidm, shrtckn_term_code, shrtckn_seq_no, 'ULVL', sysdate from shrtckn join shrtckl on shrtckl_pidm = shrtckn_pidm and shrtckl_term_code = shrtckn_term_code and shrtckl_tckn_seq_no = shrtckn_seq_no where shrtckl_levl_code = 'UG' and shrtckn_term_code <= '201230' and shrtckn_subj_code not like '%*' and regexp_like(shrtckn_crse_numb, '^[A-Z]*\d\d\d[A-Z]+$') and not exists (select 1 from shrattr where shrattr_pidm = shrtckn_pidm and shrattr_term_code = shrtckn_term_code and shrattr_tckn_seq_no = shrtckn_seq_no and shrattr_attr_code = 'ULVL'); commit;