Revision as of 14:06, 21 September 2012

Monthly installments are integrated into both web services and the desktop client. There are several stages to this process. This is designed around exporting data to Rotessa.

Enrolment

Enrolment happens through Registry OnLine Services for Students.

Bank and Branch Information

The data for the two validation tables, banks.dbf and branch.dbf, comes from http://www.cdnpay.ca.

There are several PDFs that should be downloaded and converted to text with one branch on each line. The utility program brnchcmp.exe can then be used to add/update branches. This will also note any banks that are missing.

Web Services

STUMENU.REL

ROTESSA will enable this menu item for students.

STMNTRAN.REL

The entry ROTESSA should be assigned text for the display of the link. Monthly Installment Enrollment is the value currently specified.

ROTESSA.REL

Rotessa.rel contains the year and term for current enrolment. This should be set to the Fall term after 15 June. This should be set to Winter after 1 December. The effect is that if the student enrolled for a prior year and term, they have the option of re-enrolling for the current term.

ROTESSA.HTM

Rotessa.htm contains the terms and conditions to which students are agreeing when they enrol.

Student Enrolment

Students then enter their bank, branch and account information. These are validated dynamically. When the student also checks the agreement checkbox, the submission button is enabled. An email is sent to the student and the defined online services address with this agreement.

Students can subsequently enter the site and withdraw their enrolment if they have not confirmed for that term, or update their banking information.

Email to the defined registry address is generated on withdraw or updates to the defined online services address.

Exporting Enrolment to Rotessa

Exporting to CSV

Rotessac.exe is the utility to create a csv file for importing into Rotessa. This file generates output that needs to be piped into a file to save the data.

The parameters for this program are:

Group - for King's this is Undergraduate Year - e.g. 2012/13 Term - e.g. 1 TestFee - e.g. SSF Owing Threshold - $40 (given that the fee is $37.50).

So an example would be

    rotessac Undergraduate 2012/13 1 SSF 40.00 > rot20120920.csv

The output will be CSV lines and some error messages. The lines with error messages should be removed from the file prior to upload to Rotessa. The types of errors that may come up are:

25643 has recent transaction activity -- skipping which means that the student has had financial activity (potentially affecting whether he/she needs the installment plan) either on the current date or the prior business day. These could be payments or a/r entries.

Insufficient credit 20120222 9.50 12.00 which means that the student, whose id is listed, is having financial aid excluded because they are in too few credits (the number after the student id) than the aid item requires (the final number).

No balance owing record for 99999 which means that there is no exported record for this student from AccPac

Invalid bank/branch for 99999 which means that the bank/branch information is incorrect. This should not happen except in teh case of a branch that closed.

Error retrieving credits for 2012/131ENGL204 which would mean that a course offering was deleted when students were still registered.

Importing to Rotessa

Creating Payments

Creation of Payments in CrossRoad

In Current Students on the Installments tab, the Create button will calculate the amount owing from the current balance less any unreceived student loans and unapplied financial aid. Once the amount is calculated a query asks whether the create payments. If yes then a dialog to get the dates of the payments is presented -- this prepopulates with the first of the next three months from the current date. The payments are then created or updated if there is an existing payment for that date.

(Note that updates should be immediately manually updated in Rotessa.)

Students should be emailed with the results of this. InstlEml.exe creates a file for merge emailing payments to students. The parameters are year and term.

Emailing Students with their payment schedule

To produce these, use the utility InstlEml.exe. Year and term are the parameters and the output should be redirected. E.g.

  InstlEml 2012/13 1 > instleml.tsv

Open instleml.tsv (or wherever you redirected the output) in Excel. It is tab delimited. Save that as a workbook such as U:\CrossRoad\DBF.MM\instleml.xlsx.

Then use and edit the merge print template, L:\Installment Initial Email.docx

Merge this to Email messages.

Exporting Payments to Rotessa

Detecting Payment Excess/Insufficiency

This is done by running the utility instlprb, with year and term as the parameters. Output may be redirected.

    instlprb 2012/13 1

This will show each person with an installment entry whose installment payments are too high or too low to address their balance owing.

Upcoming Payment Alert Email

A utility INSTLEM2.EXE will produce a batch file (by redirecting the output) and html files for the content of each email. The template for the content file is INSTLEM2.HTM and it may have the following replaceable parameters:

[[FNAME]] - the preferred name for the student
[[SURNAME]] - the surname for the student
[[FILE#]] - the file# for the student
[[PAYDATE]] - the payment date
[[PAYAMOUNT]] - the payment amount

So an example might be

   instlem2 2012/13 1 20121001 > instlem2.bat

Instlem2.bat would then contain commands calling cmdemail using the settings file instlem2.eml for each student email address, and a delete command to tidy up the html.

This should be triggered to run 5 business days prior to the payment date.

Adjusting Payments

In CrossRoad

In Rotessa

Receiving Payment Data from Rotessa