Detailed Settlement Report

The detailed settlement report lists every item settled on a settlement date, one row per item. It is built from the network's clearing data, so it is the file to use to mark your authorized transactions as settled, to confirm the totals of the summary settlement, and to see the interchange treatment the network gave each transaction.

The file is a CSV with a header row. The column names below are the exact header names, in file order.

How to read the file

• Join a row to your authorization on two keys. wallet_reference identifies the card and transaction_id identifies the transaction within that card. transaction_id alone is not unique across cards.
• Amounts are unsigned. transaction_description tells you whether the row debits or credits the cardholder.
• Empty is not zero. A column that arrives empty has not been populated by the processor for that cycle. Treat it as unknown, not as 0.
• The period is the settlement date. Assign a row to a day or a month by transaction_date, not by the day you received the file.

Columns

• transaction_date (Date, YYYY-MM-DD). Settlement date of the transaction.
• transaction_amount_settlement_amount (Decimal, 2 places). Amount settled by the network, in settlement_currency. In currency units: 348.00 is MXN 348.00. Unsigned; see transaction_description for the direction.
• transaction_amount_cardholder_currency (Decimal). The same amount in the cardholder's currency, cardholder_currency. Equal to the settlement amount while both currencies are MXN.
• transaction_narrative (Text). Merchant descriptor as presented by the acquirer: merchant name, street, city, postal code and country, separated by backslashes. This is the text the cardholder sees.
• transaction_description (Text). What the row settles: DEDUCT, LOAD, REVERSAL, LOAD REVERSAL, 2ND PRESENTMENT, 2ND PRESENTMENT REVERSAL or CHARGEBACK. See the value table below.
• transaction_id (Text). Processor reference of the settled item. On a DEDUCT it is the same transaction_id you received in the authorization request, 1 to 6 digits, unique per card only. On a LOAD (a refund) it is a 23-digit network reference with no relation to any purchase; it equals the reference_id of the LoadAdjustment request you received for that refund. Chargebacks and second presentments carry their own 23-digit reference.
• network_transaction_id (Text). Mastercard trace identifier of the transaction, 13 characters, assigned at authorization. It is the same value delivered in the transaction data of the authorization request (trace_id in the JSON format, KLV key 063 in the XML-RPC format) and it stays constant across the authorization, any incremental authorizations and clearing. Use it to tie pre-authorizations and incremental authorizations to their settlement.
• transaction_type (Integer). Channel of the original transaction: 0 point of sale, 1 ATM, 2 adjustment. Adjustments are rows that did not originate as a purchase or withdrawal authorization: refunds, reversals, second presentments and chargebacks.
• wallet_reference (Text). Reference of the card. It is the reference value returned when the card was created or activated and sent in every authorization request for that card.
• system_date (Timestamp). Date and time the processor recorded the settlement, processor system time (UTC+2).
• sequence_number (Integer). Running number of the card within your programme, assigned by the processor when the card was created. It is also the last digits of tracking_number.
• tracking_number (Text, 15 digits). Processor identifier of the card, constant for the life of the card. It is the tracking_number you see in card operations such as Stop.
• settlement_currency (Integer). ISO 4217 numeric code of the settlement currency. 484 is MXN.
• cardholder_currency (Integer). ISO 4217 numeric code of the card's currency. 484 is MXN.
• interchange_amount (Decimal, 2 places). Interchange fee of the transaction, in MXN, as the net effect on you: positive when the fee is earned (a DEDUCT), negative when it is paid back (a LOAD or a reversal). Some debits, such as ATM withdrawals abroad, also carry a negative fee. 0.00 when the transaction generates no fee, which is most rows. Fed from Mastercard clearing data.
• acquirer_reference_number (Text, 23 digits). Acquirer Reference Data (DE 31): the reference the acquirer assigned to the presentment. It is the identifier used in retrieval requests, chargebacks and disputes.
• originating_message_format (Integer). Format in which the clearing system received the original presentment (Mastercard PDS 0191). 0: received in Mexico Domestic Format, that is, switched through Prosa or E-Global. 2: received in Mastercard's IPM format. It is not a domestic-versus-international flag. A Mexican merchant whose acquirer clears through Mastercard carries 2, and both values exist for domestic transactions. To tell domestic from international, see the note below.
• local_amount (Decimal, 2 places). Amount as presented by the acquirer, in local_currency (DE 4). For a domestic transaction it equals the settlement amount.
• local_currency (Integer). ISO 4217 numeric code of local_amount (DE 49).
• transaction_authorisation_number (Text). Approval code the network returned at authorization (DE 38). It is the authorization code printed on the cardholder's receipt.
• interchange_vat_amount (Decimal, 2 places). VAT the network applied to the interchange fee of the transaction (PDS 0297). It is 16% of interchange_amount when the transaction settled under a Mexican national settlement service and 0.00 otherwise, and it follows the sign of the fee. This is network information; it is not the VAT of your invoice.
• settlement_service_id (Text). Mastercard settlement service under which the transaction cleared, for example LAMEX48421 or LA00048411. Populated when the transaction was processed under a Mexican national settlement service; empty for foreign acquirers and always empty on ATM.
• reconciliation_cycle (Integer). Reconciliation cycle of the clearing file the transaction travelled in. Traces the row to the exact network cycle.
• settlement_cycle (Integer). Settlement cycle of the day. With reconciliation_cycle it orders the items of one settlement date.

Values of transaction_description

Domestic or international

originating_message_format does not answer this on its own. Use the channel:

• Point of sale and adjustments: read settlement_service_id. Populated means the transaction settled under a Mexican national settlement service; empty means a foreign acquirer.
• ATM: settlement_service_id is always empty, so read originating_message_format. Mexican ATMs clear in Mexico Domestic Format, so 0 is a domestic withdrawal and 2 a withdrawal abroad.

Do not infer it from the amounts or currencies, because some international acquirers settle in pesos, and do not infer it from interchange_vat_amount, which is 0.00 on any transaction that generates no fee, domestic or not.