insert into shrattr (shrattr_pidm, shrattr_term_code, shrattr_tckn_seq_no,
shrattr_attr_code, shrattr_activity_date)
select shrtckn_pidm shrattr_pidm,
shrtckn_term_code shrattr_term_code,
shrtckn_seq_no shrattr_tckn_seq_no,
'WRIT' shrattr_attr_code,
sysdate shrattr_activity_date
from shrtckn
where shrtckn_term_code >= '201010'
and shrtckn_subj_code = 'SPAN'
and shrtckn_crse_numb = '139X'
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 = 'WRIT');
insert into shrtatt (shrtatt_pidm, shrtatt_trit_seq_no, shrtatt_tram_seq_no,
shrtatt_trcr_seq_no, shrtatt_trce_seq_no, shrtatt_attr_code,
shrtatt_activity_date)
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,
'WRIT' shrtatt_attr_code,
sysdate shrtatt_activity_date
from shrtrce
where shrtrce_term_code_eff >= '201010'
and shrtrce_subj_code = 'SPAN'
and shrtrce_crse_numb = '139X'
and not exists (select 1 from shrtatt
where shrtrce_pidm = shrtatt_pidm
and shrtrce_trit_seq_no = shrtatt_trit_seq_no
and shrtrce_tram_seq_no = shrtatt_tram_seq_no
and shrtrce_trcr_seq_no = shrtatt_trcr_seq_no
and shrtrce_seq_no = shrtatt_trce_seq_no
and shrtatt_attr_code = 'WRIT');
commit;
|