Reconciliation Pattern

Description of a Problem:

Two systems are changing similar data and this data needs to be compared using Rules and Reconciled.

Approach:

Three types of flow to solve problem:

• Extracting data from the Systems and storing into temporary database tables
• Rules to compare Data
• Actions and Automated Flows to fix differences

After the first process extracts Data into temporary tables, Rules are run to find variances.  Automated and User based interactions are used to fix data problems in systems. 

Using a “Leasing” pattern, multiple threads and Servers can be used to process these items in parallel allowing high speed of the process.

Flows can be Scheduled using the Scheduling Engine.

Variations:

Data may be gathered in two ways:

• Fetched into temporary tables.
• If System sends out Events when Data is changed, it can be gathered in temporary tables incrementally.

References:

• Create Data Structures
• Scheduled Jobs (optional)
• Entity Leasing
• Auto Queue Batch Insert
• Rule Sets
• Assigned Form (optional)
• Worker Queues

Detailed Approach:

In this example we are going to demonstrate Account Reconciliation process between Sales Force system and Generic Billing Company System.

Defining Types:

In this process we have Sales Force Account Type and Billing Company Account Type. These types are similar.

In Decisions we are going to create several Data Types for the Reconciliation process.

Sales Force Account Data: may be created as Database Structure User Defined Type. This Data Type should have a Primary Key referred to an Account ID in Sales Force System along with other attributes from Sales Force System that needed in this process.

Billing Company Account Data:  may be created as Database Structure [Leased] User Defined Type. This Data Type should have a Primary Key referred to an Account ID in Billing Company System along with other attributes from Billing Company System that needed in this process.

Variance: may be created as Database Structure User Defined Type. This Data Type should have a Primary Key referred to an Account ID that will be changed in Billing Company System. Next, this Data Type should include Sales Force Field, Billing Company Field, Difference, Billing Company Value and Sales Force Value.

Variance Change Log: defined in the same manner as Variance Data Type. This Data will be persistent in the process. It extends Variance Data Type with Action Taken and User Driven attributes.

Extraction Processes:

Both Sales Force and Billing Company may be in Push or Pull mode.

For each system there is a Loader Flow in Decisions to fetch data.

Sales Force Loader Flow:

Maps Account data into Sales Force Account Data object in Decisions.

If communication with Sales Force system is in Pull mode, then Truncate temporary Sales Force account table and Auto Queue Batch Insert into temporary Sales Force account table.

If communication with Sales Force system is in Push mode, then Insert or Update into temporary Sales Force account table.

Billing Company Loader Flow:

Maps Account data into Billing Company Account Data in Decisions.

If communication with Billing Company system is in Pull mode, then Truncate temporary Billing Company account table and Auto Queue Batch Insert into temporary Billing Company account table.

If communication with Billing Company system is in Push mode, then Insert or Update into temporary Billing Company account table.

While extraction process is not finished, Reconciliation Process Leasing Billing Company Entity so multiple threads and Servers can be used to process these items in parallel. In this example we are Leasing Billing Company Entity because it is Master System and Sales Force is Dependent. By using Leased Entity we prevent memory overflow compared to the logic where System has to work the whole collection of Accounts at one time. After Leased Entity is processed, System Releases it or Deletes freeing up memory.

After extraction process is Done, Reconciliation Process clears up Variance temporary table and proceeds to Comparison process.

Comparison:

If Sales Force System Account Data table has an account that does not match any Billing Company Account Data, Reconciliation process Handles Missing Account by deleting this Account from Sales Force System.

If Sales Force System Account Data Table does not have an Account that is in Billing Company Account Data System, Reconciliation process Handles Missing Account by adding it to Sales Force System.

If Matching Sales Force Account is found, Reconciliation process runs a Set of Rules to determine differences between Accounts with same ID in both Systems, and records these differences in Variance temporary table using Batch Insert Auto Queue.

Reconciliation (Automated):

Handling differences between Accounts in two Systems may be Automated by reading Data from Variance temporary table and running a Set of Rules against this Data. Then, changes are stored in Variance Change Log, and Billing Company Account Data and Sales Force Account Data Entities are being cleared.

Reconciliation (User Based):

After changes between Accounts in two Systems are stored in Variance temporary table, Reconciliation process may Assign a Task for responsible person to review these changes and make appropriate Actions of Reconciliation. Then, the process records these changes in Variance Change Log persistent table along with User Based Actions taken. Next, process deletes Billing Company Account Data and Sales Force Account Data Entities.

Additional Resources

• Integrating Decisions with External Oracle Database