File Reconciliation

Used to ensure that redemption and reverse transactions match in both the Partner’s and Blue Rewards systems.

A reconciliation process has been established to ensure that successful redemption and reverse transactions match in both the partner’s systems and Blue Rewards 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 two header records, zero or more detail records, and two trailer records.

Reconciliation files are uncompressed and nonencrypted text files fololowing a specific format (described below). 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 double percentage characters (%%) there is no need for fillers or padding. For example, the Card Terminal ID is defined as Alphanumeric (16), and for sending the value of 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 Blue Rewards 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 detail 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 detail records in the file - likewise, the EndExtractDate is the latest time boundary for the detail records. This means that the local transaction date & time of all the detail 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	Points Amount	Numeric (4)	Amount (in Blue Rewards Pointss) 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).