Reconciliation report
The reconciliation endpoint is used to generate a report containing relevant information on all your account movements at the given transaction date, or within a transaction date range. The report can be used to perform financial reconciliation of bookings during the day, or at end-of-day. The reconciliation report will contain payments that have been booked on your account, and may there contain payments with status processed and pendingProcessing.
You can use the intraday reconciliation report, if you want to specify timeframes within a day.
The report is available in CSV and JSON format. There is a limitation of 50,000 payments per report.
If the desired number of payments exceeds these limitations, you will need to use the paginated reconciliation endpoint.
Parameters in reconciliation report request
| Parameter | Required | Description |
|---|---|---|
| FromTransactionDate | Mandatory | Format: ISO 8601 | YYYY-MM-DD. Specify from which transaction date the report should be generated. |
| ToTransactionDate | Mandatory | Format: ISO 8601 | YYYY-MM-DD. Specify until which transaction date the report should be generated. |
| PropertiesIncluded | Optional | Specify properties to include in the reconciliation report results. By default we will include all properties except for paymentId & lastChangedTimestamp that can be added to the array. See below table for all properties of the report. |
| PropertiesExcluded | Optional | Specifies properties to exclude from the reconciliation report results. By default we will include all properties. To get all available properties, send an empty exclude properties. |
| AccountId | Optional | Specify Id of the account on which activities have been booked. It is possible to send comma separated list of GUIDs. Format: comma (,) separated string |
| IncludeProperties | Deprecated | |
| ExcludeProperties | Deprecated |
Parameters in paginated reconciliation report request
The reconciliation report is also available in a paginated version, to allow for a larger number of rows to be included in the report. The content of the file remains unchanged, and the above specification can be used.
Date ranges not supported. The paginated version does not support date ranges, and will instead include data for the day requested and not a date set.
| Parameter | Required | Description |
|---|---|---|
| TransactionDate | Mandatory | Format: ISO 8601 | YYYY-MM-DD. Specify for which transaction date the report should be generated |
| PageNumber | Mandatory | Number of pages returned – Valid page number is 1 to N |
| PageSize | Mandatory | Page size is the number of items returned per page. Any positive number can be specified. Maximum allowed number is 5000, any specified value above 5000 will be reset to maximum value |
| PropertiesIncluded | Optional | Specify properties to include in the reconciliation report results. By default we will include all properties except for paymentId & lastChangedTimestamp that can be added to the array. See below table for all properties of the report. |
| PropertiesExcluded | Optional | Specifies properties to exclude from the reconciliation report results. By default we will include all properties. To get all available properties, send an empty exclude properties. |
| AccountId | Optional | Specify Id of the account on which activities have been booked. It is possible to send comma separated list of GUIDs. Format: comma (,) separated string |
| IncludeProperties | Deprecated | |
| ExcludeProperties | Deprecated |
Properties of reconciliation and paged reconciliation report
| Field Name | Field Type | Example | Description |
|---|---|---|---|
| pIdChannelUser | Varchar | [email protected] | User ID of the requesting client |
| pTxnDate | Date (YYYY-MM-DD; MM/dd/yyyy HH:mm:ss.fff) | 2021-10-27T00:00:00.000+00:00 | Internal date that indicates when payment was instructed in BC Connect. It does not match the processing date. |
| reportDate | Date (DDMMYYYY) | 29102021 | Date of when the report is generated |
| customerId | Number | 15106 | Customer ID of the account |
| account | Varchar | DKXXXX0000000XXXXX | IBAN of the account |
| accountCurrency | Text (3) | EUR | ISO currency of your account |
| debitAmount | Number, 2 decimals | 22.26 | The amount debited, it is only populated if DBIT. In case of reversal of a debit, the amount will be given as a negative equivalent of the original amount debited |
| creditAmount | Number, 2 decimals | 10.89 | The amount credited, it is only populated if CRDT. In case of reversal of a credit, the amount will be given as a negative equivalent of the original amount credited |
| valueDate | Date (YYYY-MM-DD;DD/MM/YYYY) | 27/10/2021 | Value date for posting on the account |
| instructedAmount | Number | 22.26 | Only for DBIT. Equivalent in original currency to the amount transferred after currency exchange. The instructed amount based on the choice of “payment currency” as provided in the payment instruction |
| instructedAmountCurrency | Text (3) | EUR | ISO currency of instruction. Contains the currency of the instructed amount as provided in the payment instruction |
| transactionAmount | Number | 18.78 | DBIT: actual amount transferred to beneficiary CRDT: actual amount received for credit to the beneficiary account |
| transactionAmountCurrency | Text (3) | GBP | ISO currency of transaction amount |
| exchangeRate | Number | 0.84367 | Currency exchange applied in the transaction, between the instructed amount and the transaction amount. |
| debtorBankCode | Varchar | SXPYDKKKXXX | Bank Code of the debtor, only populated for CRDT |
| debtorAccount | Varchar | DKXXXX0000000XXXXX | For CRDT: Account of debtor For DBIT: Blank For DBIT using VIBAN: VIBAN used for the transaction (DK representation) |
| debtorLine1 | Varchar | Debtor Name | For CRDT: Line 1 details for debtor For DBIT: Blank For DBIT using VIBAN: Name associated with Virtual account |
| debtorLine2 | Varchar | Debtor Address line 1 | For CRDT: Line 2 details for debtor For DBIT: Blank |
| debtorLine3 | Varchar | Debtor Address line 2 | For CRDT: Line 3 details for debtor For DBIT: Blank |
| debtorLine4 | Varchar | Debtor Address line 3 | For CRDT: Line 4 details for debtor For DBIT: Blank |
| beneficiaryBankCode | Varchar | SXPYDKKKXXX | Bank code of creditor, only populated for DBIT |
| beneficiaryAccount | Varchar | DKXXXX0000000XXXXX | For DBIT: Account of creditor For CRDT using VIBAN: DK IBAN representation of VIBAN used in the transaction. For CRDT: Blank |
| beneficiaryLine1 | Varchar | Creditor Name | For DBIT: Line 1 details for Beneficiary For CRDT using VIBAN: Name associated with virtual account. For CRDT: Blank |
| beneficiaryLine2 | Varchar | Creditor Address Line 1 | For DBIT: Line 2 details for Beneficiary For CRDT: Blank |
| beneficiaryLine3 | Varchar | Creditor Address Line 2 | For DBIT: Line 3 details for Beneficiary For CRDT: Blank |
| beneficiaryLine4 | Varchar | Creditor Address Line 3 | For DBIT: Line 4 details for Beneficiary For CRDT: Blank |
| paymentReferenceNumber | Varchar | 010F10xxxx001007 | BC Connect reference number, unique identifier for processed transactions |
| fileReferenceNumber | 36 CHAR a-Za-Z0-9/-?:.+ | 923578e7-7a58-4982-93f1-5862e687611d | Bulk id of instructed payment file, only populated for bulk payments |
| userReferenceNumber | a-Za-Z0-9/-?:.+ | Debtor reference | Provides the reference number of each payment record as stated in payment instruction by the debtor (DebtorReference). In case of internal transfers between BC Connect accounts, this field would be blank. |
| paymentDetails1 | Varchar | End to end id for beneficiary | Line 1 of payment details ( Remitter information line 1).Reference is propagated to beneficiary. |
| paymentDetails2 | Varchar | Info for beneficiary 2 | Line 2 of payment details |
| paymentDetails3 | Varchar | Info for beneficiary 3 | Line 3 of payment details |
| paymentDetails4 | Varchar | Info for beneficiary 4 | Line 4 of payment details |
| creditDebitIndicator | DBIT/CRDT | DBIT | CRDT for credit, DBIT for debit |
| bankTrnsCodeDomain | Varchar | PMNT | Bank transaction code for payment type identification. PMNT= Payment transfer FORX= foreign exchange ACMT=Account management: fees, interest, teller bookings |
| bankTrnsCodeFamily | Varchar | ICDT | Bank transaction sub-code for payment type identification. IDCT: Issued payment, sold leg FX or fee RCDT: Incoming payment, bought FX leg, reversal of payment fee |
Additional properties (only on request)
| Field Name | Field Type | Example | Description |
|---|---|---|---|
| bankTrnsCodeSubFamily | Varchar | DMCT | DMCT: Domestic Credit Transfer XBCT: Cross Border Credit Transfer RRTN: Reversal due to Payment Return |
| paymentId | 36 CHAR, Varchar | 4a1e5714-7ac3-9383-08a7-ffbadaa2f4bb | Unique payment reference within BC Connect that can be used to match the transaction instructed via API with the booking in the reconciliation report |
| lastChangedTimestamp | YYYY-MM-DDTHH:MM:SS.00000000+00:00 | 2019-11-08T11:40:25.00000000+00:00 | Indicates the time stamp of when a transaction was last changed in the processing system. |
| createdAt | YYYY-MM-DDTHH:MM:SS.00000000+00:00 | 2019-11-08T11:40:25.00000000+00:00 | Indicates the time stamp of when a transaction is created in the processing system. |
| additionalRemittanceInformation1 | Varchar | /RETN/ | Additional remittance information. In case of incoming return, it may contain a return indicator. |
| additionalRemittanceInformation2 | Varchar | /AC04/Closed Account Number | Additional remittance information. In case of incoming return, it may contain a return reason code. |
| additionalRemittanceInformation3 | Varchar | /MREF/010F10xxxx012345 | Additional remittance information. In case of incoming return, it may contain the original payment reference. |
| additionalRemittanceInformation4 | Varchar | //information | Additional remittance information |
| additionalRemittanceInformation5 | Varchar | //information | Additional remittance information |
| debtorAgentBankCode | Varchar | BICCIEXX | Bank Identifier Code (BIC) of the financial institution, where the debtor account resides |
| ultimateDebtorLine1 | Varchar | Name | For debits: Name of your customer For credits: Blank |
| ultimateDebtorLine2 | Varchar | Address line 1 | For debits: Your customer's address line 1 For credits: Blank |
| ultimateDebtorLine3 | Varchar | Address line 2 | For debits: Your customer's address line 2 For credits: Blank |
| ultimateDebtorLine4 | Varchar | Address line 3 | For debits: Your customer's address line 3 For credits: Blank |
| ultimateDebtorAccount | Varchar | IE29AIBK93115212345678 | For debits: The debtor account number (i.e., an account number issued by you). For credits: Blank |
| ultimateBeneficiaryAccount | Varchar | IE29AIBK93115212345678 | For debits: Blank For credits: The creditor account number (i.e., an account number issued by you) |
| statusReasonCode | Varchar | RR01 | Reason code in case of payment getting reversed |
| statusReasonDescription | Varchar | Insufficient sender account or unique identification | Reason description for payment reversal |
| instructedChargeBearer | Varchar | OUR | Charge bearer instructed by client |
| chargeBearer | Varchar | SHA | The actual charge bearer used for payment processing |
| paymentRail | Varchar | SEPA (SCT) | The payment rail used for executing the payment. You can read more about it here. |
| return | Text (4) | True | Possible values: "true" or "null". The value will be "true" if the payment is an incoming return payment, otherwise the value will be "null". This does not apply to incoming SEPA Direct Debits, where the value will always be "null. |
| directDebitMandateId | Varchar | 41b9a62baa5a2320247841ef865c1001 | directDebitMandateId is a unique identifier assigned to a mandate issued by creditors under the SEPA Direct Debit scheme, such as a customer number |
| processedTimestamp | YYYY-MM-DDTHH:MM:SS.00000000+00:00 | 2024-05-08T11:40:25.00000000+00:00 | Indicates the timestamp of when the payment is processed. |
| latestStatusChangedTimestamp | YYYY-MM-DDTHH:MM:SS.00000000+00:00 | 2024-05-08T11:40:25.00000000+00:00 | Indicates the timestamp of latest status change on the payment. |
| clientOrderId | 35 CHAR, Varchar | YourUniqueId | Your reference for your FX Trade executed via FX API. Note: Only displayed for FX trades. |
Paged intraday reconciliation report
The intraday reconciliation endpoint returns a report containing relevant information on all your payments at the given transaction date, within a specific time range indicating the time the transactions were created in our system. The report can contain one or all your accounts.
The report can be used to perform reconciliation of transactions during the day.
The last available report for transaction date= current calendar date will be at 19.00, following our EOD cycle. Kindly set the ToTransactionDate parameter for next business day, for obtaining payments received or executed after 19.00 CET.
Parameters in paged intraday reconciliation report request
| Parameter | Required | Description |
|---|---|---|
| FromTransactionDate | Mandatory | Format: ISO 8601 | YYYY-MM-DD. Specify from which transaction date the report should be generated. |
| ToTransactionDate | Mandatory | Format: ISO 8601 | YYYY-MM-DD. Specify until which transaction date the report should be generated. |
| FromCreatedAt | Mandatory | Format: ISO 8601| YYYY-MM-DDTHH:MM:SS.00000000+00:00. Indicates the time when a transaction was created in our system. This parameter specifies the created from & to time range the transaction should include in the report. |
| ToCreatedAt | Mandatory | Format: ISO 8601| YYYY-MM-DDTHH:MM:SS.00000000+00:00 Indicates the time when a transaction was created in our system. This parameter specifies until which time range transactions should be included in the report. |
| AccountId | Optional | Specify Id of the account on which activities have been booked. It is possible to send comma separated list of GUIDs. Format: comma (,) separated string |
| PageNumber | Mandatory | Integer, specify a valid page number (1 to n) |
| PageSize | Mandatory | Integer, any positive value may be specified however values above an internal maximum value will be reset to the max allowed value. |
| PropertiesIncluded | Optional | Specify properties to include in the reconciliation report results. By default we will include all properties except for paymentId & lastChangedTimestamp that can be added to the array. See below table for all properties of the report. |
| PropertiesExcluded | Optional | Specifies properties to exclude from the reconciliation report results. By default we will include all properties. To get all available properties, send an empty exclude properties. |
| IncludeProperties | Deprecated | |
| ExcludeProperties | Deprecated |
Properties of intraday reconciliation report
| Field Name | Field Type | Example | Description |
|---|---|---|---|
| P_ID_CHANNELUSER | Varchar | [email protected] | User ID of the requesting client |
| P_TXNDATE | Date (YYYY-MM-DD; MM/dd/yyyy HH:mm:ss.fff) | 2021-10-27T00:00:00.000+00:00 | Internal date that indicates when payment was instructed in BC Connect. It does not match the processing date. |
| REPORT_DATE | Date (DDMMYYYY) | 29102021 | Date of when the report is generated |
| CUSTOMERID | Number | 15106 | Customer ID of the account |
| ACCOUNT | Varchar | DKXXXX0000000XXXXX | IBAN of the account |
| ACCOUNT_CURRENCY | Text (3) | EUR | ISO currency of your account |
| DEBIT_AMOUNT | Number, 2 decimals | 22.26 | The amount debited, it is only populated if DBIT. In case of reversal of a debit, the amount will be given as a negative equivalent of the original amount debited |
| CREDIT_AMOUNT | Number, 2 decimals | 10.89 | The amount credited, it is only populated if CRDT. In case of reversal of a credit, the amount will be given as a negative equivalent of the original amount credited |
| VALUE_DATE | Date (YYYY-MM-DD;DD/MM/YYYY) | 27/10/2021 | Value date for posting on the account |
| INSTRUCTED_AMOUNT | Number | 22.26 | Only for DBIT. Equivalent in original currency to the amount transferred after currency exchange. The instructed amount based on the choice of “payment currency” as provided in the payment instruction |
| INSTRUCTED_AMOUNT_CURRENCY | Text (3) | EUR | ISO currency of instruction. Contains the currency of the instructed amount as provided in the payment instruction |
| TRANSACTION_AMOUNT | Number | 18.78 | DBIT: actual amount transferred to beneficiary CRDT: actual amount received for credit to the beneficiary account |
| TRANSACTION_AMOUNT_CURRENCY | Text (3) | GBP | ISO currency of transaction amount |
| EXCHANGE_RATE | Number | 0.84367 | Currency exchange applied in the transaction, between the instructed amount and the transaction amount. |
| DEBTOR_BANK_CODE | Varchar | SXPYDKKKXXX | Bank Code of the debtor, only populated for CRDT |
| DEBTOR_ACCOUNT | Varchar | DKXXXX0000000XXXXX | For CRDT: Account of debtor For DBIT: Blank For DBIT using VIBAN: VIBAN used for the transaction (DK representation) |
| DEBTOR_LINE_1 | Varchar | Debtor Name | For CRDT: Line 1 details for debtor For DBIT: Blank For DBIT using VIBAN: Name associated with Virtual account |
| DEBTOR_LINE_2 | Varchar | Debtor Address line 1 | For CRDT: Line 2 details for debtor For DBIT: Blank |
| DEBTOR_LINE_3 | Varchar | Debtor Address line 2 | For CRDT: Line 3 details for debtor For DBIT: Blank |
| DEBTOR_LINE_4 | Varchar | Debtor Address line 3 | For CRDT: Line 4 details for debtor For DBIT: Blank |
| BENEFICIARY_BANK_CODE | Varchar | SXPYDKKKXXX | Bank code of creditor, only populated for DBIT |
| BENEFICIARY_ACCOUNT | Varchar | DKXXXX0000000XXXXX | For DBIT: Account of creditor For CRDT using VIBAN: DK IBAN representation of VIBAN used in the transaction For CRDT: Blank |
| BENEFICIARY_LINE_1 | Varchar | Creditor Name | For DBIT: Line 1 details for Beneficiary For CRDT using VIBAN: Name associated with virtual account For CRDT: Blank |
| BENEFICIARY_LINE_2 | Varchar | Creditor Address Line 1 | For DBIT: Line 2 details for Beneficiary For CRDT: Blank |
| BENEFICIARY_LINE_3 | Varchar | Creditor Address Line 2 | For DBIT: Line 3 details for Beneficiary For CRDT: Blank |
| BENEFICIARY_LINE_4 | Varchar | Creditor Address Line 3 | For DBIT: Line 4 details for Beneficiary For CRDT: Blank |
| PAYMENT_REFERENCE_NUMBER | Varchar | 010F10xxxx001007 | BC Connect reference number, unique identifier for processed transactions |
| FILE_REFERENCE_NUMBER | 36 CHAR a-Za-Z0-9/-?:.+ | 923578e7-7a58-4982-93f1-5862e687611d | Bulk id of instructed payment file, only populated for bulk payments |
| USER_REFERENCE_NUMBER | a-Za-Z0-9/-?:.+ | Debtor reference | Provides the reference number of each payment record as stated in payment instruction by the debtor (DebtorReference). In case of internal transfers between BC Connect accounts, this field would be blank. |
| PAYMENT_DETAILS_1 | Varchar | End to end id for beneficiary | Line 1 of payment details ( Remitter information line 1).Reference is propagated to beneficiary. |
| PAYMENTDETAILS 2 | Varchar | Info for beneficiary 2 | Line 2 of payment details |
| PAYMENTDETAILS 3 | Varchar | Info for beneficiary 3 | Line 3 of payment details |
| PAYMENTDETAILS 4 | Varchar | Info for beneficiary 4 | Line 4 of payment details |
| CREDIT_DEBIT_INDICATOR | DBIT/CRDT | DBIT | CRDT for credit DBIT for debit |
| BANK_TRNS_CODE_DOMAIN | Varchar | PMNT | Bank transaction code for payment type identification. PMNT= Payment transfer FORX= foreign exchange ACMT=Account management: fees, interest, teller bookings |
| BANK_TRNS_CODE_FAMILY | Varchar | ICDT | Bank transaction sub-code for payment type identification. IDCT: Issued payment, sold FX leg or fee. RCDT: Incoming payment, bought FX leg, reversal of payment fee. |
| CREATED_AT | YYYY-MM-DDTHH:MM:SS.00000000+00:0 | 2019-11-08T11:40:25.00000000+00:00 | Indicates the time stamp of when a transaction was created in the processing system. |
Additional properties (only on request)
| Field Name | Field Type | Example | Description |
|---|---|---|---|
| Payment_Id | 36 CHAR, Varchar | 4a1e5714-7ac3-9383-08a7-ffbadaa2f4bb | Unique payment reference within BC Connect that can be used to match the transaction instructed via API with the booking in the reconciliation report |
| Lastchangedtimestamp | YYYY-MM-DDTHH:MM:SS.00000000+00:00 | 2019-11-08T11:40:25.00000000+00:00 | Indicates the time stamp of when a transaction was last changed in the processing system. |
| AdditionalRemittanceInformation1 | Varchar | /RETN/ | Additional remittance information. In case of incoming return, it may contain a return indicator. |
| AdditionalRemittanceInformation2 | Varchar | /AC04/Closed Account Number | Additional remittance information. In case of incoming return, it may contain a return reason code. |
| AdditionalRemittanceInformation3 | Varchar | /MREF/010F10xxxx012345 | Additional remittance information. In case of incoming return, it may contain the original payment reference. |
| AdditionalRemittanceInformation4 | Varchar | //information | Additional remittance information |
| AdditionalRemittanceInformation5 | Varchar | //information | Additional remittance information |
| BankTrnsCodeSubFamily | Varchar | DMCT | DMCT: Domestic Credit Transfer XBCT: Cross Border Credit Transfer RRTN: Reversal due to Payment Return |
| DebtorAgentBankCode | Varchar | BICCIEXX | Bank Identifier Code (BIC) of the financial institution, where the debtor account resides |
| UltimateDebtorLine1 | Varchar | Name | For debits: Name of your customer For credits: Blank |
| UltimateDebtorLine2 | Varchar | Address line 1 | For debits: Your customer's address line 1 For credits: Blank |
| UltimateDebtorLine3 | Varchar | Address line 2 | For debits: Your customer's address line 2 For credits: Blank |
| UltimateDebtorLine4 | Varchar | Address line 3 | For debits: Your customer's address line 3 For credits: Blank |
| UltimateDebtorAccount | Varchar | IE29AIBK93115212345678 | For debits: The debtor account number (i.e., an account number issued by you). For credits: Blank |
| UltimateBeneficiaryAccount | Varchar | IE29AIBK93115212345678 | For debits: Blank For credits: The creditor account number (i.e., an account number issued by you) |
| StatusReasonCode | Varchar | RR01 | Reason code in case of payment getting reversed |
| StatusReasonDescription | Varchar | Insufficient sender account or unique identification | Reason description for payment reversal |
| paymentRail | Varchar | SEPA (SCT) | The payment rail used for executing the payment. You can read more about it here . |
| return | Text (4) | True | Possible values: "true" or "null". The value will be "true" if the payment is an incoming return payment, otherwise the value will be "null". This does not apply to incoming SEPA Direct Debits, where the value will always be "null. |
| directDebitMandateId | Varchar | 41b9a62baa5a2320247841ef865c1001 | directDebitMandateId is a unique identifier assigned to a mandate issued by creditors under the SEPA Direct Debit scheme, such as a customer number |
| processedTimestamp | YYYY-MM-DDTHH:MM:SS.00000000+00:00 | 2024-05-08T11:40:25.00000000+00:00 | Indicates the timestamp of when the payment is processed. |
| latestStatusChangedTimestamp | YYYY-MM-DDTHH:MM:SS.00000000+00:00 | 2024-05-08T11:40:25.00000000+00:00 | Indicates the timestamp of latest status change on the payment. |
| clientOrderId | 35 CHAR, Varchar | YourUniqueId | Your reference for your FX Trade executed via FX API. Note: Only displayed for FX trades. |
Updated 2 months ago