Matching Reconciliation Transactions from Two Input Sources

In some instances of reconciliation processing, multiple transactions can have the same key combinations. For example, ATM transactions posted to your account may include the account number, ATM terminal number, and date as the key. Multiple transactions on the same day would have the same key and you want to make sure that each transaction posts individually.

Typical ACR/Detail processing combines transactions with the same key. You want to verify that you have the correct number of transactions, so you do not want to combine the transactions when you match them to the general ledger.

Problem

You need to match data between input sources where there are multiple occurrences of the same key data that needs to be matched individually.

Solution

You need to write rules to determine if the key values are the same from one record to the next in each input file. If the key values are the same, increment a tally that starts with 1 to keep each occurrence separate. If the key values are different, reset the tally back to 1. This solution will match items in order of appearance, so if an item in the middle of a sequence is missing, all subsequent items will not match. This solution does not indicate which item is the actual item that did not match, but it narrows the field of mismatches and starts the errors with the missing item.

Putting Items in Order

This example starts numbering each key with a 1, so that transaction 1 for your ATM key on your first input source can be matched to transaction 1 for your ATM key on your second input source. To begin the numbers with 1 for each input source, the data in the input sources must have the keys sorted together.

If the keys are not together in your input source, you will need to put them together on the file, so that each transaction within the keys can be numbered in sequential order, beginning with 1.

Complete the steps below to run an ACR/Detail job, for each input source, to put the keys in the correct order. If your keys are already together on your file, proceed to the "Matching the Items" section, below.

1 ) Add +1 to an extraction variable named MyInitialTallyEV.

2 ) Extract the ATM key fields and also use MyInitialTallyEV as a key. For this example, you need to extract the following key values :

- Account Number
- ATM Terminal Number
- Date
- MyInitialTallyEV

3 ) Extract your remaining record in internal or extended internal items, as needed, to maintain your input values. For example,

Move pos 1 to 80 to X-001
Move pos 81 to 160 to X-002

4 ) Create a free-form output file that has your initial record value items as the outputs, such as X-001, and then X-002.

See “Setting Up an Output File” in the ACR/Detail User Guide.

The free-form output file will then replace your input source as the input to the actual reconciliation step. The transactions are the same as the original transactions, except that they are in order by the keys that you specified, Account Number, ATM Terminal Number, and Date.

Matching the Items

After you have determined that the items are in order, set up extraction definitions for selection groups in the following order :

1 ) Set up the first selection group to see if values in extraction variables are equal to values of the key fields on the input source.

SELECT if pos 1 for 8 = MyLastAcctNumEV AND
SELECT if pos 9 for 8 = MyLastATMTermEV AND
SELECT if pos 18 for 8 = MyLastDateEV
Move literal of “MATCH” to MyEVIndicator
Add +1 to MyEVTally

If the values in the first selection group match, place a literal value of MATCH into an extraction variable, MyEVIndicator. This extraction variable is used to keep track of the match. Adding 1 to MyEVTally creates a unique key for transactions that would otherwise have the same key.

2 ) Set up the second selection group to see if values in extraction variables are not equal to values of the key fields on the input source.

SELECT if pos 1 for 8 NOT = MyLastAcctNumEV OR
SELECT if pos 9 for 8 NOT = MyLastATMTermEV OR
SELECT if pos 18 for 8 NOT = MyLastDateEV
Move literal of “NONMATCH” to MyEVIndicator
Move pos 1 for 8 to MyLastAcctNumEV
Move pos 9 for 8 to MyLastATMTermEV
Move pos 18 for 8 to MyLastDateEV
Move literal of +1 to MyEVTally

If the values in the first selection group do not match, place the key values into extraction variables and a literal value of NONMATCH into the extraction variable, MyEVIndicator. Moving 1 to MyEVTally creates a unique key and starts a new sequence for transactions that would otherwise have the same key. Identifying mismatches between input sources is easier because each key sequence starts at 1.

3 ) Set up a selection group to extract your data, using the MyEVTally item to pick up the keys and internal items.

SELECT if pos 1 for 3 = ATM

where ATM is your business selection criteria

Move MyLastAcctNumEV to Key1
Move MyLastATMTermEV to Key2
Move MyLastATMTermEV to Key3
Move MyEVTally to Key4
Move pos 32 for 15 into Internal Item 1

where 32 for 15 is the data that you need to extract and Internal Item 1 is the location where you want to place the extracted data.