© 2018 Capita Business Services Ltd. All rights reserved.

Capita Education Software Solutions is a trading name of Capita Business Services Ltd. Our Registered office is 30 Berners Street, London, W1T 3LR and our registered number is 02299747. Further information about Capita plc can be found in our legal statement.

FMS 6 - Local - Journal Import

Bank journal interfaces for ePayment Systems

Who should read this document?

This document is for any partner interfacing with or considering interfacing with FMS (Our finance product) from an ePayment or similar system. Partners using this functionality would need to have access to a copy of FMS via our FMS Partnership programme.

This functionality is available to partners from the summer 2015 release of FMS.

Introduction

This document describes the FMS interface that enables payment systems to post bank journals to FMS. The interface allows payment systems to retrieve banks set up on FMS, post journals and audit notes to FMS.

Sample calls, explanations and outputs are shown below.

Licensing

‘Payment System’ licence is required to access Payments System Management on FMS and to use the E Journal Interface API successfully.

Partners wishing to purchase licences on behalf of schools should contact partner.management@educationsoftwaresolutions.co.uk 

Setting up Payment System within FMS

Payment systems may categorise items for sale in a different way to FMS and it is essential that mappings are created from the code used within the online payments system and the codes used in FMS. This saves the need to export the chart of accounts from FMS and link sales formally to the chart of account codes.

Setting up Payment System within FMS

Payment system should be set up within FMS and products codes, bank accounts, charges and other codes should be mapped with FMS ledger codes to call ‘CSFMSEJournalInterface’ successfully.

Set up Payment system on FMS: go to ‘Tools’ | ‘Payment System Management’ | ‘Payment System Parameters’

Payment System Setup

Enter the name of the payment interface, unique (payment system) identifier, select the associated supplier and save.

Payment Parameters

Select ‘Maintain code mapping’

Payment Mappings

Map product codes (income), bank codes, VAT Codes, charge codes, VAT on charge codes from the payment system with ledger codes in FMS.

Interface: CSFMSEJournalInterface

Constructor: FmsGeneralLedgerProcessor

public FmsGeneralLedgerProcessor(string server, string database, string username, string password)

string _server = <Database Server>;
string _database = <FMS Database>;
string _username = <FMS Username>;
string _password = <FMS Password>;
FmsGeneralLedgerProcessor _FMSGLProcessor =
    new FmsGeneralLedgerProcessor(_server, _database, _username, _password);

Get Banks method – retrieves details of banks set up on FMS

Bank Accounts

public System.Xml.XPath.IXPathNavigable GetBanks(string systemIdentifier, out bool isSuccess)

//‘System identifier’ code is the unique ID that was given when setting up the payment interface:

Payment Identifier

string _systemIdentifier = <(Payment System) Unique Identifier>;

bool _isSuccess = false;
IXPathNavigable _getBanksResult = 
_FMSGLProcessor.GetBanks(_systemIdentifier, out _isSuccess) as XmlDocument;
XmlDocument _getBanksResultXml = (_getBanksResult) as XmlDocument;

Sample Get Bank result (xml):

<?xml version="1.0" encoding="iso-8859-1"?>
<Request>
  <Header requestDateTime="2015-04-30T13:57:57.68" systemIdentifier="TPPCS" fmsVersion="6.93.328" />
  <Banks>
    <Bank externalReference="BK1" lastOrderRowVersion="" lastSettlementRowVersion="" />
    <Bank externalReference="BK2" lastOrderRowVersion="" lastSettlementRowVersion="" />
    <Bank externalReference="BK3" lastOrderRowVersion="" lastSettlementRowVersion="" />
  </Banks>
</Request>

(In this case last row versions = "" as there are no previously posted journals. LastOrderRowVersion will show the number of orders that has been already posted and lastSettlementRowVersion will show the number of settlements that has already been posted. This information is required for calculating and setting last row versions when generating journal post file.)

Post Journal method – posts manual journals to FMS. 
public System.Xml.XPath.IXPathNavigable PostJournals(System.Xml.XPath.IXPathNavigable xmlData)

XmlDocument _xmlJournal = new XmlDocument();
_xmlJournal.Load("PostJournal.xml");
IXPathNavigable _journalResult = _FMSGLProcessor.PostJournals(_xmlJournal);
XmlDocument _journalResultXml = (_auditResult) as XmlDocument;

Returns whether the post was successful or whether there were errors.

[To view posted journals on FMS go to: ‘Focus’ | ‘General Ledger’ | ‘Journal Review’]

To generate journal post file, details retrieved from GetBanks method is used. Payment system unique identifier (code) set up on FMS is required to be passed with journal posts.

Key requirement: Control total must be valid for journal post to be allowed. The control total is the net sum of all the journals that the system has posted against the bank reference. Control total must be maintained correctly within the payments system and FMS in order for the payments system to post journals to FMS.

Bank codes, product codes, charge codes, VAT on charge codes, etc. should be in step with FMS.

Sample journal post file:

<?xml version="1.0" encoding="utf-8"?>
<ControlAccounts>
  <Header requestedBy="admin@capita.co.uk" responseDateTime="2015-04-30T13:59:49"    systemIdentifier="TPPCS" description="" submissionID="0071" source="Test Harness"  sourceVersion="1.0.0.1" transactionLineCount="2" />
  <Banks>
    <Bank reference="BK1" controlTotal="0">
      <Orders lastRowVersion="1">
        <Txn id="1" productReference="WATBTL1" description="Description" date="2015-04-30T13:59:32.8791584+01:00" grossAmount="9.000000" vat="1.500000" vatReference="VAT20" />
      </Orders>
      <Settlements lastRowVersion="1">
        <Txn id="1" description="Description" date="2015-04-30T13:59:32.8791584+01:00" grossAmount="9.000000" grossCharges="0.30" chargesReference="CHARGE30" chargesVat="0.00" chargesVatReference="CHARGEVAT0" />
      </Settlements>
    </Bank>
    <Bank reference="BK2" controlTotal="0"></Bank>
    <Bank reference="BK3" controlTotal="0"></Bank>
  </Banks>
</ControlAccounts>

XML Schema:

<?xml version="1.0" encoding="iso-8859-1"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="ControlAccounts">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Header">
          <xs:complexType>

            <xs:simpleContent>
              <xs:extension base="xs:string">
                <xs:attribute name="requestedBy" type="xs:string" use="required" />
                <xs:attribute name="responseDateTime" type="dateTimeSmall" use="required" />
                <xs:attribute name="systemIdentifier" type="xs:string" use="required" />
                <xs:attribute name="description" type="xs:string" use="required" />
                <xs:attribute name="submissionID" type="xs:string" use="required" />
                <xs:attribute name="source" type="xs:string" use="required" />
                <xs:attribute name="sourceVersion" type="xs:string" use="required" />
                <xs:attribute name="transactionLineCount" type="xs:int" use="required" />
              </xs:extension>
            </xs:simpleContent>
          </xs:complexType>
        </xs:element>
        <xs:element name="Banks">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="Bank" minOccurs="0">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="Orders" minOccurs="0">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element maxOccurs="unbounded" name="Txn" minOccurs="0">
                            <xs:complexType>
                              <xs:simpleContent>
                                <xs:extension base="xs:string">
                                  <xs:attribute name="id" type="xs:string" use="required" />
                                  <xs:attribute name="productReference" type="xs:string" use="required" />
                                  <xs:attribute name="description" type="xs:string" use="required" />
                                  <xs:attribute name="date" type="dateTimeSmall" use="required" />
                                  <xs:attribute name="grossAmount" type="decimal20_6_non_zero" use="required" />
                                  <xs:attribute name="vat" type="decimal20_6" use="required" />
                                  <xs:attribute name="vatReference" type="xs:string" use="required" />
                                </xs:extension>
                              </xs:simpleContent>
                            </xs:complexType>
                          </xs:element>
                        </xs:sequence>
                        <xs:attribute name="lastRowVersion" type="xs:string" use="required" />
                      </xs:complexType>
                    </xs:element>
                    <xs:element name="Settlements" minOccurs="0">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element maxOccurs="unbounded" name="Txn" minOccurs="0">
                            <xs:complexType>
                              <xs:simpleContent>
                                <xs:extension base="xs:string">
                                  <xs:attribute name="id" type="xs:string" use="required" />
                                  <xs:attribute name="description" type="xs:string" use="required" />
                                  <xs:attribute name="date" type="dateTimeSmall" use="required" />
                                  <xs:attribute name="grossAmount" type="decimal20_6_non_zero" use="required" />
                                  <xs:attribute name="grossCharges" type="decimal20_6" use="required" />
                                  <xs:attribute name="chargesReference" type="xs:string" use="required" />
                                  <xs:attribute name="chargesVat" type="decimal20_6" use="required" />
                                  <xs:attribute name="chargesVatReference" type="xs:string" use="required" />
                                </xs:extension>
                              </xs:simpleContent>
                            </xs:complexType>
                          </xs:element>
                        </xs:sequence>
                        <xs:attribute name="lastRowVersion" type="xs:string" use="required" />
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute name="reference" type="xs:string" use="required" />
                  <xs:attribute name="controlTotal" type="xs:decimal" use="required" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:simpleType name="decimal20_6_non_zero">
    <xs:restriction base="xs:decimal">
      <xs:totalDigits value="20" />
      <xs:fractionDigits value="6" />
      <xs:minInclusive value="0.010000" />
      <xs:maxInclusive value="99999999999999.999999" />
    </xs:restriction>
  </xs:simpleType>

  <xs:simpleType name="decimal20_6">
    <xs:restriction base="xs:decimal">
      <xs:totalDigits value="20" />
      <xs:fractionDigits value="6" />
      <xs:minInclusive value="-99999999999999.999999" />
      <xs:maxInclusive value="99999999999999.999999" />
    </xs:restriction>
  </xs:simpleType>

  <xs:simpleType name="dateTimeSmall">
    <xs:restriction base="xs:dateTime">
      <xs:minInclusive value="1900-01-01T00:00:00" />
      <xs:maxInclusive value="2079-06-06T23:59:00" />
    </xs:restriction>
  </xs:simpleType>

</xs:schema>

Descriptions of Element Attributes

Element

/ControlAccounts/Header

Attributes

Format

Required

Description/Comments

requestedBy

String

TRUE

User

responseDateTime

Date Time

TRUE

Date and time of the current response [Between 1900-01-01T00:00:00 and 2079-06-06T23:59:00]

systemIdentifier

String

TRUE

Identifier of the payments interface [code as set up on FMS]

Description

String

TRUE

Transaction description

submissionID

String

TRUE

ID for the current response [unique; sequential]

Source

String

TRUE

Submission origin/payment system

sourceVersion

String

TRUE

Payment system version

transactionLineCount

String

TRUE

Total number of orders and settlements in the current response

 

Element

/ControlAccounts/Banks/Bank

Attributes

Format

Required

Description/Comments

Reference

String

TRUE

Bank Account code  [set up on FMS]

controlTotal

Decimal

TRUE

Net sum of all the journals that the system has posted against the bank reference.

Difference of [total of all authorized order (excluding transactions in the current response) for a given bank sent to FMS] – [Total of all settlement amount (excluding transactions in the current response) sent to FMS for a given bank]

 

Element

/ControlAccounts/Banks/Bank/Orders

Attributes

Format

Required

Description/Comments

lastRowVersion

String

TRUE

Setting last row version for orders (to be retrieved through get banks method for the next response).

lastRowVersion = last row version retrieved from get banks method + number of orders in the current response

 

 

Element

/ControlAccounts/Banks/Bank/Orders/Txn

Attributes

Format

Required

Description/Comments

Id

String

TRUE

Order ID (sequential)

productReference

String

TRUE

Unique product code  [Income code - set up on FMS]

Description

String

TRUE

Product description

Date

Date Time

TRUE

[Between 1900-01-01T00:00:00 and 2079-06-06T23:59:00]

grossAmount

Decimal

TRUE

[Non zero - between 0.010000 and 99999999999999.999999 (max digits: 20; max decimal points: 6)]

Vat

Decimal

TRUE

[Between -99999999999999.999999 and 99999999999999.999999 (max digits: 20; max decimal points: 6)]

vatReference

String

TRUE

VAT code applied on product [VAT code on Income - set up on FMS]

 

Element

/ControlAccounts/Banks/Bank/Settlements

Attributes

Format

Required

Description/Comments

lastRowVersion

String

TRUE

Setting last row version for orders (to be retrieved through get banks method for the next response).

lastRowVersion = last row version retrieved from get banks method + number of settlements in the current response

 

Element

/ControlAccounts/Banks/Bank/Settlements/Txn

Attributes

Format

Required

Description/Comments

Id

String

TRUE

Settlement ID (sequential)

Description

String

TRUE

Product description

Date

Date Time

TRUE

[Between 1900-01-01T00:00:00 and 2079-06-06T23:59:00]

grossAmount

Decimal

TRUE

[Non zero - between 0.010000 and 99999999999999.999999 (max digits: 20; max decimal points: 6)]

grossCharges

Decimal

TRUE

[Between -99999999999999.999999 and 99999999999999.999999 (max digits: 20; max decimal points: 6)]

chargesReference

String

TRUE

Charges code [set up on FMS]

chargesVat

Decimal

TRUE

[Between -99999999999999.999999 and 99999999999999.999999 (max digits: 20; max decimal points: 6)]

chargesVatReference

String

TRUE

VAT code on Charges [set up on FMS]

Post Audit method – posts audit messages to FMS

public System.Xml.XPath.IXPathNavigable PostAuditMessage(string message)
string _auditMessage = <Audit Message>;
IXPathNavigable _auditResult = _FMSGLProcessor.PostAuditMessage(_auditMessage);
XmlDocument _auditResultXml = (_auditResult) as XmlDocument;

Returns whether the post was successful or whether there were errors.

[To view posted audit notes on FMS go to: ‘Reports’ | ‘General Ledger’ | ‘Audit’ | ‘Audit Trial’ (select the correct year if not already selected)]

Warning: The API calls are only tested on bank journals, it may be possible to create and post journals of different type using these APIs. However these would be unsupported and may break the integrity of the FMS database.