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