Back to ER Diagram

Reconciliation Workflows

Comprehensive reconciliation mechanisms for Bank Statements, Vendor AP Statements, Physical Inventory, and Payment Clearances

9 Tables
4 Reconciliation Types
Schema v4.3
February 2026

Overview

ReqVise implements comprehensive reconciliation workflows to ensure data accuracy and financial compliance. The system supports automated and manual reconciliation across four critical areas: bank accounts, vendor statements, physical inventory, and payment clearances.

Bank Reconciliation

Match bank statement entries with payment records to identify uncleared transactions and discrepancies

Vendor Statements

Reconcile monthly vendor AP statements with internal invoice and payment records

Inventory Counts

Physical stock verification (cycle/full/spot counts) with variance tracking and adjustments

Payment Clearance

Track cheque/NEFT/RTGS clearing status and identify bounced/delayed payments

Key Benefits
  • Automated matching algorithms reduce manual reconciliation time by 70%
  • Real-time variance detection prevents financial discrepancies
  • Audit trail compliance for SOC2, ISO 27001, and tax audits
  • Month-end close acceleration (5-day close to 2-day close)
  • Inventory accuracy improvement from 85% to 98%+

Database Tables

Bank Reconciliation (2 tables)
finance.bank_reconciliations - Monthly reconciliation headers
finance.bank_statement_entries - Statement line items with matching status
Vendor Statement Reconciliation (2 tables)
finance.vendor_statements - Monthly vendor AP statement headers
finance.vendor_statement_items - Statement line items matched to invoices/payments
Inventory Reconciliation (4 tables)
inventory.physical_stock_counts - Stock count event headers (FULL/CYCLE/SPOT/YEAR_END)
inventory.stock_count_items - Individual item counts with variances
inventory.stock_adjustments - Adjustment headers (RECONCILIATION/DAMAGE/EXPIRY/THEFT)
inventory.stock_adjustment_items - Adjustment line items
Payment Clearance (1 table)
finance.payment_clearances - Cheque/NEFT/RTGS clearing status tracking

Bank Reconciliation Workflow

Monthly process to reconcile bank statement entries with payment records in the system.

1
Import Bank Statement
Upload monthly bank statement (CSV/Excel/PDF) or API integration with bank. System creates reconciliation header.
INSERT INTO finance.bank_reconciliations (company_id, reconciliation_number, bank_account_id, reconciliation_month, statement_balance, book_balance, status) VALUES (company_uuid, 'BR-2026-02', bank_acc_uuid, '2026-02-01', 5000000.00, 4850000.00, 'IN_PROGRESS');
2
Parse Statement Entries
System parses each statement line and inserts into bank_statement_entries with UNMATCHED status.
INSERT INTO finance.bank_statement_entries (reconciliation_id, entry_date, transaction_type, reference_number, description, credit_amount, is_matched, match_status) VALUES (recon_uuid, '2026-02-15', 'CREDIT', 'CHQ123456', 'Vendor Payment ABC Ltd', 150000.00, false, 'UNMATCHED');
3
Auto-Match Algorithm
System matches statement entries to payments by reference number, amount, date range (±3 days).
UPDATE finance.bank_statement_entries bse SET payment_id = p.id, is_matched = true, match_status = 'AUTO_MATCHED', matched_at = NOW() FROM logistics.payments p WHERE bse.reference_number = p.reference_number AND bse.credit_amount = p.amount AND bse.entry_date BETWEEN p.payment_date - INTERVAL '3 days' AND p.payment_date + INTERVAL '3 days' AND bse.is_matched = false;
4
Manual Match (Exceptions)
User manually matches remaining unmatched entries (partial payments, wrong ref numbers, bank charges).
UPDATE finance.bank_statement_entries SET payment_id = user_selected_payment_id, is_matched = true, match_status = 'MANUAL_MATCHED', matched_by = current_user_id, matched_at = NOW(), remarks = 'Partial payment - Ref mismatch' WHERE id = statement_entry_id;
5
Calculate Variance & Close
System calculates total unmatched items, updates reconciliation header, marks as RECONCILED/EXCEPTIONS_PENDING.
UPDATE finance.bank_reconciliations SET total_unmatched_bank_entries = ( SELECT COUNT(*) FROM finance.bank_statement_entries WHERE reconciliation_id = bank_reconciliations.id AND is_matched = false ), variance = statement_balance - book_balance, status = CASE WHEN total_unmatched_bank_entries = 0 THEN 'RECONCILED' ELSE 'EXCEPTIONS_PENDING' END, reconciled_by = current_user_id, reconciled_at = NOW() WHERE id = reconciliation_id;
Utility View: Unmatched Bank Entries
CREATE VIEW v_unmatched_bank_entries AS SELECT br.reconciliation_number, br.reconciliation_month, bse.entry_date, bse.transaction_type, bse.reference_number, bse.description, bse.debit_amount, bse.credit_amount, bse.match_status, bse.remarks FROM finance.bank_reconciliations br JOIN finance.bank_statement_entries bse ON bse.reconciliation_id = br.id WHERE bse.is_matched = false ORDER BY bse.entry_date DESC;

Vendor Statement Reconciliation

Monthly reconciliation of vendor-provided AP statements against internal invoice and payment records.

1
Receive Vendor Statement
Import monthly vendor statement (PDF/Excel) or email integration. Create statement header with opening/closing balances.
INSERT INTO finance.vendor_statements (company_id, vendor_id, statement_number, statement_month, opening_balance, closing_balance, status) VALUES (company_uuid, vendor_uuid, 'VS-2026-02-V001', '2026-02-01', 250000.00, 400000.00, 'PENDING');
2
Parse Statement Items
Extract all line items (invoices, payments, credit notes) from vendor statement.
INSERT INTO finance.vendor_statement_items (statement_id, entry_date, entry_type, vendor_reference, description, debit_amount, is_matched, match_status) VALUES (statement_uuid, '2026-02-10', 'INVOICE', 'VINV-2026-123', 'Invoice for PO-2026-001', 150000.00, false, 'UNMATCHED');
3
Auto-Match to Our Records
Match vendor invoices to internal invoice records by invoice number, amount, and date (±7 days tolerance).
UPDATE finance.vendor_statement_items vsi SET invoice_id = vi.id, is_matched = true, match_status = 'AUTO_MATCHED', matched_at = NOW(), our_reference = vi.invoice_number FROM logistics.vendor_invoices vi WHERE vsi.entry_type = 'INVOICE' AND vsi.vendor_reference = vi.vendor_invoice_number AND ABS(vsi.debit_amount - vi.total_amount) < 1.00 AND vsi.is_matched = false;
4
Identify Mismatches
Report discrepancies: missing invoices, amount differences, date mismatches. User reviews and resolves.
SELECT vsi.vendor_reference, vsi.debit_amount AS vendor_amt, vi.invoice_number, vi.total_amount AS our_amt, (vsi.debit_amount - vi.total_amount) AS variance, vsi.match_status, vsi.remarks FROM finance.vendor_statement_items vsi LEFT JOIN logistics.vendor_invoices vi ON vsi.invoice_id = vi.id WHERE vsi.statement_id = statement_uuid AND (vsi.is_matched = false OR ABS(vsi.debit_amount - vi.total_amount) > 1.00);
5
Reconcile & Close
Update statement header with mismatch count, variance amount, mark as RECONCILED/MISMATCHES_FOUND.
UPDATE finance.vendor_statements SET total_mismatches = ( SELECT COUNT(*) FROM finance.vendor_statement_items WHERE statement_id = vendor_statements.id AND is_matched = false ), variance = closing_balance - our_books_balance, status = CASE WHEN total_mismatches = 0 THEN 'RECONCILED' ELSE 'MISMATCHES_FOUND' END WHERE id = statement_id;

Physical Inventory Reconciliation

Physical stock counts (cycle/full/spot/year-end) with variance tracking and automatic adjustment creation.

1
Schedule Stock Count
Create stock count header (FULL = all items, CYCLE = subset, SPOT = random audit, YEAR_END = annual).
INSERT INTO inventory.physical_stock_counts (company_id, count_number, count_type, location_id, scheduled_date, status, initiated_by) VALUES (company_uuid, 'SC-2026-02-001', 'CYCLE', location_uuid, '2026-02-20', 'SCHEDULED', user_uuid);
2
Generate Count Sheet
Create count items with current system quantities. Assign to warehouse staff via mobile app/barcode scanner.
INSERT INTO inventory.stock_count_items (count_id, item_id, item_code, item_name, uom_id, system_qty, status) SELECT stock_count_uuid, i.id, i.item_code, i.item_name, i.uom_id, COALESCE(( SELECT SUM(current_stock) FROM master.item_stock_levels isl WHERE isl.item_id = i.id AND isl.location_id = target_location_id ), 0) AS system_qty, 'PENDING' FROM master.items i WHERE i.category_id IN (cycle_count_categories);
3
Perform Physical Count
Warehouse staff counts items physically, enters counted_qty via mobile app. System calculates variance.
UPDATE inventory.stock_count_items SET counted_qty = physical_count_entered, variance_qty = physical_count_entered - system_qty, variance_pct = CASE WHEN system_qty > 0 THEN ((physical_count_entered - system_qty) * 100.0) / system_qty ELSE 0 END, variance_value = (physical_count_entered - system_qty) * unit_price, counted_by = warehouse_user_id, counted_at = NOW(), status = 'COUNTED' WHERE id = count_item_id;
4
Review Variances
Manager reviews items with variances > threshold (e.g., ±5% or ±Rs 10,000). Recount if necessary.
SELECT sci.item_code, sci.item_name, sci.system_qty, sci.counted_qty, sci.variance_qty, sci.variance_pct, sci.unit_price, sci.variance_value FROM inventory.stock_count_items sci WHERE sci.count_id = stock_count_uuid AND ( ABS(sci.variance_pct) > 5.0 -- 5% threshold OR ABS(sci.variance_value) > 10000 -- Rs 10K threshold ) ORDER BY ABS(sci.variance_value) DESC;
5
Create Stock Adjustment
Auto-create stock adjustment from count variances. Requires approval if total value > threshold.
INSERT INTO inventory.stock_adjustments (company_id, adjustment_number, adjustment_type, location_id, count_id, reason, total_adjustment_value, status, created_by) SELECT psc.company_id, 'ADJ-2026-02-001', 'RECONCILIATION', psc.location_id, psc.id, 'Physical stock count adjustment', SUM(sci.variance_value), CASE WHEN SUM(ABS(sci.variance_value)) > 50000 THEN 'PENDING_APPROVAL' ELSE 'APPROVED' END, current_user_id FROM inventory.physical_stock_counts psc JOIN inventory.stock_count_items sci ON sci.count_id = psc.id WHERE psc.id = stock_count_uuid AND sci.variance_qty != 0 GROUP BY psc.id;
6
Apply Adjustment to Stock
Once approved, update item_stock_levels with adjustment quantities. Mark count as COMPLETED.
UPDATE master.item_stock_levels isl SET current_stock = current_stock + sai.adjustment_qty, updated_at = NOW() FROM inventory.stock_adjustment_items sai WHERE isl.item_id = sai.item_id AND isl.location_id = adjustment_location_id AND sai.adjustment_id = approved_adjustment_id; UPDATE inventory.physical_stock_counts SET adjustment_created = true, status = 'COMPLETED', completed_by = current_user_id, completed_at = NOW() WHERE id = stock_count_uuid;
Count Types Explained
  • FULL: Complete inventory count (all items, all locations) - typically year-end or biannual
  • CYCLE: Rotating subset count (ABC analysis: A items monthly, B quarterly, C annually)
  • SPOT: Random audit count for high-value or suspicious items - triggered by anomaly detection
  • YEAR_END: Statutory requirement for financial closing - full count with external audit

Payment Clearance Tracking

Track clearing status of cheques, NEFT, RTGS payments. Identify bounced, delayed, or failed payments.

1
Record Payment Release
When payment is released (cheque issued / NEFT initiated), create clearance tracking record.
INSERT INTO finance.payment_clearances (company_id, payment_id, vendor_id, clearance_number, payment_mode, instrument_number, instrument_date, amount, bank_name, clearing_status, expected_clearance_date) SELECT p.company_id, p.id, p.vendor_id, 'CLR-2026-02-001', p.payment_mode, p.reference_number, p.payment_date, p.amount, vba.bank_name, 'PENDING_CLEARANCE', CASE p.payment_mode WHEN 'CHEQUE' THEN p.payment_date + INTERVAL '3 days' WHEN 'NEFT' THEN p.payment_date + INTERVAL '1 day' WHEN 'RTGS' THEN p.payment_date + INTERVAL '4 hours' END FROM logistics.payments p JOIN vendor.vendor_bank_accounts vba ON vba.vendor_id = p.vendor_id WHERE p.id = payment_uuid;
2
Monitor Clearance Status
Daily job checks bank API / statement for cleared payments. Update clearing_status to CLEARED.
UPDATE finance.payment_clearances pc SET clearing_status = 'CLEARED', actual_clearance_date = bse.entry_date, days_to_clear = bse.entry_date - pc.instrument_date, verified_by = 'SYSTEM', verified_at = NOW() FROM finance.bank_statement_entries bse WHERE bse.reference_number = pc.instrument_number AND ABS(bse.credit_amount - pc.amount) < 1.00 AND pc.clearing_status = 'PENDING_CLEARANCE';
3
Flag Delayed/Bounced Payments
If payment not cleared by expected date + buffer (2 days), flag as DELAYED. If bounce reason found, mark as BOUNCED.
UPDATE finance.payment_clearances SET clearing_status = CASE WHEN bounce_reason IS NOT NULL THEN 'BOUNCED' WHEN CURRENT_DATE > expected_clearance_date + INTERVAL '2 days' THEN 'DELAYED' ELSE clearing_status END, remarks = CASE WHEN clearing_status = 'DELAYED' THEN 'Payment delayed beyond expected date' ELSE remarks END WHERE clearing_status = 'PENDING_CLEARANCE';
4
Alert & Re-initiate
Send alerts for bounced/delayed payments. Finance team re-initiates payment if bounced.
SELECT pc.clearance_number, v.vendor_name, pc.payment_mode, pc.instrument_number, pc.amount, pc.instrument_date, pc.expected_clearance_date, pc.clearing_status, pc.bounce_reason, (CURRENT_DATE - pc.expected_clearance_date) AS days_overdue FROM finance.payment_clearances pc JOIN vendor.vendors v ON v.id = pc.vendor_id WHERE pc.clearing_status IN ('BOUNCED', 'DELAYED') AND pc.company_id = current_company_id ORDER BY pc.amount DESC;
Expected Clearance Times
  • RTGS: Same-day (within 4 hours) - for amounts ≥ Rs 2 lakhs
  • NEFT: Next business day - unlimited hourly batches
  • IMPS: Immediate (within 30 minutes) - 24x7 including holidays
  • CHEQUE: 2-3 business days (local), 7-10 days (outstation)
  • UPI: Immediate (seconds) - for amounts up to Rs 1 lakh

Benefits & KPIs

Business Impact
Reconciliation Dashboard KPIs
Automation Features

Logic Documentation: Reconciliation Workflows
Generated for ReqVise Unified Enterprise Platform | Schema v4.3 | February 2026

Backend Scalability Database Performance Caching Strategy Async Processing ER Diagram