Integration | Sage Pay | Payments Technical Guide | Statements

Programmers guide

To start, please refer our programmer’s guide for more detail on how to apply the required methodology.

Please note:
Sage Pay may provide example/sample/demo code snippets in this technical document. Such snippets are for guidance purposes only and may not function on every developer’s system/s. Sage Pay disclaims any and all liability for the usage of any of the example/sample/demo code snippets provided -and you as the developer must accept full responsibility for the usage of any example/sample and/or demo code.

While every possible effort has been taken to ensure compatibility across multiple system configurations, the example/sample/demo code cannot be guaranteed to work on all systems, with all operating systems and
or with all system configuration/s.

Introduction

A file download from a Sage Pay online account that can be used for reconciliation of data processed. All transactions including debit orders, payments, credit cards, forex, service fees and bank transfers are included in the statement which will allow for full reconciliations of cash book, debtors and creditors account within accounting or management system. Accounting guide provided for correct transaction posting at the end of this document.

Click here for the quick start guides for:

  • Account Service

Technical Information

Introduction

The Sage Pay Integration Web Service is a generic web service which facilitates the automation of uploads and downloads. The web service exposes multiple entry points and methods which are accessed by remote systems to perform specific functions. This specification relates to the downloading of daily or interim statement files from the Sage Pay system.

A Sage Pay Account service key is required for authentication and access to this end point. Service keys are issued to merchants by Sage Pay on request.

Usage

The file specified in this document is produced when the web service receives a valid request for a statement file. The remote system accesses the entry point https://ws.sagepay.co.za/NIWS/niws_nif.svc and calls the method ‘RequestMerchantStatement’ or ‘RequestInterimMerchantStatement‘. The input parameters required are the Account service key and the statement date.

The service

Object Name Description
Method RequestMerchantStatement Parameter:

  • ServiceKey (Account service key)
  • FromActionDate
Method RequestInterimMerchantStatement Parameter:

• ServiceKey (Account service key)

Method RetrieveMerchantStatement Parameter:

  • ServiceKey (Account service key)
  • PollingId (response received from request)

Full daily statements

RequestMerchantStatement

  • Full daily statements are available on the day following the business day requested.
  • The remote system may request a statement for any day in the past.
  • The requested date may not be current date or any future date.
  • It is recommended that the remote system requests a full daily statement every day and stores the data locally.

Interim statements – not recommended for Sage OpCo integration

Input

Full daily statements

Name Alpha/Numeric Value
Service Key AN Sage Pay Account key
Statement date N CCYYMMDD

Interim statements

Name Alpha/Numeric Value
Service Key AN Sage Pay Account key

Response

Successful

The Sage Pay system will respond to successful requests with a polling id which is used to poll the response queue for the statement file:

Sage Pay request example

Unsuccessful

In the event that the request was unsuccessful one of the following codes will be returned

Code Description
100 Authentication failure. Ensure that the service key in the method call is correct
101 Date format error. If the string contains a date it should be in the format CCYYMMDD
102 Invalid date. Full daily statement not available for the requested date.
200 General code exception. Please contact Sage Pay Technical Support.

Retrieve statement

Postback

If the postback option has been selected and a postback URL has been configured in the Sage Pay Connected service module, no polling is required. The statement will be posted to the URL as a tab delimited file. No additional web service call is required.

Note:

The polling id will still be returned to the remote system even if the postback option is configured. The remote system can still call the RetrieveMerchantStatement method as a failback option.

Polling

  • Where no postback option has been configured, the remote system will need to call the RetrieveMerchantStatement method to fetch the statement:
  • The remote system collects the statement file using the polling id issued by the system.
  • The remote system accesses the entry point https://ws.sagepay.co.za/NIWS/niws_nif.svc and calls the method RetrieveMerchantStatement for both interim and full daily statements.
  • The parameters required are the Account service key and the polling id which was returned by the Sage Pay system.
Name Alpha/Numeric Value
Service Key AN Sage Pay Account key
Polling id AN The id returned by the Sage Pay system


Successful

  • The Sage Pay system will respond with the requested statement data as a tab delimited string with newline constant at the end of each line.
  • Both Daily and Interim statement file will be terminated with the Closing balance (CBL) as the final record in the file.

Example

Sage Pay statement example

Unsuccessful

In the event that the request was unsuccessful one of the following codes will be returned

Code Description
100 Authentication failure. Service key incorrect
200 General code exception. Please contact Sage Pay Technical Support.
“FILE NOT READY” The requested statement has not yet been created. Retry later.
“NO CHANGE” No new transactions are available for download

Statement file format

The data is returned in a tab-delimited text file

Structure

Field Name Type Value
1 Transaction date AN CCYYMMDD
2 Transaction type A See appendix 1 below
3 Transaction id N Unique Sage Pay reference
4 Description AN Short description of the transaction
5 Amount N Transaction amount in rands
6 Symbol AN Positive/Negative
7 VAT N VAT amount
8 Extra 1 AN User-defined data received in upload file – i.e. invoice number
9 Extra 2 AN User-defined data received in upload file – i.e. order number
10 Extra 3 AN User-defined data received in upload file – i.e. client code
11 Accounting ledger description AN General Ledger description for mapping and import into external accounting system

Field explanations

Field Explanation
Transaction date For daily statements the date must be in the past.
For interim statements the date must be the current date.
Transaction type The 3 character transaction type. See appendix 1 below
Transaction id The unique transaction id assigned by Sage Pay
Description Transaction description
Amount The amount is returned as a numeric value with 4 decimal places and no sign
Symbol Indicates whether this has a positive or negative effect on the account balance
VAT The VAT on the transaction if any
Extra 1 2 & 3 The input file allows a user to include up to three additional free text fields. These fields are returned in the statement download.
The Extra fields can be used to identify or post transactions in the remote system.
You may use any identifying details like i.e. an invoice or customer number etc in these fields
Accounting ledger description General Ledger description for mapping and import into external accounting system

Sage Pay statement integration

The mapping below is aimed at mapping the Sage Pastel Bank Manager product but the same methodology can be applied to any accounting system

Initial Mapping setup

  1. Sage Pay will provide a blank statement template for initial mapping setup of the Sage Pastel Bank Manager product in Sage Accounting
  2. This template will have R0.00 values in the amount field.
  3. For the purpose of the mapping import, the following fields will display in the Sage Pastel Bank Manager for mapping:
    1. Sage Pastel Bank Manager “Statement reference” = Sage Pay “Ledger Account” column
    2. Sage Pastel Bank Manager “GL Description” = Sage Pay “Description” column
    3. The Sage Pay “Ledger Account” field is a naming convention used in Sage Pastel, as far as possible, and is used for ease of reference for the end user to apply mapping per their Chart of Accounts.
    4. When the mapping is applied to the Sage Pay template, the mapping should be linked to the “Transaction Type” field, which will always appear on every statement import from Sage Pay. This mapping will be in the background, and probably not display to the User.  This field contains acronyms for Sage Pay transactions.

Actual Statement importing

  1. When a Sage Pay statement imports (after the mapping was concluded), the following rules are to be considered
    1. Sage Pastel Bank Manager date field = Sage Pay date field
    2. Sage Pastel Bank Manager Statement Description = Sage Pay “Description”.
    3. Mapping Type Field = Determine whether there is an entry in the Extra 1 field.
      • No entry = then entry type is “G”
      • Entry = Determine whether it is a supplier or customer transaction and allocate “S” or “C”, as applicable
    4. Mapping Account field = If the Extra 1 field is blank, apply the default mapping used when doing the initial mapping using the mapping template. If there is data in Extra 1, Apply data in Extra 1 to the Mapping Field in the Sage Pastel Bank Manager eg. GSO001 appears in the Extra 1 field, input GSO001 into the mapping field.
    5. Amount field = Consolidate the Symbol field and Amount field and apply the value to the Sage Pastel Bank Manager Amount field (if the symbol is a + then the amount will be positive; if the symbol is – then the amount is negative).
    6. The GL Description field = Sage Pay “Transaction Description”.


The tables below will indicate which suggested ledger account should be affected. The contra account will always be the Sage Pay ‘bank account’.

Statement transaction types (see tips for reconciliation below)

Please click on the heading/s below for more detail:

Transaction code Transaction description Ledger Account
OBL Opening balance
CBL Closing balance
DTT Deposit Business Bank account
DTR Deposit Return Business Bank account
NSF Service fee Sage Pay fees (service fees)
BTR Bank transfer to client Business Bank account
BTU Bank transfer return Business Bank account
INP Interest paid Interest Paid expense
INR Interest received Interest received
ABR Account balance recovery Business Bank account
ABU Account balance recovery return Business Bank account
CSP Cash payment Expense account
VAT Value added tax Input Vat
IAT Inter-account transfer received Sage Pay Bank Account
IST Inter-system transfer Sage Pay Bank Account
BDW Bad Debt Write-Off
IPR Interest paid to Sage Pay reversal
IRR Interest paid to Merchants reversal
REB Rebate Debtors
Transaction code Transaction description Ledger Account
TDD 2 Day debit order Debtors
TDC 2 Day credit card Debtors
DRU Debit unpaid Debtors
SDD Same day debit order Debtors
SDC Same day credit card Debtors
NAS NAEDO Successful transaction Debtors
NAD NAEDO Disputed transaction Debtors
NAU NAEDO unpaid transaction Debtors
NAT NAEDO tracking transaction Debtors
Transaction code Transaction description Ledger Account
SAL Same day salary payment Salaries
SRU Salary payment return Salaries
SAD Dated salary payment Salaries
Transaction code Transaction description Ledger Account
CRP Same day creditor payment Creditors
CRU Creditor payment return Creditors
DCP Dated creditor payment Creditors
Transaction code Transaction description Ledger Account
PNP Pay Now Retail payment Debtors/Sales
PNQ Pay Now Retail payment return Debtors/Sales
PNM Pay Now MasterPass™ payment Debtors/Sales
PNW Pay Now MasterPass™ unpaid Debtors/Sales
PNE Pay Now EFT payment Debtors/Sales
PNZ Pay Now EFT return Debtors/Sales
PNA Pay Now Credit Card authorize Debtors/Sales
PNC Pay Now Credit Card payment Debtors/Sales
PNU Pay Now Credit Card declined Debtors/Sales
PND Pay Now Credit Card dispute Debtors/Sales
PNR Pay Now Credit Card refund Debtors/Sales
PIA iPay Auth Debtors/Sales
PIS iPay Success Debtors/Sales
PIF iPay Failure Debtors/Sales
Transaction code Transaction description Ledger Account
CDR Risk Reports report
AVS AVS

Data flow

Polling

Sage Pay process flow

Postback

Sage Pay process flow

Accounting entry guidelines

This document is a guideline to use when determining how you as a merchant could generate accounting entries, when importing the Statement download from your Sage Pay account.  Please note that it is only a guide and is used at your own discretion.

Reconciliation tips

It is highly advisable to:

  1. auto / schedule a download -and reconcile your Sage Pay statement on a daily basis after 09h00 every day as manual processing of entries received by Sage Pay during the previous 24 hours, which Sage Pay could not automatically reconcile from the banking system; will only be available after 09h00 on a working day.
  2. When  receiving electronic funds transfer (EFT) / internet bank payments to your Sage Pay statement from your customer/s ensure that;
    1. your customer uses the correct EFT payment reference
      1. EFT payments that does not have the correct payment reference as supplied by Sage Pay, will not appear on your Sage Pay statement and will be allocated to a non interest bearing suspense account until sufficient proof of payment has been received. Such EFT payments may carry an additional manual processing -and allocation fee.
    2. when your customer EFT’s funds from his/her bank account – they do so to the same bank at Sage Pay as their own. This will ensure the payment is allocated faster. Sage Pay has a number of special clearing accounts at multiple banks for this purpose. Please contact your Account Manager for more details on special clearing accounts for EFT settlement.
    3. your client does not pay cash -or cheques into the special clearing account/s as such may levy an additional charge on your Sage Pay statement for cash deposit -or special clearance fees.

Suggested business rules for processing debit orders

  1. when it comes to debit order transactions (including NAEDO);
    1. You may auto resubmit a failed payment (DRU) on the following conditions:
      1. You have a valid debit order mandate authorizing you to resubmit the transaction for payment
      2. The said transaction has not returned more than twice (2) on code [02]
      3. The transaction has not returned for any other code but [02]
    2. Should the transaction return (DRU)
      1. more than twice (2) on code [02] -or
      2. returns on any other unpaid code except [02] –
        a new mandate from your customer is required as Sage Pay will block the transaction line since the current mandate has then expired.

Transaction Types and Associated General Ledger Transactions

There are five types of transactions which can occur on your Sage Pay account.  These transactions are listed above in section 6, with a brief description to each, and the general ledger ‘double entry’ associated with each transaction.

Accounting entries

The possible accounting entries that you may want to generate from these transactions types, together with a recommended GL Account will be illustrated in this document.  In our suggested accounting setup it is imperative that you set up your Sage Pay account as a “Bank Account” and as such reconcile it as you would any other bank account.  A detailed Sage Pay Statement is also available on the Sage Pay web account for reconciliation purposes.

General Ledger Account list

A recommended chart of accounts to use for the Sage Pay related transaction would be as follows:

  • Sage Pay Bank Account.  We suggest that you set up this Sage Pay Account in the cashbook and treat it as a ‘bank account’ as it is in effect a ‘Cash’ account.  This will enable you to transact against your Accounts Receivable and Accounts Payable sub-ledgers.
  • Accounts receivable.  Setup a sub-ledger for each of your client/s.
  • Income account to reflect the relevant income for which you are debiting.
  • Bank account which is your bank account registered on your Sage Pay profile.
  • Fees account for service fees charged by Sage Pay.
  • Accounts payable sub-ledger for each one of your creditors.
  • Salaries account for when you pay salaries via Sage Pay.

Version 4/2016 last updated 5 Jun 2016