Overview

This technical guide accompanies the Bank Reconciliation RPA Project, providing implementation details for developers building similar solutions.

---

Architecture: Dispatcher-Performer Pattern

Why Separate Processes?

Concern	Dispatcher	Performer
I/O Type	Heavy (OCR, File Read)	Light (Queue Get)
CPU Usage	Low	High (SQL Queries)
Failure Impact	Isolated to parsing	Isolated to single transaction
Scaling	1 robot sufficient	Multiple robots parallel

System Flow

flowchart LR
    A[PDF Bank Statement] --> B[Dispatcher]
    B --> C{OCR + Regex}
    C --> D[Orchestrator Queue]
    D --> E[Performer 1]
    D --> F[Performer 2]
    D --> G[Performer N]
    E --> H[(SQL Server)]
    F --> H
    G --> H
    H --> I[Reconciliation_Log]

---

Dispatcher Implementation

OCR Text Extraction

' UiPath OCR Activity Output → str_OcrText
' Key Challenge: Column misalignment in PDF extraction

Regex Patterns for Transaction Parsing

' Pattern 1: Complete first line (inline format)
Dim firstLinePattern As String = "^(\d{2}-\d{2})\s+(REF\d{6})\s+.+?INV:\s*(INV-[A-Z0-9]+)"

' Pattern 2: Standalone dates
Dim datePattern As String = "^\d{2}-\d{2}$"

' Pattern 3: Description lines with embedded REF and INV
Dim descPattern As String = "^Preauthorized.+?(REF\d{6}).+?INV:\s*(INV-[A-Z0-9]+)"

' Pattern 4: Amount extraction (handles "Amount -$3,064.00" labels)
Dim amtPattern As String = "-?\$?([\d,]+\.\d{2})$"

Queue Item Structure

{
  "Date": "01-03",
  "BankRef": "REF001059",
  "Description": "Preauthorized Wd GOOGL ADS PYMT",
  "InvoiceNo": "INV-MBW401",
  "Amount": "11294.00",
  "Type": "DEBIT"
}

---

Performer Implementation

REFramework Configuration (Config.xlsx)

Name	Value	Description
OrchestratorQueueName	Bank_Recon_Queue	Queue for transactions
MaxRetryNumber	3	Retry attempts for system exceptions
Tolerance_Amount	30	Amount matching tolerance ($)
Tolerance_Days	3	Date matching tolerance (days)
Report_Email_To	finance@company.com	Exception notification recipient

Transaction Processing Flow

flowchart TD
    A[Get Queue Item] --> B{Is Credit Transaction?}
    
    B -->|Yes - AR| C[Query ERP_Receivables]
    B -->|No - AP| D[Query ERP_Ledger]
    
    C --> E{Record Found?}
    D --> E
    
    E -->|No| F[BusinessRuleException]
    E -->|Yes| G{Switch: Payment Type}
    
    G -->|BATCH| H[Sum Batch Group]
    G -->|SPLIT| I[Sum Previous Payments]
    G -->|Default| J[Calculate Variance]
    
    H --> K{Total = Bank Amount?}
    K -->|Yes| L["Matched - Batch"]
    K -->|No| M[Mismatch]
    
    I --> N{Cumulative >= ERP?}
    N -->|Yes| O["Matched - Split"]
    N -->|No| P[Partial]
    
    J --> Q{Variance = 0?}
    Q -->|Yes| R[Exact Match]
    Q -->|No| S{Variance ≤ Tolerance?}
    S -->|Yes| T[Fuzzy Match]
    S -->|No| U[Mismatch]
    
    R --> V[INSERT to Reconciliation_Log]
    T --> V
    U --> V
    L --> V
    O --> V
    P --> V

---

SQL Queries

Exact Match Query (AP)

SELECT TOP 1 Invoice_No, Vendor_Name, Amount, Posting_Date, 
       Payment_Type, Batch_Group
FROM ERP_Ledger 
WHERE Invoice_No = @InvoiceNo
  AND Reconciliation_Status = 'Pending'

Exact Match Query (AR)

SELECT TOP 1 Invoice_No, Customer_Name, Amount, Posting_Date, 
       Deposit_Type, Batch_Group
FROM ERP_Receivables 
WHERE Invoice_No = @InvoiceNo
  AND Reconciliation_Status = 'Pending'

BATCH Payment Query

SELECT ERP_ID, Invoice_No, Amount 
FROM ERP_Ledger 
WHERE Batch_Group = @BatchGroup
  AND Reconciliation_Status = 'Pending'

SPLIT Payment - Cumulative Check

SELECT ISNULL(SUM(Matched_Amount), 0) as Total_Matched
FROM Reconciliation_Log 
WHERE ERP_Invoice_No = @InvoiceNo

INSERT to Reconciliation_Log

INSERT INTO Reconciliation_Log 
    (Source_Type, ERP_Record_ID, ERP_Invoice_No, ERP_Amount, 
     Bank_Ref, Bank_Amount, Match_Type, Match_Status, 
     Matched_Amount, Variance_Amount)
SELECT 
    'AP', ERP_ID, Invoice_No, Amount,
    @BankRef, @BankAmount, @MatchType, 'Matched',
    @BankAmount, Amount - @BankAmount
FROM ERP_Ledger 
WHERE Invoice_No = @InvoiceNo

---

Fuzzy Match Logic

Match Type Classification

Match Type	Condition	Example
Exact Match	Variance = 0	$1,000 =$1,000
Fuzzy Match	0 < Variance ≤ Tolerance	|$1,000 -$1,020| = $20 ≤$30
Mismatch	Variance > Tolerance	|$1,000 -$1,100| = $100 >$30

VB.NET Implementation

' Calculate variance
dec_Variance = Math.Abs(CDec(dt_ExactMatch.Rows(0)("Amount")) - dec_Amount)

' Determine match type
If dec_Variance = 0 Then
    str_MatchType = "Exact Match"
ElseIf dec_Variance <= dec_ToleranceAmt Then
    str_MatchType = "Fuzzy Match"
Else
    str_MatchType = "Mismatch"
End If

UiPath Activity Structure (Nested If)

If (dec_Variance = 0)
├── Then: str_MatchType = "Exact Match"
└── Else:
    └── If (dec_Variance <= dec_ToleranceAmt)
        ├── Then: str_MatchType = "Fuzzy Match"
        └── Else: str_MatchType = "Mismatch"

---

Database Schema

ERP_Ledger (AP - Accounts Payable)

CREATE TABLE ERP_Ledger (
    ERP_ID NVARCHAR(20) PRIMARY KEY,
    Vendor_Name NVARCHAR(100),
    Invoice_No NVARCHAR(50),
    Amount DECIMAL(18,2),
    Posting_Date DATE,
    Payment_Type NVARCHAR(20),  -- STANDARD, BATCH, SPLIT
    Batch_Group NVARCHAR(30),
    Reconciliation_Status NVARCHAR(50) DEFAULT 'Pending'
);

ERP_Receivables (AR - Accounts Receivable)

CREATE TABLE ERP_Receivables (
    AR_ID NVARCHAR(20) PRIMARY KEY,
    Customer_Name NVARCHAR(100),
    Invoice_No NVARCHAR(50),
    Amount DECIMAL(18,2),
    Posting_Date DATE,
    Deposit_Type NVARCHAR(20),  -- MERCHANT, TRANSFER, CUSTOMER, BATCH, SPLIT
    Batch_Group NVARCHAR(30),
    Reconciliation_Status NVARCHAR(50) DEFAULT 'Pending'
);

Reconciliation_Log

CREATE TABLE Reconciliation_Log (
    Log_ID INT IDENTITY(1,1) PRIMARY KEY,
    Source_Type NVARCHAR(10),        -- 'AP' or 'AR'
    ERP_Record_ID NVARCHAR(20),
    ERP_Invoice_No NVARCHAR(50),
    ERP_Amount DECIMAL(18,2),
    Bank_Ref NVARCHAR(50),
    Bank_Amount DECIMAL(18,2),
    Match_Type NVARCHAR(50),         -- 'Exact Match', 'Fuzzy Match', etc.
    Match_Status NVARCHAR(20),
    Matched_Amount DECIMAL(18,2),
    Variance_Amount DECIMAL(18,2),
    Batch_Group NVARCHAR(30),
    Created_Date DATETIME DEFAULT GETDATE()
);

---

Exception Handling

Exception Types

Type	Cause	Action	Retry?
BusinessRuleException	No ERP record found	Email alert, skip item	No
System Exception	Database timeout	Log, retry	Yes (3x)
Application Exception	Unexpected error	Screenshot, retry	Yes (3x)

BusinessRuleException Example

Throw New BusinessRuleException("Cannot find matching ERP record: " + str_BankRef)

---

Challenges & Solutions

Challenge 1: OCR Column Misalignment

Problem: PDF OCR extracted columns vertically, causing data mismatch.

Solution: Use Description lines (containing REF and INV) as the source of truth:

' Instead of relying on column position, extract from description
Dim descPattern As String = "^Preauthorized.+?(REF\d{6}).+?INV:\s*(INV-[A-Z0-9]+)"

Challenge 2: Amount String Parsing

Problem: Amount strings contained $, , and - characters.

Solution:

dec_Amount = Math.Abs(Convert.ToDecimal(
    str_Amount.Replace("$", "").Replace(",", "").Replace("-", "")
))

Challenge 3: Null DataTable References

Problem: DataTable variables were null when queries didn’t execute.

Solution: Initialize at transaction start:

dt_ExactMatch = New DataTable()
dt_FuzzyMatch = New DataTable()

Challenge 4: AP vs AR Transaction Identification

Problem: Need to route to correct ERP table based on transaction type.

Solution: Identify by invoice prefix:

bool_IsCredit = str_InvoiceNo.StartsWith("RCPT-") OrElse 
                str_InvoiceNo.StartsWith("DEP-") OrElse 
                str_InvoiceNo.StartsWith("TFR-")
str_SourceType = If(bool_IsCredit, "AR", "AP")

---

Summary Report Queries

Overall Reconciliation Summary

SELECT 
    Match_Type,
    COUNT(*) as Transaction_Count,
    SUM(Matched_Amount) as Total_Amount
FROM Reconciliation_Log
GROUP BY Match_Type
ORDER BY Transaction_Count DESC

Daily Status Report

SELECT 
    CAST(Created_Date AS DATE) as Recon_Date,
    Source_Type,
    COUNT(*) as Processed,
    SUM(CASE WHEN Match_Status = 'Matched' THEN 1 ELSE 0 END) as Matched,
    SUM(CASE WHEN Match_Status = 'Failed' THEN 1 ELSE 0 END) as Failed
FROM Reconciliation_Log
GROUP BY CAST(Created_Date AS DATE), Source_Type
ORDER BY Recon_Date DESC

---

Related Resources

• 📘 REFramework Architecture Deep Dive
• 📘 Orchestrator Queue Management
• 📘 RPA Best Practices
• 🎯 Bank Reconciliation Project (Business Overview)