CASES21 Family Invoice Import/Export Technical Guidelines V1.1 1

/ Family Invoice Import/Export
technical guidelines
February 2015

CASES21 Family Invoice import/export – technical guidelines – v1.1 1

Introduction

This information is for schools currently (or considering) export of unpaid invoices and the import of fully paid invoices into CASES21 using a third party external product.

This document is to assist schools and external vendors to integrate their products with CASES21.

The process involves three main steps: •

• the export of unpaid invoices from CASES21

• the payment of invoices by parents in a third party system

• the import of paid invoices into CASES21

Background

CASES21 contains functionality that enables schools to provide the BPAY payment option to parents. A limitation to BPAY payments is that they do not provide the school with details regarding which individual invoice (charge item) a receipt should be generated for. The only options available to schools to allocate receipts are:

· Manual allocation, where by parents return the remittance section on the family statements to allows schools to allocate receipts to outstanding family invoices

· To auto-allocate receipted money, which will allocate receipted money against outstanding items in chronological order

This generic CASES21 import/export process provides a way for a third party product to provide flexible payment options for unpaid invoices, as well as the ability for parents to specify how their payment should be allocated across their invoices. This is achieved by CASES21 providing a facility to export unpaid invoices via a text file and import paid invoices from the third party product.

A CASES21 receipt shall be generated for each imported transaction to ensure parent allocations are tracked accurately.

For specific information regarding this CASES21 import/export tool see: Section 1 Families of the Finance of the CASES21 finance business process guide

Technical information

Both the Export and Import tasks are located under the following menu in CASES21 Financial \ Families \ Family Invoice import & Export

The following items make up the functionality of the generic import/export tool

Step 1: Export Unpaid Family Invoices (DFF51001)

This task will export a tab delimited CSV file consisting of the fields identified under the ‘Export File Structure’ of this document. The export file name format is a default only and can be changed.

e.g. InvoiceExport{4 digit school number}{YYYYMMDD}.CSV

All unpaid invoices where the student has a status of active, inactive, leaving or future are exported. Partially or fully paid invoices are not exported.

Sample Data:

3478 ABC0051 24/01/2012 DFI0000857 Y 200 0 Subject Contributions Yr 9 &10 SCULLY Edward 1999105222 Mr N & Mrs Y SCULLY Edward SCULLY 10A

Step 2: Payment of invoices by parents (Third Party)

This process occurs outside of the CASES21 system. Please contact your vendor for any support issues relating to this step of the process.

Step 3: Part 1 - Import paid family invoices

This task will import a tab delimited CSV file consisting of the fields identified under the ‘Import File Structure’ of this document. The import file shall be rejected with explanatory errors if it does not pass validation. The user shall be provided with an option to continue with the import if the validation presents warning messages. This is the first part of importing the paid invoices into CASES21. If there are no errors proceed to Step 3: Part 2Process Imported Receipts and Allocations.

Sample Data:

OT00000012 10037 1999100116 27/05/2012 150.00

OT00000013 4149 1999105909 27/05/2012 140.00

OT00000013 4150 1999105909 27/05/2012 140.00

Import Errors

The import task window will display any errors that occur during import. Errors can be copied or printed from this window to allow for examination and to facilitate correction prior to reimporting.

Possible Errors and Warnings

· An invalid number of columns, invalid field type or size and null values will generate an error

· A duplicate transaction ID is rejected with an error message. This will occur for the current and previous year only

· An transaction ID that does not match a CASES21 transaction id for the invoice, will generate an error

· If the transaction ID does not match the same CASES21 family as the BPAY customer number an error is generated

· A mismatch between BPAY number and the CASES21 family BPAY number shall generate a warning

· A fully or partially paid invoice cannot be allocated, therefore a warning is generated

· A payment that does not match the CASES21 invoice amount shall generate a warning

Sample Data:

OT00000012 10037 1999100116 27/05/2012 150.00

OT00000013 4149 1999105909 27/05/2012 140.00

OT00000013 4150 1999105909 27/05/2012 140.00

Step 3: Part 2 - Process Imported Receipts and Allocations

This is the final step of the import process and generates the receipt allocations from the imported paid invoices. Receipts shall be generated for completely unpaid imported invoices. If this batch detects partially or completely paid invoices it shall exclude these from the batch as these need to be manually dealt with.

Generally issues will arise as a result of multiple allocations of receipts or credit notes to the same invoice. For example, if a parent has paid for an item in an external system and at the same time the school has marked the same invoice as fully or partially paid (using a receipt or credit note) the import process will give warnings.

Process Import Receipts Unallocated (DF31069)

Where an invoice included in the import file has already been marked as paid partially or completely in CASES21, this batch will create an unallocated receipt for that family.

Unprocessed Imported Receipts (DF21904)

This report displays the contents of the uploaded file prior to processing the batch. It should be used to view transactions and check for error messages

Unallocated Imported Receipts (DF21905)

This report displays all unallocated receipts, for the selected receipt date range and the selected families.

Allocated Imported Receipts (DF21906)

This report displays the allocated receipts for the selected receipt date range and the selected families.

Manually allocate Receipt (task)

This is an allocations screen to allow school users to manually allocate receipts that are unallocated.

Export File Structure

CASES21 Table.Field name

Column data

Field type

Max length

DFF.TID

Transaction ID

int, not null

DFF.CODE

Family key

varchar, not null

DFF.TRDATE

Transaction date

datetime, null

DFF.TRREF

Transaction reference

varchar, null

DFF.VOLUNTARY

Is this a voluntary of compulsory charge

varchar, null

DFF.TRAMT

Transaction amount

money, null

DFF.GST_AMOUNT

Amount of GST

money, null

DFF.TRDET

Transaction detail

varchar, null

DFF.ST_SURNAME

Student surname

varchar, null

DFF.ST_FIRSTNAME

Student firstname

varchar, null

DF.BPAY_REFERENCE

BPAY reference number

varchar, null

DF.BILLINGTITLE

Billing name

varchar, null

ST.PREF_NAME

Students preferred name

varchar, null

ST.SURNAME

Student surname

varchar, null

ST.HOME_GROUP

Student home group

varchar, null

DF.E_MAIL_A

Student email address

varchar, null

Import File Structure

CASES21 Field name

Column data

Field type

Max length

REFERENCE_NO

Unique ID provided by 3rd party product

varchar

50, only 21 is used

INVOICE_TID

Transaction ID for the invoice

int

CUST_REFERENCE

Family BPAY reference number

varchar

PAYMENT_DATE

date paid

datetime

AMOUNT

receipt amount

money

· Data is imported into DFB if validation passes

· Data is imported into KBP if validation fails

More information

For more information about:

• CASES21 data entry instructions see: Section 1: Families of the CASES21 Finance Business Process Guide.

· CASES21 integration specifications see: Developer’s Resource Kit: CASES21 standards (for vendors).

CASES21 Family Invoice import/export – technical guidelines – v1.1 1