[Feature Request] Flexible Auto-Matching: Amount & Date Proximity (without mandatory Reference)

[azurre931]

Context
Currently, ERPNext’s native auto-reconciliation logic in bank_reconciliation_tool.py is strictly dependent on a matching reference number. If frappe.flags.auto_reconcile_vouchers is true, the system enforces a ref_condition in the SQL queries, making it impossible to auto-reconcile transactions where references differ.

In many modern workflows (especially with Payment API), the internal document reference (e.g., PE-0001) does not match the bank statement reference (e.g., TRF-X992J), even if the Amount and Date are an exact match.

Proposed Feature for Mint
I suggest that Mint introduces a more flexible "Auto-Match" engine that allows for reconciliation based on a Confidence Score rather than a strict reference check.

Key Improvements

1. Configurable Matching Rules: Allow users to prioritize "Amount + Date" matches over "Reference" matches.
2. Date Proximity Window: Since bank transactions often clear 1-3 days after the entry is created in ERPNext, allow a configurable "Date Proximity" (e.g., +/- 3 days).
3. Smart Ranking: Use the existing ranking logic but make the reference_no match an optional "bonus" to the score rather than a mandatory filter.
4. API-Friendly: This is crucial for users with high-volume automated transactions where bank references are often non-standardized.

Why this belongs in Mint
Mint is becoming the new standard for Banking in ERPNext. By allowing auto-reconciliation without a mandatory reference, Mint can solve a major pain point that remains unaddressed in the core bank_reconciliation_tool. This would save accounting teams hours of manual "clicks" for transactions that are obvious matches.

Use Case
Our company receives automated payments via API. The API generates a specific internal reference, but the bank statement displays a generic transaction ID. Currently, we must manually reconcile hundreds of transactions despite having exact matches for the amount and date. This feature would automate the process and significantly reduce manual accounting overhead.

[azurre931]

# Conceptual logic for erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py

def get_pe_matching_query(transaction, window_days=3, use_flexible_matching=True):
    pe = frappe.qb.DocType("Payment Entry")
    
    # 1. Scoring Logic
    ref_condition = pe.reference_no == transaction.reference_number
    amount_condition = pe.paid_amount == transaction.unallocated_amount
    
    # Reference match adds high weight, but isn't mandatory
    ref_rank = frappe.qb.terms.Case().when(ref_condition, 10).else_(0)
    amount_rank = frappe.qb.terms.Case().when(amount_condition, 5).else_(0)
    
    # 2. Date Proximity Window (+/- X days)
    from_date = frappe.utils.add_days(transaction.date, -window_days)
    to_date = frappe.utils.add_days(transaction.date, window_days)
    
    query = (
        frappe.qb.from_(pe)
        .select(
            (ref_rank + amount_rank + 1).as_("rank"),
            pe.name,
            pe.paid_amount,
            pe.reference_no,
            pe.posting_date
        )
        .where(pe.docstatus == 1)
        .where(pe.clearance_date.isnull())
        .where(pe.posting_date.between(from_date, to_date)) # Flexible Date
    )

    if use_flexible_matching:
        # Require Exact Amount, but Reference is now just a weight
        query = query.where(amount_condition)
    else:
        # Fallback to legacy strict behavior
        query = query.where(ref_condition)
        
    return query