Collection Reconciliation Report

Know exactly which payment settled which invoice - with KPI dashboard, aging analysis, customer scoring, PDF statements, and XLSX export. Built on pure SQL for fast, reliable reporting.

The Problem Odoo Doesn't Solve

Odoo's native accounting shows you an invoice balance. It does not show which payment settled which portion of which invoice - especially with partial payments or one payment covering multiple invoices.

This creates blind spots: no allocation drill-down, no payment delay visibility, no collection scoring.

What You Get

• Per-allocation analytics - payment, invoice, collected, residual, delay days, aging bucket.
• KPI Dashboard - efficiency %, recovery ratio, overdue exposure, top collectors.
• Customer Score (0-100) - daily-recomputed badge on partner form.
• PDF Customer Statement - per-customer with aging breakdown.
• Collection Reminder Wizard - one-click email with overdue summary.
• Performance Mode - materialized view for large databases.

Reconciliation Lines

Every account_partial_reconcile row normalized into a readable line. Filter, group, pivot, graph. Built-in search panel by Company, Journal, Customer.

Collection Score (0-100)

Formula: 100 - 0.5 * avg_delay - 2 * overdue_count. Clamped 0-100. Color-coded: green >=80 / yellow >=50 / red <50 / gray = no data.

PDF Statements

Per-customer: invoice date, number, due date, amount, collected, residual, aging bucket. Aging summary table at bottom.

Collection Reminder

Manager-only wizard. Auto-fills customer and template. Email includes overdue total, oldest invoice date, invoice count. Logged in chatter.

Materialized View

Toggle in Settings. Concurrent refresh with pg_try_advisory_lock - prevents duplicate refresh jobs. Faster loading on large databases.

Role-Based Access

User: view lines + score.
Manager: dashboard + compute + refresh + reminder wizard.

• Paid On Time

Invoice paid before due date. Delay = 0, payment_state = paid.

• Paid Late

Payment after due date. Delay days > 0 - visible in report and KPI average.

• Partially Paid

Customer pays less than total. Residual > 0, payment_state = partial.

• Multi-Payment

One invoice settled by two payments. Both allocations shown.

• Credit Note Offset

out_refund reconciled against invoice. Normalized with correct polarity.

• Multi-Currency

Collected amount uses invoice currency when available, falls back to company amount.

Architecture

• pg_try_advisory_lock - prevents duplicate concurrent refresh jobs.
• 6-stage CTE architecture - reconcile_base -> normalized -> final_allocations. Maintainable and optimizable.
• Injection-safe SQL - all parameters are bind variables, never interpolated.
• Odoo 18 compatible - company_ids domain, memo field, updated read_group API.
• Built-in XLSX export - xlsxwriter only. No OCA dependency.
• Arabic translation included - i18n/ar_001.po ready.

account_partial_reconcile
         |
         v
   reconcile_base CTE
   (JOIN account_move_line x 2
    JOIN account_move x 2
    LEFT JOIN account_payment x 2)
         |
         v
   normalized CTE
   (invoice  payment detection)
         |
         v
  final_allocations CTE
  (+ allocation_count window fn)
         |
         v
  collection_reconciliation_report
  (VIEW or MATERIALIZED VIEW)