FMS6 - Budget Planning Interfaces

Overview

The Budget Planning interface allows the import of a budget prepared in a third party application into FMS. To support this it is possible to acquire the General Ledger structure from FMS, and optionally the Staff and pay data from Personnel 7. Data is transferred as XML streams and data is not written to the hard disc. The user of this document should review the schema files that accompany this document.

This document, and any associated documents and files are not manuals, or training guides, and assume that the reader has the required working knowledge of:

1.        FMS, it’s processes, and the reasons for those processes; in particular how FMS holds the budget as either or both Fund Allocations and Cost Centre Allocations. That formal training will be acquired as needed through the relevant training courses
2.        Personnel 7, it’s processes, and the reasons for those processes. That formal training will be acquired as needed through the relevant training courses
3.         XML, it’s use, implementation, or integration within systems. ESS will not provide training or expertise in this area.

A knowledge of both the CFR (Consistent Financial Reporting) and SWC (School Workforce Census) specifications will prove useful as some of this data is passed by the various messages which may assist in allocating General Ledger Codes and staff  salaries within the third party application.

The FMS budget at Cost Centre level takes no account of CFR headings and any CFR data is ignored at the point of import. Fund Allocation within FMS does require that CFR Heading detail is passed and this is used as part of the import process. FMS will accept budget figures for both Cost Centre Allocation and Fund Allocation in the same import.

The anticipated workflow is to

1. Acquire the Chart of Accounts from FMS
2. Acquire, if required, the staffing details from Personnel 7
3. Prepare the budget.
4. Export the budget to FMS.

The following constraints are currently implemented by the FMS import:

1. The budget for either 1 or 2 financial years only may be imported.
2. If there is a budget for 2 financial years in the import then these must be consecutive.
3. A budget is imported to any financial year that does not have an FMS status of “Finalised”.

XML Messages Spec

There are 3 XML messages that support the interface. These are:

1. The Budget Import message (supported by the “Budget Import Interface.XSD”).
2. The General Ledger export message (supported by the “Chart of Accounts.XSD”).
3. The Staffing export message (supported by the “P7 Interface Schema.XSD”).

Method

Acquire the Chart of Accounts from FMS

This is achieved with calls to the following FMS .net assembly:

CSFMSBudgetInterface.dll

int FMSBudInt.GetVersion()	Retrieves the Version of FMS that is being used. On Error returns 999
int FMSBudInt.DBConnect(string User, string Password)	Initiates a database connection. Possible return codes: 0            Log On Successful 1            Invalid User or Password 2            Not used. 3            FMS Database Version Mismatch 999        Unknown error.(FMS not installed correctly is likely problem)
int FMSBudInt.GetYearDefinitions(out string YDXML)	Retrieves Year Definitions as xml. Possible return codes: 0            Result returned successfully. 1            Database not connected. 2            Not Used. 3            Insufficient Access Rights 999        Unknown error.(FMS not installed correctly is likely problem)
int FMSBudInt.GetChartOfAccounts(double date, out string COAXML)	Retrieves Chart of Accounts as xml. Possible return codes: 0            Result returned successfully. 1            Database not connected. 2            Invalid date parameter. 3            Insufficient Access Rights 999        Unknown error.(FMS not installed correctly is likely problem)
int FMSBudInt.DBDisConnect()	Disconnects from database. Possible return codes: 0            Log Off Successful 1            Database not Connected. 999        Unknown error.

 

This will provide the Chart of Accounts. Note that currently (February 2010) this will consist of Cost Centres and the associated Ledger codes of type (FMS type) “EX”, “ES”, “IN”, “VI”, and “VO”. Note that the CFR Code for a Cost Centre, Ledger Code, Fund combination is passed if this been mapped within FMS to a CFR code.

It is likely that future development within FMS will extend the range of Ledger Code types included in this file and the budgeting software should build in future proofing to account for this. As the schema already permits the inclusion of these there may be little or no warning given if, or when, this is implemented.

Acquire, if required, the staffing details from Personnel 7

This achieved with calls to the following Sims .net assembly:

Personnel7ExportInterfaceProcess.dll

XmlDocument BudgetingDataExchange.GetBudgetingData (string userName, string password);	Get Personnel Budgeting data using specified Sims username\password, database will be determined from connect.ini
XmlDocument  BudgetingDataExchange.GetBudgetingData (string userName, string password, string serverName, string databaseName);	As above but from the specified database
[property] string BudgetingDataExchange.ErrorMessage	Error encountered during GetBudgetingData(…) call

 

This will provide the staffing detail as held in Personnel 7 including the contractual information.

Personnel 7 determines a reference date as the First of April immediately preceding the system date. E.g. if the system date is 02/02/09 the Reference Date will be 01/04/08, if the system date is 06/06/09 the Reference Date will be 01/04/09.

The data items provided are Staff Details, National Insurance tables, Superannuation Details, and Service Term Details on the following basis:

1.            Staff Details. Staff will be included only where they have one or more contracts that are current on or after the Reference Date. When a member of staff is included then only those contracts will be included that are current on, or become current after, the Reference Date. All contracts that terminate before the Reference Date will be excluded.
2.            National Insurance Tables. National Insurance data definitions are included except for those where the “End Date” is on or before the Reference Date.
3.            Superannuation Details. All defined Superannuation schemes are included where:

1.             The Application Date is on or after the Reference Date.
2.           The Application Date is before the Reference Date and there is no value set with a later Application Date.
3.          The single instance value set where the Application Date is before the Reference Date and the next value set in chronological order has an Application Date greater than the Reference Date.

4.           Service Term Details. All defined Service Terms are included except for those where the “Hidden” attribute on the panel titled “Service Term” is ticked “On”. Each Service Term will include all the Service Term Details, all Posts, Pay Scales and all Allowances where:

1.             The Award Date is on or after the Reference Date.
2.           The Award Date is before the Reference Date and there is no value set with a later Award Date.
3.          The single instance value set where the Award Date is before the Reference Date and the next value set in chronological order has an Award Date greater than the Reference Date.

Export the budget to FMS.

This is a user activated function from within FMS by the user selecting to import the budget.  Note that depending on the licence applied to FMS the user of FMS may be able to edit the budget imported to FMS causing the budget in FMS to be different to that held in the third party software. The following DLL call is used:

Third Party to provide a COM registered assembly named FMSLink.dll, that will be called by FMS,  onforming to the following interface:

bool CheckImportAvailable()	Returns true if there is a scenario available for import, false if not
string GetImportScenarioName()	Returns the Name of the importable scenario
bool CheckCredentials(string username, string password)	Returns true if the specified user has sufficient rights to import the scenario, false if not
string GetXMLCodings (string strYear1Narrative, string strYear1FundingNarrative, string strYear2Narrative, string strYear2FundingNarrative, bool year1, bool year2);	Returns scenario data as xml conforming to the supplied schema

 

This will provide the budget to FMS as prepared externally. Note that the following validations are performed:

1. The incoming XML stream must be schema valid.
2. The value of the nodes <Message / Header / LANo> and <Message / Header / SchoolNo> must exactly match the FMS values held against the attribute “LEA Code” and “Code” respectively found on the “Basic Details” tab of “Establishment Details” (route TE Tools | Establishment Details).
3. The incoming XML stream may carry budget data for 1 or 2 Financial Years. If 2 Financial years are included these must be consecutive.

Failure to meet any element of the above will result in complete rejection of the import.

The Financial Year to be updated by  a budget import is determined as the one which starts in April of the year from the tag <Message / FinancialYears / FinancialYear / FYID>

On import:

1.            The budget for all Cost Centre, Ledger Code, Fund combinations for all periods and the budget limits, “Expense Limit” and “Income Limit” for the current (not the fixed) budget are set to zero.
2.            A Fund Allocation, either increase or decrease,  is created for each fund with an “in year balance” to set all fund balances to zero, and apportioned to CFR Headings so that these are also set to zero. Note that fund Opening Balances are excluded when determining if there is an “in year balance” and from the contra allocation to reset the “in year balance” to zero.
3.            Each Cost Centre, Ledger Code, Fund combination is validated to ensure that it exists in FMS. Validation failure results in failure of the import. If the child node is “BudgetValue” then:

i).          Either the  <Expense Limit> or the “Income Limit” will incremented by the value of the node <BudgetValue>.

ii).         The “Budget to Allocate” will be set to the value of the node <BudgetValue>.

iii).        The value will be distributed across the Financial Periods using the currently assigned profile unless this is “Manual Entry” in which case it will be spread in equal amounts across all Financial Periods with any surplus being applied to the last period.

iv).        The “Narrative” will be assigned the value “Budget Interface Import”.

If the child node is <PeriodValues> then:

a).         The either the “Expense Limit” or the “Income Limit” will be incremented by the sum of the values of the child nodes <FinancialPeriodValue.

c).          The “Budget to Allocate” will be set to the sum of the values of the child nodes <FinancialPeriodValue>.

d).         The value of the child node <FinancialPeriod> will determine the FMS financial period to be updated by the value of the child node <FiancialPeriodValue>. The “Profile Model” will be set to “Manual Entry”.

e).          The “Narrative” will be assigned the value “Budget Interface Import”.

4.           A fund allocation to be passed through the interface must have been fully apportioned in the budgeting software to the relevant CFR headings. The value of a fund allocation in FMS is the sum of the apportionments values.

Note: It is possible, using the “Budget Import Interface.XSD” schema to write an XML file to disc for manual import into FMS.