# GLRVFRM

GLRVFRM_SELECT_CLAUSE:
SHRTCKN_SUBJ_CODE||' '||SHRTCKN_CRSE_NUMB||' '||SHRTCKN_SEQ_NUMB||' Grade: '||SHRTCKG_GRDE_CODE_FINAL


GLRVFRM_DESC:
Returns 'UNOFFICIAL RECEIVED' if HS Transcript received


GLRVFRM_DESC:
Returns 'UNOFFICIAL NOT RECEIVED' if HS Transcript NOT received


GLRVFRM_DESC:
Returns one-half of base rate for terms endinmg in 20.


GLRVFRM_SELECT_CLAUSE:
STVSBGI_DESC||'  '||DECODE(SORDEGR_DEGC_CODE,'000000','No Degree',SORDEGR_DEGC_CODE)||' Major: '||STVMAJR_DESC||' '||SORDEGR_ATTEND_FROM||' to '||SORDEGR_ATTEND_TO||DECODE(SORDEGR_DEGC_DATE,NULL,NULL,' Awarded: '||SORDEGR_DEGC_DATE)


GLRVFRM_SELECT_CLAUSE:
SHRTCKN_CRN||' '||SHRTCKN_SUBJ_CODE||' '||SHRTCKN_CRSE_NUMB||' '||SHRTCKN_SEQ_NUMB||' '||SHRTCKN_CRSE_TITLE||' '||SPRIDEN_LAST_NAME||', '||SPRIDEN_FIRST_NAME


GLRVFRM_DESC:
Returns 'RENEW" for students returning to housing
Returns 'RENEW' for returning


GLRVFRM_DESC:
Returns 'NEW" for new students in housing


GLRVFRM_DESC:
Returns BIG 3 for students with HAPS CODES IN ('FN','FR','GN','GR','TN','TR')


GLRVFRM_DESC:
Returns MEDICAL for HAPOS codes in ('ME','HE')


GLRVFRM_DESC:
Returns phrase SPRING for any Spring housing app


GLRVFRM_DESC:
Returns phrase FALL for any FALL housing app


GLRVFRM_DESC:
Returns BANNER term description for terms ending in 20


GLRVFRM_DESC:
Creates a prefix based on GENDER if prefix does not exist.


GLRVFRM_DESC:
Returns phrase 'Academic Year plus calculated year for terms ending in 10


GLRVFRM_DESC:
Counselor email for All freshmen apps regardless of citizenship


GLRVFRM_DESC:
Counselor email for All US RETURNING or TRANSFER apps last name M or after


GLRVFRM_DESC:
Returns SPRING  for any term ending in 20


GLRVFRM_DESC:
Returns SUMMER  for any term ending in 30


GLRVFRM_DESC:
Returns decision code for latest decision that is an ACCEPT


GLRVFRM_DESC:
Returns DENY for all current denied decisions


GLRVFRM_DESC:
Latest decision code for preferred application.


GLRVFRM_SELECT_CLAUSE:
SSBSECT_CRN||’ ‘||SSBSECT_SUBJ_CODE||’ ‘||SSBSECT_CRSE_NUMB||’.’||SSBSECT_SEQ_NUMB||' Course: '||SSBSECT_CRSE_TITLE


GLRVFRM_DESC:
School - Course number name CRDS: number of credits


GLRVFRM_SELECT_CLAUSE:
SHRTRCE_SUBJ_CODE||' '||SHRTRCE_CRSE_NUMB||': '||SHRTRCE_CRSE_TITLE||' CRDS: '||SHRTRCE_CREDIT_HOURS


GLRVFRM_DESC:
Value based on DECISION=a, STUDENT TYPE, ADMIT TYPE=N


GLRVFRM_DESC:
Value based on DECISION=A, STUDENT TYPE, ADMIT TYPE=t


GLRVFRM_DESC:
Value based on DECISION=P, STUDENT TYPE, ADMIT TYPE


GLRVFRM_DESC:
Banner degree description EXCEPT for degrees BSME and BAAP


GLRVFRM_SELECT_CLAUSE:
Bachelor of Science  in Mechanical Engirneering'

GLRVFRM_DESC:
Creates a degree description for degree code BSME


GLRVFRM_SELECT_CLAUSE:
'Bachelor of Arts in Applioed Procrastination'

GLRVFRM_DESC:
Creates a degree description for degree code BAAP


GLRVFRM_DESC:
Degree level plus apostrophe 's' for Bachelor and Master degree levels


GLRVFRM_DESC:
Banner degree level description for Doctorate


GLRVFRM_SELECT_CLAUSE:
SSRMEET_MON_DAY|| SSRMEET_TUE_DAY|| SSRMEET_WED_DAY|| SSRMEET_THU_DAY|| SSRMEET_FRI_DAY|| SSRMEET_SAT_DAY|| SSRMEET_SUN_DAY||


GLRVFRM_SELECT_CLAUSE:
SPRIDEN_LAST_NAME||', '||SPRIDEN_FIRST_NAME||': Probation ends on '||TO_CHAR(NBRBJOB_PROBATION_END_DATE,'FMMonth dd, yyyy')


GLRVFRM_DESC:
Adds 124 days to the day a specifikc letter is added to SUAMAIL


GLRVFRM_DESC:
Returns a Y if  the finaid award letter has been generated for that aid year


GLRVFRM_DESC:
Gets the REVISED COMMENT beginning with the 5th character in the comment


GLRVFRM_DESC:
Returns August 1, term code year  for terms ending in 09


GLRVFRM_DESC:
Returns December 1,  term code year code  + 1 year  for terms ending in 01


GLRVFRM_DESC:
Returns May 1,  term code  for terms ending in 06


GLRVFRM_SELECT_CLAUSE:
TO_CHAR(SARAPPD_APDC_DATE + 31,'fmMonth dd, yyyy')


GLRVFRM_DESC:
FALL TERMS: returns May 1 with year one less that year in term code


GLRVFRM_DESC:
SPRING TERMS: returns October 1 with one year less that year in term code


GLRVFRM_DESC:
Summer TERMS: returns April 1 with year equal to year in term cocde.


GLRVFRM_SELECT_CLAUSE:
TO_CHAR(SYSDATE + 31,'fmMonth dd, yyyy')


GLRVFRM_DESC:
How many days between 01-FEB and decision date for FALL apps


GLRVFRM_DESC:
'WINTER ENGINEERING' college is EN and term is a winter term


GLRVFRM_DESC:
'SUMMER ENGINEERING'  -college is EN and term is a summer term


GLRVFRM_DESC:
'WINTER NOT ENGINEERING' - college is NOT EN and term is a winter term


GLRVFRM_DESC:
'SUMMER NOT ENGINEERING' - college is NOT  EN and term is a winter term


GLRVFRM_SELECT_CLAUSE:
TO_CHAR(RPRADSB_DISBURSE_DATE,'FMMonth DD, YYYY')||'   '||TO_CHAR(RPRADSB_DISBURSE_AMT,'$999,999.99')||'   '||RFRBASE_FUND_TITLE


GLRVFRM_DESC:
Counselor email for US APPS, last name A-Ls, R or T apps


GLRVFRM_DESC:
Counselor email for All INTERNATIONAL STUDENTS


GLRVFRM_SELECT_CLAUSE:
TO_CHAR(TO_DATE(AND HERE PUT IN THE TIME FIELD YOU WANT TO CONVERT,'HH24MI'),'HH:MI AM')

GLRVFRM_FROM_CLAUSE:
TABLE NAME WHERE THE FIELD YOU'RE CONVERTING COMES FROM


GLRVFRM_SELECT_CLAUSE:
STVTESC_DESC||'   : DATE TAKEN: '||TO_CHAR(SORTEST_TEST_DATE,'fmMonth dd, yyyy')||' - SCORE: '||SORTEST_TEST_SCORE


GLRVFRM_DESC:
How many days between 01-NOV and decision date for SPRING apps


GLRVFRM_DESC:
How many days between 01-FEB and decision date for SUMMER apps


GLRVFRM_SELECT_CLAUSE:
'TRANS RECEIVED ON:  '||SORPCOL_TRANS_RECV_DATE||'     '||STVSBGI_DESC||'  '||STVDEGC_DESC||' '||STVMAJR_DESC

GLRVFRM_DESC:
TRANS RECEIVED ON: DATE  SCHOOL NAME  MAJOR


GLRVFRM_SELECT_CLAUSE:
RTRIM(STVSBGI_DESC)||' from '||SORDEGR_ATTEND_FROM||' to '||SORDEGR_ATTEND_TO


GLRVFRM_FROM_CLAUSE:
SLRASSG A, SPRTELE, SLRASSG B, SPVADDS, STVASCD

GLRVFRM_DESC:
Rmate phone if listed, othwrise UNLISTED


GLRVFRM_DESC:
has NO comma or stata for non US with no state field


GLRVFRM_SELECT_CLAUSE:
STVTESC_DESC||' - SCORE: '||SORTEST_TEST_SCORE||CHR(10)||CHR(35||'S 1'


GLRVFRM_SELECT_CLAUSE:
'CLASS AND RANK: '||SORHSCH_CLASS_RANK|| '/' ||SORHSCH_CLASS_SIZE


GLRVFRM_SELECT_CLAUSE:
'NO CLASS RANK AND SIZE.  PERCENTILE RANK IS '||SORHSCH_PERCENTILE


GLRVFRM_SELECT_CLAUSE:
DECODE(SUBSTR(SAVCCUR_TERM_CODE,5,1),'1','Spring','2','Summer or Fall','3','Summer or Fall')


# GLRVRBL

GLRVRBL_VALUE:
‘Y’ (FIND OUT HOW THIS IS INDICATED ON THE TABLE


# Switchboard Items

ItemText:
REPORT: LIST OF VARIABLES BY CATEGORY- NO RULES


ItemText:
REPORT: VARIABLES BY CATEGORY WITH RULES


ItemText:
REPORT: VARIABLES BY ANYTHING IN THE SELECT LINE


ItemText:
REPORT: VARIABLES WITH SQL FUNCTIONS-SEARCH BY SQL FUNCTION


ItemText:
REPORT: NON FIRST TYPE NAME AND ADDRESS VARIABLES


ItemText:
REPORT: VARIABLES THAT CONTAIN SUB QUERIES


ItemText:
POP SEL AND VARIABLE DATA ENTRY AND SEARCH FORMS


ItemText:
VARIABLE SEARCH BY SELECT LINE AND TABLE NAME


ItemText:
DATA ENTRY AND SEARCH: POP SELS AND VARIABLES


ItemText:
LIST OF CATEGORIES OF VARIABLES AND POP SELS


ItemText:
CLOSE MAIN SWITCHBOARD AND RETURN TO ACCESS


ItemText:
REPORT: POP SELS USED WITH OTHER POP SELS


# tbl_SQL_FUNCTIONS

FUNCTION:
ADD_MONTHS(date field, number of months to add or subtract)

DESCRIPTION:
Returns date plus or minus a number of months

EXAMPLE OF CODE:
SELECT: ADD_MONTHS(SARAPPD_APDC_DATE,2)   OR   
SELECT: ADD_MONTHS(STVTERM_END_DATE,-1)

RESULT:
If decision date is March 15, 2010, will return the date 2 months later: May 15, 2010.  OR  
If TERM END DATE is March 15, 2010, will return the date 1 month earlier:February 15, 2010

IDEAS FOR USING THE FUNCTIONS:
To return a deadline based on the date of the decision


DESCRIPTION:
Lets you insert an ascii character, such as a hard return to produce a blank line

EXAMPLE OF CODE:
SELECT: STVTESC_DESC||' - SCORE: '||SORTEST_TEST_SCORE||CHR(10)||CHR(35)||'S 1'

RESULT:
The CHR(10) inserts a hard return between each line that the variable returns.

IDEAS FOR USING THE FUNCTIONS:
Useful if you want to separate multip[le returns produyced by the same variable, for instance cheklist items


DESCRIPTION:
The || (the one verical line character on the keyboard above the backward slash; use 2 of them) lets you list fields or fields and and text on the select line. Literal text must be in single quotes.

EXAMPLE OF CODE:
SELECT: STVTESC_DESC||' - SCORE: '||SORTEST_TEST_SCORE

IDEAS FOR USING THE FUNCTIONS:
Useful for displaying fields and some value connected to them


DESCRIPTION:
Counts the number of times the field exists

RESULT:
Will return the number of times X letter has been sent for the term specified in the rest of the variable rules

IDEAS FOR USING THE FUNCTIONS:
To create an indicator you can use to insert different WORD text based on the number of times a letter has been sent


DESCRIPTION:
Lets you use the value in a field to generate another value

RESULT:
If the gender  is F, return Ms.; if the gender is M, return Mr. in all other cases return nothing.

IDEAS FOR USING THE FUNCTIONS:
To 'manufacture' data that doesn';t exist in Banner based on data that does.


DESCRIPTION:
Sets the first character in each word to uppercase and the rest to lowercase

RESULT:
Returns the student type description in mixed case regardless of how it exists in the database: New Freshman is returned even if the description is new freshman. You can also accomplish this by using a SWITCH function in WORD. See WORD Help for SWITCHES.

IDEAS FOR USING THE FUNCTIONS:
To make Banner descriptions more attractive in letters. You can also accomplish this by using a SWITCH function in WORD. See WORD Help for SWITCHES.


FUNCTION:
INSTR(any field, the character to look for)

DESCRIPTION:
Returns the NUMBER of the space occupied by a particular character. This is pretty advanced stuff!!

EXAMPLE OF CODE:
write example--looking for colon in sjfc variable


RESULT:
Returns February 28 if the decision date is any day in February

SAMPLE VARIABLE:
do last day if diff between date and last day is > 15, othwerwise sysdate plus 15


RESULT:
Returns freshman if student type description is New Freshman. You can also accomplish this by using a SWITCH function in WORD. See WORD Help for SWITCHES.

IDEAS FOR USING THE FUNCTIONS:
Lower case may be more appropriate in the context of the WORD letter.You can also accomplish this by using a SWITCH function in WORD. See WORD Help for SWITCHES.


FUNCTION:
LPAD(character field, number of spaces to pad)

DESCRIPTION:
Adds n spaces to the left (leading)  side of the field


FUNCTION:
LTRIM(character field, number of spoaces to trim))

DESCRIPTION:
Adds  n spaces to the right (trailing) side of the field. To remove all trailing spaces, do not include n


DESCRIPTION:
Returns the maximum value for the field specified

RESULT:
Returns the HIGHEST  test score for the test specified..

IDEAS FOR USING THE FUNCTIONS:
Useful in subqeries based on the HIGHEST (or latest)  value in a field


DESCRIPTION:
Returns the minimum value for the field specified

RESULT:
Returns the LOWEST test score for the test specified..

IDEAS FOR USING THE FUNCTIONS:
Useful in subqeries based on the LOWEST (or earliest)  value in a field


FUNCTION:
MONTHS_BETWEEN(end date field, start date field)

DESCRIPTION:
Number of months between the 2 dates (integer)

RESULT:
Number of months between the APPLICATION date and today. BUT it returns a fraction OF THE MONTH BASED ON A 31 DAY MONTH; some conversion may be necessary. Could use TRUNC? To create full months?


FUNCTION:
NEXT_DAY(date field, day of week to return a date for)

DESCRIPTION:
Returns the DATE of the first NAMED DAY that is greater than a date.It can return, for instance, the first Monday after a certain date

EXAMPLE OF CODE:
SELECT: NEXT_DAY(SYSDATE+15, 'MON')
Options are SUN, MON, TUE, WED, THU, FRI, and SAT

RESULT:
If today is Friday, March 15, 2010, this returns the date for the MONDAY following an interval of 15 days.

IDEAS FOR USING THE FUNCTIONS:
This is useful when creating deadlines in a letter and you want to be sure the deadline works out to be a Monday, for instance.


FUNCTION:
NVL(field name, what value to insert if the field is null)

DESCRIPTION:
Lets you substitute a value when a null value is encountered.
The syntax for the NVL function is:
NVL( string1, replace_with )
string1 is the string to test for a null value.
replace_with is the value returned if string1 is null.

RESULT:
Bobby (where the First name is Robert and preferred name is Bobby) or Robert (where there is no preferred name)

IDEAS FOR USING THE FUNCTIONS:
Useful when you want to use the preferred nmame if it exists (say in the internal salutation). Have another variable that pulls just the first name and use that variable in the address.


FUNCTION:
ROUND(number field, n=number of places to round to); if do not specify an ' n,' then it rounds either UP or DOWN to the nearest whole number

DESCRIPTION:
n rounded to d decimal places (d defaults to 0)If no d is speciefied It rounds to the nearest integer, EITHER UP OR DOWN!

EXAMPLE OF CODE:
SELECT: ROUND(SHRTGPA_GPA,2)
SELECT: ROUND(SHRTGPA_GPA,3)
SELECT: ROUND(SHRTGPA_GPA)
SELECT: ROUND(SHRTGPA_GPA)

RESULT:
Rounds a GPA of 3.556789 to 3.56
Rounds a GPA of 3.556789 to 3.557
Rounds a GPA of 3.556789 to 4.0 (higher whole number if over mid point)
Rounds a GPA of 3.456789 to 3 (to lower whole number if below midpoint)

IDEAS FOR USING THE FUNCTIONS:
Be careful with this one; you probably don’t want to round GPAs, but you might want to round dollar amounts


DESCRIPTION:
Adds n spaces to the right side of the field

RESULT:
Adds 'n' spaces to the right of the field


DESCRIPTION:
Removes n spaces from the right side of the field. To remove all trailing spaces, do not include n

RESULT:
Trims 'n' spaces from the right of the field


FUNCTION:
SUBSTR(field name, number of the space to start with, how many spaces to include)

DESCRIPTION:
Returns the data beginning with the character in the space to start with, and up to and including the number of characters specified.

RESULT:
Looks at the FIRST name, starts with the FIRST character and returns 1 character. This would give you the initial.
Looks at the room number, starts with the FIRST character and returns 3 characters: 3 if the room number is 367. This could be used to create a FLOOR number in housing.

IDEAS FOR USING THE FUNCTIONS:
Useful any time you want to return just part of a field:  based  first character of the first name, or the last 4 of the SSN, or the first 3 of the last name.


DESCRIPTION:
Sum of values of  in the field, ignoring NULLs

RESULT:
Adds all the individual detail code balances to create a TOTAL BALANCE

IDEAS FOR USING THE FUNCTIONS:
Use this whevener you need to ADD together values


RESULT:
If today is Friday, March 15, 2010, this adds 15 days to today's date and returns March 31, 2010..

IDEAS FOR USING THE FUNCTIONS:
This is useful when creating deadlines based on the number of dayus that elapse after a base date, which could be today (SYSDATE) or the date of a decision.


DESCRIPTION:
Converts to character String and lets you format it

EXAMPLE OF CODE:
SELECT: TO_CHAR (SYSDATE,'fm Month  dd, yyyy')  OR  SELECT: TO_CHAR(SUM(TBRACCD_BALANCE),'$999,999.99')

RESULT:
Today's date in the format May 15, 2010.
the fm eliminates any leading blanks (as would be the case since the month field is as long as the longest month name ad May would have lots of leading spaces).
Sums the balance, takes the dollar amount, and converts it to a character and formats it as $1200.44 (as an example)

IDEAS FOR USING THE FUNCTIONS:
The fm makes sure the resulting format looks good. The TO_CHAR is usefuil for setting that format. You could format the SPBPERS_BIRTH)_DATE as 'fmMonth dd' and that would return birthDAY rather than birth DATE, July 4 instead of July 4, 1776.


DESCRIPTION:
This lets you do wonderful things with the date

RESULT:
Returns the name of DAY of the week of the contact

IDEAS FOR USING THE FUNCTIONS:
Useful in letters from recruiters to contacts: "it was nice to see you on XXXXX. (where XXX is the DAY, as in Wednesday)


FUNCTION:
TO_NUMBER(any field that looks like a number)

DESCRIPTION:
Convert to numeric format so the value can now be treated as a number

RESULT:
Takes the 1st 4 characters of the term code, and converts them to a number so that you can  use it as a number, for instance to ADD 1 to the year and make it the next year

IDEAS FOR USING THE FUNCTIONS:
Great for creating deadlines that are in the next year.


FUNCTION:
TRANSLATE(field name, character/s to be replaced, character/s to replace them with)

DESCRIPTION:
Replace every occurrence of search_str with replace_strunlike REPLACE() if replace_str is NULL the function returns NULL

RESULT:
Replaces every 1 in the original with a 5
every 2 with a 9, every 3 with a 6, If the ID were
112233456, it would be returned as 559966456. It would do it wherever 1 or 2 or 3 occurred.

IDEAS FOR USING THE FUNCTIONS:
Useful to 'scramble' data in the data base, for instance IDs or SSNs. To manufacture news IDs.


DESCRIPTION:
Date truncated to first date unit specified in the format, for instance the first date of the month, or the quarter, or the year.  When a date format is used by TO_CHAR or TO_DATE they return part of the date/time. When used by TRUNC they will return the

EXAMPLE OF CODE:
TRUNC(TO_DATE('22-AUG-11'), 'YEAR') (to first day of YEAR)
TRUNC(tTO_DATE('22-AUG-11'), 'Q') (to first day of QUARTER )
TRUNC(TO_DATE('22-AUG-11), 'MONTH') (to first day of MONTH)
TRUNC(TO_DATE('22-AUG-11'), 'DDD') (to the same DAY, without time stamps)
T

RESULT:
The DATE was of the first day of the year 2011
The DATE was of the first day of the QUARTER that includes the month of August, 2011
The date of the first day of the MONTH of August
The date of the first day of the WEEK that includes August 22, 2011


FUNCTION:
TRUNC(field name, number of spaces to truncate it)

DESCRIPTION:
Truncates the field to to the specified number of  decimal places; if you leave off a number, it defaults to no decimal spaces

EXAMPLE OF CODE:
SELECT: TRUNC(SHRTGPA_GPA,2)
SELECT: TRUNC(SHRTGPA_GPA,3)
SELECT: TRUNC(SHRTGPA_GPA)

RESULT:
Truncates a GPA of 3.456789 to 3.45
Truncates a GPA of 3.456789 to 3.456
Truncates a GPA of 3.456789 to 3.0 (it truncates it DOWN  to the lower whole number if less than the midpoint; it truncates it UP to the next whole nuimber if above the midpoint)


DESCRIPTION:
Returns the data in the charttacter field in all uppercase

RESULT:
Returns data in field in all uppercase regardless of original format. Smith or smith, or smIth becomes SMITH. You can also accomplish this by using a SWITCH function in WORD. See WORD Help for SWITCHES.

IDEAS FOR USING THE FUNCTIONS:
Useful to produce uppercase versions of Banner data, for instance for address labels. You can also accomplish this by using a SWITCH function in WORD. See WORD Help for SWITCHES.

SAMPLE VARIABLE:
DO--last name_upper; can also do in WORD


# tbl_VARIABLE_TYPE

VARIABLE_TYPE_DESC:
The Letter Extract Process (GLBLSEL) processes this variable first.  If the variable returns data, then GLBLSEL processes all the other variables in the letter. If the variable does not return any information for a PIDM, then GLBLSEL does not continue to process the other variables for that PIDM. FIRST variable  function like the delivered name and address variables (which are based on the view SPVADDS), but can be created for any field in Banner. Create a FIRST type variable to look at a field the PIDM MUST have to have data returned. An example is email address. If you use a FIRST type variable, do NOT include any of the delivered name and address (SPVADDS) variable. Create new variable to return the name and address info you want, but base them on the base tables NOT the SPVADDS view.


VARIABLE_TYPE_DESC:
Manual pop sels and variables are pop sels and variables in which the user specifies all the joins, including the PIDM joins. The default for the forms GLRSLCT and GLRVRBL is an automatic join on the main PIDMs of the selected tables. Sometimes you may want to join on a secondary PIDM on one of the tables. For instance, you may want to join on the advisor's PIDM (SGRADVR_ADVR_PIDM) rather than the STUDENT'S PIDM (SGRADVR_PIDM) on the SGRADVR table to use information ABOUT the advisor to SELECT  the student in a pop sel, or to pull information ABOUT the advisor FOR the student in a variable.


VARIABLE_TYPE_DESC:
The variable looks at one of the following Financial Aid views to determine its value:

ROVCSUM  Third Party Contract Payment Detail
ROVESUM  Billing Exemption Payment Summary
ROVSCRD  Current Enrollment
ROVSTOD  Basic Student Selection
ROVTHST  Summary Academic History Level and Term Selection
ROVTRMG  GPA By Term and Level Selection
RPVARCO  Contract Resource
RPVAREX  Exemption Resource
RPVATRM  Award by Term
RPVTERM  Applicant Award Term


VARIABLE_TYPE_DESC:
The value of the variable contains information regarding checklist items associated with an admissions application.  The checklist type performs a very specific function as is only used for variables that extract Banner checklist items.  The delivered variable *CHKL extracts the list of missing checklist items. Because its variable TYPE is Checklist, it also has two other effects in Banner. It updates the count of the number of times the*CHKL has been used to extract a checklist item, and thus you know how many times you have requested that particular item. It also updates the ‘first request’ and ‘last request’ fields connected to each checklist item, so you can see the first time you requested an item and the most recent time you requested it.


VARIABLE_TYPE_DESC:
The value of the variable comes from the Initials Code Validation Form (STVINIT).  This form maintains initials, descriptions, and titles for people.


# tlb_POP_SEL_COMMENTS

MEMO_COMMENT:
PIDMs with the selected major code as a MAJOR. The view SAVCCUR selects current active records.
Line 5 SAVCCUR_ORDER = 1 selects the primary curriculum
To select the major is it appears in any app curriculum change Line 5 to SAVCCUR_ORDER > 0.
To select for MINOR or CONCENTRATION change Line 2 and change the prompt in Line 1 to match.


MEMO_COMMENT:
PIDMs with the selected major code as a MINOR. The view SAVCCUR selects current active records.
Line 5 SAVCCUR_ORDER = 1 selects the primary curriculum
To select the MINOR as it appears in any app curriculum change Line 5 to SAVCCUR_ORDER > 0.
To select CONCENTRATIONS or MAJORS change Line 2 to 'CONCENTRATION' OR 'MAJOR'  and change the prompt in Line 1.


MEMO_COMMENT:
PIDMs with the selected major code as a MAJOR. The view SAVCCUR selects current active records.
Line 5 SAVCCUR_ORDER = 2 selects the second  curriculum
To select the major is it appears in any app curriculum change Line 5 to SAVCCUR_ORDER > 0.


MEMO_COMMENT:
PIDMs with the selected major code as a MAJOR. The view SGVCCUR selects current active records.
Line 5 SGVCCUR_ORDER = 1 selects the primary curriculum
To select the major is it appears in any app curriculum change Line 5 to SGVCCUR_ORDER > 0.
The sub query is needed to get info for the latest effective term.
To select for MINOR or CONCENTRATION change Line 2 and change the prompt in Line 1 to match.


MEMO_COMMENT:
PIDMs with the selected major code as a MAJOR. The view SRVCCUR selects current active records.
Line 5 SRVCCUR_ORDER = 1 selects the primary curriculum
To select the major is it appears in any app curriculum change Line 5 to SRVCCUR_ORDER > 0.
To select MINOR or CONCENTRATION change Line 2 to those values and change the prompt in Line 1 to match.


MEMO_COMMENT:
PIDMS with college selected in their primary active current curriculum. To select primary plus all other curricula, change Line 2 to SGVCCUR_ORDER >0.The sub query is needed to get info for the latest effective term.


MEMO_COMMENT:
PIDMs in current active primary program selected with registration statuses RE or RW. The sub query is needed to get info for the latest effective term.


MEMO_COMMENT:
PIDMs with latest decision within the date range specified by the user. The sub query is needed to get info for the connected to the latest decision date for the PIDM, term, and app number.


MEMO_COMMENT:
PIDMs with their most recent for any CRN equal to the grade the user provide. If there are multiple grades (as in a replaced grade of I), this pulls based on whatever the latest grade is. The sub query identifies the latest grade record.


MEMO_COMMENT:
PIDMS with a CUM GPA for the level and type (Overall, Institutional, Transfer) over the value entered


MEMO_COMMENT:
PIDMs with a TERM GPA for the level and type (Overall, Institutional, Transfer) over the value entered


MEMO_COMMENT:
When you run GLBDATA for 2 pop sels at the same time to produce a NEW pop sel, that NEW pop sel contains only the name of the PIDMs found. If you go to GLRSLCT you'll see that the NEW pop sel has NO rules attached to it. If all you want top do is use these results in some process (such as letter gen, or transcripts, etc) then you can use the NEW pop sel just the way you would the results of any other pop sel. BUT, if you want to use the NEW pop sel along with another pop sel using GLBDATA to produce another set of NEW results, then you need the pop out. Since the first NEW pop sel has no rules, GLBDATA can't use it. The pop out provides rules for GLBDATA to look at. The pop out looks at the results in the NEW pop sel and extracts the PIDMs of those results for GLBDATA to use. Here's how that would look:
Step 1: Run GLBDATA for pop sel 1 and 2 to create pop sel NEW (Let's call it '3')
Step 2: Run GLBDATA for GENERIC_POPOUT and whatever other pop sel you want to combine with it (let's call it '4'). When GLBDATA runs you will be prompted with the prompts from GENERIC_POPOUT. Respond with the information about pop sel '3'. The results will combine '3' and '4' into yet another NEW pop sel, '5'.
By the way it is MANUAL pop sel because there is NO PIDM on  GLBEXTR, so you have to tell it what field to match to SPRIDEN_PIDM. Confused? Have a drink.


MEMO_COMMENT:
The term MANUAL is used 2 ways in pop sels. If you check MANUAL in the TYPE field on GLRSLCT you are telling the form to turn off the automatic joins in the main PIDMs of the tables referenced in the pop sel. See the pop sels and variables in this data base where the TYPE indicator is set to MANUAL.
The second way we use the term MANUAL is when we use GLRSLCT to set up the name of a pop sel to which we can add names MANUALLY on GLAEXTR. This is not a MANUAL pop sel in the same way MANUAL is used in the above example. To create this kind of pop sel in which you MANUALLY add names or IDs of the people on GLAEXTR, all you need to do is create a pop sel name and description on GLRSLCT.  ( I usually use the description: Manually add IDs on GLAEXTR) .Do NOT add any rules, or info in the SELECT and FROM lines. There is no need to choose MANUAL in the TYPE field. Go to GLAEXTR and manually add the names/IDs and then use the pop sel as you would any other. You'll see that the manual column is populated, telling you the entry was added manually and NOT by the system, as would be the case if you ran a pop sel and GLBNDATA found the PIDMs for you.) Note: you must also remove any manually added entries manually.


MEMO_COMMENT:
PIDMs who are active athletes for the selected term and have the financial aid indicator checked.


MEMO_COMMENT:
PIDMs who have financial aid apps for the selected year and applications for admissions for the selected term. No admissions decision has been made yet.


MEMO_COMMENT:
Active LEARNERS  at a selected campus with a FINAID app. The sub query is needed to get info for the latest effective term.


MEMO_COMMENT:
PIDMs with FINAID record for the aid year selected, who are active students in the college selected. The sub query is needed to get info for the latest effective term.


MEMO_COMMENT:
FINAID apps missing one selected tracking requirement


MEMO_COMMENT:
FINAID recipients who fail Satisfactory Academic Progress.


MEMO_COMMENT:
FINAID students who have a FIRST registration date within a date range. The sub query provides the earliest registration date per PIDM per TERM.


MEMO_COMMENT:
PIDMs of FINAID recipients who have accepted their award. User enters ONE loan code. To use 1 or more loan codes, change the operator in Line 2 to IN and the value to &LIST_LOAN_CODES


MEMO_COMMENT:
PIDMs with Tracking requirements listed and these codes for satisfactory Academic progress for the term selected


MEMO_COMMENT:
FINAID PIDMs with current decision codes indicating they have been accepted.
This uses the view SAVAPDC; this view returns ONLY the information for the most recent decision code per PIDM, per term per app.


MEMO_COMMENT:
FINAID PIDMs with current student statues of ACTIVE.
Be sure the sub query has been created or exists in the same BANNER APPLICATION  as the pop sel. The sub query is needed to get info for the latest effective term.


MEMO_COMMENT:
PIDMs  who have been accepted and come from the county selected that is attached to the address type selected.


MEMO_COMMENT:
PIDMs that have accepted awards within the date range specified by the user.


MEMO_COMMENT:
The client wants to find people who have BOTH tests. The sub query identifies those who have ONE of the tests. Added to the pop sel that finds people with the other test, the resulting pop sel finds people who have BOTH tests. This is equivalent to running two pop sels (one for test 1 and one for test 2) and then doing an intersection.


MEMO_COMMENT:
PIDMs with current major code of '0000'. The sub query is needed to get info for the latest effective term.


MEMO_COMMENT:
PIDMs missing the checklist item HSFT for their current active highest priority curriculum


MEMO_COMMENT:
Finds PIDMs that have been sent a specific letter; for use as a sub query.


MEMO_COMMENT:
PIDMs of employees in the selected employee classes due for appraisals of their primary job within the date range specified.


MEMO_COMMENT:
Employees in the selected employee classes with a salary increase in their active primary job as of the date entered.


MEMO_COMMENT:
Active employees of the selected ORGN HOME CODE/S with a seniority date earlier than 760 days ago (approximately 2 years)


MEMO_COMMENT:
Selects people with the selected deduction code from a series of ORGN HOME CODEs. When responding to a LIKE prompt, respond as follows: A% to get any starting with A; %A to get any ending with A.


MEMO_COMMENT:
Selects SUPERVISORS who have employees with evaluations due sometime between 30 and 60 days in the future. It is a MANUAL pop sel because it is selecting PIDMs (supervisors) based on information from another PIDM (employees)


MEMO_COMMENT:
Faculty members with assignments in the selected term and part of term


MEMO_COMMENT:
Resident hall residents in selected building for the term


MEMO_COMMENT:
Housing residents assigned to selected building and rooms that begin with the number entered by the user. This assumes that all rooms on the same floor begin with the same character. Rooms on the third floor begin with 3; those on the 4th, with 4, etc.


MEMO_COMMENT:
Finds PIDMs of faculty members who have courses in which there have been registration changes within the date range specified. It is a MANUAL pop sel because the pop sel references SFRSTCR and SIRASGN and the form GLRSLCT would automatically create a join on the PIDMs of those two tables if you did not turn off that automatic join by specifying the type as MANUAL. The INSTRUCTOR PIDM (from the SIRASGN table) and the REGISTRATION PIDM (from SFRSTCR) would not be the same and the pop sel would select no records. Making it MANUAL lets you create the joins needed.


MEMO_COMMENT:
PIDMs who requested materials (stored as interests in this case) within a certain date range. 
Remember when responding to prompts with the IN operator in GLBDATA or any Banner process respond as follows:
('first value',' second value',' third value'), being sure to use parentheses and single quotation marks.


MEMO_COMMENT:
PIDMs in current active program selected by user. To select based on the program code in ANY curriculum, change Line 2 to SGVCCUR_ORDER > 0. The sub query is needed to get info for the latest effective term.


MEMO_COMMENT:
PIDMs with any of the selected academic standing codes in the selected term.
Remember, when responding to a prompt using IN when running GLBDATA respond as follows: ('value 1', 'value 2','value 3'), using parentheses and single ticks.


MEMO_COMMENT:
PIDMs with selected attributes and of selected application level. To just select based on attributes remove all references to SARADAP.


MEMO_COMMENT:
The pop sel  process  identifies students with a specific graduation term and date and excludes those who have already been sent a graduation verification letter for the selected term.
The pop sel requires a sub query (*GOT_GRAD_VERIF) to eliminate students who have already received the letter 'REG_GRAD_VERIF' for the term in question.


MEMO_COMMENT:
This view, SRVCCUR, pulls active curricula. The SRVCCUR_ORDER pulls only the current one. To pull info based on the whole record, change as follows: RVCCUR_ORDER > 0


MEMO_COMMENT:
Pulls recruits from the high schools users enters when responding on GLBDATA. This uses the concurrent curriculum view SRVCCUR; it pulls only recruits and the current active curriculum info.


MEMO_COMMENT:
PIDMs with a certain decision code who have transfer credit.


MEMO_COMMENT:
This uses the CC view SAVCCUR to get admissions info and the SAVAPDC view that pulls info for the most current decision per PIDM, per TERM per APP.


MEMO_COMMENT:
PIDMs with HOLDS that stop registration that are good TODAY (SYSDATE) for active students of a certain level, based on their must current student record. The sub query is needed to get info for the latest effective term.


MEMO_COMMENT:
PIDMs with any active holds that stop registration


MEMO_COMMENT:
This pop sel requires a sub query to select those students for whom the MOST RECENT grade in a course is 'I'. The variable that creates this sub query is *MAX_GRADE_SEQUENCE_NO.


MEMO_COMMENT:
Finds PIDMs who have NO immunizations that have been satisfied. The sub query identifies those who DO HAVE ANY satisfied immunizations


MEMO_COMMENT:
PIDMs of people with decision codes specified by user and with active addresses in selected counties; address type is specified by user.


MEMO_COMMENT:
PIDMs with recruit records for the term but who have not registered yet. It uses a sub query to identify those who HAVE registered, then excludes these from the pop sel results.


MEMO_COMMENT:
PIDMs that have NO missing checklist items and who have a specific letter on SUAMAIL that has not yet been sent. You could also use the app status code = 'C' instead of the sub query.


MEMO_COMMENT:
PIDMs with outstanding I grades. The sub query is needed to return only PIDMs with the LATEST grade in a course is I; that is, the grade I has not been replaced with another grade.
To be prompted for a grade, change Line 1 to SHRTCKG_GRDE_CODE_FINAL = &WHAT_GRADE


MEMO_COMMENT:
There may be other ways to identify PIDMs in cancelled CRNs, but this seemed to work.


MEMO_COMMENT:
PIDMs of registered advisees of an advisor. It is MANUAL because this pulls STUDENT PIDMs (SGRADVR_PIDM) information based on the advisor's information connected to the  SGRADVR_ADVR_PIDM.
The sub query gets info for the  most current advisor. To select based on last and/or first name rather than ID, change Line 6.If advisor code is not needed, remove Line 8


MEMO_COMMENT:
Applicants for a term with a high school GPA equal to or greater than (GTE) the value the user uses.


MEMO_COMMENT:
Applicants with a HIGH SCHOOL GRADUATION DATE within a range


MEMO_COMMENT:
Apps with an attribute of 'HON'. Use this as a pattern for pop sels looking for Applicants with attributes.


MEMO_COMMENT:
PIDMs with any of the selected accepted decision codes and with a diploma code on their high school of 'HF', meaning their final HS transcript has been received.


MEMO_COMMENT:
Recruits who took the selected tests within a date range


MEMO_COMMENT:
PIDMs with a test code and score selected by the user. It selects test scores based on the LATEST DATE the test was taken. The sub query provides that date. To see selection based on the HIGHEST score for the test, see the pop sel TEST_HIGH_SCORER_SCORE


MEMO_COMMENT:
PIDMs with a test code and score selected by the user. It selects test scores based on the HIGHEST TEST SCORE for the test regardless of when it was taken. The sub query provides information connected to the record with the highest score per test per PIDM. To see selection based on the LATEST DATE for the test, see the pop sel TEST_HIGH_SCORER_DATE


MEMO_COMMENT:
PIDMs who registered for selected terms before a given date. The terms selected are current or future terms.


MEMO_COMMENT:
Pulls PIDMs with a score above the selected score for the selected test. It uses the sub query to pull info from the most recent test date for the test selected. See TEST_HIGH_SCORERS_2 for a selectio0n based not on the latest test, but on the highest regardless of the date of the test.


MEMO_COMMENT:
This selects people who have EITHER an academic stating indicating  academic probation or hold registration OR people on the Dean's List. So, it includes both the 'bad' news kids and the 'good' news kids! In Banner letter include the variable that pulls either the 'bad' news cods or the 'good' news code. Then you can use ONE WORD smart letter to create the 'good' news and 'bad' news messages to the right people!


MEMO_COMMENT:
PIDMs with a degree level code and admit term selected by the user.


MEMO_COMMENT:
PIDMs with a job application in for OPEN jobs within a date range.


MEMO_COMMENT:
PIDMs for jobs that have been filled and who applied for the jobs between the dates specified.Also used with HRA_JOB_HIRED to find those not hired for filled jobs.


MEMO_COMMENT:
PIDMs who have been HIRED for jobs applied for within the date range specified. To get the PIDMs of everyone who applied for a job but did NOT get hired:  Run simultaneously HRA_JOB_FILLED as Selection 1 and this one (HRA_JOB_HIRED) as Selection 2.Use the MINUS operator.


MEMO_COMMENT:
This is an all purpose example. In Line 5, respond with MAJOR, MINOR, or CONENTRATION to select based on one of these 3 'major' types. The sub query is included because the client wanted the very earliest record.


MEMO_COMMENT:
PIDMs of the selected class (as in freshmen, sophomore, junior, senior, using the appropriate class codes) who are registered in the registration statuses used.


MEMO_COMMENT:
Uses the delivered view SOVPERS. Test this.


MEMO_COMMENT:
PIDMS registered in classes that meet on a given day and time. In this example IO used SSRMEET_MON_DAY for Monday classes; change the data element to another day if needed (SSRMEET_TUE_DAY, SSRMEET_WED_DAY, etc.)


MEMO_COMMENT:
Active or 'red-shirted' athletes who are academically eligible for the term. To find INELIGIBLE athletes, change the value in Line 1 to 'AE'


MEMO_COMMENT:
Active or 'red-shirted' athletes on the selected team


MEMO_COMMENT:
PIDMS with the selected major and decision codes. To select for MINOR or CONCENTRATION change Line 2 and change the prompt in Line 1 to match.


MEMO_COMMENT:
PIDMS at a selected LEVEL with selected academic standing codes with an OVERALL TERM GPA for a selected TERM below a selected value.


MEMO_COMMENT:
PIDMs registered in courses that have been cancelled


MEMO_COMMENT:
Applicants in the selected major. To select based on MINOR or CONCENTRATION change Line 4.


MEMO_COMMENT:
The User can respond with the words MAJOR, MINOR, or CONCENTRATION to select based on any one of these, and then with the 'major' code to select the actual field of study. To select MAJORS in ANTH, respond with MAJOR and ANTH. To select minors in ANTH, respond with MINOR and ANTH.
If CAMPUS is not relevant, remove Line 5.


MEMO_COMMENT:
Finds FINAID apps with an email address on their FAFSA.


MEMO_COMMENT:
Selects students with expected graduation dates within the range specified. The sub query gets information connected to the latest student record.


MEMO_COMMENT:
This finds people not in certain  majors who have certain decision codes made before the selected date.  The sub query is needed to get info only for the most recent decision per PIDM, per term, per application.


MEMO_COMMENT:
Faculty members with any courses that have INCOMPLETE grades. The sub query is needed to  identify only the most recent grade in a course. It is a MANUAL pop sel because you want to find the PIDMs of FACULTY members based on information ( a course grade) attached to the STUDENT's PIDM.


MEMO_COMMENT:
Selects PIDMs with aid for the selected aid year, term, and fund code.


MEMO_COMMENT:
PIDMs with a SHRDGMR record. Change any of the prompts to hard coded values if that is appropriate. This will find anyone who meets the criteria for whatever degree sequence you specify (see Line 5). To select people regardless of regardless of what degree sequence meets the criteria, remove Line 5.


MEMO_COMMENT:
This uses the concurrent curriculum OUTCOMES view. It selects based on all current and active curricula and any degree sequence. To select based ONLY on the primary and active, current curriculum change the value in Line 6 to 1. To select based on ANY degree sequence, remove Line 5. Change any of the dynamic prompts to hard-coded values if that is appropriate.


MEMO_COMMENT:
This uses a view (SAVADMT) that contains admissions info. Don Thibault has had good experience with that view. You probably do not need the SARADAP reference since the views uses SARADAP as one of its sources.


MEMO_COMMENT:
Finds PIDMs who are registered and who have non course requirements that have expired (have a date before today's day minus 1200 days)


MEMO_COMMENT:
Selects anyone with CURRENT address of the type selected.


MEMO_COMMENT:
Finds PIDMs applying to a specific campus and requiring a high school transcript, but it has not yet been received.


MEMO_COMMENT:
Finds recruits for a selected major who do not have an application for the term used. The sub query identifies those who do have an app, then the pop sel rules exclude them. You could also do this with 2 pop sels.


MEMO_COMMENT:
Finds REGISTERED students with capitalization problems in the prefix. To find ALL records with capitalization issues, remove all references to SFRSTCR, including the one in the FROM line


MEMO_COMMENT:
This finds registered students who are sponsored by a specific company. Since this finds a pidm (the student's, TBBCSTU_STU_PIDM)  based upon information from another pidm (the company's, on TBBCSTU_CONTRACT_PIDM )this must be a manual pop sel. If you don’t make this a manual pop sel, the process would match the TBBCSTU_STU_PIDM with the TBBCSTU_CONTRACT_PIDM and because they are different, you would get no result.


MEMO_COMMENT:
Finds all ACCEPTED students based on the categories established on STVAPDC.


MEMO_COMMENT:
This finds registered students with fees assessed. Rose Most used this with the pop sel REGISTERED to find students who are registered but have not yet been assessed. (See the pop sel REGISTERED.) She ran the two pop sels at the same time (Selection 1 = REGISTERED; Selection 2 = FEE_ASSESSMENT; NEW SELECTION = NO_FEES_ASSESSED; 1 MINUS 2). The new selection (NO_FEES_ASSESSED)  returns students who may go through fee assessment but fall out of all the rules, or the net of all the rules they qualify for = no posting. This ignores the SFBETRM indicator.


MEMO_COMMENT:
This finds registered students. Rose Most used this with the pop sel FEES_ASSESSED  to find students who are registered but have not yet been assessed. (See the pop sel REGISTERED.) She ran the two pop sels at the same time (Selection 1 = REGISTERED; Selection 2 = FEE_ASSESSMENT; NEW SELECTION = NO_FEES_ASSESSED; 1 MINUS 2. The new selection (NO_FEES_ASSESSED)  returns students who may go through fee assessment but fall out of all the rules, or the net of all the rules they qualify for = no posting. This ignores the SFBETRM indicator.


MEMO_COMMENT:
Finds applicants who have received a specific letter and who still have missing checklist items. This can be run simultaneously with the pop sel CHKL_RCVD_IN_DATE_RANGE (using the intersection option) to find people who have been sent the letter, still have some missing items and who also have sent in some checklist items within a date range, that is, people who are in BOTH pop sels.


MEMO_COMMENT:
Finds applicants who have received a specific letter and who still have missing checklist items. This can be run simultaneously with the pop sel HAVE_BEEN_SENT_MISS_DOCS_LTR (using the intersection option) to find people who have been sent the letter, still have some missing items and who also have sent in some checklist items within a date range, that is, people who are in BOTH pop sels.


MEMO_COMMENT:
To find people who are registered in MORE THAN ONE POT you need a pop sel for EACH POT, then INTERSECT them to get those people who are registered in BOTH POTs


# tlb_WORD_SWITCHES

F1:
SWITCHES specify how to display field results returned by the merge fields. There are many kinds of switches. To learn about switches go to WORD Help and enter 'Switches'


F1:
Below are a few examples of useful switches


F1:
Sometimes you want to change the capitalization format of data brought out of Banner. You can't uses the usual FORMAT-CHANGE CASE capability on merge fields. You must use SWITCHES.


F1:
The following format instructions determine the use of uppercase and lowercase letters.


F1:
Switches only change that instance of using the merge field, not all instances. For instance, you could use \*UPPER on the name and address fields for a label or an internal address to make them all UPPER Case and then not use this switch in the same name and address fields in the salutation or rest of the letter itself.


F2:
Capitalizes only the first letter of each word. For example, { LAST_NAME \* Caps } displays "Winfrey" even if the entry in Banner is "winfrey" or otherwise mis-keyed


F2:
Capitalizes only the first letter of the first word. For example, {STUTYP_DESC \* FirstCap } displays "First-time freshmen" even if the value in Banner is "First-time Freshmen"


F2:
Converts ALL letters to UPPER case. For example, {TERM\* Upper } displays "SPRING SEMESTER 2011", even though the value in Banner is "Spring Semester 2011"


F2:
Converts ALL   letters to LOWER case. For example, {STUTYP_DESC \* Lower } displays "freshman" even though the value in Banner is Freshman.
Note  This switch has no effect if the entire field that contains the switch is formatted as small capital letters.


F1:
Use the ALT-F9 toggle to toggle between a simplified view of the merge field and the one that displays it with the word MERGE FIELD and the MERGE BRACKETS. It will look like this: {MERGE FIELD LAST_NAME}


F1:
Put your cursor just before the } and key in the switch name,( for instance \*Caps) to produce {MERGE FIELD LAST_NAME\*Caps}


F1:
dealing withj those pesky names where prefixes, middle names and suffixes may exist in some records but not in others


F2:
Specifies the text to be inserted following the MERGE FIELD field if the field is not blank.


F1:
Placing the following three MERGE FIELD fields together and using the \f switch as specified ensures that the three fields have spaces between them, but only if the designated field information is present in the data source.


F1:
the "space" specifies to insert ONE space after the field if the field has data in it. You could specify any number of spaces or any text to insert if the field has data in it.


F1:
{ MERGE FIELD CourtesyTitle \f " " }{ MERGE FIELD FirstName\f" " }{ MERGE FIELD MiddleName\f" " }{ MERGE FIELD LastName }


F1:
If all fields are present, the \f"" inserts each field and a space after each one. It only inserts a space if the field has data in it.


F1:
Mr. Thomas Alva Edison (with a space between each field)


F1:
If MiddleName is present from the data source: the \f"" inserts a space after the middle  name and then the last name
If there is no MiddleName, the \f" " ignores it, and inserts no spaces in its place


F1:
If CourtesyTitle and MiddleName are missing from the data source, the \f"" does NOT put a space before first name, ignores the MiddleName and its space and inserts the last name


F2:
Specifies the text to be inserted before the MERGE FIELD field if the field is not blank.


F1:
{MERGE FIELD CourtesyTitle\b "TITLE: "}{ MERGE FIELD FirstName\f" " }{ MERGE FIELD MiddleName\f" " }{ MERGE FIELD LastName } will insert the text "TITLE: "and the value returned by the field CourtesyTitle  IF it  returns any value. Otherwise it inserts nothing


F1:
If there is a CourtesyTitle, this would return


F1:
TITLE: Dr. Thomas Edison (if a CourtesyTitle exists)


F1:
Thomas Edison (if a CourtesyTitle  does not exist


# Variable_Categories

Master_Category_Explanation:
Partial List: Appointments, Attributes, Checklist,


Master_Category_Explanation:
Partial List: Interests, Application status, Requested materials, Contacts


Master_Category_Explanation:
Partial List: Appointments, Attributes, Checklist, Decisions


Master_Category_Explanation:
Partial List: Schdedule, Courses, Times, Dates, Lo


Master_Category_Explanation:
Partial list: Academic standing, CAPP, GPA,.Grades


Master_Category_Explanation:
Partial List: Addresses, Email, Name, Parents, Rac


Master_Category_Explanation:
Partial List: Advisor, Campus, College, Degree, De


Master_Category_Explanation:
Partial List: Orientation, Guests, Meals


Master_Category_Explanation:
Partial List: Classes, Registration, Term, Courses added or dropped, Cancelled courses


Master_Category_Explanation:
Partial List: Classes, Registration, Term, Courses added or dropped, Canc


Master_Category_Explanation:
Partial List: Benefits, COBRA, Deductions, Job and


Master_Category_Explanation:
Partial List: Fees, Charges, Detail Codes,Payments


Master_Category_Explanation:
Partial List: Awards, Funds, Tracking Requirements


# Variable_Comments

MEMO_COMMENT:
Counts the number of detail codes with balances LESS than zero. To be used along with *DETC_COUNT_GT_ZERO to construct the variable *NSF_COUNT


MEMO_COMMENT:
This variables uses information returned by two other variables to produce a result. It uses the 'fake' table DUAL because the data returned by the two variables does not really exist on a source table; the variables have created it. When you use  variables built up of other variables, include ALL the variables in your Banner letter or you won't  get any results for the final variable. You will only use the final variable (this one) in your WORD letter.


MEMO_COMMENT:
SUB QUERY
Finds the latest sequence number for each  non course requirement code to be used in the pop sel EXPIRED_PRELIMS


MEMO_COMMENT:
This variable returns the number of years of English in high school , or, if there are no years entered, it returns the phrase 'NO ENTRY'.


Sample_output:
Spring (for terms like 200910 ansd 201010)

OR

Summer or Fall (for terms like 200920, 200930)

MEMO_COMMENT:
This variable returns a term type indicator based on the 5th character of the term code. One indicator is for Summer and Fall terms; another just for Spring. Used in WORD logic to distinguish easily  between the two categories.


MEMO_COMMENT:
This variable divides the GPA hours by the attempted hours, multiplies the result by 100 and limits the result to 2 decimal places


MEMO_COMMENT:
Degree description for primary LEARNER curriculum


MEMO_COMMENT:
The latest status date of a selected non-course requirement and selected status


MEMO_COMMENT:
SUB QUERY
Finds PIDMs who have an app- in the system for the term used.


Sample_output:
CLASS AND RANK:  120/250

OR

NO CLASS RANK AND SIZE.  PERCENTILE RANK IS 2

MEMO_COMMENT:
This multi-sequence variable returns the class and rank with a title for the high school associated with the checklist code of HST. 1IF there is a rank and size. Otherwise, it returns a message that there is no rank and size, and includes the percentile..


MEMO_COMMENT:
This finds the PROGRAM description.
All pop sels and variables that link other  tables to the SMRPRLE table should be MANUAL.


MEMO_COMMENT:
This uses a sub query (*SUB *SUB_CURR_SHRDGMR_STU_REC) to get current degree sequence, in degree sequence order. It is a  MANUAL type because of some peculiarity on the SMRPRLE table. If the type is not MANUAL, you get no results. The sub query is needed to pull information from the latest effective term.


VAR_COMMENT:
The course numbers of a faculty member's assigned courses; ordered by CRN

MEMO_COMMENT:
FOR FACULTY: Course numbers of  ALL of a FACULTY member's assignments, in CRN order. To be used in letter to the faculty member.


Sample_output:
(A list, such as:)
Bob's Super Keen Culinary Institute
College of Hard Knocks

MEMO_COMMENT:
Lists ALL  prior colleges by college name


MEMO_COMMENT:
Institutional term UNDERGRADUATE GPA ATTEMPTED HOURS for the term selected. For GRADUATE, change the code to 'GR', or make the value a dynamic prompt. For other GPA types, change Line 1 value  to T (Transfer) or O (Overall) or make the line a dynamic prompt.


VAR_COMMENT:
Used as a sub query in pop sels and variables dealing with grade changes. It finds the latest grade sequence where there is no grade change

MEMO_COMMENT:
SUB QUERY
Used in variables dealing with latest grade change


MEMO_COMMENT:
This is a manual variable because it pulls information for the PIDM (the advisor's, or SGRADVR_ADVR_PIDM) other than the  PIDM (the student's, or SGRADVR_PIDM). That is why we manually add the join SPRTELE_PIDM = SGRADVR_ADVR_PIDM. This is pulling the advisor's INFO from the SPRTELE record to send to the student. Lines 4r & 5 are client specific and may be changed. The sub query pulls the information from the most current advisor's record.


Sample_output:
RETURNS ALL COURSES WITH INC GRADES

ANTH 101 01 Smith
PSYC 100 03 Jones

MEMO_COMMENT:
FOR STUDENT: SUBJ, NUMBER, SECTION and instructor,  ONLY for all courses with the most recent grade of 'I'. The sub query is needed to make sure it pulls info only for classes for which the LATEST grade is I. It is manual because it also pulls instructor information.


MEMO_COMMENT:
UG attempted GPA hrs for the MOST recent term for which it exists so long as the term is within a range specified in the sub query. See the code for the sub query. This SUBQUERY USUALLY NOT NEEDED to pull this info, but including it pulls info from the most recent term for which there is a term GPA. See other GPA variables.


MEMO_COMMENT:
SUB QUERY
 to find latest term with a term GPA that is within the range of terms specified. Used in other variables and/or pop sels.


MEMO_COMMENT:
FOR FACULTY: The begin time of ALL of  FACULTY member's assigned courses, ordered by Canto be used in letter to the faculty member.


MEMO_COMMENT:
SUB QUERY
 to get info about the latest grade entered for a course in a term. Required in pop sels and variables wanting to pull people or info based on the latest grade, for instance those people with outstanding incomplete grades.


MEMO_COMMENT:
SUB QUERY
used to find data from latest student record on the degree table.  Used in pop sels and variables.


Sample_output:
(A list, such as:)
ACT Composite - SCORE: 25
SAT Verbal - SCORE: 800

MEMO_COMMENT:
ALL test names and scores, ordered by test name. Selected based on when test score was received,


Sample_output:
New College  BA Major: Anthropology 01-SEP-2009 to 15-DEC-20010 Awarded 15-DEC-20010

or IF NO DEGREE
New College No Degree 01-SEP-2009 to 15-DEC-20010

MEMO_COMMENT:
School, degree, major,. attend dates of  ALL prior colleges. The DECODE works this way: If the degree code is '000000', then the phrase 'No Degree' appears. If there IS any other degree code, it returns the degree code. See Sample output


Sample_output:
Prof. (if this prefix exists)
or
Ms. (if no prefix and gender is female)
or
Mr. (if no prefix and gender is male)

MEMO_COMMENT:
This is an example of both a multi-sequence variable and of how to 'create' data for the letter when none exists in Banner. It returns the prefix if it exists; otherwise it creates one based on gender.


MEMO_COMMENT:
SUB QUERY
Returns the latest print date for letters  printed in selected term. Used as sub query in variables and pop sels based on latest letter dates for these letters.


Sample_output:
No output; used as sub query in pop sels.

MEMO_COMMENT:
Finds PIDMs of people with complete immunizations. Used as sub query in pop sels to find all people who do NOT have ANY complete immunizations. This client used the code '02' to indicate that all immunizations were complete.


Sample_output:
RETURNS ONE OF THE FOLLOWING:
A-H  (SEQUENCE 1)
OR
I-M   (SEQUENCE 2)
OR
N-S   (SEQUENCE 3)
OR
T-Z   (SEQUENCE 4)

MEMO_COMMENT:
Multi sequence variable returns an indicator of the alpha range of applicant's last name to use in  WORD to determine who should sign  letters.In WORD you can specify that based upon which value is returned WORD can insert some person's name or some other information. For instance, if the variable returns 'A-H' WORD can insert the name of the person who handles applicants with last names in that range. You could also write this variable with the signer's name in the SELECT line instead of the  last name range.


MEMO_COMMENT:
SUB QUERY 
 to identify those who got a specific letter. Use in pop sels to exclude those who got this letter.


MEMO_COMMENT:
The date or dates a specific BANNER LETTER item was generated.


Sample_output:
(A list, such as:)
ANTH  101  01 Grade: B
ANTH  203  02 Grade: A

MEMO_COMMENT:
FOR STUDENT: Shows SUBJ,. NUMBE, SECTION and final grade for any course for which there has been a grade change within a date range. It uses a complex sub query, also in this data base.
STILL IN DEVELOPMENT


Sample_output:
(A list, such as:)
ANTH 101 Section 02
PSYC 103 Section 09

MEMO_COMMENT:
OR FACULTY: Shows ALL  courses for which grades are still missing for faculty member notification. Used in a letter TO the faculty member. This uses a sub query called *SUB_QUERY_CRNS_NO_GRADES that identifies CRNs without that do not have grades to feed into this variable.


MEMO_COMMENT:
SUB QUERY
Used in the variable *MISSING_GRADES_COURSES. It identifies CRNs with missing grades so faculty can be notified. It is MANUAL because if is pulling information from the SFRSTCR table (the student's PIDM) for the faculty PIDM on the SIRASGN table,


VAR_COMMENT:
Two sequence variable that returns EITHER the academic standing code OR the Dean's List code. This saves having to create separate variables.

MEMO_COMMENT:
Returns EITHER the academic standing code OR the Dean's List code. Using this, you do not need to have TWO variables, one for the 'bad' news and one for the 'good'.


MEMO_COMMENT:
SUB QUERY
Used in pop sels and variables in which information based on the latest advisor record is required.


Sample_output:
(A list, such as:)
Library Fine
Parking Fine

MEMO_COMMENT:
Descriptions of ALL  holds still in effect today, the day the variable is extracted in letter gen.


MEMO_COMMENT:
Uses the VIEW SAVAPDC that returns the most recent decision code per app, per term per pidm. To see all the fields on SAVAPDC run GURPDED (table creator ID=BANINST1)


MEMO_COMMENT:
Uses VIEW SAVAPDC that pulls latest decision code. Needs SARADAP link to use LEVEL. To see all the fields on SAVAPDC run GURPDED (table creator ID=BANINST1)


MEMO_COMMENT:
SUB QUERY 
y to get latest decision date; used in variables and pop sels to pull data based on latest decision. Note the difference in the code to pull this and the code to pull the latest date to DISPLAY in a letter.


MEMO_COMMENT:
Pulls date of latest decision. This assumes there is only ONE application for the term. This can be included in the letter. Notre the difference between the code to display the highest date and the code to create a sub query to get the highest date to use in other variables or pop sels.


Sample_output:
RETURNS ALL RECEIVED CHECKLIST ITEMS, for example:

Application Fee
College Transcript - Tequila State
Letter of Recommendation  - Jose Cuervo

MEMO_COMMENT:
Returns descriptions and comments (if any) for all RECEIVED checklist items. Note that the delivered *CHKL variable returns MISSING checklist items. The DECODE returns NOTHING if the comment field is  empty; otherwise it inserts a - between the description and the comment.


Sample_output:
(A list, such as:)
10003 AN TH 1101 03 Intro to Anthropology Goodall, Jane
10013 PSYCH 200 01  Ego Mania Freud, Sigmund

MEMO_COMMENT:
FOR STUDENT: Shows info about ALL courses in which student has INC grades, including instructor's name for inclusion in a letter to the student. The sub query is needed to pull information from the latest grade entered for the course.


Sample_output:
RETURNS ONE OF THE FOLLOWING:

RENEW

or 

NEW

MEMO_COMMENT:
Multi sequence variable returns text to be used in WORD logic based on housing app status. Use WORD to insert a different message based on the 2 different messages returned by the variable.


Sample_output:
RETURNS ONE OF THE FOLLOWING:

BIG 3 (Sequence 1)
or
FINANCIAL (Sequence 2)
or
MEDICAL (Sequence 3)

MEMO_COMMENT:
Multi sequence variable that returns different values based on the housing app status codes. The different values are used in the WORD logic to insert different messages. The values here are just examples.


Sample_output:
RETURNS ONE OF THE FOLLOWING:

SPRING  (Sequence 1)
or
FALL  (Sequence 2)

MEMO_COMMENT:
This creates FALL or SPRING indicators for housing applications based on the final 2 characters of the term code.  Thus, one WORD letter can be used for BOTH terms with different text based on this indicator.


Sample_output:
RETURNS ONE OF THE FOLLOWING:

Spring 2009  Semester (Sequence 1 for SPRING apps)
or
Academic Year 2009-20010  (Sequence 2,opr FALL apps)

MEMO_COMMENT:
For apps in FALL, this returns the phrase Academic Year and a year value calculated based on the kind of term (one ending in 10). For apps in SPRING it returns the SPRING term description. Since the variable calculates these each time, there is no need to update the WORD text. Thus, one WORD letter can be used for BOTH terms with different text based on this indicator.


Sample_output:
RETURNS ALL REGISTERED CRNS, FOR INSTANCE:

10002 ANTH 101 03 Course: Introduction to Anthropology
10003 PSYC 102 01 Course: Introduction to Psychology
10034 ENGL 200 05 Course: American Literature

MEMO_COMMENT:
FOR STUDENT: Shows list of registered courses for a STUDENT in CRN order. Change the ORDER BY field to sort in another way: SSBSECT_SUBJ_CODE,SBSECT_CRSE_NUMB would sort by sub and course number


Sample_output:
(A list, such as:)
Brownie State College -103 - Basic Math CRDS: 3
Brownie State College -110 - English CRDS: 3
Central College - 203 - Psychology CRDS: 3

MEMO_COMMENT:
Info about ALL TRANSFER COURSES coming in.


Sample_output:
(A list, such as:)
ENGL 101 Writing Skills CRDS: 3
MATH 101 Math for Beginners CRDS: 3

MEMO_COMMENT:
Lists ALL transfer equivalent courses. Ordered by subject, course number and title.
No rules are needed for the variable


Sample_output:
RETURNS ONE OF THE FOLLOWING:
ACCEPT_NEW_A_OR_F
OR

ACCEPT_TR_A_OR_F
OR

PROVISIONAL_NEW_OR_F

MEMO_COMMENT:
Creates values to be used in WORD logic to assign different decision letters. The values are determined by different combinations of decision code, student type, and admit type. This is not a complete list of the sequences, just an example.


Sample_output:
If the degree code is BSME, the variable rewrites it as Bachelor of Science  in Mechanical Engineering'
If it is BAAP, the variable rewrites it as 'Bachelor of Arts in Applied Procrastination'.
All others appear as in Banner

MEMO_COMMENT:
This  'rewrites'  a value in Banner to use in a letter. It uses  sequences 2 and 3  to rewrite the description for 2 degree code , and sequence 1 to retrieve the stored descriptions for all others. This gives a chance to improve on the Banner descriptions when extracting data for letters.


MEMO_COMMENT:
Advisor's email. It is  MANUAL type because it pulls information from the advisor's PIDM for the student. The sub query gets the most recent advisor.


MEMO_COMMENT:
College description of college applied to from current highest priority curriculum choice.


MEMO_COMMENT:
SUB QUERY 
used in other variables or pop sels looking at most recent LEARNER record.


MEMO_COMMENT:
College  CODE for LEARNER record of highest priority in the current active curriculum of the most recent LEARNER term. To get college codes from all curricula for that term make SGVCCUR_ORDER >0. The sub query is needed to pull the information from the latest effective term for each person.


MEMO_COMMENT:
College  description  for LEARNER record of highest priority in the current active curriculum of the most recent LEARNER term. To get college codes from all curricula for that term make SGVCCUR_ORDER >0. The sub query is needed to pull the information from the latest effective term for each person.


VAR_COMMENT:
Level; code of current, active highest priority curriculum for the selected most recent term

MEMO_COMMENT:
Level code from current active learner curriculum in most recent term.


Sample_output:
Bobby (where the First name is Robert and preferred name is Bobby)

or

Robert (where there is no preferred name)

MEMO_COMMENT:
Returns the preferred name if it exists. If not, it returns the first name.


MEMO_COMMENT:
Level description from current active learner curriculum in most recent term.


MEMO_COMMENT:
Term CODE of most recent effective term.  The sub query is needed to pull the information from the latest effective term for each person.


MEMO_COMMENT:
Term Description of most recent effective term.  The sub query is needed to pull the information from the latest effective term for each person.


MEMO_COMMENT:
Major code from current active learner curriculum in most recent term. To get ALL major codes from all curricula for that term make SGVCCUR_ORDER >0. The sub query is needed to pull the information from the latest effective term for each person.


VAR_COMMENT:
Current MINOR code from current active curriculum of most recent term

MEMO_COMMENT:
Current MINOR  from current active learner curriculum in most recent term. To get ALL minor codes from all curricula for that term make SGVCCUR_ORDER >0. The sub query is needed to pull the information from the latest effective term for each person.


MEMO_COMMENT:
Major Description from current active learner curriculum in most recent term. To get ALL major descriptions from all curricula for that term make SGVCCUR_ORDER >0. The sub query is needed to pull the information from the latest effective term for each person.


MEMO_COMMENT:
Major code from applicant's, current active curriculum.
To create variables for MINOR or CONCENTRATION change the value for SOVCFOS_LFST_CODE to CONCENTRATION or MINOR.


Sample_output:
(A list, such as:)
ANTH 101 Introduction to Anthropology
MATH 100 Basic Match
(courses from the second and other  school would follow)
BUSI 101 Principles of Business
CHEM 100 Introduction to Chemistry

MEMO_COMMENT:
Transfer courses from originating institution,. By institution, course and number.
The several TRANS variables order the info in the same way so they can be lined up in a document..


Sample_output:
(A list, such as:)
ANTH 100 Principles of Anthropology
MATH 023 Math and Life

MEMO_COMMENT:
Courses at client school, but ordered by the originating school.
The several TRANS variables order the info in the same way so they can be lined up in a letter or other doc.


MEMO_COMMENT:
Transfer course grades,  ordered by the originating school.
The several TRANS variables order the info in the same way so they can be lined up in a document.


MEMO_COMMENT:
Credits given for transfer courses from originating institution,. By institution, course and number.
The several TRANS variables order the info in the same way so they can be lined up in a WORD doc.


MEMO_COMMENT:
Sum of all transfer credits used, for all originating institutions


Sample_output:
(A list, such as:)
Ball State University
Chain Community College
Link Community College

MEMO_COMMENT:
Names of transfer institutions ordered by name of institution. The several TRANS variables order the info in the same way so they can be lined up in a letter doc.


MEMO_COMMENT:
Pull advisor's phone number without area code. It is manual type variable because it is pulling information for SGRADVR_ADVR_PIDM, (the advisor)  not the SGRADVR_PIDM (the student).
Change the SPRTELE_ATYP_CODE as appropriate. The sub query is needed to pull the most current advisor's record.


MEMO_COMMENT:
Calculates age in years. TEST THIS ONE!!!!


MEMO_COMMENT:
Uses the view SGVCLAS, which has useful fields, such as GPA in it. Do a GURPDED to see all the data elements,  using BANINST1 as the table owner rather than SATURN.
The view is the easiest way to do pop sels or variables based on class codes.


MEMO_COMMENT:
Uses assigned advisor from SORAINF for APPLICANTS  and NOT advisor assigned as a learner on SGAADVR. You might want to choose a GOREMAL_EMAL_CODE if the email address uses GOREMAL (the usual case). 
This is MANUAL because it pulls information about ONE PIDM (the advisor's) FOR ANOTHER PIDM (the applicant's)


Sample_output:
FA (for all terms ending in 10)
SP (for all terms ending in 20)
SU (for all terms ending in 30)

MEMO_COMMENT:
Creates a term type indicator based on the last 2 characters of the term code. Using this in WORD makes it unnecessary to  have separate Fall and Spring letters and/or update letters every term. Change the values to match the client's values. The SUBSTR looks at the term code, and starting with the 5th character, returns only 2 characters, in effect stripping the YEAR and leaving only a two character TERM. The DECODE then reads those two characters and creates a term type indicator.


MEMO_COMMENT:
Applicant's advisor's first and last name, pulled from the SORAINF table and not from the LEARNER advisor table.
This is MANUAL because it pulls information about ONE PIDM (the advisor's) FOR ANOTHER PIDM (the applicant's)


MEMO_COMMENT:
Use this pattern to convert 24 hour time to 12 hour time for any time field. The AM code at the end will produce PM in the results if the time is after 12 noon.


MEMO_COMMENT:
Shows how to use supplemental data information in a variable. In this case a money amount was stored in supplemental field 1.


Sample_output:
Bob's College from 15-SEP-2007 to 05-JUN-2009


Sample_output:
(A list, such as:)
Bob's Culinary College GPA: 3.5
College of Hard Knocks GPA: 4.0

MEMO_COMMENT:
Pulls ALL prior colleges names and transferred GPA.


MEMO_COMMENT:
This shows how to do mathematical functions on number variables. Client had one fake school (900108) where it created a value based on of all prior college GPAs. This variable takes that score, multiplies it by 200 and adds GMAT score. Used as a rating method.


Sample_output:
RETURNS ONE OF THE FOLLOWING EXAMPLES:
other UScounselor@FAKEEMAIL.COM
or
freshmancounselor@fakeemail.com' (all freshmen, all citizenships)
or
transfer@fakeemail.com ( US APPS, last name A-Ls, R or T apps
or 
Internationalcounsellor@fakeemail.com'

MEMO_COMMENT:
Creates an email address for an apps counselor based on applicant's last name and , student type, and citizenship. This assumes there is no  way to tie to counselor's PIDM to an entry on GOREMAL (the email table). The email address is hardcoded in the variable SELECT line. Each sequence returns a different result so this one variable can be used to produce all appropriate email addresses.


MEMO_COMMENT:
College description  from current active highest priority curriculum from application. Line 5, referencing application preference number is not required unless there are multiple app preferences and/or using app preference numbers.


MEMO_COMMENT:
Returns name of counselor assigned. This is  MANUAL variable because it is pulling information for the PIDM that is NOT the student's for inclusion in a letter to the student. See Line 1.
If not using app preferences,. delete Line 9.


Sample_output:
FALL ( for terms ending in 10)

or

SPRING  (for terms ending in 20)

MEMO_COMMENT:
Returns the values FALL or SPRING or SUMMER based on the last 2 characters of the term code of the term applied for. Using this in WORD makes it unnecessary to  have separate Fall and Spring letters and/or update letters every term.


MEMO_COMMENT:
Residency code from preferred application


VAR_COMMENT:
Most recent decision code from preferred application

MEMO_COMMENT:
The most recent decision for the application of the preference number supplied by user.


Sample_output:
RETURNS ONE OF THE FOLLOWING:

35 (or ANY  accept decision)

or

DENY (for ALL denied decvisions)

MEMO_COMMENT:
Sequence 1 returns whatever the decision code is for an accepted decision. Sequence 2 returns the word DENY for all denied decisions. These values are used in WORD logic, so that all DENY decisions can get the same letter, while the other decisions can get letters that are specific to each decision code.


MEMO_COMMENT:
Returns the word ATHLETE if the person is an athlete as indicated on the attributes tables. Used in WORD logic


MEMO_COMMENT:
Returns latest decision code for the preferred application.. Remove Line 6 to not reference preferred application. This uses the view SAVAPDC which returns info for only the latest decision per app.


MEMO_COMMENT:
Respond to the prompt with any term code when running GLBLSEL and the variable will return the description that goes with that term code.


MEMO_COMMENT:
SUB QUERY
This gets the next to highest sequence number (2 instead of one) to use as a sub query in the pop sel *PROG_PRIOR_DESC  to find people by their NEXT to highest curriculum sequence.


Sample_output:
RETURNS ONE OF THE FOLLOWING:
Master's  (The variable adds the `s)

or

Doctorate  (no `s added)

MEMO_COMMENT:
This variable inserts an apostrophe `s` after some degree level names (Bachelor, Master), but returns the Banner degree level name for others (Doctorate). Note that the apostrophe must be the character on the far left of the  key board under the ~.
This is a good example of how a variable can change the appearance of the data in Banner when it extracts the data.


MEMO_COMMENT:
FOR FACULTY: Begin times of faculty courses


MEMO_COMMENT:
FOR FACULTY: Term code of FACULTY member's assignment. To be used in letter to the faculty member.


Sample_output:
(A list, such as:)
Main Hall 102
Ivy Hall 200
Monty Hall 256

MEMO_COMMENT:
FOR FACULTY: Building and room of  ALL of a FACULTY member's assignments, in CRN order. To be used in letter to the faculty member.


MEMO_COMMENT:
FOR FACULTY: Lists CRN assigned to a faculty member in CRN order. To be used in letter to the faculty member.


MEMO_COMMENT:
Institutional GPA for the level selected, truncated to 2 decimal places. For other GPA types, change Line 2 value  to T (Transfer) or O (Overall) or use a dynamic prompt.


MEMO_COMMENT:
TERM institutional GPA, truncated to two decimal places.


Sample_output:
January 30, 2009  (letter was added to SUAMAIL on January 16, 2009)

MEMO_COMMENT:
This variable takes the date a specific Comm Plan letter  was added to the mail form, and HAS NOT YET BEEN SENT and adds 14 days to that date, formatting it as Month day, year. Client used this to inform student of deadline to return contract.


Sample_output:
March 15, 2009  (letter gen process run on March 1, 2009)

MEMO_COMMENT:
This variable takes TODAY'S DATE (the date user ran GLBLSEL for letter gen process) and adds 14 days to that date, formatting it as Month day, year. This is useful in letters since it calculates the date fresh each time so there is no need for maintenance of the hard coded date  in the letter.


MEMO_COMMENT:
This looks to see if an original award letter has been sent; in selected aid year. If so, then this indictors returns a value of 'y'. This is used in WORD logic in FAID award letters.


Sample_output:
Your award will be reconsidered (the comment actually reads REV:Your award will be reconsidered

MEMO_COMMENT:
Gets the REVISED COMMENT beginning with the 5th character in the comment. The comment text begins with REV:, with the message beginning in the 5th position. The sub query is needed to identify the latest comment with that comment code.


Sample_output:
RETURNS ONE OF THE FOLLOWING (years will vary based on term applied for)
May 1, 2009 (If applied for  200906:)
or
August 1, 2009  (If applied for  200909:)
or
December 1, 2010 (If applied for 200901)

MEMO_COMMENT:
Calculates deadline dates based upon the TYPE of term applied for. This makes it unnecessary to constantly update deadline dates in WORD letters. To change the Month and Day part of the variable, edit that information on the SELECT line. The SUBSTR returns the 1st 4 characters of the term code (in effect the year). The TO_CHAR converts that to characters so it can be appended to the month and day text.


Sample_output:
July 31, 2010  (decision made on June 30, 2010)

MEMO_COMMENT:
Adds 31 days to the most recent decision date on the app for a term. This assumes ONE app per term. Change 31 to some other value if needed. The sub query is needed to pull the latest decision information.


MEMO_COMMENT:
SUB QUERY
 to get latest decision date per app, per term. It assumes one app per term. It is used in variables and pop sels based upon latest decision. (The view SAVAPDC returns many fields connected to latest decision but does not include the latest decision date. Go figure!)


Sample_output:
RETURNS ONE OF THE FOLLOWING:

May 1, 2008  if applied for 200910
October 1, 2008 if applied for 200920
April 1, 2009 if applied for 200930

MEMO_COMMENT:
Calculates a response date based on the last 2 characters in term code  applied for. This automatically updates based on the term code, so can be used without need for changes. To change the Month and day, edit the month and day in the SELECT line.


Sample_output:
If letter gen is run on July 1, 2009, this variable returns the date 31 days after July 1, or August1, 2009.

MEMO_COMMENT:
This adds 31 days to whatever date letter gen was run. Change the 31 or some other number to change the returned value. Since the date is calculated, there is no need to maintain the date in a letter.


Sample_output:
15 (meaning there are 15 days left before the deadline date used in the SELECT line).

MEMO_COMMENT:
Calculates the number of days between a date in the SELECT line and the decision date. The calculation varies by term type (xxxx10, 20, 30).Note that the deadline dates differ from term type to term type. Used in WORD for messages based on how many days remain until that deadline. For instance, if the number is greater than X then insert the message 'due by Y'; if less than 'X', then 'due in two weeks or some such logic. The sub query is needed to pull the latest decision information.

USES_SQL_FUNCTIONS:
TRUNC, TO_DATE, TO_CHAR, TO_NUMBER, CONCATENATE


MEMO_COMMENT:
SUB QUERY
Used  in pop sels to exclude those with APPS for that term. Useful when selecting people who have inquired but not applied yet. You would add a final line in the pop sel to say xxxx_PIDM NOT IN (*SUB *HAVE_APP) where xxx is the name of the table referenced in the rest of the pop sel.


VAR_COMMENT:
Shows date transcript received, institution and major for each received transcript from prior colleges.

Sample_output:
TRANS RECEIVED ON: 15-JAN-2009   Bob's College  Bachelor' of Arts  Psychology
TRANS RECEIVED ON: 01-FEB-2009   SUNGARD University  Non-Degree Underwater Basket Weaving

MEMO_COMMENT:
Lists prior colleges transcript received date


MEMO_COMMENT:
Finds the program with the second highest sequence number. It is MANUAL because SMRPRLE contains a PIDM field (which you cannot see if you run GURPDED). If the variable were not MANUAL the process would try to match the PIDM of the student with this phantom PIDM and you would get 'bubkas on rye bread'. The sub query calculates the next to most recent sequence number.


Sample_output:
Must take Business 101 and get a grade of B or higher.

MEMO_COMMENT:
Client stores conditions for admissions as COMMENTS. Returns the comments with the code selected by the user when running GLBLSEL. For instance the comment code DENL could contain the reasons for denying an application. The code MAJR could contain some conditions imposed by the major of the application.


MEMO_COMMENT:
Lists the interests entered into the system within a range of dates. The client can include these codes in an ENCL: statement at the bottom of the WORD letter to help staff include the correct materials in the response envelope.


MEMO_COMMENT:
SUB QUERY
Finds the PIDMs of people with a RACE indicated on the race form. This is used in the variable *RACE which returns the RACE designation if it exists, otherwise the ETHNICITY code if a RACE designation does not exist.


Sample_output:
Asian  ( The race description if there is a RACE entry)
or
Asian-Pacific (the ethnic description if there is an ethnic entry and NO race entry)

MEMO_COMMENT:
This is a multi sequence variable that returns the RACE description from the race form  if one exists. If there is no RACE entered, then it returns the ethnic description from SPBPERS.
This is a good example of how to exclude PIDMs with some criteria from a variable.


MEMO_COMMENT:
Returns the letter name of a specific Banner letter that has been sent to the student. Can be used as an indicator in WORD to include specific text if this variable returns information.


MEMO_COMMENT:
SUB QUERY
The sub query finds people who were sent a specific Banner letter. It is used in pop sels to exclude these people from the pop sel results. See the pop sel GRADUATION_VERIFICATION. It selects people who have an expected graduation date and term but who have not yet been sent a specific letter (the client used the letter code REG_GRAD_VERIF.


MEMO_COMMENT:
Returns the program description. It is MANUAL because SMRPRLE contains a PIDM field (which you cannot see if you run GURPDED). If the variable were not MANUAL the process would try to match the PIDM of the student with this phantom PIDM and you would get 'bubkas on rye bread'


MEMO_COMMENT:
Returns the Program description for current active LEARNER program. To return ALL programs in the curriculum, change line 2 top SGVCCUR_ODER > 0. The sub query is needed to pull the information from the latest effective term for each person.


MEMO_COMMENT:
Returns the diploma name for those who have applied for graduation in the selected term


MEMO_COMMENT:
SUB QUERY 
used when getting current information from SGBSTDN.


MEMO_COMMENT:
CUM attempted hours for the level selected. Change line 2 (SHRLGPA_GPA_TYPE_IND) to 'O' for overall, or to 'T' for transfer only


Sample_output:
(A list, such as:)
Obama, Barack
Winfrey, Oprah

MEMO_COMMENT:
The client used EVENTS module to track orientation sessions and guests invited to them. This returns the names of the GUESTS associated with the PIDM of the student going to the orientation. That is why the variable is MANUAL


MEMO_COMMENT:
The client used the EVENT module to handle orientation sessions. This variable returns the name of the function to send to the student. They did a pop sel for each function and event, then a letter to the people selected  for that function and event showing them what sessio0n to attend.


Sample_output:
Oprah Winfrey (for majors that are NOT NAB or NRS)

or

Dr. Phil (for NAB and NRS majors)

MEMO_COMMENT:
The client wanted to include departmental secretary names in the student's letter. This variable looks at the current major and returns 1 of 2 secretary names based on the majors. The sub query is needed to pull information from the latest effective term.


MEMO_COMMENT:
Returns the names of all active sports of the student, sorted by sports name.


MEMO_COMMENT:
This returns a Y if the active  athlete has financial aid. This is used in the WORD logic to insert a message based on this value.


MEMO_COMMENT:
Accepted loan fund name/s and amount of the loan


MEMO_COMMENT:
The client used the COBRA area to handle retiree' benefits. This variable adds all the various amounts to be paid and  multiplies the sum by -1, thus making it a negative value. The client needed this so they could ADD this amount (the amount paid) to the amount due. See the variable *RET_TOTAL_DUE which adds two variables.


MEMO_COMMENT:
The client used the COBRA area to handle retirees' benefits. This variable adds all the various amounts to be paid.


MEMO_COMMENT:
This creates the amount due by adding the value returned from one variable to the value returned by another, and converting the result  to a dollar and cents format with a dollar sign in front. Note that the FROM line reads DUAL. You would always use DUAL rather than the table names, when creating a variable that uses other variables in the SELECT line. Include all the variables used to create the final one in the Banner letter, though you will only use the final one for data.


MEMO_COMMENT:
This variable returns the annual salary for  administrative employees with a salary increase for their primary job as of the effective date selected


MEMO_COMMENT:
This variable returns the hourly salary for hourly employees with a rate increase for their primary job as of the effective date selected.


MEMO_COMMENT:
This returns the date the salary increase became effective, formatted as Month dd, yyyy. The FM in the formatting instructions eliminate extra blank spaces in the month value.


MEMO_COMMENT:
The seniority date for the employee in Month, dd, yyyy format. The FM before the format removes any excess blanks created because the month field must be as long as the month name with the most characters.


Sample_output:
(A list, such as:)
Dickens, Charles: Probation ends on May 15, 2010
Nabokov, Vladimir: Probation ends on May 15, 2010
Winfrey, Oprah: Probation ends on June 1, 2010

MEMO_COMMENT:
This variable prints employee name and date of end of probation for all employees reporting to the supervisor selected in a pop sel and who have  probation  end dates between 30 and 60 days in the future. This information about the employee is going to the supervisor. Since the information it is pulling is not the from the supervisor's PIDM, but the employee's, the variable is a MANUAL TYPE.


MEMO_COMMENT:
This returns the employee's benefits category description


MEMO_COMMENT:
This pulls the employees home organization description


MEMO_COMMENT:
This returns employee long class description


MEMO_COMMENT:
SUB QUERY
Finds people who have received a selected letter. This will be used as a sub query in pop sels to exclude these people from the rest of the selected population. See the pop sel GRADUATION_VERIFICATION


MEMO_COMMENT:
Returns selected  attribute codes connected to an application for a term. To return ALL attributes remove Line 3.


MEMO_COMMENT:
SUB QUERY
Finds PIDMs of registered students to be used in pop sel to exclude registered students. See pop sel RECRUITS_HIGH_SCHOOL. It selects recruits who have not yet registered.


MEMO_COMMENT:
Finds PIDMS of any app for the term who has any missing checklist items. This is used in a pop sel to select people with other criteria and who have NO missing checklist items. See the pop sel SEND_MET_CHKL_LETTER


MEMO_COMMENT:
Returns the start date of the term applied for


Sample_output:
August 29, 2010 (for apps for 201070)
or
January 23, 2010 (for apps for 201020)
or
June 9, 2010 (for apps for 201040)
or
July 14,, 2010 (for apps for 201050)

MEMO_COMMENT:
This multi-sequence variable calculates the date for registration for each of 4 different terms. It calculates the YEAR to append to month and day date. To specify different months and dates, then simply change the information in the SELECT line. The year will calculate automatically based on the term code.


MEMO_COMMENT:
Creates a value of IN or OUT based on the application residency code. The codes of I,O,U return the value OUT; all other codes return the value IN. Used in WORD logic.


Sample_output:
YES  (the letter was sent)

or

NO  (the letter was not sent)

MEMO_COMMENT:
Multi-sequence variable returns a value of YES or NO depending on whether a person has been sent a specific letter. Used in WORD logic. The sub query is used in Sequence 2 to exclude people who have gotten the letter.


MEMO_COMMENT:
Returns all the cohort codes connected to an applicant. To create a variable to pull just ONE cohort code, copy this variable and add a line:
SARCHRT_CHRT_CODE = &COHORT_CODE


Sample_output:
DN (for any denied app)
or
IA_UG (for any accepted UG)
or
IA_GR (for any accepted GR)

MEMO_COMMENT:
Creates a value that combines the DECISION CODE and the LEVEL CODE into one field to be used in WORD logic. The first sequence returns just the decision code: DN. The second returns IA_UG or IA_GR. These values are used in the WORD logic. This is a simple example of how to create a variable that combines information so that you do not have to create complex WORD if statements to do the same thing.


MEMO_COMMENT:
The TOTAL LOAN amount offered from whatever loan fund/s the user enters.
Remember when responding the a prompt using the IN operator, you can list one or more answers as follows:
('value 1','value2','value3') etc., using parentheses around the entire list, single quotation marks around each value and commas between values.


Sample_output:
WINTER ENGINEERING
or
SUMMER ENGINEERING
or
WINTER NOT ENGINEERING
or
SUMMER NOT ENGINEERING

MEMO_COMMENT:
Multi sequence variable that returns a value based on college and term of application to be used in WORD logic to insert correct text. This client has separate messages based on a combination of summer or winter apps and whether the app was or was not for engineering.


Sample_output:
(A list, such as:)
February 1, 2010  Pell Grant
February 15, 2010 Basket Weaver Grant

MEMO_COMMENT:
Disbursement date and fund title for disbursements in a date range, in disbursement date order


MEMO_COMMENT:
SUB QUERY
 to pull earliest registration status date per PIDM per TERM from the FINAID view ROVSTCR; used in Pop Sel FA_REGISTERED.


MEMO_COMMENT:
SUMS all balances for all detail codes that are tuition codes, converts it to a character field so it can be formatted and formats it with a dollar sign and two decimal places.


MEMO_COMMENT:
SUB QUERY
Identifies PIDMs with a specific test code and score. It is used in a pop sel to find people who have some other test AND this one as well. See pop sel HAVE_BOTH_TESTS


MEMO_COMMENT:
SUB QUERY
 to get most recent comment of a particular originator for the term. Used in the variable *COMMENT_LATEST


Sample_output:
Must take summer courses in Math and English

MEMO_COMMENT:
Pulls the most recent comment text for the selected comment ORIG code, The sub query is needed to pull the information from the latest comment with that ORIG code for the term.


Sample_output:
Oprah Winfrey High (for N student type)
or
College of Hard Knocks (for T student type)

MEMO_COMMENT:
This multi-sequence variable returns the HIGH SCHOOL name if the applicant is a NEW student, or  the PRIOR name/s if the applicant is a TRANSFER. This variable makes it unnecessary to have TWO variables, one for N apps and one for T apps, and provides one data field for prior school for WORD letters.


MEMO_COMMENT:
Description of the active employee's leave category.


MEMO_COMMENT:
Beginning date of PROBATION for an active employee's primary active position.


MEMO_COMMENT:
End date of PROBATION for an active employee's primary active position.


MEMO_COMMENT:
This multi-sequence variable returns the value of TERM for any active TERM employee and REG for any active regular employee. This is used in WORD logic.


MEMO_COMMENT:
Position number for the primary active position


MEMO_COMMENT:
SUB QUERY
 to find the latest personnel change date for PIDM, position and suffix. Used in the variable *SALARY_ANNUAL_LATEST to pull the latest salary change.


MEMO_COMMENT:
Salary amount for the latest salary change for employees other than terminated employees. The sub query is need to select info connected to the latest personnel change date.


MEMO_COMMENT:
Returns the latest change date for the employee classes selected.


MEMO_COMMENT:
SUB QUERY
PIDMs of students with changes in registration within a date range. This will be used as a sub query in the variable *STUDENT_REG_CHANGE which will display information about students who have changed registration. This variable will be used in a letter TO the faculty member who has students in any CRNs who have dropped or added courses.


Sample_output:
(A list, such as:)
Stewart,, Martha ECON 101-03 - DROPPED
Winfrey, Oprah  READ 300-01 - ADDED

MEMO_COMMENT:
This brings back information about students who have dropped or added courses within the date range specified in the sub query. This info is to be included in a letter to the faculty member in the affected classes. This is a MANUAL pop sel because it is pulling info FOR one PIDM (the faculty member) ABOUT another PIDM (the student). The sub query is needed to identify the students who have dropped or added.


Sample_output:
(A list, such as:)
SAT Verbal   : DATE TAKEN : November 15, 2009  - SCORE : 750
SAT Verbal   : DATE TAKEN : January 15, 2010 - SCORE : 800
SAT Writing   :DATA TAKEN : January 15, 2010 - SCORE : 800

MEMO_COMMENT:
Test name, date taken and score for selected tests ordered by Test name and then by test date. If the person has taken the same test more than once, all results are shown here


MEMO_COMMENT:
Returns the codes  for ALL INTERESTS entered in a date range for the LATEST recruit record for a term. It uses a sub query to identify the latest recruit record,  The sub query is on the last line. The code for that sub query is also in this data base.


Sample_output:
(A list, such as:)
Catalog
Government Club
Political issues

MEMO_COMMENT:
Returns the DESCRIPTIONS for ALL  INTERESTS entered in a date range for the LATEST recruit record for a term. It uses a sub query to identify the latest recruit record,  It is on the last line. The code for that sub query is also in this data base.


Sample_output:
RETURNS ONE OF THE FOLLOWING: 

UNOFFICAL RECEIVED (Sequence 1)

or  

UNOFFICIAL NOT RECEIVED (Sequence 2)

MEMO_COMMENT:
This is a 2 sequence variable that returns text to indicate whether a HS transcript has been received or not. Seq1returns: 'UNOFFICIAL RECEIVED'; Seq 2 returns:' UNOFFICIAL NOT RECEIVED'. Used in WORD logic to insert different text based on what the variable returns.


MEMO_COMMENT:
SUB QUERY
Earliest contact date for the PIDM. Do not use by itself. Use only as a sub query  in other variables or pop sels.


Sample_output:
(A list, such as:)
ACT Composite
SAT Math
SAT Verbal

MEMO_COMMENT:
Lists the names of all  tests in the person's record, ordered by test name.


MEMO_COMMENT:
Returns the ONE preferred email address regardless of type. By definition preferred email addresses are active; an address cannot be preferred AND inactive.


MEMO_COMMENT:
SUB QUERY
This variable creates a sub query to pull the highest score for a given test code. Sub queries such as this are used in other variables and pop sels as needed when pulling info about the highest test score.


Sample_output:
RETURNS ALL CANCELLED CRNS FOR STUDENT, FOR INSTANCE:
10001  ANTH 101  01
10012  ANTH 230  04

MEMO_COMMENT:
FOR STUDENT: Shows STUDENT'S CRN, SUBJECT CODE, CRSE NUMBER AND SECTION for cancelled CRNs, ordered by CRN
Shows cancelled courses ONLY


MEMO_COMMENT:
Returns one half of the room rate charge for terms ending in 20 and rates ending in R. Lines 5 and 6 are client specific.


MEMO_COMMENT:
SUB QUERY
 to be used in other variables and pop sels where latest SIBINST record is needed.


MEMO_COMMENT:
SUB QUERY
to be used in variables and pop sels in which latest SIRDPCL record is needed.


VAR_COMMENT:
CRN, subject. course number, section of all courses assigned to a faculty member in a term., ordered by CRN.

MEMO_COMMENT:
FOR FACULTY: CRN, SUBJ, NUMBER and SECTION of assigned faculty courses in a term


VAR_COMMENT:
FOR FACULTY: Course titles of all assigned courses for a FACULTY member  in a term; ordered by CRN. To order by course title,, change the ORDER BY to SSBSECT_CRSE_TITLE.

Sample_output:
(A list, such as:)
Peoples of the Americas
Asian Ethnographies
Population Genetics

MEMO_COMMENT:
FOR FACULTY: Building and room of  ALL of a FACULTY member's assignments, in CRN order.


MEMO_COMMENT:
FOR FACULTY: End times of all CRNs assigned to a FACULTY member in a term,  ordered by CRN. To be used in letter to the faculty member.


MEMO_COMMENT:
FOR FACULTY: Days of ALL of  a FACULTY member's assigned courses. This will appear as MWF, TH, MWFS, etc., ordered by CRN.


MEMO_COMMENT:
FOR FACULTY: Part of term code for assigned courses  used in letter to the faculty member.


MEMO_COMMENT:
FOR FACULTY: Part of term start date for assigned courses for use in a letter to the faculty member.. To format it differently use the following code:TO_CHAR(SSBSECT_PTRM_START_DATE, 'Month dd yyyy') on the SELECT line. This will format it with the month spelled out as in January 1, 2010.


MEMO_COMMENT:
FOR FACULTTY: Part of term end date for assigned courses for use in letter to the faculty member. To format it differently use the following on the SELECT line: TO_CHAR(SSBSECT_PTRM_END_DATE, 'FMMonth dd yyyy'). This will format it with the month spelled out as in: 'January 1, 2010'


MEMO_COMMENT:
FOR FACULTY: This uses a sub query to return most current FACULTY department. See sub query *SUB_MOST_REC_SIRDPCL. To be used in letter to the faculty member. The sub query is needed to pull information from the latest faculty record.


MEMO_COMMENT:
Counts how many times a specific letter has been sent previously in a given term. Use this in WORD to insert different messages based on the number of times the letter had been sent previously.


MEMO_COMMENT:
Active PERSONAL email address. TO pull a different  email address change the value of GOREMAL_EMAL_CODE


MEMO_COMMENT:
Returns the phrase 'APP EXISTS' if the recruit has an application in the system for the selected term or any later term. I have used this in letters to recruits as an indicator for WORD to insert some message if the person already has an app in the system.


MEMO_COMMENT:
This variable will center the concatenated first and last name in the middle of the returned variable. Usually you could do this in WORD, but the client wanted to export it to E-Visions directly.


Sample_output:
Campus Visit    03-SEP-2009
Campus Tour   04-SEP-2009
Weekend    10-OCT-2009

MEMO_COMMENT:
Lists all contact descriptions of contacts codes that begin with the letter C and their dates in ascending date order. To include ALL contacts, remove Line 2.


MEMO_COMMENT:
Father's first name, if living.
Use this pattern to select other fields from SORFOLK. For instance SORFOLK_PARENT_LAST gets parent's last name for whatever SORFOLK_RELT_CODE your specify.


MEMO_COMMENT:
The name attached to the contact code GUID for the primary source. This will be the name of the guidance counselor at the primary source high school.


MEMO_COMMENT:
The area code and phone number attached to the contact code GUID for the primary source


MEMO_COMMENT:
This will return the DATE of the first MONDAY after today's date plus 14 days. If today is March 1, then this returns the first MONDAY after March 15th


MEMO_COMMENT:
SUM of all charges made before the selected date . To sum the PAYMENTS change Line 2 value to 'P'.


MEMO_COMMENT:
List of all MEMO amounts, by term and detail code


MEMO_COMMENT:
List of descriptions of the MEMOs by term and detail code


MEMO_COMMENT:
Names of teams person is active or 'res-shirted' on in a term in alpha order.


Sample_output:
Barack (the value in SORBCNT_NAME) is Barack Obama)

MEMO_COMMENT:
This client stored 'counselor' first and last name on the SORBCNT table in the SORBCNT_NAME field as 'Barack Obama'. This variables looks at that info and returns everything before that space (' ') between the first and last name. The first sequence pulls the name based on the HIGH SCHOOL SBGI code; the second sequence pulls it based on the PRIOR COLLEGE SBGI code. This ONE variable then pulls either the HS or prior college counselor name. It would pull BOTH if someone had both, but the client stored only one or the other.


Sample_output:
Obama (data is Barak Obama  Email: obama@xxx.edu)

MEMO_COMMENT:
The client stores counselor info as 'Barack Obama  Email: obama@xxxx.edu'.
This variable returns everything after the space between first and last names and up until the spaces after the last name, and trims off everything after that. To create a sequence for prior college counselor, replace SORHSCH with SORPCOL


Sample_output:
obama@xxx.edu (data is Barack Obama  Email: obama@xxx.edu)

MEMO_COMMENT:
The email is stored on the SORBCNT table as' Barack Obama  Email: obama@xxx.edu'. This variable returns everything from the colon (':') onwards.
To pull the college counselor, replace SORHSCH with SORPCOL. Do this as a second sequence in this variable and the one variable will pull both the HS or prior college counselor.


MEMO_COMMENT:
Returns the phone number/s of all roommates if the number is listed. Otherwise it returns the value 'UNLISTED'


MEMO_COMMENT:
SUB QUERY
May have lost the code…need to find it


MEMO_COMMENT:
SUB QUERY
to get latest comment date for each comment type.. Used in other variables and pop sels to select information for the latest comment of that type..


Sample_output:
'in thirty days' (term code ends in 10 and decision is BEFORE Dec 10, 2008)
'within the next two weeks'( term code ends in 10 and decision is after Dec 10, 2008)
'in thirty days' (term code ends in 60 and decsion date is before April 1, 2008)
etc.

MEMO_COMMENT:
This multi-sequence variable returns a different deadline text based upon term type and the date of the decision. The sub query is needed to pull the latest decision information .


MEMO_COMMENT:
The client offers reciprocity to apps from certain counties in neighboring states. The variable returns the values OHIO or KENTUCKY based on the state and county of the preferred app. Remove Line 9 if app preference is unimportant.


MEMO_COMMENT:
The client stores a dollar amount in an institutional data field. This returns that amount.


MEMO_COMMENT:
This can be used as a sub query. See variable *EMAIL_ITCC_OR_RC


MEMO_COMMENT:
This is a sub query that identifies PIDMs who have a school email at this particular client. It is used in the variable *EMAIL_ITCC_OR_RC.


MEMO_COMMENT:
This is a 2 sequence variable
Seq 1 returns the GOREMAL SCHOOL (in this case ITCC) email address if it exists.
Seq 2 returns the RCRAPP4 email address if the GOREMAL address does not exit. The sub query in the 2nd sequence is needed to exclude people who DO have the ITCC email address.


MEMO_COMMENT:
SUB QUERY
Finds the latest ROVSTDN TERM to be used as a sub query in pop sels and variables requiring latest student data.


Sample_output:
College of Engineering (a  non 09 college)

or
School of Design (for 09 college)

MEMO_COMMENT:
The client wanted the text 'College of ' to appear in front of the college description for all colleges except the one coded '09'. For the  09 college, the variable returns whatever is in the college description.  The sub query is needed to pull the information from the latest effective term for each person. This is a good example of how to use a variable to change the appearance of the data in Banner when extracting it.


MEMO_COMMENT:
Finds the phone number associated with the selected phone and relationship types.


MEMO_COMMENT:
Mother's last and first names with MOTHER prefix.
Change Line 1 and the hardcoded text ('MOTHER' ) to create variables for other relatives.


MEMO_COMMENT:
Street line `1 of address from the address type selected for the relationship type selected


Sample_output:
5694  (if the recruiter is MBS)
or

1234 (if the recruiter is XYZ)

MEMO_COMMENT:
This variable will return the phone extension number of the recruiter referenced in the field SARADAP_RECR_CODE. It has many sequences, one for each recruiter. I have included only 2 as examples.


MEMO_COMMENT:
PIDMs existed with no physical address, but with an email address. By making this a FIRST type variable, GLBLSEL will use this variable rather than the address hierarchy to return data for the PIDM. When GLBLSEL runs, it will look to see if the PIDM returns a value for the FIRST type variable. If it does, then GLBLSEL will return info for all the other values; if it does not, then GLBLSEL will return NO info for any of the variables in the Banner letter for that PIDM.. In this case, the process returns info ONLY for those with an active email address of the type specified. This is analogous to what happens if the PIDM does not have an address of the type specified in GLBLSEL address hierarchy. The delivered name and address variables use the view SPVADDS and function as FIRST type variables. When using FIRST type variables you create, you cannot use ANY of the SPVADDS variables, but must create new one based on the base tables rather than the view SPVADDS. The Banner letter cannot contain any SPVADDS variables. You need only 1 FIRST type variable in the Banner letter. The other variables can be of any other type.


MEMO_COMMENT:
FIRST  TYPE variable that pulls LAST name. Use this INSTEAD of the delivered last name variable that uses the SPVADDS view if you want to NOT have to use the address hierarchy on GLBLSEL


MEMO_COMMENT:
SUB QUERY
This sub query identifies the latest recruit record for a person and term.


MEMO_COMMENT:
The area code and phone number attached to the contact code GUID for the  primary source high school.


MEMO_COMMENT:
Returns the HS rank if there is one. If there is no rank, returns  a zero.


MEMO_COMMENT:
The result of an appointment that took place within the date range and of the type specified


MEMO_COMMENT:
Month of graduation date for the first degree sequence. To list all grad months of all degree sequences, delete line 1.You might then want to add SHRDGMR_SEQ_NO to the Order By line.


MEMO_COMMENT:
Month and year of graduation date of 1st degree sequence. For all degree sequences, remove Line .


MEMO_COMMENT:
Honors associated with degree sequence 1


MEMO_COMMENT:
Returns the FIRST character of the room assignment. This assumes the first character indicates the floor of the room. Line 3 is unnecessary if you want assignments for ALL housing statuses that are to be counted in usage.


Sample_output:
07-MAY-2011  Tuition
11-MAY-2011  Library fine

MEMO_COMMENT:
Transaction date and description for all charges made after a selected date ordered by date.
To create a variable for PAYMENTS only, change the value in LINE 2 to 'P'


Sample_output:
SAT Verbal - SCORE: 800

SAT French -SCORE: 800

MEMO_COMMENT:
The CHR function Lets you insert an ascii character, such as a hard return to produce a blank line. The CHR(10) inserts a hard return. CHR(35)||'S 1' inserts a blank line.


Sample_output:
Nokomis, FL  34275  (US)

Sydney, W4f5H6 (Non US with no STATE field

Toroto, ONT  A6Y6K8 (Non US with STATE field)

MEMO_COMMENT:
A multi-sequence variable that creates a city-state-zip line appropriate for US and international addresses.


MEMO_COMMENT:
This multi-sequence variable creates a 'program director' based on major code. Change line 6 if app preference is not important; change line 5 to include different majors.


MEMO_COMMENT:
Returns the major description from the application. If application preference is not an issue, remove Line 6. Create variables for MINOR and CONCENTRATION by copying this one, changing the name and changing Line 4 to 'MINOR' or 'CONCENTRATION'


MEMO_COMMENT:
This creates an indicator that returns the value 'UG' for 3 different student types for use in WORD logic.


MEMO_COMMENT:
Counts the number of detail codes with balances greater than zero. To be used along with *DETC_COUNT_LT_ZERO to construct the variable *NSF_COUNT


MEMO_COMMENT:
Description of the employee's home department


MEMO_COMMENT:
This takes the beginning balance of leave time (if there is any), adds any accrued time (if there is any) and then subtracts any leave taken (if there is any) for the categories in Line 1 to return the number of days now available. The NVL is required so that if the values in the field are ZERO, the system returns a ZERO rather than a BLANK.


Sample_output:
January 16, 2010 (if today's date is January 1, 2010)

MEMO_COMMENT:
This adds 15 days to today's date and returns it in the format Month (name spelled out), day, four character year. Use any number to add that many days. Use a negative number to create a date X number of days in the past.


Sample_output:
February 1, 2010 ( if today's date is January 1, 2010)

MEMO_COMMENT:
Adds ONE MONTH to today's date. To SUBTRACT one month from today's date, change the 1 to -1. This can be used to add or subtract any number of months from today's date.


MEMO_COMMENT:
This variable pulls the Street Line 2 from SPRADDR for the address type chosen. It does NOT use the SPVADDS view and therefore should  be used to pull address info in Banner letters in which another variable is the FIRST type variable.


MEMO_COMMENT:
This variable pulls the CITY from SPRADDR for the address type chosen. It does NOT use the SPVADDS view and therefore should  be used to pull address info in Banner letters in which another variable is the FIRST type variable.


MEMO_COMMENT:
This variable pulls the STATE CODE from SPRADDR for the address type chosen. It does NOT use the SPVADDS view and therefore should  be used to pull address info in Banner letters in which another variable is the FIRST type variable.


MEMO_COMMENT:
This variable pulls the ZIP CODE  from SPRADDR for the address type chosen. It does NOT use the SPVADDS view and therefore should  be used to pull address info in Banner letters in which another variable is the FIRST type variable.


MEMO_COMMENT:
Pulls trhe current STREET LINE 1 for the address type selected.


MEMO_COMMENT:
FIRST  TYPE variable that pulls FIRST name. Use this INSTEAD of the delivered last name variable that uses the SPVADDS view if you want to NOT have to use the address hierarchy on GLBLSEL


MEMO_COMMENT:
This variable pulls the Street Line 1 from SPRADDR for the address type chosen. It does NOT use the SPVADDS view and therefore should  be used to pull address info in Banner letters in which another variable is the FIRST type variable.


