Docs
Ongoing Tasks
Party - Bank Reco

Party - Bank Reco

Next Phase

  • Grouping of amount based on Narrations (for eg : Unmatched records in Shree Mateshri Enterprises)
  • Amount is matching but 1 day discrepancy (for eg : Unmatched records in Pragati courier services)
  • Validations can we take it next phase (Also not validation but confirmation for eg: Parties are not matching, do you still wish to continue)

1. Introduction

The AI Reconciliation module in ErpCrystal MFG provides an advanced, automated system to reconcile financial data. It comprises two primary components:

  1. AI Bank Reconciliation (driven by AIBankReco.razor): Reconciles internal ERP ledger records with uploaded external bank statement sheets.
  2. AI Party Ledger Reconciliation (driven by AIPartyLedgerReco.razor): Reconciles internal accounts receivable/payable records (System Ledger) with external vendor/customer account statements (Party Ledger).

By leveraging a hybrid approach of ClosedXML Excel parsing, custom-engineered matching heuristics, and LLM capability via AWS Bedrock (Model: Amazon Nova Lite), this module dramatically reduces manual bookkeeping review.

2. Declarative Rule Loading (.md Files at Runtime)

A defining design pattern of the AI skills in this codebase is declarative rules isolation. Instead of hardcoding prompts, rules, or categorization heuristics directly in C# strings, each skill class loads its corresponding instructions dynamically from a matching Markdown (.md) file at runtime.

How Rules are Resolved:

In the constructor of each skill, the system resolves the physical file path of the .md file relative to the running application’s base directory and reads the entire text file into memory.

// Path resolution pattern used across skills
var mdPath = Path.Combine(AppContext.BaseDirectory, "Skills", "Category", "SkillName.md");
if (File.Exists(mdPath))
{
    _rulesContext = File.ReadAllText(mdPath);
}

This loaded rules context (_rulesContext) is then passed as system instructions to the LLM runtime or used as reference logic by the matching engine. This allows developers to adjust prompts, match thresholds, exception rules, and aliases without recompiling the codebase.

Web Services (ErpCrystal_MFG.Web.Services)

These services run on the front-end Blazor web project and handle API client calls.

IAiRecoService / AiRecoService

  • ProcessAiBankReco: Packages the user’s BankReco criteria and uploaded Excel/CSV statement stream into a multipart form data request and sends it to the API to run bank statement reconciliation.
  • ProcessAiPartyLedgerReco: Sends the internal System Ledger and external Party Ledger Excel files to the API for bilateral invoice/transaction matching.

API Controllers (ErpCrystal_MFG.Api.Controllers)

These controllers run on the ASP.NET Core API project and act as coordinators for the matching business logic.

AiBankRecoController

  • AiBankRecoProcess: Handles incoming bank statement uploads, extracts rows, executes matching skills, generates the reconciliation Excel sheet, and returns the result path.
  • AiPartyLedgerRecoProcess: Performs multi-file matching between System and Party ledgers, extracts entries, runs party name verification, compiles audit classifications, and builds a merged reconciliation spreadsheet.
  • ValidateStatementPeriod: Performs safety checks to verify if the dates selected in the UI match the statement period dates auto-extracted from the file headers.
  • GenerateBankRecoExcel: Formats and outputs the final matched bank reconciliation records into a downloadable Excel worksheet.
  • GeneratePartyLedgerRecoExcel: Merges internal and external statements into a styled, double-sheet Excel workbook with color-coded confidence levels.
  • BuildPartyLedgerSummary: Calculates overall reconciliation KPIs (matched/unmatched counts, confidence score averages, net ledger balances).

AI Skills & Business Logic (ErpCrystal_MFG.Api.Skills)

These classes contain the algorithmic and model-driven rules for data parsing, comparison, and analysis.

A. Bank Reconciliation Skills (Skills/BankReco)

  • BankRecoExtractionSkill: Automatically scans Excel sheets to locate header rows, maps dynamic columns to standard fields, and extracts statement period date ranges from non-tabular top rows.
  • BankRecoMatchingSkill: Pairs transactions based on matching directions (Receipts to Credits, Payments to Debits) by checking cheque tokens, narration similarity, and falling back to LLM heuristic matching.
    • Rules file: Loads instructions from BankRecoMatchingSkill.md defining rules for exact amount comparison, cheque number prefix cleaning, and date proximity.

B. Party Ledger Reconciliation Skills (Skills/PartyLedgerReco)

  • PartyLedgerRecoExtractionSkill: Extracts rows from both fixed ERP sheets and highly variable client sheets while filtering out closing balances, headers, and total lines. Also extracts company/party headers to verify file compatibility.
  • PartyLedgerRecoMatchingSkill: Employs bilateral row comparisons, demanding absolute amount equality and scoring candidate pairs based on reference tokens, date proximity, and narration overlap.
    • Rules file: Loads instructions from PartyLedgerRecoMatchingSkill.md setting criteria score weights (High, Medium, Low confidence) and same-day matching rules.
  • PartyLedgerRecoAuditSkill: Analyzes unmatched entries, assigns explanatory codes, maps transactions to categories (e.g. “Tax Ledger Difference” or “Bank Charges / Interest”), and provides high-level narrative findings.
    • Rules file: Loads instructions from PartyLedgerRecoAuditSkill.md describing the desired format and examples of human-like forensic audit findings.

C. Shared Core Heuristics (Skills/Common)

  • NarrationMatchingSkill: Performs text normalizations (stripping lowercase, removing legal designations like “LTD”, and discarding payment headers like “NEFT”) and checks for direct substring containment or common financial concept synonyms (e.g. mapping “SAL” to “SALARY”).
    • Note: Operates on statically structured conceptual matching groups defined in code.
  • AiDecisionSkill: Evaluates complex text structures using spelling distance models (Levenshtein similarity), token prefix matching, and numeric cheque number extraction to output logical matching weights.
    • Rules file: Loads instructions from AiDecisionSkill.md containing the normalization, abbreviation filter lists, and token similarity thresholds.
ERPCrystal Lite (Free Version)DPR Under Development Points