Revision as of 14:13, 3 October 2012 (view source) WikiSysop (talk | contribs) (→‎Receiving Payment Data from Rotessa) ← Older edit		Latest revision as of 04:27, 19 September 2014 (view source) WikiSysop (talk | contribs) (→‎CrossRoad Process)
(10 intermediate revisions by 2 users not shown)
Line 1:		Line 1:
	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.		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.
		+
		+	= Overview =
		+
		+	This process is depicted in this swim lane chart. [[Image:Installments.png|thumb]] Note that a number of the processes include substeps detailed below.
	= Enrolment =		= Enrolment =
Line 40:		Line 44:
	== Exporting to CSV ==		== Exporting to CSV ==
		+
		+	Note that this documents both current and former process as we are in transition between the two.
		+
		+	=== CrossRoad Global process ===
		+
		+	In the Current Students Window, use the the menu item Students > Global Processes > Accounts Receivable > Installments > Export Installment Enrolments to launch this process.
		+
		+	The process will then ask for the relevant Year and term. Dates should be de-selected.
		+
		+	The next step will ask for a location in which to save the file.
		+
		+	The process will then proceed to export each student whose installment data is not marked as "disabled", who is deemed eligible to export, and who has a King's email address. Eligibility to export is determined by
		+
		+	- having a valid bank and branch (account codes cannot be checked)
		+	- having a SSF invoice entry (hence has confirmed)
		+	- having currently registered credits
		+	- having tuition owing after loans and financial aid are calculated above 0.00
		+
		+	=== Former DOS process ===
	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.		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.
Line 89:		Line 112:
	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.		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	+	Then use and edit the merge print template, Installment Initial Email.docx
	Merge this to Email messages.		Merge this to Email messages.
Line 96:		Line 119:
	== Exporting from CrossRoad ==		== Exporting from CrossRoad ==
		+
		+	Note that this documents both current and former process as we are in transition between the two.
		+
		+	=== Current process in CrossRoad ===
		+
		+	In the Current Students Window, use the the menu item Students > Global Processes > Accounts Receivable > Installments > Export Installment Payments to launch this process.
		+
		+	This process will tehn ask for the location of the file to save.
		+
		+	The next steps of the process are to create the export file which will include any installments created above that is future dated and not previously exported.
		+
		+	=== Former DOS process ===
	The utility rotessap.exe is used for this. This will export any future dated payments for clients who have been exported. There are no parameters, but output needs to be redirected.		The utility rotessap.exe is used for this. This will export any future dated payments for clients who have been exported. There are no parameters, but output needs to be redirected.
Line 186:		Line 221:
	=== Approved Payments ===		=== Approved Payments ===
		+
		+	For importing approved payments there are several steps.
		+
		+	==== Export Report ====
		+
		+	On the Rotessa site, in Transaction reports, choose Approved items within the desired date range. Export that to an Excel workbook.
		+
		+	==== Import into Excel ====
		+
		+	Copy the exported workbook to the last page of the Rotessa import workbook. Check that the data all transfers to the Data Entry tab. Check the date on that tab. Check that sufficient rows of the formulas are copied down on the first page of that workbook. Save this and close it.
		+
		+	==== Import into Sage ====
		+
		+	Create a new Receipt batch. Choose File | Import. Make the file type Excel 2000 and then use the Browse button to open the Rotessa Import workbook. Click OK.
		+
		+	Note that at the moment this gives a Bank Code mismatch error that has not been resolved. Depending on the error you may need to use Cancel to exit this window.
		+
		+	The number of records and dollar value should be matched against the Rotessa report. Then each student's payment should be applied.
		+
		+	The batch should be posted according to normal Financial Services practices.
		+
		+	==== Import into CrossRoad ====
		+
		+	The morning of the day on which the Accpac exported balances will include the Rotessa payments, the import should be run.
		+
		+	===== CrossRoad Process =====
		+
		+	In the Current Student window, choose the following menu sequence Students > Global Processes > Accounts Receivable > Installments > Import Installments Received. This will ask the operator to specify the file to import, and the year and term.
		+
		+	The process will then attempt to record each line of the CSV. Records that cannot be applied are recorded in a file in the same location as the original with "-Errors.txt" appended. This file, if created, is automatically opened at the end of the process. The records in this file should be assessed and manually updated.
		+
		+	===== DOS Process =====
		+
		+	Using the saved CSV file exported above, use the rotimp.exe utility.
		+
		+	rotimp 2012/13 1 export.csv
		+
		+	The output will display each record. If a matching CrossRoad record cannot be found, or the payment has already been applied, this will be noted.
		+
		+	Payments that were resubmitted due to incorrect bank information or NSF will not apply through this system and need to be done manually.

---

Latest revision as of 04:27, 19 September 2014

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.

Overview

This process is depicted in this swim lane chart. Note that a number of the processes include substeps detailed below.

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

Note that this documents both current and former process as we are in transition between the two.

CrossRoad Global process

In the Current Students Window, use the the menu item Students > Global Processes > Accounts Receivable > Installments > Export Installment Enrolments to launch this process.

The process will then ask for the relevant Year and term. Dates should be de-selected.

The next step will ask for a location in which to save the file.

The process will then proceed to export each student whose installment data is not marked as "disabled", who is deemed eligible to export, and who has a King's email address. Eligibility to export is determined by

- having a valid bank and branch (account codes cannot be checked) - having a SSF invoice entry (hence has confirmed) - having currently registered credits - having tuition owing after loans and financial aid are calculated above 0.00

Former DOS process

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

In Rotessa, under the Admin section, find the Customer Import tab. Use the browse button to find the export file. Click the Import button. You should be able to review, perhaps on several pages, the entries for validity. If they are all valid, then click the Process button. Otherwise, click Cancel and fix the problems.

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, Installment Initial Email.docx

Merge this to Email messages.

Exporting Payments to Rotessa

Exporting from CrossRoad

Note that this documents both current and former process as we are in transition between the two.

Current process in CrossRoad

In the Current Students Window, use the the menu item Students > Global Processes > Accounts Receivable > Installments > Export Installment Payments to launch this process.

This process will tehn ask for the location of the file to save.

The next steps of the process are to create the export file which will include any installments created above that is future dated and not previously exported.

Former DOS process

The utility rotessap.exe is used for this. This will export any future dated payments for clients who have been exported. There are no parameters, but output needs to be redirected.

 rotessap > rotp20120925.csv

Importing into Rotessa

In Rotessa, in the Admin Section, find the Transacation Import. Use the browse button to locate the file you created. Click the Import button. You can review the entries for validity; there may be several pages. If they are all valid, click Process. If not, click Cancel and fix the errors.

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

Adjusting payments could occur for three reasons:

1. A new charge or a failed loan occurs which needs to be added to the monthly instalments. 2. A new payment is made on behalf of the student. 3. Payment NSF and Plan Status changes.

The first two are the same situation, but the last is different.

New Charges, Failed Loans, Balloon payments

The effect of the change is to adjust all the remaining payments, rather than to change any single payment. So a payment of $600 on behalf of a student prior to the first instalment in the Fall should reduce the three instalments by $200 each. The same payment made in October should reduce the remaining 2 instalments by $300 each.

In CrossRoad

In CrossRoad the instalment amounts should be edited by highlighting the instalment, clicking the Edit button and changing the amount. If the payment is to be deleted, the delete button should be used.

In Rotessa

In Rotessa, under customers, find the student by file #, click the pencil icon to the right of the instalment and edit the amount. If the payment is to be cancelled altogether, then

Payment NSF and Plan Status Changes

If a student's banking information is incorrect -- which is noted by Rotessa as "Edit Reject" -- we can update the banking information through the Customer Edit in Rotessa after the student has submitted new information. We need then to request that the payment be resubmitted by Rotessa. At the moment this is by an email to Greg Lepp, but we may be able to use the on-line system to do this ourselves.

If a student's payment is dishonoured, an NSF charge should be entered through the Accounts Receivable tab. No other change should be required. Rotessa will automatically schedule a second attempt 15 days from the original payment date.

If a student's payment id dishonoured twice, two NSF charges should be entered through the Accounts Receivable tab. In CrossRoad the instalment payments should be deleted, and the disabled box should be checked. In Rotessa, the future instalment payments should be deleted. This student should then be workflowed to Financial Services as interest charges will need to be assessed.

Receiving Payment Data from Rotessa

Rotessa's transaction reports are where we get payment data. These can be exported to Excel for subsequent processing.

Payment Reports

Pending

Payment attempts are pending for up to 4 days from the date of the payment. Rotessa updates the status of the payments daily in that period. After the fourth day, they will change to a final payment status.

Rejected Payments

There are several possibilities for failure. The two most common are:

Edit Declined

Edit declines are students for whom the bank information is incorrect -- whether the mismatch is on bank, branch or account. No NSF fee applies, and we can get updated information and setup a new payment attempt.

Other Declines

These are generally NSF. Rotessa will schedule a second attempt for 15 days from the first payment date. After a second NSF, they are ours to deal with.

Approved Payments

For importing approved payments there are several steps.

Export Report

On the Rotessa site, in Transaction reports, choose Approved items within the desired date range. Export that to an Excel workbook.

Import into Excel

Copy the exported workbook to the last page of the Rotessa import workbook. Check that the data all transfers to the Data Entry tab. Check the date on that tab. Check that sufficient rows of the formulas are copied down on the first page of that workbook. Save this and close it.

Import into Sage

Create a new Receipt batch. Choose File | Import. Make the file type Excel 2000 and then use the Browse button to open the Rotessa Import workbook. Click OK.

  Note that at the moment this gives a Bank Code mismatch error that has not been resolved. Depending on the error you may need to use Cancel to exit this window.

The number of records and dollar value should be matched against the Rotessa report. Then each student's payment should be applied.

The batch should be posted according to normal Financial Services practices.

Import into CrossRoad

The morning of the day on which the Accpac exported balances will include the Rotessa payments, the import should be run.

CrossRoad Process

In the Current Student window, choose the following menu sequence Students > Global Processes > Accounts Receivable > Installments > Import Installments Received. This will ask the operator to specify the file to import, and the year and term.

The process will then attempt to record each line of the CSV. Records that cannot be applied are recorded in a file in the same location as the original with "-Errors.txt" appended. This file, if created, is automatically opened at the end of the process. The records in this file should be assessed and manually updated.

DOS Process

Using the saved CSV file exported above, use the rotimp.exe utility.

   rotimp 2012/13 1 export.csv

The output will display each record. If a matching CrossRoad record cannot be found, or the payment has already been applied, this will be noted.

Payments that were resubmitted due to incorrect bank information or NSF will not apply through this system and need to be done manually.