This topic helps you understand how the Transaction Matching engine evaluates transactions. The examples assumes the following transactions in Source System data source and Sub System data source.
Source System "Invoice" exactly matches Sub System invoice
Source System matches within a -1 to +3 tolerance of Sub System Date
Source System "Amount" exactly matches Sub System amount
The Source System and Sub System "Amount" attribute is the Balancing Attribute.
Order of Evaluation / "First One Matching Wins"
Transactions are evaluated for matching in the order in which the transactions exist in the database, and the first transaction evaluated for the match that satisfies the conditions will be selected for the match.
Order of Evaluation / Lowest Transaction ID Matches First
When transactions are loaded, they are assigned a Transaction ID sequentially, therefore, the matching transaction with the lowest Transaction ID means it was loaded first and will be first to match.
In a 1 to 1 match, if two transactions exist that qualify as a match with a third transaction, but only one can be matched, the transaction with the lowest Transaction ID will be the one selected as the match.
Note that if you ran the same rule with No Ambiguous selected, all transactions will remain unmatched (as expected).
There are three ways to set tolerance levels to allow matching of transactions that have variances. You can set:
- a tolerance variance value that is applied to the transactions. For example, auto match with up to a variance of -1 and +3.
- a tolerance threshold level expressed as a percentage. For example, auto match with up to 0.5% of the value of the amount.
- a tolerance threshold level expressed as a percentage along with a variance amount. For example, auto match with up to 1.0% of the value of the amount up to a maximum amount of 100.00.
Note:The percentage value can not be more than 100 for both high and low tolerance and can be used for Number or Integer data source attributes.
Evaluating Tolerances Set as Values
When evaluating tolerances that are a set tolerance value, the calculation is impacted by how the high/low tolerance values are applied to transactions. For example, in the sample transactions where we apply a tolerance to the Date values, we have an authorized tolerance of -1 and +3. If you apply these values to the first record in the Source System table, then the first record in the Sub System table satisfies the match rule because September 18 is less than or equal to +3 days from September 15. However, if we apply the tolerances to the Sub System transaction (rather than the Source System), the match fails since September 15 is not less than or equal to -1 days from September 18.
While the above example uses Date tolerances, the same logic applies to tolerances on Date, Time and Integer data types.
To ensure consistent results, we opted to use the following approach when evaluating tolerances:
Evaluating 1 to 1 Rules Types
With 1 to 1 rule types, we treat the Source transactions as the "base or anchoring transactions". This example shows the way in which the evaluation takes place. In this example, we are assuming a Date tolerance Start set to zero (0) and End was set at +3.
- Starting with the first Source transaction, the tolerance limits are added to the transaction’s Date value to derive the acceptable range of Date values from Sub System.
- Then, the first transaction from Sub System is selected that satisfies the match. In our example, the Source date value for the first transaction is September 15.
Adding the 0 to +3 tolerance to the Sub System transaction, the first transaction from Source System with a Date value greater than or equal to September 15 and less than or equal to September 18 is selected as a match. This match is identified in green below:
Continuing to the second Source transaction (with a Date value of September 16), the first transaction from Sub System with a Date greater than or equal to September 16 and less than or equal to September 19 is found. This match of September 17 is within the range and identified in red below:
- Continuing with the third Source System transaction (with a Date value of September 17), assume we need to find the first transaction from Source System with a Date greater than or equal to September 16 and less than or equal to September 20. Since no transaction from Source System satisfies this condition, no match is created.
Note:In the case of single source match processes, Positive transactions match to Negative transactions.
Evaluating 1 to Many Rule Types
With 1 to Many rule types, Source System transactions are the base transactions.
Note:For 1 to Many rule types, you must define one rule condition which can be Match Exactly or with tolerance in addition to the Balancing Attribute.
Evaluating Many to 1 Rule Types
With Many to 1 rule types, Sub System transactions are the base transactions.
Note:For Many to 1 rule types, you must define one rule condition which can be Match Exactly or with tolerance in addition to the Balancing attribute condition.
Evaluating Many to Many Rule Types
With Many to Many rule types, there is no base transaction. Rather, the conditions are evaluated using the following method:
Find the minimum and maximum values from the complete dataset, considering transactions in both Source System and Sub System; In our example, those values are:
Minimum: September 14
Maximum: September 18
Then, using the Tolerance Low and Tolerance High values, calculate the allowed "span" (the range between the minimum and maximum values); In our example, the span is 4 (0 to +3)
Lastly, evaluate whether the difference between the minimum and maximum values is less than or equal to the span value. In our case, the condition is not satisfied since the difference between September 14 and September 18 is 5, and the allowed span is 4.
Note:For Many to Many rules, you must define at least one Match Exactly condition.
Evaluating Match Rule Conditions for a Balancing AttributeThe method for evaluating match rule conditions for a Balancing Attribute is slightly different than for other attributes. Consider a Many to Many rule type using the sample data. Here's how the system evaluates this:
- The system will first select all transactions with matching Invoice numbers (rule #1).
- The Date attributes will then be evaluated to see if they satisfy the rule condition (rule #2).
- Lastly, it will evaluate whether the Source System Amount matches exactly to the Sub System Amount (rule #3).
Since it’s a Many to Many match with multiple transactions in Source System and Sub System, the Amount values must first be summed by Data Source, and then the sum totals compared.
In our example, the sum of Source System Amount is $500 and the sum of Sub System Amount is also $500, so the match conditions are satisfied:
Note:The same process applies to 1 to Many matches and Many to 1 matches. Any time multiple transactions of the same data source exist for a match, the values of Balancing Attributes must first be summed before the comparison occurs.
Evaluating Tolerances Set as a Percentage and Variance Limit
The percentage tolerance option is available for Number and Integer type data source attributes (including balancing attribute) and can be set for:
- Auto match rules
- Manual match rule
- Default attribute mapping
- Matching rule has 1% low and 1% high tolerance
- The tolerance on Up To Amount is set as .5
|Source System Amount||Sub System Amount||Calculated Tolerance||Variance (Source - Sub System Amt)||Match Calculation||Result|
|100||99.6||1% of 99.6 = .99||100 - 99.6 = .4||.4 < .99 and < .5||YES Match found|
|100||99.1||1% of 99.1 = .99||100 - 99.1 = 0.9||.9 < .99 but > .5||NO Match found since tolerance variance Up To limit was exceeded|
|99.6||100||1% of 100 = 1||99.6 - 100 = .4||.4 < 1 and < .5||YES Match Found|
|99.1||100||1% of 100 = 1||99.1 - 100 = .9||.9 < 1 and > .5||NO Match found since tolerance variance Up To limit was exceeded|
|50, 50||50, 49.6||1% of 99.6 = .99||100 - 99.6 = .4||.4 < .99 and < .5||YES Match Found|
Evaluating Date Tolerances for 1 to 1 and 1 to Many Rules
The date tolerance is the number of days to consider for matching of transactions between two data sources. Transaction Matching considers date tolerance in the most common way of matching transactions, treating the Source (in the GL for example), as available "before" the Bank deposits or Sub System transactions. The engine anchors on the Source and calculates the range of date values to consider for matching forward from there based on the tolerance values entered in the rule.
The Date tolerance option is available for Date type data source attributes and can be set for:
- Auto match rules
- Manual match rule
- Default attribute mapping
Date tolerance is very flexible and you can use it to specify matching using a:
Range of days
Use the date tolerance in rules to establish a range of days to consider for the matching. In rule creation, these fields are labelled Start and End to indicate the range. The engine provides a lot of flexibility for working with the date tolerance range. Let's say you want to consider transactions for matching between today and one day later. That would be Start and End date of 0 to 1. Another example is to start matching transactions two days from the Source transactions and ending 4 days out. You can also consider one day back (start as -1) to 1 day forward by entering a Start as -1 and End value of +1.
Additional precision for the date tolerance is offered by being able to match transactions from one source to transactions in the Sub System on another specific day instead of a range of days. This is accomplished by using the date tolerance values in the Start and End fields as the same value. For example, let's say that you only want to match your Source transactions with the Bank transactions 2 days out. You can enter 2 in the Start field and 2 in the End field to only consider exactly that day for matching.
Business Calendar instead of a regular
calendar in order to eliminate holidays and
weekends for consideration in matching.
By default, Account Reconciliation uses a regular calendar to specify the dates in a date range. You also have the option of using a business calendar when calculating date range in order to easily eliminate counting days that are work holidays for a company or weekends in calculation of the range. The Use Business Calendar check box is available as an option when creating Date Type attribute rule conditions with tolerance and also for match process default attribute mapping. The business calendar option is not available for manual matching.
Note:To use a business calendar, you must specify the work days for your organizational unit as well as a holiday rule containing the list of holidays. The organizational units are assigned to profiles and the associated business calendar is then used during creation of reconciliations. See Defining Organizational Units in the Setting Up and Configuring Account Reconciliation guide.
Here is an example of a rule that has date
tolerance specified as well as showing the
business calendar checkbox.