File Reconciliation
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
TheYYYYMMDD
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
TheBeginExtractDate
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).
Contact us for more details at partnersupport@loyalty.com.