From ITS Wiki

The Returning Student Merit Aid Rollover process, also known as CNTRmrtroll, is a local process that allows a user to copy certain types of merit aid from one financial aid year to another for a group of returning students in batch. This saves the Office of Student Financial Planning a great deal of manual data entry.

A presentation on the process was given at JAM 2008 by Stacie Yates and the process was packaged for download on the NACU Software Exchange at The NACU Website. The Powerpoint presentation is available from JAM 2008 Presentations: 511 Automating Award Rollover PDF or JAM 2008 Presentation: 511 Automating Award Rollover Powerpoint.The following is an edited version of the README included with the Software Exchange download.

This process is usually run by IT, at the request of Financial Aid, in the Mid-May timeframe. It is one of the first stages of packaging financial aid for returning students. The typical workflow is to run it once, in review mode, and allow the Office of Student Financial Planning to review the report produced. Then, preferably on a quiet system, run the process in update mode. It's best to request that the employees of the Office of Student Financial Planning log out of CX to prevent running up against a locked record. The process should only take a few minutes to complete.

README

README for the Centre Merit Aid Rollover process

Overview: This process “rolls over” aid records from one financial aid year to another for returning students. The process primarily affects what Centre qualifies as “merit aid” and saves a great deal of manual entry of aid awards.

Number of Files: 3

Files Involved:

                        $CARSPATH/modules/finaid/scripts/CNTRmrtroll
                        $CARSPATH/modules/finaid/reports/CNTRmrtroll
                        $CARSPATH/menuopt/finaid/scripts/CNTRmrtroll

File description: menuopt

The CNTRmrtroll menuopt accepts three parameters. The Source Aid Year, the Target Aid Year and the Update Flag.

The Source Aid Year is the aid year the process will use to “copy from”. The Target Aid Year is the aid year the process will “copy to”. If a student has one of the pieces of aid targeted for rollover in the Source Aid Year, and the student meets certain conditions, the student will also receive aid records for the same aid code and amount in the Target Aid Year. For example, a user might provide the years “0708” and “0809”. A student, Jane Doe, a returning Junior in good standing with an Alumni Scholarship of $7000 per semester in the Fall and Spring semesters of financial aid year 0708 would have two new aid records after the process completes. One for Fall of the financial aid year 0809, for the ALUM aid code, worth $7000, and an identical one for Spring of financial aid year 0809.

The third parameter, the Update Flag, allows the process to be run in either “report only” mode or “update” mode. The “report only” mode (Update Flag = “N”) creates an informative, detailed report that denotes the action to be taken for each student and emails it to the user. The “update” mode (Update Flag = “Y”) updates the database by creating and inserting aid_rec, stufa_rec, and aidpkg_table entries where appropriate. This mode also takes further steps to prevent error or warning messages, such as running the “nonull” process on these tables and running the financial aid audit program, faaudit.

File description: script

The CNTRmrtroll script handles “traffic control”. If the process is run in “review mode”, the scripts calls and runs the CNTRmrtroll ace report with the appropriate parameters and sends the user email on the status of the process. It also emails the results to the user as a Word attachment.

Note: The script mails the attachment by means of a command known as “attachme”. This, much like “mailme”, is a virtual printer. They are not part of the standard product. If they are present on your system, you should see scripts by those names in modules/util/commands. The “mailme” command sends text as an email message body, while “attachme” presents the output as an attachment. Given the size of this report, an attachment is much more versatile and easy to read. More information on these utilities can be found by searching the CX-TECH mailing list archives for “attachme” or “mailme”.

When run in “update” mode, the script still runs the CNTRmrtroll report in review mode and emails it to the user. Then it runs the report in “update” mode, which produces an output file full of sql statements. The script then executes these sql statements by piping them to isql. The resulting status messages are also emailed to the user, which can be useful for troubleshooting or confirming the statements executed successfully.

The script then runs the “nonull” command on aid_rec, stufa_rec and aidpkg_table. On Centre’s system, any user has rights to execute the nonull command. You may want to confirm that the user will have rights to execute this utility. If the nonull command is not run on these tables, Finally, the script runs the financial aid audit (faaudit) in update mode and emails a copy to user “stacie” in addition to the user running the process.

NOTE: You may wish to edit the “–U stacie” to email the audit to an appropriate user in your IT or Student Financial Planning/Financial Aid department. Or you may wish to remove the parameter entirely, in which case the audit will simply mail the audit results to the person running the script.

File description: ace report

The ace report, depending on the value of the update parameter, either produces a people-friendly report detailing what action, if any, will be taken on each student, or produces SQL statements to take the action. The report requires three parameters, a financial aid source year, a financial aid target year, and the aforementioned update parameter. The script will select all students who have qualifying aid in the source financial aid year. Qualifying aid will be “rolled over” to the target year if the student meets additional criteria.

NOTE: Centre identified qualifying aid as aid that rarely requires adjustment to award amounts from year to year and aid that is rarely taken away from students due to compliance issues. At Centre, most qualifying aid is identified by a local field in the aid_table, called merit_code. Each institution will need to define the hallmarks of aid that can be easily copied over from year to year and will need to adjust the selection into temporary table “rollovercodes” accordingly. Also note that Centre awards aid in Fall and Spring semesters only.

Centre’s qualifying aid falls into the following categories:

1. Any aid marked in the aid_table with the field merit_code = “Y”, with the exception of the A.H. Lively Scholarship, aid code LIVE. At Centre, aid marked as “merit” are usually offered by our Admissions office. We use this local field for a variety of reports and processing.
2. Additional aid codes including the Missouri Award, Indiana Award, Legacy Award, Fine Arts Scholarship, and KEES Scholarship.

The above codes are pulled into a temporary table called “rollovercodes”. All students with these aid codes awarded in the source financial aid year are selected into the report population.

If a student meets all of the following conditions, they are a rollover candidate.

1. The student is currently an underclassman/not a senior (prog_enr_rec.cl != “SR”). Centre rarely has returning fifth-year seniors, and when they do, Student Financial Planning prefers to be made aware of them, but to review them on a case by case basis, since they are usually not eligible for aid.
2. The student is not scheduled to graduate in Winter or Spring term within the source financial aid year, and will not be returning for an apparent fifth year. This determination is made using prog_enr_rec.plan_grad_sess , prog_enr_rec.plan_grad_yr, and prog_enr_rec.adm_yr.

Should the student qualify as a rollover candidate, the report creates SQL statements to insert Fall and Spring aid records for the target financial aid year. The aid_recs are inserted with pkg_stat = “M”, aid_stat = “A” and amt_stat = “AA”. The upd_uid is set to “-100”, to help identify aid records added by the process. Or, if the ace is being run in report only mode, the report produces text that describes the action to be taken when the report is run in update mode.

The report produces statements to create stufa_rec entries for each rollover candidate in the target year. If the student already has a stufa_rec for the target year, the insert will not complete. This can result in "insert failed" errors in the SQL results mailed to the user. These can safely be ignored.

The insert statement exists solely to create stufa_recs for any students who may not have them. The package status is defaulted to “A” for accepted, so the aid will post. Centre also defaults in the UNDG program, a pst_stat ( a Y/N flag indicating whether there are financial aid adjustments to be disbursed) of “N”, and a blank package group code. The user id is set to “-100” to distinguish them from manually added records. The assumption is made that these students did not apply for financial aid, therefore parental income, student income, need, etc. were not entered. The record also defaults to full time = “Y”, since we have few part time students.

There is also an update statement that updates the stufa_rec.pkg_stat to “A” for accepted if the record already exists. This forces all packages for these students to “Accepted” status.

The report then generates statements to compare the aidpkg_table entries for the source year to the target year. If any aid codes are missing from the target year, (i.e. Financial Aid personnel have not yet manually awarded students aid of that type in the target year), the entries are added. The Financial Aid office may be more familiar with this table as the “Fund Balance Table”. Values are simply copied from the previous year’s entry, and may need to be adjusted. (For example, the amount allocated to award.) These aidpkg_table entries must be there in order for faaudit to run properly.