Home

SCT Converter Tool User Guide

Contents

1. 2 1 SPBPERS CVT JOB ID NUMBER 8 STORAGE INITIAL IM PCTINCREASE 100 MAXEXTENTS 50 Release 3 0 B 7 SCT Converter Tool User s Guide Appendix B Sample Table Creation Scripts B 8 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix Sample SQL Loader Scripts Sample lt TargetTable gt _cvt ctl 5 1 2 3 cvt etl cote Ee ER e RUSSE ibus C 3 spraddr cyt etb iuste RR RR Ra ROI ERI C 4 Spbpers he e Ree Res C 4 C 1 Appendix Sample SQL Loader Scripts C 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix Sample SQL Loader Scripts Sample lt TargetTable gt _cvt ctl scripts SPRIDEN Identification Table spriden_cvt ctl AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This SQLLoader control file is used to load legacy data into the temporary conversion table SPRIDEN This script was generated by the SCT Converter Tool AUDIT TRAIL END Load data Infile spriden_cvt dat APPEND INTO TABLE SPRIDEN_CVT FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY TRAILING NULLCOLS CONVERT PIDM CHAR CONVERT ID CHAR CONVERT LAST NAME CHAR CONVERT FIRST NAME CHAR CONVERT MI CHAR CONVERT CHANGE IND CHAR SPRIDEN RECORD ID SEQUENCE MAX I SPRIDEN CVT STATUS CONSTANT N Notice that the contr
2. once the script has been run in Convert mode at least once explained below and these records have been successfully inserted into the lt target_table gt columns of the convert table You would choose C if you have successfully converted the data and are ready to insert it into the actual lt TargetTable gt columns E Any records that have errors are not loaded into the lt TargetTable gt columns and are marked with an E in the lt TargetTable gt _cvt_status column so they are easily identifiable for evaluation and correction After you have corrected any records that errored out on the first run of the script you can run the script again and answer the first prompt with E indicating that you wish only to process the records that were previously marked as errors on the first run You may repeat this process as many times as is necessary to correct all errors Second set of Prompts Convert Disposition Please enter the disposition to which the records should be processed C onvert records only Dnsert records only records were previously converted successfully B oth convert and insert records into lt TargetTable gt 4 8 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 4 Scripts Produced by the SCT Converter Tool October 2003 Confidential C Convert records only Answer with C if you wish the script only to populate the SCT Banner columns in the conv
3. gt RECORD ID SEQUENCE will be populated with a sequence number to identify the record and the lt TargetTable gt _CVT_STATUS will be populated with the value N to indicate that the record is N ew This value will be updated when the convert script is run in the later stages of the process Other valid values for this field during the convert process are C onverted I nserted and E rrored Notice also that the columns that are to be loaded are only the convert columns and not the lt TargetTable gt columns lt TargetTable gt columns are populated in the final step using the conversion script When you create the specifications for your conversion ofthe lt TargetTable gt the SCT Converter Tool allows you to specify the format of your flat file comma delimited or sequential fixed length and will create the appropriate type of control file depending upon your selection 4 6 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 4 Scripts Produced by the SCT Converter Tool October 2003 Confidential After generating the control file from the converter tool you will need to place the data file and the control file in the same directory on the server to load the convert table you have created Once the two files are in the same directory on the server use the following command to invoke SQL Loader sqlldr userid your_userid your_convert_user_password control lt TargetTable gt _cvt ct
4. October 2003 NEXT BLOCK CUACNVT With your cursor in the SPBPERS PIDM column field Navigate to the Length field Release 3 0 SCT Converter Tool User s Guide Confidential 3 21 Chapter 3 Conversion Example Using The SCT Converter Tool October 2003 ACTIONS STEPS FORM PROCESS NOTES amp HINTS Change the 8 to 9 no quotation marks You must change the length of the CONVERT PIDM column in the table to accommodate the legacy ID which is 9 characters long The GET PIDM function will use this ID to match against the existing SPRIDEN ID inserted previously to get the pidm and insert it into the SPBPERS PIDM field in the convert table and subsequently into the SPBPERS PIDM field in the SPBPERS table Navigate to the Load field in the Column Block and check Checking the LOAD button indicates that you want this column to the Load indicator appear in the SQL Loader script because you have legacy data in the flat file for this column to be loaded into the convert table Navigate to the Insert indicator in the Column Block and Checking the INSERT button indicates that you want this column to check the Insert indicator appear in the conversion script to be converted to the target table columns in the convert table and subsequently inserted into the actual target table Navigate to the next record the SPBPERS SSN column CUACNVT Column Ver
5. see 2 22 Operational Concepts escien aa 2 23 Convert Script for SPRIDEN EER 2 23 How It WOtkS Ree RO A 2 24 A Sample R n ned RH IR a RR SR eec 2 24 Multiple Iterations of Converting SPRIDEN sese 2 26 Getting the PIDM for Other Tables using GET PIDM 2 27 Use Generated ID s not 2 27 Use Generated ID s 2 2 2 28 2 1 Chapter 2 Elements of the SCT Converter Tool 2 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter Tool Elements of the SCT Converter Tool October 2003 Confidential Logging In If you are using the client server version of the tool double click the SCT Converter Tool icon that was created on your PC s desktop during installation If you are using the Internet Native IN version of the tool launch Internet Explorer and access the site where the IN version of the tool reside by typing it on the address window of the Internet Explorer Log in Username your_convert_username provided by your DBA Password your_convert_user password provided by your DBA Database your_convert_db desired database using correct alias in tnsnames ora file x Username your convert username Password Database your convert db Connect Cancel
6. RECORD ID NUMBER 8 CONSTRAINT PK SPRIDEN PRIMARY KEY SPRIDEN STATUS VARCHAR 2 1 SPRIDEN JOB ID NUMBER 8 STORAGE INITIAL IM PCTINCREASE 100 MAXEXTENTS 50 The convert column after each SPRIDEN column uses the naming convention that substitutes the word convert for the SPRIDEN table name 4 4 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 4 Scripts Produced by the SCT Converter Tool Also three new columns have been added The lt TargetTable gt _cvt_record_id column holds a sequence number which is assigned when the table is loaded using the SQL Loader control file produced by the Converter Tool The lt gt column holds the record status New Converted or Error assigned during the conversion process Explained in more detail in the section on the convert script The TargetTable7 job id column holds a unique number which is used in naming the log files that are created for each run of the convert script allowing each log file to remain intact for reference This script should be run in SQL on the client machine in the correct database instance to create the temporary Oracle table Caution The script includes a DROP TABLE statement The table including data is removed Run this script only if the data in the convert table in not needed Create SQL Loader Script TargetTable
7. AUTO PIDM function will generate a pidm if a pidm is not passed to the function Note that later in this process you must manually edit the spriden_convert sql script to change the order by clause in the spriden cursor statement Press the EDIT PARAMETERS button Edit Parameters Dialog Box Notice in the text of the function that there is only one parameter PIDM The left side of the parameters dialog box has the word PIDM defaulted in Enter LEGACY VALUE in the right column of the parameters dialog box LEGACY VALUE is a variable that has been defined in the convert script generated by the converter tool It refers to the legacy data value in the convert column in the convert table Any time it is used as a parameter in a function the actual value that is passed 15 the legacy data value that resides in the convert column that corresponds to the target table column to which the function 15 being applied and to which to data will be moved in the conversion process Not case sensitive Press the CONSTRUCT FUNCTION CALL button When you press the CONSTRUCT FUNCTION CALL button the blank box at the top right corner of the form is populated with the actual function call that will appear in the convert script F CVT AUTO PIDM LEGACY VALUE Press the RETURN button Choose Save amp Return Doing this will return your cursor to the Table Block of CUACNVT To resume work you must perform a NEXT BLOCK
8. CONVERT LICENSE EXPIRES DATE BEGIN spbpers rec SPBPERS LICENSE EXPIRES DATE TO DATE converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPBPERS INCAR IND BEGIN cur col SPBPERS INCAR IND legacy value spbpers rec convert incar ind CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers CONVERT INCAR IND spbpers_rec SPBPERS INCAR IND converted val EXCEPTION WHEN column error THEN col status E process status E Release 3 0 D 51 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts D 52 p err cur owner cur tbl cur_jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS WEBID BEGIN cur col SPBPERS WEBID legacy value spbpers rec convert webid CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value m converted val null converted spbpers rec CONVERT WEBID spbpers rec SPBPERS WEBID converted val EXCEPTION WHEN colu
9. NUMBER converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column_error END EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR CORRECTION DIGIT BEGIN cur col SPRADDR CORRECTION DIGIT legacy value spraddr rec convert correction digit CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val CONVERT CORRECTION DIGIT BEGIN spraddr_rec SPRADDR_ CORRECTION DIGIT NUMBER converted val y EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column ISPRADDR CARRIER ROUTE Release 3 0 D 29 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts D 30 BEGIN cur col SPRADDR_ CARRIER ROUTE legacy value spraddr rec convert carrier route CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_va
10. END Beginning evaluation of column ISPBPERS LEGAL NAME BEGIN cur col SPBPERS LEGAL legacy value spbpers rec convert legal name CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spbpers rec CONVERT LEGAL NAME spbpers_rec SPBPERS LEGAL NAME converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPBPERS PREF FIRST NAME BEGIN cur col SPBPERS PREF FIRST legacy value spbpers rec convert pref first name CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers CONVERT PREF FIRST NAME spbpers_rec SPBPERS PREF FIRST NAME converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS NAME PREFIX October 2003 Release 3 0 D 41 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts D 42 BEGIN cur col SPBPERS NAME PREFIX legacy value spbper
11. Explanation of Record Type Prompts Dealing with EtTOIS 4 ree hg bep e ep bbc 4 1 Chapter 4 Scripts Produced by the SCT Converter Tool 4 2 Release 3 0 SCT Converter Tool User s Guide October 2003 Confidential Chapter 4 Scripts Produced by the SCT Converter Tool Scripts Produced by the SCT Converter Tool CAUCNVT Create Convert Table Script TargetTable create sgql After building converstion specifications on the CUACNVT form the next step is to create an Oracle table to hold the data from the flat file the client has provided The SCT Converter Tool enables you to produce a script lt TargetTable gt _cvt_create sql that creates a convert table which contains each target table column as well as a convert column for each of the columns in the target table The convert columns are exact replicas of the target table columns in size and position but without the constraints that exist on the target table columns Convert columns are always the VARCHAR2 datatype In one of the sample conversions outlined in this document the SPRIDEN table Person Identification Table is being loaded The SPRIDEN table includes the following columns Column Data Type Constraints SPRIDEN PIDM Number 8 Not Null SPRIDEN ID Varchar2 9 Not Null SPRIDEN LAST NAME Varchar2 60 Not Null SPRIDEN FIRST NAME Varchar2 15 SPRIDEN MI Va
12. DBMS OUTPUT PUT LINE Number of Rows Inserted into spraddr rows success insert DBMS OUTPUT PUT LINE Number of Rows That Errored rows errored lt lt of program DBMS OUTPUT PUT LINE CHR 10 Completed Processing of spraddr EXCEPTION WHEN OTHERS THEN err num SQLCODE err msg SUBSTR SQLERRM 1 100 process status E p err cur owner cur tbl cur jobid cur rec cur col null null null eerr msg DBMS OUTPUT PUT LINE Number of Rows Converted in SPRADDR CVT lrows success convert DBMS OUTPUT PUT LINE Number of Rows Inserted into spraddr rows success insert DBMS OUTPUT PUT LINE Number of Rows That Errored rows errored DBMS OUTPUT PUT LINE CHR 10 Completed Processing of spraddr END SELECT CHAR sysdate DD MON Y YY Y HH24 MI STOP TIME FROM DUAL COMMIT PROMPT Querying CURCERR table for errors col msg for a35 hea ERROR MESSAGE WORD col colname for a30 hea COLUMN_NAME col jobno for 99999 hea JOB col norecs for 999999 hea COUNT set wrap on select distinct curcerr_message msg curcerr_column_name colname curcerr_cvt_identifier jobno count norecs from curcerr where curcerr table name SPRADDR group by curcerr_message curcerr_column_name curcerr cvt identifier Release 3 0 D 33 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts SPOOL OFF UNDEFINE process_level UNDEFINE records in SPBPERS General Person Biographic Demogra
13. p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END October 2003 Release 3 0 D 45 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts Beginning evaluation of column SPBPERS EYES CODE BEGIN col SPBPERS EYES CODE legacy value spbpers rec convert eyes code CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value m converted val null converted 1 spbpers rec CONVERT EYES CODE spbpers SPBPERS EYES CODE converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column SPBPERS CITY BIRTH BEGIN cur col SPBPERS CITY BIRTH legacy value spbpers rec convert city birth CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers rec CONVERT CITY BIRTH spbpers rec SPBPERS CITY BIRTH converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column ISPBPERS STAT CODE BIRTH BEGIN
14. SPRADDR CNTY CODE spraddr rec spraddr cnty code SPRADDR NATN CODE spraddr rec spraddr natn code SPRADDR PHONE AREA spraddr rec spraddr phone area SPRADDR PHONE NUMBER spraddr rec spraddr phone number SPRADDR PHONE EXT spraddr rec spraddr phone ext SPRADDR STATUS IND spraddr rec spraddr status ind SPRADDR ACTIVITY DATE SYSDATE SPRADDR USER spraddr rec spraddr user SPRADDR ASRC CODE spraddr rec spraddr asrc code SPRADDR DELIVERY POINT spraddr rec spraddr delivery point Release 3 0 D 31 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts SPRADDR CORRECTION DIGIT spraddr rec spraddr correction digit SPRADDR CARRIER ROUTE spraddr rec spraddr carrier route SPRADDR GST TAX ID spraddr rec spraddr gst tax id SPRADDR REVIEWED IND spraddr rec spraddr reviewed ind SPRADDR REVIEWED USER spraddr rec spraddr reviewed user SPRADDR CVT STATUS col status SPRADDR JOB ID cur jobid WHERE SPRADDR CVT RECORD ID cur rec IOWS success convert rows success convert 1 EXCEPTION WHEN OTHERS THEN err msg Error updating record RAISE END lt lt insert_spraddr gt gt IF process level B OR process level THEN IMEEM RR Inserting into Banner Table spraddr pv ve MIO PE BEGIN INSERT INTO SATURN spraddr SPRADDR PIDM SSPRADDR ATYP CODE SEQNO SPRADDR FROM DATE SSPRADDR TO DATE SPRADDR STREET LINEI SPRADDR CITY SSPRADD
15. err msg VARCHAR2 300 first row BOOLEAN TRUE column error EXCEPTION process level VARCHAR2 1 UPPER amp process level records in VARCHAR2 1 UPPER amp records 1n breakl value VARCHAR2 30 break2 value VARCHAR2 30 break3 value VARCHAR2 30 seq NUMBER S 0 seq2 NUMBER S 0 seq3 NUMBER S 0 CURSOR spriden_cursor IS SELECT FROM spriden WHERE spriden status records erderbs spriden evt record id Order by convert change ind desc NOTE THIS IS THE ORDER BY STATEMENT THAT MUST BE ALTERED IN ORDER FOR THEF CVT AUTO PIDM FUNCTION TO PERFORM PROPERLY BEGIN IF process level AND records in lt gt C THEN DBMS OUTPUT PUT LINE Tf inserting only you must choose to include converted records only GOTO end of program END IF SELECT cubenvt sequence CURRVAL INTO cur jobid FROM DUAL DBMS OUTPUT ENABLE 100000 DBMS OUTPUT PUT LINE Beginning Conversion of Table DBMS OUTPUT PUT LINE Job Number cur jobid commit counter 0 lt lt spriden_loop gt gt FOR spriden_rec IN spriden_cursor LOOP BEGIN cur_rec spriden_rec spriden_cvt_record_id col status C DELETE FROM curcerr WHERE curcerr table owner cur owner AND curcerr table name cur tbl AND curcerr record id cur rec Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts IF process level T
16. 5 z Allows you to enter and execute queries Convert Table Script Creates lt TargetTable gt _cvt_create sq script that creates an Oracle convert table according to user entered specifications EN SQL Loader Script Creates lt TargetTable gt _cvt ctl script that loads legacy data from client flat ASCII file to convert table using SQL Loader Conversion Script Creates lt TargetTable gt _convert sql script that transforms and moves legacy data from convert table to target table 12 View Function Allows you to view function text to enter function parameters and to construct function call to be included in TargetTable convert sql script October 2003 Release 3 0 2 5 Confidential SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool Icon Description 3 Allows you to edit a function Edit Function Returns user to the CUACNVT form from other windows in the Converter Tool Ej Converter Tool Main Window Shows records that were not converted during conversion processing Allows you to set up a crosswalk of values to be decoded from legacy to valid target table values View Edit Crosswalk Used to access the Converter Tool Control form where a variety of default session values can be set up Generates a report that displays the contents of the table and column block rules This document can be used to ma
17. Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers_rec CONVERT_LGCY CODE spbpers_rec SPBPERS LGCY CODE converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS CODE October 2003 Release 3 0 D 37 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts BEGIN cur col SPBPERS MRTL_CODE legacy value spbpers rec convert mrtl code CURCNVT Rules Column is Not Required Conversion function is not specified Validation function specified No default value converted_val null converted_val CONVERT CODE spbpers_rec SPBPERS CODE converted val col err THIS SECTION ILLUSTRATES USING THE VALIDATION FUNCTION VALIDATE SINGLE SATURN STVMRTL STVMRTL CODE LEGACY VALUE IF col err TRUE THEN err msg Validation failure RAISE column error USINGF VALIDATE SINGLE FUNCTION ELSIF SUBSTR col err 1 3 ERR THEN err msg col err RAISE column error END IF EXCEPTION WHEN column error THEN col status E process status p err cur owner cur 61 cur jobid cur rec cur col legacy
18. EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR ACTIVITY DATE BEGIN cur col SPRADDR ACTIVITY DATE legacy value spraddr rec convert activity date CURCNVT Rules Column is Required Conversion function is specified No validation specified No default value converted_val null converted_val SYSDATE IF SUBSTR converted_val 1 3 ERR OR SUBSTR converted_val 1 3 ORA THEN err msg Column conv failure converted_val RAISE column error END IF IF converted val IS NULL THEN err msg Missing required value RAISE column error END IF BEGIN spraddr rec SPRADDR ACTIVITY TO DATE converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column_error THEN col status E October 2003 Release 3 0 D 27 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts D 28 process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR USER BEGIN cur col SPRADDR USER legacy value spraddr rec convert user CURCNVT Rules Column is Not Required Conversion function 15 not specified No validat
19. NEXTVAL log file id FROM DUAL SPOOL amp spool file PROMPT PROMPT Please enter the record type which should be processed PROMPT N ew records PROMPT C onverted records PROMPT E rrored records ACCEPT records in CHAR PROMPT Include PROMPT PROMPT Please enter the disposition to which the records should PROMPT be processed PROMPT C onvert records only PROMPT Insert records only records were previously converted successfully PROMPT B oth convert and insert records into spriden ACCEPT process level CHAR PROMPT Disposition PROMPT SET SERVEROUTPUT ON SIZE 500000 PROMPT PROMPT PROMPT will be creating generated id records PROMPT in SPRIDEN CVT for any converted records PROMPT i e records with spriden status C PROMPT PROMPT SELECT TO CHAR sysdate DD MON Y YY Y HH24 MI START TIME FROM DUAL DECLARE cur jobid NUMBER 0 cur owner VARCHAR2 30 SATURN cur tbl VARCHAR2 30 SPRIDEN cur fileid VARCHAR2 2000 col status VARCHAR2 1 Release 3 0 D 3 SCT Converter Tool User s Guide Appendix D Sample Conversion Scripts D 4 legacy_value VARCHAR2 2000 process status VARCHAR2 1 err num NUMBER S col err VARCHAR2 100 cur rec NUMBER 8 cur col VARCHAR2 30 rows errored NUMBER 8 0 rows success convert NUMBER 8 0 rows success insert NUMBER 8 0 commit counter NUMBER 8 commit frequency NUMBER 8 250 converted val VARCHAR2 2000
20. cur col SPBPERS STAT CODE BIRTH legacy value spbpers rec convert stat code birth CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value D 46 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts converted val null converted val spbpers rec CONVERT STAT CODE BIRTH spbpers rec SPBPERS STAT CODE BIRTH converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS DRIVER LICENSE BEGIN cur col SPBPERS DRIVER LICENSE legacy value spbpers rec convert driver license CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spbpers_rec CONVERT DRIVER LICENSE spbpers_rec SPBPERS DRIVER LICENSE converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS STAT CODE DRIVER BEGIN cur col SPBPERS STAT CODE DRIVER legacy value spbpers rec convert stat code driver CURCNVT Rules Column is Not Re
21. priden rec SPRIDEN LAST NAME priden rec SPRIDEN FIRST NAME rec SPRIDEN MI priden rec SPRIDEN CHANGE IND priden rec SPRIDEN ACTIVITY DATE rec SPRIDEN USER rec SPRIDEN ORIGIN TOWS success insert rows success insert 1 UPDATE SPRIDEN CVT SET spriden status T WHERE SPRIDEN CVT RECORD ID cur rec EXCEPTION WHEN OTHERS THEN err msg Error inserting into Banner table col status E cur col NULL RAISE END END IF EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg WHEN OTHERS THEN err num SQLCODE err msg SUBSTR SQLERRM 1 100 process status E p err cur owner cur tbl cur jobid cur rec cur col null null null err msg END IF col status E THEN rows errored rows errored 1 END IF if commit counter commit frequency then commit commit counter 0 else commit counter commit counter 1 end if END LOOP spriden loop October 2003 Release 3 0 D 13 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts function to create spriden records in convert table with generated IDs BEGIN DECLARE CURSOR max record nbr IS SELECT max spriden
22. you are certain that all records you are loading are person records you may default the P entity indicator here or you may include it in the data file If it is defaulted in the Converter Tool the LOAD box will be unchecked and the INSERT box will be checked Navigate to the Load and Insert options and check Note If in your conversion you default a value for spriden entity ind you both will uncheck LOAD and check INSERT Navigate to the Next Record SPRIDEN ACTIVITY DATE Navigate to the Convert Function field and enter This will populate the spriden activity date field with the current system SYSDATE date If during your conversion you wish to load the legacy system maintenance date into this field you would leave the Convert Function field null and insert the date format mask into the Format Mask field For example if your legacy date is YYY YMMDD you would insert this format mask into the Format Mask field Navigate to the Load and Insert fields Uncheck No legacy date exists for this column but you do want the presecribed value LOAD Check INSERT SYSDATE inserted during the conversion Navigate to the next record SPRIDEN USER Navigate to the Default field Enter SCTCVT No quotation marks Navigate to the Default Action column Choose D efault if no legacy passed You may also choose O verride any legacy passed Navigate to the Load option and verify that it is No legacy data exists for this c
23. 3 0 2 3 SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool The SCT Converter Tool CUACNVT oracle Developer Forms Runtime Web E E lal x Action Edit Query Block Record Field Help Window USING GENERATED IDs Table Owner SATURN Table Name SPRIDEN Commit Freq 250 Activity Date 13 JUL 2001 Error Action Continue processing row initial Extent ___ Wrapup Fnetn Lai a Breakpoint into Data Input Format Fixed Length C Delimited by a Column SPRDENPIDM 7 Load Order Required Convert Fn F_CVT_AUTO_PIDM LEGACY_VALUE Activity Date fr3 JUL 2001 Default Default Action 21 Format Mask Length Load M insert M Record 1 Parts of the CUACNVT Form The CUACNVT Toolbar Icon Description Saves all changes entered since the last save Clears all information from form so that query or new record can be entered Clear Form 2 4 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter Icon Description E Previous Record Moves cursor to the first enterable field in the previous record Next Record Moves cursor to the first enterable field in the next record of the current information area Deletes the current record Inserts a new blank record
24. Commi Breakpoint Information Error amp Breakpoint 1 CONVERT Wrapup BrekFundi FLGEN_RESET_SEQNO SEQ1 Data Input F Breakpoint 2 CONVERT_ATYP_CODE Break Funcii F GEN RESET SEGNO SEQ1 Convert Val Breakpoint 3 Functi Format Mas Note The breakpoint function uses the column s in each Breakpoint field to order the conversion Each time the breakpoint value s changes the function is executed Use CONVERT columns CONVERT PIDM CONVERT ATYP CODE to indicate the breakpoints Use the F GEN RESET SEQNO function to reset the sequence numbers The sequence parameter passed should match the sequence number passed as a parameter to the F GEN SEQNO function on the column where the sequence number is being set For example to set a sequence number for address types on SPRADDR SEQNO use GEN SEQNO SEQ1 and set the breakpoints as CONVERT PIDM CONVERT ATYP CODE using GEN RESET SEQNO SEQI as illustrated Breakpoints 1 2 and 3 represent the column s on which the break in sequence or calculation should occur Break Function represents the function that re sets the sequence or performs the calculation Users have the option of breaking on up to 3 key fields 2 10 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter The Column Block Fields Description Column This field contains e
25. Control Eorm er ERU 2 7 Using Generated IDs 4 ask eV Agave umen 2 7 Control Form File Location 2 8 The Table Block vss un dene bs eost ae amt 2 8 Breakpoint Window cantet ere BO ERE dE ica ise 2 10 Block 5525 ERRORI UI EP 2 11 Using the Convert Function Feature 2 12 Applying an Oracle Function to a Column sss 2 12 Using a Delivered Function essere 2 13 The Function Code and Parameters Window sess 2 14 Using the Crosswalk Feature of the Converter Tool 2 16 Using Options on the Crosswalk Form sees 2 17 Using Crosswalk Values in the Conversion Script sese 2 19 Loading Data into the SPRIDEN Table ssesssssssseeeer eee 2 20 Generating SCT eerie 2 21 Using the Generated ID Feature of the Converter 2 2 22 Operational Concepts nepoti a 2 23 Convert Script for 2 23 How It Works on e RR Wane ene 2 24 A Sample Rn ste eR D SU EI ns es 2 24 Multiple Iterations of Converting SPRIDEN esee 2 26 Getting the PIDM for Other Tables using
26. GET 2 27 Use Generated ID s not 2 27 Use Generated ID s marked 3 02 5 AS 2 28 iti Table of Contents Chapter 3 A Conversion Example Using the SCT Converter Tool Establishing Specifications for Conversion of SPRIDEN Table 3 5 Using CUACNVT to produce the lt TargetTable gt _cvt_create sql 3 11 Using CUACNVT to produce the lt TargetTable gt cvt sql 3 12 Using CUACNVT to produce the lt TargetTable gt convert sq 3 13 Establishing Specifications for Conversion of SPRADDR Table 3 14 Establishing Specifications for Conversion of SPBPERS 3 20 Chapter 4 Scripts Produced by the SCT Converter Tool CAUCNVT Create Convert Table Script lt gt _ _ 41 4 3 Create SQL Loader Script TargetTable7 cvt ctl eessssssssssseeee 4 5 Create Convert Script TargetTable7 convert sgl sse 4 7 Running the Convert ener enne nnns 4 8 Answering the Prompts ienna eee nia a ea en eenia 4 8 Explanation of Record Type Prompts essere 4 8 Dealing with Errors coe e ede PE se
27. Getting the PIDM for Other Tables using PIDM With new ID s created for all persons from the legacy system into SCT Banner the question is How do I relate the Generated ID record back to the legacy ID for the rest of the data when I need pidm The answer is the F CVT GET PIDM function The GET PIDM function is used regardless of Generated ID s This function behaves differently depending on how the Use Generated IDs parameter 1s configured Use Generated IDs not marked GET PIDM function takes the ID passed to it and looks for e matching SCT Banner SPRIDEN ID record and e A NULL change indicator i e the most current record October 2003 Release 3 0 2 27 Confidential SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool Use Generated ID s marked The F CVT GET PIDM function takes the ID passed to it and looks for e matching SCT Banner SPRIDEN ID record and e A change indicator set to and The SPRIDEN NTYP CODE set to LGCY This is why the SPRIDEN CONVERT SQL script takes the legacy ID record in SPRIDEN CVT and sets the change indicator to T and the SPRIDEN NTYP CODE to LGCY A change indicator of I and the SPRIDEN NTYP CODE of LGCY provides a way to easily find the PIDM that is associated with the legacy ID even though a Generated ID was created for the legacy ID 2 28 Release 3 0 October 200
28. INCAR IND spbpers rec spbpers incar ind SPBPERS WEBID spbpers rec spbpers webid SPBPERS WEB LAST ACCESS spbpers rec spbpers web last access SPBPERS PIN DISABLED IND spbpers rec spbpers pin disabled ind SPBPERS ITIN spbpers rec spbpers itin SPBPERS CVT STATUS col status SPBPERS CVT JOB ID cur jobid WHERE SPBPERS CVT RECORD ID cur rec TOWS success convert rows success convert 1 EXCEPTION WHEN OTHERS THEN err msg Error updating record RAISE END lt lt insert_spbpers gt gt IF process level B OR process level THEN ee Inserting into Banner Table spbpers BEGIN INSERT INTO SATURN spbpers SPBPERS PIDM SPBPERS SSN SPBPERS BIRTH DATE CODE ETHN CODE Release 3 0 SCT Converter Tool User s Guide October 2003 Confidential Appendix D Sample Conversion Scripts October 2003 Confidential SSPBPERS SEX ACTIVITY DATE VALUES spbpers rec SPBPERS PIDM Spbpers rec SPBPERS SSN Spbpers rec SPBPERS BIRTH DATE Spbpers rec SPBPERS CODE Spbpers rec SPBPERS ETHN CODE Spbpers rec SPBPERS SEX Spbpers rec SPBPERS ACTIVITY DATE IOWS success insert rows success insert 1 UPDATE SPBPERS CVT SET spbpers cvt status WHERE SPBPERS CVT RECORD ID cur rec EXCEPTION WHEN OTHERS THEN err msg Error inserting
29. Option 1 Option 2 Option 3 198 CONVERSION 198910 Fal 1989 issor _____ _____ i 1991 CONVERSION 199110 1991 faam comwmsow emmei 1 Add to Validaton Table Record 4 4 The Entity field allows you to give to the crosswalk grouping This entity name is user defined and is used as a parameter in the function call for the F CVT CURCVAL RNULL function which will use the crosswalk to insert the SCT Banner values into the appropriate lt TargetTable gt columns during the conversion process Naming convention for Crosswalk Entities The crosswalk entity should be named after the target table for which the crosswalk is being created For example to crosswalk address types for the STVATYP validation table the crosswalk should be named STVATYP To crosswalk detail codes for the TBBDETC table the crosswalk entity should be named TBBDETC If there is a one to one relationship between legacy values and SCT Banner values you will simply enter the legacy values in the Legacy Value column and the equivalent SCT Banner values in the SCT Banner Value column You would then SAVE exit the crosswalk return to CUACNVT and use CURVAL RNULL function on the appropriate field See the following section USE THE CROSSWALK VALUES IN THE CONVERSION SCRIPT 2 16 Release 3 0 October 2003
30. SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter The Legacy Description field can be loaded if conversion data from the legacy system is supplied If cvt_chkxwlk sq utility script is used after data is loaded into CVT temporary table any values for columns using a crosswalk function will be loaded into the CUACVAL form CURCVAL table with a Legacy description of CONVERSION and a SCT Banner description of CONVERSION The SCT Banner description is used only to populate the description field for validation tables as described in the following paragraph When the Entity in the Key block is a SCT Banner validation table and there is a one to one relationship between the Legacy Value and SCT Banner Value columns you can use the Add to Validation Table button to add any new values to the validation table that are listed in the crosswalk form but are not in the validation table Using Options on the Crosswalk Form If it is necessary to use multiple values from your legacy system to translate to one SCT Banner value you will need to use the option columns on the crosswalk Your flat file will need to include all values necessary for the translation If any of the values in the flat file do not correspond to a column in the SCT Banner table you will need to add a column or columns to the lt target_table gt secifications on the CUACNVT form An example of a situation that might call for use
31. TABLENAME VALIDATION TABLE NAME COLUMN VALIDATION TABLE COLUMN IN VALUE LEGACY VALUE Click the Construct Function Call button Click the Return button Release 3 0 SCT Converter Tool User s Guide Confidential F 5 Appendix F Using the SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Choose Save amp Return You will be returned to the Table Block of the form and must perform a NEXT BLOCK function to return to the Column Block Navigate to the Load field in the Column Block and make the appropriate choice for the Load indicator Checking the LOAD button indicates that you want this column to appear in the SQL Loader script because you have legacy data in the flat file for this column to be loaded into the convert table UNchecking the Load box indicates that there 1s no legacy data to be loaded into the column There may be a default value applied by a function but the Load box should be checked ONLY if there is legacy data in the data file for this column Navigate to the Insert indicator in the Column Block and make the appropriate choice for the Insert indicator Checking the INSERT button indicates that you want this column to appear in the conversion script to be converted to the target table columns in the convert table and subsequently inserted into the actual target table Not all columns need be inserted If a column is nullable
32. THEN GOTO insert spriden END IF Beginning evaluation of column SPRIDEN_PIDM BEGIN cur col SPRIDEN_PIDM legacy value spriden rec convert pidm CURCNVT Rules Column is Required Conversion function is specified No validation specified No default value converted_val null converted val AUTO PIDM LEGACY VALUE IF SUBSTR converted val 1 3 ERR OR SUBSTR converted val 1 3 ORA THEN err msg Column conv failure converted val RAISE column error END IF IF converted val IS NULL THEN err msg Missing required value RAISE column error END IF BEGIN spriden rec SPRIDEN PIDM TO NUMBER converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END E Beginning evaluation of column SPRIDEN ID BEGIN cur col SPRIDEN 10 legacy value spriden rec convert 14 CURCNVT Rules Column is Required Conversion function 15 not specified No validation specified No default value converted_val null converted val rec CONVERT ID October 2003 Release 3 0 D 5 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts IF converted_val IS NULL
33. TargetTable gt _cvt_create sql scripts October 2003 Confidential spriden_cvt_create sql AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This script creates the temporary conversion table SPRIDEN for converting legacy data into SPRIDEN This script was generated by the SCT Converter Tool AUDIT TRAIL END DROP TABLE SPRIDEN CVT CREATE TABLE SPRIDEN CVT SPRIDEN PIDM NUMBER 8 CONVERT PIDM VARCHAR2 9 SPRIDEN ID VARCHAR2 9 CONVERT ID VARCHAR2 9 SPRIDEN LAST NAME VARCHAR2 60 CONVERT LAST NAME VARCHAR2 60 SPRIDEN FIRST NAME VARCHAR 15 CONVERT FIRST NAME VARCHAR2 15 SPRIDEN MI VARCHAR 2 15 CONVERT MI VARCHAR 2 15 SPRIDEN CHANGE IND VARCHAR2 1 CONVERT CHANGE IND VARCHAR2 1 SPRIDEN ENTITY IND VARCHAR 2 1 CONVERT ENTITY IND VARCHAR2 1 SPRIDEN ACTIVITY DATE DATE CONVERT ACTIVITY DATE VARCHAR2 9 SPRIDEN USER VARCHAR2 30 CONVERT USER VARCHAR2 30 SPRIDEN ORIGIN VARCHAR 2 30 CONVERT ORIGIN VARCHAR2 30 SPRIDEN SEARCH LAST NAME VARCHAR2 60 CONVERT SEARCH LAST NAME VARCHAR2 60 SPRIDEN SEARCH FIRST NAME VARCHAR 15 CONVERT SEARCH FIRST NAME VARCHAR 15 SPRIDEN SEARCH MI VARCHAR 2 15 CONVERT SEARCH MI VARCHAR 15 SPRIDEN SOUNDEX LAST NAME CHAR 4 CONVERT SOUNDEX LAST NAME VARCHAR2 4 SPRIDEN SOUNDEX FIRST NAME CHAR 4 CONVERT SOUNDEX FIRST NAME VARCHAR2 4 SPRIDEN NTYP CODE VARCHAR2 4 CONVERT NTYP CODE VARCHAR2 4 SPRIDEN RECORD ID NUMBER 8 CONSTRAINT S
34. TargetTable7 convert sql where lt TargetTable gt is the actual SCT Banner table that will eventually contain legacy data This script also translates legacy values to appropriate target table values by applying user specified functions default values and or computed values to the target table columns during the conversion process The SCT Converter Tool can also produce a mapping document for each target table This mapping document should be used to arrive at the proper structure for the flat data file Assumptions You have a flat ASCII file from the legacy system e Your flat file data has been mapped to an existing target table and is structured for compatibility with the target table If you are working through the sample exercises in this documentation you may need to create different sample data files that are compatible with the validation codes in your training database Release 3 0 1 3 SCT Converter Tool User s Guide Chapter 1 Introduction 1 4 Installation To install the SCT Converter Tool please refer to the Converter Tool Installation Guide delivered with the SCT Converter Tool software DBA Issues Before installing using the SCT Converter Tool you should check with your DBA for the project to discuss the issues listed below It is possible that successful and efficient processing of the conversion script would require some adjustments by the DBA especially when converting tables with large numbers of
35. USE WHEN GETTING A PIDM FOR SPRIDEN ONLY FOR ALL OTHER TABLES USEF CVT GET PIDM Function that will return a NULL if ID does not exist rather than an error This can be used in situations of running multiple stages of conversions where some of the ID s may already exist in the BANNER SPRIDEN table P CVT ERR This procedure 15 used by the converter program to insert errors into the curcerr table X X X Note Many functions can be easily modified for use in other areas Reviewing all functions 1s recommended to see if a modification to the function will help you achieve your goal It is recommended that baseline functions are not modified but copied and renamed October 2003 Release 3 0 H 6 Confidential SCT Converter Tool User s Guide
36. and Insert boxes Navigate through each of the following columns UNchecking the Load and Insert boxes on each one SPBPERS CONFID IND SPBPERS DEAD IND SPBPERS VETC FILE NUMBER SPBPERS LEGAL NAME SPBPERS PREF FIRST NAME SPBPERS NAME PREFIX SPBPERS NAME SUFFIX Navigate to the SPBPERS ACTIVITY DATE column Navigate to the Convert Function field and enter SYSDATE October 2003 Release 3 0 3 24 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Navigate to the Load and Inser boxes UNcheck the Load box CHECK the Insert box Navigate through ALL remaining columns in SPBPERS UNchecking both the Load and Insert boxes for each column SAVE your changes You have now entered all the necessary specifications for the SPBPERS table conversion The next step is to use the features of the converter tool to produce the following 3 scripts SPBPERS create sql creates the convert table SPBPERS CVT To create this script press the CONVERT TABLE SCRIPT button on the Tool Bar You will see the success message on the message line of the form This script should be run in SQL on the server to create the convert table in the host database SPBPERS cvt ctl used by SQL Loader to load the data from the client s flat file SPBPERS cvt dat to the convert table SPBPERS CVT To cre
37. converted_val CONVERT FROM DATE BEGIN spraddr_rec SPRADDR FROM TO DATE converted val YYYYMMDD EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END October 2003 Release 3 0 D 21 Confidential SCT Converter Tool User s Guide Appendix D Sample Conversion Scripts Beginning evaluation of column ISPRADDR TO DATE BEGIN col SPRADDR TO DATE legacy value spraddr rec convert to date CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spraddr rec CONVERT TO DATE BEGIN spraddr rec SPRADDR TO TO DATE converted val YYYYMMDD EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPRADDR STREET LINEI BEGIN cur col SPRADDR STREET LINEI legacy value spraddr rec convert street linel CURCNVT Rules Column is Not Required Conversion function 15 not spe
38. errors correct them and run this script again this time choosing E rrored records for the first parameter of the script and C onvert for the disposition Continue this process until all errors are corrected Once you are satisfied that all errors are correct and that your data is clean run this script for the C onverted records in I nsert mode to insert the data into the target table October 2003 Release 3 0 F 9 Confidential SCT Converter Tool User s Guide Appendix F Using the SCT Converter October 2003 Release 3 0 F 10 Confidential SCT Converter Tool User s Guide Appendix Utility Scripts util Directory Scripts L aee ee SA Ae a leanne eaves G 3 Utility Script Usage nette Re REA ee eee G 3 SPRIDEN duplicate checking steps 2 0 G 3 SPRADDR duplicate checking steps G 4 SPRTELE duplicate checking steps sssssseeeeeeeeen G 4 Extracting Data from the Audit 1 G 5 Validating Data for Crosswalked Columns seen G 5 Validating Data for Columns with Foreign Key G 5 Appendix G Utility Scripts G 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix Utility Scripts util Directory Scripts These scripts are delivered with the server zip file for the converter tool an
39. function NEXT BLOCK CUACNVT Navigate to the Length field Change the value from 10 9 This action will increase the size ofthe CONVERT PIDM column in the convert table from 8 characters to 9 characters This is necessary to accommodate the 9 character legacy ID values that reside in the CONVERT PIDM positions in the date file October 2003 Confidential Release 3 0 SCT Converter Tool User s Guide 3 6 Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Navigate to the Load field in the Column Block and Selecting the LOAD button creates a line in the SQL Loader script to verify that it is checked indicate that there is legacy data in the flat file for this column Please review the Generating SCT Banner PIDMs section of this document for a detailed explanation of the required file layout for the spriden cvt dat file In the sample data file there are records with values in the convert pidm column Navigate to the Insert indicator in the Column Checking the INSERT button indicates that you want this column to appear in Block and check the Insert indicator the conversion script to be converted to the target table columns in the convert table and subsequently inserted into the actual target table If you have edited out the unnecessary columns in the beginning of this process you w
40. of the crosswalk options columns is in the translation of legacy address codes into the SCT Banner SPRADDR address table An institution may have several campuses each of which has had its own different address codes that indicate a student s permanent address but for the new system those address codes need to be standardized across campuses Therefore the campus code is needed in order for a correct translation of address code values to occur Step One Preparing the Flat File The flat file used as the data source file for the SPRADDR table must include the campus code For this example assume the campus code follows the address type in the legacy file Step Two Set up Specifications on CUACNVT On the CUACNVT form for the SPRADDR table you will insert a column after the spraddr_atyp_code name it spraddr_camp_code and enter the correct length etc Check the load box but do not check the insert box You will only be loading legacy data into the convert table SPRADDR_CVT so that these campus values be passed as parameters to the CURVAL function and used in the translation of address types You do not load this data into the target table Step Three Set up the Crosswalk Form CUACVAL Name your entity STVATYP Do not enter any data in the Options fields in the Key Block at this time These fields are used as query fields after the crosswalk has been completed to allow you to see all specificat
41. rec spbpers rec spbpers cvt record id col status C DELETE FROM curcerr WHERE curcerr table owner cur owner AND curcerr table name cur tbl AND curcerr record id cur IF process level THEN GOTO insert spbpers END IF Beginning evaluation of column SPBPERS BEGIN cur col legacy value spbpers rec convert pidm CURCNVT Rules Release 3 0 SCT Converter Tool User s Guide D 35 Appendix D Sample Conversion Scripts Column 18 Required Conversion function is specified No validation specified No default value converted_val null converted GET PIDM LEGACY VALUE IF SUBSTR converted_val 1 3 ERR OR SUBSTR converted_val 1 3 THEN err msg Column conv failure converted_val RAISE column error END IF IF converted val IS NULL THEN err msg Missing required value RAISE column error END IF BEGIN spbpers rec SPBPERS TO NUMBER converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column_error THEN col status E process status p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS SSN BEGIN cur_col SPBPERS_SSN legacy_value spbpers_rec convert_ssn CURCNVT Rules Column is Not Required Conv
42. status C PROMPT PROMPT SELECT TO_CHAR sysdate DD MON Y YY Y HH24 MI START TIME FROM DUAL October 2003 Release 3 0 D 17 Confidential SCT Converter Tool User s Guide Appendix D Sample Conversion Scripts D 18 DECLARE cur jobid NUMBER 0 cur owner VARCHAR2 30 SATURN cur tbl VARCHAR2 30 SPRADDR cur fileid VARCHAR2 2000 col status VARCHAR2 1 legacy value VARCHAR2 2000 process status VARCHAR2 1 err num NUMBER S col err VARCHAR2 100 cur rec NUMBER 8 cur col VARCHAR2 30 rows errored NUMBER 8 0 rows success convert NUMBER 8 0 rows success insert NUMBER 8 0 commit counter NUMBER 8 commit frequency NUMBER 8 250 converted val VARCHAR2 2000 err msg VARCHAR2 300 first row BOOLEAN TRUE column error EXCEPTION process level VARCHAR2 1 UPPER amp process level records in VARCHAR2 1 UPPER amp records_in breakl value VARCHAR2 30 break2 value VARCHAR2 30 break3 value VARCHAR2 30 seq NUMBER S 0 seq2 NUMBER S 0 seq3 NUMBER S 0 CURSOR spraddr_cursor IS SELECT FROM spraddr_CVT WHERE spraddr_cvt_status records_in ORDER BY CONVERT PIDM CONVERT ATYP CODE 2 spraddr_cvt_record_id BEGIN IF process_level AND records in lt gt C THEN DBMS OUTPUT PUT LINE Tf inserting only you must choose to include converted records only GOTO end of program END IF SELECT cubenvt sequence C
43. the View Function icon on the toolbar Click the Edit Parameters button Enter SEQI as the parameter for SEQUENCE Click the CONSTRUCT FUNCTION CALL button Click RETURN SAVE amp RETURN The cursor will be in the Owner field of the Table Block Click the BREAKPOINT INFO button in the Table Block You may choose to click the BREAK FUNCTION button It will Enter the following data bring up the function library list From there you may choose the BREAKPOINT 1 CONVERT PIDM F GEN RESET SEQNO function and populate the parameters BREAK FUNCTION GEN RESET SEQNO SEQI from the View Function window OR you may simply enter the BREAKPOINT 2 CONVERT ATYP CODE data as shown This function will apply a set of sequence numbers a for every series of pidm atyp_codes that exist On a change in that BREAK FUNCTION GEN RESET SEQNO SEQI combination of fields the sequence number 15 reset and the series begins again with a new pidm atyp code set Close the window by clicking the X in the upper right corner Breakpoint Exists message appears on form beside SAVE your changes BREAKPOINT INFO button Navigate to the Column Block with the cursor in the SPRADDR_SEQNO column Navigate to the Load and Insert boxes UNcheck Load check There is no data to load but we must insert the generated sequence Insert number Navigate to the next record SPRADDR FROM DATE We have data for a From Date on two of the sample records Navigate
44. uses the legacy ID to match with SPRIDEN ID If a match is found the existing SPRIDEN PIDM is returned to populate the SPRADDR PIDM field If no match 15 found the transaction will produce an error With your cursor in the Convert Fnctn field press the VIEW Function Code and FUNCTION button on the Tool Bar Parameters Window Press the EDIT PARAMETERS button Edit Parameters Dialog Box Enter LEGACY VALUE in the right column of the parameters In the table the legacy value that will be loaded into dialog box the convert pidm column is the legacy ID That is the value that must be passed to the function The name for that value 1s LEGACY VALUE Press the CONSTRUCT FUNCTION CALL button When you press the CONSTRUCT FUNCTION CALL button the blank box at the top right corner of the form 1s populated with the actual function call that will appear in the convert script F CVT GET PIDM LEGACY VALUE Press the RETURN button Choose amp Return Doing this will return your cursor to the Table Block of CUACNV T To resume work you must perform a NEXT BLOCK function NEXT BLOCK CUACNVT With your cursor in the SPRADDR PIDM column field Navigate to the Length field Change the 8 to 9 You must change the length of the CONVERT PIDM column in the table to accommodate the legacy ID which is 9 characters long The GET PIDM function will use this ID to match against t
45. value converted val nullerr msg END Beginning evaluation of column SPBPERS ETHN CODE BEGIN cur col SPBPERS ETHN CODE legacy value spbpers rec convert ethn code CURCNVT Rules Column is Not Required Conversion function is specified No validation specified No default value THIS SECTION ILLUSTRATES THE USE OF THE CROSSWALK FUNCTION converted_val null converted val CURCVAL RNULL STVETHN LEGACY VALUE IF SUBSTR converted val 1 3 ERR OR SUBSTR converted val 1 3 ORA THEN err msg Column conv failure converted_val RAISE column error END IF USING CROSSWALK FUNCTION spbpers_rec SPBPERS ETHN CODE converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg D 38 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential END Beginning evaluation of column SPBPERS RELG CODE BEGIN col RELG CODE legacy value spbpers rec convert relg code CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value e converted val null converted 1 spbpers CONVERT RELG CODE spbpers rec SPBPERS RELG CODE converted val EXCEPTION WHEN column err
46. you are using client server mode and have chosen Local Machine on the Control Form the following dialog box will appear Target Directory 4 Of x The box will be populated with the directory value you entered on the Control Form When the script is complete you will see the following message on the message line of the form Script successfully written to c convert lt TargetTable gt _cvt_create sql To view the text of a sample convert script refer to Appendix B Sample Table Creation Scripts Run this script on the server to create the convert table in the host database October 2003 Release 3 0 F 7 Confidential SCT Converter Tool User s Guide Appendix F Using the SCT Converter Using CUACNVT to produce the TargetTable cvt ctl script With the cursor in the Table Block for the table Press the SQL LOADER SCRIPT button If you are using Internet Native forms and writing to your database server no dialog box will appear If you are using client server mode and have chosen Local Machine on the Control Form the following dialog box will appear 8 Target Directory x The box will be populated with the directory value you entered on the Control Form Press OK When the script is complete you will see the following message on the message line of the form Script successfully written to c convert lt TargetTable gt _cvt ctl To view t
47. 3 SCT Converter Tool User s Guide Confidential Chapter 3 A Conversion Example Using the SCT Converter Tool Establishing Specifications for Conversion of SPRIDEN 3 5 Using CUACNVT to produce the lt TargetTable gt _cvt_create sq 1 3 11 Using CUACNVT to produce the TargetTable cvt sql 3 12 Using CUACNVT to produce the TargetTable convert sql 3 13 Establishing Specifications for Conversion of SPRADDR Table 3 14 Establishing Specifications for Conversion of SPBPERS 3 20 3 1 Chapter 3 A Conversion Example Using The SCT Converter Tool 3 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 3 Conversion Example Using The SCT Converter Tool A Conversion Example Using the SCT Converter Tool This section examines the steps of a sample conversion using CUACNVT to create the necessary scripts to accomplish the process There are examples for loading data into three SCT Banner tables where shared data resides for all products SPRIDEN Identification Table SPBPERS Person Biographic Demographic Information Table SPRADDR Address Table The flat files that contain the legacy data spriden cvt dat spbpers cvt dat and spraddr cvt
48. 5 CONVERT MI VARCHAR 2 15 SPRIDEN CHANGE IND VARCHAR2 1 CONVERT CHANGE IND VARCHAR2 1 SPRIDEN ENTITY IND VARCHAR 2 1 CONVERT ENTITY IND 2 1 SPRIDEN ACTIVITY DATE DATE CONVERT ACTIVITY DATE VARCHAR2 9 SPRIDEN USER VARCHAR2 30 Release 3 0 1 5 SCT Converter Tool User s Guide Chapter 1 Introduction 1 6 CONVERT USER VARCHAR 2 30 SPRIDEN ORIGIN VARCHAR2 30 CONVERT ORIGIN VARCHAR2 30 SPRIDEN SEARCH LAST NAME VARCHAR2 60 CONVERT SEARCH LAST NAME VARCHAR2 60 SPRIDEN SEARCH FIRST NAME VARCHAR 2 15 CONVERT SEARCH FIRST NAME VARCHAR 15 SPRIDEN SEARCH MI VARCHAR 2 15 CONVERT SEARCH MI VARCHAR 15 SPRIDEN SOUNDEX LAST NAME CHAR 4 CONVERT SOUNDEX LAST NAME VARCHAR2 4 SPRIDEN SOUNDEX FIRST NAME CHAR 4 CONVERT SOUNDEX FIRST NAME VARCHAR 4 SPRIDEN NTYP CODE VARCHAR2 4 CONVERT NTYP CODE VARCHAR2 4 SPRIDEN RECORD ID NUMBER 8 CONSTRAINT PK SPRIDEN PRIMARY KEY SPRIDEN STATUS VARCHAR 2 1 SPRIDEN JOB ID NUMBER 8 STORAGE INITIAL IM PCTINCREASE 100 MAXEXTENTS 50 Notice the CONVERT column after each SPRIDEN column using the naming convention that substitutes the word CONVERT for the SPRIDEN table name The legacy data from an ASCII flat file 15 loaded into the convert columns in the convert table using SQL Loader and the SQL Loader control file produced from CUACNVT The Length field allows you to modify the le
49. 66 777777777 777 Lucky Lady Lane Cherokee NC 77777 888888888 888 Crazy Eight Road Hoyle WV 88888 999999999 999 John Lennon Way Revolution City ND 99999 000000000 000 Zero Street Nowhere NJ 00000 October 2003 Release 3 0 A 3 Confidential SCT Converter Tool User s Guide Appendix Sample Data Files Used in Examples A 4 spbpers_cvt dat Legacy Fields legacy ID legacy SSN birth date marital code ethnic code sex SCT Banner Fields spbpers pidm spbpers ssn spbpers birth date spbpers mrtl code spbpers ethn code spbpers sex DATA 111111111 111111111 01017077 Y 2 F 222222222 222222222 19480203 2 333333333 333333333 19750412 D 1 T 444444444 444444444 19770309 2 555555555 555555555 19780815 5 1 666666666 666666666 19480128 Y 2 M 777777777 777777777 19552323 888888888 888888888 19500406 D 3 F 999999999 999999999 19630106 W 2 M 000000000 000000000 19540321 S 1 N Release 3 0 SCT Converter Tool User s Guide October 2003 Confidential Appendix Sample Table Creation Scripts Sample lt TargetTable gt _cvt_create sq scripts B 3 spridem evt create sqli iet re A ee B 3 spraddr create RT RTT B 4 spbpers reate sql isses ue ee de e B 5 B 1 Appendix B Sample Table Creation Scripts B 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix B Sample Table Creation Scripts Sample lt
50. 999 2003 Systems amp Computer Technology Corporation All rights reserved SCT and the SCT logo are trademarks of Systems amp Computer Technology Corporation SCT Banner SCT Banner CAPP SCT Banner Object Access SCT BannerQuest and the accompanying logos are trademarks of Systems amp Computer Technology Corporation This material contains trade secrets and other confidential information and is subject to a confidentiality agreement The unauthorized possession use reproduction distribution display or disclosure of this material or the information contained herein is prohibited SCT Converter Tool User s Guide Table of Contents Chapter 1 Introduction T stallatton inherent woah 1 4 M IP 1 4 Brief Overview of Scripts Created by the SCT Converter Tool CUACNVT 1 4 Creating the Convert Table TargetTable 541 1 5 Creating the SQL Loader Control File TargetTable 1 7 Creating the Convert Script TargetTable 84 1 011000 1 8 Chapter 2 Elements of the SCT Converter Tool Logting Tii e a eti adt 2 3 The SCT Converter Tool 2 4 204 40441008000000000000 2 4 Parts of the CUACNVT Form nisse ecelesie eee e eed 2 4 The CUACNVT Toolbar cdit 2 4 The
51. CVT and insert them into SPRIDEN 8 Convert any remaining data Operational Concepts SPRIDEN NTYP CODE Gives information about the SPRIDEN record When the SPRIDEN NTYP CODE is LGCY that indicates the SPRIDEN record is the legacy ID SPRIDEN CHANGE IND Used to indicate how the ID record was changed If the value in this column is NULL the record is the current Name and ID The other standard SCT Banner values are N for a name change and T for an ID changes For purposes of conversion we are using this column with a value of T in conjunction with the SPRIDEN NTYP CODE having a value of LGCY to indicate the legacy ID Because the SPRIDEN CHANGE IND column will not be null the legacy ID will not be the current ID the generated ID will be This is used in the F CVT GET PIDM function explained later SOBSEQN table Contains sequence numbers that are used for various things in SCT Banner ID and PIDM are two numbers stored in this table When ID s are generated you get the next available ID number in SOBSEQN The F CVT GET PIDM function dynamically updates the SOBSEQN MAXSEQNO where the SOBSEQN FUNCTION PIDM SOBSEQN SEQNO PREFIX The leading character that is used for generated ID s The initial value 15 Change this value if you want generated ID s to have a different leading character Convert Script for SPRIDEN If the Using Generated IDs check box is marked on the Conversion Tool Cont
52. Convert Fnctn field For example if the legacy data 1s in all UPPER case you may wish to apply the INITCAP function to the SPRIDEN LAST NAME field Enter the following value into the Convert Fnctn field INITCAP LEGACY VALUE This will apply Oracle s INITCAP function to the legacy value in the convert last name field of the spriden cvt table during the conversion process causing the value inserted into the spriden last name field of the convert table and subsequently the spriden last name field in the actual SPRIDEN table to be mixed case Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter October 2003 Confidential Using a Delivered Function With the cursor in the Convert Fnctn field click the CONVERT FNCTN button and choose a function name from the list For this example we will use the F CVT_GET_PIDM function It is returned to the field The F CVT GET PIDM function is used to populate the pidm of the target table This function looks at the legacy ID which resides in the convert_pidm column of the convert table The function looks for a match in the SPRIDEN table in the SPRIDEN ID column then returns the SPRIDEN PIDM to be used to populate the lt TargetTable gt _pidm column of the convert table If a match on ID is not found an error message is generated The assumption is that the SPRIDEN table will be loaded first so that SPRIDEN ID can be us
53. Converter Tool Functions alphabetically by system sss H 3 October 2003 Release 3 0 Confidential SCT Converter Tool User s Guide Table of Contents vi Release 3 0 SCT Converter Tool User s Guide October 2003 Confidential Chapter 1 Introduction Installati n ER A E aA 1 4 IW e 1 4 Brief Overview of Scripts Created by the SCT Converter Tool CUACNVT 1 4 Creating the Convert Table lt gt 541 1 5 Creating the SQL Loader Control File TargetTable cvt ctl sss 1 7 Creating the Convert Script TargetTable convert sql sss 1 8 1 1 Chapter 1 Introduction 1 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 1 Introduction Introduction October 2003 Confidential In any SCT Banner conversion project the goal is to move client data from the legacy system to the Oracle tables in the SCT Banner system The SCT Converter Tool is a multi part self contained form CUACNVT that dynamically generates the basic scripts necessary to move data from legacy system flat ASCII files to Oracle tables The conversion script produced by the SCT Converter Tool also has the capability of converting legacy system values according to user defined specifications The SC
54. Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential gen id cnt gen id cnt 1 get new id number UPDATE SOBSEQN SET SOBSEQN MAXSEQNO SOBSEQN MAXSEQNO 1 SOBSEQN ACTIVITY DATE SYSDATE WHERE SOBSEQN FUNCTION OPEN PTI CURSOR FETCH PTI CURSOR INTO new 1d CLOSE PTI CURSOR update current record with change indicator of I and ntyp code LGCY UPDATE spriden cvt SET spriden change ind T code WHERE pidm cvt rec spriden pidm and spriden change ind IS NULL max rec max rec 1 cvt rec spriden 14 new 14 cvt rec spriden record 14 max rec nbr cvt rec spriden change ind NULL create record in spriden cvt with generated ID INSERT INTO SPRIDEN CVT SPRIDEN PIDM CONVERT PIDM SPRIDEN ID CONVERT ID SPRIDEN LAST NAME CONVERT LAST NAME SPRIDEN FIRST NAME CONVERT FIRST NAME SPRIDEN MI CONVERT MI SPRIDEN CHANGE IND CONVERT CHANGE IND SPRIDEN ENTITY IND CONVERT ENTITY IND SPRIDEN ACTIVITY DATE CONVERT ACTIVITY DATE SPRIDEN USER CONVERT USER SPRIDEN ORIGIN CONVERT ORIGIN SPRIDEN SEARCH LAST NAME CONVERT SEARCH LAST NAME SPRIDEN SEARCH FIRST NAME CONVERT SEARCH FIRST NAME SPRIDEN SEARCH MI CONVERT SEARCH MI SPRIDEN SOUNDEX LAST NAME CONVERT SOUNDEX LAST NAME SPRIDEN SOUNDEX FIRST NAME CONVERT SOUNDEX FIRST NAME SPRIDEN NTYP CODE CONVERT NTYP CODE SPRIDEN CVT STATUS SP
55. D s works after all the records have been converted and only creates a Generated ID record for those SPRIDEN CVT records with a status of that don t have a Generated ID This information can only be established after the records are converted and before they are inserted into SPRIDEN How It Works After the SPRIDEN CONVERT SQL script converts all data in the SPRIDEN CVT table all records with a status of converted as opposed to inserted or errored E are taken where the change indicator SPRIDEN CVT SPRIDEN CHANGE IND is null This is the current ID from the legacy system The script sets the SPRIDEN CVT SPRIDEN CHANGE IND change indicator to and the SPRIDEN NTYP CODE to LGCY to indicate this is the most current legacy ID The script then gets the next available Generated ID number from the SOBSEQN table and creates a new SPRIDEN CVT record using the Generated ID and copies all information from the converted legacy ID record When the SPRIDEN CONVERT SQL script is completed you are left with the legacy ID in the SPRIDEN CVT table The change indicator is set to SPRIDEN NTYP CODE of LGCY to be used by the F CVT GET PIDM function and a new Generated ID record in the SPRIDEN CVT table is also produced with the same pidm but the change indicator is NULL A Sample Run The listing below shows a sample run of the SPRIDEN CONVERT script using the Generated ID feature Please enter the
56. DM LEGACY VALUE IF SUBSTR converted val 1 3 ERR OR SUBSTR converted val 1 3 ORA THEN err msg Column conv failure converted_val RAISE column error END IF IF converted val IS NULL THEN err msg Missing required value RAISE column error END IF BEGIN spraddr rec SPRADDR PIDM TO NUMBER converted val October 2003 Release 3 0 D 19 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column_error END EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur tbl cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR ATYP CODE BEGIN cur col SPRADDR ATYP CODE legacy value spraddr rec convert atyp code CURCNVT Rules Column is Required Conversion function 15 specified No validation specified No default value converted_val null converted_val CURCVAL RNULL STVATYP LEGACY VALUE IF SUBSTR converted val 1 3 ERR OR SUBSTR converted_val 1 3 THEN err msg Column conv failure converted val RAISE column error END IF IF converted val IS NULL THEN err msg Missing required value RAISE column error END IF spraddr rec SPRADDR ATYP CODE converted val EXCEPTION WHEN column error THEN col status E process stat
57. DRIDGE GROVER BLUE P October 2003 Release 3 0 4 5 Confidential SCT Converter Tool User s Guide Chapter 4 Scripts Produced by the SCT Converter Tool This is a sample control file script spriden_cvt ctl generated by the converter tool for the SPRIDEN table AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This SQLLoader control file is used to load legacy data into the temporary conversion table SPRIDEN cvt This script was generated by the SCT Converter Tool AUDIT TRAIL END Load data Infile spriden_cvt dat APPEND INTO TABLE SPRIDEN_CVT FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY TRAILING NULLCOLS CONVERT PIDM CHAR CONVERT ID CHAR CONVERT LAST NAME CHAR CONVERT FIRST NAME CHAR CONVERT MI CHAR CONVERT CHANGE IND CHAR SPRIDEN RECORD ID SEQUENCE MAX I SPRIDEN CVT STATUS CONSTANT N The control file includes convert columns for the data fields specified on CUACNVT by the Load indicator in the correct order Note the loading of default values into the SPRIDEN CVT RECORD ID column and the SPRIDEN CVT STATUS columns Notice the Infile name The flat file must be re named to conform to this naming convention lt TargetTable gt _cvt dat The lt gt RECORD ID SEQUENCE and the lt gt STATUS columns also appear in the control file Values will be inserted in the load process The lt
58. DVET IND converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val null err msg END Beginning evaluation of column ISPBPERS LICENSE ISSUED DATE BEGIN cur col LICENSE ISSUED legacy value spbpers rec convert license issued date CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers rec CONVERT LICENSE ISSUED DATE BEGIN spbpers rec SPBPERS LICENSE ISSUED DATE TO DATE converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS LICENSE EXPIRES DATE BEGIN cur col SPBPERS LICENSE EXPIRES legacy value spbpers rec convert license expires date CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value e converted val null converted spbpers
59. E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END D ncc Beginning evaluation of column SPRADDR ZIP SA Uc a a EE C BEGIN cur col SPRADDR legacy value spraddr rec convert zip CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted null converted spraddr rec CONVERT ZIP spraddr rec SPRADDR ZIP converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur tbl cur jobid cur rec cur col legacy value converted val nullerr msg END D 24 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts Beginning evaluation of column ISPRADDR CNTY CODE BEGIN col SPRADDR CNTY CODE legacy value spraddr rec convert cnty code CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spraddr rec CONVERT CNTY CODE spraddr rec SPRADDR CNTY CODE converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column ISPRADDR NATN CODE BEGIN cu
60. L Enter STVETHN in the Entity field Entities should be named after the table for which the crosswalk is needed Perform a NEXT BLOCK function Cursor is in the Legacy Value field Enter the values from the data in the Legacy Value fields For this exercise you will need to discover the ethnic codes in the 1 2 3 M and enter their new SCT Banner equivalents in the training database where you are working and create some appropriate SCT Banner value fields equivalents During your conversion you may need to research the values that are being used by your functional users and get their assistance in creating crosswalks SAVE and EXIT the Crosswalk form Navigate to the Convert Function field and click the CONVERT FUNCTION button SELECT F CVT CURCVAL RNULL With your cursor in the Convert Function field click the View Function button When the Function Code and Parameters window appears click the EDIT PARAMETERS button Enter STVETHN as the entity Must be enclosed in single quotes Enter LEGACY VALUE in the LEGACY VALUE field No quotes Click the CONSTRUCT FUNCTION CALL button Click RETURN then SAVE and RETURN You will need to perform a NEXT BLOCK function to move to the column block of the form Navigate to the Load and Insert fields Check both Navigate to the SPBPERS MRTL CODE column Navigate to the Load Order field and change the 6 to 5 Navigate to the Validate Function field and click the VALID FUNC
61. MELIA KAY P 111111111 101101101 SMITH AMELIA KAY I P 222222222 51 FRANCIS P 222222222 222222222 CAMPESI MARYANNE S N P 333333333 WOODRUFF LUCILLE MARIE P 444444444 CAMPBELL JOSEPH ALEXANDER 555555555 PITTMANN MILDRED YATES P 666666666 BOYD WILLIAM GUERRY P 777777777 GLOVER SAVION TAP P 888888888 WINGFIELD WILLIAM DOUGLASS P 999999999 LENNON JOHN O P 000000000 ELDRIDGE GROVER BLUE P Generating SCT Banner PIDM s To generate SCT Banner pidms for new records users must use CVT AUTO function This function generates the SCT Banner PIDMs and dynamically updates the SOBSEQN sequence table after the assignment of each new pidm In addition to using the required spriden_cvt dat file layout users must edit the spriden_convert sql script to re order the data in the cursor This is the only occasion in which manual editing of any of the converter tool scripts is required If a NULL convert pidm is passed to the function then a pidm will be generated from SOBSEQN since this will signify the most current record If convert_pidm has a value this means either a NAME change or an ID change is in the extract then the pidm should not be generated from SOBSEQN Rather the pidm will be fetched from the current convert table spriden cvt where the convert pidm is equal to the convert id and the convert change ind is null In order for this method to work the curr
62. OUNDEX FIRST NAME BEGIN cur col SPRIDEN SOUNDEX FIRST legacy value spriden rec convert soundex first name CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spriden rec CONVERT SOUNDEX FIRST NAME spriden rec SPRIDEN SOUNDEX FIRST NAME converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val null err msg END Beginning evaluation of column SPRIDEN NTYP CODE Release 3 0 D 11 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts BEGIN cur col SPRIDEN NTYP CODE legacy value spriden rec convert ntyp code CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val CONVERT NTYP CODE spriden_rec SPRIDEN NTYP CODE converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END ERE i tia e eS e EIS Updating Temporary table with converted values LE LU Ur M EM ERE BEGIN UPDATE SPRIDEN CVT SET SPRIDEN PIDM spriden rec spriden pidm SPRIDEN ID spriden rec
63. PERS EYES CODE VARCHAR2 2 CONVERT EYES CODE VARCHAR2 2 SPBPERS CITY BIRTH VARCHAR2 20 CONVERT CITY BIRTH VARCHAR2 20 SPBPERS STAT CODE BIRTH VARCHAR2 3 CONVERT STAT CODE BIRTH VARCHAR2 3 SPBPERS DRIVER LICENSE VARCHAR2 20 CONVERT DRIVER LICENSE VARCHAR2 20 SPBPERS STAT CODE DRIVER VARCHAR2 3 CONVERT STAT CODE DRIVER VARCHAR2 3 SPBPERS NATN CODE DRIVER VARCHAR2 5 CONVERT NATN CODE DRIVER VARCHAR2 5 SPBPERS UOMS CODE HEIGHT VARCHAR2 4 CONVERT UOMS CODE HEIGHT VARCHAR2 4 SPBPERS HEIGHT NUMBER 2 CONVERT HEIGHT VARCHAR2 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix B Sample Table Creation Scripts October 2003 Confidential SPBPERS UOMS CODE WEIGHT VARCHAR2 4 CONVERT UOMS CODE WEIGHT VARCHAR2 4 SPBPERS WEIGHT NUMBER 4 CONVERT WEIGHT VARCHAR2 4 SPBPERS SDVET IND VARCHAR2 1 CONVERT SDVET IND VARCHAR2 1 SPBPERS LICENSE ISSUED DATE DATE CONVERT LICENSE ISSUED DATE VARCHAR2 9 SPBPERS LICENSE EXPIRES DATE DATE CONVERT LICENSE EXPIRES DATE VARCHAR 9 SPBPERS INCAR IND VARCHAR2 1 CONVERT INCAR IND VARCHAR2 1 SPBPERS WEBID RAW 1 CONVERT WEBID VARCHAR 1 SPBPERS WEB LAST ACCESS RAW 1 CONVERT WEB LAST ACCESS VARCHAR2 1 SPBPERS PIN DISABLED IND RAW 1 CONVERT PIN DISABLED IND VARCHAR2 1 SPBPERS ITIN NUMBER 9 CONVERT ITIN VARCHAR 9 SPBPERS RECORD ID NUMBER 8 CONSTRAINT SPBPERS PRIMARY KEY SPBPERS STATUS
64. PRIDEN PRIMARY KEY SPRIDEN STATUS VARCHAR2 1 SPRIDEN CVT JOB ID NUMBER 8 STORAGE INITIAL IM PCTINCREASE 100 MAXEXTENTS 50 Release 3 0 B 3 SCT Converter Tool User s Guide Appendix B Sample Table Creation Scripts B 4 Caution script includes a DROP TABLE statement The table including data is removed Run this script only if the data in the convert table is not needed Note Each convert table contains three other columns in addition to the convert columns 1 SPRIDEN RECORD ID holds a sequence number which identifies each row in the convert table 2 SPRIDEN CVT STATUS holds the record status New Converted or Error assigned during the conversion process It is initially populated with the value of N to indicate that the record 15 N ew and has not yet been processed This value will be updated when the convert script is run in the later stages of the process Other valid values for this field during the convert process are C onverted and E rrored A record with a status of C onverted has been successfully moved to the lt TargetTable gt A record with a status of E has one or more errors that must be corrected before movement to the lt TargetTable gt can occur An E rrored record will become a C onverted record after the error s have been corrected and the record has been processed via the convert script 3 SPRIDEN CVT JOB ID column is populated wit
65. R STAT CODE SPRADDR ZIP SPRADDR ACTIVITY DATE VALUES spraddr rec SPRADDR Spraddr rec SPRADDR ATYP CODE Spraddr rec SPRADDR SEQNO praddr rec SPRADDR FROM DATE praddr rec SPRADDR TO DATE praddr rec SPRADDR STREET LINEI Spraddr rec SPRADDR CITY Spraddr rec SPRADDR STAT CODE rec SPRADDR ZIP Spraddr rec SPRADDR ACTIVITY DATE rows_success_insert rows success insert 1 UPDATE SPRADDR CVT SET spraddr status WHERE SPRADDR CVT RECORD ID cur rec EXCEPTION WHEN OTHERS THEN err msg Error inserting into Banner table col status E cur col NULL RAISE END END IF D 32 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential Setting breakpoint values breakl value spraddr CONVERT PIDM break2_value spraddr CONVERT ATYP CODE EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg WHEN OTHERS THEN err num SQLCODE err msg SUBSTR SQLERRM 1 100 process status E p err cur owner cur 61 cur jobid cur rec cur col null null null err msg END IF col status E THEN TOWS errored rows errored 1 END IF END LOOP spraddr loop DBMS OUTPUT PUT LINE Number of Rows Converted in SPRADDR CVT lrows success convert
66. RADDR To create this script press the CONVERT TABLE SCRIPT button on the Tool Bar You will see the success message on the message line of the form This script should be run in SQL on the server to create the convert table in the host database spraddr_cvt ctl used by SQL Loader to load the data from the client s flat file spraddr_cvt dat to the convert table SPRADDR create this script press the SOL LOADER SCRIPT button on the Tool Bar You will see the success message on the message line of the form This script must be placed in the same directory on the server with the data file spraddr cvt dat Run SQL Loader on the server to load the data from the flat file to the convert table convert columns spraddr_convert sql moves the data from the convert columns to the target table columns within the convert table then moves the data from the target table columns in the convert table to the corresponding columns in the actual target table in the database To create this script press the CONVERSION SCRIPT button on the Tool Bar You will see the success message on the message line of the form This script should be run in SQL on the server Release 3 0 3 19 SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool Establishing Specifications for Conversion of SPBPERS Table The following steps will show how to use the SCT Converter Tool to set up specifications for converting d
67. RCHAR 5 CONVERT NATN CODE VARCHAR2 5 SPRADDR PHONE AREA VARCHAR 2 CONVERT PHONE AREA VARCHAR2 3 SPRADDR PHONE NUMBER VARCHAR 7 CONVERT PHONE NUMBER VARCHAR2 7 SPRADDR PHONE EXT VARCHAR2 4 CONVERT PHONE EXT VARCHAR2 4 SPRADDR STATUS IND VARCHAR2 1 CONVERT STATUS IND VARCHAR2 1 SPRADDR ACTIVITY DATE DATE CONVERT ACTIVITY DATE VARCHAR2 9 SPRADDR USER VARCHAR2 30 CONVERT USER VARCHAR 2 30 SPRADDR ASRC CODE VARCHAR2 4 CONVERT ASRC CODE VARCHAR2 4 SPRADDR DELIVERY POINT NUMBER 2 CONVERT DELIVERY POINT VARCHAR2 2 SPRADDR CORRECTION DIGIT NUMBER 1 CONVERT CORRECTION DIGIT VARCHAR2 1 SPRADDR CARRIER ROUTE VARCHAR2 4 CONVERT CARRIER ROUTE VARCHAR2 4 SPRADDR GST TAX ID VARCHAR2 15 CONVERT GST TAX ID VARCHAR2 15 SPRADDR REVIEWED IND VARCHAR2 1 CONVERT REVIEWED IND VARCHAR2 1 SPRADDR REVIEWED USER VARCHAR2 30 CONVERT REVIEWED USER VARCHAR2 30 SPRADDR CVT RECORD ID NUMBER 8 CONSTRAINT PK SPRADDR CVT PRIMARY KEY SPRADDR STATUS VARCHAR2 1 SPRADDR CVT JOB ID NUMBER 8 STORAGE INITIAL IM PCTINCREASE 100 MAXEXTENTS 50 spbpers_cvt_create sql AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This script creates the temporary conversion table SPBPERS for converting legacy data into SPBPERS This script was generated by the SCT Converter Tool AUDIT TRAIL END DROP TABLE SPBPERS CVT CREATE TABLE SPBPERS CVT SPBPERS NUMBER 8 CON
68. RIDEN CVT JOB ID SPRIDEN CVT RECORD ID VALUES cvt_rec SPRIDEN_ rec CONVERT PIDM new id cvt rec CONVERT ID rec SPRIDEN LAST NAME rec CONVERT LAST cvt rec SPRIDEN FIRST NAME rec CONVERT FIRST NAME cvt rec SPRIDEN rec CONVERT MI NULL cvt rec CONVERT CHANGE IND rec SPRIDEN ENTITY IND rec CONVERT ENTITY IND cvt rec SPRIDEN ACTIVITY DATE rec CONVERT ACTIVITY DATE cvt rec SPRIDEN USER cvt rec CONVERT USER Release 3 0 D 15 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts D 16 rec SPRIDEN ORIGIN rec CONVERT ORIGIN rec SPRIDEN SEARCH LAST NAME cvt rec CONVERT SEARCH LAST NAME rec SPRIDEN SEARCH FIRST NAME rec CONVERT SEARCH FIRST rec SPRIDEN SEARCH rec CONVERT SEARCH rec SPRIDEN SOUNDEX LAST NAME CONVERT SOUNDEX LAST NAME rec SPRIDEN SOUNDEX FIRST NAME re CONVERT SOUNDEX FIRST NAME evt rec SPRIDEN NTYP CODE rec CONVERT NTYP CODE rec SPRIDEN STATUS rec SPRIDEN JOB ID vt rec SPRIDEN RECORD 1 end of loop getting spriden cvt records that do not have generated 1d if commit counter commit frequency then commit commit counter 0 else commit counter commit counter 3 end if END LOOP DBMS OUTPUT PUT LINE Number of ids generat
69. SCT Converter Tool User s Guide SCT Application Practices Release 3 0 October 2003 Confidential Business Information This documentation is proprietary information of SCT and is not to be copied reproduced lent or disposed of nor used for any purpose other than that for which it is specifically provided without the written permission of SCT Prepared For Release 3 0 Prepared By SCT 4 Country View Road Malvern Pennsylvania 19355 United States of America Issued November 1999 Revised October 2003 This publication is intended to provide accurate information regarding SCT s software It is provided with the understanding that SCT is not engaged in rendering legal accounting or other professional services through the production of this publication Further SCT makes no claims that an institution s use of this software in accordance with this publication will insure compliance with applicable federal or state laws rules or regulations SCT recommends that organizations seek professional legal advice in order to determine that their policies and practices are in compliance with applicable laws rules or regulations Because of the nature of this material numerous hardware and software products are mentioned by name In most if not all cases these product names are claimed as trademarks by the companies that manufacture the products It is not our intent to claim these names or trademarks as our own Copyright 1
70. T Converter Tool Functions Editing Functions Online You may wish to create new functions to add to your database as you work through a conversion process You may add to your function library in two ways First you may choose to write the function script and compile it in the database instance in which you are working logged in as your conversion user The next time you open CUACNVT and look at functions your new function should appear on the list Or you may choose to edit an existing function online To do this click the Edit Function button on the Toolbar 2 This opens the Edit Function window where you can edit an existing function rename it compile it and add it to your function library This image demonstrates the procedure using the delivered F CVT_GET_PIDM function It has been edited given a new name F CVT GET PIDM TEST and recompiled under the new name This operates the same way as a save as feature in a text processing application in that the original object is preserved and a new one created with a different name and different properties Edit Function E Gl x BEGIN ft ft Functions AS ACID Compile CREATE OR REPLACE FUNCTION f get pidm id IN VARCHARZ ID to find pidm for RETURN VARCHARZ 15 pidm VARCHARZ 8 generated id cubctrl cubctrl gen id Purpose Look at current T4 get the spriden pidm if control file indicat
71. T Converter Tool has many features that allow you to customize the code of the scripts it produces through entries made on the form Some of these features include the ability to e Generate new SCT Banner ID numbers e Invoke functions that will perform a variety of modifications on the legacy data Decode legacy values using crosswalk form table e Insert user defined constants or default values into target table columns e Validate legacy values against system validation tables e Change the format of incoming date values Check errors and to separate and identify errored records for evaluation and correction When you have entered all your conversion specifications through the SCT Converter Tool form CUACNVT the SCT Converter Tool will dynamically generate scripts to be used in the conversion process The SCT Converter Tool generates the 3 following scripts 1 A script to create a convert table lt TargetTable gt _cvt_create sql The naming convention for all convert tables created is lt TargetTable gt _cvt where lt TargetTable gt is the actual SCT Banner table that will eventually contain the legacy data 2 Acontrol file used by the SQL Loader utility to load data from the flat ASCH file into the convert table lt TargetTable gt _cvt ctl where lt TargetTable gt is the actual SCT Banner table that will eventually contain the legacy data 3 Aconvert script used to move data from the convet table to the target table
72. THEN err_msg Missing required value RAISE column error END IF spriden rec SPRIDEN ID converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur tbl cur jobid cur rec cur col legacy value converted val null err msg END Beginning evaluation of column SPRIDEN LAST NAME BEGIN cur col SPRIDEN LAST legacy value spriden rec convert last name CURCNVT Rules Column is Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val CONVERT LAST IF converted_val IS NULL THEN err msg Missing required value RAISE column error END IF spriden_rec SPRIDEN LAST NAME converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val null err msg END Beginning evaluation of column SPRIDEN FIRST NAME BEGIN cur col SPRIDEN FIRST legacy value spriden rec convert first name CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value Sj converted val null converted val spriden rec CONVERT FIRST NAME spriden rec SPRIDEN FIRST NAME converted val EXCEPTION D 6 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appe
73. TION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRIDEN SEARCH LAST NAME Release 3 0 D 9 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts BEGIN cur col SPRIDEN SEARCH LAST legacy value spriden rec convert search last name CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted val spriden CONVERT SEARCH LAST NAME spriden rec SPRIDEN SEARCH LAST NAME converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPRIDEN SEARCH FIRST NAME BEGIN cur col SPRIDEN SEARCH FIRST legacy value spriden rec convert search first name CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted val spriden rec CONVERT SEARCH FIRST NAME spriden rec SPRIDEN SEARCH FIRST NAME converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur c
74. TION button SELECT F VALIDATE SINGLE from the list With your cursor in the Validate Function field click the View Function icon on the Toolbar Release 3 0 SCT Converter Tool User s Guide Confidential 3 23 Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Click the EDIT PARAMETERS button and enter the following Note OWNER TABLE and COLUMN parameters must be parameters enclosed in single quotes IN VALUE should not be enclosed in OWNER SATURN quotes TABLE STVMRTL COLUMN STVMRTL_CODE IN VALUE LEGACY VALUE Click the CONSTRUCT FUNCTION CALL button Click RETURN SAVE amp RETURN When the CUACNVT form displays perform a NEXT BLOCK function Verify that the Load and Insert boxes are checked for the SPBPERS MRTL CODE column Navigate to the SPBPERS RELG CODE column Navigate to the Load and Insert boxes UNcheck both There is no data for this field Navigate to the SPBPERS CODE column Navigate to There is no data for this field the Load and Insert boxes and UNcheck both Navigate to the SPBPERS SEX column Navigate to the Value List field and insert the following in this Any record having a legacy value other than one in the list will format F create error Naviegate to the SPBPERS_CONFID_IND UNcheck the There is no data for any of these fields Load
75. TREET converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR CITY BEGIN col SPRADDR legacy_value spraddr_rec convert_city CURCNVT Rules Column is Required Conversion function is not specified No validation specified No default value s converted val null converted spraddr rec CONVERT CITY October 2003 Release 3 0 D 23 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts IF converted_val IS NULL THEN err_msg Missing required value RAISE column error END IF spraddr_rec SPRADDR_ CITY converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur tbl cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR STAT CODE BEGIN col SPRADDR STAT CODE legacy value spraddr rec convert stat code CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spraddr rec CONVERT STAT CODE spraddr rec SPRADDR STAT CODE converted val EXCEPTION WHEN column error THEN col status E process status
76. UE variable here the INITCAP function will be applied to all legacy data values in the CONVERT LAST NAME field as they are moved to the SPRIDEN LAST NAME field during the conversion process For a look at the details of the spriden convert sql script refer to Appendix D Sample Conversion Scripts Navigate to the Load and Insert Options and check both Navigate to the Next Record SPRIDEN FIRST NAME Navigate to the Convert Fnctn field Enter the following INITCAP LEGACY VALUE Navigate to the Convert Fnctn field Enter the following INITCAP LEGACY_VALUE Navigate to the Load and Insert Options and check both Navigate to the Next Record SPRIDEN MI Navigate to the Convert Fnctn field Enter the following INITCAP LEGACY_VALUE Navigate to the Load and Insert Options and check both Navigate to the Next Record SPRIDEN CHANGE IND Navigate to the Load and Insert options and check both October 2003 Release 3 0 3 8 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Navigate to the Next Record This is one of the fields for which you can enter a default value of P Our SPRIDEN ENTITY IND sample data contains the data value for all records In your conversion if
77. URRVAL INTO cur jobid FROM DUAL DBMS OUTPUT ENABLE 100000 DBMS OUTPUT PUT LINE Beginning Conversion of Table spraddr DBMS OUTPUT PUT LINE Job Number cur jobid commit counter 0 lt lt spraddr_loop gt gt FOR spraddr_rec IN spraddr_cursor LOOP BEGIN spraddr_rec spraddr_cvt_record_id col status DELETE FROM curcerr WHERE curcerr table owner cur owner Release 3 0 SCT Converter Tool User s Guide October 2003 Confidential Appendix D Sample Conversion Scripts AND curcerr_table_name cur_tbl AND curcerr_record_id cur_rec IF process level THEN GOTO insert spraddr END IF IF NOT first row THEN break2 value lt gt spraddr ATYP CODE THEN err msg GEN RESET SEQNO SEQI IF SUBSTR err msg 1 3 ERR OR SUBSTR err msg 1 3 THEN DBMS OUTPUT PUT LINE Break 2 Error END IF END IF IF break value lt gt spraddr rec CONVERT PIDM THEN COMMIT err msg F GEN RESET SEQNO SEQI IF SUBSTR err msg 1 3 ERR OR SUBSTR err msg 1 3 THEN DBMS OUTPUT PUT LINE Break 1 Error END IF END IF END IF FIRST ROW FALSE Beginning evaluation of column ISPRADDR BEGIN cur col SPRADDR PIDM legacy value spraddr rec convert pidm CURCNVT Rules Column is Required Conversion function is specified No validation specified No default value converted_val null converted val F GET PI
78. VERT PIDM VARCHAR2 9 SPBPERS SSN VARCHAR2 9 Release 3 0 B 5 SCT Converter Tool User s Guide Appendix B Sample Table Creation Scripts B 6 CONVERT SSN VARCHAR2 9 SPBPERS BIRTH DATE DATE CONVERT BIRTH DATE VARCHAR2 9 SPBPERS LGCY CODE VARCHAR2 1 CONVERT LGCY CODE VARCHAR2 1 SPBPERS ETHN CODE VARCHAR2 2 CONVERT ETHN CODE VARCHAR2 2 SPBPERS CODE VARCHAR2 1 CONVERT CODE VARCHAR2 1 SPBPERS RELG CODE VARCHAR2 2 CONVERT CODE VARCHAR2 2 SPBPERS SEX VARCHAR2 1 CONVERT SEX VARCHAR2 1 SPBPERS CONFID IND VARCHAR2 1 CONVERT CONFID IND VARCHAR2 1 SPBPERS DEAD IND VARCHAR 1 CONVERT DEAD IND VARCHAR 2 1 SPBPERS VETC FILE NUMBER VARCHAR2 10 CONVERT VETC FILE NUMBER VARCHAR 10 SPBPERS LEGAL NAME VARCHAR2 60 CONVERT LEGAL NAME VARCHAR2 60 SPBPERS PREF FIRST NAME 2 15 CONVERT PREF FIRST NAME VARCHAR 15 SPBPERS NAME PREFIX VARCHAR2 20 CONVERT NAME PREFIX VARCHAR2 20 SPBPERS NAME SUFFIX VARCHAR2 20 CONVERT NAME SUFFIX VARCHAR2 20 SPBPERS ACTIVITY DATE DATE CONVERT ACTIVITY DATE VARCHAR2 9 SPBPERS VERA IND VARCHAR2 1 CONVERT VERA IND VARCHAR2 1 SPBPERS CITZ IND VARCHAR2 1 CONVERT CITZ IND VARCHAR2 1 SPBPERS DEAD DATE DATE CONVERT DEAD DATE VARCHAR 9 SPBPERS PIN RAW 1 CONVERT PIN VARCHAR2 1 SPBPERS CITZ CODE VARCHAR2 CONVERT CITZ CODE VARCHAR2 2 SPBPERS HAIR CODE VARCHAR2 2 CONVERT HAIR CODE VARCHAR2 2 SPB
79. _pidm spriden_id spriden_last_name spriden_first_name spriden_mi spriden change ind spriden entity ind DATA 111111111 SMITH AMELIA KAY P 111111111 101101101 5 222222222 SIMONEAUX MARYANNE FRANCIS P 222222222 222222222 CAMPESI MARYANNE S N P 333333333 WOODRUFF LUCILLE MARIE P 444444444 CAMPBELL JOSEPH ALEXANDER P 555555555 PITTMANN MILDRED YATES P 666666666 BOYD WILLIAM GUERRY P 1777777777 GLOVER SAVION TAP P 9888888888 WINGFIELD WILLIAM DOUGLASS P 999999999 L ENNON JOHN O P 000000000 ELDRIDGE GROVER BLUE P Note Please refer to the Loading Data into the SPRIDEN Table section of this document for a detailed explanation of the file layout for the spriden table spraddr cvt dat Legacy Fields legacy ID address type begin date end date street city state zip SCT Banner Fields spraddr pidm spraddr atyp code spraddr from date spraddr to date spraddr street linel spraddr city spraddr stat code spraddr zip DATA 111111111 L 20010101 20011212 3918 Wheat Street Columbia SC 29205 111111111 L 20020101 831 Timberlane Drive Rochester NY 32154 222222222 P 20010101 20011212 222 Sun Valley Drive Americus GA 39102 222222222 P 20010101 999 Tappenzee Street New Amsterdam NY 98765 333333333 333 Tugaloo Ave Columbia SC 29205 444444444 444 Fairway Oaks Drive Brunswick GA 31520 555555555 555 Broadway New 21222 666666666 666 Rodeo Drive Los Angeles CA 666
80. ables should use F CVT GET PIDM to retrieve the PIDM from SPRIDEN because in those cases you do want an error if the ID has not been converted Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter Tool The other step in converting name and ID records into SPRIDEN when the name and ID already exist is to use a new wrap up function F WRAP SPRIDEN EXISTS This function is put in the Wrap Up Function field in the top half of the CUACNVT form This function executes after all records have been converted and the status in SPRIDEN record is The function looks at the converted records SPRIDEN STATUS and if the pidm in the convert table SPRIDEN CVT exists in the SCT Banner Spriden table it sets the status to X so that the SPRIDEN record is not inserted into SCT Banner Ej Conversion Info CUACNYT 2 1 USING GENERATED IDs Table Owner SATURN Table Name SPRIDEN Commit Freq 250 Activity Date 29 SEP 2002 Error Action Continue processing row Initial Extent M fi Wrapup Fnetn Breakpoint into Data Input Format Fixed Length Delimited by a Reason for Data Chen Column SPRIDEN_PIDM Load Order 1 Required Convert NVL F_CVT_SPRIDEN_GET_PIDM spriden Activity Date 29 SEP 2002 Value List Default Default Action Yv Format Mask Length 8 Load M M
81. ach column in the target table specified in the Table Block Load Order This is a sequence number that denotes the position of the column in the legacy data file It can be changed if necessary to reflect the order of the data in the data file A change in this field will affect the order of the columns in the _cvt table and in the control file Required This indicates whether or not the column is a NOT NULL column in the target table Convert Fnctn This field allows you to insert function calls in the conversion script that will manipulate the data in that column You may insert any Oracle function such as UPPER INITCAP etc may use any of the delivered functions or may write new functions For detailed instructions on using the Convert Function feature of CUACNVT please refer to the Using the Convert Function Feature section of this document Activity Date This field indicates the date the conversion rule record was created or updated This is NOT the activity date column in the actual SCT Banner target table Activity date for the target table is populated separately through loading of the lt TargetTable gt _activity_date field Value List Allows you to specify a list of values against which legacy values can be validated Correct format is value 1 value2 value3 5 In the convert script this list follows the IN operator If value in the legac
82. add current It will also call a crosswalk function for emal code F CVT MAJR CODE This function is used to fetch the major if the program is known The SMRPRLE table must be populated October 2003 Release 3 0 H 5 Confidential SCT Converter Tool User s Guide Appendix SCT Converter Tool Functions Financial Function Name Description Alumni Aid Finance NAME INITCAP Translate the value passed in to have the initial character be upper case while the others X X X are lower case An exception is made for Mc or O where the third character will also be capitalized McDonald or O Keefe F CVT NULL MEDI NONNULL DISA Purpose Given the Disa Code and medi code 15 null find matching Medi Code F CVT NULL SECD ROLL IND Check if second major and return Y so rolling second major information F CVT PIDM TO ID This function is for use for alumni conversions Specify a pidm and this function returns X the corresponding ID F CVT PROGRAM Given the Major college and degree determine the program F CVT RETURN NULL This function will return a null value X X X F CVT SET SPRADDR SEQNO This function creates sequence numbers for spraddr records of the same address type X X X F CVT SET SPRTELE ADDR SEQNO set the sprtele addr seqno if a legacy value is no
83. ample if you wish to apply sequence numbers within each occurrence of pidm and term code and break reset the sequence number on a change in pidm and term code your breakpoints would be convert_pidm and convert_term_code Breakpoints should be set on the CONVERT columns rather than the target table columns Break Function the function that will re set the sequence based on a change in value in the breakpoint s established in the previous parameter Note There are 3 sequences delivered with the Converter Tool server side objects When using breakpoints to reset sequences be sure to use the same sequence number that was used to set the initial sequence Data Input Format Specify here the format of the legacy data file Options are Fixed Length or Delimited Delimited is the default value If data is delimited users must enter a value in the Delimiter field before proceeding Reason for Data Change Available in future version Initial Extent Allows users to set the initial extent for the convert table After entering all necessary data in the Table Block SAVE the changes Perform NEXT BLOCK function to move the to Column Block The column block is automatically populated with all columns from the target table specified in the Table Block Release 3 0 2 9 SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool Breakpoint Window Table Owner 7 Table Name SPRADDR 7
84. and if there is no data nor any place holder for data in your legacy data file you may UNcheck both Load and Insert If there is data in one record for this column or if there is a function that will apply data to this column you must check the Insert box When all specifications for conversion have been entered SAVE your changes You have now entered all the necessary specifications for conversion into any target table The next step is to use the features of the converter tool to produce the following 3 scripts e lt TargetTable gt _cvt_create sql creates the convert table lt TargetTable gt _cvt lt TargetTable gt _cvt ctl used by SQL Loader to load the data from the client s flat file lt TargetTable gt _cvt dat to the convert table TargetTable convert sql copies the data from the convert columns to the target table columns within the convert table then moves the data from the target table columns in the convert table to the corresponding columns in the actual target table in the database October 2003 Confidential Release 3 0 SCT Converter Tool User s Guide F 6 Appendix Using the SCT Converter Tool Using CUACNVT to produce the lt TZargetTable gt _cvt_create sql script With the cursor in the Table Block for the table Press the CONVERT TABLE SCRIPT button If you are using Internet Native forms and writing to your database server no dialog box will appear If
85. and insert a For this exercise our data is comma delimited For your conversion you comma have the option of using comma delimited data files or fixed length data files This field should be populated to reflect the file layout of your data SAVE your changes As you save changes and Navigate to the next block the column block is populated with columns from the SPRIDEN table NEXT BLOCK CUACNVT Column To see all columns in the table after moving to the Column Block with your Block cursor in the Column field move through the records with the NEXT RECORD button or by using the down arrow Navigate to the SPRIDEN PIDM column Navigate to the Convert field and click the CNVERT FNCTN button SELECT AUTO from the list October 2003 Release 3 0 3 5 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS With your cursor in the Convert Fnctn field press the VIEW FUNCTION button on the Tool Bar Function Code and Parameters Window The View Function icon is the icon to the left of the Pencil Please be patient It takes a few seconds for this window to appear You will see the Function Code and Parameters window It shows the text of the function create script and has three buttons across the top of the window above the function text The
86. ata into the SCT Banner SPBPERS table Note This exercise will demonstrate the use of the Load Order feature and the F VALIDATE SINGLE function Sample legacy data for this file includes the following values in this order pidm ssn birth date marital code values Y M D W S ethn code values 1 2 3 M sex values F M N ACTIONS STEPS FORM PROCESS NOTES amp HINTS Open CUACNVT and log in CUACNVT Login your converter username Password your convert user password Database your database Insert a new record CUACNVT Table Record Insert on the Menu OR Block Press the F6 key OR Use the down arrow key to Navigate to a blank record Enter the name of the table owner SATURN Navigate to the Next Record Enter the name of the table SPBPERS Navigate to the Error Action field Accept the default Error Action Continue Processing Row SAVE your changes NEXT BLOCK CUACNVT Column Block Cursor is in the SPBPERS PIDM field Navigate to the Convert Fnctn field Press the CONVERT FNCTN button October 2003 Release 3 0 3 20 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS SELECT GET from the list The assumption is that the legacy data has already been loaded into the SPRIDEN table This function uses the legacy ID to ma
87. ate this script press the SOL LOADER SCRIPT button on the Tool Bar You will see the success message on the message line of the form This script must be placed in the same directory on the server with the data file SPBPERS cvt dat Run SQL Loader on the server to load the data from the flat file to the convert table convert columns e SPBPERS convert sql moves the data from the convert columns to the target table columns within the convert table then moves the data from the target table columns in the convert table to the corresponding columns in the actual target table in the database To create this script press the CONVERSION SCRIPT button on the Tool Bar You will see the success message on the message line of the form This script should be run in SQL on the server October 2003 Release 3 0 3 25 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool October 2003 Release 3 0 3 26 Confidential SCT Converter Tool User s Guide Chapter 4 Scripts Produced by the SCT Converter Tool CAUCNVT Create Convert Table Script TargetTable7 create sql esses Create SQL Loader Script lt gt _ Create Convert Script lt TargetTable7 convert sql esee Running the Convert Script ccs ee Beles e ei etre a eee Answering the oue Re URDU
88. ble Name SPRIDEN Commit Freq 250 Activity Date 29 SEP 2002 Error Action Continue processing row Initial Wrapup Fnetn Breakpoint info Data Input Format C Fixed Length Delimited by Reason tot Wate Fields Description Table Owner Enter the name of the owner of the target table SATURN FIMSMGR TAISMGR etc Table Name Enter the name of the target table Commit Freq Enter the number of rows for the commit interval for the convert 66 22 script A commit will be performed every rows during the run of the target table convert sql script Activity Date The system date defaults here Error Action The two options are Continue processing row default Terminate processing of row Wrap up Function Allows you to insert a function into the conversion script that will accomplish additional processing after all rows are processed For example in Academic History conversions it would be possible to insert a function that would calculate g p a and insert it into the proper column 2 8 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter October 2003 Confidential Fields Description Breakpoint Info Commonly used with sequences The window has two parameters that must be entered Breakpoint s the column s on which the break is to occur for setting a breakpoint function For ex
89. bpers rec CONVERT UOMS CODE WEIGHT spbpers rec SPBPERS UOMS CODE WEIGHT converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS WEIGHT BEGIN cur col SPBPERS WEIGHT legacy value spbpers rec convert weight CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spbpers_rec CONVERT_WEIGHT BEGIN spbpers_rec SPBPERS WEIGHT TO_NUMBER converted_val October 2003 3 0 D 49 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts D 50 EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column_error END EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur tbl cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPBPERS SDVET IND BEGIN col SPBPERS SDVET legacy value spbpers rec convert sdvet ind CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers_rec CONVERT SDVET IND spbpers_rec SPBPERS S
90. called Scripts Produced by the SCT Converter Tool The TargetTable7 job id column holds a unique number which is used in naming the log files created for each run of the convert script allowing the log files not to be overwritten each time the convert process is run The lt TargetTable gt _cvt_create sql script should be run in SQL on the server in the correct database instance to create the convert table Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 1 Introduction October 2003 Confidential Creating the SQL Loader Control File TargetTable cvt ctl A second script created by the converter tool 1s the loader script or control file that is used by the SQL Loader utility to load the legacy data into the convert columns in the lt TargetTable gt _cvt table To indicate which columns should be included in the control file the user can check the Load indicator on the CUACNVT form The resulting control file will contain a listing of the convert columns that parallel the target table columns selected on the CUACNVT form and marked with the Load indicator Also included are the TargetTable convert record id and the TargetTable status columns NOTE Check the Load indicator only for columns for which there is legacy data If there is no legacy data for a target table column or if the column will be populated using a database function do not check the Load in
91. cified No validation specified No default value converted_val null converted_val spraddr rec CONVERT STREET LINE spraddr rec SPRADDR STREET LINEI converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPRADDR STREET LINE2 D 22 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts BEGIN cur col SPRADDR_STREET_LINE2 legacy value spraddr rec convert street line2 CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spraddr rec CONVERT STREET LINE2 spraddr rec SPRADDR STREET LINE2 converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR STREET LINE3 BEGIN cur col SPRADDR STREET legacy value spraddr rec convert street line3 CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spraddr CONVERT STREET LINE3 spraddr_rec SPRADDR_ S
92. cise L MA P PR Please verify that these values L P and enter their new SCT Banner equivalents in the exist in your training database During your conversion you may appropriate SCT Banner value fields MA PR need to research the values that are being used by your functional users and get their assistance in creating crosswalks SAVE and EXIT the Crosswalk form Navigate to the Convert Function field and click the CONVERT FUNCTION button SELECT F CVT CURCVAL RNULL With your cursor in the Convert Function field click the View Function button When the Function Code and Parameters window appears click the EDIT PARAMETERS button Enter STVATYP as the entity Must be enclosed in single quotes Enter LEGACY VALUE in the LEGACY VALUE field No quotes Click the CONSTRUCT FUNCTION CALL button Click RETURN then SAVE and RETURN You will need to perform a NEXT BLOCK function to move to the column block of the form Navigate to the Load and Insert fields Check both Navigate to the Next Record SPRADDR_SEQNO Navigate to the Convert Function field and click the CONVERT FUNCTION button SELECT F GEN SEQUENCE Release 3 0 October 2003 Confidential SCT Converter Tool User s Guide 3 16 Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS With your cursor in the Convert Function field click
93. current record must be processed first by the conversion program spriden convert sql meaning the cursor must have an ORDER BY clause so that records with null change indicators are processed first For example order by convert change ind desc F CVT CHK SPRADDR SEQNO This function finds out the sequence numbers for spraddr records of a given address type X X X F CVT COLL CODE Purpose This function can be used to fetch the college code into a student record from the SMRPRLE table when only the program code 1s known F CVT DEGC CODE This function is used to fetch the degree code from the SMRPRLE table when only the program code is known F CVT DEPT CODE Given the Major college and degree determine the department F CVT DFT ACTIVITY DATE CHK This function will default the activity date to the system date if the legacy value is null Otherwise it will format and pass the legacy value F CVT EITHER CHECK This function is to ensure that we do not populate both the PIDM field and the other X legacy field on some of the ALUMNI tables F CVT ENTITY CODE Given the Validation table name create an Entity code for use in xwalking X X X F CVT FIRST TERM This function is used to find the first term of attendance The SGBSTDN table must be populated F CVT GET APRCATG PIDM This function is for use for alumni conversions Once the legacy ID is put in X aprcatg cvt pidm we can find a constituents and organizations pidm by looking up the legacy id in aprcat
94. cvt ctl The second script created by the converter tool is the loader script or control file that is used by the SQL Loader utility to populate the lt TargetTable gt _cvt convert table with the flat file data The control file that is produced will contain a listing of the convert columns that parallel the lt TargetTable gt columns selected on the CUACNVT form by checking the Load indicator as well as the TargetTable convert record id and the lt TargetTable gt _cvt_status columns Note If you have data in the flat file that you want to load into the convert table you must check the Load indicator on the CUACNVT form In our sample conversion this is the flat file that is loaded by the loader process File Name spriden cvt dat Legacy Fields legacy ID legacy ID last name first name middle name change ind entity ind SCT Banner Fields spriden id spriden id spriden last spriden first name spriden mi spriden change ind spriden entity ind DATA 111111111 8 1 111111111 101101101 SMITH AMELIA KAY L P 222222222 SIMONEAUX MARYANNE FRANCIS P 222222222 222222222 CAMPESI MARYANNE S NP 333333333 WOODRUFF LUCILLE MARIE P 444444444 CAMPBELL JOSEPH ALEXANDER 555555555 PITTMANN MILDRED YATES P 666666666 BOYD WILLIAM GUERRY P 777777777 GLOVER SAVION TAP P 888888888 WINGFIELD WILLIAM DOUGLASS P 999999999 LENNON JOHN O P 000000000 EL
95. cvt record id FROM spriden cvt max rec nbr spriden cvt spriden record 14 CURSOR PTI CURSOR IS SELECT SOBSEQN SEQNO PREFIX LPAD TO CHAR SOBSEQN MAXSEQNO 8 0 FROM SOBSEQN WHERE SOBSEQN FUNCTION ID new id VARCHAR2 9 gen id cnt INTEGER cursor only gets spriden cvt records that do not have an ID generated already i e those PIDMS where there is not a record with a ntype code LGCY and change indicator of I in SPRIDEN CVT or the actual BANNER SPRIDEN table CURSOR SPRIDEN CVT RECORDS IS SELECT FROM spriden cvt x WHERE status C AND spriden change ind IS NULL AND not exists SELECT X from spriden cvt y where x spriden pidm y spriden and change ind and spriden code LGCY SPRIDEN RECORDS orowtype CURSOR SPRIDEN L RECORDS IS SELECT rowid FROM spriden cvt WHERE pidm rec spriden pidm AND spriden change ind T AND spriden ntyp code record SPRIDEN L RECORDSP rowtype 1 record count INTEGER BEGIN gen id cnt 0 get max record number OPEN max record nbr FETCH max record nbr INTO max rec nbr CLOSE max record nbr select all the converted records where the change 1nd is null and ID not already generated OPEN SPRIDEN CVT RECORDS LOOP FETCH SPRIDEN CVT RECORDS INTO cvt rec IF SPRIDEN _RECORDS NOTFOUND THEN EXIT END IF D 14 Release 3 0 October 2003 SCT
96. d are provided to help validate data that has been loaded into a temporary table prior to running the convert script This appendix lists the scripts and how they are used e Address_match_report sql e Address type match different street report sql e Create get rules sgl e Cvt chkfkeys sql e Cvt chkxwlk sql e status sgl e Czkrule sql e Czkrull sql e Id duplicate sql e Id last name duplicate sql e Jd ssn duplicate sql Spraddr duplicates sql e Spraddr duplicate checking steps txt e Spriden duplicates sql e Spriden duplicate checking steps txt e Sprtele duplicate checking steps txt e Sprtele duplicates sql e Telephone and type match report sql Utility Script Usage October 2003 Confidential SPRIDEN duplicate checking steps STEP 1 Run spriden_duplicates sql This script will check the spriden table to determine if the record in the spriden cvt table already exists in spriden It should be run after loading spriden cvt and spbpers cvt The user 18 prompted for the legacy date format of the convert birth date field the spbpers cvt table If it is determined the id may already exist in SCT Banner the spriden cvt status is set to a specific value which is checked by the following three SQL scripts STEP 2 Run id_duplicate sql This report lists the records in cvt where convert id already exists in SPRIDEN but last names do not match Release 3 0 G 3 SCT Converter Tool U
97. d_val null converted spbpers CONVERT UOMS CODE HEIGHT spbpers_rec SPBPERS UOMS CODE HEIGHT converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column SPBPERS HEIGHT BEGIN col SPBPERS HEIGHT legacy value spbpers rec convert height CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value D 48 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts converted val null converted spbpers rec CONVERT HEIGHT BEGIN spbpers rec SPBPERS HEIGHT TO NUMBER converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS UOMS CODE WEIGHT BEGIN col SPBPERS UOMS CODE WEIGHT legacy value spbpers rec convert uoms code weight CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value xj converted val null converted sp
98. dat are included in Appendix A Sample Data Files Used in Examples Assumptions Users will be generating new SCT Banner ID s Spriden cvt dat data file will be configured according to guidelines outlined in Section 2 18 of this user manual October 2003 Release 3 0 3 3 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool October 2003 Confidential Release 3 0 SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool Establishing Specifications for Conversion of SPRIDEN Table The following steps will show how to use the SCT Converter Tool to set up specifications for converting data into the SCT Banner SPRIDEN table ACTIONS STEPS FORM PROCESS NOTES amp HINTS Open CUACNVT and log in CUACNVT Login your_convert_username Password your_convert_user password Database your_database Perform an INSERT RECORD function CUACNVT Table Block Enter the name of the table owner SATURN If you are practicing or if you are in training the owner of your table may be your user name your login or some other owner If you are setting up specifications for your conversion use the SATURN owner Navigate to the Next Field Enter the name of the table SPRIDEN Navigate to the Error Action field Accept the default Error Action Continue Processing Row Navigate to the Delimited By field
99. dicator In our sample conversion this is the flat file that contains the data to be loaded into the spriden convert table by the loader process spriden cvt dat Legacy Fields legacy ID legacy ID last name first name middle name SCT Banner Fields spriden id spriden id spriden last name spriden first name spriden mi spriden change ind spriden entity ind 11111111 9 111111111 101101101 SMITH AMELIA KAY I P 222222222 SIMONEAUX MARYANNE FRANCIS P 222222222 222222222 CAMPESI MARYANNE S N P 333333333 WOODRUFF LUCILLE MARIE P 444444444 CAMPBELL JOSEPH ALEXANDER 555555555 PITTMANN MILDRED YATES P 666666666 BOYD WILLIAM GUERRY P 777777771 GLOVER SAVION TAP P 888888888 WINGFIELD WILLIAM DOUGLASS P 999999999 LENNON JOHN O P 000000000 ELDRIDGE GROVER BLUE P Note Please refer to the Loading Data into the SPRIDEN Table section of this document for a detailed explanation of the file layout for the spriden table This is a sample control file spriden_cvt ctl generated by the converter tool for the SPRIDEN table after selecting the desired columns on the CUACNVT form by checking the Load indicator AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This SQLLoader control file is used to load legacy data into the conversion table SPRIDEN This script was generated by the SCT Converter Tool AUDIT TRAIL END Load data Infi
100. e 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter Tool LOGGING tle SA AL eed 2 3 The SCT Converter Tool QUAGCNVT 3 RW ee ee 2 4 Parts of the CUACNVT iii 2 4 The CUACNVT Toolbar tne tee e de ee ent 2 4 The Gontrol se erede eee aede 2 7 Using Generated IDs iier bor ORO 2 7 Control Form File Location Feature esee 2 8 hes Tabl Block 2 eta BO mr em an mte 2 8 Breakpoint Window cantiere i tb iie it vies 2 10 The Column Block 2 E been tias 2 11 Using the Convert Function Feature of 2 12 Applying an Oracle Function to a Column sse 2 12 Using a Delivered Function essere nennen enne 2 13 The Function Code and Parameters Window c cccscessesssceseceseceseceeeeeecaeeeneeeeeeneeeeeeees 2 14 Using the Crosswalk Feature of the Converter Tool CUACVAL eene 2 16 Using Options on the Crosswalk 2 nennen 2 17 Using Crosswalk Values in the Conversion 2 19 Loading Data into the SPRIDEN Table ssesssseseseeeeeeeene ens 2 20 Generating SCT Banner 2 21 Using the Generated ID Feature of the Converter Tool
101. e convert script can be run in three modes Convert mode copies data from convert columns to lt TargetTable gt columns in convert table and transforms according to transformation rules set up by user Insert mode inserts converted data into lt TargetTable gt columns in actual target table Both mode converts and inserts data into the target table in the same transaction The convert script lt TargetTable convert sql incorporates all the specifications made in the CUACNVT form for columns marked with the Insert indicator such as functions default values for columns decodes crosswalk values etc When the lt TargetTable gt _convert sq script is run on the server in Convert mode it copies the values from the convert columns in the convert table to the lt TargetTable gt columns in the convert table and applies all functions default values etc to the lt TargetTable gt columns You can then observe any errors that occur when the legacy data is moved to the lt TargetTable gt columns in the convert table where the constraints are enabled Records that cannot be successfully moved to the lt TargetTable gt columns in the convert table are marked with an error indicator E in the TargetTable7 status column and displayed in the Conversion Errors window CUICERR of the CUACNVT form Error feedback is also given online after the convert script has run Users can also view the error
102. e loaded with the first value in the client flat file which 1s the legacy ID SSN in this example Recall in the sample conversion that the column length was modified from 8 to 9 on CUACNVT This allows the SQL Loader utility to load the legacy ID with a length of 9 into the convert pidm column During the conversion process through the use of a function this ID will be used to match against the spriden which at this point in the conversion process must already have been loaded SPBPERS General Person Biographic Demographic Table spbpers cvt ctl AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This SQLLoader control file is used to load legacy data into the temporary conversion table SPBPERS cvt This script was generated by the SCT Converter Tool AUDIT TRAIL END Load data Infile spbpers cvt dat APPEND INTO TABLE SPBPERS CVT FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY TRAILING NULLCOLS CONVERT PIDM CHAR CONVERT SSN CHAR CONVERT BIRTH DATE CHAR Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix Sample SQL Loader Scripts CONVERT MRTL CODE CHAR CONVERT ETHN CODE CHAR CONVERT SEX CHAR SPBPERS CVT RECORD ID SEQUENCE MAX 1 SPBPERS CVT STATUS CONSTANT N October 2003 Release 3 0 C 5 Confidential SCT Converter Tool User s Guide Appendix Sample SQL Loader Scripts C 6 Release 3 0 October 2003 SCT Converter Tool User
103. e processed N ew records C onverted records E rrored records These values are stored in the TargetTable7 status field in the convert table lt TargetTable gt _cvt This column holds the indicators for the status of the record Valid values are N New C Converted Inserted E Error All records are assigned a default value of N New when the convert table is loaded using the lt TargetTable gt _cvt ctl loader script The value is changed to C when the record is successfully copied to the lt TargetTable gt column the convert table through the convert process The value is changed to E if the record cannot be copied to the lt TargetTable gt column in the convert table because of a constraint or other error Explanation of Record Type Prompts N If you are running the convert script for the first time you should choose N ew When data is loaded into the convert table for the first time all records are marked with an indicator of N for New Choosing N at this prompt instructs the script to use the values in the convert columns apply any special functions or instructions that you indicated on CUACNVT when you created the script It then copies those values into the lt target_table gt columns in the convert table C The second choice at the first prompt C onverted will instruct the script to select only those records marked with Records are marked with
104. ecords are loaded during the conversion For a detailed explanation of generating IDs during a conversion please see the Using the Generated ID Feature of the Converter Tool section of this document October 2003 Release 3 0 2 7 Confidential SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool Control Form File Location Feature Users are able to specify a default directory for Converter Tool output files on the Control Form Local Machine In client server mode the local machine will be the hard drive of the user s local computer In Internet Native mode the local machine will be the forms server where the Converter Tool client objects are located Since this may create some difficulties with access in some situations it is recommended that users write to the database server by choosing the Database Server option Database Server This option uses the Oracle UTL_FILE package which reads and writes files to the database server In order to use this option the user must have an entry in the init ora file By choosing Database Server the scripts are written directly to the database server thus eliminating the need to FTP files from the user s computer to the database server The Table Block The top block of CUACNVT is the Table Block To begin using the form choose the target table into which you will be inserting legacy data USING GENERATED IDs Table Owner SATURN Ta
105. ed to char gen 1d cnt end of generated id code end of generated id code END end of generated 1d block END DBMS OUTPUT PUT LINE Number of Rows Converted in SPRIDEN CVT lrows success convert DBMS OUTPUT PUT LINE Number of Rows Inserted into spriden rows success insert DBMS OUTPUT PUT LINE Number of Rows That Errored rows errored end of program DBMS OUTPUT PUT LINE CHR 10 Completed Processing of EXCEPTION WHEN OTHERS THEN err num SQLCODE err msg SUBSTR SQLERRM 1 100 process status E p err cur owner cur tbl cur jobid cur rec cur col null null null eerr msg DBMS OUTPUT PUT LINE Number of Rows Converted in SPRIDEN lrows success convert DBMS OUTPUT PUT LINE Number of Rows Inserted into spriden rows success insert DBMS OUTPUT PUT LINE Number of Rows That Errored rows errored DBMS OUTPUT PUT LINE CHR 10 Completed Processing of END SELECT TO CHAR sysdate DD MON Y Y Y Y HH24 MI STOP TIME FROM DUAL COMMIT PROMPT Querying CURCERR table for errors col msg for a35 hea ERROR MESSAGE WORD col colname for a30 hea COLUMN col jobno for 99999 hea JOB col norecs for 999999 hea COUNT set wrap on Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts select distinct curcerr_message msg curcerr_column_name colna
106. ed to fetch pidms Please note that the function will return a match to a legacy id where the change indicator is set to and the spriden ntyp code is set to LGCY if the Generate IDs option is chosen on the CUACTRL control form when setting up rules for converting the SPRIDEN table The length of the convert pidm column must be adjusted to accommodate legacy IDs longer than 8 characters To adjust the length of any column go to the Length field in the column block of CUACNVT and change the number All convert columns are VARCHAR2 datatypes For more information on the structure of the convert table and an explanation of the convert columns please see Scripts Produced by the SCT Converter Tool To continue click the VIEW FUNCTION button on the Tool Bar response is not immediate please wait a few seconds The Function Code and Parameters window appears Release 3 0 2 13 SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool The Function Code and Parameters Window This window allows you to view the text of the function enter parameters and constructthe function call When the window is first opened the text of the function will appear The buttons across the top are RETURN EDIT PARAMETERS and CONSTRUCT FUNCTION CALL When you click the EDIT PARAMETERS button you will see the dialog box shown in the image and the EDIT PARAMETERS button changes to Hide PARAMETERS as shown i
107. ent record must be processed first by the conversion program spriden convert sql meaning the cursor must have an ORDER BY clause so that records with null change indicators are processed first ex order by convert change ind desc Steps for loading the SPRIDEN table using generated PIDMs and generated IDs are outlined in the next section of this document Further steps for loading the SPRIDEN table are included in the Sample Conversions section of this document Release 3 0 2 21 SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool Using the Generated ID Feature of the Converter Tool The Control Form provides the ability to check whether or not you are using Generated ID s If the Use Generated IDs check box is marked and saved upon returning to the main converter tool form CUACNVT a message is displayed at the top of the form to indicate the use of Generated ID s Ej Conversion Info CUACNVT 2 1 USING GENERATED IDs Table Owner Table BRRDEN Commit Freq 250 Activity Date 29 2002 Error Action Continue processing row m Initial Extent ooh Wrapup Fnetn ee XXE Breakpoint info Data Input Format C Fixed Length Delimited by Reason for Data Chan Column SPRIDEN_PIDM Load Order 1 Required 2 Convert CVT GET PIDM LEGACY VALUE Activity Date 29 SEP 2002 Value List Valid De
108. ersion function is not specified No validation specified No default value converted_val null converted_val spbpers rec CONVERT SSN spbpers rec SPBPERS SSN converted val EXCEPTION WHEN column error THEN col status E process status p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS BIRTH DATE D 36 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts BEGIN cur col SPBPERS BIRTH DATE legacy value spbpers rec convert birth date CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spbpers_rec CONVERT_BIRTH_DATE THIS SECTION ILLUSTRATES USING THE DATE FORMAT MASK BEGIN spbpers rec SPBPERS BIRTH DATE TO DATE converted val YYYYMMDD FORMATTING FOR DATES EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS LGCY CODE BEGIN col LGCY CODE legacy value spbpers rec convert lgcy code CURCNVT Rules
109. ert table and not to insert values into the target SCT Banner table This will allow you to evaluate and correct any errored records while not touching the actual SCT Banner tables D Insert records that were previously converted successfully Choose this option after you have run the script at least once in Convert mode to check for errors Choosing I at this prompt instructs the script to insert converted values into the target SCT Banner table in the database B Both Convert and Insert records into actual SCT Banner table Choosing B at this prompt will instruct the script to move the values from the convert columns in the convert table to the lt TargetTable gt columns in the convert table and then to insert the values from the lt TargetTable gt columns in the convert table into the actual lt TargetTable gt of these transactions are performed in just one run of the convert process Errored records will remain in the convert table marked with TargetTable status of E When the script completes its run you will see the following output e Number of Rows Converted in lt TargetTable gt _CVT e Number of Rows Inserted into lt TargetTable gt e Number of Rows That Errored Completed Processing of lt TargetTable gt You are told how many rows were converted into the lt TargetTable gt columns in the convert table how many rows were inserted into the actual lt TargetTable gt and how many rows had e
110. es using generated IDs look at the legacy ID spriden change ind I and SPRIDEN NTYP CODE LGCY to get the pidm We are assume for conversion legacy data that requires a pidm vill use the legacy ID We are also assuming there is only one spriden id with a change indicator of I and name type code of LCGY NO ERRORS For more discussion on database functions and the converter tool refer to The Function Code and Parameters Window in Chapter 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix F Using the SCT Converter Tool SCT Converter Tool 8 0000 Using CUACNVT to produce the lt TargetTable gt _cvt_create sql script Using CUACNVT to produce the lt TargetTable gt _cvt ctl Using CUACNVT to produce the lt TargetTable gt _convert sq 1 Appendix F Using the SCT Converter F 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix F Using the SCT Converter Tool SCT Converter Tool Instructions The following steps will show how to use the CUACNVT form to set up specifications for any conversion ACTIONS STEPS FORM PROCESS NOTES amp HINTS Open CUACNVT and log in CUACNVT Login yo
111. every current legacy ID record was created Please enter the record type which should be processed N ew records C onverted records E rrored records Include C Please enter the disposition to which the records should be processed C onvert records only Dnsert records only records were previously converted successfully B oth convert and records into spriden Disposition I October 2003 Release 3 0 2 25 Confidential SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool 2 26 will be creating generated id records in SPRIDEN CVT for any converted records i e records with spriden_cvt_status C Beginning Conversion of Table spriden Job Number 67 Number of ids generated 528 Number of Rows Converted in SPRIDEN_CVT 0 Number of Rows Inserted into spriden 528 Number of Rows That Errored 0 Completed Processing of spriden PL SQL procedure successfully completed Commit complete Note If non current ID s are being loaded for historical information in addition to the most current legacy ID the number of records produced will not be exactly twice the number of records loaded into SPRIDEN_CVT Multiple Iterations of Converting SPRIDEN Converting name and ID information into SPRIDEN from one system is relatively straightforward 1 Load SPRIDEN CVT and run the SPRIDEN CONVERT SQL script 2 Fix any errors along the way and everything is in SPRIDEN But
112. fault Default Action Format Mask Length 8 Load M M The Generated ID Feature includes three parts e Converter Tool Control Form e Convert Script for SPRIDEN F CVT GET PIDM function Use the following steps 1 Mark the Use Generated IDs indicator on the Conversion Tool Control Form CUACTRL and save your work 2 Setup SPRIDEN conversion and load data into the SPRIDEN CVT table 3 Create the SPRIDEN CONVERT script should contain the necessary code to generate all expected ID s 2 22 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter October 2003 Confidential 4 Change the order by clause for the spriden_cursor FROM order by spriden_cvt_record_id TO order by convert change ind desc Edit the spriden convert script as follows CURSOR spriden cursor IS SELECT FROM spriden CVT WHERE spriden cvt status records in erder bs spriden evt reeord id order by convert change ind desc 5 Ensure the LGCY validation value exists in the GTVNTYP table If it does not exist add it as new validation value where the code should be set to LGCY and the description can be set to Legacy ID or any other desired description 6 Execute the SPRIDEN CONVERT SQL script to convert all Name and ID information in SPRIDEN CVT 7 Execute the SPRIDEN CONVERT SQL script to take rows from SPRIDEN
113. function is not specified October 2003 Release 3 0 D 43 Confidential SCT Converter Tool User s Guide Appendix D Sample Conversion Scripts No validation specified No default value converted_val null converted_val spbpers CONVERT CITZ IND spbpers rec SPBPERS CITZ IND converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column ISPBPERS DEAD DATE BEGIN cur col DEAD legacy value spbpers rec convert dead date CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value xj converted val null converted spbpers CONVERT DEAD DATE BEGIN spbpers rec SPBPERS DEAD DATE TO DATE converted val y EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS PIN BEGIN cur col SPBPERS legacy value spbpers rec convert pin CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value s converted
114. g with a single record per pidm The legacy ID 15 stored in the convert pidm column It works well so long as there is one record per pidm If you convert all their alumni tables after converting one donor category per pidm this should work fine You can then convert the remaining donor categories into APRCATG F CVT GET NEXT APPL NO To return the next application number in a series of Admissions applications gt gt gt October 2003 3 0 H 4 Confidential SCT Converter Tool User s Guide Appendix SCT Converter Tool Functions Financial Function Name Description Alumni Aid Finance CVT GET Purpose Look at current id to get spriden pidm if control file indicates using X X X generated IDs look at the legacy ID spriden change SPRIDEN NTYP CODE LGCY to get the pidm We are assume for conversion legacy data that requires a pidm will use the legacy ID We are also assuming there is only one spriden id with a change indicator of and name type code of LGCY If not using generated ID s the most current spriden id should be the same as the legacy ID so look for a change indicator of null F CVT GET SPRIDEN CVT PIDM This function is for use for alumni conversions Once the pidm corresponding to the X legacy ID has been identified and logged stored in SPRIDEN CVT SPRIDEN PIDM we can then look for a pidm corresponding to a
115. h a unique sequence number for each run of the conversion process This job number is used in naming the log files that are created for each run of the convert script allowing the log files not to be overwritten each time the convert process 15 run spraddr cvt create sql AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This script creates the temporary conversion table SPRADDR for converting legacy data into SPRADDR This script was generated by the SCT Converter Tool AUDIT TRAIL END DROP TABLE SPRADDR CVT CREATE TABLE SPRADDR CVT SPRADDR PIDM NUMBER CONVERT PIDM VARCHAR2 9 SPRADDR ATYP CODE VARCHAR2 2 CONVERT ATYP CODE VARCHAR2 2 SPRADDR SEQNO NUMBER CONVERT SEQNO VARCHAR2 2 SPRADDR FROM DATE DATE CONVERT FROM DATE VARCHAR2 9 SPRADDR TO DATE DATE CONVERT TO DATE VARCHAR 9 SPRADDR STREET VARCHAR2 30 CONVERT STREET LINE VARCHAR2 30 SPRADDR STREET LINE2 VARCHAR2 30 CONVERT STREET LINE2 VARCHAR2 30 SPRADDR STREET LINE3 VARCHAR2 30 CONVERT STREET LINE3 VARCHAR2 30 SPRADDR CITY VARCHAR2 20 CONVERT CITY VARCHAR2 20 SPRADDR STAT CODE VARCHAR2 3 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix B Sample Table Creation Scripts October 2003 Confidential CONVERT STAT CODE VARCHAR2 3 SPRADDR ZIP VARCHAR2 10 CONVERT ZIP VARCHAR 10 SPRADDR CNTY CODE VARCHAR2 5 CONVERT CNTY CODE VARCHAR2 5 SPRADDR NATN CODE VA
116. h report lis which is a listing of the records that will not be loaded since an exact match was found STEP 3 RUN address type match different street report sql Will produce address type match different street report lis which is a listing of the records that match on address type with a previously loaded SCT Banner record and both are active SCT Banner only allows one active address per address type so a decision needs to be made as to which record should remain active Manual updating of SCT Banner or Legacy records 15 required for those that need to be made inactive If Legacy records have been changed then the extracts must be re run SPRTELE duplicate checking steps STEP 1 Run sprtele_duplicates sql This program will check that the legacy telephone number has not been loaded previously for a specific telephone type It will flag SPRTELE CVT STATUS with a D in the temporary conversion table SPRTELE CVT If the telephone number does not exist but other phone numbers of the same telephone type have been loaded preiously the program will adjust the sequence number in CONVERT SEQNO stored in the temporary conversion table SPRTELE CVT Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix Utility Scripts STEP 2 RUN telephone match report sql This program will produce a report named telephone match report lis which will list the phone numbers that were found to be duplicate infor
117. he existing SPRIDEN ID inserted previously to get the pidm and insert it into the SPRADDR PIDM field in the convert table and subsequently into the SPRADDR PIDM field in the SPRADDR table Navigate to the Load field in the Column Block and check Checking the LOAD button indicates that you want this column to the Load indicator appear in the SQL Loader script because you have legacy data in the flat file for this column to be loaded into the convert table October 2003 Release 3 0 3 15 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Navigate to the Insert indicator in the Column Block and Checking the INSERT button indicates that you want this column to check the Insert indicator appear in the conversion script to be converted to the target table columns in the convert table and subsequently inserted into the actual target table Navigate to the next record the SPRADDR ATYP CODE CUACNVT Column column Block Click the Crosswalk icon to display the Crosswalk form CUACVAL Enter STVATYP in the Entity field Entities should be named after the table for which the crosswalk is needed Perform a NEXT BLOCK function Cursor is in the Legacy Value field Enter the two values from the data in the Legacy Value fields For this exer
118. he text of the script refer to Appendix C Sample SQL Loader Scripts Place this script and the lt TargetTable gt _cvt dat data file in the same directory on the server and run SQL Loader to load the data into the convert table Consult with your DBA for the correct command for executing SQL Loader A sample command sqlldr userid your convert username your convert user password control lt TargetTable gt _cvt ctl October 2003 Release 3 0 F 8 Confidential SCT Converter Tool User s Guide Appendix F Using the SCT Converter Tool Using CUACNVT to produce the TargetTable convert sql script With the cursor in the Table Block for the table Press the CONVERSION SCRIPT button If you are using Internet Native forms and writing to your database server no dialog box will appear If you are using client server mode and have chosen Local Machine on the Control Form the following dialog box will appear N Target Directory El x oes The box will be populated with the directory value you entered on the Control Form Press OK When the script is complete you will see the following message on the message line of the form Script successfully written to c convert lt TargetTable gt _convert sql To view the text of the script refer to Appendix D Sample Conversion Scripts Once you have evaluated the data in the convert table convert columns run this script for the N ew records in C onvert mode If there are
119. how is the SCT Converter Tool used to convert name and ID information from multiple systems especially when the same people can be found in multiple systems For example some students are included in the Human Resource system as employees but will also be found in the Student system If you need to convert your Human Resource system first how do you only create SPRIDEN records for those students who are not in the Human Resource system i e have not been put into SPRIDEN yet There are two things to change in the SCT Converter Tool on the SPRIDEN table rules First change the Convert Function for SPRIDEN PIDM Change this function to reflect NVL F CVT SPRIDEN GET PIDM spriden rec convert id F CVT AUTO PIDM LE GACY VALUE Nesting these functions within the Oracle NVL function says if F CVT SPRIDEN GET PIDM returns a NULL value i e no matching ID found in the SCT Banner SPRIDEN table then use F CVT AUTO PIDM to create a new pidm So after the SPRIDEN CONVERT SQL function is run the SPRIDEN PIDM column in the SPRIDEN CVT table will have a pidm that is either an existing pidm from the SCT Banner SPRIDEN table F CVT SPRIDEN GET PIDM or a new pidm F CVT AUTO PIDM F CVT SPRIDEN GET PIDM is a new function with the 2 0 version of the tool It is different from the original F CVT GET PIDM because it returns a NULL for the pidm instead of an error when looking for a pidm in the SCT Banner SPRIDEN table All other t
120. i D 34 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Table of Contents Appendix E SCT Converter Tool Functions Viewing EUctioris uoce recette ER REI P Ee e ERE E 3 Editing Functions Online eese eene ener ener E 4 Appendix F Using the SCT Converter Tool SCT Converter Tool Instructions eese enne enne nennen rennen ener enne F 3 Using CUACNVT to produce the lt TargetTable gt _cvt_create sql F 7 Using CUACNVT to produce the lt TargetTable gt _cvt ctl F 8 Using CUACNVT to produce the lt gt convert sql F 9 Appendix G Utility Scripts Natil Directory Scripts zee ave ihe PEE ene dean G 3 Utility Script eeu ish eis G 3 SPRIDEN duplicate checking 2 240 1 0 G 3 SPRADDR duplicate checking G 4 SPRTELE duplicate checking steps sese G 4 Extracting Data from the Audit 1 G 5 Validating Data for Crosswalked Columns essen G 5 Validating Data for Columns with Foreign Key G 5 Appendix H SCT Converter Tool Functions SCT
121. idm id IN VARCHAR2 ID to find pidm for RETURN VARCHAR2 IS pidm VARCHAR2 8 generated id cubctrl cubctrl gen id ind96type BEGIN Purpose Look at current id to getthe spriden pidm if control file indicates using generated IDs After constructing the function call click the RETURN button You will have three choices Do you want to exit this window and return to the main window if Rollback Return Choosing Save amp Return will return your cursor to the Table Block of CUACNVT You must perform a NEXT BLOCK function to Navigate to the column block and resume data entry For more detailed information about the functions delivered with the Converter Tool review the on line documentation for each function or review the same function documentation in the cvt_genlib sq file in the install directory of the Converter Tool October 2003 Release 3 0 2 15 Confidential SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool Using the Crosswalk Feature of the Converter Tool CUACVAL Clicking the CROSSWALK button on the Tool Bar provides access to the Conversion Crosswalk Values window which allows set up of an equivalency table between legacy values and SCT Banner values ERI Developer Forms Runtime Web n Action Edit Query Block Record Field Help Window Legacy Value Legacy Description Banner Value Banner Description
122. ified No validation specified No default value xj converted val null converted spraddr rec CONVERT PHONE NUMBER spraddr rec SPRADDR PHONE NUMBER converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR PHONE EXT BEGIN cur col SPRADDR PHONE EXT legacy value spraddr rec convert phone ext CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spraddr rec CONVERT PHONE EXT spraddr rec SPRADDR PHONE EXT converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END D 26 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts Beginning evaluation of column ISPRADDR_STATUS_IND BEGIN col SPRADDR STATUS IND legacy value spraddr rec convert status ind CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spraddr rec CONVERT STATUS IND spraddr rec SPRADDR STATUS IND converted val
123. ify that the Load and Insert boxes are checked Block Navigate to the SPBPERS BIRTH DATE column Navigate to the Format Mask field Enter YYY YMMDD Verify that the Load and Insert boxes are checked Navigate to the SPBPERS LGCY CODE column Navigate to the Load and Insert boxes UNcheck both Navigate to the SPBPERS ETHN CODE column Navigate to The Load Order feature allows you to establish the order of the the Load Order field Change the number from 5 to 6 columns in the SQL Loader control file This could be helpful 1f for some reason the data elements in your data file were in an order different from that required by the SCT Banner table In this exercise our data file places the ethnic code legacy value ahead of the marital code legacy value forcing us to reverse the load order for these two fields Changing the load order does not affect the order ot the columns in the Insert phase into the SCT Banner tables nor does it affect the architecture of the convert table NOTE Once you save your changes and re enter the record for SPBPERS the columns will have been re ordered the way you have specified Release 3 0 SCT Converter Tool User s Guide Confidential 3 22 Chapter 3 Conversion Example Using The SCT Converter Tool October 2003 ACTIONS STEPS FORM PROCESS NOTES amp HINTS Click the Crosswalk icon to display the Crosswalk form CUACVA
124. ill check Insert for all remaining columns If you choose not to edit out unnecessary columns in the beginning of this process you may indicate with the Insert indicator whether or not they are to be converted inserted Navigate to the Column field and perform a NEXT CUACNVT Column RECORD function to display the SPRIDEN ID Block column Navigate to the Load field and check the box Checking the LOAD box indicates that you want this column to appear in the SQL Loader script because you have legacy data in the flat file for this column to be loaded into the convert table Navigate to the Insert field check the box Checking the INSERT box indicates that you want this column to appear in the conversion script to be converted to the target table columns in the convert table and subsequently inserted into the actual target table Navigate to the next record SPRIDEN LAST NAME Navigate to the Convert Fnctn field names in the legacy data file are in ALL CAPS It is appropriate here to use Oracle s INITCAP function to change the three SPRIDEN name fields to mixed case Release 3 0 October 2003 Confidential SCT Converter Tool User s Guide 3 7 Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Enter the following This will cause the legacy data to change from ALL CAPS to Mixed Case INITCAP LEGACY VALUE during the conversion process By using the LEGACY VAL
125. in the cvt_genlib sql file on the database server You can also view a function source code by placing the cursor on the field that contains the function name and pressing the VIEW FUNCTION button EN Function Code and Parameters F CVT AUTO PIDM 4 Return Edit Parameters FUNCTION F CVT AUTO PIDM pidm in 2 October 2003 Confidential hd maxseqno NUMBER S RETURN 2 5 M R TAN T XXXXXXXX eM ODIFY SPRIDEN CONVERT SQL ADD ORDER BY CLAUSE Purpose Used to generate verify that pidm exists for a record If a NULL convert pidm is passed to the function then a pidm will be generated from since this will signify the most current record If convert pidm has a value this means either a NAME change or an ID change is in the extract then the pidm should not be generated from sobseqn Rather we will get the pidm from the current temporary table spriden where the convert pidm is equal to the convert id and the convert change ind is null In order for this method to work the current record must be processed first by the conversion program spriden convert sql meaning the cursor must have an ORDER BY clause so that records with null change indicators are processed first ex order by convert change ind desc Release 3 0 SCT Converter Tool User s Guide Construct Function Call Appendix SC
126. indicate that you wish the current column to appear in the SQL Loader script The columns marked with the load indicator should be only those that have values in the legacy data flat file Insert Allows you to indicate that you wish the current column to appear in the Conversion Script to be inserted into the target table The columns marked with the insert indicator should be all those which will be inserted into the convert columns and subsequently into the target table columns An inserted column will not necessarily be a loaded column inserted values could come from defaults or from functions as well as from legacy data Using the Convert Function Feature of CUACNVT The Convert Fnctn field allows you to insert function calls in the convert script that will manipulate the data in a selected column during the conversion process You may insert any baseline Oracle function such as UPPER INITCAP DATE etc use any of the functions delivered with the converter tool or you may write new functions To use new or existing functions not delivered with the Converter Tool you need only to compile the function s in the database where you are working under the owner you are using to do the conversion Your function name should appear in the list of functions the next time you log in to the Converter Tool Applying an Oracle Function to a Column Insert the function and its parameters directly into the
127. into Banner table col status E cur col NULL RAISE END END IF EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur tbl cur jobid cur rec cur col legacy value converted val nullerr msg WHEN OTHERS THEN err num SQLCODE err msg SUBSTR SQLERRM 1 100 process status E p err cur owner cur tbl cur jobid cur rec cur col null null null err msg END IF col status E THEN rows_errored rows errored 1 END IF if commit counter commit frequency then commit commit counter 0 else commit counter commit counter 1 end if END LOOP spbpers loop DBMS OUTPUT PUT LINE Number of Rows Converted in SPBPERS_ CVT lrows success convert DBMS OUTPUT PUT LINE Number of Rows Inserted into spbpers rows success insert DBMS OUTPUT PUT LINE Number of Rows That Errored rows errored e nd of program DBMS OUTPUT PUT LINE CHR 10 Completed Processing of spbpers EXCEPTION WHEN OTHERS THEN err num SQLCODE err msg SUBSTR SQLERRM 1 100 Release 3 0 D 55 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts process status E p err cur owner cur tbl cur jobid cur rec cur col null null null eerr msg DBMS OUTPUT PUT LINE Number of Rows Converted in SPBPERS CVT lrows success convert DBMS OUTPUT PUT LINE Number of Rows Inserted into spb
128. ion on the last academic history table shrtckg 1f only institutional records and shrtrce if inst and transfer records are loaded F CVT AFBCAMP NAME This function is used in an Alumni conversion to determine if the Campaign Name is X longer than 30 characters F CVT AMRSTAF SOLICIT IND This function is to find the ATVSTFT SOLICIT IND for setting on the AMRSTAF X record F CVT AORCONT SEQNO This function 15 to find the next Organization Contact sequence number from the X AORCONT table F CVT APRACLD LEAD DESC This function 15 to find the STVLEAD DESC value for setting the Alumni Activity X Leadership field APRACLD LEAD CODE F CVT APRACLD SEQNO This function is to find the next sequence number for the Alumni Activity Leadership X APRACLD record F CVT APRADEG SEQNO This function is to find the next degree sequence number X F CVT APRCHLD SEQNO This function is to find the next child sequence number from the APRCHLD table X F CVT APRCHLD XREF This function is to ensure that we only populate the XREF field if the PIDM field is NOT X null on the APRCHLD table F CVT APRCSPS XREF This function is to ensure that we only populate the XREF field if the PIDM field is NOT X null on the APRCSPS table F CVT APREHIS ATYP This function is to find the SPRADDR record in order to set the CODE on the X APREHIS record F CVT APREHIS SEQNO This function is to find the SPRADDR record in order to set the SEQNO on the X APREHIS record F CVT APRMAIL ATYP This f
129. ion specified No default value 3 converted val null converted spraddr rec CONVERT USER spraddr rec SPRADDR USER converted val EXCEPTION WHEN column error THEN col status E process status p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val null err msg END Beginning evaluation of column ISPRADDR ASRC CODE BEGIN col SPRADDR ASRC CODE legacy value spraddr rec convert asrc code CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spraddr CONVERT ASRC CODE spraddr_rec SPRADDR_ASRC_CODE converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR DELIVERY POINT BEGIN cur col SPRADDR DELIVERY POINT legacy value spraddr rec convert delivery point CURCNVT Rules Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spraddr rec CONVERT DELIVERY POINT BEGIN spraddr_rec SPRADDR DELIVERY POINT
130. ions for a particular value in any option column NEXT BLOCK Enter the legacy value that you wish to translate the SCT Banner value and in the Option 1 Column enter the first campus value that is required for a correct translation of the address code Add row for each address code campus code combination For this example the correct setup of the crosswalk table is displayed in the following image October 2003 Release 3 0 2 17 Confidential SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool 0 Developer Forms Runtime Web 151 xl Action Edit Query Block Record Field Help Window a Entity STVATY Legacy Value Legacy Description Banner Value Banner Description Option 1 Option 2 Option 3 Mailing o ____ B T es oe aS ee a Dal omer Local 2 Mating Campus co campus Summer _ EE Add to Validaton Table FRM 40400 Transaction complete 4 records applied and saved Record 4 7 Notice the two sets of records which have options In each set the campus codes of B and are used to set the extra condition needed for an accurate translation of values into SCT Banner 2 18 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of SCT Converter Tool October 2003 Confidential U
131. l Note The command to invoke SQL Loader sqlldr may vary from platform to platform Please check with your DBA to get the correct command Create Convert Script lt TargetTable gt _convert sql The third script produced by the converter tool is the convert script This script is run on the server after the legacy data from the flat file has been loaded into the convert columns of the convert table At this point you have included any functions variables decodes or default values on CUACNV T necessary to manipulate the data so that it is compatible with the lt TargetTable gt constraints Additionally all appropriate columns to be inserted have been coded as such with the Insert indicator on the form The convert script lt TargetTable convert sql generated by the converter tool incorporates all the specifications set up on the CUACNVT form and applies them as the convert script is run The convert script can be run in Convert mode or Insert mode Running it in Convert mode copies the legacy data from the convert columns of the convert table to the lt TargetTable gt columns of the convert table performing any specified data transformation during processing Running the script in Insert mode inserts the values from the lt TargetTable gt columns in the convert table into the actual lt TargetTable gt columns of the target table There is the option for the convert script to both Convert a
132. l spraddr CONVERT CARRIER ROUTE spraddr_rec SPRADDR_ CARRIER ROUTE converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR GST TAX ID BEGIN col SPRADDR GST TAX 10 legacy value spraddr rec convert gst tax id CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted val spraddr rec CONVERT GST TAX ID spraddr rec SPRADDR GST TAX ID converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR REVIEWED IND BEGIN cur col SPRADDR REVIEWED IND legacy value spraddr rec convert reviewed ind CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spraddr CONVERT REVIEWED IND spraddr_rec SPRADDR REVIEWED IND converted val Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential EXCEPTION WHEN column_error THEN col
133. l see the record detail the Oracle error message and more You may also get error information from the CURCERR table while at the SQL gt prompt This is the structure of the CURCERR table Column Data Type Constraints CURCERR_TABLE NAME Varchar2 30 CURCERR CVT IDENTIFIER Number 3 CURCERR RECORD ID Number 8 CURCERR COLUMN NAME Varchar2 30 CURCERR LEGACY VALUE Varchar2 100 CURCERR BANNER VALUE Varchar2 100 CURCERR ERRNO Number 6 CURCERR MESSAGE Varchar2 300 CURCERR ACTIVITY DATE Date CU RCERR TABLE OWNER Release 3 0 SCT Converter Tool User s Guide Varchar2 30 Chapter 4 Scripts Produced by the SCT Converter Tool 4 12 Release 3 0 SCT Converter Tool User s Guide October 2003 Confidential Appendix A Sample Data Files Used in Examples ASCII Data Files Used in Conversion Examples 2 2 A 3 cte eR Aoi P ea nee 3 spraddr cvt dat tes RE in een A 3 Spbpers Re 4 Appendix A Sample Data Files Used in Examples A 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix Sample Data Files Used in Examples ASCII Data Files Used in Conversion Examples spriden_cvt dat Legacy Fields legacy ID legacy ID last name first name middle name change indicator entity indicator SCT Banner Fields spriden
134. last name is current If not current last name this field should contain previous last name and the spriden change ind field should contain N CONVERT FIRST NAME Current first name if first name is current If not current first name this field should contain previous first name and the spriden change ind field should contain N CONVERT MI Current middle name if middle name is current If not current middle name this field should contain previous middle name and the spriden change ind field should contain N CONVERT CHANGE IND NULL if record is current If not current record this field should contain T if the ID is not current and N if the name is not current CONVERT ENTITY IND P if record is a person record C if record is a non person record CONVERT ACTIVITY DATE optional for data file Can be legacy maintenance date can be inserted as sysdate or default date during conversion CONVERT USER Should be some identifiable conversion user such as CONVERT Typically populated with default value using Converter Tool default value field CONVERT ORIGIN optional Typically populated with default value of CONVERSION using Converter Tool default value field 2 20 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter October 2003 Confidential Sample spriden_cvt dat file layout 111111111 SMITH A
135. le spriden_cvt dat APPEND INTO TABLE SPRIDEN_CVT FIELDS TERMINATED OPTIONALLY ENCLOSED BY TRAILING NULLCOLS CONVERT PIDM CHAR Release 3 0 1 7 SCT Converter Tool User s Guide Chapter 1 Introduction 1 8 CONVERT ID CHAR CONVERT LAST NAME CHAR CONVERT FIRST NAME CHAR CONVERT MI CHAR CONVERT CHANGE IND CHAR SPRIDEN RECORD ID SEQUENCE MAX I SPRIDEN CVT STATUS CONSTANT N The control file includes convert columns for the legacy data fields from the flat file in the correct order as specified on the CUACNVT form with the Load indicator The control file and the data source file should be moved to the same directory on the database server SQL Loader should be run on the server from the directory where both files reside For detailed instructions on using SQL Loader to load the convert tables refer to Appendix F Instructions for Using the SCT Converter Tool Additionally refer to the Oracle Server Utilities documentation for more information about using SQL Loader Note Disable Archive Log Mode when using SQL Loader to load data to a production database Archive Log Mode impedes performance of large data loads Creating the Convert Script TargetTable convert sql A third script produced by the SCT Converter Tool is the conversion script This script is run on the server after the legacy data from the flat file has been loaded into the convert columns of the convert table Th
136. legacy ID by utilizing looking for a row with the specified legacy id in SPRIDEN CVT CONVERT 1 and return the pidm in SPRIDEN CVT SPRIDEN PIDM F CVT GET SPRIDEN LAST NAME Look at current or previous ID s to get the distinct last name The parameter for this X X X function will be tablename rec convert pidm where tablename is the name of the table you are working in This will furnish the legacy ID to the function which will then use the ID to match to the current spriden id to return the last name associated with the ID F CVT GET VALUE Feed in a column you would like to retrieve along with a table you are retrieving from X X X and it will return the value from that table Optionally you can specify up to four where constraints entering each where column with a where value to ensure that only one row is returned Example call which retrieves the first name from SPRIDEN where the last name is Smith and the ID is 12340 DECLARE my value VARCHAR2 2000 BEGIN dbms output enable my value f get value spriden first name spriden spriden last name Smith spriden 1d 123405 DBMS OUTPUT PUT LINE my value END F CVT GOREMAL DISP WEB This function 15 to find if any of the Banner Web modules exists so we can set the X X X GOREMAL DISP WEB IND field F CVT GOREMAL EMAL CODE This function is to find if person already has a pre existing GOREMAL record for the X X X EMAL CODE before attempting to
137. legacy value VARCHAR2 2000 process status VARCHAR2 1 err num NUMBER S Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential col_err VARCHAR2 100 cur rec NUMBER 8 cur col VARCHAR2 30 rows errored NUMBER 8 0 rows success convert NUMBER 8 0 rows success insert NUMBER 8 0 commit counter NUMBER 8 commit frequency NUMBER 8 250 converted val VARCHAR2 2000 err msg VARCHAR2 300 first row BOOLEAN TRUE column error EXCEPTION process level VARCHAR2 1 UPPER amp process level records in VARCHAR2 1 UPPER amp records 1 break1 value VARCHAR2 30 break2 value VARCHAR2 30 break3 value VARCHAR2 30 seq NUMBER S 0 seq2 NUMBER S 0 seq3 NUMBER S 0 CURSOR spbpers_cursor IS SELECT FROM spbpers CVT WHERE spbpers cvt status records in order by spbpers cvt record 14 BEGIN IF process level AND records in lt gt C THEN DBMS OUTPUT PUT LINE TIf inserting only you must choose to include converted records only GOTO end of program END IF SELECT cubenvt sequence CURRVAL INTO cur jobid FROM DUAL DBMS OUTPUT ENABLE 100000 DBMS OUTPUT PUT LINE Beginning Conversion of Table spbpers DBMS OUTPUT PUT LINE Job Number cur jobid commit counter 0 lt lt spbpers_loop gt gt FOR spbpers_rec IN spbpers_cursor LOOP BEGIN cur
138. lue doesn t it inserts it into the crosswalk table CURCVAL It inserts the same value in the LEGACY VALUE column and BANNER VALUE column You should then use the converter tool crosswalk form CUACVAL to review the crosswalk entities for the table you are checking Example start cvt chkxwlk SATURN SSBSECT Validating Data for Columns with Foreign Key Constraints Many columns have legacy values that are not crosswalked but still have a foriegn key constraint a validation table There is a script cvt_chkfkeys sql to look for these values and insert them into the validation table It takes two parameters the SCT Banner table owner and the SCT Banner table name Example start cvt chkfkeys SATURN SSBSECT October 2003 Release 3 0 G 5 Confidential SCT Converter Tool User s Guide Appendix G Utility Scripts G 6 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix SCT Converter Tool Functions SCT Converter Tool Functions alphabetically by system Appendix SCT Converter Tool Functions H 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix SCT Converter Tool Functions SCT Converter Tool Functions alphabetically by system Financial Function Name Description Alumni Aid Finance F CALC INST GPA This function should be used as a wrap up funct
139. mation based on telephone type Extracting Data from the Audit Tables If you turn on the auditing feature in the control form CUACTRL perform the following steps to extract the rules changes from the CUBCNVT AUDIT CUBCNVT AUDIT VALUES CURCNVT AUDIT and the CURCNVT AUDIT VALUES tables in the form of update insert or delete scripts 1 Log in as sctcvt or what ever the db login is that made the changes 2 Install the 2 dbprocs czkrule sql and czkrull sql under your convert user schema 3 Run the dbproc to create the rule extract scripts by typing execute czkrule p update rules 4 Runthecreate get rules sql in the ctool server util directory It will create a get rules sql 5 Run get rules sql This will create separate scripts for the CUBCNVT and CURCNVT table for each table owner that had changes to the rules in the CUACNVT form Now you have the rules changes that have been made since the auditing feature was turned on Validating Data for Crosswalked Columns For many columns a legacy value is being crosswalked using the function F CVT CURCVAL and the table CURCVAL In many cases the legacy value is missing from the crosswalk table The script is cvt_chkxwlk sql checks this after the data is loaded into the temp table It takes two parameters the SCT Banner table owner and the SCT Banner table name It verifies that all the data in the CONVERT columns of the temp table exist in the CURCVAL table for the crosswalk If a va
140. me curcerr_cvt_identifier jobno count norecs from curcerr where curcerr table name SPRIDEN group by curcerr_message curcerr_column_name curcerr cvt identifier SPOOL OFF UNDEFINE process level UNDEFINE records in SPRADDR Address Table spraddr_convert sql Process spraddr_convert sql Generated 30 Sep 2002 09 13 AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This script translates legacy values into Banner values and inserts Banner values into spraddr This script was generated by the SCT Converter Tool AUDIT TRAIL END COLUMN file id NEW VALUE spool file NOPRINT SET VERIFY OFF SET ECHO OFF SELECT spraddr lcubenvt sequence NEXTVAL log file id FROM DUAL SPOOL amp spool file PROMPT PROMPT Please enter the record type which should be processed PROMPT N ew records PROMPT C onverted records PROMPT E rrored records ACCEPT records in CHAR PROMPT Include PROMPT PROMPT Please enter the disposition to which the records should PROMPT be processed PROMPT C onvert records only PROMPT Insert records only records were previously converted successfully PROMPT B oth convert and insert records into spraddr ACCEPT process level CHAR PROMPT Disposition PROMPT SET SERVEROUTPUT ON SIZE 500000 PROMPT PROMPT PROMPT will be creating generated id records PROMPT in SPRIDEN for any converted records PROMPT i e records with spriden cvt
141. mn error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted msg END Beginning evaluation of column SPBPERS WEB LAST ACCESS BEGIN cur col WEB LAST ACCESS legacy value spbpers rec convert web last access CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted spbpers CONVERT WEB LAST ACCESS spbpers_rec SPBPERS WEB LAST ACCESS converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS PIN DISABLED IND BEGIN cur col SPBPERS PIN DISABLED IND legacy value spbpers rec convert pin disabled ind CURCNVT Rules Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spbpers CONVERT DISABLED IND spbpers_rec SPBPERS PIN DISABLED IND converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur ow
142. n the following image ra Function Code and Parameters F GET PIDM 4 gt Construct Function Call Hide Parameters FUNCTION f_cvt_get_pidm id IN VARCHAR2 ID to find pidm for RETURN VARCHAR IS pidm VARCHAR2 8 generated id cubctrl cubctrl id ind96type BEGIN Purpose Look at current id to get the spriden pidm if control file indicates using generated IDs 7 zi Parameter names default into the left column of the parameters dialog box Enter the parameter value in the right column In this function F CVT_GET_PIDM the value that must be passed to the function is the legacy value in the convert column of the convert table The correct variable already defined in the convert script is LEGACY VALUE For a more detailed discussion of the construction of the convert table and the contents of the columns in the convert table please see the Scripts Produced by the SCT Converter Tool section of this document When you have entered the correct parameter click the CONSTRUCT FUNCTION CALL button The box on the top right corner of the form is populated with the function call as in the image below 2 14 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter ra Function Code and Parameters F GET PIDM LEGACY VALUE F CVT GET PIDM 4 gt Hide Parameters Construct Function Call FUNCTION f_evt_get_p
143. nd Insert in the same transaction This is convenient for loading small simple tables such as validation tables where there is a strong confidence in the accuracy and compatibility of the data with the lt TargetTable gt For most conversions however it is recommended that the script be run in Convert mode until all errors are corrected Specifying Rollback Segments for the Conversion Process Depending upon your version of Oracle for large conversion files you may need to ask your DBA to create a large rollback segment typically LRG_RBS1 if one does not exist already Before running the conversion script against a large convert table you will need to take the smaller rollback segments off line and be sure that the large rollback segment is online To do this ask your DBA to execute the following commands at the SQL gt prompt note that these rollback segment names are samples your environment may differ alter rollback segment RBS1 offline alter rollback segment RBS2 offline alter rollback segment RBS3 offline alter rollback segment LRG RBSI online Release 3 0 4 7 SCT Converter Tool User s Guide Chapter 4 Scripts Produced by the SCT Converter Tool Running the Convert Script Answering the Prompts First set of Prompts Record Type When starting the convert script you are prompted for a record type and given the following 3 choices Please enter the record type which should b
144. ndix D Sample Conversion Scripts October 2003 Confidential WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END hee ci ice inier e Beginning evaluation of column SPRIDEN MI wae foe I alot BEGIN cur col SPRIDEN legacy value spriden rec convert mi CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spriden rec CONVERT MI spriden rec SPRIDEN MI converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPRIDEN CHANGE IND BEGIN cur col SPRIDEN CHANGE legacy value spriden rec convert change ind CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val _ IND spriden_rec SPRIDEN CHANGE IND converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val null err msg END Beginning evaluation
145. ner cur 61 cur jobid cur rec cur col legacy value converted val null arr msg END Beginning evaluation of column SPBPERS ITIN BEGIN cur col SPBPERS ITIN legacy value spbpers rec convert itin CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value xj converted val null converted spbpers rec CONVERT ITIN BEGIN spbpers rec SPBPERS ITIN TO NUMBER converted val y EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Updating Temporary table with converted values ere ae eee Oe BEGIN UPDATE SPBPERS CVT SET SPBPERS PIDM spbpers rec spbpers pidm SPBPERS SSN spbpers_rec spbpers_ssn SPBPERS BIRTH DATE spbpers rec spbpers birth date SPBPERS LGCY CODE spbpers rec spbpers code SPBPERS CODE spbpers rec spbpers code SPBPERS ETHN CODE spbpers rec spbpers ethn code Release 3 0 SCT Converter Tool User s Guide D 53 Appendix Sample Conversion Scripts D 54 SPBPERS RELG CODE spbpers rec spbpers relg code SPBPERS SEX spbpers rec spbpers sex SPBPERS CONFID IND spbpers
146. ngth of the convert columns to accommodate longer legacy values than would be allowed in the target table The data can then be manipulated within the convert table as necessary using a variety of functions to be made compatible with the structure and constraints of the target table Note Ifthe legacy data files are fixed position or fixed width the length specified in the CUACNVT form must be exactly the same length as the field in the incoming legacy data file If the legacy data files are variable width and delimited by a special character e g a comma the Length field must be at least as long as the longest known legacy value for that column but can be longer Notice also that 3 additional columns not part of the original table appear in the convert table The TargetTable7 record id column holds a sequence number for each record which is assigned when the table is loaded using the SQL Loader control file produced by the SCT Converter Tool e The lt gt status column holds a code that indicates the conversion status of the record In the initial loading of the legacy data to the convert table the status 15 set to N for N ew During the conversion process the status can change to E rrored or C onverted Finally once the data has been inserted into the target table the value in this column becomes for I nserterd These values and their use are explained in more detail in the section
147. nvert script It is identified by the cursor name and the column name To view convert script code refer to Appendix D Sample Conversion Scripts Release 3 0 2 19 SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool Loading Data into the SPRIDEN Table The SCT Banner SPRIDEN table is the first table to be loaded with person data The SCT Banner PIDM is created during the loading of the SPRIDEN table The SCT Converter Tool function F CVT AUTO PIDM generates the PIDMs and dynamically updates the SOBSEQN sequence table after the assignment of each new pidm This pidm generating function assumes a specific data configuration for the legacy data in the spriden cvt dat file The flat file for loading spriden must follow the designated configuration below in order for the F CVT AUTO PIDM to correctly assign pidms to incoming legacy name identification records Required file layout for SPRIDEN CVT DAT CONVERT PIDM Null if record is the current record If not current record because of name or ID change this field should contain the current legacy ID and the convert change ind column should contain the appropriate indicator I if the record represents an ID change and N if the record indicates a name change CONVERT ID Current ID if record is current If not current ID this field should contain the previous ID and the spriden change ind field should contain T CONVERT LAST NAME Current last name if
148. nverter Tool They are exact replicas of the target table columns in size and position but without the constraints that exist on the target table columns These values can be changed by the user based on the field size and data element order of the data in the ASCII flat file from the legacy system Convert columns are always the VARCHARQ2 datatype If the actual SCT Banner column is not a character data type the Converter Tool will create a VARCHAR2 column equivalent to the length of the SCT Banner column For example a SCT Banner date column would have a convert column created that 15 nine characters long because the default Oracle date format 4 DD MON Y Y is nine characters long It is essential to adjust the size for each convert column based on the size of the data in the ASCII flat legacy data files This is a sample of a convert table creation script spriden create sql generated from the CUACNVT form AUDIT TRAIL Converter Tool 2 10 SCTCVT 08 10 2002 This script creates the conversion table SPRIDEN for converting legacy data into SPRIDEN This script was generated by the SCT Converter Tool AUDIT TRAIL END DROP TABLE SPRIDEN CVT CREATE TABLE SPRIDEN CVT SPRIDEN PIDM NUMBER 8 CONVERT PIDM VARCHAR2 8 SPRIDEN ID VARCHAR2 9 CONVERT ID VARCHAR2 9 SPRIDEN LAST NAME VARCHAR2 60 CONVERT LAST NAME VARCHAR2 60 SPRIDEN FIRST NAME VARCHAR 15 CONVERT FIRST NAME VARCHAR2 15 SPRIDEN MI VARCHAR 2 1
149. of column SPRIDEN ENTITY IND Release 3 0 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts BEGIN cur col SPRIDEN ENTITY IND legacy value spriden rec convert entity ind CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted spriden rec CONVERT ENTITY IND spriden rec SPRIDEN ENTITY IND converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur tbl cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRIDEN ACTIVITY DATE BEGIN cur col SPRIDEN ACTIVITY legacy value rec convert activity date CURCNVT Rules Column is Required Conversion function is specified No validation specified No default value converted_val null converted val SYSDATE DEFAULTING SYSDATE HERE IF SUBSTR converted_val 1 3 ERR OR SUBSTR converted_val 1 3 THEN err msg Column conv failure converted_val RAISE column error END IF IF converted val IS NULL THEN err msg Missing required value RAISE column error END IF BEGIN spriden rec SPRIDEN ACTIVITY TO DATE converted val y EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column e
150. ol legacy value converted val nullerr msg END Beginning evaluation of column SPRIDEN SEARCH MI BEGIN col SPRIDEN SEARCH legacy value spriden rec convert search mi CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val CONVERT SEARCH MI spriden_rec SPRIDEN SEARCH MI converted val D 10 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPRIDEN SOUNDEX LAST NAME BEGIN cur col SOUNDEX LAST legacy value spriden rec convert soundex last name CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spriden CONVERT SOUNDEX LAST spriden_rec SPRIDEN SOUNDEX LAST NAME converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur tbl cur jobid cur rec cur col legacy value converted val null err msg END Beginning evaluation of column SPRIDEN S
151. ol file contains the convert columns and not the SPRIDEN columns Also note the name of the Infile spriden cvt dat Be sure to following this naming convention in naming your flat files The convert activity date field can be populated with the system date or with legacy data If legacy data exists the user should check the Load and Insert boxes on CUACNVT for the activity date column If legacy data does not exist the user should leave the Load box blank and check only the Insert box so that sysdate will be defaulted October 2003 Release 3 0 C 3 Confidential SCT Converter Tool User s Guide Appendix Sample SQL Loader Scripts C 4 SPRADDR Address Table spraddr cvt ctl AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This SQLLoader control file is used to load legacy data into the temporary conversion table SPRADDR cvt This script was generated by the SCT Converter Tool AUDIT TRAIL END Load data Infile spraddr_cvt dat APPEND INTO TABLE SPRADDR_CVT FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY TRAILING NULLCOLS CONVERT PIDM CHAR CONVERT ATYP CODE CHAR CONVERT FROM DATE CHAR CONVERT TO DATE CHAR CONVERT STREET LINEI CHAR CONVERT CITY CHAR CONVERT STAT CODE CHAR CONVERT ZIP CHAR SPRADDR RECORD ID SEQUENCE MAX 1 SPRADDR CVT STATUS CONSTANT N Note that the convert pidm column remains in the control file This field will b
152. ol status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPBPERS DEAD IND BEGIN cur col SPBPERS DEAD IND legacy value spbpers rec convert dead ind CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers CONVERT DEAD IND spbpers_rec SPBPERS DEAD IND converted_val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS VETC FILE NUMBER BEGIN cur col VETC FILE NUMBER legacy value spbpers rec convert vetc file number CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers rec CONVERT VETC FILE NUMBER spbpers rec SPBPERS VETC FILE NUMBER converted val D 40 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg
153. olumn unchecked Navigate to the Insert option and check it Navigate to the Next Record SPRIDEN ORIGIN Navigate to the Default field Enter CONVERSION No quotation marks Release 3 0 October 2003 Confidential SCT Converter Tool User s Guide 3 9 Chapter 3 A Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Navigate to the Default Action column Choose D efault if no legacy passed You may also choose O verride any legacy passed Navigate to the Load option and verify that it is No legacy data exists for this column unchecked Navigate to the Insert option and check it Navigate through the remainder of the SPRIDEN SPRIDEN SEARCH and SPRIDEN SOUNDEX fields are populated via a columns and Uncheck both LOAD and INSERT for database trigger on the SPRIDEN table and do not need to be included in the all LOAD or INSERT options for this table SAVE your changes October 2003 Confidential You have now entered all the necessary specifications for the SPRIDEN table conversion The next step 15 to use the features of the converter tool to produce the following 3 scripts e spriden create sql creates the convert table SPRIDEN e spriden cvt ctl used by SQL Loader to load the data from the client s flat file spriden cvt dat to the convert table SPRIDEN e spriden convert sql mo
154. onfidential Appendix D Sample Conversion Scripts IF SUBSTR converted_val 1 3 ERR OR SUBSTR converted_val 1 3 THEN err msg Column conv failure converted_val RAISE column error END IF IF converted val IS NULL THEN err msg Missing required value RAISE column error END IF BEGIN spbpers rec SPBPERS ACTIVITY DATE TO DATE converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column ISPBPERS VERA IND BEGIN cur col VERA IND legacy value spbpers rec convert vera ind CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers VERA IND spbpers rec SPBPERS VERA IND converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS CITZ IND BEGIN col CITZ_IND legacy value spbpers rec convert citz ind CURCNVT Rules Column is Not Required Conversion
155. or THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS SEX BEGIN cur col SEX legacy_value spbpers_rec convert_sex CURCNVT Rules Column is Not Required Conversion function is not specified Validation list specified No default value converted_val null converted_val spbpers_rec CONVERT_ SEX spbpers_rec SPBPERS SEX converted val THIS SECTION ILLUSTRATES THE USER OF THE LIST OF VALUES IF spbpers rec SPBPERS SEX NOT IN M F N THEN err msg Validation failure RAISE column error USING LIST OF VALUES END IF EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column SPBPERS CONFID IND Release 3 0 D 39 SCT Converter Tool User s Guide Appendix Sample Conversion Scripts BEGIN col SPBPERS_CONFID_IND legacy value spbpers rec convert confid ind CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spbpers_rec CONVERT CONFID IND spbpers_rec SPBPERS CONFID IND converted val EXCEPTION WHEN column error THEN c
156. p legacy data to target table columns or can be used as conversion archive Generate Report report Closes CUACNVT and logs user off Displays the Help window which provides Overall help Clicking Help on the Menu brings up context sensitive help Help 2 6 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter The Control Form The Conversion Tool Control Form is the first form that the user should access when setting up rules in the converter tool It is accessed by the CTRL button on the tool bar The Control Form allows users to enter several global settings that will affect the entire conversion From the control form users can specify whether or not to use SCT Banner generated ID numbers and can specify a default directory for all Converter Tool output either on the local machine or on the database server Developer Forms Runtime Web Action Edit Query Block Record Field Help Window 7 9 Use Generated IDs File Location 6 Local Machine Database Server Default Directory for Scripts E Audit Changes on CUACNVT Activity Date Check if using generated ids Affects conversion script for SPRIDEN and F_CVT_GET_PIDM function Record 11 Using Generated 10 5 Check the Use Generated IDs box if you wish to generate new SCT Banner IDs as r
157. pers rows success insert DBMS OUTPUT PUT LINE Number of Rows That Errored rows errored DBMS OUTPUT PUT LINE CHR 10 Completed Processing of spbpers END SELECT TO CHAR sysdate DD MON Y YYY HH24 MI STOP TIME FROM DUAL COMMIT PROMPT Querying CURCERR table for errors col msg for a35 hea ERROR MESSAGE WORD col colname for a30 hea COLUMN_NAME col jobno for 99999 hea JOB col norecs for 999999 hea COUNT set wrap on select distinct curcerr_message msg curcerr_column_name colname curcerr_cvt_identifier jobno count norecs from curcerr where curcerr table name SPBPERS group by curcerr message curcerr column name curcerr cvt identifier SPOOL OFF UNDEFINE process level UNDEFINE records in D 56 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix E SCT Converter Functions Viewing Functions 6 ee Bee RA LAR Aa RAL AR ae ane Editing Functions Online acc noes Rake eee RA EUR TR A E 1 Appendix SCT Converter Tool Functions E 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix SCT Converter Tool Functions Viewing Functions The database functions provided with the Converter Tool can be helpful in getting data from your legacy system to SCT Banner The source code and internal documentation for the functions delivered with the Converter Tool can be viewed in the Converter Tool install directory
158. phic Table D 34 spbpers_convert sql Process spbpers_convert sql Generated 30 Sep 2002 08 37 AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This script translates legacy values into Banner values and inserts Banner values into spbpers This script was generated by the SCT Converter Tool AUDIT TRAIL END COLUMN file id NEW VALUE spool file NOPRINT SET VERIFY OFF SET ECHO OFF SELECT spbpers cubcnvt sequence NEXTVAL log file id FROM DUAL SPOOL amp spool file PROMPT PROMPT Please enter the record type which should be processed PROMPT N ew records PROMPT C onverted records PROMPT E rrored records ACCEPT records in CHAR PROMPT Include PROMPT PROMPT Please enter the disposition to which the records should PROMPT be processed PROMPT C onvert records only PROMPT Insert records only records were previously converted successfully PROMPT B oth convert and insert records into spbpers ACCEPT process level CHAR PROMPT Disposition PROMPT SET SERVEROUTPUT ON SIZE 500000 PROMPT PROMPT PROMPT will be creating generated id records PROMPT in SPRIDEN for any converted records PROMPT i e records with spriden_cvt_status C PROMPT PROMPT SELECT TO_CHAR sysdate DD MON Y Y Y Y HH24 MI START TIME FROM DUAL DECLARE cur jobid NUMBER 0 cur owner VARCHAR2 30 SATURN cur tbl VARCHAR2 30 SPBPERS cur fileid VARCHAR2 2000 col status VARCHAR2 1
159. pts produced by the SCT Converter Tool Details about the scripts appear in the section entitled Scripts Produced by the SCT Converter Tool Full text of the scripts from the sample conversion appears in Appendix B Sample Table Creation Scripts Appendix C Sample SQL Loader Scripts and Appendix D Sample Conversion Scripts Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 1 Introduction October 2003 Confidential Creating the Convert Table TargetTable cvt create sql The first script you create using the SCT Converter Tool and execute in SQL Plus creates an Oracle convert table to hold the data from the client s flat file and converted transformed values that can be loaded directly into the SCT Banner target table The CUACNVT form allows you to specify columns that will be loaded during the conversion and to define transformation rules to be applied during the conversion process These should include all columns for which you have legacy data as well as columns for which there will be default values and or functions After you have defined your conversion rules you can then produce a script lt TargetTable gt _cvt_create sql that creates a convert table into which the legacy data will be loaded The table creation script contains each column in the target table as well as a convert column for each of the columns in the target table The convert columns for the script are created by the Co
160. quired Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers CODE DRIVER spbpers_rec SPBPERS STAT CODE DRIVER converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END October 2003 Release 3 0 D 47 Confidential SCT Converter Tool User s Guide Appendix D Sample Conversion Scripts Beginning evaluation of column SPBPERS NATN CODE DRIVER BEGIN cur col SPBPERS NATN CODE DRIVER legacy value spbpers rec convert natn code driver CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value m converted val null converted spbpers NATN CODE DRIVER spbpers rec SPBPERS NATN CODE DRIVER converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column ISPBPERS UOMS CODE HEIGHT BEGIN cur col SPBPERS UOMS CODE HEIGHT legacy value spbpers rec convert uoms code height CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converte
161. r s Guide F 3 Appendix F Using the SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Navigate to the Convert Fnctn field if you wish to apply function to the selected column If you wish to use a function on a field the following steps outline that process Press the CONVERT FNCTN button Choose the appropriate function from the list With your cursor in the Convert Fnctn field press the VIEW FUNCTION button on the Tool Bar Function Code and Parameters Window You will see the Function Code and Parameters window It shows the text of the function create script and has three buttons across the top of the window above the function text Press the EDIT PARAMETERS button Edit Parameters Dialog Box The left side of the parameters dialog box has the parameter names defaulted in Enter the correct parameters for the function you are using LEGACY VALUE is a variable that has been defined in the convert script generated by the converter tool It refers to the legacy data value in the convert column in the convert table Any time it is used as a parameter in a function the value passed to the function is the legacy data value that resides in the convert column corresponding to the target table column to which the function is being applied Press the CONSTRUCT FUNCTION CALL button When you press the CONSTRUCT FUNCTION CALL button the blank box at
162. r col SPRADDR_NATN_ CODE legacy value spraddr rec convert natn code CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spraddr CONVERT NATN CODE spraddr rec SPRADDR NATN CODE converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR PHONE AREA BEGIN cur col SPRADDR_ PHONE AREA legacy value spraddr rec convert phone area CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value October 2003 3 0 D 25 Confidential SCT Converter Tool User s Guide Appendix Sample Conversion Scripts converted_val null converted_val spraddr CONVERT PHONE AREA spraddr_rec SPRADDR_ PHONE AREA converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR PHONE NUMBER BEGIN cur col SPRADDR PHONE NUMBER legacy value spraddr rec convert phone number CURCNVT Rules Column is Not Required Conversion function 15 not spec
163. racticing or if you are in training the owner of your table may be your user name your login or some other owner If you are setting up specifications for your conversion use the SATURN owner Navigate to the Next Field Enter the name of the table SPRADDR Navigate to the Error Action field Accept the default Error Action Continue Processing Row Navigate to the Delimited By field and insert a comma For this exercise our data is comma delimited For your conversion you have the option of using comma delimited data files or fixed length data files This field should be populated to reflect the file layout of your data SAVE your changes As you save changes and Navigate to the next block the column block is populated with columns from the SPRADDR table NEXT BLOCK CUACNVT Column To see all columns in the table after moving to the Column Block Block with your cursor in the Column field move through the records with the NEXT RECORD button or by using the down arrow Navigate to the SPRADDR PIDM column Navigate to the Convert Fnctn field Press the CONVERT FNCTN button October 2003 Release 3 0 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Choose F CVT GET PIDM from the list The assumption is that the legacy data has already been loaded into the SPRIDEN table This function
164. rchar2 15 SPRIDEN CHANGE IND Varchar2 1 SPRIDEN ENTITY IND Varchar2 1 SPRIDEN ACTIVITY DATE Date Not Null SPRIDEN USER Varchar2 30 SPRIDEN ORIGIN Varchar2 30 SPRIDEN SEARCH LAST NAME Varchar2 60 SPRIDEN SEARCH FIRST NAME Varchar2 15 SPRIDEN SEARCH MI Varchar2 15 SPRIDEN SOUNDEX LAST NAME Char 4 SPRIDEN SOUNDEX FIRST NAME Char 4 SPRIDEN NTYP CODE Varchar2 4 The flat file that we are using in our example contains the legacy data for the SPRIDEN ID SPRIDEN LAST NAME SPRIDEN FIRST NAME and SPRIDEN MI columns to see the contents of the flat files used in the example refer to Appendix A Sample Data Files Used in Examples Other required and or desired fields for the convert table are SPRIDEN PIDM SPRIDEN ENTITY IND SPRIDEN ACTIVITY DATE SPRIDEN USER and SPRIDEN ORIGIN other fields are excluded from the Load and or Insert process on the CUACNVT form by checking unchecking the Load and Insert buttons The spriden create sql script however contains all columns in the SPRIDEN target table plus a set of convert columns that mirror the target table columns The differences between the columns are 1 the convert columns are all VARCHAR2 data types and 2 the size of the convert column may vary from the size of the target table column if users have specified changes on the CUACNVT form during setup October 2003 Confidential Release 3 0 4 3 SCT Converter Tool User s Guide Chapter 4 Scripts P
165. rd state codes Navigate to the SPRADDR ZIP column Navigate to the Load amp Insert boxes Check both Navigate to the SPRADDR CODE column Navigate to the Load amp Insert boxes UNcheck both Navigate to the SPRADDR NATN CODE column Navigate to the Load amp Insert boxes UNcheck both Navigate to the SPRADDR PHONE AREA column Navigate to the Load amp Insert boxes UNcheck both Navigate to the SPRADDR PHONE NUMBER column Navigate to the Load amp Insert boxes UNcheck both Navigate to the SPRADDR PHONE EXT column Navigate to the Load amp Insert boxes UNcheck both Navigate to the SPRADDR STATUS IND column Navigate to the Load amp Insert boxes UNcheck both Navigate to the SPRADDR ACTIVITY DATE column Navigate to the Convert Function field Enter SYSDATE Navigate to the Load amp Insert boxes UNcheck Load Check Insert Navigate through all remaining columns UNcheck both Load and Insert SAVE your changes October 2003 Release 3 0 3 18 Confidential SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter October 2003 Confidential You have now entered all the necessary specifications for the SPRADDR table conversion The next step is to use the features of the converter tool to produce the following 3 scripts spraddr_cvt_create sql creates the convert table SP
166. rec spbpers confid ind SPBPERS DEAD IND spbpers rec spbpers dead ind SPBPERS VETC FILE NUMBER spbpers rec spbpers vetc file number SPBPERS LEGAL NAME spbpers rec spbpers legal name SPBPERS PREF FIRST NAME spbpers rec spbpers pref first name SPBPERS NAME PREFIX spbpers rec spbpers name prefix SPBPERS NAME SUFFIX spbpers rec spbpers name suffix SPBPERS ACTIVITY DATE SYSDATE SPBPERS VERA IND spbpers rec spbpers vera ind SPBPERS CITZ IND spbpers rec spbpers citz ind SPBPERS DEAD DATE spbpers rec spbpers dead date SPBPERS PIN spbpers rec spbpers pin SPBPERS CITZ CODE spbpers rec spbpers citz code SPBPERS HAIR CODE spbpers rec spbpers hair code SPBPERS EYES CODE spbpers rec spbpers eyes code SPBPERS CITY BIRTH spbpers rec spbpers city birth SPBPERS STAT CODE BIRTH spbpers rec spbpers stat code birth SPBPERS DRIVER LICENSE spbpers rec spbpers driver license SPBPERS STAT CODE DRIVER spbpers rec spbpers stat code driver SPBPERS NATN CODE DRIVER spbpers rec spbpers natn code driver SPBPERS UOMS CODE HEIGHT spbpers rec spbpers uoms code height SPBPERS HEIGHT spbpers rec spbpers height SPBPERS UOMS CODE WEIGHT spbpers rec spbpers uoms code weight SPBPERS WEIGHT spbpers rec spbpers weight SPBPERS SDVET IND spbpers rec spbpers sdvet ind SPBPERS LICENSE ISSUED DATE spbpers rec spbpers license issued date SPBPERS LICENSE EXPIRES DATE spbpers rec spbpers license expires date SPBPERS
167. record type which should be processed N ew records C onverted records E rrored records Include N 2 24 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 2 Elements of the SCT Converter Please enter the disposition to which the records should be processed C onvert records only I nsert records only records were previously converted successfully B oth convert and records into spriden Disposition C will be creating generated id records in SPRIDEN CVT for any converted records Le records with spriden status C Beginning Conversion of Table spriden Job Number 66 Number of ids generated 264 Number of Rows Converted in SPRIDEN_CVT 264 Number of Rows Inserted into spriden 0 Number of Rows That Errored 0 Completed Processing of spriden PL SQL procedure successfully completed Commit complete When the SPRIDEN CONVERT SQL script is executed a second time to insert the converted records into the actual SCT Banner SPRIDEN table two ID records are created for each person legacy ID that has the change indicator of and the SPRIDEN NTYP CODE of LGCY e The Generated ID with the change indicator of NULL In the following example that continues from the previous example twice as many SPRIDEN records are loaded 528 than were originally loaded into SPRIDEN_CVT 264 This is because a Generated ID record for
168. records Some DBA issues e Creation of the appropriate user for the conversion process A user is created specifically for the converter tool the default user delivered with the tool installation scripts is SCTCVT Your DBA should review the database scripts that create the user and it s privileges e Rollback segments to be online for processing With the amount of data involved in a conversion it is important for the DBA to make sure rollback segments are sized accordingly e Tablespace for conversion objects The Converter Tool installation scripts create tablespace to store tables used by the converter tool A specific Oracle data file location and name is required prior to running the installation scripts This is discussed further in the Converter Tool Installation Guide e Tablespace sizing Make certain that tablespace is sized accordingly for your institution and the amount of converted legacy data e Buffer size edited in the convert script the SET SERVEROUTPUT ON command The size of the area that stores the SOL command string If an ORU 10027 Buffer Overflow error occurs when running a conversion script change the SIZE option in the SETSERVEROUTPUT ON command Example SET SERVEROUTPUT ON SIZE 100000 The CREATE TABLE command will include changes in a future release of the SCT Converter Tool Brief Overview of Scripts Created by the SCT Converter Tool CUACNVT This section offers a brief overview of the scri
169. roduced by the SCT Converter Tool This is a sample of a table creation script spriden cvt create sql generated by the converter tool AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This script creates the temporary conversion table SPRIDEN for converting legacy data into SPRIDEN This script was generated by the SCT Converter Tool AUDIT TRAIL END DROP TABLE SPRIDEN CVT CREATE TABLE SPRIDEN CVT SPRIDEN PIDM NUMBER 8 CONVERT PIDM VARCHAR2 9 SPRIDEN ID VARCHAR2 9 CONVERT ID VARCHAR2 9 SPRIDEN LAST NAME VARCHAR2 60 CONVERT LAST NAME VARCHAR2 60 SPRIDEN FIRST NAME VARCHAR 15 CONVERT FIRST NAME VARCHAR 15 SPRIDEN MI VARCHAR 2 15 CONVERT MI VARCHAR 2 15 SPRIDEN CHANGE IND VARCHAR 1 CONVERT CHANGE IND VARCHAR2 1 SPRIDEN ENTITY IND VARCHAR 2 1 CONVERT ENTITY IND VARCHAR2 1 SPRIDEN ACTIVITY DATE DATE CONVERT ACTIVITY DATE VARCHAR2 9 SPRIDEN USER VARCHAR2 30 CONVERT USER VARCHAR2 30 SPRIDEN ORIGIN VARCHAR2 30 CONVERT ORIGIN VARCHAR2 30 SPRIDEN SEARCH LAST NAME VARCHAR2 60 CONVERT SEARCH LAST NAME VARCHAR2 60 SPRIDEN SEARCH FIRST NAME VARCHAR 2 15 CONVERT SEARCH FIRST NAME VARCHAR 15 SPRIDEN SEARCH MI VARCHAR 2 15 CONVERT SEARCH MI VARCHAR 15 SPRIDEN SOUNDEX LAST NAME CHAR 4 CONVERT SOUNDEX LAST NAME VARCHAR2 4 SPRIDEN SOUNDEX FIRST NAME CHAR 4 CONVERT SOUNDEX FIRST NAME VARCHAR2 4 SPRIDEN NTYP CODE VARCHAR2 4 CONVERT NTYP CODE VARCHAR 4 SPRIDEN
170. rol Form the first step 15 to convert all name and ID information into SPRIDEN The conversion process 15 the same whether or not you are using Generated ID s 1 Setup your SPRIDEN conversion in the converter tool Create your SPRIDEN CVT table Load data into the SPRIDEN table Be 99 BS Create your SPRIDEN CONVERT script should contain the necessary code to generate your ID s Release 3 0 2 23 SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool 5 Modify the spriden_convert sql script to change the order by clause in the cursor statement CURSOR spriden_cursor IS SELECT FROM WHERE spriden status records in erder bs spriden evt record id order by convert change ind desc Note There is one restriction on converting SPRIDEN records with Generated ID s You must run the conversion script generated by the converter tool in two separate steps The conversion script allows you to convert records in SPRIDEN and insert them into SPRIDEN in one step disposition of B When using the Generated ID feature you must run the script with the Convert disposition and then run the script with the Insert disposition In other words you cannot use the action that allows you to both convert and insert in the same step disposition of B The reason has to do with how the Generated ID s are created The process to create SPRIDEN records with Generated I
171. rror THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg D 8 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts October 2003 Confidential END Beginning evaluation of column SPRIDEN_ USER BEGIN cur col SPRIDEN_USER legacy value spriden rec convert user CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified Default value 5 Default Action D Default if null legacy value DEFINING DEFAULT USER AND DEFAULT ACTION p converted val null converted val NVL spriden rec CONVERT USER SCTCVT spriden rec SPRIDEN USER converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPRIDEN ORIGIN BEGIN cur col SPRIDEN_ORIGIN legacy value spriden rec convert origin CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified Default value CONVERSION Default Action D Default if null legacy value converted_val null converted val NVL spriden_rec CONVERT_ORIGIN CONVERSION spriden rec SPRIDEN ORIGIN converted val EXCEP
172. rrors that prevented them from being loaded How the Convert Script Processes the Specifications Set up on CUACNVT Here are two excerpts from the spriden convert sql script Notice the following items highlighted in the script e function call in the SPRIDEN section with the passed parameter of LEGACY VALUE e the Default value SCTCVT in the SPRIDEN USER section These values were entered in the CUACNVT form PROMPT PROMPT will be creating generated id records PROMPT in SPRIDEN CVT for any converted records PROMPT i e records with spriden status C PROMPT Beginning evaluation of column SPRIDEN_PIDM BEGIN cur col SPRIDEN_PIDM legacy value spriden rec convert pidm CURCNVT Rules Column is Required Conversion function is specified No validation specified No default value 3 0 4 9 SCT Converter Tool User s Guide Chapter 4 Scripts Produced by the SCT Converter Tool 4 10 converted_val null converted val CVT AUTO PIDM LEGACY VALUE IF SUBSTR converted val 1 3 ERR OR SUBSTR converted val 1 3 THEN err msg Column conv failure converted val RAISE column error END IF Beginning evaluation of column ISPRIDEN USER BEGIN cur col SPRIDEN USER legacy value spriden rec convert user CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified Defa
173. s Guide Confidential Appendix Sample Conversion Scripts Sample lt TargetTable gt _convert sql scripts D 3 spridemn convert secte TRU RI D 3 SPRADDR A ddress eene innen inet teen D 17 spraddr convert sgl cete see e Redes D 17 SPBPERS General Person Biographic Demographic D 34 spbpers convert sql 1 geeinigt es riii D 34 Appendix D Sample Conversion Scripts D 2 Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix Sample Conversion Scripts Sample lt TargetTable gt _convert sql scripts October 2003 Confidential This is the entire text of the 3 lt TargetTable gt _convert sq scripts used in our sample conversion They were created from the SCT Converter Tool after specifications were entered on the CUACNVT form Areas of interest are highlighted and or have notes in the right margin SPRIDEN Identification Table spriden_convert sql Process spriden convert sql Generated 30 Sep 2002 09 27 AUDIT TRAIL Converter Tool 2 10 SCTCVT 09 30 2002 This script translates legacy values into Banner values and inserts Banner values into spriden This script was generated by the SCT Converter Tool AUDIT TRAIL END COLUMN file id NEW VALUE spool file NOPRINT SET VERIFY OFF SET ECHO OFF SELECT spriden cubcnvt sequence
174. s in the log file or from a sql session selecting from the error table curcerr The intermediate stage of populating the lt TargetTable gt columns in the convert table allows you to evaluate the results of the conversion without working in the actual target tables correct the errors and re run the conversion script until all records are successfully loaded into the lt TargetTable gt columns of the convert table Once all errors have been corrected or handled in some other way the convert script is run in Insert mode This process inserts the data from the convert table target table columns into the actual target table Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 1 Introduction October 2003 Confidential There is the option for the convert script to both Convert and Insert the same transaction This is convenient for loading small simple tables such as validation tables where there is a strong confidence in the accuracy and compatibility of the data with the target table For most conversions however it is recommended that the script be run in Convert mode first to determine if errors exist For a detailed look at the convert script refer to the Scripts Produced by the SCT Converter Tool section of this document as well as Appendix D Sample Conversion Scripts Release 3 0 1 9 SCT Converter Tool User s Guide Chapter 1 Introduction 1 10 Releas
175. s rec convert name prefix CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers CONVERT NAME PREFIX spbpers_rec SPBPERS NAME PREFIX converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPBPERS NAME SUFFIX BEGIN cur col NAME SUFFIX legacy value spbpers rec convert name suffix CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers CONVERT SUFFIX spbpers_rec SPBPERS NAME SUFFIX converted val EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPBPERS ACTIVITY DATE BEGIN cur col ACTIVITY legacy value spbpers rec convert activity date CURCNVT Rules Column is Required Conversion function is specified No validation specified No default value converted_val null converted val SYSDATE Release 3 0 October 2003 SCT Converter Tool User s Guide C
176. ser s Guide Appendix G Utility Scripts G 4 STEP 3 last name duplicate sgl This report lists the records in the spriden cvt table where the id and last name already exists in SPRIDEN STEP 4 RUN id_ssn_duplicate sql This report list the records in the cvt temp table where their convert 1d already exists in SPRIDEN and their convert ssn already exists in SPBPERS The convert last name in the temp table however does not match the spriden last name in SPRIDEN SPRADDR duplicate checking steps STEP 1 Run duplicates sql This program will check that the legacy address information has not been loaded previously for a specific address type If a match is found on id pidm address type and the first seven characters of street linel then the SPRADDR STATUS is set to D and the record will not be loaded since it already exists If a match is found on id pidm address type but the street linel does not match and both the legacy address and the SCT Banner address active then the SPRADDR STATUS is set to X so a report be produced for the client to decide which address record should be the active record for the address type manual updating of either the legacy address or the SCT Banner address to inactive and re extract of data file should be done once legacy records are accurate STEP 2 RUN address match report sql Will produce address matc
177. sing Crosswalk Values in the Conversion Script With your cursor in the Convert Fnctn field press the CONVERT FNCTN button and Choose F CVT CURCVAL RNULL Click the VIEW FUNCTION button on the Tool Bar In the Function Code and Parameters window press the EDIT PARAMETERS button Enter the following parameters using the STVATYP entity from the image CONVERSION ENTITY STVATYP or the name of your entity that you have created must be enclosed in single quotes LEGACY VALUE LEGACY VALUE Note Any parameter passed that 15 not a defined variable must be enclosed in single quotation marks In this example the name of our entity 55 enclosed in quotes and the variable LEGACY VALUE is not enclosed in quotes To include options as parameters you must identify the option with the name that appears in the convert script That naming convention is target table rec convert column name the name of the column that holds the value that is used as the option In our example of the STVATYP entity which is being used to translate values for the SPRADDR table the parameter for Optionl would be spraddr rec convert camp code Press the CONSTRUCT FUNCTION CALL button Press the RETURN button Choose Save amp Return NEXT BLOCK to the Column Block of the form and continue to set up specifications Note The naming convention for the option parameters 1s derived from the name assigned to the convert column in the co
178. spriden 14 SPRIDEN LAST NAME spriden rec spriden last name SPRIDEN FIRST NAME spriden rec spriden first name SPRIDEN MI spriden rec spriden mi SPRIDEN CHANGE IND spriden rec spriden change ind SPRIDEN ENTITY IND spriden rec spriden entity ind SPRIDEN ACTIVITY DATE SYSDATE SPRIDEN USER spriden rec spriden user SPRIDEN ORIGIN spriden rec spriden origin SPRIDEN SEARCH LAST NAME spriden rec spriden search last name SPRIDEN SEARCH FIRST NAME spriden rec spriden search first name SPRIDEN SEARCH MI spriden rec spriden search mi SPRIDEN SOUNDEX LAST NAME spriden rec spriden soundex last name SPRIDEN SOUNDEX FIRST NAME spriden rec spriden soundex first name SPRIDEN NTYP CODE spriden rec spriden ntyp code SPRIDEN CVT STATUS col status SPRIDEN CVT JOB ID cur jobid WHERE SPRIDEN CVT RECORD ID cur rec IOWS success convert rows success convert 1 EXCEPTION WHEN OTHERS THEN err msg Error updating record RAISE END lt lt insert_spriden gt gt IF process level B OR process level THEN Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Appendix D Sample Conversion Scripts BEGIN INSERT INTO SATURN spriden SPRIDEN SPRIDEN ID sPRIDEN LAST SPRIDEN FIRST NAME SPRIDEN MI SSPRIDEN CHANGE IND SSPRIDEN ACTIVITY DATE SSPRIDEN USER SPRIDEN ORIGIN VALUES spriden rec SPRIDEN priden rec SPRIDEN ID
179. status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR REVIEWED USER BEGIN cur col SPRADDR REVIEWED USER legacy value spraddr rec convert reviewed user CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spraddr CONVERT REVIEWED USER spraddr_rec SPRADDR_ REVIEWED USER converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Updating Temporary table with converted values et BEGIN UPDATE SPRADDR_CVT SET SPRADDR PIDM spraddr rec spraddr pidm SPRADDR ATYP CODE spraddr rec spraddr atyp code SPRADDR SEQNO spraddr rec spraddr seqno SPRADDR FROM DATE spraddr rec spraddr from date SPRADDR TO DATE spraddr rec spraddr to date SPRADDR STREET LINEI spraddr rec spraddr street linel SPRADDR STREET LINE2 spraddr rec spraddr street line2 SPRADDR STREET LINES spraddr rec spraddr street line3 SPRADDR CITY spraddr rec spraddr city SPRADDR STAT CODE spraddr rec spraddr stat code SPRADDR ZIP spraddr rec spraddr zip
180. t provided X X X F CVT SET SPRTELE SEQNO This function creates sprtele seqno records where none are supplied X X X F CVT TERM To convert incoming legacy term codes to Banner Term codes Used by SCT in testing X X X Can be modified at client site to fit client s legacy term code scheme Alternative to building crosswalks in CUACVAL CURCVAL F GEN RESET SEQNO Used to reset a sequence within the converter tool Use SEQ1 SEQ2 and SEQ3 This X X X function can be called during the breakpoint change F GEN SEQUENCE Used to generate a sequence For the converter tool pass in SEQI SEQ2 SEQ3 X X X F GET TCKN SEQ NO To fetch the shrtckn seq no needed for academic history conversion Required by shrtckl shrtckg F VALIDATE SINGLE Powerful way to validate codes Pass in the owner table and column of the validation X X X table along with the value you are verifying The function will dynamically create a select statement based upon your parameters The function returns FALSE if no errors it found the entry and TRUE if no data is found for the value you are passing in F WRAP SPRIDEN EXISTS use as wrap up functon for spriden when using generated ID s to see if the convert ID X X X exists in production assumes we ran spriden convert SQL and pidm has a value that 15 either generated or from an existing spriden record if record in SPRIDEN CVT is found with the same pidm in the BANNER spriden table set the status to X so it will not get converted
181. t sql script to change the order by clause in the cursor statement Please refer to the documentation for further instructions Once you have evaluated the data in the convert table convert columns run this script in SQL on the server for the N ew records in C onvert mode If there are errors correct them and run this script again this time choosing E rrored records for the first parameter of the script and C onvert for the disposition Continue this process until all errors are corrected Once you are satisfied that all errors are correct and that your data is clean run this script in SQL on the server for the C onverted records in I nsert mode to insert the data into the SPRIDEN table October 2003 Release 3 0 3 13 Confidential SCT Converter Tool User s Guide Chapter 3 A Conversion Example Using The SCT Converter Tool Establishing Specifications for Conversion of SPRADDR Table The following steps will show how to use the SCT Converter Tool to set up specifications for converting data into the SCT Banner SPRADDR table Sample flat file appears in Appendix B Sample Table Creation Scripts ACTIONS STEPS FORM PROCESS NOTES amp HINTS Open CUACNVT and log in CUACNVT Login your_convert_username Password your_convert_user password Database your_database Perform an INSERT RECORD function CUACNVT Table Block Enter the name of the table owner SATURN If you are p
182. tch with SPRIDEN ID If a match is found the existing SPRIDEN PIDM is returned to populate the SPBPERS PIDM field If no match is found the transaction will produce an error With your cursor in the Convert Fnctn field press the VIEW FUNCTION button on the Tool Bar Function Code and Parameters Window Click the EDIT PARAMETERS button Edit Parameters Dialog Box The parameter for the F CVT GET PIDM function is ID Enter LEGACY VALUE in the right column of the parameters dialog box In the spbpers cvt table the legacy value that will be loaded into the convert pidm column is the legacy ID That is the value that must be passed to the function In the Length field we will change the number from 8 to 9 to accommodate the legacy ID value that will be loaded into the convert table This action will cause the convert pidm column in the convert table to have a length of 9 while the spbpers pidm column in the convert table retains its prescribed length of 8 Press the CONSTRUCT FUNCTION CALL button When you press the CONSTRUCT FUNCTION CALL button the blank box at the top right corner of the form is populated with the actual function call that will appear in the convert script F CVT GET PIDM LEGACY VALUE Press the RETURN button Choose Save amp Return Doing this will return your cursor to the Table Block of CUACNV T To resume work you must perform a NEXT BLOCK function
183. the top right corner of the form is populated with the actual function call that will appear in the convert script Press the RETURN button October 2003 Choose Save amp Return Doing this will return your cursor to the Table Block of CUACNVT To resume work you must perform a NEXT BLOCK function NEXT BLOCK CUACNVT Exiting the Function Code and Parameters window places you in the Table Block Move through all remaining columns entering the necessary CUACNVT Column Use the down arrow or the NEXT RECORD button on the Tool Bar to specifications as you go Block move through the remaining columns in the target table entering the necessary specifications as you go If you enter no specification the legacy value will be moved from the convert column to the lt TargetTable gt column in the convert table and subsequently to the actual lt TargetTable gt column with no actions taken To enter a Default Value for a column With your cursor in the appropriate record Navigate to the Default field Release 3 0 SCT Converter Tool User s Guide Confidential F 4 Appendix F Using the SCT Converter Tool October 2003 ACTIONS STEPS FORM PROCESS NOTES amp HINTS Enter the default value in the field Do not use any quotation marks Navigate to the Default Action column The Default Action field requires a value if an en
184. to the Format Mask field and enter YYYYMMDD Navigate to the Load and Insert boxes Check both Navigate to the next record SPRADDR TO DATE Navigate We have data for a To Date on two of the sample records to the Format Mask field and enter YY YYMMDD Navigate to the Load and Insert boxes Check both Navigate to the SPRADDR STREET LINE column For our exercise we are not adjusting the size of the convert Navigate to the Load amp Insert boxes Check both columns for the address You may choose to leave the size as it 15 in order to identify any records that will not meet SCT Banner s size constraints for addresses OR you may choose to adjust the size and discover the incompatible records during the Insert phase Release 3 0 October 2003 Confidential SCT Converter Tool User s Guide 3 17 Chapter 3 Conversion Example Using The SCT Converter Tool ACTIONS STEPS FORM PROCESS NOTES amp HINTS Navigate to the SPRADDR STREET LINE2 column Navigate to the Load amp Insert boxes UNcheck both Navigate to the SPRADDR STREET LINE3 column Navigate to the Load amp Insert boxes UNcheck both Navigate to the SPRADDR CITY column Navigate to the Load amp Insert boxes Check both Navigate to the SPRADDR STAT CODE column Navigate For this exercise we will assume that there is no need for a to the Load amp Insert boxes Check both crosswalk because we expect the SEED data in your training database to contain standa
185. try is made in the Default field To enter a date format mask With your cursor in the appropriate record Navigate to the Format Mask field Enter the date format of the legacy date The format entered in the format mask field is the legacy data date MMDDYY MM DD YY etc no quotation marks field format The format mask here will allow the convert script to recognize the value in the convert column as a date to be converted into the correct system date format To use a Value List With your cursor in the appropriate record Navigate to the Value List field Enter a list of acceptable values in that column Correct format is 1 value2 value3 etc To use a Validation Function With your cursor in the appropriate record Navigate to the Valid Fnetn field Press the VALID FNCTN button and choose Use this function to validate codes Pass in the owner table and F VALIDATE SINGLE from the list column of the validation table along with the value you are verifying The function will validate the code in the convert column against the specified validation table and return an error 1f the code does not exist in the validation table Click the VIEW FUNCTION button on the Tool Bar Click the EDIT PARAMETERS button and enter the following The parameters for OWNER TABLENAME and COLUMN must parameters be enclosed in single quotes LEGACY VALUE does not require OWNER TABLE OWNER quotes
186. ult value SCTCVT Default Action D Default if null legacy value converted_val null converted val NVL spriden rec CONVERT _USER SCTCVT spriden rec SPRIDEN USER converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPRIDEN_ ENTITY IND BEGIN cur col SPRIDEN ENTITY legacy value spriden rec convert entity ind CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified Default value P Default Action D Default 1f null legacy value converted_val null converted_val NVL spriden_rec CONVERT ENTITY IND P spriden rec SPRIDEN ENTITY IND converted val Release 3 0 October 2003 SCT Converter Tool User s Guide Confidential Chapter 4 Scripts Produced by the SCT Converter Tool October 2003 Confidential Dealing with Errors If your data has errors the message after the convert script is run will indicate the number of errors that have occurred To view the details about the error go to the CUACNVT form click the VIEW ERRORS Number of Rows Converted in lt TargetTable gt _CVT Number of Rows Inserted into lt TargetTable gt Number of Rows That Errored button enter the table name in the key block and perform a Next Block function You wil
187. unction is to find the ATYP CODE PREF X for setting the APRMAIL ATYP CODE field F CVT APRXREF PRI IND This function is to figure out what to set the APRXREF CM PRI IND for the XREF X record we are dealing with at the moment Note Make sure XREF has a SPOUSE record We are only interested in doing this for spousal relationships Parameter xref code should be the converted banner value not the original plus value 1e aprxref rec aprxref xref code F CVT AUTO ID Used to verify that an ID exists for a record If an ID is not passed to the function then an X X X ID will be generated October 2003 Release 3 0 H 3 Confidential SCT Converter Tool User s Guide Appendix SCT Converter Tool Functions Financial Function Name Description Alumni Aid Finance AUTO PIDM JoceeeijIMPORTANT eeeeeeer X X X xxt MODIFY SPRIDEN CONVERT SQL kk ADD ORDER BY CLAUSE Purpose Used to generate verify that a pidm exists for a record If a NULL convert_pidm is passed to the function then a pidm will be generated from SOBSEQN since this will signify the most current record If convert_pidm has a value this means either a NAME change or an ID change is in the extract then the pidm should not be generated from sobseqn Rather we will get the pidm from the current temporary table spriden_cvt where the convert pidm is equal to the convert id and the convert change ind is null In order for this method to work the
188. ur_convert_username Password your_convert_user_password Database your_database Click on the Control Form icon on the Toolbar Control form is displayed Select Use Generated ID s if you plan to do so Identify your default directory for output SAVE your changes and EXIT the CUACNVT form displays form Insert a new record CUACNVT Table Record Insert on the Menu OR Block Press the F6 key OR Use the down arrow key to Navigate to a blank record Enter the name of the table owner Navigate to the Next Record Enter the name of the target table Navigate to the Error Action field Accept the default Error Action Continue Processing Row Navigate to the Data Input Format field and select the data format and delimiter if appropriate SAVE your changes As you save your changes the column block is populated with all the columns from the target table NEXT BLOCK CUACNVT Column To see all columns in the table after moving to the Column Block Block with your cursor in the Column field move through the records with the NEXT RECORD button or by using the down arrow The cursor is in the Column field and the first column in the Use the PREVIOUS RECORD button or the Up arrow to move back table is displayed through the records to the first column Navigate to the Load Order field if you need to change the order of the columns to be loaded by the SQL Loader control file Release 3 0 October 2003 Confidential SCT Converter Tool Use
189. us E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR SEQNO BEGIN cur col SPRADDR_SEQNO legacy value spraddr rec convert seqno CURCNVT Rules Column is Required Conversion function is specified No validation specified No default value 0 20 3 0 SCT Converter Tool User s Guide October 2003 Confidential Appendix D Sample Conversion Scripts converted_val null converted val GEN SEQUENCE SEQI IF SUBSTR converted val 1 3 ERR OR SUBSTR converted_val 1 3 THEN err msg Column conv failure converted val RAISE column error END IF IF converted val IS NULL THEN err msg Missing required value RAISE column error END IF BEGIN spraddr rec SPRADDR SEQNO TO NUMBER converted val EXCEPTION WHEN OTHERS THEN err msg Invalid Data Format RAISE column error END EXCEPTION WHEN column_error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column ISPRADDR FROM DATE BEGIN cur col SPRADDR FROM legacy value spraddr rec convert from date CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null
190. v E eir o ris esent 4 11 Appendix A Sample Data Files Used in Examples ASCII Data Files Used in Conversion Examples A 3 tn cand nieces Hr Rent A 3 spraddi ien tec Rees A 3 steve earl teenie D CS 4 Appendix Sample Table Creation Scripts Sample lt TargetTable gt _cvt_create sq 4 0 44 3 spriden cvt create sql nee ear Res B 3 cvt createsqLis ios et e pg E Ee Rr ERES B 4 spbpets Cvt create sal iu er ee tee er B 5 Appendix Sample SQL Loader Scripts Sample lt TargetTable gt _cvt ctl scripts essere C 3 6 ete enean entere i eie edes C 3 spraddr cyt etl 45 eec re t OE e eot ds C 4 spbpers o ce e e diete een C 4 Appendix D Sample Conversion Scripts Sample TargetTable convert sql scripts esessssssseseeeeeeenenenenns D 3 spriden convert sq au oe te RE e eek eee eei D 3 SPRADDR A ddress Table eese esses eene teen ener teen nennen D 17 sptaddr Re tide SERO Se et D 17 SPBPERS General Person Biographic Demographic D 34 spbpers convert sql ecce etc eit ede n eee
191. val null D 44 Release 3 0 SCT Converter Tool User s Guide October 2003 Confidential Appendix D Sample Conversion Scripts converted_val spbpers CONVERT PIN spbpers rec SPBPERS PIN converted_val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted valnullerr msg END Beginning evaluation of column SPBPERS CITZ CODE BEGIN cur col SPBPERS CITZ CODE legacy value spbpers rec convert citz code CURCNVT Rules Column is Not Required Conversion function 15 not specified No validation specified No default value converted_val null converted_val spbpers_rec CONVERT_CITZ_CODE spbpers_rec SPBPERS_ CITZ CODE converted val EXCEPTION WHEN column error THEN col status E process status E p err cur owner cur 61 cur jobid cur rec cur col legacy value converted val nullerr msg END Beginning evaluation of column SPBPERS HAIR CODE BEGIN cur col SPBPERS HAIR CODE legacy value spbpers rec convert hair code CURCNVT Rules Column is Not Required Conversion function is not specified No validation specified No default value converted_val null converted_val spbpers CONVERT HAIR CODE spbpers_rec SPBPERS HAIR CODE converted val EXCEPTION WHEN column error THEN col status E process status E
192. ves the data from the convert columns to the target table columns within the convert table then moves the data from the target table columns in the convert table to the corresponding columns in the actual target table in the database Steps in creating the scripts appear on the following pages Release 3 0 3 10 SCT Converter Tool User s Guide Chapter 3 A Conversion Example Using The SCT Converter October 2003 Confidential Using CUACNVT to produce the TargetTable create sql script With the cursor in the Table Block for the SPRIDEN table Click the CONVERT TABLE SCRIPT button The following message appears on the message line your database server name will differ from that in the example Create table script successfully written to database server u02 temp hold convertiscripts file name is spriden create sdl To view the text of the script refer to Appendix B Sample Table Creation Scripts Run this script in SQL on the server to create the convert table in the host database Release 3 0 SCT Converter Tool User s Guide 3 11 Chapter 3 Conversion Example Using The SCT Converter Tool October 2003 Confidential Using CUACNVT to produce the TargetTable cvt ctl script With the cursor in the Table Block for the SPRIDEN table Press the SOL LOADER SCRIPT button The following message appears on the message line your database server name will differ from that in the e
193. xample SQL Loader control file script successfully written to database server at u02 temp_hold conyert scripts file name is spriden cvt ctl To view the text of the script refer to Appendix C Sample SQL Loader Scripts Be sure that your spriden cvt dat file is in the same directory on the server and run SQL Loader to load the data into the convert table Consult with your DBA for the correct command for executing SQL Loader A sample command sqlldr userid your convert username your convert user password control spriden_cvt ctl Release 3 0 3 12 SCT Converter Tool User s Guide Chapter 3 Conversion Example Using The SCT Converter Using CUACNVT to produce the lt 7argetTable gt _convert sql script With the cursor in the Table Block for the SPRIDEN table Press the CONVERSION SCRIPT button Note Ifyou are planning to use the SCT Conversion Tool s Generated ID Feature be sure to mark the Use Generated IDs checkbox on the Conversion Tool Control Form before creating SPRIDEN CONVERT SQL The following message appears on the message line your database server name will differ from that in the example Conversion script successfully written to database server at u02 temp_hold convertscripts name is spriden_convertsq To view the text of the script refer to Appendix D Sample Conversion Scripts For the SPRIDEN table load and only for the SPRIDEN table load you will need to edit the spriden conver
194. y row does not appear in the list an error will occur in the row but processing will continue if you have chosen Continue Processing Row as your Error Action in the Table Block of the form Data entered into this field becomes part of a standard Oracle IN statements NOTE Do not enclose data in parentheses Validate Function Allows you to choose a function that will validate all incoming legacy values against a target validation table Use the F VALIDATE SINGLE function from the function list Default Allows you to enter a default value for a column Values should not be enclosed in quotes Default Action Required if default value is entered Choices are D efault if no legacy passed S ubstitute if no value error O verride any legacy passed October 2003 Release 3 0 2 11 Confidential SCT Converter Tool User s Guide Chapter 2 Elements of the SCT Converter Tool 2 12 Fields Description Format Mask Enter format of legacy date value so that it will be recognized and appropriately translated as a date Example Legacy date value format ddmmyy Enter this format mask in Format Mask field Convert script will recognize it as a date and translate the value to proper system date format Length Allows you to set the length of the convert columns in the convert table if they need to be larger than the target table columns in order to receive legacy data for manipulation Load Allows you to

Download Pdf Manuals

Related Search

Related Contents

Manual - MS Tecnopon  DV1FM-TR(1fiber)  Netatmo Thermostat user manual  LG L194WS User's Manual  Air King 9312 User's Manual  Content Workbench 3.0 User Manual for Supplier  AVERTISSEMENT - Eaton Canada  

Copyright © All rights reserved. Failed to retrieve file