File Reconciliation

Used to ensure “Redemption” and “Reverse” transactions match in both the Partner’s and AIR MILES systems.

A Reconciliation process has been established to ensure that successful “Redemption” and “Reverse” transactions match in both the partner’s systems and AIR MILES systems. This matching ensures that the number of transactions in both systems and the transaction values (e.g., amounts, dates) are identical.

What are reconciliation files

The Reconciliation files are sent to the Partner at regular intervals, and contain all successful Redemptions and Reversals that were reconciled on a given day.

The following sections describe the Reconciliation process, files, formats, and file transfer methods.

File formats and schedules

Each Reconciliation file contains the following:

  • Header Records x2
  • Trailer Records x2
  • Detailed Records 0 or more

Reconciliation files are text-based and have a specific format (described below), use delimiters to separate fields, and are uncompressed & un-encrypted. Records within the reconciliation file use the standard ASCII character set, where fields within a record are separated by double percent characters (%%), and records are separated by a new line character at the end of each line.

Since fields in the file records are delimited with percentage characters (%%) there is no need for fillers or padding. As an example, the “amount” field is defined as Numeric and 4 characters in length, so in order to send 190 Miles, it is enough to send just those three digits (i.e., there is no need to send 0190). Similarly, the “Card Terminal ID” is defined as Alphanumeric and 16 characters in length, so in order to send the characters SPON000101 there is no need to append filler spaces at the end of those characters.

During normal processing times, reconciliation files will be created at regular intervals. For resubmissions, the files may contain more (or less) than 24-hours of transactions, and may include a different time range. However, in all instances the “Local Transaction Date/Time” of all records within the file must be between the BeginExtractDate and EndExtractDate values in the “Header” and “Trailer” records (see an example of the file format below).

Files sent to the partner

The naming of the reconciliation file uses the following format:

RECON_LMG_YYYYMMDD.txt

As an example, a reconciliation file created on July 19, 2020 would be named RECON_LMG_20200719.txt.

Sample file contents

H%%20100718070925%%20100718221523
S%%95001%%20100718070925%%20100718221523
D%%2210%%80000000001%%190%%358%%162824%%20100718%%39000000883%%SPON000101%%20.00
D%%2210%%80000000001%%190%%344%%070925%%20100718%%39000000842%%SPON001101%%20.00
D%%2210%%80000000001%%190%%355%%221523%%20100718%%39000000750%%SPON000121%%20.00
D%%2430%%80000000001%%190%%359%%162940%%20100718%%39000000883%%SPON000105%%20.00
D%%2210%%80000000001%%190%%2909%%083455%%20100718%%39000000644%%SPON000106%%20.00
D%%2430%%80000000001%%190%%2912%%143448%%20100718%%39000000636%%SPON000106%%20.00
D%%2210%%80000000001%%190%%341%%090658%%20100718%%39000000693%%SPON000101%%20.00
D%%2430%%80000000001%%190%%345%%151102%%20100718%%39000000842%%SPON000101%%20.00
D%%2430%%80000000001%%190%%2911%%143455%%20100718%%39000000644%%SPON000101%%20.00
D%%2210%%80000000001%%95%%327%%074551%%20100718%%39000000677%%SPON000111%%10.00
F%%95001%%20100718070925%%20100718221523%%10
T%%20100718070925%%20100718221523%%12

Header and trailer record templates

As mentioned above, the reconciliation files contain two (2) header records, two (2) trailer records, and zero (0) or more detailed records.

Header 1
H%%BeginExtractDate%%EndExtractDate
Header 2
S%%CardAcceptorId%%BeginExtractDate%%EndExtractDate
Trailer 1
F%%CardAcceptorId%%BeginExtractDate%%EndExtractDate%%NumOfDetailRowsInFile
Trailer 2
T%%BeginExtractDate%%EndExtractDate%%NumOfDetailRowsInFile+2

Reconciliation file fields

# Field Type (length) Description
1 Row Type Alpha (1) Always ‘D’
2 MTI Numeric (4) The values are 2210 for redemptions and 2430 for reversals
3 Collector Number Numeric (11) The Collector number
4 Miles Amount Numeric (4) Amount (in AIR MILES Reward Miles) of the redemption or reversal
5 System Trace Number Numeric (12) Partner issued unique tracking number
6 Local Transaction Time Numeric (6) Format: HH24MISS (the time component of Local Date/Time)
7 Local Transaction Date Numeric (8) Format: YYYYMMDD (the date component of Local Date/Time)
8 Transaction Confirmation Number Numeric (12) The unique identification number included in any “redemption” or “reversal” response message
9 Card Terminal Id Alphanumeric (16) Concatenation of sponsorCode, storeId, and deviceId
10 Dollar Amount Numeric (6, 2) Format: NNNN.NN (no left padding - the amount in dollars of the redemption or reversal)

File transmission method

The accepted transmission method for receiving the Reconciliation file is via SFTP (Simple File-Transfer-Protocol).

Contact us for more details at partnersupport@loyalty.com.