Clearing files

A clearing file is a daily closing report that is mainly used by large merchants for the daily closing in their ERP system. They are provided in the CSV format.

The clearing files can be retrieved through a SFTP connection and are provided daily.
SFTP
To set up the SFTP connection, please follow these steps: https://ratepay.gitbook.io/sftp/​
The clearing files are stored on the SFTP server as a CSV file under the following path: 
/from_ratepay/clearing/

Each clearing file will be removed from this path after 30 days and copied to 
/archive/clearing/
CSV structure
clearing_example.csv
811B
Binary
Clearing File Example CSV
Column
Description
Possible Values
Example
Issued by
UID
bigint
Unique identifier
-
123
Ratepay
ORDER_DATE
timestamp
Date when request was sent to Ratepay-gateway
YYYY-MM-DD HH:MM:SS
2013-01-01 12:32:53
Ratepay
TRANSACTION_ID
char(20)
Ratepay-Identifier of order
-
39-2013050217283
Ratepay
SHOPSORDER_ID
Shop-Identifier of order
-
47110815
​order-id​
REFERENCE
char(20)
Ratepay-Identifier of record (unique identifier)
-
RP12313123XY
Ratepay
ENTRY_TYPE
char(20)
Type of entry
1 - PAYMENT_REQUEST
2 - PAYMENT_CONFIRM
3 - CONFIRMATION_DELIVER
4 - CANCELLATION
5 - RETURN
6 - CREDIT
7 - CHANGE-ORDER
PAYMENT_REQUEST
Ratepay
EXPORT_DATE
timestamp
Date when entry was exported to Ratepay-FIBU. If EXPORT_DATE is empty then the entry is send only for informational purpose.
YYYY-MM-DD HH:MM:SS
2013-01-05 12:32:53
Ratepay
AMOUNT_GROSS
decimal(14,2)
Gross amount of order / payment change
-
111.99
Ratepay
DISAGIO_GROSS
decimal(14,2)
Gross amount of disagio
-
11.99
Ratepay
TRANSACTIONFEE_GROSS
decimal(14,2)
Gross amount of transaction fee
-
1.99
Ratepay
PAYMENTCHANGEFEE_GROSS
decimal(14,2)
Gross amount of payment change fee
-
2.99
Ratepay
CURRENCY
char(3)
Currency of order
EUR
EUR
Ratepay
CUSTOMERGROUP
char(10)
Customer Group Identifier
1 - B2B
2 - B2C
2
Ratepay
KNOWNCUSTOMER
char(1)
Customer sent as known regular customer by shop
1
0
1
Ratepay
PRODUCT
char(1)
ID describing product
1 - INVOICE
2 - INSTALLMENT
3 - DIRECT DEBIT
4 - PREPAYMENT
5 - INSTALLMENT DIRECT DEBIT
1
Ratepay

How to import a CSV settlement report into Excel

Merchant invoice

Last modified 3yr ago

Column	Description	Possible Values	Example	Issued by
`UID` bigint	Unique identifier	-	123	Ratepay
`ORDER_DATE` timestamp	Date when request was sent to Ratepay-gateway	YYYY-MM-DD HH:MM:SS	2013-01-01 12:32:53	Ratepay
`TRANSACTION_ID` char(20)	Ratepay-Identifier of order	-	39-2013050217283	Ratepay
`SHOPSORDER_ID`	Shop-Identifier of order	-	47110815	order-id
`REFERENCE` char(20)	Ratepay-Identifier of record (unique identifier)	-	RP12313123XY	Ratepay
`ENTRY_TYPE` char(20)	Type of entry	1 - PAYMENT_REQUEST 2 - PAYMENT_CONFIRM 3 - CONFIRMATION_DELIVER 4 - CANCELLATION 5 - RETURN 6 - CREDIT 7 - CHANGE-ORDER	PAYMENT_REQUEST	Ratepay
`EXPORT_DATE` timestamp	Date when entry was exported to Ratepay-FIBU. If EXPORT_DATE is empty then the entry is send only for informational purpose.	YYYY-MM-DD HH:MM:SS	2013-01-05 12:32:53	Ratepay
`AMOUNT_GROSS` decimal(14,2)	Gross amount of order / payment change	-	111.99	Ratepay
`DISAGIO_GROSS` decimal(14,2)	Gross amount of disagio	-	11.99	Ratepay
`TRANSACTIONFEE_GROSS` decimal(14,2)	Gross amount of transaction fee	-	1.99	Ratepay
`PAYMENTCHANGEFEE_GROSS` decimal(14,2)	Gross amount of payment change fee	-	2.99	Ratepay
`CURRENCY` char(3)	Currency of order	EUR	EUR	Ratepay
`CUSTOMERGROUP` char(10)	Customer Group Identifier	1 - B2B 2 - B2C	2	Ratepay
`KNOWNCUSTOMER` char(1)	Customer sent as known regular customer by shop	1 0	1	Ratepay
`PRODUCT` char(1)	ID describing product	1 - INVOICE 2 - INSTALLMENT 3 - DIRECT DEBIT 4 - PREPAYMENT 5 - INSTALLMENT DIRECT DEBIT	1	Ratepay