Introduction

The purpose of this guide is to familiarize users with the data export mechanism that allows exporting raw transaction and statement data, generated in the remittance process within the gateway to the respective database tables.

Intended Audience

This guide will be useful for database administrators and users that want to learn how to configure and use the mechanism of statement data export to the database tables for further data usage, such as generation of reports and merchant statements with the integrated front-end system.

Overview

In certain cases, there is a need to generate merchant statements and other types of financial reports internally, within the integrated system. For this purpose, the data export mechanism can be used instead of the reporting API module provided that it’s a single front-end system interacting with the gateway.

Data export mechanism enables automatic export of raw transaction and statement data, generated in the gateway to the respective database tables accessible to administrative users with the corresponding access privileges. The source of the exported data is the remittance process taking place on the gateway side. Thus, this mechanism can only be used by facilitators that do both transaction processing and remittance on the gateway side. Data export mechanism is enabled and configured at Portfolio level: you can choose the set of database tables to be filled automatically for each Portfolio. For the export of transaction related data, you should set up TransactionListExport database table creation, for statement related data - StatementListExport, StatementDetailListExport, and StatementFeeDetailListExport tables.


The source of the exported data is the remittance process taking place on the gateway side. Thus, this mechanism can only be used by the integrators that do both transaction processing and remittance on the gateway side.

Access to the exported data and its processing is done directly through the gateway database. Thus, one or more MySQL user accounts with read-only access privileges should be created by the database administrator.

Database Tables Overview

DataRegistryExport

DataRegistryExport table is used to register all records that have been imported to one or more database tables selected in the configuration process. This table cross-references all of the tables with dataRegistryId parameter (the foreign key) and ensures the integrity of the data. It is created for each record (merchant statement) and contains the following information:

• associated entities’ identifiers (portfolioId, resellerId, accountId);
• data type: merchant statement data, merchant statement detail data, transaction data. Each data type corresponds to one of the Data Export Policy settings.
• dates when the data was generated in the gateway (effectiveDate) and exported (createDate). All the data is exported for a closed time period. Thus, createDate is preceded by effectiveDate. Dates of the database tables update and clean-up are calculated based on the createDate field.

TransactionListExport

TransactionListExport table contains the list of transactions contained in merchant statements and details of the transactions including the identifiers of associated entities (merchant account, reseller), payment account and billing information, and related fees amounts.

StatementListExport

StatementListExport table contains general information about merchant statements including total and net amounts of submissions, merchant fees, reserves, splits, etc.

StatementDetailListExport

StatementDetailListExport table contains information about statement details grouped by submission ID and stereotype.

Stereotype is a unique identifier of a set of transactions associated with a particular submission grouped by specific criteria (i.e. submission type, card brand, etc).
Thus, this table contains count and amount of transactions contained in merchant statements, grouped by specific criteria, such as transaction type (credit/sale/return/chargeback/void), card brand, response type (declines/errors), statement adjustment type, etc.
The full list of stereotypes is available here.

StatementFeeDetailListExport

StatementFeeDetailListExport table contains information about all fee amounts and rates related to merchant statements, including per item, flat, intercharge, assessment, and service fees.

Database Export Mechanism

Configuration

Database export mechanism implies required settings of non-recurring (System level) and recurring (Portfolio level) nature.

Camel Job configuration

On the System level you should configure and activate imerchant.data-export Camel job and timer that triggers automatic export of the data from the gateway database to the integrator’s database tables at a set time on a daily basis.

Navigate to Administration => System perspective => Jobs => Job objects and locate imerchant.data-export job using Job Object Search.

To enable automatic database export you should activate imerchant.data-export Camel job and configure the timer using cron expression. We recommend to execute data export between 3 a.m. and 6 a.m., when the server load is low.

Data Export policy settings

Data Export Policy settings allow to select the type of data that will be exported (merchant statement data, merchant statement detail data, transaction data). Data Export Policy is configured on the user interface for each Portfolio in the following steps:
1) Navigate to Portfolio Perspective=>Details=>General
2) There are two Data Export policy positions on this tab. The values in dropdown lists define how the export tables are populated:

• Merchant Statement Data;
• Transaction Data.

Data Export frequency

The data is exported on a daily basis at a time specified in imerchant.data-export Camel job configuration.

Period for which the data is exported

Under regular conditions, the remittance data used to populate database tables is exported on a daily basis for a closed period, i.e. for 24 hours before yesterday midnight. All data for the preceding period is marked as exported and is not taken into account in the process of database tables population.



However, there are some exceptional cases, in which the data is exported for a time period different from the regular scenario:

1) In case when database tables are filled for the first time:

After the logic is deployed on the server and configured as described in previous sections, the database tables are filled for the first time starting from the first of the month in which the logic was set up.

2) In case of technical issue.

For the rare occasions of technical issues, there is a data loss prevention mechanism. This mechanism ensures that the tables are filled with data for the period starting from the last successful export. The dates, for which the data was not exported, are identified based on createDate field value of each exported record registered in DataRegistryExport table.

For example, if the data export was not performed for April, 2-3 due to a system failure, the createDate field value of the last exported records would be April,1. Then it took us two days to fix the issue. Thus, the next database export would be performed on April, 6. In this case, the tables would be populated with the data for the time period between the second and the sixths of April.

Please note that if a specified data set is present in the system, it cannot be exported for the second time.

Data storage and tables cleanup

Database export functionality implies the mechanism of ongoing tables cleanup to maintain the reasonable size of the database and to optimize the server performance.
All records with the creation date earlier than two weeks ago are deleted from the database tables. The creation date is identified in the createDate field of the DataRegistryExport table.

For example, if the data was exported on March,1, this value is saved in the createDate field. In 14 days, on March, 15, all data with the createDate=2019-03-01 23:59:59 is removed.

Monitoring and Diagnostics

If you have noticed any indications of the potential problems with the data export mechanism (for example, data for the last three days is missing in the relevant database tables), you should consider the following possible reasons:

1. The statement data was not generated in the gateway.
In case there are no merchant statements generated in the gateway and approved for a certain time period, data export tables are not populated for the respective period, and this is proper functioning of the mechanism.

2. Incorrect Data Export policy configurations.
Please make sure that the necessary data export options are chosen as described in this section for each Portfolio.

3. Camel job imerchant.data-export was not configured correctly or execution failed.
Make sure that imerchant.data-export is active and configured as described in this section.

If the Camel job was configured correctly on the user interface, verify the archive, Next Execution, and Last Execution dates. If in the archive there are no records for the relevant time period, it may indicate that the job execution failed. In this case, the notification is sent to the system administrator. See this guide for more information.

If you have verified all settings but still have doubts that the data export mechanism works properly, you can run the following query for verification using the Data Querying Module:


The query selects all merchant statements that have been generated in the gateway for the last two weeks but not exported to the database tables, provided that export was enabled in Data Export Policy settings.





In case you have revealed any discrepancies using the query, you should consider the possible reasons with incorrect configurations described above. If the mechanism is configured correctly but you still run into the issues, contact gateway support.