Migration – Conversion Steps

Conversion Support Week – August 18- 20, 2009

Source Instance – TEST8

Target Instance – CONV8

Scrub and Migration - Prepare Target Instance

Goal – target instance non-pidm tables to look like source instance non-pidm tables.

  • Create dblink in target instance pointing to source instance
  • Create count table FA_TABLE_COUNTS in the target instance
  • For all the count related steps use scripts fromSCRIPT FA Table Counts.txt – remember to change @dblink_name throughout script to the dblink name
  • Create counts for source tables
  • Create counts for target tables – pre-scrub
  • Scrub - Using Gen_Delete_FAISMGR_ALL_Tables.sql delete all FAISMGR “R” tables except RURVERS in target instance.
  • Run “DELETE” statement generator script
  • Run DELETE statements
  • Run until no errors
  • Create counts for pre-migration to make sure all financial aid tables in target instance have zero rows, except for RURVERS
  • Run migration scriptFHDA SCRIPT FA Migrate non PIDM Tables.doc to populate non-PIDM target tables from source
  • remember to change @dblink_name throughout script to the dblink name
  • Check to make sure all the Schedule B tables for the New Year Roll 10-11 are in step 3 and all the Schedule A tables in step 4
  • MAKE SURE YOU FOLLOW DIRECTIONS IN UPDATING ROSSLCT SEQUENCE in the Target Instance
  • Create post migration counts
  • Run comparison SELECT statement and justify any table count differences between source and post migration counts

All tables count differences were accounted for because of “DO NOT USE” or functional folks were adding data as we speak.

Conversion

Goal – target instance pidm tables, listed below,initialized from PLUS.

RHRCOMM – RORSTAT create script

RORSAPR

RPRAWRD – RORSTAT create script

Pre Conversion Setup Steps – DONE in CONV8

1. Turn off PIN creation by setting GTVSDAX - RORSTAT CREATEPIN to "N"
2. Turn off Insert logging on ROALOGC for the Converted tables above

Conversion Test – Aug 19, 2009

Pre-requisites

  • Set up the CTOOL for RHRCOMM, RPRAWRD, RORSAPR in CONV8
  • Setup NO_API_CALL crosswalk with RHRCOMM, RPRAWRD
  • Setup crosswalks AIDY_CODE, FUND_CODE, RTVSAPR
  • Create .dat extract files and copy to correct folder

RHRCOMM – For De Anza

  • Get into SQLPLUS
  • @rhrcomm_cvt_create – drop and create rhrcomm_cvt
  • !sqlldr username/password rhrcomm_cvt.ctl
  • Loaded 18615 records
  • Checked rhrcomm_cvt to make sure everything was loaded into the right columns correctly
  • @rhrcomm_convert – N to C
  • 4105 pidm errors – checked CURCERR for job 8 for errors
  • We had unchecked the generated_id on CTL in the CTOOL
  • Check that RHRCOMM is empty – it is
  • NOTE: DISABLE RT_RHRCOMM_USERACTIVIT_INSUPD trigger in Banner 8.x so activity date isn’t wiped out with today’s date – it’ll mess up the sort on RHACOMM if we don’t
  • @rhrcomm_convert – C to I – loaded 14510 records
  • Checked RHACOMM for ID 11202004 and it looked good
  • NOTE: ENABLE RT_RHRCOMM_USERACTIVIT_INSUPD trigger in Banner 8.x
  • RUN Create_RORSTAT_records_for_RHRCOMM (use new one dated Aug 19, 2009) script and commit
  • Created 8346 rorstat records for 0809

RPRAWRD – For De Anza

  • Get into SQLPLUS
  • @rprawrd_cvt_create – drop and create rprawrd_cvt
  • !sqlldr username/password rprawrd_cvt.ctl
  • Loaded 54583 records
  • RUN script to delete fund codes we don’t want to take to Banner

For De Anza…

delete from rprawrd_cvt

where convert_fund_code in ('85001','85003','85006','85008','85009','85011','85012','85013','85015','85016','85017','85020','85021');

Deleted 9011 records

  • Checked rprawrd_cvt to make sure everything was loaded into the right columns correctly
  • @rprawrd_convert – N to C
  • 2277 pidm errors
  • Make sure all the fund codes crosswalked before proceeding
  • We deleted the 2909 that didn’t crosswalk this time around but for the PROD run we want to make sure all crosswalk correctly
  • @rprawrd_convert – C to I – loaded 40646 records
  • Checked RPAAWRD for ID 10002513 and it looked good
  • RUN Create_RORSTAT_records_for_RPRAWRD script (use the Aug 19, 2009 version) and commit
  • Created 18619 rorstat records

RHAPBAW showed funds in ASC aid year order

RHIAFSH showed funds in DESC aid year order

RHRCOMM – for Foothill

  • Get into SQLPLUS
  • Make sure using the correct rhrcomm_cvt.dat file for Foothill
  • @rhrcomm_cvt_create – drop and create rhrcomm_cvt
  • !sqlldr username/password rhrcomm_cvt.ctl
  • Loaded 7581 records
  • Checked rhrcomm_cvt to make sure everything was loaded into the right columns correctly
  • @rhrcomm_convert – N to C
  • 319 pidm errors
  • NOTE: DISABLE RT_RHRCOMM_USERACTIVIT_INSUPD trigger in Banner 8.x so activity date isn’t wiped out with today’s date – it’ll mess up the sort on RHACOMM if we don’t
  • @rhrcomm_convert – C to I – loaded 7262 records
  • NOTE: ENABLE RT_RHRCOMM_USERACTIVIT_INSUPD trigger in Banner 8.x
  • RUN Create_RORSTAT_records_for_RHRCOMM (use new one dated Aug 19, 2009) script and commit
  • Created 3361 rorstat records for 0809

RPRAWRD – For Foothill

  • Get into SQLPLUS
  • Make sure you’re using the correct rprawrd_cvt.dat for Foothill
  • @rprawrd_cvt_create – drop and create rprawrd_cvt
  • !sqlldr username/password rprawrd_cvt.ctl
  • Loaded 21710 records
  • RUN script to delete fund codes we don’t want to take to Banner

For Foothill…

delete from rprawrd_cvt

where convert_fund_code in ('85005','85007','85008','85011','85014','80999','81010','81004','81005','81006','81007','81008','81009','81010','81013','81014','81017','81019','81020','81021','81022','81024','81025','81027','81032','81035','81039','82000');

Deleted 1990 records

  • Checked rprawrd_cvt to make sure everything was loaded into the right columns correctly
  • @rprawrd_convert – N to C
  • 1478 pidm errors
  • Make sure all the fund codes crosswalked before proceeding
  • We deleted the 194 that didn’t crosswalk this time around but for the PROD run we want to make sure all crosswalk correctly
  • @rprawrd_convert – C to I – loaded 18049 records
  • RUN Create_RORSTAT_records_for_RPRAWRD script (use the Aug 19, 2009 version) and commit
  • Created 6840 rorstat records

RHAPBAW showed funds in ASC aid year order

RHIAFSH showed funds in DESC aid year order

RORSAPR – for Foothill

  • Get into SQLPLUS
  • Make sure you’re using the correct rorsapr_cvt.dat file for Foothill
  • @rorsapr_cvt_create – drop and create rorsapr_cvt
  • !sqlldr username/password rorsapr_cvt.ctl
  • Loaded 2935 records
  • Checked rorsapr_cvt to make sure everything was loaded into the right columns correctly
  • @rorsapr_convert – N to C
  • 1 pidm errors
  • @rorsapr_convert – C to I – loaded 2934 records
  • Checked RORSAPR for a couple IDs and it looked good

RORSAPR – for De Anza

  • Get into SQLPLUS
  • Make sure you’re using the correct rorsapr_cvt.dat file for Foothill
  • @rorsapr_cvt_create – drop and create rorsapr_cvt
  • !sqlldr username/password rorsapr_cvt.ctl
  • Loaded 10102 records
  • Checked rorsapr_cvt to make sure everything was loaded into the right columns correctly
  • @rorsapr_convert – N to C
  • 7 pidm errors
  • @rorsapr_convert – C to I – loaded 10095 records
  • Checked RORSAPR for a couple IDs and it looked good

Script to list those who have different SAP codes for 201021 and 201022

select

spriden_id,

spriden_last_name

from rorsapr a

inner join spriden

on spriden_pidm = rorsapr_pidm

and spriden_change_ind is null

where rorsapr_term_code = '201021'

and exists

(select 'x'

from rorsapr

where rorsapr_pidm = a.rorsapr_pidm

and rorsapr_term_code = '201022'

and rorsapr_sapr_code > a.rorsapr_sapr_code

)