insert into shrtatt
select distinct shrtrce_pidm shrtatt_pidm,
shrtrce_trit_seq_no shrtatt_trit_seq_no,
shrtrce_tram_seq_no shrtatt_tram_seq_no,
shrtrce_trcr_seq_no shrtatt_trcr_seq_no,
shrtrce_seq_no shrtatt_trce_seq_no,
shrattr_attr_code shrtatt_attr_code,
sysdate shrtatt_activity_date
from shrtckn
join shrattr
on shrtckn_term_code = shrattr_term_code
and shrtckn_pidm = shrattr_pidm
and shrtckn_seq_no = shrattr_tckn_seq_no
join shrtrce
on shrtrce_subj_code = shrtckn_subj_code
and shrtrce_crse_numb = shrtckn_crse_numb
join shrtram
on shrtram_trit_seq_no = shrtrce_trit_seq_no
and shrtram_seq_no = shrtrce_tram_seq_no
where shrtckn_term_code >= '201010'
and shrtram_term_code_entered >= '201010'
and shrtram_term_code_entered < '201240'
and shrattr_attr_code not in ('QUAN', 'LLVL', 'ILVL', 'ULVL')
and shrattr_attr_code not like 'DV%'
and not exists (select 1 from shrtatt
where shrtatt_pidm = shrtrce_pidm
and shrtatt_trit_seq_no = shrtrce_trit_seq_no
and shrtatt_tram_seq_no = shrtrce_tram_seq_no
and shrtatt_trcr_seq_no = shrtrce_trcr_seq_no
and shrtatt_trce_seq_no = shrtrce_seq_no
and shrtatt_attr_code = shrattr_attr_code);
commit;
|