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;
  • No labels