DocumentationRecipesAPI ReferenceChangelog
Documentation

Reconcile Report

To generate Reconcile reports for full payments, refer to the guide below.

👍

Connect using SFTP

  • IP Address : 52.76.184.174
  • Port : 22
  • Username : The merchant must fill in theSFTP setup form and send the request to support team. An account will be created for merchant to access 2c2p SFTP in demo and production environment.
  • Response Path : /output
  • Drop time for response file: Everyday Before 4.00AM GMT+7

 

How to get the report


  1. At the specified time, 2C2P will drop a CSV file in the SFTP server. The file name follows the format Reconcile2c2p_A_v2.1_MID_CURRENCY_yyyy-MM-dd_HHmmss.csv
  2. The merchant must get the file from the SFTP Server.
  3. For more information on the report's contents, refer to Data Parameters below.

 

🚧

File Format Specification

  • File name format: Reconcile2c2p_A_v1.8_MID_CURRENCY_yyyy-MM-dd_HHmmss.csv - Reconcile2c2p_A_v2.1_JT_THB_2017-07-18_153136.ext
  • Parameter delimiter: comma "," for CSV Format
  • Record delimiter: line break
📘

Data Attribute Reference

Data Type
  • A: Alphabet
  • AN: Alphanumeric
  • C: Characters
  • B: Boolean
  • D: Decimal
  • N: Numeric
Mandatory attributes
  • M: Mandatory
  • C: Conditional
  • O: Optional

 

Data Parameters


👍

Sample File

Download sample csv file here.

2C2P2023102301,6,H,test,0,23/10/2023,test,23/10/2023,000000,00/00/00,000000,702702000000000,00000000000000000000,00000000000000000000,000,000,000,572.00,0.00,0.00,17.16,1.19,,,,,,,0.51,554.16,0.00,SGD,000000,0,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0.0,0.0
2C2P2023102301,1,D,test,0,23/10/2023,test,20/10/2023,102619,20/10/2023,000000,702702000000000,234r23r23r,,001,VI,FIRST DATA CORPORATIONS,1.00000,0.00000,0.00000,0.03000,0.00000,2C2P-231020-0023,21/10/2023,0.66000,0.04000,0.70000,,0.00090,0.97090,0.00000,SGD,478170,05,411111XXXXXX1111,,,,,,3.00,,USA,VI,8076932,2C2P SG Test B,,Customer,Payment for 234r23r23r,,0.00,,,,,0.00,,,,,,,,,7315705,0.00000,0.00000
2C2P2023102301,2,D,test,0,23/10/2023,test,20/10/2023,101800,20/10/2023,000000,702702000000000,998899,,001,VI,FIRST DATA CORPORATIONS,10.00000,0.00000,0.00000,0.30000,0.02000,2C2P-231020-0023,21/10/2023,0.66000,0.04000,0.70000,,0.00900,9.68900,0.00000,SGD,319581,05,411111XXXXXX1111,,,,,,3.00,,USA,VI,8076907,2C2P SG Test B,,Customer,Payment,,0.00,,,,,0.00,,,,,,,,,7315689,0.00000,0.00000
2C2P2023102301,3,D,test,0,23/10/2023,test,20/10/2023,150350,20/10/2023,000000,702702000000000,acxasd123,,001,VI,FIRST DATA CORPORATIONS,10.00000,0.00000,0.00000,0.30000,0.02000,2C2P-231020-0023,21/10/2023,0.66000,0.04000,0.70000,,0.00900,9.68900,0.00000,SGD,627247,05,411111XXXXXX1111,,,,,,3.00,,USA,VI,8078188,2C2P SG Test B,,Customer,Payment for acxasd123,,0.00,,,,,0.00,,,,,,,,,7316638,0.00000,0.00000

Parameter

Data Type

Mandatory

Description

BATCH_NO

AN 20

M

2C2P+YYMMDD (Payin BATCH NO)+NN

LINE_NO

N 5

M

H = Total Line No. , D= Line number

TYPE_TABLE

AN 1

M

H=Header, D=Detail

BANK_CODE

AN 25

M

Bank Swift Code

ACCOUNT_NO

AN 25

M

Merchant Bank Account Number

PAYIN_DATE

AN 10

M

DD/MM/YYYY

VAT_NO

AN 25

M

0107536000633 (Merchant Tax Registation No)

TRANSACTION_DATE

AN 10

M

Transaction date [format : DD/MM/YYYY]

TRANSACTION_TIME

AN 6

M

Transaction time [format : HHMMSS]

SETTLEMENT_DATE

AN 10

M

Settlement date [format : DD/MM/YYYY]

SETTLEMENT_TIME

AN 6

M

Settlement time [format : HHMMSS]

ORDER_DATE

AN 10

M

Customer order date [format : DD/MM/YYYY] Applicable only to HSBC Reconcile Report

ORDER_TIME

AN 6

M

Customer order time [format : HHMMSS] Applicable only to HSBC Reconcile Report

PAYMENT_DATE

AN 10

M

Customer pay date [format : DD/MM/YYYY] Applicable only to HSBC Reconcile Report

PAYMENT_TIME

AN 6

M

Customer pay time [format : HHMMSS] Applicable only to HSBC Reconcile Report

MERCHANT_ID

AN 15

M

Merchant ID defined by 2C2P

ORDERID_INVOICENO / ORDER_ID

AN 20

M

Unique merchant order number

  • Example: 00000000010000091203
  • Note: The same order number may appear in the file for different payment methods. For instance, 1 line item for Loyalty and 1 line item for Card Payment

Applicable only to HSBC Reconcile Report

MERCHANT_INVOICE_NO / INVOICE_NO

AN 50

M

Merchant Invoice Number Applicable only to HSBC Reconcile Report

PAYMENT_TYPE

AN 3

M

Payment type codes

001 = Credit and debit cards / Wallets

002 = Cash payment channels

003 = Direct debit

004 = Others

005 = Refund

006 = Installment Payment

007 = Loyalty Payment

008 = Chargeback

009 = Chargeback Reversal

010 = Other Charges

011 = Authorize Charges

012 = Void Charges

013 = Sale/Capture Interchange Charges

014 = Refund Interchange Charges

015 = Chargeback Interchange Charges

016 = Loyalty Refund

Added 008 in Version 1.9 Added 016 in Version 2.4

PAYMENT_CHANNEL / CHANNEL_CODE

AN 30

M

Refer to Payment Channel Matrix Table

In version 2.3 onwards, PAYMENT_CHANNEL is renamed to CHANNEL_CODE

AGENT_CODE / PAYMENT_AGENT_SERVICE

AN 30

M

Refer to Agent Code

In version 2.3 onwards, PAYMENT_AGENT_SERVICE is renamed to AGENT_CODE

AMOUNT

N 15

M

Amount in 2-decimal value

  • Example: 1000.00

DISCOUNT

N 15

M

Discount amount (Always 0)

TRANSACTION_FEE

N 15

M

Transaction fee, if needed

SERVICE_FEE

N 15

M

Service fee charged to Merchant (MDR)

COMMISSION_FEE

N 15

M

Service fee charged to Merchant (MDR) Applicable only to HSBC Reconcile Report No longer available in version 1.9 onwards

VAT_SERVICE_FEE

N 15

M

Commission Vat Amount

COM_VAT_AMOUNT

N 15

M

Commission Vat Amount Applicable only to HSBC Reconcile Report

TAX_INVOICE_NO / TAX Invoice No

AN 50

M

Tax invoice number which 2C2P issues to merchant on daily settlement

TAX_INVOICE_DATE / TAX Invoice Date

AN 10

M

Issuance date of tax Invoice (Pay-in date)

  • In DD/MM/YYYY format

TOTAL_SERVICE_FEE_AMOUNT / TAX Invoice COM Amount

N 15

M

Tax Invoice COM Amount from Tax Invoice issued to merchant

TOTAL_VAT_AMOUNT / TAX Invoice VAT Amount

N 15

M

Tax Invoice VAT Amount from Tax Invoice VAT issued to merchant

TOTAL_DEDUCT_AMOUNT / TAX Invoice SUM Amount

N 15

M

Tax Invoice Sum Amount = TAX Invoice Com Amount + TAX Invoice VAT Amount

WHT_NO

AN 50

O

WHT Number (Blank)

WHT_AMOUNT

N 15

M

Withholding Tax Amount (3% of ServiceFee)

TOTAL_PAYIN_AMOUNT / PAYIN_AMOUNT

N 15

M

Total Payin Amount (Include Refund) (Amount - Commission Fee - COM_VAT_Amount + WHT Amount - EWT Amount)

SHIPPING_FEE

N 15

M

Shipping cost charged by Merchant (Always 0)

CURRENCY_CODE / CurrencyCode

AN 3

M

ISO 4217 Currency Code

APPROVAL_CODE / approval_code

AN 10

M

Authorization code from issuing bank

  • This field is only applicable when the customer pays with credit or debit card

ECI / eci

AN 2

M

ECI value

  • This field is only applicable when the customer pays with credit or debit card

MASKED_PAN_OR_PAYMENT_REF_CODE masked_pan/ Payment Ref Code

AN 20

M / O

(Optional) First 6 and last 4 digits of the credit card.

  • Example: 444321XXXXXX3212
  • This field is only applicable when the customer pays with credit or debit card

USER_DEFINED_1

AN 150

O

Reference number, booking number, tracking number or order number for merchant use.

USER_DEFINED_2

AN 150

O

Reference number, booking number, tracking number or order number for merchant use.

USER_DEFINED_3

AN 150

O

Reference number, booking number, tracking number or order number for merchant

USER_DEFINED_4

AN 150

O

Reference number, booking number, tracking number or order number for merchant use.

USER_DEFINED_5

AN 150

O

Reference number, booking number, tracking number or order number for merchant use.

MDR

N 5

M

Full Payment MDR in percentage (%)

REFUND_NOTE

AN 250

O

Refund / Chargeback notes

ISSUER_COUNTRY / Issuer country

AN 3

O

Card Issuer Country Added in Version 1.1 onwards

CARD_BRAND

AN 30

O

Refer to Payment Channel Matrix Table Added in Version 1.2 onwards

TRANSACTION_ID / TxnID

N 10

O

Txn ID in PGW and 123 Added in Version 1.3 onwards

MERCHANT_NAME / Merchant_Name

AN 300

O

Merchant's name in 2C2P records Added in Version 1.3 onwards

QR_REF_ID

AN 20

O

QR Payment = QR Ref ID Added in Version 1.5 onwards

CARDHOLDER_NAME

AN 150

O

Cardholder's name Added in Version 1.5 onwards

PRODUCT_DESCRIPTION

AN 250

O

Product description Added in Version 1.5 onwards

PROMOTION_CODE

AN 50

O

Merchant's Promotion Code Added in Version 1.5 onwards

ACQUIRER_DISCOUNT

N 15

O

Discount given to cardholder by acquirer Added in Version 1.6 onwards

ACQUIRER_DISCOUNT_CURRENCY

AN 3

O

Currency of Discount given to cardholder by acquirer Added in Version 1.6 onwards

MCP_AMOUNT

N 15

O

Cardholder's paid amount Added in Version 1.6 onwards

MCP_CURRENCY

AN 3

O

Cardholder's paid currency Added in Version 1.6 onwards

MCP_EXRATE

N 15

O

Cardholder's paid currency to merchant currency Added in Version 1.6 onwards

EWT_AMOUNT

N 15

O

EWT Amount if applicable Added in Version 1.7 onwards

CUSTOM_DATA_ONE

AN 150

O

Custom dataset from 2C2P Added in Version 1.7 onwards

CUSTOM_DATA_TWO

AN 150

O

Custom dataset from 2C2P Added in Version 1.7 onwards

REDEEMED_POINTS

N 15

O

Redeemed points Added in Version 1.8 onwards

REDEEMPOINTS_AMOUNT

N 15

O

Points amount Added in Version 1.8 onwards

VOUCHERDESCRIPTION

AN 255

O

Description of voucher Added in Version 1.8 onwards

REDEEMVOUCHERAMOUNT

N 15

O

Total redeemed voucher amount Added in Version 1.8 onwards

REDEEMEDVOUCHERSCOUNT

N 15

O

Number of vouchers redeemed Added in Version 1.8 onwards

PAYMENTTYPE_NAME

AN 50

O

Payment Type Name Added in Version 1.8 onwards

BACKEND_REFERENCE

AN 50

O

Internal Reference No Added in Version 2.0 onwards

REFUND_CHARGEBACK_FEE

N 15

O

Refund Chargeback fee Added in Version 2.0 onwards

REFUND_CHARGEBACK_VAT

N 15

O

Refund Chargeback VAT amount Added in Version 2.0 onwards

CARDTYPE

AN 15

O

Type of Card Added in Version 2.2 onwards

CARDLEVEL

AN 15

O

Level of Card Added in Version 2.2 onwards

AGENT_CHANNEL_CODE

AN 50

O

Refer to Payment Channel Matrix Table Added in Version 2.3 onwards