View source for T2202A Data Generation

There are several stages to the production of T2202 and T4A receipts. Preparation simply ensures that the necessary data is available and set up. Data Extraction creates files suitable for merge printing using Word, and updates the file used by <b>Web Services T2202A delivery</b>. Validation attempts to reconcile the totals generated through this technique with totals from AccPac. Production is actually merge printing. A chart of the first three steps are cyclical; Validation may require returning to preparation and repeating data extraction.

=Preparation=

Preparation simply ensures that the necessary data is available and set up. This means having four specific areas of CrossRoad should be reviewed.

==Genstats historical files==

The term stats from CrossRoad are created in part by the generation of a file named Genstats. Existing records are stored in an historical file that is named by the convention GS[first two year digits from academic year][last two year digits from academic year][term number].dbf. So the file for term 1 of 2004/05 would be GS04051.DBF. These need to exist for every term in the calendar year being considered for this process.

==Clear Accounts Receivable Records==

This is in the Current Students window, under the menu entries Students | Global Processes | Accounts Receivable. The effect is to remove all a/r entries for specified terms from the current file and to place them into a history file. The history files are used for the data extraction. So this process must be complete for each year and term of the calendar year for which receipts are to be produced. These files are named following the convention INVL[first two digits of academic year][term number].dbf. So the file for term 1 of 2004/05 would be INVL041.DBF.

==Financial Aid Receipt Checkbox==

In the financial aid window there is a checkbox called Receipt. Those financial aid items that are to be receipted on a T4A by the institution should have this checked. Those that are not should not be checked. There is a verification process as data extraction occurs, so it is helpful to have a list of item codes handy.

==QIFees Receipt Checkbox==

Edited from Support Tables, the Receipt checkboxes should be checked for those items that are to be included in T2202A receipts.

=Data Extraction=

Data Extraction creates files suitable for merge printing using Word. This uses a command line utility called newt2202.exe. This file requires a INI file called gent2202.ini. The structure of that file is:

[PARAMS]

EXCLUSIONS=[Tilde delimited list of item codes excluded from calculations]

YEARTERMS=[Tilde delimited list of yearterms,startmonth, endmonth and full-time threshold]

STIPENDS=[Tilde delimited list of stipend itemcodes]

The exclusions list is simply the items codes separated by tildes.

The yearterm list is more complex. Each entry in that list is structured with colons separating the elements. The elements are

yearterm, eg 2014/151 for the fall term of 2014/15
startmonth, eg 09 for September. Note that some years classes start in August in the fall.
endmonth, eg 12 for December.
full-time threshold, eg 09.00. For regular terms this is the full time threshold; for special terms it is normally 03.00.

So the one example entry would be:

2014/151:09:12:09.00~

In the NewT2202 program, the operator should choose File and Open to display the progress window, and they "Go" to run the process. Through the process, the operator is asked about including financial aid items, as well as entries from invlog that cannot be identified as fees or courses. Anything that is a tuition charge, a refund of tuition, and mandatory fees (as defined by CRA) should be included.

Financial Aid items identified should only those for which the institution is responsible to issue the receipt. 

This process produces several files:

<table border>
<tr><td>t2202inc.dbf</td><td>a/r entries included for T2202As.</td></tr>
<tr><td>t2202exc.dbf</td><td>a/r entries included for T4As.</td></tr>
<tr><td>months.dbf</td><td>Interim file that counts the number of credits per month</td></tr>
<tr><td>t202inct.dbf</td><td>adds month counts for T2202</td></tr>
<tr><td>t202exct.dbf</td><td>adds month counts for T4A</td></tr>
<tr><td>t202incz.dbf</td><td>Totals entries for T2202</td></tr>
<tr><td>t202excz.dbf</td><td>Totals entries for T4A</td></tr>
<tr><td>T2202a.dat</td><td>Merge Print Data file for T2202As</td></tr>
<tr><td>T4a.dat</td><td>Merge Print Data file for T4As.</td></tr>
<tr><td>archives.lst</td><td>This file lists the students for whom data is generated but who are in archives.</td></tr>
<tr><td>t2202a.dbf</td><td>This file captures all historical t2202a data and is used for <b>Web Services T2202A delivery</b></td></tr>
</table>

=Validation=

Validation attempts to reconcile the totals generated through this technique with totals from Sage as well as to catch situations with unusual counts of months. 

==Sage==

There are two sets of totals: those for T2202As and those for T4As. Subtotals can be created in a number of ways, but Excel is quite a useful tool in this process. The two files that should be used are: t2202inc.dbf, t202exct.dbf.

For the first a simple sum is usually sufficient. With the second, a Pivot Table report can be helpful as it can give an award by award breakdown of totals.

NOTE: It is at this point that remedial action may need to be taken. There may be fees or financial aid items that were missed, or included when they should not have been. This can usually be remedied by changing flags in 1.3 and 1.4 and rerunning 2. In other cases entries may have been missed or students may have been omitted from historical genstats files. These can be corrected by using a utility to directly correct tables to construct the necessary entries in the relevant files.

There may also be errors on the Sage side that are pointed up through this process.

==Month Counts==

For a number of reasons, you may get records that have no part-time or full-time months but which report tuition. This would be the case for audit only registrations or non-credit only registrations. In these cases the tuition is not generally receiptable as it does not count towards a degree, diploma or certificate. So those tuition entries should be removed.

You will also in the spring and summer sessions some issues with the number of months to count as not all courses are readily allocated to the months of the term. In this case each individual student needs to be checked for the right number of months, those adjusted along with the totals. In general, on campus courses have a 1 month full-time allowed; for other courses the ratio of 3 credits per month being full time should be used. So a 6 credit course over two months is two months full-time. A 3 credit course over two months would be 2 part-time months.

Once corrections have been made, validation should be repeated. Only when validation agrees should the next step be completed.



=Production=

Production is actually merge printing. This involves creating a Word template document that conforms to CRA specifications. This can take two forms: a document that prints the entire receipt (which must be approved by CRA) or a form which only prints the data onto preprinted laser forms. The latter prints much more quickly as the former must have the appropriate government logo printed three times for each page. This is a mail merge document to which the following files need to be attached: t2202a.dat and t4a.dat.

In the archive file containing this document, there are some other Microsoft Word files. One demonstrates the layout for T4A receipts in a merge document using the t4a.dat file imported into an Excel spreadsheet. The other is the layout documents for T2202As, but these have not yet been converted to merge print documents. There are two of these, one with the original form as a background image to assist with layout, the other with the background image removed.

Finally, the archives.lst file lists those students for whom records have been produced. Presumably these cannot be distributed by delivery to students on campus and so need to be pulled after printing.

=Web Services T2202A Delivery=

For Web Services delivery three things must happen.

==CRA Form Approval==

In the Fall, Keeler Services will provide a reformatted T2202A form. Each institution needs to receive approval from CRA (Keeler Services will give you the email address and language for this request), and send a copy of that approval to Keeler Services.

==Perl Script File Generation==

Once the approval is received, Keeler Services will provide two Perl scripts that are used in the Web generation process. These, along with the approved PDF form, need to be placed in sub-folders of your current data directory. Assuming your current directory is called dbf.mm, this would be:

    dbf.mm\t2202a\[tax year]

where [tax year] is the 4 digit year.

==Releasing Receipts==

Forms are "held" by default. That is, the student will not be able to retrieve the information. In order to release this, you need to use the application shipped with the windows client for CrossRoad called "EAS Admission." One of the options there allows you to adjust T2202A data, including its release. The typical reason for not releasing a T2202A is that the student has not finished paying for the tuition receipted.

=Fields in QIFEES and Financial Aid Section and Effect on T4A/T2202A Production.=

==Fee Table==

[[image:QIFees.jpg]]

This table is accessed through support tables.

<table border> 
<tr><td>Field</td><td>Field Name</td><td>Type</td><td>Width</td><td>Dec.</td><td>Description</td></tr>
<tr><td>1</td><td>FEENAME</td><td>Character</td><td>14</td><td></td><td>Name == AccPac ItemCode.</td></tr>
<tr><td>2</td><td>FEETYPE</td><td>Character</td><td>1</td><td></td><td>Type of Fee (X = fixed fee, C = per credit fee, R = per course fee, F = by FT/PT status)</td></tr>
<tr><td>3</td><td>FEEAMOUNT</td><td>Numeric</td><td>10</td><td>3</td><td>Amount of fee</td></tr>
<tr><td>4</td><td>FEECOND</td><td>Character</td><td>50</td><td></td><td>Conditions for preselecting fee.</td></tr>
<tr><td>5</td><td>FEEFT</td><td>Numeric</td><td>10</td><td>2</td><td>Fee for Full-Time students (if FeeType is F)</td></tr>
<tr><td>6</td><td>FEEPT</td><td>Numeric</td><td>10</td><td>2</td><td>Fee for Part-Time students (if FeeType is F)</td></tr>
<tr><td>7</td><td>FEEDESC</td><td>Character</td><td>45</td><td></td><td>Fee Description (long name in drop lists)</td></tr>
<tr><td>8</td><td>FEEMAXIMUM</td><td>Numeric</td><td>10</td><td>2</td><td>Maximum fee amount charged (leave 0.00 for no maximum)</td></tr>
<tr><td>9</td><td>FEEMINIMUM</td><td>Numeric</td><td>10</td><td>2</td><td>Minimum fee amount charged (leave 0.00 for no minimum)</td></tr>
<tr><td>10</td><td>RECEIPT</td><td>Logical</td><td>1</td><td></td><td>Is this fee included on a T2202A?v
</table>

The two items, then, that are critical to T2202A production are:
*	field number 1, which matches the itemcode used in the A/R section as well as that used in AccPac.
*	field number 10, which identifies fees which are to be included on the T2202A.


==Financial Aid==
[[image:FinAid.jpg]]

This is accessed through the Financial Aid section.
 
<table border>
<tr><td>Field</td><td>Field Name</td><td>Screen Name</td><td>Type</td><td>Width</td><td>Dec.</td><td>Description</td></tr>
<tr><td>1</td><td>FINAIDID</td><td>Aid Id:</td><td>Numeric</td><td>5</td><td></td><td>Numeric internal id</td></tr>
<tr><td>2</td><td>NAME</td><td>Name:</td><td>Character</td><td>60</td><td></td><td>Name of Aid</td></tr>
<tr><td>3</td><td>ACADEMIC</td><td>Academic</td><td>Logical</td><td>1</td><td></td><td>Is an academic award?</td></tr>
<tr><td>4</td><td>NEED</td><td>Need</td><td>Logical</td><td>1</td><td></td><td>Is a need based award?</td></tr>
<tr><td>5</td><td>OTHERCOND</td><td>Other Conditions:</td><td>Character</td><td>100</td><td></td><td>Other data used in award (fields from other tables)</td></tr>
<tr><td>6</td><td>SEQUENCE</td><td>Reporting Sequence:</td><td>Character</td><td>3</td><td></td><td>Report Order Sequence</td></tr>
<tr><td>7</td><td>HSORPS</td><td>Prefer High School GPA</td><td>Logical</td><td>1</td><td></td><td>Preferred average</td></tr>
<tr><td>8</td><td>AMOUNT</td><td>Amount:</td><td>Numeric</td><td>10</td><td>2</td><td>Amount (or percentage if set below)</td></tr>
<tr><td>9</td><td>PCTPERTERM</td><td>% applied per term</td><td>Numeric</td><td>6</td><td>2</td><td>% of award applied per term</td></tr>
<tr><td>10</td><td>ITEMCODE</td><td>Item Code:</td><td>Character</td><td>14</td><td></td><td>Item code for AccPac</td></tr>
<tr><td>11</td><td>AMTISPCT</td><td>Amount is %</td><td>Logical</td><td>1</td><td></td><td>If true, the amount is a % of tuition</td></tr>
<tr><td>12</td><td>VALIDTERMS</td><td>Valid terms:</td><td>Character</td><td>10</td><td></td><td>Number of terms this can be applied to</td></tr>
<tr><td>13</td><td>SCHBURAWAR</td><td>Unlabeled drop box</td><td>Character</td><td>1</td><td></td><td>Scholarship = S, Bursary = B, Award = A</td></tr>
<tr><td>14</td><td>INTERNAL</td><td>Internal</td><td>Logical</td><td>1</td><td></td><td>True = Internal, False = External</td></tr>
<tr><td>15</td><td>RECEIPT</td><td>Receipt</td><td>Logical</td><td>1</td><td></td><td>Include in T4A Receipts</td></tr>
</table>

T4A critical items are 
Field number 10, itemcode, which matches the itemcode used in the A/R section as well as that used in AccPac.
Field number 15, which determines whether this is included in the T4A data.

Aside from field values for these two tables, the other critical factor in production of the T4A and T2202A data files is that records exist at the time of exporting the data. That is, a fee or financial aid item must be retained in the system even if it has been discontinued. Details except for those identified as "critical" above may be changed, but the record must be present. This usually translates to keeping records for an additional academic year beyond discontinuance.

=T2202a.dat and T4a.dat field descriptions=

==T2202A.Dat fields==

<table border>
 <tr>
  <td>Fieldname</td>
  <td>Description</td>
  <td>Example</td>
 </tr>
 <tr>
  <td>Namecust</td>
  <td>Name</td>
  <td>&quot;CHARLES SCHULTZ&quot;
  </td>
 </tr>
 <tr>
  <td>textstre1  </td>
  <td>Address  </td>
  <td>&quot;99  BLUETREE CLOSE&quot;  </td>
 </tr>
 <tr>
  <td>Namecity</td>
  <td>City</td>
  <td>&quot;ST ALBERT&quot;</td>
 </tr>
 <tr>
  <td>Codestte</td>
  <td>Province</td>
  <td>&quot;AB&quot;</td>
 </tr>
 <tr>
  <td>Codepstl</td>
  <td>Postal Code</td>
  <td>&quot;T8N 2A1&quot;</td>
 </tr>
 <tr>
  <td>Codectry</td>
  <td>Country</td>
  <td>&quot;CANADA&quot;</td>
 </tr>
 <tr>
  <td>Degree</td>
  <td>Degree</td>
  <td>&quot;BA3 &quot;</td>
 </tr>
 <tr>
  <td>textopfl4</td>
  <td>Student ID</td>
  <td>&quot;990137&quot;</td>
 </tr>
 <tr>
  <td>Wintot</td>
  <td>Total Tuition for
  Winter Term</td>
  <td>&quot;337.00&quot;</td>
 </tr>
 <tr>
  <td>Winptmon</td>
  <td>Part time months for
  Winter Term</td>
  <td>&quot;04&quot;</td>
 </tr>
 <tr>
  <td>Winmon</td>
  <td>Full time months for
  Winter Term</td>
  <td>&quot;00&quot;</td>
 </tr>
 <tr>
  <td>Sprtot</td>
  <td>Total Tuition for
  Spring/Summer Terms</td>
  <td>&quot;0.00&quot;</td>
 </tr>
 <tr>
  <td>Sprptmon</td>
  <td>Part time months for
  Spring/Summer Terms</td>
  <td>&quot;00&quot;</td>
 </tr>
 <tr>
  <td>Sprmon</td>
  <td>Full time months for
  Spring/Summer Terms</td>
  <td>&quot;00&quot;</td>
 </tr>
 <tr>
  <td>Faltot</td>
  <td>Total Tuition for Fall
  Term</td>
  <td>&quot;720.00&quot;</td>
 </tr>
 <tr>
  <td>Falptmon</td>
  <td>Part time months for
  Fall Term</td>
  <td>&quot;04&quot;</td>
 </tr>
 <tr>
  <td>Falmon</td>
  <td>Full time months for
  Fall Term</td>
  <td>&quot;00&quot;</td>
 </tr>
 <tr>
  <td>total99</td>
  <td>Total Tuition for Year</td>
  <td>&quot;1057.00&quot;</td>
 </tr>
 <tr>
  <td>Totalptmon</td>
  <td>Total Part Time Months for Year</td>
  <td>&quot;08&quot;</td>
 </tr>
 <tr>
  <td>Totalmon</td>
  <td>Total Full Time Months
  for Year</td>
  <td>&quot;00&quot;</td>
 </tr>
 <tr>
  <td>Sin</td>
  <td>Social Insurance Number</td>
  <td>&quot;777888999&quot;</td>
 </tr>
 <tr>
  <td>Sortname</td>
  <td>Name for alpha sort</td>
  <td>&quot;SCHULTZ              CHARLES        &quot;</td>
 </tr>
 <tr>
  <td>Firstname</td>
  <td>First name</td>
  <td>&quot;CHARLES&quot;</td>
 </tr>
 <tr>
  <td>Lastname</td>
  <td>Surname</td>
  <td>&quot;SCHULTZ&quot;</td>
 </tr>
</table>

<u>A couple of notes:</u>

<u>Winter</u> is the first year term
listed when you extract the data.

<u>Spring/Summer</u> are all the terms
between the first year/term listed and the last.

<u>Fall</u> is the last term listed when
you extract the data.

==T4A fields==

<table border>
 <tr>
  <td><b>Fieldname</b></td>
  <td><b>Description</b></td>
  <td><b>Example</b></td>
 </tr>
 <tr>
  <td>Namecust</td>
  <td>Name</td>
  <td>&quot;ALEXANRA SMYTHE&quot;</td>
 </tr>
 <tr>
  <td>textstre1</td>
  <td>Address</td>
  <td>&quot;270 STORKDALE ROW&quot;</td>
 </tr>
 <tr>
  <td>namecity</td>
  <td>City</td>
  <td>&quot;LEDUC&quot;</td>
 </tr>
 <tr>
  <td>codestte</td>
  <td>Province</td>
  <td>&quot;AB&quot;</td>
 </tr>
 <tr>
  <td>codepstl</td>
  <td>Postal Code</td>
  <td>&quot;T4Q 2T9&quot;</td>
 </tr>
 <tr>
  <td>codectry</td>
  <td>Country</td>
  <td>&quot;CANADA&quot;</td>
 </tr>
 <tr>
  <td>sin1</td>
  <td>First 3 digits of Social
  Insurance Number</td>
  <td>&quot;777&quot;</td>
 </tr>
 <tr>
  <td>sin2</td>
  <td>Second 3 digits of Social
  Insurance Number</td>
  <td>&quot;888&quot;</td>
 </tr>
 <tr>
  <td>sin3</td>
  <td>Final 3 digits of Social
  Insurance Number</td>
  <td>&quot;999&quot;</td>
 </tr>
 <tr>
  <td>schtotal</td>
  <td>Total scholarship amount</td>
  <td>&quot;2696.00&quot;</td>
 </tr>
 <tr>
  <td>sortname</td>
  <td>Name for sorting</td>
  <td>&quot;SMYTHE              ALEXANRA       &quot;</td>
 </tr>
 <tr>
  <td>firstname</td>
  <td>Firstname</td>
  <td>&quot;ALEXANRA       &quot;</td>
 </tr>
 <tr>
  <td>lastname</td>
  <td>Surname</td>
  <td>&quot;SMYTHE              &quot;</td>
 </tr>
</table>

= Reference = 

It might be helpful to read through CRA comments about T2202A and what tuition is countable. See [http://www.cra-arc.gc.ca/tx/tchncl/ncmtx/fls/s1/f2/s1-f2-c2-eng.html#N10250]