December 2012
CASES21 Budget import/export information – v1.1 3
Introduction
This information is for schools currently (or considering) using external budget management products. If your school is only using CASES21 for Budget management you should read and follow the instructions in the CASES21 Finance Business Process Guide – Section 6: Budgets instead of using these guidelines.
This document is to assist schools and external vendors to integrate their budget management products with CASES21.
If your school uses an external budget management product, you should ensure that:
· your version of the product is current and compliant with CASES21. Ask your vendor to read the integration specifications and confirm their product complies.
· all reference data stored in the product (including sub program codes) has been entered into CASES21.
· the budget is regularly imported into CASES21 to make sure it is up to date in CASES21.
Background
School councils are required to account for all monies under their control and to prepare appropriate statements of financial transactions. A vital part of this process is the development and monitoring of the approved annual budget. Schools prepare an annual budget before a school year commences (planning), in order to manage financial performance during the year (control), and evaluate performance at the end of the year (evaluation). This three pronged approach will increase the likelihood of a school attaining its financial goals. Revenue and expenditure budgets are used to plan, control and evaluate the financial position of the school.
An Annual Budget is a financial plan for the school that is calculated from potential revenue and proposed expenditure for the coming year. In the case of revenue, this should include both funds available from the current year to be carried forward and potential revenue for the coming year. In total, these determine the spending potential of the school. Budgets help ensure that school expenditure is controlled and that it does not exceed both:
• the amount the school plans to spend; and
• the amount of money that is available for the school to spend.
All schools must enter revenue and expenditure budgets onto CASES21 Finance. Expenditure budgets are to include budgets for recurrent (operating) expenditure as well as capital expenditure (assets with a value greater than $5,000).
For specific information regarding CASES21 data entry instructions see: Section 6: Budgets of the CASES21 Finance Business Process Guide.
Technical information
Both the Export and Import tasks are located under the following menu in CASES21 Financial \ General Leger \ View and Maintain Budgets
GLBUDG51001 Export Budget Data
Budget Export Task
This task will export a comma delimited CSV file consisting of the fields identified under the ‘File Structure’ of this document. The export file name format is a default only and can be changed.
Example of Export File name: Budgets00012011011.CSV
All current year and next year budget records will be exported irrespective of the status of the sub program, GL code or initiative.
Sample Data:
7051,Photocopying,86102,Photocopying,000,Not Applicable,416.63,416.67,416.67,416.67,416.67,416.67,416.67,416.67,416.67,416.67,416.67,416.67,5000,0,0,0,0,0,0,0,0,0,0,0,0,0
Import Budget Data
Budget Import Task
This task will import a comma delimited CSV file. The file must contain the field lengths, field headers, field types and structure as outlined under the ‘File Structure’ section of this document. Row uniqueness is determined by Gl code, sub program and initiative. Rules for importable codes are based on the same filter that is applied in GL Budgets by Subprogram (GLBUDG11006), subprogram, initiatives must be active and general ledger codes must be active and not in the 1xxxxx and 3xxxxx ranges.
Note that the import will perform a full overwrite of any existing data.
Import file naming requirements
There is a specific file naming requirement for the import file.
Import file name: shall start with the word "Budgets" followed by the 4 digit school number after which you can add your own identifiable details:
e.g. Budgets{4 digit School No}*****.CSV
e.g. Budgets0001_MAR.CSV
What the Import Does
The import process will create a valid CASES21 budget for the current and next year. If the data is valid, the import will delete all GLBUDG records and then insert data from the CSV file. The sign for expense and revenue is handled by the import so there is no need for negative signs to be included in the data.
An import file can consist of current year data only by leaving next year's data blank. If the user has zero budgets for next year in the import file then this is still valid data (blanks are converted to zero).
An import file can consist of current year data only, next year data only or both current and next year data.
If next year data is imported and then the End of Year Finance process is completed, the data will be rolled over as the current year
Warning: If current year data is blank (information deleted) and next year data is to be imported, the file will delete any budgets entered against current year. When this file is imported it will overwrite all information in the appropriate budget fields. If amendments have only been done in C21 and this file is imported, the amendments will be overwritten.
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
· Invalid importable codes. Valid codes (GL, SUB PROG, PROG, INIT) are identical to drop downs entries in (GLBUDG11006), all others fall into an error trap and import fails. E.g. (1XXXX and 3XXXX GL codes are not valid)
· Data is restricted by field type and by content. A column that is used for numbers only, will only allow numbers.
· Field type and length validation (see table under File Structure for field types and lengths)
· If there is not enough data to create the primary key (eg inactive codes, 1XXXX and 3XXXX GL accounts)
· The four digit school number not validated at import
· If Monthly and Annual Budget columns contain values, each row is compared, any variances will be flagged.
· If duplicate rows are found in the import file, the row (Gl code, sub program and initiative).
File structure
CASES21 Field name / column header / Field Type / Max LengthSUBPROGRAM / sub program / VARCHAR / 4
SUBPROGRAM_TITLE / sub program title / VARCHAR / 30
CODE / GL code / VARCHAR / 10
GL_TITLE / GL title / VARCHAR / 30
INITIATIVE / initiative / VARCHAR / 3
INITIATIVE_TITLE / initiative title / VARCHAR / 50
CURRENT JAN / current January budget / Money, null
CURRENT FEB / current February budget / Money, null
CURRENT MAR / current March budget / Money, null
CURRENT APR / current April budget / Money, null
CURRENT MAY / current May budget / Money, null
CURRENT JUN / current June budget / Money, null
CURRENT JUL / current July budget / Money, null
CURRENT AUG / current August budget / Money, null
CURRENT SEP / current September budget / Money, null
CURRENT OCT / current October budget / Money, null
CURRENT NOV / current November budget / Money, null
CURRENT DEC / current December budget / Money, null
CURRENT TOTAL / current total budget / Money, null
NEXT JAN / next January budget / Money, null
NEXT FEB / next February budget / Money, null
NEXT MAR / next March budget / Money, null
NEXT APR / next April budget / Money, null
NEXT MAY / next May budget / Money, null
NEXT JUN / next June budget / Money, null
NEXT JUL / next July budget / Money, null
NEXT AUG / next August budget / Money, null
NEXT SEP / next September budget / Money, null
NEXT OCT / next October budget / Money, null
NEXT NOV / next November budget / Money, null
NEXT DEC / next December budget / Money, null
NEXT TOTAL / next total budget / Money, null
CASES21 Budget import/export information – v1.1 3
More information
For more information about:
· Budget Management see: A Guide to Budget Management.
· CASES21 data entry instructions see: Section 6: Budgets of the CASES21 Finance Business Process Guide.
· CASES21 integration specifications see: Developer’s Resource Kit: CASES21 standards (for vendors).
CASES21 Budget import/export information – v1.1 3