Home

Financial Aid Technical - Youngstown State University

Contents

1. Z zo ll zu ROVST96 STYP CODE 9 UG ROVST96 STST CODE IN AS SA RCRAPP1_PIDM RCRAPP2 PIDM lt RCRAPPx ROVST96 PIDM RCRAPP2 PIDM PIDM join AND RCRAPP2 INFC CODE RCRAPP1 INFC CODI AND RCRAPP2 SEO NO RCRAPP1 SEQ NO AND RCRAPP1 AIDY CODE RCRAPP2 AIDY CODI AND ROVST96 AIDY CODE RCRAPP2 AIDY CODI AND RCRAPP2 AIDY CODI SAIDY OO 7 AND RCRAPP2 PIDM PIDM 1 LH 1 Z z gt Pp Evo Eu BE Z U GI LH LH GI Gl Gl FI Continued on the next page SunGard 2004 2005 Financial Aid Technical Page I 8 Hints on Improving Performance of Rules Created on RORRULE Continued Non RCRAPPx Compiled code ROVST95 STST CODE ROVST95 LEVL CODE RCRAPP2 MO RCRAPP1 RCRAPP1 However when it is created in this order ROVST95 STYP CODE AND lt First rule line is Non RCRAPPx column ro AND N AS SA D EL CDE UG AND D AND VER FICAT ON MSG 1 AND CURR R ND y R RECO The resulting compiled code DOES NOT INCLUDE either the RCRAPPI to RCRAPP2 PIDM join or the RCRAPP1 to RCRAPP2 AIDY CODE join S O SunGard 2004 2005 ELECT D ST FROM HER T z Z NCT ROVST96 PI ROVST96 RCRAPP2 ROVST96 STYP CODI ROVST96 STST COD ROVS
2. SunGard 2004 2005 Financial Aid Technical Page G 1 Letter Generation Overview Description Letter Generation allows you to extract data from the SCT Banner database and merge that data with text then print the results and log what letter was created and for whom the letter was created Letter Generation allows you to extract data from SCT Banner based on a given Population Selection merge extracted data with text print the results and maintain a log of printed letters How does Letter Letter Generation extracts specific data from the PIDMs which are extracted Generation during a Population Selection work SELECT spriden first name spriden last name FROM spriden WHERE pop sel criteria Next it merges the extracted data with paragraphs customized for your implementation O SunGard 2004 2005 Financial Aid Technical Page G 2 Example Letter Example April 20 2000 John Doe 123 Street Malvern PA 19355 Heading Dear John The University is in the process of verifying student information in order to prepare for registration using SCT Banner WEB Please verify b that the following information 1s current and correct ID 123456789 New Birth date 01 01 80 Paragraph Ifthe information is incorrect notify our office immediately Thank you for your assistance Sincerely O SunGard 2004 2005 Financial Aid Technical Page G 3 Steps to Create a Letter Steps e SunGard 2004 2005
3. CL Gk 0 53 constraint name format a30 column name format a25 ctype format a22 head Con linesize 130 all cons columns constrain all cons columns column n all cons columns position decode all constraints constraint type from Referencial Integrity Null Check P Primary U Unique V With Che O With Read Only ctyp all constraints status cons columns a cons where and and order self sel generating sql Se E spool select wher ike upper sTableNames 1 cons columns constrain constraints constraint VOS HO OO all constraints table name by table name all constra position head off echo off on cons columns column name word wrap straint Type t name ame ES S Gt Not Key ck Option e cons columns table name traints t name name cons columns table name ints constraint name At prompt enter name or partial name of a table then enter part of a column name ie code select distinct column name from table name from dba tab columns table name like upper amp tables and column name like upper amp column E O spool pause edit o pause O SunGard 2004 2005 Press any key to continue n lst Don t forget to set head on and
4. GJAJOBS Process Maintenance Form GIAPCTL Process Submission Controls Form GJAPDEF Parameter Definitions Form GJAPDFT Default Parameter Value Validations Form GJAPVAL Parameter Value Validations Form GJARSLT Process Results Form GLAEXTR Population Selection Extract Data Form GLIAPPL Application Inquiry Form GLIEXTR Population Selection Extract Data Inquiry Form GLROBIT Object Definition Rules Form ____________ GLRVRBL Variable Definition Rules Form GTVLETR Letter Code Validation Form GTVPARA Paragraph Code Validation Form GTVPRNT Printer Validation Form ooo GUAINST Installation Control Form GUALETR LetterProcessFom GUAPARA Letter Generation Paragraph Form RBAABUD ApplicantBudgetForm 333 RCRINFR Data Source Code Validation Form RCRNAME First Name Translation Rules Form RCRTPTR Interface Data Translation Rules Form REACORR SIR Correction Reguest Form READIxx Year specific Pell Disbursement Acknowledgement Form READRCT EDE Directory Form 0 O O e RENI Origination Acknowledgement Form eo Form e vM Form Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page L 2 Forms Job Aid Continued Introduction continued Form Description Form Form Schedule Rules
5. SUNGARD SCT HIGHER MAH EE 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 O 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 the SCT logo and Banner Campus Pipeline Luminis Power CAMPUS 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 names are trademarks or registered trademarks of their respective holders Table of C
6. COD Extract Process REREXxx Send Disbursement File to COD O SunGard 2004 2005 REREXxx Updates REICODD Batches records for Updated with batch information transmission to COD READIxx Disbursement record is updated Status updated to S sent REASTID Transmit Disbursement If first COD transaction Student Change file name from Identifier record is created and rerexxx nnnm xml to sent COMRECIN Creates and extracts COD disbursement records for all Pell records where there is a payment and the COD disbursement record has not been sent Will not create extract until the Student Identifier is acknowledged Updates e Disbursement Acknowledgement Form READIxx e Student Identifier Record REASTID e COD Document Control REICODD Access FTP software Move rerex04 nnnnn xml file from jobsub to a local PC Rename rerex04 nnnnn xml file to COMRECIN Upload COMRECIN file to COD website Financial Aid Technical Page D 44 Pell Disbursement Import Diagram SunGard 2004 2005 Receive Acknowledgment Receive acknowledgement file COMRECOP RERIMxx Import Disbursement Acknowledement file into Banner Output includes list of all corrected and rejected records Page D 45 4 ny e ear E D a REICODD Updated with batch information REASTID Student identifier record updated READIxx Disbursement Acknowledgment fields updated REAORxx
7. RPRCONT Third Party Contract Rules Table Do S RPRFUND Packaging Fund Total Table Do RORAUDT 60 OVER _ Audit Grading Mode Table ee SAP Translation Rules Table Naravni i RORSTRF H amp T Prior Institution Transcript Base Covered with NSLDS Table O L O Batch Type Validation Table defines Delivered the batch types that are available in Direct Lending one type for each kind of record sent to or received by the servicer RTVDLBT LEA p ie A RE Es Es E ES ES Ea E x x X X X X X X X X X X X X L CommonLine Loan Application Phase Delivered Validation Table VER Program Code validation table for Delivered NSLDS OVER Batch Posting Type Validation Table OVER Reject Validation Table RORPOST Exclude by Section Table OTHER Batch Posting Rule Table Cannot be moved until pop sels are moved Short Term Table ESSE Continued on the next page gt RPBSTAB O SunGard 2004 2005 Financial Aid Technical Page K 5 Financial Aid Rule and Validation Tables for Migration from PPRD to PROD January 5 2005 Continued Parameter Sets Population Selections Letter Generation and Job Submission Parameter Sets Population Selections Table Name Import Rule Area Description Comments Order Validation Table GLBSLCT Application Code is FINAID GLRSERM 0X E O go GLRSLCTT X TPS Variables Table Name Import Rule Area Descrip
8. gt RTVAWST Group4 X_ JA Award Status Validation Table RTVBTYP Group4 X JB Budget Type Validation Table RTVCINT Group4 X JL Certification Initials Validation Table RTVCOMP Group4 X JB Budget Component Validation Table RTVFSRC Group4 X F Fund Source Validation Table RTVETYP F FundType Validation Table RTVHOLD Group 4 HOLDS Financial Aid Hold Type Validation Table RTVICMT Group 4 OVER ISIR Comment Code Text Validation Table RTVLNST Loan Status Validation Table x MESG RORCLVE Group3 X OVER Course Student Level Exclusion pat PAE RTVMESG MESG Message Validation Table K RTVRFST Referral Status Validation Table K USER User Defined Variable Description Table Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page K 2 Financial Aid Rule and Validation Tables for Migration from PPRD to PROD January 5 2005 Continued Table Name Import Rule Description Comments Order Validation Table RORDATA 1 OVER Data Dictionary Table RPRFEDR 3 OVER Federal Rules Table RFRMESG 4 F Fund Message Assignment Rules Table RBRGMSG x X X X X Budget Group Message Rules Table RRRTMSG RACK Tracking Requirement Message Rules Table RFRBCMP 7 Fund Budget Component Table RFRTREQ Fund Requirements Assignment RFRBASE Rules Table RCRAIDY Fund Base Data Table RCRTERM OVER Telephone
9. 2004 2005 Financial Aid Technical Page H 13 Cleaning Database Cleaning Review cln finaid 7x sgl database O SunGard 2004 2005 Financial Aid Technical Page H 14 Migration to Production Migration to e Review the Migration to Production table in Section K Table Indexes production e Non supported data client needs to review their database and SCT Banner installation O SunGard 2004 2005 Financial Aid Technical Page H 15 Section Troubleshooting Overview In this section These topics are covered in this section Building Online Group Assignment Rules Using Outer Joins Tips for Financial Aid Rules on Forms Hints on Improving Performance of Rules Created on 1 5 RORRULE SunGard 2004 2005 Financial Aid Technical Page I 1 Building Online Group Assignment Rules Using Outer Joins Incorrectrule The following budgeting assignment rule would not function as intended if you were attempting to select a person into a particular budget group with the following characteristics e The person has a BANNER housing code that indicates the person is a dormitory resident and e The person is identified as an undergraduate in either the general student record or admission record RCRAPP1 CURR REC IND Y AND CRAPPl INST HOUS CDE 2 AND ROVST95 LEVL CODE UG OR ROVAD95 LEVL CODE UG ux Hm Explanation The SCT views to the Student System the Student View with Ef
10. Aid is then and billing applied to student accounts Reporting e Federal Pell Grant Report e FISAP Fiscal Operations Report e State Grant Reporting O SunGard 2004 2005 Financial Aid Technical Page D 5 Financial Aid System Process Flow Diagram Applicant Record Need Analysis Requirements Creation Verification Tracking Student System Shared Data Packaging Budgeting Dishursements Award History Funds Student Transcripts Management Employment 1 Common Functions Financial Aid TRM Supplement pages 1 2 and 1 3 12 of 156 O SunGard 2004 2005 Financial Aid Technical Page D 6 Applicant Record Creation Applicant Financial Aid applicant records can be created through several processes record creation including MDE tape loads Pell Electronic Data Exchange and manual creation using Record Creation Need Analysis forms such as RRAAREQ RNANAxx Applicant record creation forms Seven character Proper name of form process report or table name Admissions Application Inquiry Form Prior College Inquiry Form Test Score Inquiry Form O SunGard 2004 2005 Financial Aid Technical Page D 7 Applicant Record Creation Data Load Procedures Procedures e Load electronic media from external sources e Stored in temporary ORACLE tables e By processes RCBCTxx CSS or RCBTPxx EDE e Used by the RCPMTCH process e RCRTPxx process completes load For more information on these procedures refer to Chapter 2 of the SCT Ban
11. DataLogInquiryForm ROISAPR SAP Code InquiryForm ROISARI SAR ID InquiryForm RORRULE Financial Aid Selection Rules Form RPAAPMT Package Maintenance Form RPAARSC ResourceFom RPAAWRD AwardFom RPAIDEN Financial Aid Identification Form Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page L 4 Forms Job Aid Continued Introduction continued RPAMACC Award Acceptance Mass Entry Form RPAMOFF Award Offer Mass Entry Form RPIARPY Contracts and Exemptions Payment Inquiry Form Applicant Requirements Form Applicant Requirements Mass Entry Form Requirements Tracking Group Requirements Rules Form Requirements Tracking Code Message Rules Form Third Party Contract Inquiry Form Degrees and Other Formal Awards Inquiry Form Inquiry Form RSIENRL Financial Aid Enrollment Inquiry Form RSIEXPT Billing Exemption Inquiry Form RSIHIST Summary Academic History Inquiry Form RSIHOUS Housing Information Inquiry Form RSIHSCH High School Inquiry Form RSIINTL International Student Inquiry Form RTVFTYP Financial Aid Fund Type Validation Form RSIPCOL Prior College Inquiry Form Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page L 5 Forms Job Aid Continued Introduction continued RTVHOLD Hold Type Validation Form RTVRJCT Rejection Code Validation Form RTVTGRP Requirements Tracking Group Validation For
12. Define variables to be used Construct paragraphs Combine paragraphs into letters Extract population Extract letter variables Print letter or create merge file Page G 4 Financial Aid Technical Step 1 Define Variables 1 Paragraph April 20 2000 DATE1 John Doe FUNC_PERSON_NAME 123 Street FUNC PERSON ADDRESS LINE1 LINE2 LINE3 Malvern PA 19355 FUNC PERSON CITY STATE ZIP Dear John FUNC PERSON SALUTATION GLRVRBL Variable Rules Definition table GLRVRBL e Builds selection criteria for data element e Owned by an application e Must have data associated with an ID 2 paragraph The University is in the process of verifying student information in order to prepare for registration using SCT Banner WEB Please verify that the following information is current and correct ID 123456789 ID Birth date 01 01 80 BIRTH DAT El If the information is incorrect notify our office immediately Thank you for your assistance Sincerely Compilation When the form is exited the Parameter Selection Process GLBPARM compiles the SELECT statement O SunGard 2004 2005 Financial Aid Technical Page G 5 Step 2 Construct Paragraphs GTVPARA Paragraph Code Validation Form GTVPARA e Define codes and descriptions for paragraphs GUAPARA Letter Generation Paragraph Form GUAPARA e Define the contents of each paragraph e Paragraphs contain variables formatting commands and text e
13. Disbursement fields updated Financial Aid Technical Data Request Extract Requesting It is better to only load the MRR records the COD sends to you and not request MRR records MRR records It is highly recommended that discretion be used when requesting MRR records For example never request all Origination Records for all your students or all institutions There are many schools that originate for every ISIR that they receive if you request records in this way you may receive thousands of records O SunGard 2004 2005 Financial Aid Technical Page D 46 Data Request Import Import files into Move files from COD Banner to dataload directory I I sa MN ON EU Na s Vi ita r E ia X EO a alas Creates RERRDRQ REISSOA REIYTDS REIRVRR RERRECN Acknowledgement Updated with Updated with year Updated with data Table fields updated Summary Statement to date summary from MRR file of Account data data REIDSOA Updated with detail statement of account data RFMS Import RFMS Import Process RERFIxx loads the following records into Banner Process e Data Request Acknowledgement PGRAxxOP RERFIxx e Year to Date Records PGYRxxOP e Statements of Account PGASxxOP e Multiple Reporting Records PGMRxxOP e Reconciliation Files PGRCxxOP O SunGard 2004 2005 Financial Aid Technical Page D 47 Reports and Inquiries Pell RFMS Used to help identify exception processing requirements Miscellaneous Available data in
14. For Job Submission dates use DD MON YYYY format on the Process Submission Controls Form GJAPCTL O SunGard 2004 2005 Financial Aid Technical Page B 20 PIDMs What is a A Primary Identification Master or PIDM uniquely identifies the records of PIDM one person or non person entity in SCT Banner It is an internal key field used to identify and store records and is assigned automatically when new records are created A PIDM is generated from an Oracle Sequence called PIDM SEQUENCE through a function named F GEN PIDM O SunGard 2004 2005 Financial Aid Technical Page B 21 SOBSEQN Purpose This common overall base table is used to store one up numbers for various entities It should have maintenance access set at the highest security level Contents of select from sobseqn SOBSEQN SOBSEQN FUNCTION S SOBSEQN MAXSEQNO SOBSEQN A RECEIPT 203 22 FEB 05 ID 18 19 SEP 96 PIDM 571 07 JAN 05 ALUMNIGIFT 108 21 FEB 05 ALUMNI PLEDGE 44 15 FEB 05 EDIREQUESTID 1 25 APR 95 EDI DCMT SEONO 1 08 DEC 95 ALUMNIDUES 3 15 FEB 05 ALUMNIRECEIPT 1 31 JAN 96 EVENT A 4 15 FEB 05 HRREQ R 1 23 NOV 04 PROSPECT ID P 0 04 JUN 03 HRENCD96 10004 18 NOV 04 HRENCDO5 10003 17 FEB 05 O SunGard 2004 2005 Financial Aid Technical Page B 22 PIDMs and SOBSEQN Using How to get the next available PIDM SOBSEQN e Select pidm sequence currval from DUAL e Select pidm
15. Form Schedule Rules Form RHIAFSH Fund Sequence History Inquiry Form RHRTRNS Transcript Rules Form RJASEAR Student Employment Authorization Form RJASEME Student Employment Mass Entry Form RJASERF Student Employment Referral Form RJISEWH Student Employment Work History Form RNANAxx Year specific Need Analysis Form RNAOVxx Year specific Applicant Override RNAPRxx Year specific Need Analysis NE LL AN Processing Form RNASLxx Year specific Student Loan Data System Form Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page L 3 Forms Job Aid Continued Introduction continued A o Supplemental Form RNA VRxx Year specific Need Analysis EE TE mv om Inguiry Form ee Inquiry Form RNINAIQ Calculated Need Analysis Detail Inquiry o Sds X RNRGLxx INAS Global Policy Options Rules Do eee om ROAALOG Applicant Data Log Application Form ROAFSAP FISAPPerson Maintenance Form ROAIDEN Person Name ID Search Form ROAIMMP Applicant Immediate Process Form ROAINST Institutional Financial Aid Options Form ROAMGMT Funds Management Form PE ROAPELL Applicant Pell Grant Form Do ROAPERS Financial Aid General Person Form Do ROARMAN Financial Aid Record Maintenance Form ROASTAT Applicant Status Form O ROIAIDY Aid Year Inquiry Form ROIALOG Applicant Data Log Inguiry Form ROIDISB Disbursement ResultsForm ROHLOG
16. LOCK IND LL VARCHAR2 1 This field indicates whether a lock exists on the student s award package RORSTAT PGRP PROC IND NO LL VARCHAR RORSTAT PIDM NUMBER 8 0 Match to The internal system student SPRIDEN identification number T NU T NULL RORSTAT TGRP PROC IND NOTNULL VARCHAR2 T NULL T NULL NO O RORSTAT USER ID M Le RARE The logon ID of the user who entered or last updated the record in this table RORSTAT VER COMPLETE 1 N for all This field specifies whether history years the verification has been completed Indicates if an applicant is eligible for additional Stafford awards The date on which all tracking requirements for this applicant were satisfied RORSTAT ADDL STFD ELIG IND VARCHAR2 DATE RORSTAT APPL RCVD_DATE DATE institutional option 7 7 RORSTAT APRD CODE VARCHAR 6 RTVAPRD The Aid Period to which the applicant is currently assigned 7 ESSA Continued on the next page RORSTAT ALL REQ COMP DATE The date on which the applicant s Financial Aid application was received process or created depending on the RORSTAT AUTH DISB AID DATE DATE Do SunGard 2004 2005 Financial Aid Technical Page K 7 Applicant Status Table Base Financial Aid Table RORSTAT Continued Data Type Length Validation RORSTAT AUTH DISB AID TERM VARCHAR2 m u ZEN RORSTAT BGRP CODE EBEN VARCHAR2 RTVBGRP E B d NN d ME
17. Paragraphs for merge files contain only variables O SunGard 2004 2005 Financial Aid Technical Page G 6 Step 3 Combine Paragraphs GTVLETR GUALETR Example letter 1 paragraph Example letter 2 paragraph Letter Code Validation Form GTVLETR e Define codes and descriptions for letters Letter Process Form GUALETR e Combine paragraphs into a letter DATE1 FUNC PERSON NAMI E LH E FUNC PERSON ADDRESS LINE1 LINE2 LINE3 E RSON_CITY STATE ZIP FUNC_ PI nl Dear FUNC PERSON SALUTATION X SHE x nl The University is in the process of verifying student information in nl order to prepare for registration using SCT Banner WEB Please nl verify that the following information is current and correct nl nl ID nl ID nl Birth date nl BIRTH DATE nl If the information is incorrect notify our office immediately nl Thank you for your assistance nl nl Sincerely Continued on the next page SunGard 2004 2005 Financial Aid Technical Page G 7 Step 3 Combine Paragraphs Continued Example letter DATE1 merge file FUNC PERSON NAME FUNC PERSON ADDRESS LINE1 LINE2 LINES FUNC PERSON CITY STATE ZIP FUNC PERSON SALUTATION SED BIRTH DATE O SunGard 2004 2005 Financial
18. Proper name of form process report or table name Seven character Proper name of form process report or table name RFRBASE Fund Base Data Table Major tables RFRBCMP Fund Budget Component Table RFRFFID Federal Fund ID Table Processes and There are no processes in Funds Management other than procedures for the procedures functional staff to follow They may need assistance with RORRULEs O SunGard 2004 2005 Financial Aid Technical Page D 29 Funds Management Process Flow Diagram Create Fund Standard Aid Year Award and Specific Data Dishursement Schedules Create Fund Std Award No Specific Award Sched a Schedule Create Fund Specific Disbursement Schedule Create Fund Disbursement Rules O SunGard 2004 2005 Financial Aid Technical Page D 30 Packaging and Disbursement Forms and Tables Packaging and The Packaging Disbursements processes match an applicant s need with funds disbursement determine an appropriate package for the applicant and record the disbursement of the funds Seven character Proper name of form process report or table name Seven character Proper name of form process report or table name RPRAWRD Applicant Award table Major tables RPRATRM Applicant Award by Term table RPRLAPP Loan Application table O SunGard 2004 2005 Financial Aid Technical Page D 31 Packaging Process Flow Diagram Financial Aid Application Current Record Packaging
19. cope RCRAPP4 AIDY CODE PIDM PIDM INFC CODE AIDY CODE sli SEQ_NO PIDM INFC CODE SEQ NO RCRIMPI ROVNEED AIDY CODE AIDY CODE AIDY CODE AIDY CODE DM PIDM PIDM PIDM aD e INFC CODE INFC CODE INFC CODE INFC CODE SEQ NO SEQ NO SEQ NO SEQ NO RNROYRD RCRESAR RCRAPP2 RNRACMT RCRAPP3 AIDY CODE PIDM 2 RNROCMT RCRINFR RNRTPTR RCVAPPL Banner2000 Financial Aid Supplement page 2 6 60 of 156 September 1999 O SunGard 2004 2005 Financial Aid Technical Page D 13 Need Analysis Online Recalculation Process Flow Diagram Financial Aid TRM Supplement pages 1 14 24 of 156 O SunGard 2004 2005 INAS Long Calculation Pell Award Calculation Financial Aid Application Current Record Page D 14 INAS Short Calculation Pell Award Calculation INAS Short Results Financial Aid Technical Requirement Tracking Forms and Tables Forms The Requirements Tracking module monitors the request for and receipt of certain documents Financial Aid applicants are often required to provide so that their applications can be fully processed Seven character Proper name of form process report or table name RRAAREQ Applicant Requirements Form ROARMAN Financial Aid Record Maintenance Form RRAMASS Applicant Requirements Mass Entry Form RHACOMM Applicant Comments Form ROAIMMP Applicant Immediate Process Form Seven character Proper name of form process report or table name RRRGREQ Tra
20. for these objects Note Keys are implemented as constraints A candidate key selected as a unique entity identifier which enforces unique non null keys PK ppppppp is the SCT Banner convention for primary keys where e PK stands for Primary key e ppppppp is the Primary key table name Example PK STVTERM e Alter Term Code Validation table STVTERM e Add constraint PK STVTERM e Primary key column name An attribute in one table whose values must match the primary key in another table or whose values must be null A table can have any number of foreign keys Usage e Referencing validation tables e Application hierarchy Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page B 13 Referential Integrity Continued Foreign key Validation tables validation FK n fffffff INV ppppppp code is the SCT Banner convention for Foreign keys where e FK stands for Foreign key e nisaone up number which distinguishes potential duplicate Foreign key names in a given table e fffffff is the Foreign key table name e ppppppp is the Primary key table name Example FK3 RPRATRM INV STVTERM CODE e Alter Applicant Award by Term table RPRATRM e Addconstraint FK3 RPRATRM INV STVTERM CODE e Foreign key sfrstcr code e References STVTERM STVTERM CODE Foreign key Application hierarchy hierarchy e FK luptt foreign tablet INV Primary table KEY e FK3 ROBUS
21. page O SunGard 2004 2005 Financial Aid Technical Page I 5 Hints on Improving Performance of Rules Created on RORRULE Continued Rule created on When the rule is created on RORRULE as RORRULE Compile order O SunGard 2004 2005 RCRAPP1 VERIF CAT RCRAPP2 MODEL CD ROVST95 STYP CODI ROVST95 STST COD ROVST95 LEVL CODI ON MSG 1 AND E D AND E 9 AND E IN AS SA AND Boc UG The rule is compiled by ROOGSQL in the following order SELECT DISTINCT RCRAPP1 PIDM FROM RCRAPP1 RCRAPP2 ROVST96 HERE RCRAPP1 VERIFICATION MSG 1 AND RCRAPP2 MODEL CDE D AND ROVST96 STYP CODE 9 AND ROVST96 STST CODE IN AS SA AND ROVST96 LEVL CODE UG AND RCRAPP2 PIDM RCRAPP1 PIDM AND ROVST96 PIDM RCRAPP1 PIDM AND RCRAPP2 INFC CODE RCRAPP1 INFC CODE AND RCRAPP2 SEO NO RCRAPP1 SEQ NO AND RCRAPP1 CURR REC IND Y AND RCRAPP2 AIDY CODE RCRAPP1 AIDY CODE AND ROVST96 AIDY CODE RCRAPP1 AIDY CODE AND RCRAPP1 AIDY CODE AIDY AND RCRAPP1 PIDM PIDM Page I 6 Continued on the next page Financial Aid Technical Hints on Improving Performance of Rules Created on RORRULE Continued RCRAPP1_ CURR_REC_ IND added Compile order When RCRAPP1 CURR REC IND i
22. page 2 28 82 of 156 Financial Aid TRM Supplement page 2 30 84 of 156 O SunGard 2004 2005 Financial Aid Technical Page D 22 Student System Shared Data Processes and Procedures RSRENRL Financial Aid Enrollment Hours Process RSRENRL e Add credit and billing hours from student enrollment to Financial Aid e C process used to capture or freeze hours O SunGard 2004 2005 Financial Aid Technical Page D 23 Pell Electronic Data Exchange Forms Pell EDE The Pell EDE program allows institutions to share information electronically with the Pell central processor Seven character Proper name of form process report or table name EDE Directory Form REAORxx Y ear specific Pell Origination Acknowledgement Form READIxx Y ear specific Pell Disbursement Acknowledgement Form O SunGard 2004 2005 Financial Aid Technical Page D 24 Pell Electronic Data Exchange Tables Seven character Proper name of form process report or table name RCRESAR Applicant ESAR REBDRCT EDE Directory REBPAYV Applicant Payment Voucher O SunGard 2004 2005 Financial Aid Technical Page D 25 ISIR Data Receipt Process Flow Diagram O SunGard 2004 2005 Student files FAFSA MDE FAFSA Central Processor Request for Data Institution using GES Software Page D 26 Institution Updates SCT Banner Financial Aid Technical EDE ISIR Data Correction Process Flow Diagram Resubmit Prior C
23. pidm and rpratrm pidm spbpers pidm and rpratrm fund code rfrbase fund code and spbpers dead ind is null and spriden change ind is null and spriden entity ind P and rpratrm term code like amp term code order by spriden id rpratrm aidy code rpratrm term code rpratrm fund code Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 71 Self Check Answer Key Continued Exercise 6 Write a script to return those students that have different EFCs Script should Database return student ID formatted name EFCs and indicate if record has a lock Structure Prompt the user for aid year set linesize 100 column column select from where and and and and and and and and order O SunGard 2004 2005 name format a30 head Student Name locked format a9 head Locked spriden id ID f format name spriden pidm LF30 Name rcvappl pell pgi SAR EFC rcvappl pgi Pell EFC rcvappl tfc SYS EFC rcvappl used trans no Trans rorstat lock ind Locked rcvappl rorstat spriden rcvappl pidm rorstat pidm rorstat pidm spriden pidm spriden change ind is null spriden entity ind P rcvappl aidy code rorstat aidy code rcvappl infc code EDE rcvappl aidy code amp Aid Year rcvappl curr rec ind Y rcvappl tfc rcvappl pgi or rcvappl tfc rcvappl pell pgi or rcvappl pgi lt gt rcvappl pell pgi by name Continued on the next page Financial Aid Technic
24. plan and get approval Develop procedures and programs Test Create database Perform conversion Verify and correct Test You will need your end users SunGard 2004 2005 Page H 5 Financial Aid Technical Financial Aid System Conversions Conversions e Persons e Students e Award history and transcripts e Tracking requirements Refer to Chapters 5 6 and 8 of the Financial Aid Technical Reference Manual O SunGard 2004 2005 Financial Aid Technical Page H 6 General Person Records Records e Identification table SPRIDEN Person ID required e Address table SPRADDR e Telephone Number table SPRTELE e Person table SPBPERS Basic person e Applicant Holds table SPRHOLD Holds SunGard 2004 2005 Financial Aid Technical Page H 7 General Student Records SGBSTDN Student Base table SGBSTDN Required Other student e data O SunGard 2004 2005 Residency Academic status Educational goal Assistantship Fellowship Internship Educational opportunity Student disability Veteran status Sports information Page H 8 Financial Aid Technical Tracking Requirements ROBINST Financial Aid Institution table ROBINST e Create one record for each aid year that you load e Enter data manually through the Financial Aid Institution Options Form ROAINST RTVTRST Tracking Requirements Status Validation table RTVTRST e All tracking requirement status codes must exist before you can load his
25. 0 word wrap column name format a25 ctype format a22 head Constraint Type linesize 130 all cons columns constraint name all cons columns column name all cons columns position all constraints constraint type R Referencial Integrity C Not Null Check P Primary Key U Unique V With Check Option O With Read Only ctype all constraints status all cons columns table name all cons columns all constraints all cons columns column name like upper amp TableName all cons columns constraint name all constraints constraint name all cons columns table name all constraints table name by table name all constraints constraint name position Exercise 4 Write a simple query to get ID first name middle name last name and the General Person Change indicator for persons who have had changes made to their ID on the Identification Form SPAIDEN records SELECT FROM WHERE AND O SunGard 2004 2005 spriden id spriden first name spriden mi spriden last name spriden change ind spriden spriden change ind I spriden entity ind P Continued on the next page Financial Aid Technical Page B 28 Self Check Answer Key Continued Exercise 5 Write a simple query to get PIDM ID first name middle initial last name and Social Security number for persons who have had changes made to their name records select spriden pidm spriden id spriden first name spriden mi spride
26. 06 Budget Defaults RTVAPRD Confirm Aid Periods for 0506 RTVBTYP Confirm 0506 Budget Types RBRCOMP Update Budgets for 0506 RTVBGRP Confirm 0506 Budget Groups RBRGTYTP Confirm 0506 Budget Types RORMESG Associate Budget Messages created on RTVMESG with Budget Groups RORRULE Create 0506 Budget Group Assignment Rules ROAINST RFRBASE Update FUNDS for the 0506 Aid Year RFRMGMT Build 0506 Aid year Specific Data and Rules for Each Fund You Intend to Use Initial Page of RFRMGMT Form does NOT roll RFRDEFA Create Term Award and Disbursement Schedules for 0506 by Aid Period RFRASCH Create OPTIONAL Term Award and Disbursement Schedules for the 0506 Aid Year RORRULE Create OPTIONAL Fund Awarding Rules for 0506 by Fund Code Continued on the next page Financial Aid Technical Page D 59 New Year Roll Forms Tables and Modules Continued Packaging and e disbursement Loan module e EDE module e O SunGard 2004 2005 ROAINST Confirm that Full Time Half Time 4 Time Values are associated with EACH STVTERM Code and EACH STVLEVL code for 0506 pg 4 RPROPTS Create 0506 Packaging and Disbursement Options RTVPGRP Confirm 0506 Packaging Groups RPRGFND Associate 0506 Packaging Groups with Funds indicating the Priority Order and Respective Minimum and Maximum A wards RPRCLSS Create Class Code Translations for Disbursements RORRULE Create Packaging Group Assignment Rules for 0506 if using Aut
27. 11 2 This field represents the gross need of the applicant defined as budget minus TFC minus resources RORSTAT IM BATCH LOCK VARCHAR2 1 Indicates whether the IM data is locked from any updating by the batch tape load process RORSTAT IM GRS NEED NUMBER 11 2 Gross need based on institutional methodology RORSTAT IM LOCK VARCHAR2 1 Indicates whether the IM data is locked from any type of update RORSTAT IM TFC NUMBER 6 0 Total Family Contribution based on institutional methodology RORSTAT IM UNMET NEED NUMBER 11 2 Unmet need based on institutional methodology RORSTAT INAS OPTION VARCHAR2 IO RORSTAT_INFO_ACCESS_IND VARCHAR2 1 A Y or a NULL in this field indicates that the award information for this applicant may be accessed via Voice Response Banner Web and similar methods RORSTAT INST PC LOCK IND VARCHAR2 1 This field indicates if a lock exists on the institutional methodology parent contribution RORSTAT INST SC LOCK IND VARCHAR2 1 This field indicates if a lock exists on the institutional methodology student contribution RORSTAT LAST CYCLE ELIGIBLE VARCHAR2 1 The last cycle that a student was eligible to receive Pell 1 8788 2 8889 3 8990 4 9091 5 9192 6 9293 RORSTAT MAX YRS ELIG NUMBER jao RORSTAT MEMO REQ COMP DATE IDATE RORSTAT NEED DATE DATE o og O RORSTAT NO YRS USED NUMBER 42 Continued on the next page
28. 4 2005 Section A Introduction At the end of this workbook participants will be able to identify SCT Banner Financial Aid forms and tables query the SCT Banner Financial Aid Technical tables follow key Financial Aid Technical processes identify and read reports processes procedures and scripts in SCT Banner identify the SCT Banner Financial Aid naming conventions form types directories and their contents database structure processes and procedures follow the basics of how Job Submission Population Selection and Letter Generation work The target audience for this training course is the Technical staff power users and Financial Aid managers who may use or teach others about SCT Banner Financial Aid Technical tables and processes To complete this workbook you should have completed the SCT Education Practices computer based training CBT tutorial Banner 6 x Fundamentals Forms and Navigation or have equivalent experience navigating in the Banner system basic knowledge of Oracle These topics are covered in this section Tepe Page A 2 Financial Aid Technical Page A 1 Introduction Topics The attendees will receive a lecture and demonstration that describes the SCT Banner Financial Aid Technical module Topics include SCT services SCT Banner naming conventions data directories and their contents database structures Job Submission Population Selection Letter Generation convers
29. Aid Technical Page G 8 Step 4 Extract a Population Extraction A population must have been extracted prior to the next few steps in Letter requirement Generation O SunGard 2004 2005 Financial Aid Technical Page G 9 Step 5 Extract Letter Variables GLBLSEL Run the Letter Extract Process GLBLSEL from Job Submission This process extracts data from the database for selected PIDMS and stores it in a collector table O SunGard 2004 2005 Financial Aid Technical Page G 10 Step 6 Print the Letter GLRLETR Submit the Letter Generation Print Report GLRLETR through the Process Submission Controls Form GJAPCTL e Removes the selected variables from the Collector table e Merges that data with text specified in a letter e Prints the letter or creates the appropriate word processing file O SunGard 2004 2005 Financial Aid Technical Page G 11 Creating a Letter Required forms Application Definition Rules Form GLRAPPL e Create an application only once Object Definition Rules Form GLROBJT e Created once per object Letter Code Validation Form GTVLETR e Needed for every letter Variable Definition Rules Form GLRVRBL e Every variable has to be created only once Paragraph Code Validation Form GTVPARA e Needed for every paragraph Letter Generation Paragraph Form GUAPARA e Details needed for each paragraph Can be used for multiple letters in the same module O SunGard 2004 2005 Financial Aid Technical Pag
30. Check Answer Key Continued Exercise 9 Modify your script from the previous exercise to select students where they Database have any awards paid for aid year Structure Advanced set pagesize 60 set linesize 80 rem column ID format a9 column NAME format a30 column EFC format 99 990 column UNMET NEED format 99 990 column WORK format a6 column PAID AMT format 99 990 rem spool AWARDS lis rem select spriden id ID spriden last name Ispriden first name NAME rorstat pri sar pgi EFC rorstat unmet need UNMET NEED rcrapp4 work study WORK sum rprawrd paid amt PAID AMT from spriden spbpers rprawrd rorstat rcrapp rcrappl where rcrappl aidy code amp aidy code and rcrappl curr rec ind Y and rcrappl pidm rcrapp4 pidm and rcrappl infc code rcrapp4 infc code and rcrappl seg no rcrapp4 seg no and rcrappl aidy code rcrapp4 aidy code and rcrappl pidm rorstat pidm and rcrappl aidy code rorstat aidy code and rcrappl pidm rprawrd pidm and rcrappl pidm spbpers pidm and rcrappl pidm spriden pidm and spbpers dead ind is null and spriden change ind is null and spriden entity ind P and rprawrd paid amt lt gt 0 group by spriden id spriden last name spriden first name rorstat pri sar pgi rorstat unmet need rcrapp4 work study order by 2 spool off O SunGard 2004 2005 Financial Aid Technical Page D 75 Section E Job Submission Overview In this section These
31. Considerations Three very important considerations e Do NOT copy or manually enter rules for a module on the RORRULE form for 0506 before running the ROPROLL process e Do NOT copy or manually enter 0506 data for a module that you wish to automatically roll to the 0506 aid year prior to running ROPROLL e Wait to edit and or manually update the 0506 year specific data you want to automatically roll until AFTER the ROPROLL process has been performed O SunGard 2004 2005 Financial Aid Technical Page D 56 New Year Roll ROPROLL ROPROLL What data do you want to roll automatically decisions Parameter Value 01 From Aid Year Code ex 0405 02 To Aid Year Code ex 0506 03 Perform roll of Group Rules Y or N 04 Perform roll of FUND data Y or N 05 Perform roll of BUDGET data Y or N 06 Perform roll of TRACKING data Y or N 07 Perform roll of PACKAGING data Y or N 08 Perform roll of COMMON data Y or N 09 Perform roll of EMPLYMENT data Y or N ROPROLL Process Rom Fan PaameterSe fr Printer Control Printer fd Special Print Lines 55 Submit Time Parameter Values Parameters Values El E pi fas ha Fri te pa ertor Dds V los Y los peri T vo Submission Save Parameter Set as Name Description Hold Submit m O SunGard 2004 2005 Financial Aid Technical Page D 57 New Year Roll Forms Tables and Modules Common ROAI
32. DF INV RORSTAT KEY STVTERM Before a record may be added to RPRATRM it must exist in RORSTAT Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page B 14 Referential Integrity Continued Parent and child The Child row has the foreign key constraint and the Parent row has the rows primary key constraint The Parent row cannot be deleted when a Child row exists SIVATYP CODE STVATYP DESC Address validation table SPRADDR PIDM SPRADDR ATYP CODE Parent Address repeating table Child PR Primary key Permanent Address SIVATYP_CODE Address validation table P 123456789 Parent Foreign key SPRADDR ATYP CODE Address repeating table Child SunGard 2004 2005 Financial Aid Technical Page B 15 Data Standardization Components e SunGard 2004 2005 IDs Names Addresses Dates Page B 16 Financial Aid Technical Identification Numbers Persons Enter an identification number of your choice or use the identification number that the System generates on any identification form xPAIDEN Non persons Use the System generated identification number O SunGard 2004 2005 Financial Aid Technical Page B 17 Names Basic format Prefix First Name Last Name Suffix Mr William Smith Jones II Case Use conventional upper and lower case letters Last name Do not enter spaces within prefixed last names considerations e MacArthur O Connor VanHusen o St Jo
33. Financial Aid Award Year RPRCLSS HH 1 Table defines the award years that are available for telephone E 22 23 24 ml mm d gt E o e X defines the terms that are available for telephone OVER Class Code Translation Table l OVER Campus Undergraduate Level Code Rules Table Telephone Financial Aid Term Table RORINID OVER Financial Aid College Codes Table Budget Group Budget Type Rules Table Budget Component Rules Table TRACK Tracking Group Requirements Rules Table Job Title Table Placement Base Data Table SE Student Employment Default Rules Table Payroll Load Rules Table Placement Rules Table RULES Group Selection Rules Base Table RULES Generated SQL Selection Identifier Table RULES Selection Identifier Compiled Statements RBRGTYP RBRCOMP RRRGREQ RJBJOBT RJBPLBD RJBSEDR 2 2 RJRPAYL 2 RJRPLRL 2 RORGDAT 2 2 E OVER Enrollment Options by Term Table 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 nin nin Continued on the next page SunGard 2004 2005 Financial Aid Technical Page K 3 Financial Aid Rule and Validation Tables for Migration from PPRD to PROD January 5 2005 Continued Table Name Import Rule Area Description Comments Order Validation Table RCRINER 27 X EDE Tape Matching Rules Table notusedin7X RCRTPTR 28 X JEDE Financial Aid Tape Translation Table RCRNAME x JEDE First Name Translation Rul
34. Form Billing Exemption Inquiry Form O SunGard 2004 2005 Financial Aid Technical Page D 19 Student System Shared Data Tables and Views Seven character Proper name of form process report or table name RORENRL Enrollment Status Financial Aid Captured data ROVSTDN Basic Student Information ROVADAP ROVADxx ROVDGMR O SunGard 2004 2005 Financial Aid Technical Page D 20 Student System Shared Data Process Flow Diagram ROVDGMR ROVINTL ROVVETN ROVADAP CMM AX a ZE PIDM PIDM PIDM PIDM SEQ NO VTYP CODE CURRENT TERM CODE V TERM CODE ENTRY z VETC CODE APPL NO ROVMASG PIDM ROVSTDN PIDM ROVPCOL N TERM CODE TRIT SEQ NO MRCD CODE PIDM BLDG CODE PIDM PIDM PIDM ROOM NUMBER TERM CODE TESC CODE SBGI CODE TERM CODE RDEF CODE ROVRASG ROVPREQ ROVTEST ROVHSCH ROVSTDD ROVSTU2 ROVSTU3 RPVADDR RPVADRS 5 pee Financial Aid TRM Supplement page 2 26 80 of 156 O SunGard 2004 2005 Financial Aid Technical Page D 21 Student System Process Flow Diagram ROVHIST TERM CODE sto g T TERM CODE LEVL CODE EM CODE Tm TERM CODE LEVL CODE LEVL OODE TERM CODE LEVL CODE LEVL CODE ROVTRMG ROVIGPA ROVTGPA ROVOGPA ROVTHST N STU PIDM STU PIDM CONTRACT FIDM CONTRACT PIDM ROVCDTL gt CONTRACT NUMBER ROVCONT CONTRACT NUMBER lt ROVCSUM TERM_CODE TERM_CODE 6 ROVSTxx ROVADxx ROVSCRD PIDM PIDM EOVEDTL gt EXEMPTION CODE ROVEXPT EXEMPTION_CODE H ROVESUM T 6 Financial Aid TRM Supplement
35. N RORSTAT AWD LTR IND RORSTAT BORROWER BASED CDE RORSTAT CM PC LOCK IND RORSTAT CM SC LOCK IND m no o MM m di ME RORSTAT CM TFC LOCK IND This field indicates that an award letter should be generated for this student the next time letters are printed The student s assigned budget group This field indicates whether the student s loans need to be processed using a borrower based academic year rather than the standard award year This code is free form without validation This field indicates if a lock exists on the congressional methodology parental contribution This field indicates if a lock exists on the congressional methodology student contribution This field indicates if a lock exists on the congressional methodology total family contribution RORSTAT DISB DATE PATE TE A A RORSTAT DISB REQ COMP DATE NI B di ME E o AME a no SN RORSTAT FM BATCH LOCK RORSTAT FORMER HEAL IND The date that all disbursement requirements were met for the particular applicant Indicates whether the FM data is locked from any updating by the batch tape load process If this indicator is set to Y this student qualifies for additional Stafford Unsub loans in replacement of the HEAL Loan program Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page K 8 Applicant Status Table Base Financial Aid Table RORSTAT Continued Data Type RORSTAT GRS NEED NUMBER
36. NST functions forms RTVTREQ RTVPGRP and data RTVTRST STVLEVL STVTERM RORDATA RTVAPRD RORTPRD RTVSAPR RTVUSER RTVTGRP RORPOST RTVBGRP RCRINFR Record creation e ROAINST module e RCRINFR Create Data Source Matching Rules e RTVINFC Update Data Source Code e RCRTPTR Review for accuracy e RPROPTS Review Packaging and Disbursement options Need analysis e RPROPTS Check Packaging Fields for New Aid Year module e ROAINST Check for completion of 0506 Institutional Parameters e RORTPRD Update with 0506 Aid Periods and Terms e RFRBASE Review and Update Funds for 0506 e RFRMGMT Review Funds Management Forms for 0506 e RPRCLSS Review STVLEVL STVCLAS and Banner Financial Aid Codes Tracking ROAINST Review 0506 Tracking Parameters validation tables e RTVTGRP Review Tracking Group Validation Form and rules e RTVTREQ Review and Edit Requirements Tracking Validation Form for 0506 e RRRGREQ Update 0506 Tracking Group Requirements and Rules e RORRULE Create Group Assignment Rules for 0506 Using the Copy Function e RRRTMSG Associate any new messages for 0506 to Tracking Requirement Codes Continued on the next page SunGard 2004 2005 Financial Aid Technical Page D 58 New Year Roll Forms Tables and Modules Continued Budgeting e validation tables and rules e e e e e Funds e Management P validation tables and rules SunGard 2004 2005 ROAINST Create 05
37. O SunGard 2004 2005 Financial Aid Technical Page K 9 Applicant Status Table Base Financial Aid Table RORSTAT Continued Data Type RORSTAT NSLDS OVRD IND H B IU o a i i E Indicates which validation tests Packaging Disbursement processes should override for a spedific year student L limit tests aggregate D default tests O overpayment tests A all tests This field indicates the parents contribution toward educational expenses It is the primary parental contribution from the student s current record The date that the student s Financial Aid package was complete RORSTAT PCKG FUND DATE PATE UTIs Ss RORSTAT PCKG REQ COMP DATE m B a VARCHAR2 RORSTAT PELL DISB LOCK IND RORSTAT PELL LT HALF COA NUMBER 9 0 a M RORSTAT PELL ORIG IND RORSTAT PELL SCHED AWD NUMBER 5 0 VARCHAR2 RORSTAT PGI LOCK IND RORSTAT PGRP CODE VARCHAR2 6 RTVPGRP This field indicates the packaging group code This field indicates the date that all tracking requirements have been satisfied for packaging If Y indicates that all disbursement activity for Pell is prevented due to RFMS disb Reference numbers 91 99 Valid values Y blank Student s Pell Cost of Attendance if enrolled for less than half time Identifier that indicates whether an updated Pell Origination record is to be created automatically Valid values Y blank This field indicates the scheduled Pell Grant Award for
38. T96 LEVL CODI DM CRAPP1 ro AS SA UG N TO LI Fl V Z RCRAPP2 MO D ET T EL CDE Z VE Up RCRAPP1 RCRAPP1_C RCRAPP2_PI RCRAPP1_PI RCRAPP2 I RCRAPP2 SE z Z E SD UO AUCI 0 00 o DI Z z Z Z RCRAPP1 AI ROVST96 AI ROVST96 PI Z B A E D EL DE Z RCRAPP2 AIDY ICATION MSG EC IND Y ROVST96 PIDM ROVST96 PIDM CODE RCRAPP1 INFC CODI RCRAPP1 SEQ NO ROVST96 AIDY CODI ROVST96 AIDY CODI AIDY ZO il LH LH GI Ed Er E IDM Financial Aid Technical Page I 9 Section J SCT Banner Help Resources Overview In this section These topics are covered in this section SCT Banner Discussion Lists SunGard SCT s ActionWeb SunGard SCT s ActionMail SunGard SCT s ActionLine O SunGard 2004 2005 Financial Aid Technical Page J 1 SCT Banner Discussion Lists Discussion lists List of lists Subscribing SunGard SCT supports a number of useful discussion lists e boracle Oracle issues e bfinaid Financial Aid product issues e bgeneral General product issues e report Reporting issues e btrain Training issues e bpost Electronic distribution announcements For a listing of SunGard SCT s discussion lists send an email message to listserv sungardsct com e No subject e Message body LISTS For additional discussio
39. This field maintains the secondary Pell Grant index from the student SAR Continued on the next page RORSTAT SAR SSN VARCHAR2 VARCHAR2 NUMBER 5 0 NUMBER 5 0 RORSTAT SAR TRAN NO VARCHAR2 RORSTAT SC RORSTAT SEC SAR PGI RORSTAT SAR INIT m VARCHAR2 RORSTAT SLCT IND VARCHAR2 O SunGard 2004 2005 Financial Aid Technical Page K 11 Applicant Status Table Base Financial Aid Table RORSTAT Continued Data Type PES Tl calculated TFC RORSTAT TGRP CODE VARCHAR2 6 RTVTGRP This field maintains the tracking group assigned to the student in the record RORSTAT TRK LTR IND VARCHAR2 1 This field indicates that a tracking letter should be generated for this student the next time tracking letters are printed RORSTAT_UNMET_NEED NUMBER 11 2 This field maintains the student s unmet need which is the gross need minus any awards O SunGard 2004 2005 Financial Aid Technical Page K 12 Section L Reference Overview Introduction The purpose of this section is to provide reference materials related to the workbook In this section These topics are covered in this section DO Topic Page Forms Job Aid L 2 O SunGard 2004 2005 Financial Aid Technical Page L 1 Forms Job Aid Introduction Use this table as a guide to the forms used in this workbook The Owner column may be used as a way to designate the individual s responsible for maintaining a form
40. ad how can new records be created New records can be created on RRAAREQ RNANAxx or ROASTAT e Explain what the process RCPDTMP does It cleans out the temporary data load tables Write a rule to capture in a tracking group those individuals that have a C code on their current records You will need to create a tracking group and tracking code To complete this assignment you should be able to run ROBBGRP and place a requirement on student records that fit the above category Create code on RTVTREQ Create Group on RTVTGRP Find field on RNARSLT RCRAPP2 ELIGIBILITY IND 1 Write RORRULE RCRAPP2 ELIGIBILITY IND 1 and RCRAPP1 CURR REC IND Y e Save Compile Execute Group to test e Run ROBBGRP Write a script that will list the student ID aid year fund code and Paid Award Amount for a student whose aid year is entered as a parameter select spriden id id rprawrd aidy code aidyr rprawrd fund code fund code rprawrd paid amt pd amount from spriden spbpers rprawrd where spriden pidm rprawrd pidm and spbpers pidm spriden pidm and spbpers dead ind is null and spriden change ind is null and spriden entity ind P and rprawrd paid amt is not null and rprawrd aidy code amp aidyr Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 69 Self Check Answer Key Continued Exercise 4 SAP Write a script to select ID name progress code and term for each s
41. al Page D 72 Self Check Answer Key Continued Exercise 7 Write a script to select ID name unmet need EFC FWS work preference and Database total paid awards for each student Use wild card selection for aid year Structure Advanced set pagesize 60 set linesize 80 rem column ID format a9 column NAME format a30 column EFC format 99 990 column UNMET NEED format 99 990 column WORK format a6 column PAID AMT format 99 990 rem spool NEED lis rem select spriden id ID spriden last name Ispriden first name NAME rorstat pri sar pgi EFC rorstat unmet need UNMET NEED rcrapp4 work study WORK sum rprawrd paid amt PAID AMT from spriden spbpers rprawrd rorstat rcrapp rcrappl where rcrappl aidy code amp aidy code and rcrappl curr rec ind Y and rcrappl pidm rcrapp4 pidm and rcrappl infc code rcrapp4 infc code and rcrappl seg no rcrapp4 seg no and rcrappl aidy code rcrapp4 aidy code and rcrappl pidm rorstat pidm and rcrappl aidy code rorstat aidy code and rcrappl pidm rprawrd pidm and rcrappl pidm spbpers pidm and rcrappl pidm spriden pidm and spbpers dead ind is null and spriden change ind is null and spriden entity ind P group by spriden id spriden last name spriden first name rorstat pri sar pgi rorstat unmet need rcrapp4 work study order by 2 spool off Continued on the next page O SunGard 2004 2005 Financial Aid Technical Pa
42. alidate the award s Running this process ascertains whether the applicant is eligible for an individual fund being added to the applicant s package either online or during the Packaging process The process also validates that the applicant is eligible for system or manual disbursements from the awards within the applicant s package O SunGard 2004 2005 Financial Aid Technical Page D 37 Batch Validation Process RPBVLDT RPBVLDT This process validates all awards within an applicant s package to ensure that no changes made to the applicant or the fund s invalidate the award s Running this process ascertains whether the applicant is eligible for an individual fund being added to the applicant s package either online or during the Packaging process The process also validates that the applicant is eligible for system or manual disbursements from the awards within the applicant s package O SunGard 2004 2005 Financial Aid Technical Page D 38 Disbursement Process RPEDISB RPEDISB This process passes the amount of deferred Financial Aid available to an applicant within a specific term to the SCT Banner Student System This is displayed as a memo transaction in the Student Billing module The process passes the amount of Financial Aid available to be disbursed to an applicant within a specific term to the SCT Banner Student System This is displayed as an authorization in the Student Billing module The process also passes th
43. and Fund Rules Need Analysis Results Record O SunGard 2004 2005 Page D 32 Packaging Group Codes and Rules Log Applicant Acceptance Rejection Financial Aid Technical Disbursement Process Flow Diagram Financial Aid Fund Application Applicant s Disbursement Current Award Package Rules Record Disbursement Processing Memo Records SunGard 2004 2005 Financial Aid Technical Page D 33 Loan Process Flow Diagram Loan Period Loan Options Lender Data Rules by Fund Create Loan Application Lending Determine Institution Loan Eligibility Loan Approval Receipt of Loan Check No Eligible Yes Student Receives Check O SunGard 2004 2005 Financial Aid Technical Page D 34 Packaging Process RPEPCKG Packaging e Actual or Simulation process e RPBPDRV creates reports e RPBAWRD out The Award Report that results from the packaging run e RPBFUND out The Fund Report that results from the packaging run O SunGard 2004 2005 Financial Aid Technical Page D 35 Pell Grant Calculation Process RPEPELL RPEPELL e Online batch or from database trigger e Triggers when certain data changes on the following forms e RNARSLT RBAABUD RNASUxx RNANAxx and ROAPELL O SunGard 2004 2005 Financial Aid Technical Page D 36 Online Award Validation Process RPOVLDT RPOVLDT This process validates all awards within an applicant s package to ensure that no changes made to the applicant or the fund s inv
44. cking Group Requirements Rules Applicant Requirements Tables RRRTMSG Tracking Requirement Message Rules RRVAREQ RRVREQI Object Access F A Requirements View O SunGard 2004 2005 Financial Aid Technical Page D 15 Requirement Tracking Process Flow Diagram Create Applicant Requirement Records Financial Aid Application Current Record Create Applicant Requirement Records Other Processes Pckg Memo Dish Can Proceed Send Requirements Tracking Letters To Applicants Requirement Tracking Group Codes and Rules Log Satisfied Requirements Applicant Documents Financial Aid User Manual Requirements Tracking page 7 4 4 of 8 O SunGard 2004 2005 Page D 16 Financial Aid Technical Requirements Tracking Processes and Procedures RTVTREQ Requirements Tracking Validation Form RTVTREQ e Define tracking requirement codes Examples might include institutional applications FAT tax returns verification statements SARs loan applications etc RTVTRST Requirements Tracking Status Validation Form RTVTRST e Define tracking requirement status codes Examples might include received waived established satisfied cancelled or received incomplete RTVTGRP Requirements Tracking Group Validation Form RTVTGRP e Define tracking groups Examples might include variations of Graduate vs Undergraduate documents Selected for Verification documents Independent vs Dependent verification group
45. clude E RD e All MRR records e Year to Date reporting e Justin Time no disbursement e Justin Time no charge e More Review multiple e Used to view a student s multiple reporting record status and to identify reporting record any open issues for the student data REIRMRR Summary e Accesses the summary statement of the Electronic Statement of Account Statement of ESOA data online Account Inquiry Form REISSOA Detail Statement e Accesses the detail statement of the Electronic Statement of Account of Account ESOA data online Inquiry Form REIDSOA O SunGard 2004 2005 Financial Aid Technical Page D 48 Common Functions Forms Forms These are general functions that support all of the other Financial Aid processes name Aid Year Inquiry Form Major tables Seven character Proper name of form process report or table name RORSTAT RORPELL RORHOLD RORDVAL ROBALOG ROBUSDF RORCMPL RORSAPR Satisfactory Academic Progress Rules Table Financial Aid Messages RORENRL RORCRHR ROBINST RORGSQL O SunGard 2004 2005 Financial Aid Technical Page D 49 Common Functions Process Flow Diagram Applicant Record Need Analysis Creation Verification Packaging Disbursement SunGard 2004 2005 Financial Aid Technical Page D 50 Satisfactory Academic Progress Process Diagram ROVSAPR ROVSAxx Batch assign SAP status codes lis file includes SAP views fi number af students VENS ser processed a
46. d on an ANNUAL Basis e Output from FAFSA is the Student Aid Report SAR e Student receives SAR in the mail e Institution receives SAR electronically ISAR e Expected Family Contribution EFC e Institutional application e Application for admission to the institution Financial Aid e File Processing reviews e Verification paperwork e Request additional documentation e Tracking Letter e Documents required for packaging or disbursement e Paperwork completed File Complete e SAP review Packaging e Federal Pell Grant determined by EFC and budget awarding ofaid e Campus based aid SEOG Perkins Loan and Federal Work Study based on institutional packaging guidelines e Scholarships merit and need e Loans subsidized unsubsidized and Plus SunGard 2004 2005 Financial Aid Technical Page D 3 Types of Financial Aid Aid types O SunGard 2004 2005 Scholarships Work Study Loans Subsidized Unsubsidized Parent Perkins Grants Federal Pell Grant Federal Supplemental Opportunity Grant SEOG State Grants Institutional Grants Page D 4 Financial Aid Technical Acceptance and Application of Aid Award letter An award letter is mailed to a student who has the option to accept or decline the awarded aid When the student s acceptance of the aid is entered into the system the aid is then ready for memo authorization or payment Fee assessment Students register for and are subsequently billed for courses
47. dent by PIDM by aid year by hold code e Hold Type Validation Form RTVHOLD e Financial Aid hold codes must exist here before you can load historic records Applicant Applicant Status table RORSTAT Comments e Financial Aid Institution table ROBINST e Financial Aid Institution Options Form ROAINST e Applicants Comments RHRCOMM e Applicant comments are created by PIDM by user ID e Comments also require sequence number O SunGard 2004 2005 Financial Aid Technical Page H 11 Testing Testing e Use SCT Banner forms to view inserted data e Navigate through several forms e User testing O SunGard 2004 2005 Financial Aid Technical Page H 12 Database Requirements Clean instance A clean instance must have at least RFRFFID Federal Fund ID table RORDATA Data Dictionary table RORDSUP Supplemental Log Data table RORDVAL Audit Log Form Data Code table RORPELL PELL Grant Payment Schedule table RPRFEDR Federal Rules table RTVINFC Application Tape ID Validation table e Code Desc and Activity Date only e All other columns should be null RTVRJCT Reject Validation table RTVPTYP Batch Posting Type Validation table RTVDLBT Batch Type Validation table RTVPRCD Program Code Validation table RTVYICD Year in College Description table RORDATA amp RORDSUP are delivered by SCT and should never be changed They map the BANNER fields to the fields the central processor recognized for Need Analysis SunGard
48. dictionary and public synonyms e Grants public access to the synonyms Retrieval The database dictionary is divided by users To retrieve basic information use e ALL Lists all objects that a user has been granted access to e USER Lists all objects that a user owns e DBA Lists all objects in the database e ALL TABLES Descriptions of tables e ALL COL COMMENTS Comments on columns of accessible tables e ALL TAB COLUMNS Lists columns contained in all tables e ALL TAB COMMENTS Comments on tables e ALL USERS Information on all users in database e ALL VIEWS Lists views accessible to user Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page B 11 Database Dictionary Continued Table and For more complete information refer to Oracle Server Reference or select module table name from dict in SQL Plus ownership Product Table owner in Oracle Module owner in SCT Banner Accounts Receivable TAISMGR TAISUSR ALUMNI ADISUSR GENERAL GENERAL Resources SATURN SAISUSR O SunGard 2004 2005 Financial Aid Technical Page B 12 Referential Integrity Definition Primary key Primary key example Foreign key Referential integrity is a condition by which a dependent table s foreign key must have either a null entry or a matching entry in a related table Oracle enforces relationships between tables through the use of primary and foreign key constraints SCT Banner uses naming conventions
49. e beginning of each script prior to the new population being inserted Banner2000 Financial Aid User Manual release 4 0 page 3 38 September 1999 SunGard 2004 2005 Financial Aid Technical Page G 13 Section H Conversions Overview In this section These topics are covered in this section O SunGard 2004 2005 Financial Aid Technical Page H 1 Conversion Methods Manual e Slower conversions e Data entry errors e Small volume only Electronic e Large volumes conversions e Requires development and testing O SunGard 2004 2005 Page H 2 Financial Aid Technical Conversion Considerations Considerations O SunGard 2004 2005 Keeping track of PIDM on Legacy System What type of data you are bringing across Random ID or SSN Name Address formatting e No number signs ff e No periods Address types Multiple IDs on Legacy System Page H 3 Financial Aid Technical Timing Considerations New year e Recommended conversion e Starts January 1 Mid year e Not recommended conversion e Each table must be populated to suggest SCT Banner Financial Aid was running since the beginning of the aid year O SunGard 2004 2005 Financial Aid Technical Page H 4 Conversion Steps Steps e 1 e 2 e 3 e 4 e 5 e 6 e 7 e 8 e 9 e 10 e 11 e 12 Document Review current data Determine scope What will you convert Map current data to SCT Banner Write a detail data plan Review
50. e G 12 Tracking Letters PL SQL scripts Purpose Notes There are three PL SQL scripts to select those needing letters e RESTRKI SQL Selects all students that have never had a user specified tracking letter sent or have had that tracking letter sent more than a user specified number of days ago e RESTRK2 SQL Selects only students that have never had a user specified tracking letter sent e RESTRK3 SQL Selects only students that have had a user specified tracking letter sent more than a user specified number of days ago The user specified values are defined at the beginning of each script These scripts help you identify students who need to have an initial or a follow up tracking letter sent When executed these SQL PLUS scripts place student IDs into Population Selection IDs for use in Letter Generation These scripts replace the usual procedure of defining rules for a Population Selection ID and then running the Population Selection Extract Process GLBDATA It is necessary to use these scripts because of current limitations in the Population Selection process RESTRK1 SQL can be used if you use the same letter for initial and follow up tracking requests If you use different letters then you could use RESTRK2 SQL for initial requests and RESTRK3 SQL for follow up requests Note The Selection ID being populated must first have been created on GLRSLCT Also note that all students are deleted from the Selection ID at th
51. e Table notusedin7X RPBLNDR s ii ferns a Tae Table RPBLPRD HEN CMM Loan Period Base Data Table RPBLOPT 32 X Loan Options Table Use A DL and RORTPRD iX Fs Aid Period Term Code Table RFRDEFA Default Fund Award Schedule Rules pogoj it Sa JJ RFRDEFD 35 Default Fund Disbursement Schedule Portas ii E BI A si III Table RERDLCK Fund Disbursement Lock Table ES RERASCH Fund Award Schedule Rules Table RERCOMM 39 X E Fund Comments RPRLDSB 42 X L Loan Period Disbursement Schedule pere qe Je ce PIT RFRASPC 43 X F FundAidYearSpecificData _________ a anid Ald Peri ues Table J RPBOPTS 45 x OVER PackagingOptions Table repere x e fran Di ole ske E RPRLTRM 48 Loan Period Term Schedule Rules Table RPRESRC 49 X JA Maximum Amount Per Fund Source RPRDEFE Default Equity Packaging Rules Table RPRDEFG 51 X JA Default Gap Packaging Rules Table pu Ec Rules Table Packaging Rules Table pese oq coser Rules Table Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page K 4 Financial Aid Rule and Validation Tables for Migration from PPRD to PROD January 5 2005 Continued Table Name Import Rule Area Description Comments Order Validation Table RPRDEFS Default Self Help Packaging Rules Table sm RPRGFND Packaging Group Fund Rules Table RPREXPT Exemption Rules Table Do
52. e amount of Financial Aid scheduled to be disbursed to an applicant within a specific term to the SCT Banner Student System All scheduled disbursements with a date less than or equal to the processing date that have not been disbursed will be processed These are displayed as payment transactions in the Student Billing module O SunGard 2004 2005 Financial Aid Technical Page D 39 Pell Origination Extract Diagram REREXxx REICODD Updates Batches records for pesso Updated with batch information transmission to COD 1 REAORxx I ROA Origination record data may be F A E Transmit Oreanization viewed and deleted prior to g i extraction Origination record Change file name from status updated to S sent rerexxx nnnnn xmi to COMRECIN I REASTID I If first COD transaction Student a Identifier record is created and sent COD Extract e Process extracts Process e Origination Data SERES e Student Identifier Record e Process updates e Origination Record REAORxx e Student Identifier Record REASTID e COD Document Control REICODD Send e Access FTP software Origination File e Move rerex04 nnnnn xml file from jobsub to a local PC COD e Rename rerex04 nnnnn xml file to COMRECIN e Upload COMRECIN file to COD website O SunGard 2004 2005 Financial Aid Technical Page D 40 Pell Origination Import Diagram Receive REICODD Acknowledgment f di Updated with batch information Rec
53. ed RCRAPPI CURR REC IND is shown in column 2 Based on its order it uses PK RCRAPPI WITHOUT WITH 1 RCRAPP1 PIDM 1 RCRAPPl PIDM 2 RCRAPP1 AIDY CODE 2 RCRAPPl AIDY CODE 3 RCRAPP1 CURR REC IND 3 RCRAPP1 SEQ NO 4 RCRAPPl SEO NO 4 RCRAPP1 INFC CODE 5 RCRAPP1 INFC CODE 5 RCRAPPl CURR REC IND 1 3 RCRAPP1 CURR INDEX 1 4 PK RCRAPP1 Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page I 7 Hints on Improving Performance of Rules Created on RORRULE Continued Rule RULE If your rule will require PIDM and AIDY CODE joins between RCRAPPI and another RCRAPPx table use an RCRAPPx column as the first column in your rule This is necessary because the ROOGSQL compile program uses the first rule line s table as the driving table for additional joins RCRAPPx When the rule is created on the form as RCRAPP2 MODEL CDE D AND lt _ First rule line is RCRAPPx column RCRAPP1 VERIFICATION MSG 1 AND RCRAPP1 CURR REC IND Y AND ROVST95 LEVL CODE UG AND ROVST95 STYP CODE 9 AND ROVST95 STST COD N AS SA Compiled rule The compiled rule INCLUDES an RCRAPP1 to RCRAPP2 PIDM join SELECT DISTINCT RCRAPP2 PIDM FROM RCRAPP2 RCRAPP1 ROVST96 HERE RCRAPP2 MODEL CDE D RCRAPP1 VERIFICATION MSG 1 D Y Y UG Y Z D D RCRAPP1 CURR REC D ROVST96 LEVL CODI D D D
54. eive origination acknowledgement fle COMRECOP REASTID Student Identifier Record Updated RERIMxx REAORxx Import origination Origination record status Acknowledgement file updated into Banner Output A Accepted includes list of all C Corrected corrected and rejected D Duplicate records R Rejected COD File e Loads the acknowledgement files received from COD into SCT Banner Import Process e FTP the COMRECOP file into the dataload directory RERIME COD File Import Process updates e Origination Record REAORxx e Student Identifier Record REASTID e COD Document Control REICODD Review Pell e COD Document Control REICODD Origination and e Monitors status of all data batches for Pell and Direct Lending disbursement e Includes batches sent and received document IDs O SunGard 2004 2005 Financial Aid Technical Page D 41 Pell Origination Resend Resend a Pell e COD Extract Process REREXxx Origination e Enter Document ID on parameter 13 batch e Resets Document ID on student records that are included in the original batch O SunGard 2004 2005 Financial Aid Technical Page D 42 Pell Disbursement Diagram READIxx Updated with minimal Disbursement information RPEDISB TSAAREV Disbursement Process Updated with Disbursement information RPAAWRD Updated with Disbursement cias information O SunGard 2004 2005 Financial Aid Technical Page D 43 Pell Disbursement Extract Diagram
55. election Process Flow Process flow Application definition e GLIAPPL Application Inquiry Form e GLRAPPL Application Rules table Objects e GLIOBJC e GLIOBJT Variable usage Variable definitions e GLRVRBL Variable Definition Rules table Population Selection rules e GLRSLCT Population Selection Definition Rules table Manual population creation e GLAEXTR Population Selection Extract Data Form View population e GLIEXTR Population Selection Extract Data Inquiry Form SunGard 2004 2005 Financial Aid Technical Page F 7 Self Check Directions Use the information you have learned in this workbook to complete this self check activity Exercise 1 Write a population selection to extract students who have an EFC between 100 and 200 Extract the students and then run a report one that s in baseline Banner to print to the database the extracted students full names and IDs O SunGard 2004 2005 Financial Aid Technical Page F 8 Self Check Answer Key Exercise 1 Write a population selection to extract students who have an EFC between 100 and 200 Extract the students and then run a report one that s in baseline Banner to print to the database the extracted students full names and IDs The report to run is the Basic Applicant Report RORAPLT O SunGard 2004 2005 Financial Aid Technical Page F 9 Section G Letter Generation Overview In this section These topics are covered in this section G 3 G 6
56. ess and phone number e Your question detailed description O SunGard 2004 2005 Financial Aid Technical Page J 4 SunGard SCT s ActionLine Description Phone Fax help for time critical issues To contact ActionLine Phone 1 800 522 4TCP FAX 1 610 725 7430 O SunGard 2004 2005 Page J 5 Financial Aid Technical Section K Table Indexes Overview In this section These topics are covered in this section Financial Aid Rule and Validation Tables for Migration from K 2 PPRD to PROD January 5 2005 Loe Applicant Status Table Base Financial Aid Table K 7 RORSTAT SunGard 2004 2005 Financial Aid Technical Page K 1 Financial Aid Rule and Validation Tables for Migration from PPRD to PROD January 5 2005 Table Name Import Rule Area Description Comments Order Validation Table Esse RTVAPRD Aid Period Validation Table RTVBGRP Groupl X B Budget Group Validation Table DO RTVPGRP Packaging Group Validation Table RTVSAPR Groupl X SAP Satisfactory Academic Progress Validation Table RTVTGRP TRACK Tracking Group Validation Table EE Hm pegos Table e pe sn Validation Table Activity Date Do ROBINST OVER Financial Aid Institution Table RORCAMP OVER Financial Aid Campus Table K RORCRHR OVER Credit Hours Table K RORWEBA OVER Web Acceptance Table K RTVARSC Financial Aid Resource Codes S RTVAUST Group 4 X SE Employment Authorization Status pa E e
57. essed via the Applicant Immediate Process Form ROAIMMP SunGard 2004 2005 Financial Aid Technical Page D 17 RORRULE and ROOGSQL ROOGSQL The Online Group Assignment Selection Criteria Generation process ROOGSQL creates a SQL statement from the budgeting tracking and packaging group selection rules created by the Financial Aid Selection Rules Form RORRULE O SunGard 2004 2005 Financial Aid Technical Page D 18 Student System Shared Data Forms Shared data The SCT Banner Financial Aid System is designed to share information with forms the institution s Student Information System the SCT Banner Student System or another Student system Data is shared with the SCT Banner Student System through a series of views that contain information that the Financial Aid system may require for certain processes This information is available through a series of inquiry only forms listed on the Student System Shared Data menu Student System data is shared through these views rather than via direct access of the SCT Banner Student System tables Thus in the event that SCT Banner Financial Aid is installed as a stand alone product in an environment in which SCT Banner Student is not installed these views can be created as tables by the institution and periodically populated with data from its Student System Seven character Proper name of form process report or table name Summary Academic History Inquiry Form Test Score Inquiry
58. fective Date ROVSTxx and the Admissions Application View ROVADxx were specifically designed for use in Financial Aid rules forms These views enable you to access data in the Student system without the need to specify term code joins The difficulty with the preceding rule is that when you commit and exit from the Financial Aid Selection Rules Form RORRULE the Automatic Rule Compilation ROOGSQL COBOL process adds the necessary PIDM and aid year joins for you behind the scenes Therefore the preceding rule becomes SELECT DISTINCT RCRAPP1 PIDM FROM FAISMGR RCRAPP1 FAISMGR ROVST95 FAISMGR ROVAD95 HERE RCRAPPl CURR REC IND Y AND RCRAPPl INST HOUS CDE 2 AND ROVST95 LEVL CODE UG OR ROVAD95 LEVL CODE UG AND ROVST95 PIDM RCRAPP1 PIDM AND ROVAD95 PIDM RCRAPP1 PIDM AND ROVST95 AIDY CODE RCRAPP1 AIDY CODE AND ROVAD95 AIDY CODE RCRAPP1 AIDY CODE AND RCRAPP1 AIDY CODE AIDY oo E AND RCRAPP1_PIDM PIDM The additional PIDM and aid year joins require that a record exist in each view not just one or the other for this statement to be successful Students must have records in both views to be selected this was not the intention of the rule Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page I 2 Building Online Group Assignment Rule
59. ge D 73 Self Check Answer Key Continued Exercise 8 Modify your script from the previous exercise to select only students that Database indicated they wanted FWS for 1998 99 Structure Advanced set pagesize 60 set linesize 80 rem column ID format a9 column NAME format a30 column EFC format 99 990 column UNMET NEED format 99 990 column WORK format a6 column PAID AMT format 99 990 rem spool FWS lis rem select spriden id ID spriden last name Ispriden first name NAME rorstat pri sar pgi EFC rorstat unmet need UNMET NEED rcrapp4 work study WORK sum rprawrd paid amt PAID AMT from spriden spbpers rprawrd rorstat rcrapp rcrappl where rcrappl aidy code amp aidy code and rcrappl curr rec ind Y and rcrappl pidm rcrapp4 pidm and rcrappl infc code rcrapp4 infc code and rcrappl seg no rcrapp4 seg no and rcrappl aidy code rcrapp4 aidy code and rcrappl pidm rorstat pidm and rcrappl aidy code rorstat aidy code and rcrappl pidm rprawrd pidm and rcrappl pidm spbpers pidm and rcrappl pidm spriden pidm and spbpers dead ind is null and spriden change ind is null and spriden entity ind P and rcrapp4 work study 1 group by spriden id spriden last name spriden first name rorstat pri sar pgi rorstat unmet need rcrapp4 work study order by 2 spool off Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 74 Self
60. hn deBolt DuShen Do not enter spaces within hyphenated last names o Smith Jones Cochram Ashley First initials For names using a first initial and middle name enter a period and one space middle name between the initial and the name Example R Maureen Smith Prefixes Suffixes Use periods after prefixes and suffixes where applicable Miss Mrs Mr Jr SunGard 2004 2005 Financial Aid Technical Page B 18 Addresses Pound sign Do not use a pound sign when completing an Address field if your organization uses SCT Banner Letter Generation SCT Banner Letter Generation recognizes a pound sign as a formatting command Code definitions County codes are defined on the County Code Validation Form STVCNTY State and province codes are defined on the State Province Code Validation Form STVSTAT State and Province names are entered into the State Prov fields O SunGard 2004 2005 Financial Aid Technical Page B 19 Dates Date format You may select the date format via the Date Format radio button on the selection Installation Control Form GUAINST Date formats e MDY November 6 2005 is entered as 11 06 05 MDY DMY DMY November 6 2005 is entered as 06 11 05 X MD e YMD November 6 2005 is entered as 05 11 06 Notes e Ifyou 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 e
61. ions and resources for data and help O SunGard 2004 2005 Financial Aid Technical Page A 2 Section B Financial Aid Technical Training Overview Overview In this section These topics are covered in this section SCT Education Centers SCT Banner Products Financial Aid Directories 3 4 Naming Conventions 5 7 8 B 10 Bo Bio O SunGard 2004 2005 Financial Aid Technical Page B 1 SCT Education Centers Benefits e Provide a centralized location for training in SCT Banner and Oracle e Provide an environment where SCT clients can focus on learning e Provide an opportunity to interact with colleagues from other institutions O SunGard 2004 2005 Financial Aid Technical Page B 2 SCT Banner Products Diagram General Student Financial Human Resources Accounts Position Receivable Control O SunGard 2004 2005 Financial Aid Technical Page B 3 Financial Aid Directories Diagram O SunGard 2004 2005 Financial Aid Technical Page B 4 Naming Conventions Naming All SCT Banner objects adhere to naming conventions The names of all SCT conventions Banner forms except menu forms reports processes and tables are seven characters long with each character representing a position location Objects include forms tables processes etc Example RBRBCMP Budget Component Rule Form e R gt Financial Aid e B gt Budgeting e R gt Rule e BCMP gt acronym Position 1 Identifies the primar
62. ir name records O SunGard 2004 2005 Financial Aid Technical Page B 26 Self Check Answer Key Exercise 1 Data Write a simple script to use as a tool to list information about SCT Banner Dictionary Financial Aid tables to include table name table comment The script should include ability to ask user for name of table and should accept partial input Save your script to a temporary directory on your computer for future use column comments format a30 word wrap select table name comments from all tab comments where table name like upper amp Table Name and table name not like order by table name Exercise 2 Write a simple script to use as a tool to examine constraints placed on tables by Referential SCT Banner designers Integrity Save your work column column set select from where order O SunGard 2004 2005 constraint name format a30 word wrap column name format a25 linesize 130 constraint name column name position table name all cons columns column name like upper TableNames by table name position Continued on the next page Financial Aid Technical Page B 27 Self Check Answer Key Continued Exercise 3 Write a sc ript that will show constraint name column name position type of Referential constraint constraint status and table name Integrity Challenge column column column set select decode from where and and order constraint name format a3
63. m RTVTREQ Requirements Tracking Validation Form STVCNTY County Code Validation Form STVSBGI Source Background Institution Code Validation Form STVSTAT State Province Code Validation Form RTVTRST Requirements Tracking Status Validation Form O SunGard 2004 2005 Financial Aid Technical Page L 6 Release Date This workbook was last updated on 6 14 2005
64. n jobs 2 Parameter Definitions Form GJAPDEF Name and define parameters 3 Default Parameter Value Validations Form GJAPDFT Define default parameters 4 Parameter Value Validations Form GJAPVAL Enter parameter values for a job 5 Process Submission Controls Form GJAPCTL Run a job 6 Process Results Form GJARSLT O SunGard 2004 2005 View the results of a job Page E 4 Financial Aid Technical Job Submission at the Functional Level Diagram Error Creates results execution senpi Defines job to Unsuccessful completion Job Submission Success Writes job according to Report 1 Programmer Job Submission job standards submitted Defines job requirements SunGard 2004 2005 Financial Aid Technical Page E 5 Job Submission at the Database Level Diagram GJARSLT GJAPVAL GJAPDEF amp Unsuccessful completion GJAPDFT GJAPCTL Report 1 job submitted GJAJOBS Defines job requirements SunGard 2004 2005 Financial Aid Technical Page E 6 Section F Population Selection Overview In this section These topics are covered in this section Population Selection Overview F2 Step 1 Identify an Application O SunGard 2004 2005 Financial Aid Technical Page F 1 Population Selection Overview Description Population Selection allows you to identify and group entities in the database such as people vendors or organizations Population Selection extracts a sub
65. n last name spbpers ssn spriden change ind from spriden spbpers where spriden change ind N and spriden entity ind P and spriden pidm spbpers pidm O SunGard 2004 2005 Financial Aid Technical Page B 29 Section C Database Tool Set for SQL Overview In this section These topics are covered in this section SQL Plus Scripts O SunGard 2004 2005 Financial Aid Technical Page C 1 SQL Plus Scripts Scripts The following scripts are used to examine the database structure using SQL Plus check col column comments format a30 word wrap comments sql select column name comments from all col comments where table name like upper amp Table Name order by column name check tab column comments format a30 word wrap comments sql select table name comments from all tab comments where table name like upper amp Table Name and table name not like 5 order by table name check cons sql column constraint name format a30 word wrap column column name format a25 column ctype format a22 head Constraint Type set linesize 130 select constraint name column name position table name from all cons columns where column name like upper amp TableName order by table name position ja O SunGard 2004 2005 Continued on the next page Financial Aid Technical Page C 2 SQL Plus Scripts Continued check cons colum all sql colum colum se selec
66. n lists try listproc sungardsct com Send mail to listserv sungardsct com or listproc e Leave subject field blank e In message body subscribe listname YourFirstName YourLastName You may subscribe to multiple lists in the same email as long as each subscribe command is on a separate line Example subscribe boracle John Doe subscribe bfinaid John Doe SunGard 2004 2005 Financial Aid Technical Page J 2 SunGard SCT s ActionWeb ActionWeb Available from SunGard SCT s web site www sungardsct com click on Support ActionWeb allows clients to e Contact the ActionLine e Check status of their contacts with ActionLine e Browse Known Issues e Review FAQ s e Access Electronic Distribution download e Access additional services Requesting To request access to SunGard SCT s secured ActionWeb pages send access email requests to csr sungardsct com Include your name phone and organization name SunGard 2004 2005 Financial Aid Technical Page J 3 SunGard SCT s ActionMail ActionMail ActionMail allows clients to send their ActionLine questions through email ActionMail requests questions are given the same priority as those made over the phone Use this service for less time critical issues Contacting Send the following information to ambanfa sungardsct com ActionMail e Your name and organization e Your product and release number e Severity of problem high medium low e Your e mail addr
67. nd Determination Form RFQDETC e Fund Query Form RFQFUND e Disbursement Validation Query Form ROQPVAL e Award Disbursement Query Form RPQAWRD O SunGard 2004 2005 Financial Aid Technical Page B 9 Inquiry Forms Description The 3 character of an inquiry form is always 1 Inquiry forms cannot be used to change information they are used strictly for lookups They may be accessed from any menu Examples e Applicant Need Analysis Application Inquiry Form RNIAPPL e Applicant Data Log Inquiry Form ROIALOG e Aid Year Inquiry Form ROIAIDY e Budget Inquiry Form RBIBUDG O SunGard 2004 2005 Financial Aid Technical Page B 10 Database Dictionary Description Maintenance Ownership catalog sql The database dictionary is a set of tables and views used as a read only reference about the database It is one of the most important parts of an Oracle database because it contains e Names of schema objects tables views e Names of Oracle users e Privileges and roles granted to each user The database dictionary is maintained internally e System table space e Select only In SQL Plus describe dictionary or desc dict SYS owns all base dictionary tables e Users have access to views of the data dictionary e No user should alter any object contained in the SYS schema During normal operation Oracle verifies the existence of objects and checks whether users have proper access to them e Creates the database
68. nd students rules population selection Individually assign Schedule pending SAP status codes letters for SAP online effective term optional Translates Batch post SAP calculated status effective codes to new status termstatus to codes and assigns ROASTAT record letters optional SunGard 2004 2005 Financial Aid Technical Page D 51 Reporting Reporting The Reporting module contains standardized reports that extract data from all module other Financial Aid modules Commonly used Financial Aid Roll Rule Report ROPROLL reports e Create the scripts that rolls year rules e Byaid year Group Fund Budgeting Tracking Packaging FISAP Report RORFSxx e Prints FISAP report Other reports to e Disbursement Process RPEDISB know e Pell Awarding Process RPEPELL e Packaging Process RPEPCKG e Year specific Need Analysis Process RNEINxx e INAS SunGard 2004 2005 Financial Aid Technical Page D 52 Award History and Transcripts Forms This module maintains historical applicant data and produces Financial Aid transcripts Seven character Proper name of form process report or table name RHRTRNS Transcript Rules Form RHAPBAW Pre Banner Award Summary Form RHAPTRN Prior Institution Transcript Form O SunGard 2004 2005 Financial Aid Technical Page D 53 Student Employment Forms This module maintains data related to student employment financial assistance Seven character Proper name of form process re
69. ner Financial Aid User Manual O SunGard 2004 2005 Financial Aid Technical Page D 8 Prepare for the Data Load Process RTVINFC RCRTPTR Common Matching ROAINST RCRDTSR O SunGard 2004 2005 Set up the Application Tape ID Validation Table The values are delivered already populated To set up this form find the source code s used at your institution Define the rest of the columns for that source This includes setting a requirements code tracking requirement that can be updated when the data is loaded which is a supplemental requirement that can be used for CSS The related tracking requirement status is also defined The Contact Type and Recruiting Source fields must be populated for the Data Load process to run to completion Even though you may not be creating recruitment records this data is required for Part 3 of the Data Load process RCRTPxx to complete successfully Build the Interface Data Translation Rules Form RCRTPTR Determine the conversion values on the Interface Data Translation Rules Form RCRTPTR The converted data is data that is contained on the incoming data file The fields that you must define are Major Code Marital Status and SBGI Code These fields may contain data that the institution uses and this is the opportunity to make the incoming data match the institutionally defined validation tables for these fields Enter values and rules on the Common Matching Forms Build the Instituti
70. odules used within a SCT Banner application the SCT System identifier should be used as the first character i e R Financial Aid and W Y or Z should be used as the second character For more information refer to Chapter 1 of the General Technical Reference Manual O SunGard 2004 2005 Financial Aid Technical Page B 6 Form Types Menu e Lists all related forms e Outlines the System Application e Allows one to enter update and query Functional Validation e Lists all possible values for a given field e Information may be entered Rule Control e Defines how variables objects and applications can be used Query e Allows information to be queried and returned to another form Inquiry e Used only to look up information O SunGard 2004 2005 Financial Aid Technical Page B 7 Validation Forms Description Validation forms always have TV as their 2 and 3 characters All validation forms must have e Acode e A description e Anactivity date Validation Table and form names are the same tables Examples e Budget Type Validation table RTVBTYP e Budget Group Validation table RTVBGRP e Holds Validation table RTVHOLD O SunGard 2004 2005 Financial Aid Technical Page B 8 Query Forms Description The 3 character of a query form is always a O Query forms can only be reached when called by another form Query forms cannot be used to change information they are used strictly for lookups Examples e Fu
71. omated Packaging RPRDEFR OPTIONAL Set up for Institutions Using Self Help Gap or Equity Packaging RPRPCKR Create Self Help Gap or Equity Packaging Rules if pertinent to Packaging Philosophy RPRAUDT Create OPTIONAL rules for Audit Grading Mode if using Adjusted Enrollment Hrs RORRULE Create 0506 OPTIONAL Group Fund Awarding Rules RPREXPT RPRCONT Create OPTIONAL Exemption Interface Rules and or Contract Interface Rules RPRFSRC Create OPTIONAL Fund Source Maximum Rules for 0506 ROAINST Update 0506 Institutional Loan Parameters RPRLOPT Create 0506 Packaging and Disbursement Options for Each Loan Fund RPRLPRD Create 0506 Loan Periods RFRMGMT Verify that the Loan Process Indicator is Checked for Each Loan Fund to be Awarded in 0506 RPRLNDR Review Lender Information and Update for 0506 ROAINST Update EDE Corrections Indicator form and Complete Pell COD and Commonline Windows for 0506 RORDATA Verify Presence This SCT Delivered Table Modifications to RORDATA Are NOT Recommended Financial Aid Technical Page D 60 New Year Roll Summary Summary Three steps to roll Aid Year data e Create an 0506 instance of ROAINST prior to installation of new year release e Run the ROPROLL process from Job Submission e Review Edit each of the Financial Aid modules listed in the New Year Start up Checklist O SunGard 2004 2005 Financial Aid Technical Page D 61 Self Check Directions Exercise 1 Applican
72. onal Financial Aid Options Form ROAINST The following fields must all be defined prior to running the data load for the year Current Term Code Start Year End Year EFC Proration Default Aid Period Primary MDE SBGI Conversion Initial SAP Code Update Trans EFC Applicant Received Date Indicator and the MDE codes if applicable Establish the matching siource and parameter set for the aid year and data source used for the processing of indivicual records from the Financial Aid Suspended Record Maintenance Form RCRSUSP Continued on the next page Financial Aid Technical Page D 9 Prepare for the Data Load Process Continued Destination Regardless of what source your data is being received from and in which directory format the data must be loaded to the SDATA HOME finaid directory for UNIX or DATASHOME for VMS Ensure that you have write privileges to the appropriate directory Filenames The disk file name data load file must be renamed to match what the processes are expecting Rename the file to e xxyyesar tap EDE ISIR diskette or download e xxyyesat tap EDE ISIR tape e xxyycssd tap CSS diskette or download e xxyycsst tap CSS tape This step must be completed every time there is a new input file to be loaded to the system SunGard 2004 2005 Financial Aid Technical Page D 10 Run the Data Load Process Data Load steps e Run RCBTPxx Part 1 of Data Load for federal data or e Run RCBCTxx Part 1 of Da
73. ontents Tope Page Section A Introduction Overview Section B Financial Aid Technical Training Overview SCT Banner Products B 3 B 5 Query Forms Inquiry Forms B 10 Database Dictionar B 11 Referential Integrity B 13 Data Standardization B 16 Identification Numbers B 17 B 18 Addresses B 19 Dates B 20 PIDMs B 21 SOBSEQN PIDMs and SOBSEQN Self Check Self Check Answer Key Section C Database Tool Set for SQL Names NINININ YA Apu no alo Ni Overview SQL Plus Scripts Continued on the next page Table of Contents Continued DO Tepe Page e Overview Continued on the next page Table of Contents Continued DO Tope Page Pell Grant Calculation Process RPEPELL D 36 D 37 D 38 D 39 D 40 D 41 D42 DA D 44 DAS D 46 D 47 DAB D 49 D 50 Di D 52 D 53 D 54 D 53 D 56 D 57 D 58 D 61 Section E Job Submission Job Submission at the Database Level Continued on the next page Table of Contents Continued DO pie Page e Section F Population Selection ____ Overview Population Selection Overview _ F2 Step 1 Identify an Application F3 Step2 DefineCriteria FA Step3 ExtacttheIDs O FS Step 4 Review Population Selection F6 Population Selection Process Flow F 7 Self Check F8 Self Check Answer Key SectionG LetterGeneration Overview OOOO Letter Gene
74. orrections ROAALOG Control Report Lists Corrections RERCRCR View Ouigoing Batch Information RPIBATC O SunGard 2004 2005 Enter Changes RNANAxx RNAVRxx RNAOVxx or RNAPRxx Process Latest Changes RLRLOGG Create Batch Request REBCDxx ED Connect Software Page D 27 CORRXXOP BannerDataload Processes to Import Corrected ISAR Records Report on Changes Loaded RORALOG View Changes Logged for Individual Siudents ROIALOG View Changes Logged for the Institution ROIILOG Financial Aid Technical Budgeting Forms Tables and Reports Forms Budgeting maintains data regarding an applicant s cost of attending an institution Seven character Proper name of form process report or table name RBAABUD Applicant Budget Form ROARMAN Financial Aid Record Maintenance RHACOMM Applicant Comments Form ROAIMMP Applicant Immediate Process Form Tables Seven character Proper name of form process report or table name RBBABUD Applicant Budget Table RBRACMP Applicant Budget Component Table Processes There are no Budgeting processes or procedures procedures and reports There are however two reports e Applicant Budget Report RBAABUD e Budget Component Report RBRBCMP O SunGard 2004 2005 Financial Aid Technical Page D 28 Funds Management Forms and Tables Forms Funds Management maintains data related to Financial Aid funding sources Seven character
75. other table or view the outer join symbol has been included by the ROOGSOL process This rule now requires that a record exist in ROVADOS with or without a level code and makes the ROVST95 record optional In other words if both a general student record and an admissions record exist for this student check if either has the level code set to UG If a general student record does not exist yet for this student use the student s level on the admissions record and ignore the general student record references If there is no admissions record however the statement fails and the student is either processed into another group or is assigned to the default group O SunGard 2004 2005 Financial Aid Technical Page 1 3 Tips for Financial Aid Rules on Forms Tips e SunGard 2004 2005 If you reference any of the RCRAPPx tables in the rules that you write for Financial Aid forms you do not need to include RCRAPP1 CURR REC IND Y on the form it is automatically generated into the compiled rule by the ROOGSQL process when you commit and exit the form You can only add an outer join to one table or view on the form Use outer joins only on table view to table view joins Therefore only include the outer join symbol on a single rule line that joins the key column of one table view to the key column of another This is considered a PIDM join when grouping rules You should not use it on every line which accesses the oute
76. port or table name RJASEAR Student Employment Authorization Form RJASERF Student Employment Referral Form RJASEME Student Employment Mass Entry Form RJISEWH Student Employment Work History Form Major tables Seven character Proper name of form process report or table name RJBPLBD Placement Base Data Table RJBJOBT Job Title Table RJRSEAR Student Employment Authorization Table RJRSERF Student Employment Referral Table RJRSEWH Student Employment Work History Table Processes and Payroll Load Process RJRLOAD procedures e Interface payroll data from SCT Banner Human Resources to SCT Banner Financial Aid SunGard 2004 2005 Financial Aid Technical Page D 54 Logging Forms and e Institution Financial Aid Options Form ROAINST audit logging tables e Budget logging e Needs Analysis logging e Packaging logging e Pell Correction logging e Audit Log table ROBALOG O SunGard 2004 2005 Financial Aid Technical Page D 55 New Year Roll 2005 2006 Objectives e To review all of the steps required to roll selected aid year specific information from one aid year to the new aid year including e Creation of an 0506 ROAINST instance e Running the ROPROLL process from Job Submission e Manually reviewing and editing rules and validation tables FOLLOWING the roll Warning BEFORE your IT Department can possibly install the appropriate Banner New Aid Year release you MUST first create an 0506 instance of ROAINST
77. r joined tables When you use outer joins on the Financial Aid rules forms you must create the join line with the optional table view on the left for example ROVST95 PIDM RCRAPP PIDM Financial Aid Technical Page I 4 Hints on Improving Performance of Rules Created on RORRULE Rule RULE If you are using RCRAPP2 RCRAPP3 RCRAPP4 or RCRESAR in your RORRULE rules include the rule RCRAPP1 CURR REC IND Y asa rule on the form To make it easier to see we have placed it as the LAST rule on the form Including this rule will ensure that the correct RCRAPP1 index constraint is used for performance purposes If RCRAPPI is the only RCRAPPx table that is used in your rule you do not need to include it onthe form The reason for the performance difference is the order in which ROOGSQL creates the compiled rules and as a result the RCRAPP1 index which is used at run time RCRAPPI has 2 indexes RCRAPP1_CURR_INDEX a nonunique index and Indexes PK RCRAPPI a unique primary key constraint The preferred index constraint when using multiple RCRAPPx tables is PK RCRAPPI RCRAPP1_ RCRAPP1_CURR_INDEX nonunique is made up of CURR_INDEX 1 RCRAPPl AIDY CIDE 2 RCRAPP1 PIDM 3 RCRAPP1 CURR REC IND PK RCRAPP1 PK RCRAPPI primary key constraint is made up of 1 RCRAPP1 AIDY CODE 2 RCRAPP1 PIDM 3 RCRAPP1 INFC CODE 4 RCRAPP1 SEO NO Continued on the next
78. ration Overview Cid ExampleLetter Steps to Create a Letter Step 1 Define Variables gt gt Step 2 Construct Paragraphs Step 3 Combine Paragraphs Step 4 Extract a Population U INI Q A I CA Q S Step5 Extract Letter Variables Step 6 Print the Letter o Gn Creating a Letter Tracking Letters O oo SectionH Conversions Overview OOOO H4 Conversion Methods H2 Conversion Considerations TS Timing Considerations CT H4 Conversion Steps H5 Financial Aid System Conversions H6 GR KA KA RA SILE u temi EJ NU S General Person Records HW General Student Records CT H8 Tracking Requirements H9 Award History and Transcripts mio Conversion oO Testing o HA Database Requirements Y ERB Cleaning Database HM Migration to Production HAS Continued on the next page Table of Contents Continued Tope Page Section I Troubleshooting Overview ja 1 Building Online Group Assignment Rules Using Outer Joins Tips for Financial Aid Rules on Forms Hints on Improving Performance of Rules Created on I 5 RORRULE r1 1 2 r3 1 4 r5 Section K Table Indexes K 1 PPRD to PROD January 5 2005 RORSTAT Section L Reference Pons dob Aud Overview Workbook goal Intended audience Prerequisites In this section O SunGard 200
79. s etc RRRGREQ Requirements Tracking Group Requirements Rules Form RRRGREQ e Develop tracking group requirements for each group Indicate which documents are required for each specific tracking group e You have the opportunity here to change your defaults for a specific code from the defaults you indicated on RTVTREQ for the code e These defaults will only be used if the system brings in the requirements for the specific student If the requirement is entered manually and the manually entered code is not defined in the student s particular tracking group the default from the RTVTREQ table will appear RORRULE Financial Aid Selection Rules Form RORRULE e Develop tracking group rules associated with each group Assign criteria for placing applicants into tracking groups Use the standard SCT Banner data element dictionary table Selection criteria may come from the SCT Banner Student System Financial Aid System or any other SCT Banner system which has been installed RRRTMSG Requirements Tracking Code Message Rules Form RRRTMSG e Develop specific messages for each tracking code if desired ROBBGRP Batch Automatic Grouping process ROBBGRP e Perform Budget Tracking and or Packaging group assignment in batch mode for all applicants or a selected population of applicants ROOAUTO Automatic Grouping process ROOAUTO e Perform Online Budget Tracking and Packaging group assignment for a specified applicant This process may be acc
80. s Using Outer Joins Continued Outer join Revised statement This statement however can be easily modified and made to work using an outer join An outer join enables you to make one table or view optional Ifa row exists in that table or view you use it if it does not exist the statement does not fail and continues without it The join column of the optional table or view has the symbol added to it to identify it as an outer join In this case add one more row to the preceding statement RCRAPP1 CURR REC IND Y AND RCRAPP1 INST HOUS CDE 2 AND ROVST95 LEVL CODE UG OR ROVAD95 LEVL CODE UG AND lt Added AND ROVST95 PIDM RCRAPP1 PIDM lt Additional row The compiled statement becomes the following SELECT DISTINCT RCRAPP1 PIDM FROM FAISMGR RCRAPP1 FAISMGR ROVST95 FAISMGR ROVAD95 HERE RCRAPPl CURR REC IND Y AND RCRAPP1 INST HOUS CDE 2 AND ROVST95 LEVL CODE UG OR ROVAD95 LEVL CODE UG AND ROVST95 PIDM RCRAPPl PIDM AND ROVST95 PIDM RCRAPP1 PIDM AND ROVAD95 PIDM RCRAPP1 PIDM AND ROVST95 AIDY CODE RCRAPP1 AIDY CODE AND ROVAD95 AIDY CODE RCRAPP1 AIDY CODE AND RCRAPPl AIDY CODE AIDY AND RCRAPPl PIDM PIDM Note that wherever ROVSTO9S is joined to an
81. s added to the rule on RORRULE as follows RCRAPP1 VERIFICATION MSG 1 AND RCRAPP2 MODEL CDE D AND ROVST95 STYP CODE 9 AND ROVST95 STST CODE IN AS SA AND ROVST95 LEVL CODE UG RCRAPP1 CURR REC IND Y lt HERE The compiled rule is created in this order SELECT DISTINCT RCRAPP1 PIDM FROM FAISMGR RCRAPP1 FAISMGR RCRAPP2 FAISMGR ROVST96 HERE RCRAPP1 VERIFICATION MSG 1 AND RCRAPP2 MODEL CDE D AND ROVST96 STYP CODE 9 AND ROVST96 STST CODE IN AS SA AND ROVST96 LEVL CODE UG AND RCRAPP1 CURR REC IND Y AND RCRAPP2 PIDM RCRAPP1 PIDM AND ROVST96 PIDM RCRAPP1 PIDM AND RCRAPP2 INFC CODE RCRAPP1 INFC CODE AND RCRAPP2 SEQ NO RCRAPP1 SEQ NO AND RCRAPP2 AIDY CODE RCRAPP1 AIDY CODE AND ROVST96 AIDY CODE RCRAPP1 AIDY CODE AND RCRAPP1 AIDY CODE AIDY oo B AND RCRAPP1 PIDM PIDM Notes Since SQL processes from the bottom up the rule WITHOUT the manual RCRAPPI CURR REC IND Y rule finds the RCRAPPI columns in the order shown in column 1 and uses the nonunique RCRAPP1 CURR INDEX The column order of the rule WITH the manually add
82. s process All output from Job Submission is owned by jobsub GURJOBS is dependent upon the Oracle database pipe dbmspipe sgl The Printer Validation Form GTVPRNT defines the printers that are accessible to users Printers must be accessible from the network The database server must know about the printer and the print queue NOPRINT and DATABASE may be defined as printer codes but do not route to a printer The Process Maintenance Form GJAJOBS allows for maintenance of overall process attributes e Process name e Process description e Process type Pro C Pro COBOL etc Parameters for any given process can be maintained dynamically through SCT Banner forms e Parameter Definitions Form GJAPDEF e Parameter Value Validations Form GJAPVAL e Default Parameter Value Validations Form GJAPDFT The Process Submission Controls Form GJAPCTL creates a one up number to uniquely identify the job and passes the user entered parameters and the one up number to the table GJBPRUN At run time all processes access GJBPRUN for their parameters The Process Results Form GJARSLT allows for the review of the process log Output files All process output lis files reside in the jobsub home directory Log files log also reside in the jobsub home directory O SunGard 2004 2005 Financial Aid Technical Page E 3 Job Submission Process Flow Process flow 1 Process Maintenance Form GJAJOBS Define and maintai
83. sequence nextval from DUAL O SunGard 2004 2005 Financial Aid Technical Page B 23 Self Check Directions Use the information you have learned in this workbook to complete this self check activity Exercise 1 Data Write a simple script to use as a tool to list information about SCT Banner Dictionary Financial Aid tables to include table name table comment The script should include ability to ask user for name of table and should accept partial input Save your script to a temporary directory on your computer for future use Exercise 2 Write a simple script to use as a tool to examine constraints placed on tables by Referential SCT Banner designers Integrity Save your work Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page B 24 Self Check Continued Exercise 3 Write a script that will show constraint name column name position type of Referential constraint constraint status and table name Integrity Challenge Exercise 4 Write a simple query to get ID first name middle name last name and the General Person Change indicator for persons who have had changes made to their ID on the Identification Form SPAIDEN records Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page B 25 Self Check Continued Exercise 5 Write a simple query to get PIDM ID first name middle initial last name and Social Security number for persons who have had changes made to the
84. set echo on before running any of these scripts Press any key to continue Page C 3 Financial Aid Technical Section D Review of Financial Aid Process Overview In this section These topics are covered in this section D D 12 Z J AK o2 7 z z m CA O Qo DID DA Requirements Tracking Process Flow s a Need Analysis Online Recalculation Process Flow Reguirements Tracking Forms and Tables Reguirements Tracking Processes and Procedures D 17 RORRULE and ROOGSOL Student System Shared Data Forms wile S Student System Shared Data Tables and Views D 20 Student System Shared Data Process Flow Student System Process Flow Student System Shared Data Processes and Procedures NININIJIN BR ju inNIi D 27 D 28 D 29 D 30 D 31 D 32 D 33 D 34 Continued on the next page Need Analysis Verification Table Relationships Uy NIN Nn A It O SunGard 2004 2005 Financial Aid Technical Page D 1 Overview Continued In this section These topics are covered in this section cont IS Y TO Page Pell Disbursement Import New Y ear Roll Forms Tables and Modules Self Check D 69 Self Check Answer Key Logging O SunGard 2004 2005 Financial Aid Technical Page D 2 Financial Aid Process Student applies The student will fill out and submit for aid e Free Application for Federal Student Aid FAFSA e Student competes for Financial Ai
85. set of IDs for use in SCT Banner reports and letters and allows processes to be run using that subset of IDs such as people vendors or organizations It is used for reporting purposes or for facilitating Letter Generation How does Selects the PIDM for individuals who meet the selection criteria population SELECT pidm selection work FROM table VHERE criteria Primary Identification Master Population Selection involves four basic steps e Identify an application e Define the Population Selection criteria Extract the IDs that match the criteria Review the Population Selection SunGard 2004 2005 Financial Aid Technical Page F 2 Step 1 Identify an Application Application An application owns a grouping of Population Selections The rules established by the parent application will be inherited by the child objects e Examples General Recruiting Alumni Although the application must be defined in the Application Rules table GLRAPPL the rules criteria at this level are optional O SunGard 2004 2005 Financial Aid Technical Page F 3 Step 2 Define Criteria Example Example test female SELECT FROM VHERE AND AND AND AND spbpers pidm spbpers spriden spriden pidm spriden change ind spraddr stat code spbpers sex F spriden entity ind GLRSLCT spbpers pidm IS NULL T PA Uu P Population Selection Definition Rules table GLRSLCT e Allo
86. t record creation Use the information you have learned in this workbook to complete this self check activity e Other than data load how can new records be created e Explain what the process RCPDTMP does Exercise 2 Write a rule to capture in a tracking group those individuals that have a C RORRULE code on their current records You will need to create a tracking group and tracking code To complete this assignment you should be able to run ROBBGRP and place a requirement on student records that fit the above category Exercise 3 Write a script that will list the student ID aid year fund code and Paid Award Packaging and Amount for a student whose ID and aid year are entered as parameters disbursement Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 62 Self Check Continued Exercise 4 SAP Write a script to select ID name progress code and term for each student History coded in Banner for Satisfactory Academic Progress Some students are coded for more than one term If students have more than one term select the most current term and select that term s progress code The academic progress table is RORSAPR Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 63 Self Check Continued Exercise 5 Write a script to list the student ID aid year term code fund description and offered award amount for the term of a student whose ID and term code is en
87. ta Load for CSS PROFILE data only e Run RCPMTCH Part 2 of Data Load e Run RCRTPxx Part 3 of Data Load e It is important to run the RCPMTCH and RCRTPxx steps as close together as possible This decreases the chances of someone adding new students online which would make the New status as determined by RCPMTCH incorrect e Run the RCPDTMP Process SunGard 2004 2005 Financial Aid Technical Page D 11 Need Analysis Verification Forms and Tables Forms The Need Analysis process performs calculations that determine the contribution an applicant can make toward his her expenses The Verification process validates certain information provided on an aid application against the same data from other documents Seven character Proper name of form process report or table name Y ear specific Miscellaneous Results Inquiry Form RNAVRxx Y ear specific Need Analysis Document Verification Form RNAPURG Application Purge Form RNASLxx Y ear specific Student Loan Data System Form Seven character Proper name of form process report or table name RNRACMT Financial Aid Application Comments RNRGLBL INAS Global Policy Options Table Tables RNROCMT Financial Aid Application Override Comments ROROVRD Applicant Need Analysis Override Table RNRVRFY Needs Analysis Verification Table O SunGard 2004 2005 Financial Aid Technical Page D 12 Need Analysis Verification Table Relationships Diagram RNRVRFY RCRAPPI amy
88. tered as a parameter Allow for the use of wild card entry for both ID and term code Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 64 Self Check Continued Exercise 6 Write a script to return those students that have different EFCs Script should Database return student ID formatted name EFCs and indicate if record has a lock Structure Prompt the user for aid year Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 65 Self Check Continued Exercise 7 Write a script to select ID name unmet need EFC FWS work preference and Database total paid awards for each student Use wild card selection for aid year Structure Advanced Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 66 Self Check Continued Exercise 8 Modify your script from the previous exercise to select only students that Database indicated they wanted FWS for 1998 99 Structure Advanced Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 67 Self Check Continued Exercise 9 Modify your script from the previous exercise to select students where they Database have any awards paid for aid year Structure Advanced O SunGard 2004 2005 Financial Aid Technical Page D 68 Self Check Answer Key Exercise 1 Applicant record creation Exercise 2 RORRULE Exercise 3 Packaging and disbursement e Other than data lo
89. the aid year This field indicates if a lock exists on the Pell Grant index Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page K 10 Applicant Status Table Base Financial Aid Table RORSTAT Continued Column Required Data Type RORSTAT PRI SAR PGI NUMBER 5 0 This field indicates the primary SAR Pell Grant VARCHAR2 i index or EFC This field indicates whether the need analysis needs to DATE NUMBER 11 2 DATE RORSTAT RECALC NA IND be recalculated If the indicator is set then INAS will be run for this applicant in the next batch run The last date on which the resource amount column was updated The total amount of outside resources which will be used in the applicant s need calculation This field identifies the date that the SAR is complete and ready for payment This field maintains the original SAR initials as a part of the SAR identification number RORSTAT RESOURCE ACT DATE RORSTAT RESOURCE AMT 1 7 RORSTAT SAR DATE 7 2 RORSTAT SAR PGI LOCK IND This field identifies if a lock exists on the SAR Pell Grant index or EFC 9 This field maintains the original SAR Social Security number portion of the SAR identification number 2 This field maintains the SAR transaction number associated with the record This field maintains the student s contribution toward educational expenses from the student s current need analysis record
90. tion Comments Order Validation Table GLBVRBL Application Code is FINAID GLBVERM X VAR GLRVRBL x VAR o e ooo oo Letters Table Name Import Rule Area Description Comments Order Validation Table GTVLETR a that begin with and SAP GTVPARA Nr oo E ei GUBPARA X JR o GUBLETR X LTR oo ooo Job Submission parameter sets Table Name Import Rule Area Description Comments Order Validation Table Ca 1 d MR jesse IGIRIPRM x ds Too O SunGard 2004 2005 Financial Aid Technical Page K 6 Applicant Status Table Base Financial Aid Table RORSTAT Condition Records must exist in this table for each aid year the student has data before any other data is converted RORSTAT ACTIVITY DATE NOT NULL DATE 7 The date that information for this record in the table was entered or last updated RORSTAT AIDY CODE NOT NULL VARCHAR2 4 ROBINST The aid year to be associated with information in this record Ex 9091 9192 9293 etc RORSTAT AUTH DISB AID IND NOTNULL VARCHAR2 RORSTAT BGRP PROC IND NOTNULL VARCHAR O o RORSTAT LOCK IND NOT NULL VARCHAR2 1 This field indicates whether the student s Financial Aid application record is locked RORSTAT PCKG FUND PROC IND LL VARCHAR2 1 O RORSTAT PCKG PROC IND NOTNULL VARCHAR2 o RORSTAT PCKG STATUS IND NOTNULL VARCHAR 0 RORSTAT PGRP
91. topics are covered in this section Job Submission Overview E i SS i c ob Submission at the Functional Level ob Submission at the Database Level O SunGard 2004 2005 Financial Aid Technical Page E 1 Job Submission Overview Description The Job Submission process allows users to identify and define job parameters work with job scheduling and printing schedule jobs and control job printing How does Job e It defines the parameters used to execute any given process ri e It communicates with the database server environment to schedule the work process e It communicates with the database server environment to print the output of the process Diagram Job Submitter Client STEMP lis How does Oracle Database Server Job Submission work por y ey l Network Printer JOBSUB ID jobsub is a System user ID not an Oracle user ID jobsub starts Job Submission table GURJOBS for each database All reports lis files reside in jobsub s directory Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page E 2 Job Submission Overview Continued Start Job Submission Sleep Wake Establish printers Define processes Define process parameters Submit job Review results GURJOBS is a PRO C program created to handle requests in a client server configuration GURJOBS must be running in Sleep Wake mode prior to Job Submission The user ID jobsub should start thi
92. toric records STVSBGI Source Background Institution Code Validation Form STVSBGI e Source background institution codes must exist before you can load historic records with SBGI codes RTVTREQ Tracking Requirements Validation table RTVTREQ e All tracking requirements codes must exist before you can load historic records RTVTGRP Tracking Group Validation table RTVTGRP e All tracking group codes must exist before you can load historic records SunGard 2004 2005 Financial Aid Technical Page H 9 Award History and Transcripts Functional staff e Financial Aid Institution table ROBINST tables Fund Base Data table RFRBASE e Satisfactory Academic Progress Validation table RTVSAPR e Award Status Validation table RTVAWST e Award Status codes must exist here e Student Term Validation table STVTERM e Term codes must exist here before you can load historic records Technical staff e Fund Aid Year Specific Data table RFRASPC tables e Packaging and Disbursement uses aid year specific fund codes e Only required for mid year conversion e Satisfactory Academic Progress Rules table RORSAPR e Applicant Status Report RORSTAT e Applicant Award table RPRAWRD e Create award records one per aid year per PIDM per fund code O SunGard 2004 2005 Financial Aid Technical Page H 10 Conversion Financial Aid Applicant Status table RORSTAT Hold e Financial Aid Holds table RORHOLD e Financial Aid holds are linked to the stu
93. tudent History coded in Banner for Satisfactory Academic Progress Some students are coded for more than one term If students have more than one term select the most current term and select that term s progress code The academic progress table is RORSAPR set pagesize 60 set linesize 80 rem column ID format a9 column NAME format a30 column TERM format a8 column CODE format a8 rem spool PROGRESS lis rem select spriden id ID spriden last name spriden first name NAME x rorsapr term code TERM x rorsapr sapr code CODE from spriden spbpers rorsapr x where x rorsapr term code select max y rorsapr term code from rorsapr y where y rorsapr pidm x rorsapr pidm and x rorsapr pidm spriden pidm and x rorsapr pidm spbpers pidm and spbpers dead ind is null and spriden change ind is null and spriden entity ind P order by 2 spool off Continued on the next page O SunGard 2004 2005 Financial Aid Technical Page D 70 Self Check Answer Key Continued Exercise 5 Write a script to list the student ID aid year term code fund description and offered award amount for the term of a student whose term code is entered as a parameter Allow for the use of wild card entry for term code select spriden id id rpratrm aidy code aidy rpratrm term code term code rfrbase fund title fund desc rpratrm offer amt offered amt from rfrbase spbpers rpratrm spriden where spriden pidm rpratrm
94. ws for entry of the specific criteria which distinguishes one population from another e Ifthe tables are joined by PIDM the PIDM join is automatic e Ifthe tables are joined by additional columns the conditions must be explicitly defined When the form is exited the process Parameter Selection Process GLBPARM compiles the SELECT statement O SunGard 2004 2005 Page F 4 Financial Aid Technical Step 3 Extract the IDs GLBDATA Run the Population Selection Extract Process GLBDATA from Job Submission Population Selection Extract Process GLBDATA parameters Parameter Y Selection Identifier 1 Enter in all capital letters Selection Identifier 2 Used only for Union Intersect or Minus functionality Oy Le Used only for Union Intersect or Minus functionalit LO ia Used only for Union Intersect or Selection Minus functionality EE EL Enter U nion Dntersect Used only for Union Intersect or M inus Minus functionality Y Application Enter in all capital letters Y Enter in all capital letters Detailed Execution Report O SunGard 2004 2005 Financial Aid Technical Page F 5 Step 4 Review Population Selection GLAEXTR Population Selection Extract Data Form GLAEXTR e Review the results of running Population Selection Extract Process GLBDATA e Add other IDs not processed by GLBDATA e Remove IDs processed by GLBDATA O SunGard 2004 2005 Financial Aid Technical Page F 6 Population S
95. y SCT Banner product owning the form report process or table Some common examples follow A Advancement Human Resources Payroll Personnel F Finance R_ Financial Aid N Position Control T Accounts Receivable Position 2 Identifies module that owns the form report process or table The letter assignments will vary by SCT Banner System The following table lists the letter assignments when position 1 R Financial Aid Budgeting J Student Requirements Employment Tracking C Record Creation L Logging S Student System Shared Data Electronic Data Need Analysis Validation Form Exchange EDE Table E Funds Management O Common Functions History and Packaging and Transcripts Disbursement Continued on the next page SunGard 2004 2005 Financial Aid Technical Page B 5 Naming Conventions Continued Position 3 Identifies the type of form report process or table Application form Q Queyfom Base table Batch Rule table repeating table COBOL process or report process Inquiry form General maintenance temporary table E Online COBOL process Validation form table or E IP Process or Procedure Positions 4 7 A unique four character acronym identifying the form report process or table Client W Y or Z can be used as the first character for client developed non SCT developed items Banner applications built to coexist with SCT Banner applications For client developed reports or m

Download Pdf Manuals

Related Search

Related Contents

Nikkor ED 1200mm f/11 IF  Presto Scandinavian Design User's Manual    Cables Direct B6-505Y networking cable  取扱説明書    Startup Guide  ESPECIFICACIÓN DE PRODUCTO COMIVER Sofrito de cebolla y      

Copyright © All rights reserved. Failed to retrieve file