In the Internet industry, as long as payment is involved, there is bound to be a need for account checking. Almost all the businesses of Internet companies will involve payment. Larger companies even have their own third-party payment companies, so account checking is universal. Account reconciliation system is the most important part of the payment system, but also the last line of defense to ensure the security of transactions and funds. In most Internet companies, there are generally independent reconciliation systems to deal with it, such as e-commerce platforms, Internet finance, third-party payment companies, etc.

Reconciliation is a part of the payment system, so before reconciliation we first understand the relevant business knowledge

Business knowledge

What is reconciliation

The traditional reconciliation is to check the accounts, refers to the accounting, in order to ensure that the books are correct and reliable records, the relevant data in the books to check and check the work. In bank or third-party payment, account checking is actually a process of mutual confirmation of transactions within a certain period. Generally, on the second day, the bank or third-party payment company will clear up the transactions of the previous day, generate a statement for merchants on the platform to download, and settle the settlement to merchants on the platform. At the next level, in the Internet finance industry or the e-commerce industry, account reconciliation is actually to confirm the correctness of transactions and funds with the payment provider (bank and the counterparty payment) within a fixed period, and ensure that the transactions and funds of both sides are consistent and correct.

In a broad sense, all data interactions across applications should theoretically be reconciled. So reconciliation can also be divided into information flow reconciliation, capital flow reconciliation. Information flow reconciliation is also generally used in the reconciliation of its own internal system, such as the reconciliation between the payment data of the payment system and the business data of the business system, to ensure the consistency of capital transactions and business transactions. Capital flow reconciliation is the capital transaction reconciliation between the payment system and the bank or third-party payment system.

Check for ways

  • One-way account checking: generally, the third-party payment institutions or bank statements are used for account checking with their own system to prevent the problem of order dropping;
  • Two-way checking: two-way checking of the flow between two applications, such as the order and financial system, to ensure that the financial system payment success of the record, the order system is also successful; Also ensure that the order system records success and that the financial system is successful.

We usually use two – way account reconciliation

Reconciliation related issues

Different systems day cut point inconsistent problem: rolling reconciliation error processing: compensation, compensation (refund)

Relevant concepts

Ledger of peace

For every transaction, the records of each participant must match without deviation. The work of the reconciliation system is to find discrepancies in the records, that is, rolling accounts; Then, by manual or automatic means, these differences are resolved, that is, accounting.

Long bills and missing orders

On the basis of platform transactions, I checked the account with the bank and found that for transactions within the cycle, the platform had this order but the third-party payment did not, which became the platform long payment. Platform long payment is generally due to the user in the time of payment across the day, such as the user created an order at 23:58 minutes, in the morning of the next day 00:03 minutes to pay. In the case of reconciliation based on bank transactions, if the bank has the order but the platform does not have the order, the platform misses the order. Platform missing order is rare, generally direct manual processing.

The account system

In the general payment system, it is divided into login account and payment account. The payment account refers to the owner’s equity certificate of the user’s funds used for transaction in the payment system. Login account refers to the user’s credentials and personal information for logging in to the system. A user can have more than one login account, and a login account can have more than one payment account, such as change account, stored value card account, etc. In general, payment accounts are not shared between multiple login accounts. A reconciliation transaction is usually a payment account involved in the transaction.

Transactions and Accounts

Account setup, usually begins with a transaction. The realization of the transaction must have the support of the account, the account is the basic component of the transaction. From the perspective of a payment system, the flow of funds involved in a transaction is the flow of funds from one account to another. The party that initiates the transaction, called the transaction agent, can be either a person or an institution.

Clearing and settlement

Settlement mainly refers to the currency receipt and payment between different banks, which can be considered as the sending, receiving and verification of payment instructions by the initiating bank and the receiving bank before settlement. The result is a comprehensive exchange of settlement tools and payment information, and the establishment of final settlement positions.

Settlement refers to the process in which the positions to be settled resulting from the settlement process are respectively processed by the sponsoring bank and the receiving bank, the fund transfer is completed, and the receipt and payment parties are notified. At present, most bank settlement business is mainly completed through two types of accounts: one is agency accounts opened between banks and the other is accounts opened with the central bank, independent financial institutions such as UnionPay, or third-party payment institutions.

Settlement: to calculate the time and amount of money receivable and payable by each party. Settlement: The actual transfer of funds to the parties at the specified time based on the result of the settlement

pool

Users’ reserve funds (such as recharge) are put in the bank account of the enterprise, and the enterprise can use these funds at will, which is the capital pool. Corresponding to third-party escrow, the user’s standby payment funds are placed in the virtual account opened by the enterprise in the third-party payment institution for the user, and the enterprise cannot take out these funds at will. At present, the Internet finance fully requires the access to bank depositories, that is, the bank will create a fund account for each user to protect the user’s funds, and the Internet finance company cannot transfer the amount in these funds accounts at will.

Reconciliation system

Check for design

Reconciliation system design stage, the reconciliation system is divided into four modules, each module is responsible for their own functions.

  • File acquisition module: download or read account reconciliation files of various channels
  • File parsing module: Creates different parsing templates and obtains them based on the channel and file type
  • Reconciliation processing module: reconciliation of business logic processing
  • Error handling module: Processes orders in the error pool

Generally, a scheduled task is designed and triggered at a fixed time point every day. The scheduled driven scheduling class calls four modules respectively to handle the reconciliation. Some banks will actively push the statement and then trigger the reconciliation process through HTTP callback.

Reconciliation algorithm

I. Process:

1. Obtain reconciliation documents from upstream channels (banks, Unionpay and other financial institutions), and the program will parse them line by line and put them into storage; 2. In the process of the program, based on the table of the upstream reconciliation file, the program reads line by line and compares the accounting records with the transaction records of our system (in the case of an accounting system, a reasonable plan should be to compare the accounting records) to find out the difference records; 3. Based on the transaction records of our system and the accounting records, the program reads and compares the upstream reconciliation files line by line to find out the different records.

Ii. Defects:

1. Query related data in the process of account checking. If the amount of data is huge, it will have a great impact on the performance of the database, and the expansion of account checking logic is very troublesome; 2. The efficiency of line-by-line alignment algorithm is low, but there is no room for good optimization in the algorithm. INTERSECT and MINUS have high pressure on the database; 3. In the case of large business volume (for example, there are hundreds of upstream channels to be matched, and each has hundreds of thousands of transaction records), the load of the reconciliation server and database server is high. Even if the use of read and write separation, accounting use read library, the same pressure; 4, import batch files, line by line storage efficiency is low (need to establish network connection and close the connection each time).

Iii. Improvement:

1. If network transmission is involved, batch operation should be used as far as possible to reduce network consumption and time consumption; 2. Use NOSQL database such as Redis to reduce the pressure on database server. At the same time in the expansion is also easy, a Redis server is not enough, can be used for unlimited increase server reconciliation; 3. Use the SDIFF function of Redis set and the high performance of Redis SDIFF algorithm to compare the difference between the upstream record and our record.

Reconciliation process

1. Download the statement

Most banks require the access party to provide the FTP service. The bank regularly pushes the statement to the FTP server provided by the access party. Some banks will provide the download service of the statement, through FTP/HTTP, FTP mode is most; In addition, the statement of e-banking is special. Generally, it needs to be settled and logged into the background management system of e-banking, manually downloaded, and imported into the reconciliation system after the settlement and download of the statement.

Different payment channels have different download classes. If it is an HTTP interface, the file will be written to the statement. If it is an FTP server, the statement in the server will be downloaded to the local directory with resolution. The main involved code FTP tool class, HTTP (S) tool class, related IO read and write.

Technology selection, HTTP(S) with Apache HttpClient can achieve link pooling and breakpoint continuation, FTP can also use apache Commons Net API. In either case, you need to set the retry times and link timeout. Retry times and interval Settings need to be careful, retry too often, easy to kill the server. If the time interval is too large, the subsequent processing steps will be blocked. Five to 10 minutes is an appropriate retry interval.

2. Create batches

Batches are created to prevent duplicate reconciliation on the one hand and to store the reconciliation result information into batches at the end of reconciliation on the other hand.

3. Parse the file

Parsing file is mainly to download the reconciliation file parsing into the data type we can reconcile and into the library. Different types of files are parsed in different channels, so you can also design different parsing templates, using the factory pattern to parse different formats of files into a uniform data type that can be reconciled. The file types that are parsed generally include: JSON, TEXT, CVS, Excel, etc. In addition, some banks will encrypt the bill or provide zip packaging format, so it is necessary to develop additional ZIP tool classes and encryption and decrypting tool classes for processing.

The main information contained in the reconciliation file includes the following fields: merchant order number, transaction serial number, transaction time, payment time, payer, transaction amount, transaction type and transaction status.

4. Account reconciliation

Account reconciliation is also the core logic of account reconciliation, specifically divided into the following steps to achieve:

  • Query all successful orders on the platform
  • Query all trading orders on the platform
  • Query the data in the platform cache pool
  • Query the bank transaction successful order
  • Account reconciliation begins with the data of the platform, and the long payment of the platform is recorded into the buffer pool
  • Start account reconciliation based on the data of the bank channel

Account reconciliation logic based on platform orders: Based on all successful orders on the platform, iterate through all the data of bank orders, find out the orders with the same order number, and compare whether the amount and commission of the orders are consistent. If it is skipped uniformly; If inconsistent, the platform order will enter the error pool; If the transaction is not found in the bank order, the order goes into the cache pool and the platform payment is recorded. At the same time statistical reconciliation related amount and order number.

Account reconciliation logic based on bank orders: Based on the transaction data of the bank, the transaction of all platforms (including unsuccessful orders) is traversed to find out the orders with the same order number but inconsistent payment status, and then the comparison amount is deposited into the error pool. If the order is not found in the transaction of the platform, it will search through the cache pool to find the corresponding platform order and verify whether the amount is consistent. If not, it will enter the error pool. If the corresponding order is still not found in the cache pool pool, directly enter the error pool and record the missed order of the platform. At the same time statistical reconciliation related amount and order number.

5. Account checking statistics

According to the reconciliation process, statistical information includes: reconciliation time, reconciliation is successful, balance amount and order number, error amount and order number, cache pool amount and order number, etc.

6. Error handling

In the general system, error handling is divided into two kinds, one is to handle manually, the other is to handle automatically.

The main cases are as follows:

  • No local payment, payment channel has paid. This is mainly caused by the incorrect local receiving of the asynchronous notification from the channel. The general process is to change the local state to paid and do subsequent processing in response, such as notifying the business party, etc.
  • The payment has been made locally and through the payment channel, but the amount is different, which needs to be checked manually.
  • The payment has been made locally, but there is no record in the payment channel; Or there is no local record, but the payment channel has a record. Excluding trans-japanese factors, this situation is very rare and needs to be dealt with after understanding the specific cause.