Efficient Automation Strategies for Financial Aid at UTHSC

Creative Tips using Automic WILLIAM E. BRANCH, UNIVERSITY OF TENNESSEE HEALTH SCIENCE CENTER OCTOBER 12 , 2:00PM SESSION ID # 2.4

Introduction In 2017, UTHSC began the automation of Financial Aid. After a year of development, the automation went live and was largely successful. Many jobs that were run once a week were now run daily. Also, many new jobs were added. Many of these jobs have output that needs to be examined. The chains were sending around 100 reports per day. Financial Aid struggled to keep up with the sheer volume of reports to be examined. This is an ongoing effort, but the number of reports has been reduced to about 30 per day. My current emphasis is coping output reports to shared folders as an alternative to email notifications. This presentation is about the ITS effort to reduce the workload on personnel working reports from Automic jobs. 2 COHESION SUMMIT

Agenda 1 Substitution Variables 2 Notifications 3 Conditions 4 Output Scans 5 File Copies 6 RORAPLT 7 RLPDLOC 8 RERIMXX 9 RORBPST 3 COHESION SUMMIT

Substitution Variables

Substitution Variables Substitution variables start with # and are enclosed by brackets {} Use substitution variables to avoid hard-coding Substitution variables can receive values from PL/SQL procedures 5 COHESION SUMMIT

Substitution Variable #popsel_count Calls a PL/SQL Procedure Use :result for return value 6 COHESION SUMMIT

PL/SQL for #popsel_count 7 COHESION SUMMIT

Substitution Variable #popsel_application Holds value of Banner PopSel Application Used as parameter in #popsel_count SQL procedure call 8 COHESION SUMMIT

Substitution Variable #fa_reports_day Holds the day of the week for our once-a-weeks reports 9 COHESION SUMMIT

Notifications

Notifications Based on task statuses Consider avoiding creating notifications with specific text Use substitution variables to create general purpose notifications 11 COHESION SUMMIT

UTHSC Financial Aid Notification Groups FA_ADMIN Everyone in FA Administration FA_ADMIN2 Everyone in FA Administration except the FA Director FA_GHS College specific reports for GHS FA_COM College specific reports for CoM FA_COD College specific reports for CoD FA_COP College specific reports for CoP FA_CON College specific reports for CoN FA_CHP College specific reports for CHP BANNER_LOG_ERROR Notification of error in log file BANNER_LOG_WARNING Notification of warning in log file FA_CHAIN_ERRORS ITS notification of chain issues 12 COHESION SUMMIT

FA_GHS Notification Object 13 COHESION SUMMIT

Conditions

Conditions Conditions are a powerful tool for controlling the execution of tasks Conditions can be placed on chains as well as tasks Conditions can be executed before, after or during a task Conditions can store values in substitution variables Conditions can skip tasks Conditions can send emails Conditions can check for the existence of a file 15 COHESION SUMMIT

Conditions Example First store values for #popsel_count SQL parameters If #popsel_count is 0 then we skip this task Populate #file_pattern so we only send the .lis file Send reports to FA_GHS notification group 16 COHESION SUMMIT

Output Scans

Output Scan for Student ID Output scans are Applications Manager objects used to scan output for text strings This example scans the .lis output files for a UTHSC ID (begins with 885) If 885 is found, change the job status to Students_Yes 18 COHESION SUMMIT

File Copies

File Copies Copy files from Banner jobs directory to shared folders Develop Bash script to copy files Meet with Financial Aid Director to decide directory structure and file naming conventions 20 COHESION SUMMIT

Bash Script copy_job_output.sh 21 COHESION SUMMIT

Automic Job to Execute copy_job_output.sh 22 COHESION SUMMIT

Automic Condition for COPY_JOB_OUTPUT Some Banner jobs are exception reports so skip task if the file does not exist 23 COHESION SUMMIT

Automic Prompts for COPY_JOB_OUTPUT Prompts: 24 COHESION SUMMIT

#build_directory Variable {#fa_banner_output_directory}{#fa_notification_group}/reports_{#today_char}/ 25 COHESION SUMMIT

#build_filename Variable {#chain_abbr}_{#job_title}{#file_suffix}_{#get_aid_year}{#file_extension} Set the #chain_abbr variable at the beginning of the chain #job_title is the title of the job in the chain, not the Banner job 26 COHESION SUMMIT

More Variables 27 COHESION SUMMIT

ISIR Load Chain We only use the reports from the RCRTPXX (Part 3) job We don t use the RCRTPXX.LIS file If they exists, we want the RCRTPXX_APPS.LIS, RCRTPXX_NEED.LIS, RCRTPXX_NSLDS.LIS and RCRTPXX_ERRS.LIS files 28 COHESION SUMMIT

Conditions on RCRTPXX_ISIR_PART3 Copies exception reports from RCRTPXX job 29 COHESION SUMMIT

Conditions on COPY_OUTPUT_ISIR_PART3-APPS Check for existence of file Use #file_suffix variable 30 COHESION SUMMIT

Prompts on COPY_OUTPUT_ISIR_PART3-APPS Add _apps to prompt 1, Source Filename 31 COHESION SUMMIT

RORAPLT CHAIN

RORAPLT RORAPLT is a simple applicant listing that lists the students in a PopSel. UTHSC has 8 different RORAPLT reports. Our RORAPLT reports are divided by college, so users only see their assigned students If the report is empty, do not send email notification. Execute only on Mondays 33 COHESION SUMMIT

Calling the RORAPLT_BY_COLLEGE Chain Here re 3 of the calls to our RORAPLT_BY_COLLEGE chain. These are the conditions on RORAPLT_BY_COLLEGE_IRS_03_04. 34 COHESION SUMMIT

RORAPLT_BY_COLLEGE Chain Divides the work by college Run for each RORAPLT report The dynamic parameter for college is the only hard- coded parameter Copy RORAPLT output files to appropriate folders 35 COHESION SUMMIT

PopSel FA_IRS_REQ_FLAG_03_04 Dynamic Parm. Dynamic Parameter &College divides report by college ROBUSDF is used to clear students from report 36 COHESION SUMMIT

Using ROAUSDF to Clear Students from a Report Clear students from RORAPLT IRS 03/04 report by entering any text into field 20 for that student 37 COHESION SUMMIT

Conditions on RORAPLT_GHS Gets PopSel count and skips job if 0 Send notification to FA_GHS Only send the .lis file Prepare variables for file copy 38 COHESION SUMMIT

RLPDLOC

Output Scan for Student ID Output scans are Applications Manager objects used to scan output for text strings This example scans the .lis output files for a UTHSC ID (begins with 885) If 885 is found, change the job s status to Students_Yes 40 COHESION SUMMIT

RLPDLOC_GHS Job Setup for Output Scan 41 COHESION SUMMIT

RLPDLOC_GHS Conditions Conditions on RLPDLOC jobs check status and only send notification if the output scan set the job s status to Students_Yes . 42 COHESION SUMMIT

RERIMXX

RERIMXX RERIMXX is Banner job that imports files from COD. The RERIMXX job includes loan issues in its log file. The goal is to alert users when issues are found. 44 COHESION SUMMIT

Output Scans for RERIMXX The Banner_LOG_SCAN output scan searches the log file for the text error or warning and if found sets the job status to UTHSC_ERROR or UTHSC_WARN . 45 COHESION SUMMIT

RERIMXX Conditions Check job status and send appropriate notification 46 COHESION SUMMIT

RORBPST

RORBPST RORBPST executes batch posting. Setup is executed in RORPOST. Use category in RORPOST to execute several postings with one run For automation, use ROPPCAT to set category UTHSC runs 7 different RORBPST jobs 48 COHESION SUMMIT

RORPOST Batch posting category can use multiple popsels We need to know if there s a student in any of these popsels 49 COHESION SUMMIT

Substitution Variable #popsel_count_rorpost Calls procedure that returns sum of popsels in RORPOST category 50 COHESION SUMMIT