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.

Note

All examples in the following sections are based on regular runs containing data for a standard 24-hour period, with cut-off at 12:00AM EST.

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.

Tip

The YYYYMMDD value should represent the file creation date (e.g., 20200625 for June 25, 2020). These files are transferred from AIR MILES after 2:00PM EST.

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

Note

The BeginExtractDate in the header and trailer records is the earliest time boundary for all detailed records in the file - likewise, the EndExtractDate is the latest time boundary for the detailed records. This means that the local transaction date & time of all the detailed records fall within the BeginExtractDate and EndExtractDate date/time frames.

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).