Portal Invoice Reconciliation

Automated comparison of customer billing portal exports against CieTrade AR. Replaces manual Excel Power Query reconciliation (90+ seconds per file) with a single command that processes all portals in under 5 seconds.

What It Produces

Three Excel files in output/ after every run:

File	What it means	Action
result1_unbilled_wos.xlsx	Portal has a WO/service with no matching CieTrade invoice	Bill in CieTrade
result2_unsubmitted_invoices.xlsx	CieTrade has an open invoice not found on any portal	Submit to portal
result3_amount_discrepancies.xlsx	Invoice exists on both sides but amounts differ by >$0.01	Investigate & correct

Architecture Overview

How It Works

Portal export files (Ariba, Coupa, FM Pilot…)
        ↓
Auto-detect portal from filename
        ↓
Adapter normalizes to NormalizedInvoice
  - Invoice # cleaned (strips -Correction, R/rev, -1/-2, -A/B/C suffixes)
  - Coupa line items aggregated to invoice level
        ↓
CieTrade AR loaded live from PostgreSQL
  (cietrade.accounts_receivable, balance > 0)
        ↓
3-pass reconciliation engine
        ↓
result1_unbilled_wos.xlsx
result2_unsubmitted_invoices.xlsx
result3_amount_discrepancies.xlsx

Live Results (2026-06-11)

Three portals active (Ariba, Coupa, FM Pilot) against 20,750 open AR records ($15.3M open balance):

Result	Count	Notes
Unbilled WOs	282	All FM Pilot, all open. 243 have a Final Cost already priced.
Unsubmitted invoices	20,645	Expected high — only 3/8 portals connected. Drops significantly when Corrigo is added.
Amount discrepancies	5	All FM Pilot, open status. Differences range $5–$797.

Portal Coverage

Result 2 is large today because only 3 of 8 portals are connected. The top customers in that list (JLL - THE CHURCH: 3,961 invoices, Regions Bank: 1,058) are likely routed through Corrigo or other portals not yet connected. See Portal Coverage for details.

Quick Start

See Running a Reconciliation for day-to-day usage, Matching Logic for how the reconciliation engine works under the hood, or How a Reconciliation Run Works (Technical) for the full pipeline — the two independent data flows and exactly when CieTrade data is integrated.

Dashboard Build

The next phase of this project is a wdp-palantiri dashboard page — weekly AR workflow with file uploads, customer-level rollup, and 5-tab report.

Document	What's in it
📋 Build Spec	Full technical spec — data model, new adapters (Mercado, VendorCafe, Oracle), dashboard design, step-by-step tasks, acceptance criteria
🚀 Roadmap & Open Questions	Phase timeline, open questions tracker (Oracle compound IDs, Corrigo adapter, etc.), decision log

Source Code

ADO Repo: dev.azure.com/wasteology/Wasteology/_git/portal-reconciliation

portal_reconciliation/
├── adapters/        One BaseAdapter subclass per portal
├── cietrade.py      Live PostgreSQL AR query
├── models.py        NormalizedInvoice dataclass
├── normalizer.py    Invoice # normalization logic
└── reconciler.py    3-result comparison engine
reconcile.py         CLI entrypoint