FHDA Script FA Migrate Non PIDM Tables

FHDA Script FA Migrate non PIDM Tables

Prepared by Ken Whitelaw, SunGardHE, 18-AUG-2009

Initial script, needs constant checking and possible revision as new

releases come out and changes made to FA implementation.

1. ROBINST constrained Parents.

--FK1_ROBINST_INV_RTVAPRD_CODE

insert into RTVAPRD select * from RTVAPRD@TEST8_DLINK

where upper(RTVAPRD_DESC) NOT LIKE '%DO NOT USE%';

--FK1_ROBINST_INV_RTVBGRP_CODE

insert into RTVBGRP select * from RTVBGRP@TEST8_DLINK

where upper(RTVBGRP_DESC) NOT LIKE '%DO NOT USE%';

--FK1_ROBINST_INV_RTVPGRP_CODE

insert into RTVPGRP select * from RTVPGRP@TEST8_DLINK

where upper(RTVPGRP_DESC) NOT LIKE '%DO NOT USE%';

--FK1_ROBINST_INV_RTVSAPR_CODE

insert into RTVSAPR select * from RTVSAPR@TEST8_DLINK

where upper(RTVSAPR_DESC) NOT LIKE '%DO NOT USE%';

--FK1_ROBINST_INV_RTVTGRP_CODE

insert into RTVTGRP select * from RTVTGRP@TEST8_DLINK

where upper(RTVTGRP_DESC) NOT LIKE '%DO NOT USE%';

--FK1_ROBINST_INV_RTVTREQ_CODE

insert into RTVTREQ select * from RTVTREQ@TEST8_DLINK

where upper(RTVTREQ_SHORT_DESC) NOT LIKE '%DO NOT USE%'

and upper(RTVTREQ_LONG_DESC) NOT LIKE '%DO NOT USE%';

--FK1_ROBINST_INV_RTVTRST_CODE

insert into RTVTRST select * from RTVTRST@TEST8_DLINK

where upper(RTVTRST_DESC) NOT LIKE '%DO NOT USE%';

--FK1_ROBINST_INV_RTVINFC_CODE

insert into RTVINFC select * from RTVINFC@TEST8_DLINK

where upper(RTVINFC_DESC) NOT LIKE '%DO NOT USE%';

--I prefer to migrate only the RTVINFC values that are required for use –

--EDE, FAH, ISIR, MANUAL, SIM, and TRM. If you use the CSS Profile, then 'CSS'.

--FK1_ROBINST_INV_STVSBGI_CODE (Not Necessary)

--FK1_ROBINST_INV_STVTERM_CODE (Not Necessary)

--FK2_ROBINST_INV_RTVSAPR_CODE (Duplicate)

--FK2_ROBINST_INV_RTVTREQ_CODE (Duplicate)

--FK2_ROBINST_INV_RTVTRST_CODE (Duplicate)

--FK3_ROBINST_INV_RTVTREQ_CODE (Duplicate)

--FK4_ROBINST_INV_RTVTREQ_CODE (Duplicate)

2. ROBINST aid years.

insert into ROBINST select * from ROBINST@TEST8_DLINK;

3. System Required Tables.

System required tables are listed in "Schedule B — Delivered System Required Tables Reference" from the Banner Financial Aid New Aid Year Start-Up Checklist (attached). Many reference the ROBINST_AIDY_CODE, and must be migrated after the appropriate aid years exist in ROBINST. I recommended retaining one year in addition to the current year. It is not necessary to migrate other AIDY_CODE specific records in the system tables.

--RFRFFID Federal Fund ID Table RFIFFID NO

insert into RFRFFID select * from RFRFFID@TEST8_DLINK;

--RNRGLBL INAS Global Policy Options Table RNRGLBL YES

insert into RNRGLBL select * from RNRGLBL@TEST8_DLINK;

--ROBLOGC Logging Control Base Table ROALOGC NO

insert into ROBLOGC select * from ROBLOGC@TEST8_DLINK;

--The RORLOGC table is missing from the list...

insert into RORLOGC select * from RORLOGC@TEST8_DLINK;

--RORDATA Data Dictionary Table RORDATA YES

insert into RORDATA select * from RORDATA@TEST8_DLINK;

--RORDSUP Supplemental Log Data RORDSUP NO

insert into RORDSUP select * from RORDSUP@TEST8_DLINK;

--RORDVAL Audit Log Form/Data Code Table [NONE] NO

insert into RORDVAL select * from RORDVAL@TEST8_DLINK;

--RORPELL Pell Grant Payment Schedule Table [NONE] YES

insert into RORPELL select * from RORPELL@TEST8_DLINK;

--RPRFEDR Federal Rules Table RPIFEDR YES

insert into RPRFEDR select * from RPRFEDR@TEST8_DLINK;

--RTVDLBT Batch Type Validation Table [NONE] YES

insert into RTVDLBT select * from RTVDLBT@TEST8_DLINK;

--RTVICMT ISIR Comment Code Text Validation Table RTVICMT YES

insert into RTVICMT select * from RTVICMT@TEST8_DLINK;

--RTVINFC Data Interface Validation Table RTVINFC NO

--DONE ABOVE STEP 1

--RTVPHAS CommonLine Loan Application Phase Validation Table [NONE] YES

insert into RTVPHAS select * from RTVPHAS@TEST8_DLINK;

--RTVRJCT Rejection Code Validation Table RTVRJCT NO

insert into RTVRJCT select * from RTVRJCT@TEST8_DLINK;

--RTVPTYP Posting Code Validation Table RTVPTYP NO

insert into RTVPTYP select * from RTVPTYP@TEST8_DLINK;

--RTVYICD Year in College Description Table [NONE] NO

insert into RTVYICD select * from RTVYICD@TEST8_DLINK;

--RTVCDNT COD Nation Table RTVCDNT NO

insert into RTVCDNT select * from RTVCDNT@TEST8_DLINK;

--RTVCDST COD State Table RTVCDST NO

insert into RTVCDST select * from RTVCDST@TEST8_DLINK;

--RTVPRCD NSLDS Program Code Validation Table [NONE] NO

insert into RTVPRCD select * from RTVPRCD@TEST8_DLINK;

--RORMVAL Miscellaneous Validation Rules Table ROIMVAL NO

insert into RORMVAL select * from RORMVAL@TEST8_DLINK;

--RPRCIPC Financial Aid CIP Code Rules Table RPICIPC NO

insert into RPRCIPC select * from RPRCIPC@TEST8_DLINK;

--RPRSAHP State Advanced/Honors Program Codes Table RPISAHP NO

insert into RPRSAHP select * from RPRSAHP@TEST8_DLINK;

--RORLEXM Local Exit Mapping Table [NONE] NO

insert into RORLEXM select * from RORLEXM@TEST8_DLINK;

--RURVERS WAS NOT SCRUBBED

4. System Required Validation Tables

Listed in "Schedule A — System Validation Forms Reference" in the Banner Financial Aid New Aid Year Start-Up Checklist.

--* RTVAATP – NEW – Athletic Aid Type Validation Form

insert into RTVAATP select * from RTVAATP@TEST8_DLINK;

--* RTVALGR – NEW – Algorithmic Packaging Rule Code Validation Form

insert into RTVALGR select * from RTVALGR@TEST8_DLINK;

--* RTVAPRD Aid Period Validation Form Required

--DONE ABOVE STEP 1

--* RTVARSC Resource Code Validation Form Required for Other Resource Maintenance

insert into RTVARSC select * from RTVARSC@TEST8_DLINK;

--* RTVAUST Authorization Validation Form Required for Student Employment Module

insert into RTVAUST select * from RTVAUST@TEST8_DLINK;

--* RTVAWST Award Status Validation Form Required

insert into RTVAWST select * from RTVAWST@TEST8_DLINK

where upper(RTVAWST_DESC) NOT LIKE '%DO NOT USE%';

--* RTVBGRP Budget Group Validation Form Required

--DONE ABOVE STEP 1

--* RTVBTYP Budget Type Validation Form Required

insert into RTVBTYP select * from RTVBTYP@TEST8_DLINK;

--* RTVCINT Certification Initials Validation Form Required for Loan Module

insert into RTVCINT select * from RTVCINT@TEST8_DLINK

where upper(RTVCINT_LAST_NAME) NOT LIKE '%DO NOT USE%';

--* RTVCOMP Budget Component Validation Form Required

insert into RTVCOMP select * from RTVCOMP@TEST8_DLINK

where upper(RTVCOMP_DESC) NOT LIKE '%DO NOT USE%';

--*RTVFASP – Financial Aid Sport Validation Form

Insert into RTVFASP select * from RTVFASP@TEST8_DLINK;

--*RTVFCAT – NEW - Fund Category Validation Form

Insert into RTVFCAT select * from RTVFCAT@TEST8_DLINK;

--* RTVFSRC Fund Source Validation Form Required

insert into RTVFSRC select * from RTVFSRC@TEST8_DLINK

where upper(RTVFSRC_DESC) NOT LIKE '%DO NOT USE%';

--* RTVFTYP Fund Type Validation Form Required

insert into RTVFTYP select * from RTVFTYP@TEST8_DLINK

where upper(RTVFTYP_DESC) NOT LIKE '%DO NOT USE%';

--* RTVHOLD Hold Validation Form Required for Hold Functionality

insert into RTVHOLD select * from RTVHOLD@TEST8_DLINK

where upper(RTVHOLD_DESC) NOT LIKE '%DO NOT USE%';

--* RTVINFC Data Interface Code Validation Form System Required and Delivered with Values

--DONE ABOVE STEP 1

--* RTVLNST Loan Status Validation Form Required for Loan Module

insert into RTVLNST select * from RTVLNST@TEST8_DLINK;

--* RTVMESG Message Code Validation Form Required for Messages in Letter Generation

insert into RTVMESG select * from RTVMESG@TEST8_DLINK

where upper(RTVMESG_MESG_DESC) NOT LIKE '%DO NOT USE%';

--* RTVPGRP Package Group Validation Form Required for Automated Packaging

--DONE ABOVE STEP 1

--* RTVPTYP Batch Posting Type Validation Form System Required and Delivered with Values

--DONE ABOVE STEP 3

--* RTVRFST Referral Source Validation Form Required for Student Employment Module

insert into RTVRFST select * from RTVRFST@TEST8_DLINK;

--* RTVRJCT Rejection Code Validation Form System Required and Delivered with Values

--DONE ABOVE STEP 3

--* RTVRPSR Repayment Source Validation Form Required for Short-Term Credit Module

insert into RTVRPSR select * from RTVRPSR@TEST8_DLINK;

--* RTVSAPR Satisfactory Academic Progress Form Required

--DONE ABOVE STEP 1

--* RTVTGRP Tracking Group Validation Form Required

--DONE ABOVE STEP 1

--* RTVTREQ Tracking Requirements Validation Form Required

--DONE ABOVE STEP 1

--* RTVTRST Tracking Status Validation Form Required

--DONE ABOVE STEP 1

--*ROAUSIO – NEW – U.S.-Specific Institutional Financial Aid Options Form

--Insert into ROAUSIO select * from ROAUSIO@TEST8_DLINK;

--* RORUSER User-Defined Variables Description Table

insert into RORUSER select * from RORUSER@TEST8_DLINK;

--* RORNYVD Non Year User-Defined Variables Description Table

insert into RORNYVD select * from RORNYVD@TEST8_DLINK;

--* RPRDEGR Financial Aid Degree Rules Form

insert into RPRDEGR select * from RPRDEGR@TEST8_DLINK;

--*RPRMAJR – NEW – Financial Aid Major Rules Form

Insert into RPRMAJR select * from RPRMAJR@TEST8_DLINK;

--* RTVCCOM Comment Category Code Validation Table

insert into RTVCCOM select * from RTVCCOM@TEST8_DLINK;

5. Other Settings.

To be as comprehensive as possible, step through all tables in the FAISMGR schema. Ignore any table that contains a PIDM, has been previously migrated, or contains no rows and consider it for migration. There is only one table that I am aware of that DOESN'T have a PIDM, that ISN'T a settings table, ROALOGD. (ROALOGM is similar, but it does have a PIDM field, although not a key value.) In otherwords, DON'T migrate ROALOGD, or ROALOGM. Do not migrate any "INSTALL" table, and do not migrate the Versions Table RURVERS (And RURVERS should NEVER be scrubbed).

--THIS TABLE IS OBSOLETE FOR 7.9 on

--* RTVUSER User Defined Data Validation Form Required for User-Defined Fields

--insert into RTVUSER select * from RTVUSER@TEST8_DLINK;

--RORAPEL Alternate Pell Grant Payment Schedule Table [NONE] YES

insert into RORAPEL select * from RORAPEL@TEST8_DLINK;

insert into RORCAMP select * from RORCAMP@TEST8_DLINK;

--Note: Values needed in STVCAMP – HAS CAMPUS CODES SO WILL COME ACROSS

insert into RORCRHR select * from RORCRHR@TEST8_DLINK;

insert into RORCLVE select * from RORCLVE@TEST8_DLINK;

insert into RORWEBA select * from RORWEBA@TEST8_DLINK;

insert into RBRGMSG select * from RBRGMSG@TEST8_DLINK;

insert into RRRTMSG select * from RRRTMSG@TEST8_DLINK

where rrrtmsg_mesg_code in (SELECT RTVMESG_CODE FROM RTVMESG)

and rrrtmsg_treq_code in (SELECT RTVTREQ_CODE FROM RTVTREQ);

insert into RFRBASE select * from RFRBASE@TEST8_DLINK

where upper(RFRBASE_FUND_TITLE) NOT LIKE '%DO NOT USE%';

insert into RPRCLSS select * from RPRCLSS@TEST8_DLINK;

--Note: Values needed in STVLEVL – NOTHING IN RPRCLSS

insert into RPRDATE select * from RPRDATE@TEST8_DLINK;

insert into RBRGTYP select * from RBRGTYP@TEST8_DLINK

WHERE RBRGTYP_BGRP_CODE IN (SELECT RTVBGRP_CODE FROM RTVBGRP);

insert into RBRCOMP select * from RBRCOMP@TEST8_DLINK

where RBRCOMP_COMP_CODE IN (SELECT RTVCOMP_CODE FROM RTVCOMP)

and RBRCOMP_APRD_CODE IN (SELECT RTVAPRD_CODE FROM RTVAPRD);

insert into RRRGREQ select * from RRRGREQ@TEST8_DLINK

where RRRGREQ_TREQ_CODE IN (SELECT RTVTREQ_CODE FROM RTVTREQ);

insert into RCRTPTR select * from RCRTPTR@TEST8_DLINK

WHERE RCRTPTR_INFC_CODE IN (SELECT RTVINFC_CODE FROM RTVINFC);

insert into RORTPRD select * from RORTPRD@TEST8_DLINK

where rortprd_aprd_code in (select rtvaprd_code from rtvaprd);

insert into RFRDEFA select * from RFRDEFA@TEST8_DLINK

where RFRDEFA_APRD_CODE IN (SELECT RTVAPRD_CODE FROM RTVAPRD);

insert into RFRDEFD select * from RFRDEFD@TEST8_DLINK

where RFRDEFD_APRD_CODE IN (SELECT RTVAPRD_CODE FROM RTVAPRD);

insert into RFRDSCH select * from RFRDSCH@TEST8_DLINK;

insert into RFRASPC select * from RFRASPC@TEST8_DLINK

where rfraspc_fund_code in (select rfrbase_fund_code from rfrbase)

and RFRASPC_DECLINE_AWST_CODE in (select rtvawst_code from rtvawst);

insert into RFRDLCK select * from RFRDLCK@TEST8_DLINK

where RFRDLCK_AIDY_CODE||RFRDLCK_FUND_CODE in

(select RFRASPC_AIDY_CODE||RFRASPC_FUND_CODE from RFRASPC);

insert into RFRASCH select * from RFRASCH@TEST8_DLINK;

insert into RFRCOMM select * from RFRCOMM@TEST8_DLINK;

insert into RPRLDSB select * from RPRLDSB@TEST8_DLINK;

insert into RFRTREQ select * from RFRTREQ@TEST8_DLINK

where RFRTREQ_AIDY_CODE||RFRTREQ_FUND_CODE in

(select RFRASPC_AIDY_CODE||RFRASPC_FUND_CODE from RFRASPC);

insert into RFRMESG select * from RFRMESG@TEST8_DLINK

where RFRMESG_AIDY_CODE||RFRMESG_FUND_CODE in

(select RFRASPC_AIDY_CODE||RFRASPC_FUND_CODE from RFRASPC);

insert into RPBLPRD select * from RPBLPRD@TEST8_DLINK;

insert into RPRLPAP select * from RPRLPAP@TEST8_DLINK;

insert into RPBOPTS select * from RPBOPTS@TEST8_DLINK;

insert into RPRGEQY select * from RPRGEQY@TEST8_DLINK

where rprgeqy_pgrp_code in (select rtvpgrp_code from rtvpgrp);

insert into RPRGFND select * from RPRGFND@TEST8_DLINK

where rprgfnd_pgrp_code in (select rtvpgrp_code from rtvpgrp);

insert into RPREXPT select * from RPREXPT@TEST8_DLINK;

insert into RORAUDT select * from RORAUDT@TEST8_DLINK;

insert into RORSTRF select * from RORSTRF@TEST8_DLINK;

insert into RCRAIDY select * from RCRAIDY@TEST8_DLINK;

insert into RCRTERM select * from RCRTERM@TEST8_DLINK;

insert into RFRBCMP select * from RFRBCMP@TEST8_DLINK;

insert into RFRDETC select * from RFRDETC@TEST8_DLINK;

insert into RORCOLS select * from RORCOLS@TEST8_DLINK;

insert into RORINID select * from RORINID@TEST8_DLINK;

insert into RPBSTAB select * from RPBSTAB@TEST8_DLINK;

insert into RPRCLVL select * from RPRCLVL@TEST8_DLINK;

insert into RPRDEFE select * from RPRDEFE@TEST8_DLINK;

insert into RPRDEFG select * from RPRDEFG@TEST8_DLINK;

insert into RPRDEFS select * from RPRDEFS@TEST8_DLINK;

insert into RPRFSRC select * from RPRFSRC@TEST8_DLINK;

-- May not be moved until RTVTREQ, RTVTRST (SHOULD BE DONE IN STEP 1) & Population Selections have been moved. Check rorpost_query and make sure the value exist in GLBSLCT (popsel).

insert into RORPOST select * from RORPOST@TEST8_DLINK;

--if all pop-sels in RORPOST have an entry in GLBSLCT then the following

--should return zero rows

/*

Select * from rorpost

where not exists

(select 'x'

from glbslct

where rorpost_query = glbslct_selection

and glbslct_application = 'FINAID'

)

*/

insert into RJBJOBT select * from RJBJOBT@TEST8_DLINK;

insert into RJBPLBD select * from RJBPLBD@TEST8_DLINK;

insert into RJBSEDR select * from RJBSEDR@TEST8_DLINK;

insert into RJRPAYL select * from RJRPAYL@TEST8_DLINK;

insert into RJRPLRL select * from RJRPLRL@TEST8_DLINK;

Insert into RPRFUND select * from RPRFUND@TEST8_DLINK;

Insert into RPRGGAP select * from RPRGGAP@TEST8_DLINK;

Insert into RPRGSHP select * from RPRGSHP@TEST8_DLINK;

Insert into RPRLTRM select * from RPRLTRM@TEST8_DLINK;

insert into RORCODI select * from RORCODI@TEST8_DLINK;

insert into RCRINFR select * from RCRINFR@TEST8_DLINK;

insert into RTVELTM select * from RTVELTM@TEST8_DLINK;

insert into RPBLNDR select * from RPBLNDR@TEST8_DLINK;

insert into RORWEBR select * from RORWEBR@TEST8_DLINK;

insert into ROBALOG select * from ROBALOG@TEST8_DLINK;

insert into RORSLOG select * from RORSLOG@TEST8_DLINK;

--added Aug 18

insert into RTVWTXT select * from RTVWTXT@TEST8_DLINK;

insert into RTVWEBQ select * from RTVWEBQ@TEST8_DLINK;

insert into RTVINTL select * from RTVINTL@TEST8_DLINK;

insert into RPRLNDP select * from RPRLNDP@TEST8_DLINK;

insert into RORWTXT select * from RORWTXT@TEST8_DLINK;

insert into RORWTAB select * from RORWTAB@TEST8_DLINK;

insert into RORWSQL select * from RORWSQL@TEST8_DLINK;

insert into RORWEBQ select * from RORWEBQ@TEST8_DLINK;

insert into RORELTR select * from RORELTR@TEST8_DLINK;

insert into RORELTF select * from RORELTF@TEST8_DLINK;

insert into RERCODD select * from RERCODD@TEST8_DLINK;

insert into REBRDRQ select * from REBRDRQ@TEST8_DLINK;

insert into REBDRCT select * from REBDRCT@TEST8_DLINK;

------

--RORRULEs THESE ARE SPECIAL!!!!

--migrate RORGDAT

insert into RORGDAT select * from RORGDAT@TEST8_DLINK;

--migrate RORGSQL

insert into RORGSQL select * from RORGSQL@TEST8_DLINK;

------

*** DON’T MIGRATE

RORCMPL move (the compiled RORRULE table)

RORCMPL must be moved by hand... DBLinks does not support the LONG datatype.

Use Export/Import

BEGIN Sample RORCMPL exp/imp process

Can do export/import to SQL insert statements with SQLDEV.

OR …..

First export the RORCMPL table from TEST...

TEST> exp faismgr/u_pick_it

Export: Release 9.2.0.5.0 - PPRDuction on Fri Aug 26 13:40:41 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - PPRDuction

With the Partitioning option

JServer Release 9.2.0.5.0 - PPRDuction

Enter array fetch buffer size: 4096 > <return>

Export file: EXPDAT.DMP > rorcmpl_test.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes <return>

Compress extents (yes/no): yes <return>

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > rorcmpl

. . exporting table RORCMPL 138 rows exported

EXP-00091: Exporting questionable statistics.

Table(T) or Partition(T:P) to be exported: (RETURN to quit) <return>

Export terminated successfully with warnings.

Next change to point to the PPRD database...

TEST> PPRD

%DCL-I-SUPERSEDE, previous value of ORA_DFLT_HOSTSTR has been superseded