10 th Anniversary 1999 - 2009

1. 10th Anniversary 1999 - 2009 Population Selections, RORRULEs, and Web Text – Oh My! Using SQL to Create Rules in Banner Financial Aid

2. General Announcements: • Please turn off all cell phones/pagers • If you must leave the session early, please do so as discreetly as possible • Please avoid side conversations during the session • Questions will be answered ….. Thank you for your cooperation

3. Millersville University • 8,300 students • About 70% receive aid • Banner Financial Aid Version 8.5 • Banner General Version 8.2

4. University of Scranton • 5,800 Students • Banner Financial Aid Version 7.15 • Banner General Version 7.5 • Banner 8 Conversion set for January 2010

5. What is SQL? • Structured Query Language • A way to ask the database questions • Some uses in Banner Financial Aid: • Population Selections • Variables • Selection Rules • Web Text Rules

6. Where To Start? • Define your question: • Who has a given fund code awarded? • Who needs to have loan fees posted to their budget? • Who is independent only because of a positive response to the emancipated minor question on the FAFSA? • Who should be assigned to a given budget group?

7. Translating To SQL • What data items in Banner would help you answer your questions? • Find the field names of each of these items • Place your cursor in the field • Select ‘Help’ from the menu bar • Select ‘Dynamic Help Query’ • The Field is displayed

8. Building a Population Selection • GLRSLCT • Enter the Application (FINAID) • Enter the name of your pop-sel in the Selection ID • Enter a brief description • Definition: • Select – generally a PIDM • From – include names of all tables involved • Define your Rules

9. Pop-Sel Example 1

10. Run the Pop-Sel using GLBDATA

11. Pop-Sel Example 2 • Using Union/Intersect/Minus • Define the two groups by writing two separate pop-sels • Example: Post loan fees to budgets of those students who indicated interest in loans on the FAFSA and who do not already have loan fees included in their budgets • Pop-sel part 1 – select all interested in loans • Pop-sel part 2 – select all with loan fees on budget

12. Run the Pop-Sel

13. Using a Variable in a Pop-Sel • A variable is a set of rules that pulls a specific piece of data from the database. For example, variables can be written to pull a student’s name, street address, city, state, ZIP, grade level, total aid awarded, etc. They are often used in letter generation. • When used in a pop-sel, a variable can help to limit the results to a defined group or to exclude a defined group.

14. Example of a Variable

15. Example of aVariable in a Pop-Sel

16. Pop-Sel Tips • Dynamic parameters allow a pop-sel to be used for different aid years, fund codes, etc. Example: RPRAWRD_AIDY_CODE = &aid_year_code • When using a variable in a pop-sel, it must be the last condition entered on GLRSLCT and it must be entered as a Value—not as a Data Element. • If you make changes to a variable, you must recompile the pop-sel in order for the revised variable to be recognized. • When using multiple tables, be sure to join common fields.

17. RORRULE • RORRULEs can be written for a variety of purposes but all are written in the same way

18. RORRULE in Simple Mode

19. RORRULE in Simple Mode (Cont.)

20. RORRULE in Expert Mode • Access Expert Mode by either performing Next Block from Simple Mode or by selecting Options and then Compiled/Expert SQL Code.

21. RORRULE in Expert Mode (Cont.)

22. SQL From Expert Mode • SELECT DISTINCT(RCRAPP1_PIDM) • FROM RCRAPP1,RCRAPP3,RZVST10 • WHERE RCRAPP1_CURR_REC_IND = 'Y' AND • RCRAPP1_INST_HOUS_CDE = '2' AND • RCRAPP3_YR_IN_COLL_2 < '5' AND • RZVST10_RESD_CODE = 'Y' AND • RZVST10_STST_CODE = 'AS' AND • RCRAPP3_PIDM =RCRAPP1_PIDM AND • RZVST10_PIDM =RCRAPP1_PIDM AND • RCRAPP3_INFC_CODE = RCRAPP1_INFC_CODE AND • RCRAPP3_SEQ_NO = RCRAPP1_SEQ_NO AND • RCRAPP3_AIDY_CODE = RCRAPP1_AIDY_CODE AND • RZVST10_AIDY_CODE = RCRAPP1_AIDY_CODE AND • RCRAPP1_AIDY_CODE = :AIDY AND • RCRAPP1_PIDM =:PIDM

23. RORRULE Tips • If your rule is not producing the desired results, examine it in Expert mode. One thing to check is that there may be a join you are not expecting—like to :TERM when you don’t mean for the rule to be term-specific. This can be identified and removed when using Expert mode.

24. Web Text Rules • Web Text Rules allow you to write a rules that will, in the end, display text in Self Service for students who meet the conditions of each rule • You determine on which tab(s) the text appears • There is no Simple Mode—only Expert Mode

25. Web Text Set-Up • Define the web text code on RTVWTXT • Write the web text rule and define the text on RORWTXT • Define the sequence in which the web text will appear on each tab on RORWTAB (text will not appear unless this final step is completed)

26. RORWTXT – Example 1

27. RORWTXT – Example 1 (Cont.) • SQL from Example 1: select 'X' from RPRARSC where RPRARSC_AIDY_CODE = :AIDY and RPRARSC_PIDM = :PIDM and (RPRARSC_ACTUAL_AMT > 0 or (RPRARSC_EST_AMT > 0 and (RPRARSC_ACTUAL_AMT <> 0 or RPRARSC_ACTUAL_AMT is null)))

28. RORWTXT – Example 1 (Cont.)

29. RORWTXT Example 1 onSelf Service

30. RORWTXT – Example 2

31. RORWTXT – Example 2 (Cont.)

32. RORWTXT – Example 2 (Cont.) • Text includes a link • You have UNSATISFIED document requirements which are preventing the awarding of some or all of your aid. Please review your <a href="https://muhp5.millersville.edu:4433/pls/muhp3_pprd/bwrkelig.P_DisplayTabs" title="Outstanding Document Requirements."> Outstanding Document Requirements.</a>

33. RORWTXT Example 2 on Self Service

34. RORWTXT – Example 3 • select rorsapr_sapr_code from rorstat,rorsapr,robnyud where rorstat_aidy_code = :AIDY and rorstat_pidm = :PIDM and rorsapr_pidm = :PIDM and robnyud_pidm = :PIDM and rorsapr_sapr_code = (Select RORSAPR_SAPR_CODE from rorsapr where rorsapr_pidm = :PIDM and rorsapr_term_code = (select max(rorsapr_term_code) from rorsapr AL2 where AL2.rorsapr_pidm = :PIDM)) and robnyud_value_22 is null

35. RORWTXT – Example 3 (Cont.)

36. RORWTXT Example 3 onSelf Service

37. RORWTXT Tips • When writing your Select statement, do not select a PIDM—you need something that will return the same value(s) for all students • Purchase a basic HTML book if you don’t know HTML

38. General SQL Tips • Be sure you are typing the name of the table, not the form, when you are entering your SQL (RPRAWRD – not RPAAWRD) • Know your data!

39. Documentation • Population Selections • Banner General User Guide – Chapter 5 • Banner General Population Selection Training Workbook • RORRULE • Banner Financial Aid Requirements Tracking Training Workbook • Banner Financial Aid Requirements Budgeting Training Workbook • Banner Financial Aid Requirements Packaging Training Workbook • Web Text • Banner Release Guide 8.2 • Financial Aid Self Service Training Workbook 8.1 • General Financial Aid • Banner Financial Aid User Guide

40. Open to the Floor • Questions • Comments

41. Contact Information • Tammy R. Bittner Financial Aid Systems Administrator Millersville University tammy.bittner@millersville.edu 717-872-3028 • John Tabor Senior Application Developer University of Scranton taborj1@scranton.edu 570-941-4206