Home

SCT Banner Technical Student Technical Training Workbook

1. SOBSEQN_FUNCTION S SOBSEQN_MAXSEONO SOBSEQON_A RECEIPT 210 15 JUN 98 ID 47 24 JUN 98 PIDM 559 26 JUN 98 ALUMNIGIFT 43 16 JUN 98 ALUMNIPLEDGE 23 07 JUN 98 EDIREQUESTID 1 25 APR 95 EDI_DCMT_SEQNO 1 O08 DEC 95 ALUMNIDUES 3 06 MAY 97 ALUMNIRECEIPT 1 31 JAN 96 EVENT A 4 18 JUN 98 HRREQ R 0O 31 JAN 96 e Maximum Sequence Number Last number used e The setting of maxseqno will be discussed in greater detail in the Conversion lesson SunGard 2004 2005 Student Technical Page 68 Section E General Person Lesson PIDM and SOBSEQN Continued IDM and SOBSEQN To use the SOBSEQN table in conversion get the maximum pidm SELECT sobseqn_maxseqno FROM saturn sobseqn WHERE sobsegqn_function PIDM Increment sobseqn_maxsegno by 1 and update SOBSEQN with the next pidm UPDATE saturn sobseqn SET sobseqn_maxseqno sobseqn_maxseqno 1 WHERE sobseqn_function PIDM ID and SOBSEQN The column sobseqn_seqno_prefix allows the client to determine the character which will precede a generated ID For example a sobseqn_seqno_prefix set to precedes the generated ID 00000001 A user can set the prefix to be any character The prefix designates that an ID is assigned by the system and not entered manually Warning If your institution is using Voice Response check for conflicts or compatibility issues with special characters SunGard 2004 2005 Student Technical Page 69 Section E General
2. Degree Information SHADEGR Degree Repeating Table SHRDGMR e Required even if a student does not have a degree SHRDGMR_ DEGC_CODE SO for seeking SHRDGMR_ DEGC_CODE DA or AW for degree awarded if student has degree e Contains major and term awarded Institutional Honors by Degree Table SHRDGIH e Used if student had institutional honors associated with the degree Departmental Honors by Degree Table SHRDGDH e Used if student had departmental honors associated with the degree Records in SHRDGIH and SHRDGDH are associated by SHRDGMR sequence numbers Note There are other academic history tables that can be populated during the conversion based on the legacy data e g SHRQPND Qualifying Papers SunGard 2004 2005 Student Technical Page 220 Section P Academic History Lesson GPA Tables Level GPA Table SHRLGPA e Cumulative institutional courses I e Cumulative transfer courses T e Overall GPA O Includes both institutional and transfer courses Term GPA Table SHRTGPA e Term statistics for institutional courses I e Term statistics for transfer courses T SunGard 2004 2005 Student Technical Page 221 Section P Academic History Lesson Pre Banner Summary Diagram Pre Banner Summary Hours and GPA Form SHAPCMP Term GPA Tabie SHRTGPA Term Sequence Course History Form SHATERM Level GPA Table SHRLGPA If detailed academi
3. cc cccccccccscssssssnsececececessessnaceeeeeeeeesessssnaeeeeeceens 113 ELE 114 E EE 115 Other SC pts iss siacasadscacdasdscedsnseesanassendncvssnepenteadanadsndeacscvashpied saadedaceasbaced snsnosaaaasbebaceossepeavenbonas 116 CONVEFSIOM ET 117 Self Check Admissions Hxvercse 118 Self Check Admissions Exercise Answer key 119 SunGard 2004 2005 Student Technical Page 107 Section H Admissions Lesson SCT Banner Student Admissions Module Diagram Courses Schedule Instructional Assignments General Person Staff Faculty Load Employees Prospects Jobs Applicants Banner HUMAN oe RESOURCES Recruits Accepted General Candidates Student Objectives Examine Review e Major amp Required Forms and Tables e Reports Processes and Procedures e Conversion of Data SunGard 2004 2005 Student Technical Page 108 Section H Admissions Lesson SCT Banner Student Admissions Module Continued Diagram Student Application Decision Form Table Admissions Application Form SAAADIVS A ER e og EN Gu Admissions Application Table SARADAP Admissions Source of Application Admissions Cohort Checklist Table Application Table Comments Table Attributes Table Admissions Table SARCHKL SARRSRC SARACMT SARAATT SARCHRT Required tables The required tables are SARADAP for Admissions application data and SARAPPD for Application decision data Major Validation T
4. Lesson Self Check Course Catalog Exercises Answer Key Continued Exercise 3 Write a select statement that would produce a catalog report which includes the following no formatting necessary subject code course number course title effective term start term course level grade mode Hint You will need to use SCBCRSE and 3 other tables SQL gt SELECT scbcrse_subj_ code 2 scbcrse_crse_numb scbcrse_title scbcrse_eff_term scbcrky_term_code_start scrlevl_levl_code scrgmod_gmod_code FROM scrgmod scrlevl scbcrky scbcrse WHERE scbcrse_subj_code scrlevl_subj_code 10 AND scbcrse_crse_numb scrlevl_crse_numb 11 AND scbcrse_eff_term scrlevl_eff_term 12 AND scbcrse_subj_code scrgmod_subj_code 13 AND scbcrse_crse_numb scrgmod_crse_numb 14 AND scbcrse_eff_term scrgmod_eff_term 15 AND scbcrse_subj_code scbcrky_subj_code 16 AND scbcrse_crse_numb scbcrky_crse_numb 17 v OO JO D amp WwW SunGard 2004 2005 Student Technical Page 49 Section D Referential Integrity Lesson Overview Objectives At the end of this section you will be able to Describe referential integrity concepts and how they are implemented in SCT Banner Prerequisites To complete this section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Section Contents OLTA A E A E A 50 IR Shere tii ee 51 Referential Integrity Ilustrated EE 52 R f r ntial Integrity
5. Once duplicate records are entered for the same person it is very difficult and time consuming to correct the problem especially if financial transactions have occurred SunGard 2004 2005 Student Technical Page 73 Section E General Person Lesson General Person Procedures Continued Enter General Person information Navigate to SPAPERS Enter SSN SIN in Canada Enter Birth Date Enter Confidentiality Indicator Save Exit SPAPERS Place holds on records Navigate to SOAHOLD Use LOV field to see list of holds Place 2 different types of holds on your record Save Query for your record in SPRIDEN Describe SPRIDEN Write a query to retrieve the data that was entered in SPRIDEN today where spriden_activity_date like sysdate Notice the data in spriden_change_ind spriden_search_last_name spriden_soundex_last_name spriden_entity_ind spriden_pidm SunGard 2004 2005 Student Technical Page 74 Section E General Person Lesson General Person Procedures Continued Query for your record in SPBPERS e Describe SPBPERS e Write a query to retrieve the data that you entered in SPBPERS where spbpers_activity_date like sysdate Notice the data in e spbpers_prefix spbpers_suffix spbpers_ssn spbpers_confidential_ind spbpers_activity_date Note SSN is NOT required If institution does not use SSN or SIN Canada for ID yet wishes to keep SSN stored in database for other purposes SSN mus
6. SLBBLDG gare e Section Meeting Location Building Faculty Member Table SIBINST SunGard 2004 2005 Student Technical Page 154 Section L Schedule Lesson Section General Information Form SSASECT Section General Information Base Table SSBSECT Purpose e Used to build and maintain schedule of classes e Much of the data defaults from Course Catalog SCBCRSE etc e Connections with AR module through billing hours tuition waivers SunGard 2004 2005 Student Technical Page 155 Section L Schedule Lesson Term Control Form SOATERM Purpose e Related tables Term Control Table SOBTERM and Section Part of Term Validation Table SOBPTRM e Used to set up controls for each term s schedule registration and fee assessment CRN oneup Before building the Schedule for a term a beginning CRN must be set CRN Oneup There are more details about SOATERM and the underlying tables SOBTERM and SOBPTRM in Lesson 14 Registration Major Validation Tables Forms Term Code Validation Form Table STVTERM Level Code Validation Form Table STVLEVL Part of Term Code Validation Form Table STVPTRM Campus Code Validation Form Table STVCAMP Section Status Code Validation Form Table STVSSTS Schedule Type Code Validation Form Table STVSCHD Subject Code Validation Form Table STVSUBJ Grading Mode Code Validation Form Table STVGMOD Day of Week Code Validation Form Table STVDAYS Detail Charge Pay
7. select column_name from all_cons_columns where table name SARADAP and constraint_name PK_SARADAP select table_name comments from all_tab_comments where table_name like SA SunGard 2004 2005 Student Technical Page 114 Section H Admissions Lesson Reports Reports e Admissions Count by College Major Report SARACTM This report prints admission application count by college major o C program run from Job Submission Admissions Application Report SARADMS Admission Decision Criteria Report SARDCSN Other reports and purge processes are also available for the Admissions module Refer to Chapter 10 of the Student System Technical Reference Manual for a complete list of reports processes SunGard 2004 2005 Student Technical Page 115 Section H Admissions Lesson Other Scripts BANNER_HOME student dbprocs functions saf BANNER_ HOME student views views sav savadm0 sql creates as_admissions_applicant Some views are used in conjunction with the Object Access method of retrieving data from database This method uses the concept of layered views you must have the GTVSDAX form table populated with crosswalk values For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 116 Section H Admissions Lesson Conversion Is
8. 1 5 92 Converts to 05 JAN 1992 If you are querying information and part of the query is a year you need to enter the century and the year to insure accuracy in your selections Note Century can be overwritten when doing data entry Oracle format In writing scripts reports etc the ORACLE DD MON RR date format provides additional flexibility e 50 99 20th century e 00 49 21st century See Oracle7Server SOL Language Reference Manual SunGard 2004 2005 Student Technical Page 72 Section E General Person Lesson General Person Procedures Add a new person Navigate to SPAIDEN Add a person to the form Generate an ID Enter Name Information including suffix or prefix Add Address Information Save this information Rollback to the key block Edit a person s ID address Enter SPAIDEN again Next Block Change the ID Save Change the middle name Save Add another address different type Save Rollback to the key block Query for a person Use the LOV field to access SOAIDEN Perform a query to find the person you just entered Notice the change indicators I N Exit SPAIDEN Name search consistency It is important for data entry staff to perform extensive careful and consistent name searches before entering a new person entity into the system Users need to know that their username is stored in the table along with the data they entered so their errors are traceable
9. 91 Conversion Tee 92 SUMMATY EE 93 Self Check Curriculum Program Rules Exeroses 94 Self Check Curriculum Program Rules Exercises Answer key 95 SunGard 2004 2005 Student Technical Page 97 Section G Recruiting Lesson SCT Banner Student Recruiting Module Diagram Courses Schedule Instructional Assignments Staff Faculty Load Load Employees Prospects 8 Jobs Applicants Benner HUMAN RESOURCES Recruits Admissions Admissions Accepted E A Candidates E A This section covers the Recruiting module If it is implemented it would be best to follow this order working on Recruitment after Catalog General Person and Curriculum Rules Objectives Examine e Major amp Required Forms and Tables e Reports Processes and Procedures e Conversion of Data SunGard 2004 2005 Student Technical Page 98 Section G Recruiting Lesson SCT Banner Student Recruiting Module Continued Diagram Prospect Information Form SRARECR Recruiting Base Table SRBRECR Source of Recruit Cohort Recruiting Recruiting Recruiting Table Table Attribute Table Comment Table SRRRSRC SRRCHRT SRRRATT SRRRCMT Only one table is required for conversion if you choose to convert if you do you must also have appropriate validation tables set up Major forms and tables Major Form e Prospect Application Form SRARECR Major Tables Recruiting Base Table SRBRECR Source of Recruit Table SRRRSRC
10. Decode SPBPERS_SEX decode_sex sql spool decode update sytpers set spbpers_sex decode spbpers_sex Mt IET FO TMI IN 3 spool off Usage This is an example of using the update statement to decode a value in the sytpers temp table This script is run after the temp table is loaded A simple example of how cross walking can be done within the temp table rather than on the legacy side Whichever is easier for you SunGard 2004 2005 Student Technical Page 248 Section Q Conversion Lesson Conversion Example Check data in the temp tables select spriden_id substr spriden_last_name 1 15 T wa spriden_first_name spriden_change_ind IND spriden_entity_ind ENT spriden_activity_date spriden_pidm spraddr_pidm spbpers_pidm spraddr_street_linel spraddr_city spraddr_stat_code spraddr_zip spbpers_sex spbpers_birth_date from sytiden sytaddr sytpers where spriden_pidm spraddr_pidm and spriden_pidm spbpers_pidm order by spriden_pidm Usage Check the data in the temporary tables This is just one example of a simple script to use for checking SunGard 2004 2005 Student Technical Page 249 Section Q Conversion Lesson Conversion Example Insert Statement Code Insert into SATURN tables insert_real sql spool insert_real insert into spriden select from sytiden insert into spraddr select from sytaddr insert into spbpers select from sytpers spool off Usage Now insert th
11. Exercises Exercise 1 Write a query which returns full name id level the level code associated with gpa hours and calculations and term gpa for institutional work for a given term Prompt user for term SunGard 2004 2005 Student Technical Page 229 Section P Academic History Lesson Self Check Academic History Exercises Continued Exercise 2 Write a query which returns full name id course level crn subject code course number and grades for a given term Prompt user for term SunGard 2004 2005 Student Technical Page 230 Section P Academic History Lesson Self Check Academic History Exercises Continued Exercise 3 Write a query which returns full name id level all transfer courses and grades for all students who have transfer work Order by student last name SunGard 2004 2005 Student Technical Page 231 Section P Academic History Lesson Self Check Academic History Exercises Answer Key Exercise 1 Write a query which returns full name id level the level code associated with gpa hours and calculations and term gpa for institutional work for a given term Prompt user for term select substr spriden_last_name 1 15 II substr spriden_first_name 1 15 spriden_id shrtgpa_term_code shrtgpa_levl_code shrtgpa_gpa from spriden shrtgpa where shrtgpa_pidm spriden_pidm and spriden_change_ind is null and shrtgpa_term_code amp term and sh
12. SUNGARD SCT HIGHER EDUCATION SCT Banner Technical Student Technical Training Workbook January 2005 Release 7 Confidential Business Information This documentation is proprietary information of SunGard 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 SunGard SCT Prepared By SunGard SCT 4 Country View Road Malvern Pennsylvania 19355 United States of America SunGard 2004 2005 All rights reserved The unauthorized possession use reproduction distribution display or disclosure of this material or the information contained herein is prohibited In preparing and providing this publication SunGard SCT is not rendering legal accounting or other similar professional services SunGard SCT makes no claims that an institution s use of this publication or the software for which it is provided will insure compliance with applicable federal or state laws rules or regulations Each organization should seek legal accounting and other similar professional services from competent providers of the organization s own choosing SunGard the SunGard logo SCT and Banner Campus Pipeline Luminis PowerCAMPUS SCT fsaATLAS SCT Matrix SCT Plus SCT OnSite and SCT PocketRecruiter are trademarks or registered trademarks of SunGard Data Systems Inc or its subsidiaries in the U S and other countries All other trade name
13. Student Billing Control Form TSACTRL GACTRL TBBCTRL The Billing Control Table TBBCTRL is used with each of these forms These forms are required for conversion Student Technical SunGard 2004 2005 Page 179 Section N Accounts Receivable Lesson Detail Code Control Form TSADETC TSADETC e Enter detail code information e Establish payment priorities used in the Application of Payment Process TGRAPPL Priority e Priority is an algorithm determined 3 digit code that determines a payment hierarchy select distinct tbbdetc_priority from tbbdetc will display which priorities have been set e 0 is a wildcard in priority codes it will pay anything o TBBDETC TBRACCT tables o Establishes interface with Finance package SCT Banner Finance or a third party package o Set up fund codes account numbers o Required for conversion SunGard 2004 2005 Student Technical Page 180 Section N Accounts Receivable Lesson AR Rules Forms TSASBRL Schedule Bill Rules Form TSASBRL This form sets up parameters used in the Student Billing Statement Process TSRCBIL TBBSBRL is its related table TSATBDS Term based Designator Rules Form TSATBDS This form allows users to establish relationships between term codes and term based AR designators TBBTBDS is its related table SunGard 2004 2005 Student Technical Page 181 Section N Accounts Receivable Lesson TGACREV TGACSPV TGACREV Cashier
14. e How do you determine where to put it in SCT Banner Note It is generally not recommended to convert a schedule but to enter it manually instead Schedule creation After Catalog data is entered or converted the Schedule can be created If it is decided that conversion of legacy data is required it may be advisable to manually enter several courses into SSASECT to ensure that all of the Catalog data that should default to the sections does so properly SunGard 2004 2005 Student Technical Page 161 Section L Schedule Lesson Self Check Schedule Exercise Exercise Write a query that returns full name id crn subject code course number section number course title and term code for all faculty members teaching any English course Prompt the user for the term SunGard 2004 2005 Student Technical Page 162 Exercise Section L Schedule Lesson Self Check Schedule Exercise Answer Key Write a query that returns full name id crn subject code course number section number course title and term code for all faculty members teaching any English course Prompt the user for the term select substr spriden_last_name 1 15 a substr spriden_first_name 1 15 spriden_id ssbsect_crn ssbsect_subj_code ssbsect_crse_numb a scbcrse_ title ssbsect_seq_ numb a scbcrse_eff_term ssbsect_term_code from spriden ssbsect scbcrse a sirasgn where sirasgn_pidm spriden_pidm and spriden_ch
15. table_type comments from all_tab_comments where table_name like SO and table_type TABLE e Are any data elements required in SORTEST SORHSCH SORPCOL o Examine GUROPTM select from guroptm where guroptm_form_name SOATEST This will show what forms are related to SOATEST and how they are related triggers etc e How many overall tables are there and what is a description of their content select table_name table_type comments from all_tab_comments where table_name like SO and table_type TABLE SunGard 2004 2005 Student Technical Page 123 Section l Overall Forms and Tables Lesson Conversion Issues Questions e Will Overall data be converted or entered manually by the users e What Overall data do you have in your legacy system e How do you determine where to put it in SCT Banner SunGard 2004 2005 Student Technical Page 124 Section I Overall Forms and Tables Lesson Reports Processes Interface Tape Load Process SORTAPE Run the SORTAPE process from Job Submission This process uses the conversion and default values from SOBCNVT If the process fails it is possible that a code does not have the appropriate conversion value One of the parameters of this process allows you to delete the record s from the temporary tables as it gets inserted into the SCT Banner table If you need to rerun SORTAPE because a conversion value is not in SOBCNVT you may add the
16. Definition Table e Populate TBRACCT Detail Code Account Definition Table fund and account codes TBBDETC and TBRACCT must be populated before fee assessment can take place Refer to Student Technical Ref Manual pp 5 12 5 15 SunGard 2004 2005 Student Technical Page 193 Section N Accounts Receivable Lesson Self Check Accounts Receivable Exercises Exercise 1 Find all columns in the Accounts Receivable module associated with detail codes Exercise 2 Write a simple report that will show full name id term and balance from the student account detail table for a given term for those students with a balance gt 0 Prompt user for term SunGard 2004 2005 Student Technical Page 194 Section N Accounts Receivable Lesson Self Check Accounts Receivable Exercises Answer Key Exercise 1 Find all columns in the Accounts Receivable module associated with detail codes select owner table_name column_name comments from all_col_comments where owner TAISMGR and column_name like DET _CODE Exercise 2 Write a simple report that will show full name id term and balance from the student account detail table for a given term for those students with a balance gt 0 Prompt user for term select substr spriden_last_name 1 12 substr spriden_first_name 1 10 spriden_id sum tbraccd_ balance from spriden tbraccd where spriden_pidm tbraccd pidm and spriden_change_ind is
17. ENABLED FK1_SCBCRSE_INV_STVDIVS_CODE R ENABLED FK1_SCBCRSE_INV_STVPWAV_CODE R ENABLED FK1_SCBCRSE_INV_STVREPS_CODE R ENABLED FK1_SCBCRSE_INV_STVSUBJ_CODE R ENABLED FK1_SCBCRSE_INV_STVTERM_CODE R ENABLED Type R Referential Integrity Constraint This illustrates the connection to the Validation tables SunGard 2004 2005 Student Technical Page 59 Section D Referential Integrity Lesson Validation Tables Codes Example POSITIONS 9th 14th SCBCRSE_SUBJ_CODE TABLE NAME Validation Description V STV SUBJ SunGard 2004 2005 Student Technical Page 60 Section D Referential Integrity Lesson Referential Integrity Summary Summary Enforces unique non null columns Establishes relationship between parent and child tables Parent table row has the Primary Key constraint Child table row has the Foreign Key constraint Parent row can not be deleted when a child row exists the child row must be deleted first Examples 199101 must exist in STVTERM before inserting a record in SCBCRKY with term_code_eff 199101 199101 cannot be deleted from STVTERM if SCBCRKY record exists with 199101 term_code_eff Check constraints to enforce integrity issues specified by the check condition prefix would be cc Unique constraints designates a column or a combination of columns as a unique key prefix is uk SunGard 2004 2005 Student Technical Page 61 Section E General Person Lesson O
18. Faculty Assignment Form SIAASGN Faculty Assignment Table SIRASGN SIAASGN SIRASGN This form and table contain faculty teaching assignments for a particular term It is populated automatically when a faculty member is entered on the SSASECT form in the schedule module if records exist in SIBINST faculty status Parts of SIAASGN are updated automatically when faculty information is entered in the SSASECT form The Assignment Type field IS NOT updated and must be updated manually After building sections in SSASECT SSBSECT assignment information will be present in SIRASGN SIRASGN is a good table to use for faculty load reports You will need to include SIRASGN in schedule reports to get faculty pidms in connection with class assignments SunGard 2004 2005 Student Technical Page 133 Section J Faculty Load Lesson SQL Plus Questions e What tables are part of the Faculty Load Module select table_name from all _tables where table_name like SI e What data elements are required desc sibinst o Notice the NOT NULL columns e What are the key fields in sibinst select column_name from all_cons_columns where table_name SIBINST and constraint_name PK_SIBINST Warning SIBINST_ADVR_IND MUST be filled in if you want to use faculty as advisors when you get to General Student even though it is not required by the table SunGard 2004 2005 Student Technical Page 134 Section J
19. Faculty Load Lesson Reports and Processes Reports and processes Faculty Load Purge SIPASGN Faculty Schedule Report SIRASGQ Faculty Load Contract Analysis Report SIRCTAL Faculty Load Term Analysis Report SIRTRAL Refer to the Student Technical Reference Manual Chapter 10 for additional information on Student Module reports and processes Don t forget that the Person Directory Process SPRPDIR can generate the directory information including for faculty SunGard 2004 2005 Student Technical Page 135 Section J Faculty Load Lesson Other Scripts BANNER_HOME student dbprocs functions sif BANNER_HOME student views views Siv Some views are used in conjunction with Object Access method of retrieving data from database using the concept of layered views you must have the GTVSDAX form table populated with crosswalk values Clients should know the naming conventions and the locations of these other database object creation scripts To use them as models create your own MODS directory as discussed earlier in this course and put any modifications in there For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 136 Section J Faculty Load Lesson Conversion Issues Questions e Will Faculty Load data be converted or entered
20. Faculty Load Module Diagram Academic History Instructional Assignments Grades and Sections SC Prerequisites Registration RESOURCES J Enrollment Location Management Housing and Meal Assignments Banner General aan Student racuates ADVANCEMENT Overview Faculty Load enables users to enter and maintain information including instructional and non instructional assignments for a faculty member or advisor Personnel information such as tenure status and sabbatical dates is maintained in this module along with workload and contract information Faculty MUST have a SPRIDEN record etc before they can be designated as Faculty Advisor or Instructor A person must be flagged as an instructor in the SIBINST table SIAINST form before he she can be assigned an instructional section in SCHEDULE SunGard 2004 2005 Student Technical Page 130 Section J Faculty Load Lesson Faculty Load Forms and tables Faculty Information Form SIAINST Faculty Member Base Table SIBINST Faculty Contract Faculty Member Faculty Attribute Faculty Comment Type Repeating Department and Information Information Table College Repeating Table Repeating Table SIRICN Information SIRATTR SIRCMN 5 Repeating Table 5 SIRDPCL Faculty Load is a swing module which may be shared between HR and Student Decisions need to be made about ownership responsibility of maintenance e
21. Flow Diagrams SunGard 2004 2005 Student Technical Page 238 Section Q Conversion Lesson Conversion Steps Continued Notes Conversions can be automatic manual or a combination of both Validation information can be keyed in by end users If RI is turned on much error handling does not have to be built into scripts or programs When doing a conversion keep in mind that both form based and table based rules must be met SunGard 2004 2005 Student Technical Page 239 Section Q Conversion Lesson Conversion Strategies Strategies Create data standards especially for names and addresses All offices need to agree to and document data standards Determine whether you will enter the data electronically or manually e g Some validation tables forms can be entered manually in both the preproduction and production databases if the number of records is small unlike STVSBGI Determine which tables you will be using May be helpful to look at the forms with the users then you will be able to determine the tables used Mapping legacy data to SCT Banner Review the legacy to SCT Banner mapping with the users Create a document programmers can use that tells how to convert the data Create a Conversion Plan document Review the steps that are involved to get to your go live dates Create a time line Determine the processes that need to be written o Will data need to be translated o Will data need to be cleaned up
22. Key KEE 53 Primary Key Constraints eegetigerbut reg bgieiechk easar eege Kegel 54 Foreign Key ISU ANAS ss dacacs ce cts ceade nsgeneaszgasdeqsungaceenvanviaeanasesenndactandanantaasphusaeneaaoeaadaaveucoeeaans 56 Creating Foreign Key Onsale sisses tiroan a rera Er PATE E EERE 58 Validation Tables Codes E 60 Referential Integrity Summary E 61 SunGard 2004 2005 Student Technical Page 50 Section D Referential Integrity Lesson Referential Integrity Importance Referential integrity is reviewed here because of the importance of understanding it in relation to conversion All required validation tables needed for a module must be populated before populating other data tables within each module Ideally those attending this class should have taken the DBA Toolkit class or have a basic familiarity with SQL Types of Data Integrity e Nulls e Unique Column Values e Primary Key Values e Referential Integrity Source Oracle 7 Server Concepts In any discussion of implementation of SCT Banner and conversion to SCT Banner we must consider referential integrity which has a direct influence over the order of conversion and implementation SunGard 2004 2005 Student Technical Page 51 Section D Referential Integrity Lesson Referential Integrity Illustrated Diagram STVTERM Referenced or Parent Values stvterm_code 199610 199710 199810 199910 SCBCREKY SCBCREY Dependent or schcrky_term_code_star
23. L Schedule Lesson Schedule Module Academic History Instructional Assignments E Grades and Sections Des Prerequisites Faculty Load Jobs Registration RESOURCES J Enrollment Location f Management Housing and Meal i Assignments enera Student Graduates At this point Catalog has been built buildings and rooms have been defined and Faculty are active and available for scheduling in sections Also terms SOATERM have been defined Banner ADVANCEMENT SunGard 2004 2005 Student Technical Page 153 Section L Schedule Lesson Schedule Module Continued Objectives Examine Review Major amp Required Forms and Tables Reports Processes and Procedures Conversion of Data Overview Build and print a schedule of classes including term attributes such as date for each session within a term Establish course reference numbers Assign instructors to classes Schedule classes in rooms Allow users to roll the schedule forward to next applicable term to decrease the data entry process Diagram Detail Chre Grading Code Payment Code Maintenance Definition Table Table TBBDETC SHRGRDE Term Control Form Section General Information Form SOATERM SSASECT Term Base Table Section General Information Base Table SOBTERM SSBSECT Section Part of Term Validation Table SOBPTRM Faculty Member i Sg Instructor Times Table Description Table Assi t Tabl SSRMEET
24. Management Lesson Location Management Module Continued Major forms and tables e Building Definition Form SLABLDG o Table SLBBLDG e Room Definition Form SLARDEF o Table SLBRDEF If your institution is residential e Dorm Room amp Meal Application Form Table SLARMAP SLBRMAP o Room Application Form Table SLARASG SLRRASG Major Validation Tables Forms STVBLDG STVCAMP STVRRCD STVHAPS STVARTP STVTERM STVASCD TTVDCAT TTVTAXT for Canadian use only TBBDETC must also be populated for those making residence hall assignments AR table TBBDETC must be set up for residential institutions before they can assign residence hall rooms SunGard 2004 2005 Student Technical Page 144 Section K Location Management Lesson SQL Plus Questions e What tables are part of the Location Management Module select table_name from all_tables where table_name like SL e What data elements are required desc slbrdef o Notice the NOT NULL columns e What are the key fields in SLBRDEF select column_name from all_cons_columns where table name SLBRDEF and constraint_name PK_SLBRDEF Fields of note e slrrasg_assess_needed This field identifies whether fee assessment is needed for the room assignment SLRFASM FEE ASSESSMENT PROCESS looks at that field to determine which records should be assessed fees e slrrasg_ar_ind This field identifies whether the room assignment charges have
25. Meal Assignments Graduates General ADVANCEMENT Student Objectives Examine Review e Major amp Required Forms and Tables e Reports Processes and Procedures e Conversion of Data Overview Allows for the definition of the institution s building and room facilities Provides a means of assigning rooms for special events Provides a listing of available rooms with attributes Maintains dormitory meal plan phone assignments and assessments SunGard 2004 2005 Student Technical Page 142 Section K Location Management Lesson Location Management Module Continued Overview continued Location Management Control RoomiMeal Phone Location Rate Cade Rules Management FormTable Term Control SLALMPE FormiTable SERIMFE SLATERMS SLBTERM Room Assignment Housing Refund Status by Term Percentage Table FormiTable SLRRFCR SLAASCD SLRASCD Building Room Definition Definition Form Form SLABLDG SLARDEF Dorm Room amp Meal Application Form Table SLARMAP SLBRMAP Location Building Room Attributes Description Table Table SLBBLDG SLBRDEF Room Assignment Housing Special Form Table Requirements SLARASG Table SLRRASG SLRPREQ SLBBLDG and SLRRDEF are required for scheduling If you are a residential institution you will need to populate SEBRMAP and SLRRASG as well SunGard 2004 2005 Student Technical Page 143 Section K Location
26. SOACURR and SOACTRL SunGard 2004 2005 Student Technical Page 86 Section F Curriculum Program Rules Lesson Curriculum Program Rules Overview Continued Major tables Program Definition Rules Table SMRPRLE Curriculum Rules Form SOBCURR Curriculum Rules Control Table SOBCTRL Curriculum Major Rules Table SORCMJR Curriculum Minor Rules Table SORCMNR Curriculum Concentration Rules Table SORCCON Major validation tables Term Code Validation Form Table STVTERM Level Code Validation Form Table STVLEVL College Code Validation Form Table STVCOLL Degree Code Validation Form Table STVDEGC Campus Code Validation Form Table STVCAMP Department Code Validation Form Table STVDEPT Major Minor Concentration Code Validation Form Table STVMAJR These validation tables must be set up if you use curriculum rules SunGard 2004 2005 Student Technical Page 87 Section F Curriculum Program Rules Lesson Program Definition Rules Form SMAPRLE SMAPRLE SMRPRLE is the underlying table Program is required only if CAPP s Program Planning indicator is set to Yes in SOACTRL sobctrl_program_ind Y Part of CAPP module Program is not required unless you are using CAPP and or unless you set CAPP s Program Planning Indicator to Y on SOACTRL When program is used on a curriculum rule the following must match on SOACURR what is defined in SMAPRLE Level Campus College Deg
27. Session Review Form TGACREV This form is used to review all charge or payment activity for a specific session The Cashier Session Table TBBCSHR is its related table TGACSPV Cashier Supervisory Form TGACSPV This form is used to display all active and inactive cashiering sessions on the system The Cashier Session Table TBBCSHR is its related table SunGard 2004 2005 Student Technical Page 182 Section N Accounts Receivable Lesson Student Account Detail Form TSADETL TSADETL This form holds account detail by detail code e Major Table Student Account Detail Review Table TBRACCD The form also displays deposits memos and comments e Tables Deposit Table TBRDEPO Chrg Payment Detail Memo Table TBRMEMO Comment Table TBRCMNT SunGard 2004 2005 Student Technical Page 183 Section N Accounts Receivable Lesson Student Account Detail Review Form TSAAREV TSAAREV This form is used to review and enter information about an account It presents an online view of each transaction by term The Student Account Detail Review Table TBRACCD is its related table which is also accessed from SFAREGS SunGard 2004 2005 Student Technical Page 184 Section N Accounts Receivable Lesson Student Payment Form TSASPAY TSASPAY This form is used to determine status of student s account for a term It can be used to accept charges and disburse Financial Aid This form is affected by
28. Student Information Table SIBINST Faculty Information Table SPRCOLR Address Collector File SPRTELE Telephone Number Table View used in SPRPDIR pc e SPVADDS Address View Parameters Term Type Confidentiality Indicator Address Type Print ID Faculty type A I B Population Selection Can Be Used C program Run via Job Submission Example To see a view that would be handy to modify for reporting purposes take a look at gpvent0 sql in BANNER_HOME general views gpvent0 sql SunGard 2004 2005 Student Technical Page 77 Section E General Person Lesson Conversion Issues Issues What additional general person data do you have in your legacy system e Become familiar with all General Person forms and tables select table_name comments from all_tab_comments where table_name like SP How do you determine where to put it in SCT Banner e Consult users about where to put data SunGard 2004 2005 Student Technical Page 78 Section E General Person Lesson Other Scripts BANNER_HOME general views views gpv ag_entity_data Object Access view which presents general person data gpvent0 sql Object Access views are used in conjunction with the Object Access method of retrieving data from database This uses the concept of layered views you must have the GTVSDAX form table populated with crosswalk values Clients should know the naming conventions and the locations of these other
29. SunGard 2004 2005 Student Technical Page 45 Section C Course Catalog Lesson Self Check Course Catalog Exercises Answer Key Exercise 1 Get the following information about any two of the Course Catalog module tables Table Owner Table Name Column Name Data Type Null Not Null Column Hint You will use the data dictionary view all_tab_columns Step 1 SQL gt desc all_tab_columns Name Null Type OWNER NOT NULL VARCHAR2 30 TABLE NAME NOT NULL VARCHAR2 30 COLUMN_NAME NOT NULL VARCHAR2 30 DATA TYPE VARCHAR2 9 DATA_LENGTH NOT NULL NUMBER DATA PRECISION NUMBER DATA SCALE NUMBER NULLABLE VARCHARZ2 1 Step 2 SQL gt SELECT owner table_name column_name data_type nullable FROM all_tab_columns WHERE table name like SC SunGard 2004 2005 Student Technical Page 46 Section C Course Catalog Lesson Self Check Course Catalog Exercises Answer Key Continued Exercise 2 Get the following information from the course catalog module about all courses with a subject code of ENGL subject code course number course title effective term start term end term Hint You will need to use SCBCRSE and one other table Step 1 SQL gt desc scbcrse SQL gt desc scbcrky Step 2 SQL gt SELECT scbcrse_subj_code scbcrse_crse_numb scbcrse_title scbcrse_eff_term scbcrky_term_code_start scbcrky_term_code_end FROM scbcrky scbcrse WHERE scbcrse_subj_code scbcr
30. VIEWS directories e C Pro C and C source files e COB Pro COBOL files UNIX only e COBPCO Pro COBOL files VAX VMS only e COM DCL command files VAX VMS only e DATA Course request and scheduler input directory under COB UNIX only or COBPCO VMS only e FORMS ORACLE FORMS fmb fmx pll and lib files e INSTALL SCTDMP file used during initial install e LOADER ORACLE SQL LOADER ASCII to Oracle Tables e MISC Shell scripts UNIX only COM DCL command files for VAX VMS e PLUS SQL PLUS scripts e VIEWS SQL PLUS scripts to recreate views e DBPROCS SQL PLUS scripts to recreate database procedures packages functions triggers SunGard 2004 2005 Student Technical Page 23 Section B Student Technical Training Overview Lesson Directory Structure for Client Developed Items Diagram Client developed reports forms views etc that will be used within Banner should use W Y or Z as the second character in the name ofthe object E g SWRDESC pc S Student W Client developed R Process DESC 4 char description MODS directory The MODS directory is a separate directory for client modifications Within the MODS directory you would find another STUDENT directory etc Examples of modifications found here include forms modifications to C programs client created views functions etc SunGard 2004 2005 Student Technical Page 24 Section B Student Technical Training Overview Le
31. creation scripts To use them as models create your own MODS directory as discussed earlier in this course and put any modifications in there For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 226 Section P Academic History Lesson Conversion Issues Questions Will detailed academic history data be converted Will you convert ALL academic history records or only a certain number of years What academic history data do you have in your legacy system How do you determine where to put it in Banner SunGard 2004 2005 Student Technical Page 227 Section P Academic History Lesson Reports Processes End of Term End of Term processes Grade Roll to Academic History Process SHRROLL Repeat Equivalent Course Check Process SHRRPTS Calculate GPA Process SHRCGPA Calculate Academic Standing Process SHRASTD Grade Mailer Process SHRGRDE Student Type Update SHRTYPE Refer to the Student Technical Reference Manual Chapter 10 for additional information on Student Module reports and processes Additional processes e Academic Transcript Process SHRTRTC o Can be run in sleep wake mode e Degree Status Update Process SHRDEGS SunGard 2004 2005 Student Technical Page 228 Section P Academic History Lesson Self Check Academic History
32. each element of the name SunGard 2004 2005 Student Technical Page 57 Section D Referential Integrity Lesson Creating Foreign Key Constraints Alter Statement FKn_ foreign table_ INV_ Primary table_ CODE FK1_SCBCRKY_INV_STVTERM_CODE alter table SCBCRKY add constraint FK1_SCBCRKY_INV_STVTERM_CODE foreign key SCBCRKY_TERM_CODE_START references SATURN STVTERM STVTERM_CODBE Before you can enter a term code in SCBCRKY it must exist in STVTERM STVTERM codes cannot be deleted if they exist in other tables Example 1 select constraint_name from all_constraints where table_name SCBCRKY SYS_C002703 NOT NULL SYS_C002704 NOT NULL SYS_C002705 NOT NULL SYS_C002706 NOT NULL SYS_C002707 NOT NULL PK_SCBCRKY PRIMARY KEY CONSTRAINT FK1_SCBCRKY_INV_STVSUBJ_CODE Foreign Key for Subject Code FK1_SCBCRKY_INV_STVTERM_CODE Foreign Key for term_code_start FK2_SCBCRKY_INV_STVTERM_CODE Foreign Key for term_code_end SunGard 2004 2005 Student Technical Page 58 Section D Referential Integrity Lesson Creating Foreign Key Constraints Continued Example 2 SQL gt SELECT constraint_name constraint_type status FROM all_constraints WHERE table_ name SCBCRSE CONSTRAINT_NAME CON TYPE STATUS FK1_SCBCRSE_INV_STVAPRV_CODE R ENABLED FK1_SCBCRSE_INV_STVCIPC_CODE R ENABLED FK1_SCBCRSE_INV_STVCOLL_CODE R ENABLED FK1_SCBCRSE_INV_STVCSTA_CODE R ENABLED FK1_SCBCRSE_INV_STVDEPT_CODE R
33. ege 225 EELER geed eneen 226 CONVERSION USSUCS nascer riire ini E EAEE REEE E 227 Reports Processes End of Term ssssssssessessseseseseesseeesseessesserssetessseessresseesseeesseesssresseesse 228 Self Check Eleng 229 Self Check Academic History Exercises Answer ken 232 SunGard 2004 2005 Student Technical Page 211 Section P Academic History Lesson Student System Overview Academic Courses Histor Ss y and GPA Instructional Assignments Grades and Banter Sections Ge FINANCIAL General Prerequisites AID Person Stall raculty Load Disbursements Registration Accounts Applicants Receivable RESOURCES Enrollment Location Hee NM Management Charges and Housing z Payments Recruits and Meal EE SE Assignments Banner T ccepte ener Been Candidates Student Graduates ADVANCEMENT FNANCE This overview diagram shows Academic History and its relationship with all other modules of the SCT Banner Student System Pr SE Jobs ospects p Registration Fees SunGard 2004 2005 Student Technical Page 212 Section P Academic History Lesson Academic History Module Diagram Instructional Assignments Grades and Sections a Prerequisites Faculty Load RESOURCES Enrollment Location a Management Housing and Meal m i Assignments enera dindant Graduates Academic History should be implemented prior to Registration if pre and or co requisite checki
34. null and tbraccd_balance gt 0 and tbraccd_term_code amp term group by spriden_last_name spriden_first_name spriden_id order by spriden_last_name SunGard 2004 2005 Student Technical Page 195 Section O Registration Lesson Overview Objectives At the end of this section you will be able to Describe the role and functions of the Registration module Prerequisites To complete this section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Section Contents OVETVIEW eegene 196 een 197 Registration Mod le 2 accra cu aaasssanencounsas Gaevensqalentaieae EE r EE EEE ETR E E rI 198 Fee Assessment mesne artane t cated es a eas i ae e ed a aaaea 203 DO a EE E 204 Reports Be 205 E ENEE 206 Other SCH pts sascdsscsiadscacsasnsnecnseopanesaubsncvesnopententosacsndaacacassnpied aaadedaceaslanedsnsnosaaaasbebaceossepenvenbonse 207 Conversion eege Eet eer 208 Self Check Registration EXerCiSE cscreyes sagacccces is acd arn arenas AE 209 Self Check Registration Exercise Answer key 210 SunGard 2004 2005 Student Technical Page 196 Section O Registration Lesson Student System Overview Diagram Academic Courses Catalog Courses Credits y and GPA Instructional z Assignments anner Sectiony Tad and FINANCIAL General Prerequisites AID Person Stall lraculty Load Empl Revistrati Disbursements mployees Jobs egistration Prospects p Registratio
35. o SFRSTCR Table containing Course Registrations e A registration record SFBETRM may be created without courses Be aware of this when writing reports using SFBETRM The Registered Not Paid Process SFRRNOP when run in update mode will clean out these records Alternatively a script written to delete unwanted SFBETRM records may be necessary SunGard 2004 2005 Student Technical Page 202 Section O Registration Lesson Fee Assessment Fee Assessment Fee Assessment may be performed online via SOATERM or in batch via the Batch Fee Assessment Process SFRFASM Fee Assessment uses rules built in Catalog Schedule and Registration Modules It always writes a record to the Registration Fee Assessment View Collector Table SFRCOLR which should be cleaned out periodically There is a good discussion of Fee Assessment options in the Registration chapter of the Student User Manual SunGard 2004 2005 Student Technical Page 203 Section O Registration Lesson SQL Plus Questions e What tables are part of the Registration Module select table_name from all _tables where table_name like SF e What data elements are required desc sfrstcr o Notice the NOT NULL columns e What are the key fields in sfrstcr select column_name from all_cons_columns where table_name SFRSTCR and constraint_name PK_SFRSTCR SunGard 2004 2005 Student Technical Page 204 Section O
36. on legacy side If error handling is built into programs then RI can be turned off SunGard 2004 2005 Student Technical Page 240 Section Q Conversion Lesson Seed Data Values Chapter 6 of the Student Technical Reference Manual lists all validation table values that must be kept in production If the System required indicator Y this does not mean one must use this information Most of this information is needed by external reports to third parties e g IPEDS SunGard 2004 2005 Student Technical Page 241 Section Q Conversion Lesson Conversion Examples Examples The following examples will demonstrate how to Convert data to three Banner tables Create drop and alter temporary tables Assign a pidm Use SQL LOADER to load temporary tables Use Update statement and Decode function to do cross walk translation Use Insert statement Use a shell script or command procedure Check the data when complete Clean up data if it is incorrect Flat file The examples will use a flat file containing Person s student s SSN Last name First name Street City State Zip Sex Birth date Information converted We will convert basic general person information Person Identification Name Table SPRIDEN General Person Table SPBPERS Address Information Table SPRADDR SunGard 2004 2005 Student Technical Page 242 Section Q Conversion Lesson Conversion Example F
37. tables and point out the relationships among the tables The lesson will also illustrate which tables must be populated during conversion if you are going to use curriculum rules For more details on setting up and using program and curriculum rules CAPP training is appropriate You can look at Chapter 9 of the CAPP user manual for a detailed discussion of Curriculum rules as well as the Student User Manual in Chapters 10 Recruiting 11 Admissions 12 General Student 13 Registration and 15 Academic History SunGard 2004 2005 Student Technical Page 85 Section F Curriculum Program Rules Lesson Curriculum Program Rules Overview Continued Diagram Curriculum Rules Curriculum Rules Form SOACURR Form Table Curriculum Base Table SOBCURR SOACTRL SOBCTRL Program Rules Curriculum Major Concentration Curriculum Minor Form Table Rules Table Curriculum Rules Rules Table SMAPRLE SORCMJR Table SORCMNR SMRPRLE SORCCON Notice the naming convention e SOACURR is an OVERALL form used by many modules e SMAPRLE is part of the CAPP module M is the letter for that module Major forms e Program Definition Rules Form SMAPRLE e Curriculum Rules Form SOACURR e Curriculum Rules Control Form SOACTRL If your institution is planning to use CAPP you will be concerned with the SMAPRLE Program Rules form If not and you do plan to use curriculum rules you will only need to be concerned with
38. term to be credited to a student s account and or bill in three ways payments authorizations and or memos Students must pass all user defined edits and any applicable federal requirements Any adjustments made by the Financial Aid office to student awards or due to funds failing disbursement edits may be posted to a student s account and or bill SunGard 2004 2005 Student Technical Page 14 Section B Student Technical Training Overview Lesson The Student System Continued Student and Human Resources Data entered through either General Person module is shared Reports pull information from both the Faculty Load module and the HR system for reports e SIRCTAL Faculty Load Contract Analysis Salary information can be added with a parameter entry e PEREFACL Faculty Load Comparison This process identifies where data does not match If the data matches total and recording of Total Contact Hours and FTE are updated in the HR system SunGard 2004 2005 Student Technical Page 15 Section B Student Technical Training Overview Lesson Shared Student Validation Forms Field Table description Advance Finance FinAid General HR ment STVACCG Activity Category A STVACTC Activity A STVACTP Activity Type A STVACYR Academic Year R STVADMT _ Admission Type R STVAPDC Admission Applic
39. value to the form SOTCNVT then rerun SORTAPE for the remainder of the records still in the temporary tables e For more information about SORTAPE refer to Chapter 14 of the Student Technical Reference Manual Tape Interface Rules Form SOAINFR e Establish rules for each tape type Tape Code Conversion Form SOTCNVT Establish converted values on the Tape Code Conversion Form Load the tape to a flat file Clean out the temporary tables run BH student plus sostdel sql Run the SQL Loader from UNIX prompt sqlldr userid username password control sat9495 ctl data sat9798 dat Note For a good discussion of SOTCNVT refer to Chapter 16 of the Student Users Manual Note sat9495 ctl should not need to be modified unless the tape layout changes Suspended Records Maintenance Form SOASUSP Tape Comparison Processing Report SORINFR e Run SORINFR from UNIX prompt to compare data in temporary tables to existing SCT Banner information SORINFR identifies Matches New records or Errors The source code is located in u01 banner test student c e If this process produces any records that have an indicator other than M Match or N New then the records can be viewed and corrected on the Suspended Records Maintenance Form SOASUSP SunGard 2004 2005 Student Technical Page 125 Section l Overall Forms and Tables Lesson Self Check Overall Forms and Tables Exercise Exercise Get the following information abou
40. you should have e completed OR101 Introduction to Oracle e completed SCT Banner Navigation Section Content OVETVIEW eege 140 Student System OVC VIS ee 141 Location Management Module sccascscorsseteevenaaiertsiae neta geegent 142 UP E 145 Reports and RE 146 Other Sepis eege a E EE R TA 147 Conversion EE 148 Self Check Location Management Exercise 00 eescccesseeeesececeseeeceeececeeececeeeeeseeeeeneeeees 149 Self Check Location Management Exercise Answer Key ccescccseseeeeseeeeeeteeeesneeees 150 SunGard 2004 2005 Student Technical Page 140 Section K Location Management Lesson Student System Overview Academic Courses i gt sepa y and GPA Instructional Assignments Grades and Banner Sections SCH FINANCIAL Prerequisites AID Staff Faculty Load Disbursements Registration Pr panes The ospects Registration Fees aiid Receivable Housing HUMAN Recruiting RESOURCES Enrollment Location Fees A Management Charges and Housing A Payments Recruits and Meal re caga Assignments Banner ccepte ener Pami Admissions Candidates Student Graduates ADVANCEMENT we Applicants SunGard 2004 2005 Student Technical Page 141 Section K Location Management Lesson Location Management Module Diagram Academic History Instructional Assignments Grades and Secti WW EE Prerequisites Faculty Load RESOURCES Enrollment Housing and
41. 159 EE 160 Conyersion ISSUES sieisenco otia EREE EE EEE EREET EEEE EEES EER 161 Self Check Sched l EN 162 Self Check Schedule Exercise Answer Key sssssssssssssssssesesesrsseeessessressereseessseesseese 163 Section M General Student seseesessessossesessossescosossosoossssossossesoesossessossesessossesessossessssssssssess 164 EE 164 Student System TN 165 General Student TEE 166 S0 E 169 ET CR 170 EEN 171 Conversion ISSUES nasini ieii ege eeR AEN 172 Self Check General Student EXGreise siciaisscisanetessueesdserreptentsecsasataasavuaseassasseancenusaderasageuane 173 Self Check General Student Exercise Answer key 174 Table of Contents Continued Section N Accounts Receivable ssecesocesooessocssccessecesccssoocscocessecesccesocesooessoessoesssocesocsssosees 175 EE 175 Student System TEE 176 Accounts Receryable e TE 177 Accounts Receivable Billing Control Form TGACTRL Student Billing Control Form CTS AC TR Ls EE 179 Detail Code Control Form TSADETC cccccccccccccecsssesssecececeeeesensaeceeeeeeeesessnseaeeeseeeens 180 AR RIGS E 181 TGACREV TGACSPV sssscaiesetiinces esos Were uneceaeseearpodieuanaleeeeeeenr eee 182 Student Account Detail Form TSADETL ccccc cc ccccccecsssssseceeeceeeenessnseceeeceesenesenseaeees 183 Student Account Detail Review Form TSAAREV ssssssssnesssssseseneesssssseserensessssoserreeesssss gt 184 Student Payment Form CTSASPAY
42. 95 Section F Curriculum Program Rules Lesson Self Check Curriculum Program Rules Exercises Answer Key Continued Exercise 2 Write a query to retrieve a list of students who have an invalid major based on the curriculum rules selecting id last name college code degree code major code This is an advanced exercise select spriden_id spriden_last_name sgbstdn_coll_code_1 sgbstdn_degc_code_l1 sgbstdn_majr_code_1 from spriden sgbstdn where spriden_pidm sgbstdn_pidm and not exists select x from soremjr sobcurr where sobcurr_curr_rule soremjr_curr_rule and sorcmjr_majr_code sgbstdn_majr_code__ and sobcurr_coll_code sgbstdn_coll_code_1 and sobcurr_degc_code sgbstdn_degc_code_1 te il SunGard 2004 2005 Student Technical Page 96 Section G Recruiting Lesson Overview Objectives At the end of this section you will be able to Describe the role and functions of the Recruiting module Prerequisites To complete this section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Section Contents OVETVIEW egener E ENS EASE EEEE a EREE E EEES EEEE ARa 84 Curriculum Program Fuere Eeer 85 Program Definition Rules Form GMAPRT Pi 88 Curriculum Rules Form SOACURR cccccccscccccecsesesensecececececeesensesecececeeeesessaeaeeeeeeeeeees 89 Curriculum Rules Control Form GOACTRL 90 Major Minor Concentration Rules Form
43. 99 hea HOURS break on stunam noduplicates skipl select substr spriden_last_name 1 15 I substr spriden_first_name 1 15 stunam ssbsect_subj_ code subj ssbsect_crse_numb crse sfrstcr_crn crn sfrstcr_credit_hr hrs from spriden ssbsect sfrstcr where spriden_pidm sfrstcr_pidm and spriden_change_ind is null and sfrstcr_term_code amp term and sfrstcr_rsts_code IN RE RW and sfrstcr_crn ssbsect_crn and sfrstcr_term_code ssbsect_term_code order by stunam SunGard 2004 2005 Student Technical Page 210 Section P Academic History Lesson Overview Objectives At the end of this section you will be able to e Describe the role and functions of the Academic History module Prerequisites To complete this section you should have e completed OR101 Introduction to Oracle e completed SCT Banner Navigation Section Content OVETVIEW eege 211 Student System CVS C VIS ee 212 Academic History KMoetulgeegesteseeroeegeageeeggeegeeeggEence geed 213 Institutional CO r ES seiere tian e EE E E E aE 215 Transfer CO TSE Sire ssion stee aneo ta E EE EERE AR EEE A E REEE OORSEE RIE 217 IR 219 GPA EE 221 Pre Banner Summary 3 c6sc5cccisnvssacssdassnetasnecedastabacevadavacsassaiessesasendsustadentaasseansdesdadesaunencvasveges 222 Term GPA Table SHRTGPA cc cccccsssssececececeesessnsececeeeceeeessaaeeeseceeecsessaeaeeeeeesesenes 223 Level GPA Table SHREUGPA Jeiercrcinureroane ni n eee acters 224 BO FS
44. APCTL form Method two through forms is the most commonly used method Submitting Sleep Wake processes via Job Submission e Define Printer and print command on GTVPRNT On the SOADEST or TOADEST form enter the correct printer code from GTVPRNT On GJAPCTL for the valid sleep wake jobs enter the parameters that specify sleep wake processing e Stop sleep wake process on GJASWPT form Refer to Chapter 10 of the Technical Reference Manual for specific directions for setting up printer commands etc Sleep Wake jobs Jobs that can be run in sleep wake mode e Student Schedule Process SFRSCHD Academic Transcript Process SHRTRTC Account Receipt Process TGRRCPT Student Billing Statement Process TSRCBIL Miscellaneous Receipt Process TGRMISC SunGard 2004 2005 Student Technical Page 206 Section O Registration Lesson Other Scripts BANNER_HOME student dbprocs functions sff ex sffrgfel sql BANNER_HOME student views views sfv sfvstcO sql creates view as_student_registration_detail Some views are used in conjunction with theObject Access method of retrieving data from the database using the concept of layered views you must have the GTVSDAX form table populated with crosswalk values This one does not use the GTVSDAX table not all layered views refer to GTVSDAX It is used as a crosswalk table to spread out repeating table row values into columns for easier reporting Clients should know th
45. CT Banner directory structure and a closer look at some of the database object creation scripts for each of the Student System modules This training program will provide the Student technical staff with a basic knowledge of the tables reports and processes that make up the SCT Banner Student System The training also includes discussion about data conversion as well as a conversion example exercise The purpose of this course is to make programmers analysts and other technical staff familiar with the tables and other database objects and processes that make up the Student System Others areas of interest such as forms modification advanced database toolkit topics Object Access role based security etc are covered completely in other training offered by SCT either at the Ed Center or at the client site SunGard 2004 2005 Student Technical Page 9 Section A Introduction Lesson Workbook contents Workbook contents This workbook contains the following sections Section A Introduction Section B Student Technical Training Overview Section C Course Catalog Section D Referential Integrity Section E General Person Section F Curriculum Program Rules Section G Recruiting Section H Admissions Section I Overall Forms and Tables Section J Faculty Load Section K Location Management Section L Schedule Section M General Student Section N Accounts Receivable Section O Registration Section P Academic His
46. Cohort Recruiting Table SRRCHRT Recruiting Attribute Table SRRRATT Recruiting Comment Table SRRRCMT Curriculum Rules Tables Notice patterns in the names of forms and tables on this page SunGard 2004 2005 Student Technical Page 99 Section G Recruiting Lesson SCT Banner Student Recruiting Module Continued Major Validation Tables Forms Term Code Validation Form Table STVTERM Level Code Validation Form Table STVLEVL College Code Validation Form Table STVCOLL Degree Code Validation Form Table STVDEGC Major Minor Concentration Code Validation Form Table STVMAJR Student Type Validation Form Table STVSTYP Residence Code Validation Form Table STVRESD SunGard 2004 2005 Student Technical Page 100 Section G Recruiting Lesson Prospect Information Form SRARECR SRARECR This form provides information necessary for all recruitment related activities and is the basis for all related recruiting forms e Can go to SPAIDEN form to create a person record from this form e Notice connections to Curriculum SunGard 2004 2005 Student Technical Page 101 Section G Recruiting Lesson Quick Recruit Form SRAQUIK SRAQUIK This form allows entry of new prospective students General Person information is created via this form populating tables SPRIDEN SPBPERS SPRADDR etc along with other information populating tables SORHSCH SORPCOL SORINTS SRRRSRC SORCONT etc Note
47. Dictionary Exercises Answer key 29 SunGard 2004 2005 Student Technical Page 11 Section B Student Technical Training Overview Lesson The Student System Description The Student System interacts with Finance through General GURFEED but also can be considered to have a direct connection to Finance through Accounts Receivable in the Student System Diagram 4 Financial Human Student i Aid Resources Accounts Position Receivable Control Student and Advancement Student to Advancement Interface APPSTDI e Adds records that define individuals as constituents as well as updates information on existing constituents Shared information across SCT Banner e Identification Person amp Address Information Information pulled from Student into Advancement e Academic information is pulled from Admissions Academic History amp Registration e Student Cooperative information may also be retrieved for employment history e Student activities will also be retrieved SunGard 2004 2005 Student Technical Page 12 Section B Student Technical Training Overview Lesson The Student System Continued Student and Finance Accounts Receivable connects these Systems Charges can be posted to an account through the following Student modules Admissions Registration Location Management Academic History CAPP Cashiering sessions would be created for the above transactions TGRFEED FURFEED proces
48. E 135 KEE 136 Conversion LE 137 Self Check Fac lty Load Exercise aekeeeeen ite eege Keng Eug eege Eet 138 Self Check Faculty Load Exercise Answer Key s sssssssssessssssersserresseessressresseerssersssres 139 Table of Contents Continued Section K Location Management soesoossesoossocssessoesosssessossocssessoesoossesssssoossossoessossosssssosseoe 140 Eege 140 Student System TE 141 Location Management Module giscccscssisctasnccedssnsessasavncccsasnsneassnnseadsaanabensasodecdesnssedesauedecvastenes 142 SOL PIUS oee E T AE E A E A E 145 ET AG REN 146 Other SOT DUS siseses otier aonni sia RE EEEE A EEE RE ER EAE AEAEE N 147 C nyersion ISSUE Shenene ansaan Ea SE EE E EE E TA A EAEE TEE SSeS 148 Self Check Location Management Exercise ossseeeessesseeesesrseeesseesstessresseseserersseesseese 149 Self Check Location Management Exercise Answer Key cesccssseeeeeseeeenteeeeneeees 150 Section L Schedule scccsscssivecceisccacsensndeocetsusdassenseudseasseuscontneuccensaudusdseaiacssdscsendseosdeosescenasessents 151 OVINI EW t A E aap dar vacton amen asteaan pag ann anaatnaaeraaeenanresreneeennes 151 Seege 152 Schedule e E 153 Section General Information Form SSASECT Section General Information Base Table SSBSECT E 155 Term Control Form GSOATERM 156 SLOMEET and Si AIA EE 157 GE PISS eiscasasicssiacssdeetaeiesdanedsxieacausasuacadssinedaaeawivuadevaensax dase A E E 158 E CR
49. EE SE Assignments Banner T ccepte ener Been Candidates Student Graduates ADVANCEMENT FNANCE General Person is often the first module to be converted because of its reach into other modules and into other products as well Employees Job Prospects on p Registration Fees SunGard 2004 2005 Student Technical Page 63 Section E General Person Lesson General Person Module Diagram Instructional Assignments Staff Faculty Load E ees ies mploy Jobs Applicants Recruiting RESOURCES Recruits Accepted General The arrow descriptors on the diagram indicate the primary content of the module Overview Data entry standards are important in all modules but because General Person allows many opportunities for freeform data entry it is appropriate to discuss data standards It is also important to establish institutional data standards before converting legacy data Before a person can become a recruit applicant student instructor advisor or have an account the person must first be identified to the system with an identification number and a name A person is initially added to the system using the SPAIDEN form which maintains a person s identification number SunGard 2004 2005 Student Technical Page 64 Section E General Person Lesson General Person Module Objectives Objectives Examine Major amp Required Forms and Tables e SOBSEQN PIDM Data standards e SP
50. EST Form Table SOAPCOL SORDEGR SORTEST SOAHSCH SORPCOL SORHSCH Overall tables are not restricted to connections with Recruitment or Admissions but are referenced through many areas of SCT Banner Overall tables are referenced in reporting Many other overall forms may be accessed from the General Student Module through the Educational Background Menu and other menus SunGard 2004 2005 Student Technical Page 121 Section l Overall Forms and Tables Lesson Overall Forms and Tables Continued Major forms e Test Score Information Form SOATEST o Used to maintain test score information e High School Information Form SOAHSCH o Used with Recruiting and Admissions for high school information e Prior College Form SOAPCOL o Used with Recruiting Admissions and Faculty Load for prior college information Major tables Student Test Score Table SORTEST Person Related HS Subject Table SORHSCH Prior College Table SORPCOL Prior College Degree Table SORDEGR Major Validation Tables Forms e Test Code Validation Form Table STVTESC e Source Background Institution Code Validation Form Table STVSBGI e Degree Code Validation Form STVDEGC You will use STVTESC in the exercise at the end of the lesson SunGard 2004 2005 Student Technical Page 122 Section l Overall Forms and Tables Lesson SQL Plus Questions e What are the tables that are used for multiple modules select table_name
51. General Person Table SPBPERS Address Information Table SPRADDR Telephone Table SPRTELE Person Related Holds Table SPRHOLD Major Validation Tables Forms Address Type Code Validation Form STVATYP State Province Code Validation Form STVSTAT Nation Code Validation Form STVNATN ZIP Postal Code Validation Form GTVZIPC Telephone Type Validation Form STVTELE Hold Type Code Validation Form STVHLDD These tables have to be populated before converting other general person tables SunGard 2004 2005 Student Technical Page 67 Section E General Person Lesson PIDM and SOBSEQN PIDM A pidm is a Person IDentification Master an internal key field used to identify and store records This is a numeric data type Non Persons such as vendors can also have pidms Pidms reduce the possibility of creating multiple identification numbers or entries for the same person entity If SCT Banner is used correctly one person could have multiple IDs or name iterations but only one pidm Extensive name searches and proper matching procedures further help to eliminate multiple entries for the same person SOBSEQN e SOBSEQN is a table which stores numbers used to generate pidms and other sequence numbers e It is built before Oracle incorporates sequence objects All numbers should be set to zero during production setup Maintenance access should be at highest security level Contents of SOBSEQN select from sobsegqn
52. H STVRELT Relation H STVRMST Room Status R STVRRCD Room Rate R STVRSTS Course Registration Status R H STVSBGI Source Background Institution A R H STVSITE Site R STVSPON International Student Sponsor R STVSTAT State A F R H STVSTST Student Status R STVTADM Test Score Administration Type R STVTELE Telephone Type A F R H STVTEPR Test Purpose R SunGard 2004 2005 Student Technical Page 17 Section B Student Technical Training Overview Lesson Shared Student Validation Forms Continued Field Table description Advance Finance FinAid General HR ment STVTERM Term R STVTESC Test Code R STVTSRC Admission Test Score Source R STVVETC Veteran Type R STVVTYP Visa Type R H TTVBILL Billing Code R TTVDCAT Detail Category R TTVPAYT Payment Type R TTVSRCE Charge Payment Detail Source R H SunGard 2004 2005 Student Technical Page 18 Section B Student Technical Training Overview Lesson Product Table Owners SCT Banner views and BANINST1 All SCT Banner views owned by BANINSTI General General Person Finance Accounts Receivable Position Control Payroll Student Financial Aid Advancement Security SCT Banner views All SCT Banner Views GENERAL SATURN FIMSMGR TAISMGR POSNCTL PAYROLL SATURN FAISMGR ALUMNI BANSECR BANINSTI1 SunGard 2004 2005 Student Technical Page 19 Section B Student Technical Training Overview Lesson Student System Ov
53. Load Lesson Overview Objectives At the end of this section you will be able to Describe the role and functions of the Faculty Load module Prerequisites To complete this section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Section Contents EE 128 e EE 129 F c lty Eto Be TE 130 Faculty UU BEE 131 Faculty Information Form SIAINST Faculty Member Base Table SIBINST 132 Faculty Assignment Form SIAASGN Faculty Assignment Table SIRASGN 133 e EE 134 Reports and RE 135 Oth r EIERE 136 Conversion EE 137 Self Check Faculty Teater see ee deet 138 Self Check Faculty Load Exercise Answer Key EEN 139 SunGard 2004 2005 Student Technical Page 128 Section J Faculty Load Lesson Student System Overview a Diagram Schedule Academic Coursen Gier y and GPA Instructional a Assignments anner Sections oe FINANCIAL Prerequisites AID Staff Faculty Load Empl Repistrati Disbursements mployees egistration Prospects Jobs Si Registration Fees anner i Receivable HUMAN ae Recruiting RESOURCES Enrollment Location ees A Management Charges and Housing A Payments Recruits and Meal RRE caga Assignments Banner ccepte ener Rewer Admissions Candidates Student Graduates ADVANCEMENT we Applicants SunGard 2004 2005 Student Technical Page 129 Section J Faculty Load Lesson
54. N 70 Cetera Person EE SE e E ET 77 OMY SISO Teen 78 EE egeege 79 Self Check General Person Exercises pics ccctaisssassacsveasatersaxtanceoui ss lhdoetssacencasaseterslalbescetasrnocnda 80 Self Check General Person Exercises Answer Key ccccscccesceceeseeeeseeceesneeecneeeenaeeeenas 82 Section F Curriculum Program Rulles sccsssscssssscssssscssssccssssccssscscssssscsssessssscscssscssescees 84 OVETVIE eet 84 Curriculum Program Rules EIERE 85 Program Definition Rules Form GMAPRT Pi 88 Curriculum Rules Form SOACURR ccccccsccccceceesessnsecececececsesenseaecececeeeesessaeaeeeeeeeeeeees 89 Curriculum Rules Control Form GOACTRL 90 Major Minor Concentration Rules Form 91 C nversi n Tee 92 SUMMATY posisine seniti sienet EEEE EEEE EERTE EEE AEAT EEEE EEE REEE O EAEE TEER 93 Self Check Curriculum Program Rules EXe rcises accsascssosssesasesscnaaisoustessnnvsvanteavendesgvenea sans 94 Self Check Curriculum Program Rules Exercises Answer key 95 Section G EN TE 97 EE 97 SCT Banner Student Recruiting Module 98 Prospect Information Form SRA RECR wcscssusiedssnssavan desvelisnvadeutagnenvendasasduccasanvsnonsedienansovasseds 101 Ouick Recruit Form SRAQUIK cscisicssseccccssisccesssascecasdaceisiasseedassecdensasdueadestectasdae vee detnates 102 SOL PIUS E E 103 et EE 104 Other OT ES aissei adacen desieawcdni anaes OERE E EEEE EEE ERR EEEE 105 Conversi n ISSUES EE 106 Table of Co
55. NAL The GREEN boxes on the diagram indicate the primary content of the module SunGard 2004 2005 Student Technical Page 32 Section C Course Catalog Lesson Course Catalog Module Continued Implementation SCT Banner must be implemented in a logical order We have organized this training presentation in a conversion order model It would be recommended to convert either Catalog or General Person first depending on the needs of other systems This order follows a general implementation order that allows clients to build the necessary rules and validation tables in the order that they are needed for successful implementation Objectives Examine Review Major amp Required Forms and Tables e Reports Processes and Procedures e Review Referential Integrity e Conversion of Data SunGard 2004 2005 Student Technical Page 33 Section C Course Catalog Lesson Course Catalog Diagram Basic Course Information Form SCACRSE Course Catalog Base Table SCBCRKY Course General Info Table SCBCRSE Course Level Course Grading Course Schedule Repeating Table Mode Repeating Type Repeating SCRLEVL Table Table SCRGMOD SCRSCHD Description The Course Catalog module holds all general course information It is used as the foundation for each term s schedule but does not hold schedule information by term You cannot create a schedule for a term and therefore students cannot register for a te
56. NS Transfer Institution Table SHRTRIT Attendance Period by Transfer institution Table SHRTRAM Transfer Course Detail Table SHRTRCR Transfer Course Equivalent Table SHRTRCE If detailed transfer course information is to be converted then all of the tables listed will be required SunGard 2004 2005 Student Technical Page 217 Section P Academic History Lesson Transfer Courses Continued Academic History Transfer Course Form SHATRNS Required only if detail of transfer courses is to be converted For each course transferred Transfer Institution Repeating Table SHRTRIT Attendance Period by Transfer Institution Repeating Table SHRTRAM Transfer Course Detail Repeating Table SHRTRCR Transfer Course Equivalent Repeating Table SHRTRCE Records are associated by term and by SHRTRAM SHRTRIT and SHRTRCR sequence numbers SunGard 2004 2005 Student Technical Page 218 Section P Academic History Lesson Degrees Diagram Degrees and Other Formal Awards Farm SHADEGR Degree Table SHRDGMR Departmental Honors By Institutional Honors By Degree Table Degree Table SHRDGDH SHRDGIH Each student must have at least one degree record with the status SO Those students who have graduated will have a second sequence number with a status of AW SunGard 2004 2005 Student Technical Page 219 Section P Academic History Lesson Degrees Continued
57. ODE_EFF which is part of the primary key of SGBSTDN GURRDDL If you run gurrddl you can see the alter statements defining the foreign keys related to STVTERM This is for SGBSTDN_TERM_CODE_EFF ALTER TABLE SATURN SGBSTDN ADD CONSTRAINT FK3_SGBSTDN_INV_STVTERM_CODE FOREIGN KEY SGBSTDN_TERM_CODE_EFF REFERENCES SATURN STVTERM STVTERM_CODE Definitions e Foreign Key The column or set of columns included in the definition of the referential integrity constraint that reference a referenced key e Referenced Key The unique or primary key of the same or different table that is referenced by a foreign key e Dependent or Child Table The table that includes the foreign key and is therefore dependent on the values present in the referenced unique or primary key e Referenced or Parent Table The table that is referenced by the child table s foreign key and which determines whether specific inserts or updates are allowed in the child table Source Oracle 7 Server Concepts SunGard 2004 2005 Student Technical Page 56 Section D Referential Integrity Lesson Foreign Key Constraints Continued Naming convention FKn_ foreign table_ INV_ Primary table_ CODE where e n is a one up number e foreign table is the table that contains the constraint e primary table is the table which contains the primary or referenced key Example FK1_SCBCRKY_INV_STVTERM_CODE Note The underscore character _ separates
58. Objectives At the end of this section you will be able to e Describe the conversion process Prerequisites To complete this section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Secton Contents Page 236 KEE eege 236 Conversion E 237 Conversion Steps essorer ieiti renion E EEA EEE REEERE EAEE 238 EE ET 240 Seed DR 241 Conyersion ARIS csp drag seca deaatecavacaseraseiueaescaayores snes EEEa TEES EE aED ERENS P EES AETS 242 Conversion Example Plat Pile Laenktem etteeegdereteteestegeaggeder tege eege ase 243 Conversion Example Create Statement 6iccscsscscvssnssatssseasaccusratecsseniedsassssadeaanssaceaanssendentosns 244 Conversion Example Alter Statement sc cscssscececisceessasssacscasseaticansdadaateeaasenanatieanseeasasansvadeavne 245 Conversion Example SOL LOADER eet eebe 246 Conversion Example Decode Statement 05cc sssccsssscsesssscasassasvesessandevensesboenesassaveedassavessannede 248 Conversion Example Check data in the temp tables 0 ce eecceesseeeeseeceeeeceeeeeceeeeeeeeeeeeees 249 Conversion Example Insert Statement EE 250 Conversion Example Check the data in SCT Banner 251 Conversion Example Update SOBSEQN 334 2ccssstonssvalessansiisunsusnatnudotcartuntadssatucdsanosiuacdtabenenadse 252 Conversion Example Clean the data in SCT Banner 253 Conversion Example Shell REENEN eteeh 254 SunGard 2004 2005 Student Technical Section Q Conversion Lesson C
59. PA Instructional p Assignments anner Sections _ Grades and FINANCIAL Prerequisites AID Stafl lPa culty Load Empl Revistrati Disbursements mployees egistration Prospects ra Jobs Registration Fees Accounts Applicants Receivable RESOURCES Enrollment Location Bees f Management Charges and Housing S Payments Recruits and Meal oe SE Assignments Banner f ccepte ener Bamer Candidates Student Graduates ADVANCEMENT mance SunGard 2004 2005 Student Technical Page 165 Section M General Student Lesson General Student Module Diagram Academic History Instructional Assignments Grades and Sections a Prerequisites Faculty Load RESOURCES J Enrollment Location a Management Housing and Meal Assignments Banner Graduates ADVANCEMENT General student records have been created via the traditional method through Admissions using SAAADMS or by quick admitting via SAAQUIK SIBINST table is populated with faculty data and advisor flag Objectives Examine Review e Major amp Required Forms and Tables e Reports Processes and Procedures e Conversion of Data SunGard 2004 2005 Student Technical Page 166 Section M General Student Lesson General Student Module Continued Overview Student Classification Rules General Student Form Curriculum Rules Form Tabie Form Table SGASTDN SOACURR SOBCURR SGACLSR SGRCLSR Student Base T
60. Person Lesson Data Standards Names Omit spaces within prefixed last names e MacArthur O Connor VanHusen e St John deBolt DuShen Omit spaces within hyphenated last names e Smith Jones Cochram Ashley Use the conventional mixed case format Punctuation Use periods after prefixes and suffixes where applicable e Miss Mrs Mr Jr MI Example e Prefix Firstname Hyphenated Last name e Mrs Joann Robinson O Connor Names should have no spaces this makes it easier to use name search in SOAIDEN Special characters Avoid use of the pound sign Banner Letter Generation identifies a pound sign as a formatting command Avoid the use of the following special characters see Student Technical Reference Manual Chapter 5 amp This will increase the efficiency of SCT Banner FOCUS BannerQuest and any other database accessing tool helping to minimize confusion for users SunGard 2004 2005 Student Technical Page 70 Section E General Person Lesson Data Standards Continued Conversion and standard compliance In conversion you may need to massage the data in order for it to comply to standards Your institution may set up a committee to review and set data standards which will be documented The committee may also make decisions regarding which office has change control or maintenance responsibility for specific data particularly IDs names and addresses If other
61. RIDEN SPBPERS indicators e SPRPDIR process e Conversion of data e Stores all biographic and demographic info about an entity in the database SunGard 2004 2005 Student Technical Page 65 Section E General Person Lesson General Person Forms and Tables Diagram identification Form SPAIDEN Person dentification Name Table SPRIDEN General Person Form Table SPAPERS SPBPERS Address Telephone Person Related Information Table Form Table Holds Form Table SPRADDR SPATELE SOAHOLD SPRTELE SPRHOLD Asterisked text mdicates required data Forms and tables SPRADDR and SPRTELE data are viewed from SPAIDEN Although SPBPERS is not required it is used as if it is and it will be discussed in this lesson The same principle applies to SPRTELE AND SPRHOLD most schools use them SPBPERS student data is typically maintained by the Registrar s Office for purposes of Federal and State Reporting If the institution has the Banner HR product the HR office may maintain general person data for employees SunGard 2004 2005 Student Technical Page 66 Section E General Person Lesson General Person Forms and Tables Continued Major forms Identification Form SPAIDEN General Person Form SPAPERS Telephone Form SPATELE Hold Information Form SOAHOLD Note There is no SPAADDR form Address information data is entered through SPAIDEN Major tables Identification Table SPRIDEN
62. RM_CODE_END NOT NULL VARCHAR2 6 Note A relationship exists between subj_code and crse_numb If scbcrse_eff_term is populated then scbcrky_term_code_start must be gt scbcrse_eff_term SunGard 2004 2005 Student Technical Page 40 Section C Course Catalog Lesson Conversion Issues Questions e Will Course Catalog data be converted or entered manually by the users e What course catalog data do you have in your legacy system e How do you determine where to put it in Banner Legacy data If there is legacy data which does not have an obvious place in the required tables look at other catalog module tables To see all tables in the Course Catalog module select table_name comments from all_tab_comments where table_name like SC College Department Table SCBCDEP Course Catalog Base Table SCBCRKY Course General Information Base Table SCBCRSE Supplemental Course Data Table SCBSUPP Course Attribute Repeating Table SCRATTR College Department Text Table SCRCDTX Course Corequisites Repeating Table SCRCORQ Equivalent Course Repeating Table SCREQIV Course Fees Repeating Table SCRFEES cele SunGard 2004 2005 Student Technical Page 41 Section C Course Catalog Lesson Reports Processes 4 SCRBULT SCRBULT Bulletin Report Prints catalog of courses Parameter Academic Year see STVACYR C program Run via Job Submission Actions Go to SCT Banner Job Submission to run th
63. Registration Lesson Reports and Processes Reports and processes e Student Schedule Process SFRSCHD o Can be run in sleep wake mode Class Roster Process SFRSLST Batch Fee Assessment Process SFRFASM Registered Not Paid Process SFRRNOP Registration Purge Process SFPREGS Student Schedule Process SFRSCHD This process prints a student schedule for a term It may be run in sleep wake mode SFRSCHD is a C program run from Job Submission Batch Fee Assessment Process SFRFASM This process is run if an institution decides not to do online fee assessment Registration charges are posted to the student s account in the Accounts Receivable module SFRFASM is a C program run from Job Submission There is a good discussion of Fee Assessment options in the Registration chapter of the Student User Manual Registered Not Paid Process SFRRNOP This process prints purges all students who have registered but not yet paid for a term It may be run in query or update mode SFRRNOP is a C program run from Job Submission SunGard 2004 2005 Student Technical Page 205 Section O Registration Lesson Sleep Wake Mode Purpose Sleep Wake mode allows you to run jobs in cyclical or sleep wake up manner There are two methods of doing this e Submit from Operating System and terminate manually scripts are in BANNER_HOMF general misc and BANNER_HOMFE general plus e Submit through SCT Banner Job Submission GJ
64. SIBINST is the only required table e SIRASGN is populated when the schedule is done Major Validation Tables Forms e Faculty Status Code Validation Form Table STVFCST o STVFCST is the Faculty Status validation table it is required because it is used in SIBINST Faculty Category Code Validation Form Table STVFCTG Faculty Staff Type Code Validation Form Table STVFSTP Faculty Contract Type Code Validation Form Table STVFCNT Term Workload Rules Code Validation Form Table STVWKLD Contract Rules Validation Form Table STVCNTR SunGard 2004 2005 Student Technical Page 131 Section J Faculty Load Lesson Faculty Information Form SIAINST Faculty Member Base Table SIBINST SIAINST SIBINST This form and table are used to maintain Faculty Information Codes and Indicators for e Active Inactive sibinst_fcst_code e Instructor sibinst_schd_ind e Advisor sibinst_advr_ind SIAINST also utilizes these tables but data is not required in SIBINST e Faculty Contract Type Repeating Table SIRICNT e Faculty Member Department and College Information Repeating Table SIRDPCL e Faculty Attribute Information Repeating Table SIRATTR e Faculty Comment Information Repeating Table SIRCMNT The Prior College Degree Table SORDEGR is used in the Faculty Degree Information Form SIAFDEG form to maintain faculty degree information SunGard 2004 2005 Student Technical Page 132 Section J Faculty Load Lesson
65. Student Technical Page 246 Section Q Conversion Lesson Conversion Example SQL LOADER Continued Usage This example of SQL LOADER includes a method for assigning a pidm The starting integer is determined after checking the SCT Banner database for the max and min spriden_pidm 77777777 is used in this example The data in the flat file is in columns however the loader can be written to use comma or quotation delimited files The default is APPEND so it is optional and the discardfile is optional The activity date could be sysdate rather than a constant but using a date like 25 DEC 97 stands out as a conversion date Spriden_change_ind will be null by default note that it is commented out Notes If one record does not load for some reason i e the data in the column is out of alignment then none of the data will load into the temp table for that record but the sequence number pidm will be preserved The log from the loader will indicate which record didn t load The bad data file will also show which records didn t load and could be edited to use with the next load of data Verification Verify that the number of records loaded match the number in the data file e Review the log file e Check that pidms were assigned properly e Were all NOT NULL columns filled SunGard 2004 2005 Student Technical Page 247 Section Q Conversion Lesson Conversion Example Decode Statement Code
66. SunGard 2004 2005 Student Technical Page 252 Section Q Conversion Lesson Conversion Example Clean the data in SCT Banner Code Clean SATURN tables clean_tables sql spool clean_tables delete from spriden where spriden_pidm gt 77777776 delete from spraddr where spraddr_pidm gt 77777776 delete from spbpers where spbpers_pidm gt 77777776 spool off Usage Clean the real SCT Banner tables if the data was not inserted correctly or you wish to rerun the process clean_tables sql For this example we will clean out the data we inserted into Banner In a test and production environment you may need to delete all or some records if they are inaccurate or invalid By looking at the activity date in each table or the pidm range records can be deleted Notes Some versions of sql loader will permit the use of a sequence In this case instead of using sequence 77777777 1 use myseq nextval and myseq currval A sequence would need to be created create sequence for PIDM create_seq sql spool sequence drop sequence myseq create sequence myseq increment by 1 start with 77777777 spool off exit SunGard 2004 2005 Student Technical Page 253 Section Q Conversion Lesson Conversion Example Shell script Code Shell Script convert shl export ORAENV_ASK NO export ORACLE_SID YOURSID oraenv sqlplus saturn u_pick_it create_temp sqlplus saturn u_pick_it alter_temp sqlldr saturn u_pi
67. SunGard 2004 2005 Student Technical Page 110 Section H Admissions Lesson Admissions Application Form SAAADMS SAAADMS This form is used for maintaining applications submitted to the institution It can maintain an unlimited number of applications for any given term saradap_term_code_entry saradap_appl_no SunGard 2004 2005 Student Technical Page 111 Section H Admissions Lesson Quick Admit Form SAAQUIK SAAQUIK This form allows entry and registration of new students with minimal effort General Person information is created via this form populating tables SPRIDEN SPBPERS SPRADDR SPRTELE etc Admissions and or Recruitment records may be created through this form Other information can be accessed via this form tables SORHSCH SORPCOL SPRHOLD SORTEST SPRINTL etc SunGard 2004 2005 Student Technical Page 112 Section H Admissions Lesson Admissions Decision Form SAADCRV SAADCRV The underlying table is SARAPPD Once an applicant is accepted through SAADCRYV a student record is created SGASTDN form SGBSTDN table SunGard 2004 2005 Student Technical Page 113 Section H Admissions Lesson SQL Plus Questions e What tables are part of the Admissions Module select table_name from all _tables where table_name like SA e What data elements are required desc saradap o Notice the NOT NULL columns e What are the key fields in SARADAP
68. TERM_CODE For this record set the _GPA_TYPE_IND to T If separate institutional and transfer brought forward data is converted load one or two SHRTGPA records per level per student Load one record per level representing the institutional GPA if the person has no brought forward transfer work using 000000 for the SHRLGPA_TERM_CODE and I for the SHRTGPA_GPA_TYPE_IND If the person also has brought forward transfer work load a second record using 000000 for the TERM CODE and T for the GPA _TYPE_IND SunGard 2004 2005 Student Technical Page 223 Section P Academic History Lesson Level GPA Table SHRLGPA Level GPA Table SHRLGPA SHRLGPA contains two records per student per level with a third record for students who have transferred e Institutional GPA e T ransferGPA not always present depending on the student e O verall GPA a combination of Institutional and Transfer GPAs Conversion For summary conversion determine whether separate brought forward data will be maintained for institutional and transfer data If only a total cumulative strip is to be converted load two SHRLGPA records per level per student For one record set the SHRLGPA_GPA_TYPE_IND to I Institutional For the second record set the SHRLGPA_GPA_TYPE_IND to O Overall If separate institutional and transfer brought forward data is converted load two or three SHRLGPA records per level per student depending upon whether t
69. This form allows direct creation of a person record you don t go to SPAIDEN the form does that in the background SunGard 2004 2005 Student Technical Page 102 Section G Recruiting Lesson SQL Plus Questions e What tables are part of Recruiting Module select table_name from all _tables where table_name like SR e What data elements are required desc srbrecr o Notice the NOT NULL columns e What are the key fields in srbrecr SQL gt select column_name from all _cons_columns where table_name SRBRECR and constraint_name PK_SRBRECR e Describe each table SRBRECR SRRRSRC SRRCHRT SRRRATT SRRRCMT SQL gt desc srbrecr SQL gt desc srrrsrc SQL gt desc srrchrt SQL gt desc srrratt SQL gt desc srrremt SunGard 2004 2005 Student Technical Page 103 Section G Recruiting Lesson Reports Reports e Recruiting Enrollment Analysis SRRENRL e Recruits Never Applied to Institution Report SRRINQR SunGard 2004 2005 Student Technical Page 104 Section G Recruiting Lesson Other Scripts BANNER_HOME student dbprocs functions srf BANNER_HOME student views views srv srvrecr0 sql creates view called as_recruiting data Some views are used in conjunction with the Object Access method of retrieving data from database This method uses the concept of layered views you must have the GTVSDAX form table populated with crosswalk values f_gurmail_rowid
70. able Refer to Curricukim SGBSTDN Programi Rules Student Activity Table Information Table Table SGRSACT SGRVETN SGRSCMT Veteran Student Comment Major Form Table General Student Form SGASTDN Student Base Table SGBSTDN e Used to maintain current and historical information about a student SGASTDN form also utilizes the following tables e Student Activity Table SGRSACT e Veteran Information Table SGRVETN e Student Comment Table SGRSCMT Notes Data in form is similar to Recruiting and Admissions No change can be made in SGASTDN for a term if the student has registered for that term Changes would need to originate in SFAREGS e Must have a SPRIDEN record Must know how far back to go with academic history A student must have a SGBSTDN record before the registration process is allowed on that student s record Must have correct flags on stvmajr major minor concentration Must have SGBSTDN for records if you are converting Academic History SunGard 2004 2005 Student Technical Page 167 Section M General Student Lesson General Student Module Continued Rule Forms Tables Student Classification Rules Form Table SGACLSR SGRCLSR e Used to establish classification rules based on range of credit hours entered and student attributes Curriculum Rules Form Table SOACURR SOBCURR e Refer to Lesson 5 Curriculum Program Rules e If rules are to be used indicator will be ON for General Studen
71. ables Forms Term Code Validation Form Table STVTERM Level Code Validation Form Table STVLEVL College Code Validation Form Table STVCOLL Degree Code Validation Form Table STVDEGC Major Minor Concentration Code Validation Form Table STVMAJR Student Type Code Validation Form Table STVSTYP Residence Code Validation Form Table STVRESD Admission Application Status Code Validation Form Table STVAPST Admission Application Decision Code Validation Form Table STVAPDC Test Code Validation Form Table STVTESC Degree Level Code Validation Form Table STVDLEV Degree Award Category Code Validation Form Table STVACAT State Province Code Validation Form Table STVSTAT Letter Code Validation Form Table GTVLETR Paragraph Code Validation Form Table GTVPARA All of these Validation Tables are necessary for the Admissions Module SunGard 2004 2005 Student Technical Page 109 Section H Admissions Lesson SCT Banner Student Admissions Module Continued Major Validation Tables Forms cont Notice that many of the tables were previously mentioned in preceding modules particularly in Recruiting GTVLETR and GTVPARA are used in Letter Generation You may want to set these up before bringing up Recruiting if you plan to do that before Admissions and if you plan to send mail to prospects New validation tables e STVAPST Admission Application Status Codes e STVAPCD Admission Application Decision Codes
72. ange_ind is null and sirasgn_crn ssbsect_crn and sirasgn_term_code ssbsect_term_code and ssbsect_subj_ code ENGL and ssbsect_term_code amp term and ssbsect_subj_code a scbcrse_subj_code and ssbsect_crse_numb a scbcrse_crse_numb and a scbcrse_eff_term select max b scbcrse_eff_term from scbcrse b where b scbcrse_subj_code ssbsect_subj_code and b scbcrse_crse_numb ssbsect_crse_numb and b scbcrse_eff_term lt ssbsect_term_code SunGard 2004 2005 Student Technical Page 163 Section M General Student Lesson Overview Objectives At the end of this section you will be able to e Describe the role and functions of the General Student module Prerequisites To complete this section you should have e completed OR101 Introduction to Oracle e completed SCT Banner Navigation Section Contents EN 164 ENEE 165 General lee EE Eeer EE 166 SEENEN 169 REpPorts aid PROCESSES sacss ssscesascecascsasesnevastesevaertundeaaaaransasienensaadeonasasnapeeiasageas eaadeeddsagnebevanneoes 170 ETS Te EE as eaaeea cade pnasunasnnar uated E E 171 Conversion TEE 172 Self Check General Student Exercise isc ses sssessacsasnscecsasnasadasauedacsasnsied svsessanssabsdaceasaedenventios 173 Self Check General Student Exercise Answer key 174 SunGard 2004 2005 Student Technical Page 164 Section M General Student Lesson Student System Overview Diagram Academic peng S B Histor e gg y and G
73. areas are implemented such as HR Finance and or Advancement decisions need to be made about maintenance responsibility Refer to the Conversion chapter of the Student System Technical Reference Manual Date formats MDY DMY YMD GUAINST uses radio buttons to determine which date format is used in SCT Banner e MDY January 5 1995 is entered as 01 05 95 e DMY January 5 1995 is entered as 05 01 95 e YMD January 5 1995 is entered as 95 01 05 If you enter only part of the date the rest of the current date defaults e Ifyou are including a date in query criteria always include the century e You can enter a dash instead of a slash Job Submission uses DD MON YYYY format in GJAPCTL Accounts Receivable uses DD MON YY format Remember the date formatting that has been chosen and use that format when entering dates in the exercises SunGard 2004 2005 Student Technical Page 71 Section E General Person Lesson Continued Century The Century Pivot field in GUAINST determines the cutoff year for determining which century a two digit year belongs to The value entered in this field will be the earliest year assigned to the 20 century For example if Century Pivot is set to 27 and the Date Format record group is set to MDYY then dates convert in this manner 1 5 19 Converts to 05 JAN 2019 1 5 20 Converts to 05 JAN 2020 1 5 27 Converts to 05 JAN 1927 1 5 28 Converts to 05 JAN 1928 1 5 78 Converts to 05 JAN 1978
74. ation Decision R STVAPST Admission Application Status R STVASCD Room Assignment Status R STVASRC Address Source A F R H STVASTD Academic Standing R STVATYP Address Type A F R H STVBLDG Building R G STVCAMP Campus R STVCIPC CIP Code H STVCLAS Class R STVCNTY County A F R H STVCOLL College A R G STVDEGC Degree A R H STVDEGS Degree Status R STVDEPT Department R G STVDIVS Division R STVDLEV Degree Level R H STVDPLM Diploma Type R STVESTS Enrollment Status R STVETHN Ethnic A F R H STVETYP Event Type G STVGEOD Geographic Region Division A STVGEOR Geographic Region A STVHAPS Housing Application Status R STVHLDD Hold Type R SunGard 2004 2005 Student Technical Page 16 Section B Student Technical Training Overview Lesson Shared Student Validation Forms Continued Field Table description Advance Finance FinAid General HR ment STVHONR _ Institutional Honors A STVINIT Initials A STVLANG Language H STVLEAD Leadership A STVLEVL Level R STVLGCY Legacy A F R H STVMAJR Major Minor Concentration A R H STVMDEQ Medical Equipment H STVMEDI Medical H STVMRCD Meal Rate R STVMRTL Marital Status A F R H STVMSCD Meal Assignment Status R STVORIG Originator A STVPENT Port of Entry R H STVRATE Student Fee Assessment Code R STVRDEF Building Room Attribute R STVRELG Religion A F R
75. been processed SLFRASM updates this field when fees have been assessed SunGard 2004 2005 Student Technical Page 145 Section K Location Management Lesson Reports and Processes Reports and processes e Batch Room Meal and Phone Assessment Process SLRFASM o selects records based on slrrasg_assess_needed o updates slrrasg_ar_ind in records that were assessed e Active Housing Assignments Report SLRHLST e Room Assignment Roll Process SLRROLL Roll like terms fall to fall etc SunGard 2004 2005 Student Technical Page 146 Section K Location Management Lesson Other Scripts BANNER_HOME student dbprocs functions slf BANNER_HOME student views views slv slvresO sql creates view as_residential_life Some views are used in conjunction with Object Access method of retrieving data from database using the concept of layered views you must have the GTVSDAX form table populated with crosswalk values Clients should know the naming conventions and the locations of these other database object creation scripts To use them as models create your own MODS directory as discussed earlier in this course and put any modifications in there For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 147 Section K Location Management Lesson Conv
76. c history institutional courses transfer courses are not going to be converted then SHRTGPA should be populated with the students GPA information I nstutional and T ransfer Refer to the Student User Manual Chapter 15 SHAPCMP The Pre Banner Summary Hours and GPA Form SHAPCMP captures and maintains summary GPA in lieu of the actual converted term s course work This is helpful if an institution does not intend to convert transcript data or has chosen to defer the conversion to a later date The ability to add pre Banner hours and GPA means that more accurate assessments can be made when determining class level and in calculating the institutional or transfer GPA SunGard 2004 2005 Student Technical Page 222 Section P Academic History Lesson Term GPA Table SHRTGPA Term GPA Table SHRTGPA Results are displayed in the Pre Banner Summary Hours and GPA Form SHAPCMP There will be at least one record per student in SHRTGPA e shrtgpa_type_ind T o would reflect total cumulative statistics e Use 000000 as the term code It is possible to have two records in SHRTGPA T and T GPA types for a student who has transferred Conversion For summary conversion determine whether separate brought forward data will be maintained for institutional and transfer data If only a total cumulative strip is to be converted load only one SHRTGPA record per level per student using 000000 as the SHRTGPA_
77. ccssssssccssssiesscanasheccasancuvnevorvaventasiovse tasbavedebssaveayasenmetaaiees 185 so EE 186 Reports RE 187 Application of Payments Process CTORAPPI NA 188 Accounting Feed Process 1 GRE RED s scesscaiisstasedvanneswindssanesdsancnelustabusdtedetonidacesusieivastiveds 190 Student Billing Statement Process TSRCBIL oe ciseecsccstesesasssdedecseettccdeeneamenateiiawe 191 Oth r SCI eege 192 CONVEFSION E 193 Self Check Accounts Receivable Exercises si siccissssecssstssaassasscsssarccdassecrocedasvacccasensccdsaavenes 194 Self Check Accounts Receivable Exercises Answer ken 195 Section O Remstratmt egenen Rate ge KUER EE adisessmaassnvn sheds deeadsen a boassacisnen 196 KEIER meeega Eege Eege 196 EE 197 Registration E 198 Fee ASO GU E 203 IP NN Schengen RE E E ETE E ed 204 e Cu BEE 205 Sleep Wake WIE gereest eebe 206 Other SCH PUS nisreen esan nonae n RE E EEEE E 207 EE ISSUE Saner E E E E E A E 208 Self Check Registration EE eege 209 Self Check Registration Exercise Answer ken 210 Table of Contents Continued Section P Academic e E 211 BEE eeEEEeg 211 Student Systemi OVeryieW sarsies erronea snee EE chant ER EEEE ES AEE RISE E eian 212 Academic History Mod le s scseisrensinoeiiireianea ee EO AE E a Ai 213 Tistitutional EE 215 Transfer COURS OS oirean E E RE A A E 217 IR 219 E SE 221 Pre Ba ner CHEN ee 222 Term GPA Table SHRTGPA EE 223 Level GPA Table SHREGPA J 5 csacecincnsnestanesiensdcwi
78. changes in TSADETL SFAREGS SLAMASG and other forms The Student Account Detail Review Table TBRACCD is its related table SunGard 2004 2005 Student Technical Page 185 Section N Accounts Receivable Lesson SQL Plus Questions e What tables are part of the Accounts Receivable Module select table_name from all_tables where table_name like T e What data elements are required desc tbhraccd o Notice the NOT NULL columns e What are the key fields in tbraccd select column_name from all_cons_columns where table_name TBRACCD and constraint_name PK_TBRACCD SunGard 2004 2005 Student Technical Page 186 Section N Accounts Receivable Lesson Reports and Processes Reports and processes Application of Payment Process TGRAPPL Accounting Feed Process TGRFEED Student Billing Statement Process TSRCBIL Account Receipt Process TGRRCPT Miscellaneous Receipt Process TGRMISC Refer to the Student Technical Reference Manual Chapter 10 for a complete list of Accounts Receivable reports and processes SunGard 2004 2005 Student Technical Page 187 Section N Accounts Receivable Lesson Application of Payments Process TGRAPPL Overview This process e Applies payments to charges for accounts based on priority tbbdetc_priority e Creates correct accounting entries to be fed by TGRFEED process e Gets other rules from TBBCTRL table The process is a C progra
79. ck_it control load ctl sqlplus saturn u_pick_it decode_sex sqlplus saturn u_pick_it insert_real Usage After verifying that each script works properly the scripts can be combined into a shell script for UNIX or a command file for VMS Your shell script may look differently depending on your operating system Check with your system administrator Notes Each script Oe create_temp will need to have exit at end spool create_tables drop table sytiden drop table sytaddr drop table sytpers create table sytiden as select from spride where 1 2 create table sytaddr as select from spradd where 1 2 create table sytpers as select from spbper where 1 2 spool off exit SunGard 2004 2005 Student Technical Page 254 Release Date This workbook was last updated on 09 19 2005
80. dap_pidm and saradap_appl_no sarappd_appl_no and sarappd_seq_no select max sarappd_seq_no from sarappd where x sarappd_pidm sarappd_pidm and x sarappd_term_code_entry sarappd_term_code_entry and x sarappd_appl_no sarappd_appl_no and spriden_pidm saradap_pidm and spriden_change_ind is null order by spriden_last_name SunGard 2004 2005 Student Technical Page 119 Section l Overall Forms and Tables Lesson Overview Objectives At the end of this section you will be able to Describe major forms and tables referenced through many areas of SCT Banner Prerequisites To complete this section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Section Contents ONS DA NRA E TE E E E E E EE E e 120 Overall Forms and Tables ccccccccccccessssssssssccecececeesensssececececeesensnsaaecececcesesenesseaeeeseesesenes 121 SOL PIUS EE 123 CONVETSI N ET 124 ET e 125 Self Check Overall Forms and Tables Exercise cccccccessssssscececececsessssaeceseceesesensnaees 126 Self Check Overall Forms and Tables Exercise Answer Key 127 SunGard 2004 2005 Student Technical Page 120 Section l Overall Forms and Tables Lesson Overall Forms and Tables Diagram Recruiting Base Table SRBRECR Admissions Applicant Table SARADAP Student Test Score Person Related HS Prior College Prior College Form Table Subject Form Table Degree Table SOAT
81. database object creation scripts To use them as models create your own MODS directory as discussed earlier in this course and put any modifications in there SunGard 2004 2005 Student Technical Page 79 Section E General Person Lesson Self Check General Person Exercises Exercise 1 Write a query to return the pidm id first name middle name last name and change indicator for persons who have had changes made to their SPRIDEN records today Exercise 2 Write a query to return the id first name last name and change indicator for the record that you entered about yourself in the database today There should be an ID change indicator I a name change indicator N and a record in which the change indicator IS NULL Have the query prompt you for the pidm SunGard 2004 2005 Student Technical Page 80 Section E General Person Lesson Self Check General Person Exercises Continued Exercise 3 Write a query to select the pidm id first name last name change indicator social security number from SPBPERS where changes were made to the ID records in SPRIDEN Exercise 4 Write a query to extract information that you would use on a mailing label For this query select the address type that appears the maximum number of times in the SPRADDR table You should extract the most current record from the SPRIDEN table For purposes of simplicity assume that all SPRADDR records for this addr
82. e Control Form TSADETC Detail Change Payment Code Accounts Receivable Module Continued Account Charge Payment Detail Table TBRACCD Deposit Table TBRDEPO Chre Payment Detail Memo Table TBRMEMO Detail Application of Payment Table TBRAPPL Definition Table TBBDETC Detail Code Account Definition Table TBRACCT Cashier Review Form TGACREV Cashier Supervisory Form TGACSPY Cashier Session Table TBBCSHR Accounts Receivable Billing Control FormiTable TGACTRL TBBCTRL Student Billing Control Form TSACTRE Accounts Receivable Billing Control Table TBBCTRL Forms Tables with an asterisk are required in live processing Note For conversion only TBRACCD needs to be populated You will need to set up TBBDETC and TBRACCT first along with other validation tables Major Validation Tables Forms Bill Code Validation Form Table TTVBILL Detail Category Code Validation Form Table TTVDCAT Delinquency Code Validation Form Table TTV DELI Deposit Type Code Validation Form Table TTVDTYP Payment Type Code Validation Form Table TTVPAYT Charge Payment Source Code Validation Form Table TTVSRCE Term Based Designator Validation Form Table TTVTBDS Tax Type Code Validation Form Table TTVTAXT for Canadian Inst only SunGard 2004 2005 Student Technical Page 178 Section N Accounts Receivable Lesson Accounts Receivable Billing Control Form TGACTRL
83. e Review e Major amp Required Forms and Tables e Reports Processes and Procedures e Conversion of Data Diagram 2 Enrollment Status Control Form SFAESTS 1 Term Control Form SOATERM Term Base Table SOBTERM Section Part of Term Validation Table SOBPTRM Student Registration Status Table SFBESTS Student Refund Percentage Table SFBRFST 6 Curriculum Rules 3 Course Registration Status FormiTable Control FormiTable SOACURR f SOBCURR SFARSTS SFRRSTS Refer to CurricuhamsP Course Refund Percentage E Rules iaid Table SFRRFCR 5 Refund Contr ol Form Table 4 Registration Fees Process SFARFND SFRRFND Control Form Table SFARGFE SFRRGFE Required if using Refund by Total Note Registration is connected directly to Student AR through fee assessment SunGard 2004 2005 Student Technical Page 199 Section O Registration Lesson Registration Module Continued Registration tables Student Registration Table SFBETRM sfbetrm term code sfbetrm pidm sfbetrm ests_code Student Course Registration Table SFRSTCR sfrster_term_code sfrster pidm sfrster_ern For reporting purposes these two tables are most important in Registration Module SunGard 2004 2005 Student Technical Page 200 Section O Registration Lesson Registration Module Continued Forms and tables Curriculum Rules Form SOACURR e SOBCURR is the relat
84. e data into the real SCT Banner tables Review the Jet file and verify that all records were inserted SunGard 2004 2005 Student Technical Page 250 Code select from where and and order by Usage Section Q Conversion Lesson Conversion Example Check the data in SCT Banner spriden_pidm substr spriden_last_name spriden_first_name 1 25 spriden_entity_ind spraddr_atyp_code spraddr_seqno spraddr_street_linel spraddr_city spraddr_stat_code spraddr_zip spbpers_sex spbpers_birth_date spraddr spbpers spriden spriden_pidm spriden_pidm spriden_pidm spriden_pidm gt 77777776 spraddr_pidm spbpers_pidm Data is now in the SCT Banner tables and should be reviewed before proceeding Notice the search and soundex columns that are now in SPRIDEN Write some scripts to look at the data in SCT Banner SunGard 2004 2005 Student Technical Page 251 Section Q Conversion Lesson Conversion Example Update SOBSEQN Code update sobseqn set sobseqn_maxseqno 77777783 sobseqn_activity_date sysdate where sobseqn_function PIDM Usage Provided all is well with the insert and the data looks good then SOBSEQN needs to be updated with the appropriate pidm that was last used and the activity date There may be cases where the pidm range for student records is lower than a product that was previously converted e g human resources may have a higher pidm range
85. e naming conventions and the locations of these other database object creation scripts To use them as models create your own MODS directory as discussed earlier in this course and put any modifications in there For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 207 Section O Registration Lesson Conversion Issues Issues e Conversion is not recommended for Registration e Possibly could run parallel e Legacy and SCT Banner SunGard 2004 2005 Student Technical Page 208 Section O Registration Lesson Self Check Registration Exercise Exercise Write a query that returns a student s full name and a list of courses for which he or she is registered for a given term including subject and course number crn and credit hours Prompt user for term SunGard 2004 2005 Student Technical Page 209 Section O Registration Lesson Self Check Registration Exercise Answer Key Exercise Write a query that returns a student s full name and a list of courses for which he or she is registered for a given term including subject and course number crn and credit hours Prompt user for term col stunam for a30 hea STUDENT NAME col subj for a8 hea SUBJECT col crse for a6 hea COURSE NUMBER col crn for a7 hea CRN col hrs for 999
86. eceivable Registration Academic History Curriculum Advising amp Program Planning CAPP Course overview This course will take a forms approach looking first at the major forms in each module then at the underlying tables that are required for conversion We will also examine the validation tables required for conversion Next we will look at the tables in the database using SQL Plus and learn their structure and content and the relationships among tables in a module We will look carefully at the delivered reports and processes looking at the code that produces them Finally we will look in the Banner Student directories at some of the scripts that produce database objects such as views functions and procedures Your institution may not choose to use every form or every field on any form but this class will cover the required forms tables and fields reports and processes you will need for conversion in order to use the Student System effectively SunGard 2004 2005 Student Technical Page 21 Section B Student Technical Training Overview Lesson SCT Banner Student Directories Diagram SunGard 2004 2005 Student Technical Page 22 Directories Section B Student Technical Training Overview Lesson SCT Banner Student Directories Continued We will be looking at these directories in your operating system in detail for each module as we move through the class We will look closely at the C DBPROCS and
87. ed table Term Control Form SOATERM e SOBTERM amp SOBPTRM are the related tables e Set Online Fee Assessment e Error Checking amp Severity Level etc e Rules forms must be set up before registration can occur Enrollment Status Control Form SFAESTS e SFBESTS amp SFBRFST are the related tables e Enrollment status codes are maintained on the Enrollment Status Code Validation Form Table STVESTS e Also related Student Refund Percentage Table SFBRFST Course Registration Status Control Form SFARSTS e SFRRSTS amp SFRRFCR are the related tables e Course registration status codes are maintained on the Course Registration Status Code Validation Form Table STVRSTS e Also related Student Refund Percentage Table SFBRFST Registration Fees Process Control Form SFARGFE e SFRRGFE is the related table Refund Control Form SFARFND e SFRREND is the related table e Connection to AR Major Validation Tables Forms e All that were mentioned in the previous modules e For acomplete list refer to the Student User Manual SunGard 2004 2005 Student Technical Page 201 Section O Registration Lesson Registration Module Continued Major Form Tables Student Course Registration Form SFAREGS e Mechanism for registering students e Upon opening SFAREGS for the first time in a Banner session you go directly to SOADEST printer choice form for sleep wake processes o SFBETRM Table containing Registration Status
88. ersion Issues Questions e Will your institution convert or manually enter Location Management information e What Location Management data do you have in your legacy system e How do you determine where to put it in SCT Banner SunGard 2004 2005 Student Technical Page 148 Section K Location Management Lesson Self Check Location Management Exercise Exercise Write a simple report that will show the residence hall assignments for a term prompt the user for the term On the report show last name id term building description and room SunGard 2004 2005 Student Technical Page 149 Exercise Section K Location Management Lesson Self Check Location Management Exercise Answer Key Write a simple report that will show the residence hall assignments for a term prompt the user for the term SELECT FROM WHERE AND AND AND On the report show last name id term building description and room substr spriden_last_name 1 10 spriden_id stvbldg_desc slrrasg_room_number slrrasg_term_code spriden stvbldg slrrasg spriden_pidm slrrasg_pidm spriden_change_ind IS NULL slrrasg_bldg_code stvbldg_code slrrasg_term_code amp term SunGard 2004 2005 Student Technical Page 150 Section L Schedule Lesson Overview Objectives At the end of this section you will be able to Describe the role and functions of the Schedule module Prerequisites To complete thi
89. erview Diagram Catalog Courses Academic Courses Histor Se ai y and GPA Banner Instructional Assignments Grades and Sections Be FINANCIAL General Prerequisites AID Person Stall lraculty Load Disbursements Employees Job Registration Prospects pas p Registration Fees Accounts Applicants Housing Receivable Loa Management Charges and Housing Payments Recruits and Meal e EE Assignments Banner en de ccepte ener Banner Candidates Student Graduates ADVANCEMENT FNANCE Diagram legend The overview diagram displays how each module interacts with other parts of the Student System The diagram flows left to right This does not necessarily reflect the order for conversion and implementation but does show the logical order of operation in production This presentation will follow the logical conversion implementation order conversion and implementation order will be discussed along the way Color codes more easily viewable in the course s PowerPoint Black Major Student Modules Green Major Content Area of Modules Red Other Banner products which can complement Student SunGard 2004 2005 Student Technical Page 20 Section B Student Technical Training Overview Lesson Recommended Order for Conversion Recommended order Catalog General Person Recruitment Admissions Location Management amp Housing Schedule Faculty Load General Student Accounts R
90. erview This process takes all applications of payment deposits miscellaneous transactions and account detail transactions from finalized cashiering sessions Based on the accounts built it creates a file of accounting detail records GURFEED that interface the Accounts Receivable module with the institution s financial accounting system along with refund and check information GURAPAY Source tables are updated to show that those records have been fed into the General Ledger Output The process produces a report that details debit and credit entries by account number TGRFEED uses data from TBRAPPL TBRDEPO TBRMISD and TBRACCD and refers to the TBRACCT TBBDETC and TBBCTRL tables for distribution and detail information TGRFEED goes to TSADETC TBRACCT to get accounting distribution codes and rules TGRFEED is a C program run from Job Submission SunGard 2004 2005 Student Technical Page 190 Section N Accounts Receivable Lesson Student Billing Statement Process TSRCBIL Overview e In Invoicing Mode TSRCBIL prints invoices and estimates credits based on current charges e In Statement Mode TSRCBIL calculates credits prints bills updates accounts with billed and due dates applies credits and begins the aging process Schedules may also be printed via this job Rule parameters for TSRCBIL are set on the Bill Selection Parameters Window of the Schedule Bill Rules Form TSASBRL TBBSBRL table The proce
91. ess type are current SunGard 2004 2005 Student Technical Page 81 Section E General Person Lesson Self Check General Person Exercises Answer Key Exercise 1 Write a query to return the pidm id first name middle name last name and change indicator for persons who have had changes made to their SPRIDEN records today SQL gt SELECT spriden_pidm 2 spriden_id 3 substr spriden_last_name 1 15 II 4 Py II 5 substr spriden_first_name 1 15 6 7 substr spriden_mi 1 1 8 spriden_change_ind 9 FROM spriden 10 WHERE spriden_entity_ind PI 11 AND spriden_activity_ date like sysdate 12 Exercise 2 Write a query to return the id first name last name and change indicator for the record that you entered about yourself in the database today There should be an ID change indicator I a name change indicator N and a record in which the change indicator IS NULL Have the query prompt you for the pidm SQL gt SELECT spriden_id spriden_last_name spriden_first_name spriden_change_ind FROM spriden WHERE spriden_entity_ind p AND spriden_activity date like sysdate AND spriden_pidm amp pidm SunGard 2004 2005 Student Technical Page 82 Section E General Person Lesson Self Check General Person Exercises Answer Key Continued lt Jump to TDL Exercise 3 Write a query to select the pidm id first name last name change indicator social sec
92. ge Process SSPSCHD Term Roll Process SSRROLL Roll like terms Fall to Fall Class Schedule Report SSRSECT Scheduled Section Tally SSRTALY Rolling of Room Assignments should be of like terms fall to fall spring to spring etc See Student Technical Reference Manual Chapter 10 for additional information on Student Module reports and processes SunGard 2004 2005 Student Technical Page 159 Section L Schedule Lesson Other Scripts BANNER_HOME student dbprocs functions ssf BANNER_HOME student views views ssv ssvsecO sql creates view as_catalog_schedule Some views are used in conjunction with Object Access method of retrieving data from database using the concept of layered views you must have the GTVSDAX form table populated with crosswalk values Clients should know the naming conventions and the locations of these other database object creation scripts To use them as models create your own MODS directory as discussed earlier in this course and put any modifications in there For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 160 Section L Schedule Lesson Conversion Issues Questions e Will Schedule data be converted or entered manually by the users e What Schedule data do you have in your legacy system
93. gpatde iasnesalanmnoades 34 NEEN eege eene 36 Major Validation Tables FOrMS escroa E E teee 38 Basic Course Information Form GCACRSE 39 OP E 40 Conversion ISS UGS s nsesdeseuiess Gesvndeiesoonctereienesln diced E ieee A S 41 Eet EE ee 42 Self Cheek Course Catalog EE 43 Self Check Course Catalog Exercises Answer Key 46 SunGard 2004 2005 Student Technical Page 30 Section C Course Catalog Lesson Student System Overview Diagram Academic Courses Geier e ER y and GPA Instructional z Assignments aer Sectiony Tad and FINANCIAL General Prerequisites AID Person Stall y raculty Load Empl Registrati Disbursements mployees egistration Prospects PRI Jobs Bs Registration Fees Accounts Receivable Applicants RESOURCES Enrollment Location Pees A Management Charges and Recruit Housing Payments ecruits and Meal SE Accepted General Candidates Student Assignments Banner Graduates ADVANCEMENT Banner FINANCE You will begin with the Catalog module one of the first to be converted You could also convert General Person first depending on the needs of other systems SunGard 2004 2005 Student Technical Page 31 Section C Course Catalog Lesson Course Catalog Module Applicants Banner HUMAN Recruiting RESOURCES Recruits i Accepted General Ad fons Candidates Student Legend This diagram shows a part of the large overview diagram The Recruiting Module is OPTIO
94. he student has transfer work at the level For the institutional record set the GPA _TYPE_IND to I For the transfer record set the GPA _TYPE to T For the cumulative record set the GPA_TYPE_IND to OT SunGard 2004 2005 Student Technical Page 224 Section P Academic History Lesson SQL Plus Questions e What tables are part of the Academic History Module select table_name from all_tables where table_name like SH e What data elements are required desc shrdgmr o Notice the NOT NULL columns e What are the key fields in shrdgmr select column_name from all_cons_columns where table_name SHRDGMR and constraint_name PK_SHRDGMR SunGard 2004 2005 Student Technical Page 225 Section P Academic History Lesson Other Scripts BANNER_HOME student dbprocs functions shf ex shfttrm sql BANNER_ HOME student views views shv shvsum0 sql creates view as_academic_history_summary Some views are used in conjunction with theObject Access method of retrieving data from the database using the concept of layered views you must have the GTVSDAX form table populated with crosswalk values This one does not use the GTVSDAX table not all layered views refer to GTVSDAX It is used as a crosswalk table to spread out repeating table row values into columns for easier reporting Clients should know the naming conventions and the locations of these other database object
95. iculum rules are turned on for STUDENT sorcmjr_stu_ind Y e Build control rules in SOACTRL o if sobctrl_curr_rule_ind N in SOACTRL then sobctrl_program_ind can Y o This means that you are not using CAPP s Program Planning but you are using curriculum rules SunGard 2004 2005 Student Technical Page 93 Section F Curriculum Program Rules Lesson Self Check Curriculum Program Rules Exercises A Exercise 1 Write a query to retrieve curriculum rules that apply to STUDENT listing major and program department code level code college code campus code degree code Exercise 2 Write a query to retrieve a list of students who have an invalid major based on the curriculum rules selecting id last name college code degree code major code This is an advanced exercise SunGard 2004 2005 Student Technical Page 94 Section F Curriculum Program Rules Lesson Self Check Curriculum Program Rules Exercises Answer Key Exercise 1 Write a query to retrieve curriculum rules that apply to STUDENT listing major and program department code level code college code campus code degree code select sorcmjr_majr_code sobcurr_program sorcmjr_dept_code sobcurr_levl_code sobcurr_coll_code sobcurr_camp_code sobcurr_degc_code from sobcurr sorcmjr where sobcurr_curr_rule soremjr_curr_rule and sorcmjr_stu_ind Y SunGard 2004 2005 Student Technical Page
96. iden_pidm A spriden_change_ind is null SunGard 2004 2005 Student Technical Page 174 Section N Accounts Receivable Lesson Overview Objectives At the end of this section you will be able to e Describe the role and functions of the Accounts Receivable module Prerequisites To complete this section you should have e completed OR101 Introduction to Oracle e completed SCT Banner Navigation Section Contents EE eher 175 leede 176 Accounts Receivable e x ssscpesissersisincainac ta neandeseaiaimentesaaacieementaneane 177 Accounts Receivable Billing Control Form TGACTRL Student Billing Control Form RK al E ET 179 Detail Code Control Form TSADETC cccccccccccccecsssssscececececeessssacceceeeeseseesnssaeeeeeceens 180 AR RSS EE 181 TGOAGREY UGAC EE 182 Student Account Detail Form TSADETL cccccccccccccsssssnsececececeesessneceeeceesesesenssaeees 183 Student Account Detail Review Form TSAAREV ssssssssseesssssseseosessesssssseseoeessssssesrreeseseo 184 Student Payment Form SAGE Y ocsiecenassssscansiiasseinvosetensoceasndaceeqdonastanselabeasecnaqnes easseacieene 185 RE ACEN 186 E CR 187 Application of Payments Process CTOGRAPPI XA 188 Accounting Feed Process EE 190 Student Billing Statement Process TSRCBIL cccssisicesasiiccssssesssesasteccesancsscnsaseesnesasnsscevenessns 191 OGM FS eee a a E a e r a a 192 Conversion Teen 193 Self Check Accounts Receivable Exercises iscscccscccsccs
97. idms Pidms may be created during the load process or after the data is loaded into the temp tables The main thing here is to know how to create alter and drop tables Drop is in the script so that if a shl or com is used the process can be rerun i e drop all tables then recreate them Not mandatory Remember when a table is dropped all of the data is lost In a production situation a backup of the completed temp table should be made before manipulating the data in the temp table and running the process SunGard 2004 2005 Student Technical Page 244 Section Q Conversion Lesson Conversion Example Alter Statement Code Alter temporary tables alter_temp sql spool alter_tables alter table sytiden modify spriden_pidm null alter table sytaddr modify spraddr_pidm null alter table sytpers modify spbpers_pidm null spool off Usage Alter the table so that the pidm column could be null not absolutely necessary depends on method for creating the pidm This example will not need a null pidm but in later temp tables when the pidm already exists in SCT Banner tables it may be a good method for handling pidms For example if HR has been converted but you know some HR general person records are students then you can get the pidm from SCT Banner before proceeding General student If general person records have been created for students and you are now doing general student information update sytide
98. in sofmail in student views For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 105 Section G Recruiting Lesson Conversion Issues Questions e Will Recruiting data be converted or entered manually by the users e What Recruiting data do you have in your legacy system select table_name comments from all _tab_ comments where table_name like SR e How do you determine where to put it in SCT Banner e Will you use curriculum rules Recruiting Module if used is usually brought up first and is usually a manual process of setting up validation tables and curriculum rules if they are to be used with Recruiting Conversion of legacy data is not common SunGard 2004 2005 Student Technical Page 106 Section H Admissions Lesson Overview Objectives At the end of this section you will be able to Describe the role and functions of the Admissions module Prerequisites To complete this section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Section Contents KENE ee 107 SCT Banner Student Admissions Module 108 Admissions Application Form GA A ADM 111 Quick Admit Form SAA QUIRK ccccccssssecccececsesessnsecesececeeeeseeseeececeeeeserssaeeeeeesesenensas 112 Admissions Decision Form SAADCRY
99. is report Send the report output to GJIREVO Type DATABASE in the printer field to view the report output within SCT Banner If you are unfamiliar with Job Submission it is covered in more detail in the General Technical training course SunGard 2004 2005 Student Technical Page 42 Section C Course Catalog Lesson Self Check Course Catalog Exercises Exercise 1 Get the following information about any two of the Course Catalog module tables e Table Owner e Table Name e Column Name e Data Type e Null Not Null Column Hint You will use the data dictionary view all_tab_columns SunGard 2004 2005 Student Technical Page 43 Section C Course Catalog Lesson Self Check Course Catalog Exercises Continued Exercise 2 Get the following information from the course catalog module about all courses with a subject code of ENGL subject code course number course title effective term start term end term Hint You will need to use SCBCRSE and one other table SunGard 2004 2005 Student Technical Page 44 Section C Course Catalog Lesson Self Check Course Catalog Exercises Continued Exercise 3 Write a select statement that would produce a catalog report which includes the following no formatting necessary subject code course number course title effective term start term course level grade mode Hint You will need to use SCBCRSE and 3 other tables
100. ity Prerequisites To complete this section you should have e completed OR101 Introduction to Oracle e completed SCT Banner Navigation Section Contents OVETVIEW egener E ENS EASE EEEE a EREE E EEES EEEE ARa 84 Curriculum Program Fuere Eeer 85 Program Definition Rules Form GMAPRT Pi 88 Curriculum Rules Form SOACURR cccccccscccccecsesesensecececececeesensesecececeeeesessaeaeeeeeeeeeees 89 Curriculum Rules Control Form GOACTRL 90 Major Minor Concentration Rules Form 91 Conversion Tee 92 SUMMATY EE 93 Self Check Curriculum Program Rules Exeroses 94 Self Check Curriculum Program Rules Exercises Answer key 95 SunGard 2004 2005 Student Technical Page 84 Section F Curriculum Program Rules Lesson Curriculum Program Rules Overview Introduction Although Curriculum and Program rules are not a separate module those tables are introduced now because of their connections to Recruiting Admissions and General Student These rules are not required but most institutions use them because of CAPP and Web for Students Admissions Objectives Examine e Forms used to build rules e Table relationships Overview Your functional consultant will go over the setting up of these rules in detail The purpose of this lesson is to examine three forms and six tables which should be set up before implementing Recruiting and Admissions The lesson will illustrate how to discover the underlying
101. ky_subj_code 10 AND scbcrse_crse_numb scbcrky_crse_numb 11 AND scbcrse_subj_code ENGL 12 vw OO JO DG LAM SunGard 2004 2005 Student Technical Page 47 Section C Course Catalog Lesson Self Check Course Catalog Exercises Answer Key Continued Exercise 2 continued SCBC SCBCR SCBCRSE_TITLE SCBCRS SCBCRK SCBCRK ENGL 1005 Literature amp Composition I 199510 199510 999999 ENGL 1005 Literature amp Composition I 199610 199510 999999 ENGL 1005 Literature amp Composition I 199620 199510 999999 ENGL 1006 Literature amp Composition II 199510 199510 999999 ENGL 101 English Composition 198710 198710 999999 ENGL 101 English Composition 199010 198710 999999 ENGL 101A Computer Literacy 198710 198710 999999 ENGL 103 20th Century American Lit 199510 199510 999999 ENGL 1050 The Literary Experience 199510 199510 999999 ENGL 107 World Lit 199010 199010 999999 ENGL 108 World Lit 199010 199010 999999 ENGL 109 World Lit 199010 199010 999999 ENGL 1201 Survey of American Lit I 199510 199510 999999 ENGL 201 Topics in English 198710 198710 999999 ENGL 310 African American Prose 199010 199010 999999 ENGL 311 African American Poetry 199010 199010 999999 ENGL 312 African American Drama 199010 199010 999999 ENGL 408 Topics in English Lit 199520 199520 999999 ENGL 410 Topics in American Lit 199520 199520 999999 19 rows selected SunGard 2004 2005 Student Technical Page 48 Section C Course Catalog
102. lat File Layout Layout 210009506Abbe Anthony PO Box 21049 Malvern PA19355226 MAR 77 610009711Abbot James PO Box 27 Malvern PA19355217 NOV 79 210009101Adams Andrew 803 King Street Malvern PA19355210 DEC 72 610009101Adams Anthony 20789 Lancaster Ln Clarksville PA15122210 DEC 74 710000011Adams Eugene 3400 Wendrow Way University ParkPA16802201 JAN 01 210009619Barker Clementine 83 Park Avenue New York NY10013128 APR 72 210009613Barker James 854 Charlestown Pk King of PrussiaPA19401201 DEC 77 This layout is in columns although comma delimited or quotation mark delimited can be used with SQL LOADER see manual for more information SunGard 2004 2005 Student Technical Page 243 Section Q Conversion Lesson Conversion Example Create Statement Code Create temporary tables create_temp sql spool create_tables drop table sytiden drop table sytaddr drop table sytpers create table sytiden as select from spriden where 1 2 create table sytaddr as select from spraddr where 1 2 create table sytpers as select from spbpers where 1 2 spool off Usage First create temporary tables based on the actual layout of the SCT Banner table then alter the table so that the pidm column could be null not absolutely necessary depends on method for creating the pidm This example will not need a null pidm but in later temp tables when the pidm already exists in SCT Banner tables it may be a good method for handling p
103. ld If a code is not in the validation table it cannot be used as data in that field Validation tables have a parent child relationship with records The principle of referential integrity will be covered more thoroughly in a subsequent lesson Validation tables parents must be populated with correct codes before converting data which will populate the child records in the tables SunGard 2004 2005 Student Technical Page 38 Section C Course Catalog Lesson Basic Course Information Form SCACRSE Components Key Block From and To Terms Fields Related to AR LOV Fields Level Grade Mode Schedule Type SunGard 2004 2005 Student Technical Page 39 Section C Course Catalog Lesson SQL Plus Questions e How are the SCBCRSE and the SCBCRKY tables related e What data elements are required in SCBCRSE SCBCRKY and SCRLEVL e How are level grading mode schedule types connected to a course SQL gt desc scrlevl SQL gt desc scrgmod SQL gt desc scrschd Common fields Look at the fields that each has in common with SCBCRSE and SCBCRKY Note Validation tables STV 4 character identifier _code SCBCRSE and SCBCRKY SCBCRSE Name Null Type SCBCRSE_SUBJ_CODE NOT NULL VARCHAR2 4 SCBCRSE_CRSE_NUMB NOT NULL VARCHAR2 5 SCBCRSE_EFF_TERM VARCHAR2 6 SCBCRKY_SUBUJ_CODE NOT NULL VARCHAR2 4 SCBCRKY_CRSE_NUMB NOT NULL VARCHAR2 5 SCBCRKY_TERM_CODE_START NOT NULL VARCHAR2 6 SCBCRKY_TE
104. le_name SGBSTDN and constraint_name PK_SGBSTDN SunGard 2004 2005 Student Technical Page 169 Section M General Student Lesson Reports and Processes Reports and processes e Hold Purge SGPHOLD e General Student Purge SGPSTDN e Student Report SGRSTDN See Student Technical Reference Manual Chapter 10 for additional information on Student Module reports and processes SunGard 2004 2005 Student Technical Page 170 Section M General Student Lesson Other Scripts BANNER_HOME student dbprocs functions sgf BANNER_ HOME student views views sgv sgvstd0 sql creates view as_student_data Some views are used in conjunction with Object Access method of retrieving data from database using the concept of layered views you must have the GTVSDAX form table populated with crosswalk values Clients should know the naming conventions and the locations of these other database object creation scripts To use them as models create your own MODS directory as discussed earlier in this course and put any modifications in there For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 171 Section M General Student Lesson Conversion Issues Questions What General Student data do you have in your legacy system How far back do you
105. m run from Job Submission Results are visible on the Application of Payment Review Form TSIAPPL and it populates the Detail Application of Payment Table TBRAPPL Refer to the matrix in the Student Technical Manual p 10 18 for stats about this process SunGard 2004 2005 Student Technical Page 188 Section N Accounts Receivable Lesson Application of Payments Process TGRAPPL Continued Application of payments This chart shows how the process TGRAPPL applies payments using a series of transactions in the Student Account Detail Review Table TBRACCD e Detail codes o ACTF activity fee o T101 tuition o CHEK Check o AMEX American Express Payment e Detail codes are defined in TBBDETC as charge or payment codes TBRACCD TGRAPPL applies BALANCE TBRAPPL table table S 35 ACTF 7000 AMEX pmt chg 0 tbrappl_chg_tran_number 1 tran num 1 tbracct tran num 3 pmt tbrappl_pay_tran_number 3 6965 tbrappl_amount 35 7500 T101 chg 535 tbrappl_chg_tran_number 2 tran num 2 6965 pmt bal bal tbrappl_pay_tran_number 3 tbrappl_amount 6965 of AMEX pmt tbraccd tran num 3 Serger tran num 3 in trans 1 and 2 535 CHEK 535 CHEK pmt chg 0 tbrappl_chg_tran_number 2 tran num 4 pmt 0 tbrappl_pay_tran_number 4 tbrappl_amount 535 SunGard 2004 2005 Student Technical Page 189 Section N Accounts Receivable Lesson Accounting Feed Process TGbREEED Ov
106. manually by the users e What Faculty Load data do you have in your legacy system e How do you determine where to put it in SCT Banner When we talk about SGBSTDN Student record we will see that the advisor pidm is stored in this table To ensure that the student s advisor will appear in the form the advisor must be active for that term and flagged as an adviser sibinst_advr_ind SunGard 2004 2005 Student Technical Page 137 Section J Faculty Load Lesson Self Check Faculty Load Exercise Exercise Write a query which would return the full name id faculty status and effective term for that status for an instructor SunGard 2004 2005 Student Technical Page 138 Section J Faculty Load Lesson Self Check Faculty Load Exercise Answer Key Exercise Write a query which would return the full name id faculty status and effective term for that status for an instructor select substr spriden_last_name 1 15 substr spriden_first_name 1 15 spriden_id sibinst_term_code_ eff sibinst_fcst_code from spriden sibinst where sibinst_pidm spriden_pidm and spriden_change_ind is null order by spriden_last_name SunGard 2004 2005 Student Technical Page 139 Section K Location Management Lesson Overview Objectives At the end of this section you will be able to e Describe the role and functions of the Location Management module Prerequisites To complete this section
107. ment Code Definition Table TBBDETC TBBDETC must be set up if you populate SSRFEES table through the SSADETL form which is not required Other Forms Tables Section Meeting Times Table SSRMEET Location Building Description Table SLBBLDG Faculty Information Form Table SIAINST SIBINST Faculty Assignment Form Table SIAASGN SIRASGN SSASECT uses SSRMEET table to store meeting times and buildings SSRMEET_BLDG_CODE Building information is built in the SLABLDG form and faculty information is built in SIAINST and SIAASGN forms SunGard 2004 2005 Student Technical Page 156 Section L Schedule Lesson SLQMEET and SSAMATX SLQMEET Available Classroom Query Form SLQMEET e Only accessible through SSASECT SSAMATX Building Room Schedule Form SSAMATX e Accessible through menu direct access other form SSASECT SunGard 2004 2005 Student Technical Page 157 Section L Schedule Lesson SQL Plus Questions e What tables are part of the Schedule Module select table_name from all_tables where table_name like SS e What data elements are required desc ssbsect o Notice the NOT NULL columns e What are the key fields in ssbsect select column_name from all_cons_columns where table name SSBSECT and constraint_name PK_SSBSECT SunGard 2004 2005 Student Technical Page 158 Section L Schedule Lesson Reports and Processes Reports and processes Schedule Pur
108. n Fees Accounts Applicants Housing Receivable RESOURCES Enrollment Location Fees A Management Charges and i Housing Payments Recruits and Meal Assignments Banner RF General Admissions Graduates ADVANCEMENT Banner Student FINANCE Notice the placement of the Registration module Accepted Candidates Catalog Schedule General Person Admissions General Student Faculty Load Location Management and AR have all been implemented Registration is connected to all of these modules Accounts Receivable appears after Registration in this chart to show the flow of activity however the implementation order must have AR in place before Registration can occur so that fees from Registration may be assessed SunGard 2004 2005 Student Technical Page 197 Section O Registration Lesson Registration Module Diagram Academic History Instructional Assignments f Grades and Sections a Prerequisites Faculty Load Jobs RESOURCES Enrollment WM Management Housing and Meal Assignments Banner Graduates ADVANCEMENT Student e Catalog is built e Buildings and rooms are defined e Schedule of classes is built e Faculty Information is loaded e Student records are active e Accounts Receivable has been set up e Current students may now register for classes SunGard 2004 2005 Student Technical Page 198 Section O Registration Lesson Registration Module Continued Objectives Examin
109. n y set y spriden_pidm select distinct x spriden_pidm from spriden x where y spriden_id x spriden_id You can then alter the temporary table used for general student information to include an id update sytstdn set sytstdn_pidm select distinct spriden_pidm from spriden where spriden_id sytstdn_id SunGard 2004 2005 Student Technical Page 245 Section Q Conversion Lesson Conversion Example SQL LOADER Code SQL LOADER load ctl load data infile data_file dat badfile bad_data txt discardfile discard_file txt append into table sytiden spriden_pidm sequence 77777777 1 spriden_id position 1 9 spriden_last_name position 10 23 spriden_first_name position 24 39 spriden_change_ind null spriden_entity_indconstant P spriden_activity_date constant 25 DEC 98 spriden_user constant CONVERSION spriden_origin constant CONVERSION into table sytaddr spraddr_pidm sequence 77777777 1 spraddr_atyp_code constant MA spraddr_seqno constant II spraddr_street_linel position 40 58 spraddr_city position 59 73 spraddr_stat_code position 74 75 spraddr_zip position 76 80 spraddr_activity_date constant 25 DEC 98 spraddr_user constant CONVERSION into table sytpers spbpers_pidm sequence 77777777 1 spbpers_ssn position 1 9 spbpers_sex position 81 81 spbpers_birth_date position 82 90 spbpers_activity_date constant 25 DEC 98 SunGard 2004 2005
110. ndsecaesednndetandtasanlstraciwsabnaninenabarvantuats 224 SMe PIU E E E 225 EE 226 Conversion Teenager ee eege 227 Reports Processes End of Term s isccssiccssscisdssscaiascsavsccsastaccesaseodedstauseaesaacdccsdesesedaasadedadeosntens 228 Self Check Academic History Exercises 5 scssiasstasscicunsnstsdunnetuenicedsansaadubadoatieseedstiessnstanensbane 229 Self Check Academic History Exercises Answer ken 232 Section Q Conversion ssecessseceessececssccecsssseceesscceessceceesscsecessseceesseceesscceesscceesscseceessoceesseceessee 236 OVErVICW ioesiisirissessissri ine ensis E ru TEE EEEE EES EEE EEEE ESKAE EEEE ENESE EE TES EKE E NEKEAK 236 Conversion EE eegene 237 EEN 238 CONVERSION Strategie Sensi oesie iii n va Eao EEE EREE EE IEE E ERE REE oiek 240 ei A D f E 241 Conversion Examples snesen e E E 242 Conversion Example Flat File Layout ccccccsccscassenestaaseaesznnsoesseaiseandenaverestenasaateansonesteatewicarse 243 Conversion Example Create EE ege Eege 244 Conversion Example Alter EE 245 Conversion Example SQL LOADER iegeegdieee geegent deg Seege 246 Conversion Example Decode Statement saciviindarecawnisn suneiindasnsingnaandsrcesedasssudabenvetdsrssatueaetovvndss 248 Conversion Example Check data in the temp tables eessceesseceeneceeeeeeeneeceeeeeeeeneeeees 249 Conversion Example Insert Statement ee esgeeeegeuggreetatieege Stegen adr 250 Conversion Example Check the data in SCT Banner ccccccs
111. ng is used Otherwise Academic History information can be implemented as soon as possible after Registration in order for current students to have statistics and courses in Academic History prior to the end of the first live term Banner ADVANCEMENT Objectives Examine Review e Major amp Required Forms and Tables e Reports Processes and Procedures e Conversion of Data SunGard 2004 2005 Student Technical Page 213 Section P Academic History Lesson Academic History Module Continued Grade Code Academic Standing Repeat Equivalent Maintenance Form Rules Form Course Rules Form SHAGRDE SHAACST SHARPTR Grade Code Academic Status Repeat Equivalent Maintenance Table Rules Table Course Rules Table SHRGRDE SHRASTR SHBRPTR Valid Grading Modes Dean s List Calculation Rules day eect Table Continuant Term SHRASDL Rules Form SOACTRM Dean s List Grade Transcript Type Rules Cade Excluded Table Continuing Terms Form SHATPRT SHRASGE Table SORCTRM Transcript Rules Request Type Table SHRTPRT All of these rule tables are required Asterisked text indicates required Major Validation Tables Forms Grade Change Code Validation Form Table STVGCHG Grading Mode Code Validation Form Table STVGMOD Academic Standing Code Validation Form Table STVASTD Many of the validation tables that have been referenced in previous modules SunGard 2004 2005 Stude
112. nnel C Courts Q Electronic Work Queue D CashDrawer IR FinancialAid I Information Access 1 U Utilities N PositionControl gt SunGard 2004 2005 Student Technical Page 36 Section C Course Catalog Lesson Naming conventions Continued Position 2 Position 2 identifies the module that owns the form report process or table The letter assignments will vary by System Position 3 Position 3 identifies the type of form report process or table Letter Type Type A Application form Q Queryform B Base table Batch COBOL process or report process temporary table O Online COBOL process view Positions 4 7 The remaining positions identify a unique four character name for the form report process or table Example e SCACRSE S Student C Catalog A Application CRSE Course Information SunGard 2004 2005 Student Technical Page 37 Section C Course Catalog Lesson Major Validation Tables Forms Validation forms Subject Code Validation Form STVSUBJ Term Code Validation Form STVTERM College Code Validation Form STVCOLL Course Status Code Validation Form STVCSTA Level Code Validation Form STVLEVL Grading Mode Code Validation Form STVGMOD Schedule Type Code Validation Form STVSCHD Validation tables Critical to each module are the related validation tables Validation tables contain the codes that are acceptable to use in a particular fie
113. nt Technical Page 214 Section P Academic History Lesson Institutional Courses Diagram Term Course Maintenance Form SHAINST institutional Course Maintenance Term Header Repeating Table SHRTTRi4Q institutional Course Term Maintenance Repeating Table SHRTCKN institutional Courses Grade Repeating Table SHRTCKG Institutional Course Maintenance Level Applied Repeating Table SHRTCKL If detailed institutional course information is to be converted then all of the tables listed will be required SunGard 2004 2005 Student Technical Page 215 Section P Academic History Lesson Institutional Courses Continued Term Course Maintenance Form SHAINST Term Header Information Table SHRTTRM e Academic Status e Dean s List For each institutional course taken e Institutional Course Term Maintenance Table SHRTCKN subjects course numbers titles etc e Institutional Course Grade Repeating Table SHRTCKG credit hours final grade etc e Course Level Applied Repeating Table SHRTCKL course level applied Records are associated by term and by SHRTCKN sequence numbers Term Header Information Table SHRTTRM Course Section Attribute Table SHRATTR Transcript Comment Table by Level SHRTMCM Transcript Comment Table by Term SHRTTCM SunGard 2004 2005 Student Technical Page 216 Section P Academic History Lesson Transfer Courses Diagram Transfer Course Form SHATR
114. ntents Continued Section H Admissions E 107 EE eebe 107 SCT Banner Student Admissions MOGUl Cay cssistescsizvudscerdosarmssedsopsiceassswienataeutbeatioueuieleeads 108 Admissions Application Form GA A ADM 111 Quick Admit Form GA AOUIK 112 Admissions Decision Form SAADCRYV cc cccccccccecsssesssececececeesessnceeceeeeeesensnsaaeeeeeceens 113 SOLA US E 114 JEA 010 E 115 EREM Eeer 116 OMY CISION EE 117 Self Check Admissions E 118 Self Check Admissions Exercise Answer Key 119 Section I Overall Forms and Tables ccsscccsssscssssscesssccssssccssssccssseccssssscsssscsssssscssssssoess 120 EIERE 120 Overall D EE KE EE 121 e E aaa aa a a a ese 123 COVEFSION EE 124 eegenen eege EOS EE EEE AE AASE E EEEa EE 125 Self Check Overall Forms and Tables Exercise ccccsscccesssscseceesenteceeessneeecsssseaeeeensaes 126 Self Check Overall Forms and Tables Exercise Answer Key 127 Section J Faculty Load siesssssssvsssesesssiecssseccsesedsssensssassanssvncssevvnnssenss soncessoencesnssessieenseecnsnvauscacenne 128 EE 128 St dent EE 129 Faculty Load Module sicccsssscccssndssesvadasssevasnecedaatabscaradavan sahsoedansaseadsenageddastoacdeadusccesasnasedeasiees 130 Faculty Load ME 131 Faculty Information Form SIAINST Faculty Member Base Table SIBINST 132 Faculty Assignment Form SIAASGN Faculty Assignment Table SIRASGN 133 e E CN 134 Reports and R
115. onversion Considerations Considerations e Keeping track of PIDM on legacy system e Generated ID or SSN e Name Address formatting o Avoid if using letter generation o Additional data standards if using BannerQuest e Address types o Do you have Multiple ID s on legacy system SunGard 2004 2005 Student Technical Page 237 Section Q Conversion Lesson Conversion Steps Steps Document steps as you proceed Review current data e Determine scope o What will you convert Map legacy data to Banner tables e Write a detail plan of o Data to be converted o Banner tables to be populated o Deadlines timelines Review plan amp get approval from users Develop procedures amp programs Test conversion in TEST or PPRD database Users verify data TEST again and make corrections to procedures and programs Do conversion in production Users verify data Resources Users can populate the validation tables and can will use some of the seed data Functional consultants may assist with the conversion process mapping Refer to the Student Technical Reference Manual for more information on conversion topics e Chapter 5 Conversion Has good information regarding Accounts Receivable Data Conversion e Chapter 6 Migration to Production Includes info about seed data that must be kept e Chapter 7 Integration Includes a list of Shared Tables and has information about ethnicity codes and non resident aliens e Chapter 8 Process
116. ple t1 then tl must exist A given foreign key value must have a matching primary key value somewhere in the referenced relation if that foreign key value is non null SunGard 2004 2005 Student Technical Page 53 Section D Referential Integrity Lesson Primary Key Constraints Constraints e Primary Key special case of a candidate key unique identifier absolutely fundamental to the operation of the overall relational model to enforce unique non null keys e Uniqueness At any given time no two distinct rows or records of a relation have the same value for any given attribute e Minimality None of the attributes can be discarded from the set of attributes without destroying the uniqueness property Primary key Every relation has at least one candidate key because at least the combination of all of its attributes has the uniqueness property One candidate key is designated at the primary key The remaining candidate keys if any are called alternate keys Example SPRIDEN PIDM and LAST_NAME If they were each unique then the relation has two candidates PIDM amp LAST_NAME PIDM could be chosen as the primary key LAST_NAME then becomes an alternate key Note PIDM amp LAST_NAME are NOT unique SCT Banner naming convention PK_ primary key table name Example PK_STVTERM is defined by alter table STVTERM add constraint PK_STVTERM Primary key stvterm_code For Definition of Primary Key Integ
117. ree SunGard 2004 2005 Student Technical Page 88 Section F Curriculum Program Rules Lesson Curriculum Rules Form GOACURR SOACURR SOBCURR is the underlying table e Used to view or create curriculum rules e Rules are based on Program Definitions if you are using program rules otherwise program is not a required field Note The key block for SOACURR uses term which is optional If you put the term in the key the form only shows you the rules which are valid for that term no future term rules are displayed Note When program is used on a rule the level campus college and degree have to match what has been defined on SMAPRLE If the campus on SMAPRLE is blank all campuses are valid for the rule The information defaults back into SOACURR from the List of Values window for SMAPRLE SunGard 2004 2005 Student Technical Page 89 Section F Curriculum Program Rules Lesson Curriculum Rules Control Form SOACTRL SOACTRL SOBCTRL is the underlying table e Indicators determine if how various areas related to curriculum are used e Can set Use CAPP s Program Planning to Y or N e Indicators set severity level of error checking by module if curriculum rules are used SunGard 2004 2005 Student Technical Page 90 Section F Curriculum Program Rules Lesson Major Minor Concentration Rules Forms A Major Minor Concentration e Curriculum Major Rules Form SORCMJR e Cur
118. riculum Minor Rules Form SORCMNR e Curriculum Concentration Rules Form SORCCON Each table contains on off indicators for each module using curriculum rules e e g Admissions sorcmjr_adm_ind Y The data from these tables shows up through SOACURR SunGard 2004 2005 Student Technical Page 91 Section F Curriculum Program Rules Lesson Conversion Issues Issues Will your users build curriculum rules If so then can you use the rules to your advantage when converting student data e Can you use the student s major on legacy side to get the valid department and program codes from SOBCURR and SORCMJR If you convert the legacy major codes to match Banner major codes in STVMAJR then you can run a query which will use curriculum rules to give you the valid department and program You can use the converse of this to determine which records on your legacy system will have an invalid major when converted to Banner SunGard 2004 2005 Student Technical Page 92 Section F Curriculum Program Rules Lesson Summary Summary e Build rules in SOACURR o All curriculum rules must be built before setting indicators in SOACTRL e Build Program Rules on SMAPRLE if you plan to use CAPP s Program Planning e Build control rules in SOACTRL o if sobctrl_curr_rule_ind Y then sobctrl_program_ind must Y This means that you are using CAPP s Program Planning o This indicator means that major curr
119. rity Constraints Refer to p 7 10 in Oracle 7 Server Concepts SunGard 2004 2005 Student Technical Page 54 Section D Referential Integrity Lesson Primary Key Constraints Continued Data dictionary views Oracle 7 Server Reference contains a listing of all data dictionary views such as all_constraints etc The Data Dictionary views all_constraints and all_cons_columns as a way of getting detailed information about all constraints desc user_constraints and all_constraints select from all_constraints where table_name STVTERM ALTER statements To see alter statements that create primary key constraints for a table ex SCBCRKY In SQL PLUS run GURRDDL script for SCBCRKY alter table SCBCRKY add constraint PK_SCBCRKY Primary key scbcrky_subj_code scbcrky_crse_numb SunGard 2004 2005 Student Technical Page 55 Section D Referential Integrity Lesson Foreign Key Constraints Foreign key A foreign key is an attribute or attribute combination in one relation whose values are required to match those of the primary key of some other relation to ensure that children are not updated inserted if parent rows do not exist and to prevent the deletion of parents if children do exist Foreign to primary key matches represent references from one relation to another they are the glue that holds the database together Examples e STVTERM Primary Key STVTERM_CODE e SGBSTDN Foreign Key TERM_C
120. rm unless all courses are built in Catalog first Catalog controls the courses and the TYPE of courses i e labs that may be included in the Schedule SunGard 2004 2005 Student Technical Page 34 Section C Course Catalog Lesson Course Catalog Continued 4 Forms and tables SCACRSE is the main form SCBCRSE SCBCRKY SCRLEVL SCRGMOD SCRSCHD are the main tables All of these tables are required RED asterisked text in the diagrams of this manual indicates required elements Major Forms e SCABASE e SCACRSE Major Tables SCBCRSE SCBCRKY SCRLEVL SCRGMOD SCRSCHD SunGard 2004 2005 Student Technical Page 35 Section C Course Catalog Lesson Naming conventions Naming conventions All SCT Banner objects adhere to naming conventions Objects include forms tables processes etc For more information refer to Chapter 1 of the Student Technical Reference Manual Form process and table naming The names of all SCT Banner forms except menu forms reports processes and tables are seven characters long with each character representing a position location Example Character SCACRSE Position Location 1234567 Position 1 Position 1 identifies the primary System that owns the form report process or table Note The letters W Y and Z are reserved for client applications which coexist with SCT Banner A Advancement O Customer Contact _ B PropertyTax LP HR Payroll Perso
121. rtgpa_gpa_type_ind I order by spriden_last_name SunGard 2004 2005 Student Technical Page 232 Section P Academic History Lesson Self Check Academic History Exercises Answer Key Continued Exercise 2 Write a query which returns full name id course level crn subject code course number and grades for a given term Prompt user for term The solution to this exercise contains some formatting as well as the select statement You may introduce the formatting if there s time col stunam for a25 hea STUDENT NAME col id for a10 hea ID col levl for a5 hea LEVEL col crn for a6 hea CRN col subj for a6 hea SUBJ col crse for a6 hea CRSE NUMBER col grde for a6 hea GRADE col today new_value xtoday col term new_value xterm ttitle LE xtoday CE Student Grade Report for xterm RI Page format 999 sql pno SKIP2 break on stunam nodup skipl on id nodup on id nodup on levl nodup SunGard 2004 2005 Student Technical Page 233 Section P Academic History Lesson Self Check Academic History Exercises Answer Key Continued Exercise 2 continued select from where and and and and and and and and order substr spriden_last_name 1 15 I substr spriden_first_name 1 15 stunam spriden_id id shrtckl_levl_code levl shrtckn_crn crn shrtckn_subj_code subj shrtckn_crse_numb crse shrtckg_grde_code_final grde sysdate today shrtckn_
122. s are trademarks or registered trademarks of their respective holders Table of Contents Section At NAPOMUCHIOM cs sicesccesinacenasasteccoansnancceessnsnenssnteccnedsoassansciuncouteodsnasacaysensieaneuasastuacoenssanse 8 OVETVIEW aeren 8 ee e E 9 Workbook Contents E 10 Section B Student Technical Training OvervieW e ssesccesesssesocssossoesooescsssesoossosssessoesosessse 11 Student Technical Training Overview eege 11 Th St dent Syste eieiei esii eis ae EEE ERE ERAR ET E ES 12 Shared St de nt V lidation FORMS is dscssascssnnsdyanstvvdanesaesninasenvenslesxanuruntasestansncansastawasaddounendasnees 16 Frod ct Valle CIWS EE 19 Student System TEE 20 Recommended Order for Conversion jaisiasssncnicenstacnsusnnvaniaissnsisnadennetdaisandldoaspansdaseausdsdwsneiuiddtes 21 SCT Banner Student Directories ssnssseeeeseeesseesseesseeesettesttrsstesstesstreseeesseetssresseesseeeseeesstte 22 Directory Structure for Client Developed femms 24 R view of Database W018 4 1 55a5etasunie sce sinaacGgpendeee E E EE E eae 25 The Data Dictionary nepsoiiiiiosiii ierre ni ien E E EAE EE ER 26 GURPDED PBA CE US erageet 27 Self Check Data Dictionary PRerOiSG ciscsacessewazecravegaceyeuedecsesseeaceveeeteeese ieee came sisirin rutean 28 Self Check Data Dictionary Exercises Answer key 29 Section Ce Course E ET 30 LG 2575 611 sh gener enee 30 Student SY ET EE 31 Course Catalog Mole E 32 Eege eelere 34 Naming COMI EMILIO S sne
123. s section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Section Contents OVE VAG WD eeneg 151 Student System OVC EE 152 BS TNR e E 153 Section General Information Form SSASECT Section General Information Base Table SSB HN WT 155 Term Control Form GSOATERM 156 SLQMEET and SSAMATX EE 157 KA Ed 158 PREP CR 159 Ee E 160 Conversion KEE eege eege 161 Self Check Schedule EE 162 Self Check Schedule Exercise Answer Key scccssisccisscescccdasesicesasesdadscanoacdsoarercedeasesscesanens 163 SunGard 2004 2005 Student Technical Page 151 Section L Lesson Schedule Student System Overview Diagram Academic Courses S We a gay 7 and GPA Instructional z Assignments aer Sections TE FINANCIAL General Prerequisites AID Person Stall y raculty Load Empl Revistrati Disbursements mployees Jobs egistration Prospects p Registration Fees Accounts Applicants Housing Receivable RESOURCES Enrollment Location Hee Ze Management Charges and P Housing Payments Recruits and Meal Accepted Candidates General Student Assignments Banner Graduates ADVANCEMENT Banner FINANCE Note the relationship of the Schedule module to the entire SCT Banner System Catalog General Person Faculty Load Location Management and validation tables must be set up before using Schedule SunGard 2004 2005 Student Technical Page 152 Section
124. sen a E EE N a mids sa epee neta ae ae 36 Major Validation Tables Forms js c issssccssceccciestasasesasscacsuasioeasinatedesasnsdeddaonsvenssagseactasnaeeddaaesoen 38 Basic Course Information Form GCACRSE 39 EE A0 ee EE 41 Uer 42 Self Check Course Catalog Exercises ssccccicssiscacsadessceaaseadansssaseadsasnesecsesagoanasdeveaceannsenvanvesuate 43 Self Check Course Catalog Exercises Answer key 46 Section D Referential Tite grity ccccsssccsssssssesssssssnsssessesssvncsoasonessenss sestonssunes sessesseess sececavevnsssesoese 50 EE eege Eege 50 Referental INEST oenen E re paneer ere mene one rr Ne re areer ere ee 51 Referential Doerner 52 Referential Integrity KT 53 Primary Key Constraints sissies sasidsccesscrvacvasnsvnse irinna ne AE ERE ERE i rR aiis 54 Foreign Key E 56 Creating Foreign Key Eege 58 Validation Tables GOdes ai cidacissseacsiantcecdaseondaessundeceaavtvenvenesscetudatbenvasagnedssobedactuacdcestaseoncdaaaagedes 60 Refer ntial Integrity Summary 5554 csscecavcanstascseanienedidatnsussisdudanibedtaastauascunhadess duadebawenutedtancnceavans 61 Table of Contents Continued Section E General E E 62 Elei gereegelt 63 EEN E e TE 64 General Person Module Objectives s lt csscesecsendesecbescaiscinsnsvedseatabacdiasaved sasnssanassnseadsaguatecvasdeades 65 General Person Forms and Tables yicscsissesncassvacensannentonteatsvanionatasdciadnworansntataanitiadeesacnebaidene 66 PIDM Aid SOB EE 68 Data Stan EE
125. ses to move the AR transactions from AR to Finance TGRFEED inserts rows into the GURFEED table FURFEED reads each row and loads the data into the Finance system TSRRFND FURAPAY Processes to move AP transactions from AR to Finance TSRREND inserts rows into the GURAPAY table FURAPAY reads each row and loads data into the Finance system SunGard 2004 2005 Student Technical Page 13 Section B Student Technical Training Overview Lesson The Student System Continued Student and Financial Aid Disbursements TSASPAY Student Payment form Users can disburse Financial Aid from this form If automatic disbursement flag on TSACTRL is checked then disbursement is done automatically If flag is unchecked the user can disbursement manually Manual disbursement is performed by entering a Y in the Recalculate Financial Aid field on the Financial Aid Recalculation window An AR transaction will be created if disbursement occurred TSASPAY Student Payment form Authorized and memoed Financial Aid will display on the student payment form TSASPAY Authorized Financial Aid can reduce the amount due on this form if the Committed Authorized FA Reduces Amount Due Indicator on the TSACTRL form is checked Memos never reduce amount due TSRCBIL Student Billing Process Can have authorized FA reduce amount due if flag is seton TSACTRL Memos can only be printed RPEDISB allows disbursable aid for a specified
126. ss updates AR indicators in SERMASG SLRPASG SLRRASG and SFBETRM TSRCBIL is a C program run from Job Submission which can be run in sleep wake mode SunGard 2004 2005 Student Technical Page 191 Section N Accounts Receivable Lesson Other Scripts BANNER_HOME student arsys functions t f ex tofbala sql BANNER_HOME student views views t v tovbal0 sql creates view at_ar_history_by_balance Some views are used in conjunction with theObject Access method of retrieving data from the database using the concept of layered views you must have the GTVSDAX form table populated with crosswalk values This one does not use the GTVSDAX table not all layered views refer to GTVSDAX It is used as a crosswalk table to spread out repeating table row values into columns for easier reporting Clients should know the naming conventions and the locations of these other database object creation scripts To use them as models create your own MODS directory as discussed earlier in this course and put any modifications in there For more information about Object Access views and the GTVSDAX translation form table contact your account manager to request the manuals and or appropriate training SunGard 2004 2005 Student Technical Page 192 Section N Accounts Receivable Lesson Conversion Issues Conversion issues Balance Forward e Set up controls on TSACTRL e Populate TBBDETC Detail Charge Payment Code
127. sscessecssesssceerencseesneeeseeseess 251 Conversion Example Update SOBSEQN jcccccsssccsssncsessassevassastedecsacssensancssessaauesectacnesessennaoes 252 Conversion Example Clean the data in SCT Banner 253 Conversion Ex mple Shell Seript EE 254 Section A Introduction Lesson Overview Workbook goal This course is designed to provide an overview of the major tables reports and processes included in each module of the Student System as well as providing an introduction to the SCT Banner directory structure and a closer look at some of the database object creation scripts for each of the Student System modules This training program will provide the Student technical staff with a basic knowledge of the tables reports and processes that make up the SCT Banner Student System The training also includes discussion about data conversion as well as a conversion example exercise Intended audience Programmers DBA s and analysts who may teach others about SCT Banner tables and processes will benefit from the training Section contents QV ELVIS gece eege EEN 8 Introd cHO eee a a a a a a a a a e E Tne 9 Work DOOK CONDENS E 10 SunGard 2004 2005 Student Technical Page 8 Section A Introduction Lesson Introduction Introduction This course is designed to provide an overview of the major tables reports and processes included in each module of the Student System as well as providing an introduction to the S
128. sscscdasssedesssarascasscesecedaanicceseeteceddaanaces 194 Self Check Accounts Receivable Exercises Answer ken 195 SunGard 2004 2005 Student Technical Page 175 Section N Accounts Receivable Lesson Student System Overview Academic Courses i gt sepa y and GPA Instructional Assignments Grades and Banner Sections SCH FINANCIAL Prerequisites AID Staff Faculty Load Disbursements Registration Pr panes The ospects Registration Fees aiid Receivable Housing HUMAN Recruiting RESOURCES Enrollment Location Fees A Management Charges and Housing A Payments Recruits and Meal re caga Assignments Banner ccepte ener Pami Admissions Candidates Student Graduates ADVANCEMENT we Applicants SunGard 2004 2005 Student Technical Page 176 Section N Accounts Receivable Lesson Accounts Receivable Module Courses pa Credits CAPP may and GPA Grades and Prerequisites i FINANCIAL 5 Disbursements Registration Fees i dE Housin Fees Management Charges and Payments Banner FINANCE For additional process flow and database schematics refer to the Student Technical Reference Manual Chapters 8 and 9 Objectives Examine Review e Major amp Required Forms and Tables e Reports Processes and Procedures e Conversion of Data SunGard 2004 2005 Student Technical Page 177 Section N Accounts Receivable Lesson Diagram Detail Cod
129. sson Review of Database Tools Brief review This brief review of database tools will cover the data dictionary which we will use extensively throughout the training and a brief review of the principle of referential integrity an understanding of which is essential to a successful conversion There are several good ways to see the contents of the database and learn the structure and content of the tables Data Dictionary Built into Oracle is the Data Dictionary a series of views that give detailed information about the database This is covered in more detail in the DBA Toolkit class GURPDED process from Job Submission Banner has provided a form interface to the Data Dictionary with the GURPDED utility program run through job submission Technical Addendum Also available is the Technical Addendum which is a large all inclusive hard copy version of the output of the GURPDED process Even if you have a copy of the Technical Addendum you should know how to gather table information directly from the database Itis better to use the Data Dictionary or GURPDED to create a customized Technical Addendum for your institution Third party navigator Finally your institution may have a third party navigator which will be the tool that you will use instead of the data dictionary These third party products are NOT SUPPORTED BY SCT SunGard 2004 2005 Student Technical Page 25 Section B Student Technical Training Overvie
130. sues Questions e Will Admissions data be converted or entered manually by the users e What Admissions data do you have in your legacy system Admissions is typically not converted for going live but may be converted later for Institutional Research purposes SunGard 2004 2005 Student Technical Page 117 Section H Admissions Lesson Self Check Admissions Exercise Exercise Write a query to get the id last name term of entry and student type for applicants for a specific future term prompt user for term code The records returned should be for the most current application for that term and the decision should be the most recent decision made that matches that application SunGard 2004 2005 Student Technical Page 118 Section H Admissions Lesson Self Check Admissions Exercise Answer Key Exercise Write a query to get the id last name term of entry and student type for applicants for a specific future term prompt user for term code The records returned should be for the most current application for that term and the decision should be the most recent decision made that matches that application select spriden_id substr spriden_last_name 1 15 saradap_term_code_entry saradap_styp_code from spriden saradap where saradap_term_code_entry amp TERM and saradap_pidm in select sarappd_pidm from sarappd x where sarappd_term_code_entry saradap_term_code_entry and sarappd_pidm sara
131. t Major Validation Tables Forms Term Code Validation Form Table STVTERM Residence Code Validation Form Table STVRESD Level Code Validation Form Table STVLEVL Student Status Code Validation Form Table STVSTST Campus Code Validation Form Table STVCAMP Class Code Validation Form Table STVCLAS College Code Validation Form Table STVCOLL Degree Level Code Validation Form Table STVDLEV Degree Code Validation Form Table STVDEGC Degree Award Category Code Validation Form Table STVACAT Major Minor Concentration Code Validation Form Table STVMAJR Student Type Code Validation Form Table STVSTYP Most of the validation tables have been used in Recruiting and Admissions When the student record is created the data from Admissions SARADAP will default into SGBSTDN Additional Information SGRADVR Multiple advisors SGRSPRT Sports SGRCHRT Cohorts SGRSATT Attributes SGRDISA Disability Services Rules SOBCURR major program department etc SGRCLSR Student classification rules SunGard 2004 2005 Student Technical Page 168 Section M General Student Lesson SQL Plus Questions e What tables are part of the General Student Module select table_name from all_tables where table_name like SG e What data elements are required desc sgbstdn o Notice the NOT NULL columns e What are the key fields in sgbstdn select column_name from all_cons_columns where tab
132. t scberky_term_code_end Child Values 199610 199910 Parent and child STVTERM is the parent SCBCRKY is the child Term values cannot appear in SCBCRKY fields unless they are in STVTERM A rule defined on a column or set of columns in one table that allows the insert or update of a row only if the value for the column or set of columns the dependent or child value matches a value in a column of a related table the referenced or parent value Source Oracle 7 Server Concepts SunGard 2004 2005 Student Technical Page 52 Section D Referential Integrity Lesson Referential Integrity Key Types Key types Referential Integrity relies on two types of keys e Primary Keys e Foreign Keys These keys are implemented as constraints which enforce unique non null keys Two Integrity Rules e Entity integrity e No attribute participating in the primary key of a base relation is allowed to accept null values Referential integrity e Ifa base relation includes a foreign key matching the primary key of some other base relation then every value of the foreign key in the Ist base relation must either be equal to the value of the primary key in some tuple row of the other base relation OR be wholly null i e each attribute value participating in that foreign key value must be null The two base tables are not necessarily distinct e The basic intent of this rule is that if some tuple t2 references some tu
133. t all applicants for a term prompt for term Full Name Entry Term Test Code Test Score High School GPA for Students who took either the ACT English or the SAT Verbal tests Your query should return only records with values in all the above areas SunGard 2004 2005 Student Technical Page 126 Section l Overall Forms and Tables Lesson Self Check Overall Forms and Tables Exercise Answer Key Exercise Get the following information about all applicants for a term prompt for term Full Name Entry Term Test Code Test Score High School GPA for Students who took either the ACT English or the SAT Verbal tests Your query should return only records with values in all the above areas Step 1 desc stvtesc find SAT Verbal and ACT English code Step 2 desc sortest get proper column names Step 3 desc sorhsch find column name for hs gpa Step 4 select spriden_id substr spriden_last_name 1 15 II substr spriden_first_name 1 15 saradap_term_code_entry sortest_tesc_code sortest_test_score sorhsch_gpa from spriden saradap sortest sorhsch where sorhsch_pidm saradap_pidm and saradap_term_code_entry amp term and sorhsch_pidm sortest_pidm and sortest_tesc_code IN S0O1 AO1 and sorhsch_pidm spriden_pidm and spriden_change_ind is null and sorhsch_gpa is not null order by spriden_last_name SunGard 2004 2005 Student Technical Page 127 Section J Faculty
134. t be entered HERE Query for your record in SPRADDR e Describe SPRADDR e Write a query to retrieve the data that you entered in SPRADDR select from SPRADDR where spraddr_activity_date like sysdate Notice the data in e spraddr_atyp_code e spraddr_seq_no e spraddr from date e spraddr o date Note To get information from yesterday instead of today use like sysdate I instead of like sysdate Note If a student changes addresses for a defined period of time you would populate the from and to_date fields These must be accounted for in reporting SunGard 2004 2005 Student Technical Page 75 Section E General Person Lesson General Person Procedures Continued Query for your record in SPRHOLD e Describe SPRHOLD e Write a query to retrieve the data that you entered in SPRHOLD select from SPRHOLD where sprhold_activity_date like sysdate Notice the data in e sprhold_hldd_code e sprhold_user e sprhold_from_date e sprhold_to_date SunGard 2004 2005 Student Technical Page 76 Section E General Person Lesson SPRPDIR Person Directory SPRPDIR The Person Directory SPRPDIR produces a list of persons addresses and primary phone numbers by type of person Recruit R Applicant A Student S Faculty F Tables and views used Tables used in SPRPDIR pc SPBPERS General Person Info Table SRBRECR Recruit Information Table SARADAP Applicant Information Table SGBSTDN
135. term_code term spriden shrtckl shrtckn shrtckg shrtckg_pidm spriden_pidm spriden_change_ind is null shrtckg_term_code amp term shrtckg_pidm shrtckn_pidm shrtckg_term_code shrtckn_term_code shrtckg_tckn_seq_no shrtckn_seq_no shrtckg_pidm shrtckl_pidm shrtckg_term_code shrtckl_term_code shrtckg_tckn_seq_no shrtckl_tckn_seq_no by 1 5 6 SunGard 2004 2005 Student Technical Page 234 Section P Academic History Lesson Self Check Academic History Exercises Answer Key Continued Exercise 3 Write a query which returns full name id level all transfer courses and grades for all students who have transfer work Order by student last name set pagesize 47 set linesize 130 col stunam for a25 hea STUDENT NAME col id for al0 hea ID col term for a6 hea TERM col subj for a20 hea TRAN CRSE NAME TRUNC col crse for a6 hea TRAN CRSE NUMBER col titl for a25 hea TITLE TRUNC col grde for a6 hea GRADE select substr spriden_last_name 1 15 I substr spriden_first_name 1 15 stunam spriden_id id shrtrcr_term_code term shrtrcer_trans_course_name subj shrtrcr_trans_course_numbers crse shrtrcr_tcrse_title titl shrtrcer_trans_grade grde from spriden shrtrcr where spriden_pidm shrtrcr_pidm and spriden_change_ind is null order by spriden_last_name shrtrcer_term_code SunGard 2004 2005 Student Technical Page 235 Section Q Conversion Overview Lesson
136. tory Section Q Conversion SunGard 2004 2005 Page 10 Student Technical Section B Student Technical Training Overview Lesson Student Technical Training Overview Intended audience Programmers DBA s and analysts who may teach others about SCT Banner tables and processes will benefit from the training Objectives At the end of this section you will be able to e Describe many of the basic components of the SCT Banner Student System Prerequisites To complete this section you should have e completed OR101 Introduction to Oracle e completed SCT Banner Navigation Section contents Student Technical Training Overview sciecciiassiasscecsascsnedascisecedsansier sastedanasaioand ECO Nee 11 The Student Sy EE 12 Shared Stud t Validation POMS scvntecsesceaatewagnocneieGaceyseraeetatseadeseumaencedinadeerarwGeedeuiacearenrnes 16 Product Table Ines 2sueeeuast eeek tgeegieeeenk REE RE E EREEREER EOE OERE 19 Stud nt System TEE 20 Recommended Order for WE EE 21 SCT Banner Student Directories teen EaEeeg 22 Directory Structure for Client Developed Items si ccisccssissssourecssasesssnndavsnscsadastodeavesssstanesssenane 24 Review of Database TO0I8 sccissicscissstesesiansaccesasnavedsasnecaasasnasandanievendasenenesancusenveseaoasosaaveevasndyenie 25 CABECA DIANTO EE 26 GURPDED EE 27 Self Check Data Dictionary Exercise sesssesesssessesserrssreestttssrtssressreesetessetessressresseenseet 28 Self Check Data
137. urity number from SPBPERS where changes were made to the ID records in SPRIDEN SQL gt SELECT spriden_pidm spriden_id spriden_first_name spriden_last_name spriden_change_ind spbpers_ssn FROM spbpers spriden WHERE spriden_pidm spbpers_pidm AND spriden_change_ind I Note spriden_id is not necessarily the same as spbpers_ssn That is an institutional decision Exercise 4 Write a query to extract information that you would use on a mailing label For this query select the address type that appears the maximum number of times in the SPRADDR table You should extract the most current record from the SPRIDEN table For purposes of simplicity assume that all SPRADDR records for this address type are current Step 1 SQL gt SELECT DISTINCT spraddr_atyp_code count FROM spraddr GROUP BY spraddr_atyp_code RESULT List of address types with counts of each type Choose max count Step 2 SQL gt SELECT spriden_first_name spriden_last_name spraddr_street_linel spraddr_street_line2 spraddr_city spraddr_stat_code spraddr_zip FROM spriden spraddr WHERE spriden_pidm spraddr_pidm AND spriden_change_ind IS NULL AND spraddr_atyp_code PR SunGard 2004 2005 Student Technical Page 83 Section F Curriculum Program Rules Lesson Overview Objectives At the end of this section you will be able to e Describe the forms and tables used in curriculum program rules functional
138. verview Objectives At the end of this section you will be able to Describe the role and functions of the General Person module Prerequisites To complete this section you should have completed OR101 Introduction to Oracle completed SCT Banner Navigation Section Contents Student System OVERVIEW cops iccisnicadevniosaaccedaacsiaashedvaceiaed E E EEEE ARE EEAO E EKIA EEEE 63 General Person Module 64 General Person Module Object Ve Sinnica a E 65 General Person Forms and Tables cccccessssscccececeesessnsecececececsesensasecececeeeesessnseaeeeeeeeesenes 66 PIDM arid SOB SE OM E 68 Data RE Ee EE 70 General Person Drocedures disri ss ri stirrat Arara rS ESES ias STES 73 SPRPDIR oriana A aaa a aa E a E E Aa AEAEE 71 CONVErSIOM EE 78 EE 79 Self Check General Person Exercises ccccccccccesssssssececececsensssnseceeeceescsesesseaeeeecesesenensaaees 80 Self Check General Person Exercises Answer Key ccsccccsscceessececeneeecseeeeesaeeesnaeerenas 82 SunGard 2004 2005 Student Technical Page 62 Section E General Person Lesson Student System overview Academic Courses Histor SE y and GPA Instructional Assignments Grades and Ze Sections Ge FINANCIAL General Prerequisites AID Person Stall y raculty Load Disbursements Registration Accounts Applicants Receivable RESOURCES Enrollment Location Hee NM Management Charges and Housing z Payments Recruits and Meal
139. w Lesson The Data Dictionary Description The Data Dictionary is a set of tables and views that are used as a read only reference about the database The Data Dictionary stores information about both the logical and physical structure of the database Types of Data Dictionary views USER_xxxxx shows objects and events owned by user ALL_xxxxx shows all objects and events to which user has access DBA_xxxxx restricted assigned only to those with DBA role ALL_TABLES descriptions of tables ALL_COL_COMMENTS comments on columns of accessible tables ALL_TAB_ COLUMNS lists of columns of all tables ALL_TAB_ COMMENTS comments on tables ALL_USERS information on all users in database ALL VIEWS lists text of views accessible to user ALL_INDEXES descriptions of indexes ALL_IND_COLUMNS lists columns of the indexes For a complete reference refer to your Oracle documentation SunGard 2004 2005 Student Technical Page 26 Section B Student Technical Training Overview Lesson GURDDED Procedure Description This procedure is run from the Process Control Submission Form GJAPCTL Parameters Enter Parameters e Table Name e Table Owner Output Output Technical Addendum e To DATABASE e View or Print from GJIREVO SunGard 2004 2005 Student Technical Page 27 Section B Student Technical Training Overview Lesson Self Check Data Dictionary Exercise Exercise 1 Find out what inde
140. wish to go with your data conversion How do you determine where to put legacy data in Banner Must have a student record with sgbstdn_term_code_eff first term of history SunGard 2004 2005 Student Technical Page 172 Section M General Student Lesson Self Check General Student Exercise Exercise Write a query that returns the student s full name id advisor s name major code and residency code from the current student record SunGard 2004 2005 Student Technical Page 173 Exercise Section M General Student Lesson Self Check General Student Exercise Answer Key Write a query that returns the student s full name id advisor s name major code and residency code from the current student record select from where and and and and and and substr S spriden_last_name 1 15 substr S spriden_first_name 1 15 S spriden_id sgbstdn_majr_code_l sgbstdn_resd_code substr A spriden_last_name 1 15 spriden S spriden A sgbstdn sgradvr sgradvr_pidm S spriden_pidm S spriden_change_ind is null sgradvr_term_code_eff SELECT MAX I SGRADVR_TERM_CODE_EFF FROM SGRADVR I WHERE I SGRADVR_TERM_CODE_EFF lt amp term AND SGRADVR_PIDM I SGRADVR_PIDM sgradvr_pidm sgbstdn_pidm sgbstdn_term_code_eff SELECT MAX B SGBSTDN_TERM_CODE_EFF FROM SGBSTDN B WHERE B SGBSTDN_TERM_CODE_EFF lt amp term AND SGBSTDN_PIDM B SGBSTDN_PIDM sgradvr_advr_pidm A spr
141. xes exist for the course catalog table SCBCRSE Exercise 2 List the columns in the SCBCRSE indexes that you discovered SunGard 2004 2005 Student Technical Page 28 Section B Student Technical Training Overview Lesson Self Check Data Dictionary Exercises Answer Ke Exercise 1 Find out what indexes exist for the course catalog table SCBCRSE desc all_indexes select index_name from all_indexes where table _ name SCBCRSE Result scbcrse_key_index Exercise 2 List the columns in the SCBCRSE indexes that you discovered select column_name from all_ind_columns where table name SCBCRSE and index_name SCBCRSE_KEY_ INDEX Result scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term SunGard 2004 2005 Student Technical Page 29 Section C Course Catalog Lesson Overview Intended audience Programmers DBA s and analysts who may teach others about SCT Banner tables and processes will benefit from the training Objectives At the end of this section you will be able to e Describe the role and functions of the Course Catalog module Prerequisites To complete this section you should have e completed OR101 Introduction to Oracle e completed SCT Banner Navigation Section contents OVETVIEW eege 30 Student System CIV SIV UV enee gege 31 Course Ee TE 32 Course Catalog i5iiccaceacedsczaadaasdveadsasnncde daseasadessunduavesnnnedssimensdaaduatadausnsoedasadeaad va

SCT Banner Technical Student Technical Training Workbook

Contents

Download Pdf Manuals

Related Search

Related Contents