Data Reconciliation

The purpose of reconciliation is to compare transaction records and verify that payments have been processed correctly. By reconciling transaction information regularly, merchants will gain a better understanding of their business transaction life cycle - from initial authorization, to settlements, transaction disputes (returns), and refunds.

Available Payment Methods

Reconciliation is currently available for alternative payment methods (i.e. all payment methods but credit card) within Wirecard Payment Gateway.

Reconciliation Process

The reconciliation files must be downloaded from an SFTP server on the Wirecard Payment Gateway. The download process is described in File Download. Although it is possible to download files manually, we recommend to automate the process for the sake of consistency and convenience. Likewise, it is recommended to run a program to facilitate the reconciliation process of the downloaded files. The program used must be able to handle the CSV file format.

To reconcile your transactions, compare the reference transaction ID in the reconciliation file with a transaction ID that is already available in your system. You will be able to see if another transaction has been created updating the current status of your payment. Should you notice inexplicable discrepancies between the reconciled data and your records that you cannot resolve internally, please contact Merchant Support

Reconciliation Files

The Wirecard Payment Gateway generates transaction reports which are very similar to a typical bank account statement. Merchants are recommended to match and reconcile these report files regularly (daily) with the data recorded in their system. When matching transaction data it is important to remember how the payment total is calculated and also that the data does not represent a complete payment history but only the account activities that have been recorded over the previous 24 hours.

Formats

The Wirecard Payment Gateway is configured to generate daily transaction reports containing sequential data sets for each of your transaction requests processed by the system. These records are called reconciliation files and are saved in CSV format on the Wirecard SFTP server. When opened and read in a spreadsheet application like Microsoft Excel, the data entries are divided in columns and rows. Opened as plain text in any standard text editor program, however, the field entries are separated by a semicolon (;).

The files contain transaction data processed over the previous 24 hours. They do not present a complete payment flow per payment (from authorization, to settlement with possible rejections and disputes) but only those transactions which have been processed by the Wirecard Payment Gateway within the time period for which the report has been created.

Naming Conventions

The reconciliation files are stored on the Wirecard server in the following format:

<identifier>_<ip>_<date>_<merchant account id>.csv

where

  • <identifier> identifies the processing gateway

  • <ip> is an internal Wirecard IP address

  • <date> is a time stamp represented as YYYYMMDD (year, month, day). It shows the exact file generation date.

  • <Merchant Account ID> is a 36 character identifier specifying your business uniquely in the Wirecard Payment Gateway.

  • .csv is the file extension denoting the format Comma Separated Value.

Transactions processed on the Wirecard Payment Gateway are recorded in a file named EngineAPTransactions.csv. This file encompasses all transaction types for the alternative payments currently available in the Wirecard Payment Gateway processed over the previous 24 hours (currently get-url, check-signature, pending-debit, pending-credit, credit, debit, deposit, credit return, and debit return).

Example
EngineAPTransactions_195.93.244.10_20130101_1b3be510-a992-48aa-8af9-6ba4c368a0ac.csv
Structure and Attributes for Alternative Payments

The file EngineAPTransactions*.csv currently comprises transfer transaction requests for the alternative payment methods that have been posted or modified by the system over the past 24 hours. If a column does not apply to a payment method, the column will contain NULL. The data is recorded in the following columns/fields:

Field Description

MERCHANT ACCOUNT NAME

The name of the merchant in the WebService Interface

MERCHANT ACCOUNT ID

The Merchant Account ID. This is a unique 36-digit number defining the merchant account.

REQUEST ID

This is the identification number of the request identifying the transaction in the merchant’s system. It is unique for each request.

PAYMENT METHOD

This is the name of the payment method that was processed via Wirecard Payment Gateway.

TRANSACTION ID

This field contains the Transaction ID that was created by Wirecard Payment Gateway for the initial XML request message.

TRANS. CREATION TIME STAMP

Timestamp of the transaction from the merchant’s system.

TRANSACTION TYPE

Possible values for transactions are: pending-debit, pending-credit, debit, credit, debit-return, credit-return, deposit.

TRANSACTION STATUS

The transaction status (e.g. SUCCESS). See also Transaction Statuses.

TRANSACTION STATUS CODE

The transaction status code (e.g. 201.0000). See also Transaction Statuses.

TRANSACTION REASON DESCRIPTION

A reason description (e.g. File generation was performed successfully). See also Transaction Statuses.

TRANSACTION AMOUNT

The amount of the original transaction presented with decimal point and two decimal places (example: 411.40 or 7.88).

TRANSACTION CURRENCY

The currency of the original transaction.

SETTLEMENT AMOUNT

The amount credited to the merchant account.

SUM SETTLEMENT AMOUNT

The total sum of credit and debit type transactions that were booked on the account within one summary booking.

SETTLEMENT CURRENCY

The ISO 4217 currency code of the credited amount.

EXCHANGE RATE

The exchange rate used for this transaction (applicable only if the transaction and settlement currencies differ).

EXCHANGE RATE SOURCE

This is the source of the referenced exchange rate (e.g. Deutsche Bank).

USAGE

A message sent with the original XML request for the debtor of the transaction that will appear on the debtor’s account statement. Also known as "Descriptor".

REF. TRANSACTION ID 1

Reference to a previous transaction of the same payment flow. Also known as parent-transaction-ID. For instance, a reversal has a transaction-ID referring back to the previous transaction.

REF. TRANSACTION ID 2

REF. TRANSACTION ID 3

PTRID

Provider Transaction Reference ID: This ID represents the end-to-end flow of a transaction and is used to identify transactions within the banking system. It is generated by Wirecard.

ORDER NUMBER

This is the order number of the merchant.

If a merchant uses the Wirecard Enterprise Portal (WEP) in combination with the data reconciliation files, there may be a timezone discrepancy.

The timezone for the data reconciliation file is UTC.

The timezone in WEP is configurable. The timezone is automatically taken from the user’s device (laptop, desktop, etc.). If there is a difference, please adjust the timezone in WEP to UTC.

Sample Reconciliation File

The following is an example of the file layout for the AP Data Reconciliation for Wirecard Payment Gateway, displayed with a spreadsheet application:

Data Reconciliation File Screenshot
Download Sample

Here you can download a full compilation of samples of possible values within the reconciliation file also displayed with a spreadsheet application.

Unmatched Payment/Transactions

If any of the transaction types deposit, debit-return or credit-return enters the Wirecard Payment Gateway and cannot be matched to an existing payment, this transaction is created as unmatched transaction. It appears in the reconciliation file without a reference transaction ID.

If this unmatched transaction is manually matched to an existing payment on the same day as it has entered the Wirecard Payment Gateway, it appears as a transaction within the payment to which it has been matched. There will be no record of an unmatched payment in the reconciliation file.

If, however, this unmatched transaction is manually matched to an existing payment more than a day after it has entered the Wirecard Payment Gateway, it will not appear in the reconciliation file. If it appeared in the reconciliation file, there would be two entries in the reconciliation file for one transaction referring to two different payments, thereby resulting in a duplicate.

File Download

Although manual downloads are possible, we recommend to use an automated process.

To be able to connect to the Wirecard SFTP server you must have an SFTP client and an internet connection supporting SSH-2 and the necessary network security policies.

Please contact your system administrator to ensure that SFTP traffic is permitted from your machine.

Reconciliation files older than three months will be moved from the Wirecard SFTP server to external storage and will no longer be accessible to the merchant.
Therefore we recommend that the merchant always saves downloaded files to avoid any missing information.
Windows

In a Windows environment, the reconciliation files can be downloaded using any graphical SFTP application (like WinSCP) or command line SFTP download.

To download files with a graphical application, enter the host name sftp.wirecard.com and your username and password. If you do not know your personalized access information, please contact Merchant Support.

Unix or MacOS

You may also use a command line SFTP download from a Unix workstation or MacOS.

To connect to your location on the Wirecard host server (sftp.wirecard.com), enter:

sftp username@sftp.wirecard.com

followed by the password when prompted.

File Directory

When you log on, you are automatically connected by username/customer number/partner number (e.g. C0001 or P0001) to your file repository. In the root window of the remote SFTP server site (Wirecard server) you see the following subdirectories:

SFTP Directory Structure

Retrieving File
  1. Open the folder to <customerName>. It contains three subfolders: error, new, and processed. The files are stored in the folder new.

  2. Open the folder new.

  3. Select the file or files of the time period you want to reconcile.

  4. Copy the desired CSV files to your local machine. We recommend to automate the daily downloads. If you are using a graphical user interface and you do not run an automated program, you can drag and drop those files or copy and paste them.

  5. Reconcile the files using a program tailored to the CSV format.

  6. Wirecard Payment Gateway allows you to archive reconciled report files on the file server for future reference. Therefore move the files to the folder processed.
    Be aware that it is at your discretion to use this file repository.