When we reconverted academic history in June, we did not re-delete the SHAPCMP rows for the summary hours that were initially loaded, and where real transfer work was now created. We also need to create SHRTGPA records for the appropriate transfer attendance periods/institutions.

delete from shrtgpa
where shrtgpa_gpa_type_ind = 'T'
and shrtgpa_term_code = '000000'
and shrtgpa_pidm in (select shrtrce_pidm from shrtrce);

commit;

insert into shrtgpa
select shrtram_pidm,
       shrtram_term_code_entered,
       shrtram_levl_code,
       'T',
       shrtram_trit_seq_no,
       shrtram_seq_no,
       nvl((select sum(shrtrce_credit_hours)
        from shrtrce
        where shrtrce_pidm = shrtram_pidm
        and shrtrce_levl_code = shrtram_levl_code
        and shrtrce_trit_seq_no = shrtram_trit_seq_no
        and shrtrce_tram_seq_no = shrtram_seq_no),0),
       nvl((select sum(shrtrce_credit_hours)
        from shrtrce
        where shrtrce_pidm = shrtram_pidm
        and shrtrce_levl_code = shrtram_levl_code
        and shrtrce_trit_seq_no = shrtram_trit_seq_no
        and shrtrce_tram_seq_no = shrtram_seq_no),0),
       0,
       0,
       0,
       sysdate,
       nvl((select sum(shrtrce_credit_hours)
        from shrtrce
        where shrtrce_pidm = shrtram_pidm
        and shrtrce_levl_code = shrtram_levl_code
        and shrtrce_trit_seq_no = shrtram_trit_seq_no
        and shrtrce_tram_seq_no = shrtram_seq_no),0)
from shrtram
where not exists (select 1 from shrtgpa
                  where shrtram_pidm = shrtgpa_pidm
                  and shrtram_term_code_entered = shrtgpa_term_code
                  and shrtram_levl_code = shrtgpa_levl_code
                  and shrtgpa_gpa_type_ind = 'T'
                  and shrtram_trit_seq_no = shrtgpa_trit_seq_no
                  and shrtram_seq_no = shrtgpa_tram_seq_no);

commit;
  • No labels