Matching Logic

Two complementary strategies drive the reconciliation: invoice number normalization (mapping portal-specific variants back to CieTrade base numbers) and status-based exclusion (filtering records that should never generate alerts). All lookups are O(1) — portal records are pre-indexed into sets and dicts at startup.

Step 1 — Invoice Number Normalization

CieTrade assigns plain numeric invoice numbers (e.g. 639294). Portals append their own suffixes for revisions, corrections, and line splits. Before any matching happens, every portal invoice number is run through normalize_invoice_number() in portal_reconciliation/normalizer.py, which strips exactly one suffix per number using a priority-ordered list of regex patterns.

Portal raw value	Suffix type	Normalized result
639294A	Trailing letter	639294
303563 R	Space + R (revision)	303563
938228-Correction	-Correction	938228
1097495-1	Numeric sequence (-1, -2…)	1097495
662523-A	Letter variant (-A, -B…)	662523
589362rev	rev suffix	589362
12USP6532630	Portal-assigned ID (starts non-numeric)	(skipped — not a CieTrade number)

Only one suffix is stripped per call — patterns are applied in specificity order so that e.g. 938228-Correction hits the -Correction rule before the generic -[letter] rule fires.

Matching Priority

An AR invoice is matched to a portal record in priority order — the first strategy that produces a match wins:

Priority	Strategy	When used
1 (Primary)	Normalized invoice number join	All portals; the AR invoice number (normalized) matches a portal invoice number directly.
2 (Group)	Group # join	The AR invoice's CieTrade group_invoice_no matches a portal invoice number — the customer submitted one portal invoice covering the whole billing group. See Group Number Matching below.
3 (Secondary)	PO number join	Portals that carry a PO number reference (Ariba Reference, Coupa PO Number, FM Pilot Work Order #).
4 (Tertiary)	Address + ZIP match	VendorCafe and any portal where PO matching fails; uses data/address_overrides.csv.

A direct invoice-number match (priority 1) always wins for a given AR record; the Group # fallback only fires when the record's own number was not submitted.

Group Number Matching

Customers may submit one portal invoice under the CieTrade Group # instead of the individual billsheet invoice numbers. Without group awareness, every member invoice appears as "Not Submitted" and per-invoice variance is meaningless.

Observed live: CBRE – Exxon Mobil submitted Coupa invoice 1052996 — a Group # covering 18 billsheet invoices totaling $9,424.41. Across a single Coupa export, 653 open-AR invoices spanning 323 groups matched via their Group #.

Match rule (CustomerRollup and Reconciler): an AR invoice counts as submitted if its own number matches a portal invoice (direct), or — as a fallback — its group_invoice_no matches a portal invoice number. The direct match always wins for that record.

Group-sum variance rule: for group matches, variance is computed once per group as:

group variance = (summed CT amount of the whole group) − (portal amount on the Group #)

The variance is attributed once per group — on the first member detail row; other member rows leave Variance / Third Party Amount blank. This stops the customer-level ct_vs_submission_variance from multiplying the group variance by the member count.

Split-submission rule: a customer may submit several portal invoices under the same Group #. (Observed live: Coupa group 1104126 = $700.41 + $6,750.00, together exactly the group CT total.) The portal side of the comparison is therefore the sum of all portal records carrying that Group # (_portal_amount_sum_by_invoice in the rollup; summed portal_matches in Result 3). Result 3 emits one row per group and skips the group only when every submission is paid/closed.

Group CT totals source: cietrade.load_group_ct_totals() sums POSTED billing_sheet_charges.group_invoice_no history rather than the run's AR records. AR purges paid invoices, so a partially-paid group summed from AR alone would show a false variance. Both CustomerRollup and Reconciler accept the totals via the optional group_ct_totals parameter and fall back to AR-derived per-group sums when it is absent.

Group # normalization: _normalize_group_no() treats CieTrade's "no group" sentinels (0, NULL, blank) as no group, and collapses the float round-trip Excel produces (1052996.0 → 1052996).

Surfacing: Result 3 group rows use the Group # as invoice_number, the group total as ar_amount, and carry matched_by = "group". The "Invoices Needing Action" sheet carries a Group # column (immediately after Third Party). Guarded by tests/test_group_matching.py.

Step 2 — Three Reconciliation Passes

Result 1 — Unbilled Work Orders (FM Pilot only)

FM Pilot is a work order portal, not an invoice portal. Its records carry a WO number plus a Vendor Invoice Number field that is only populated once the WO has been invoiced in CieTrade.

1. Take every FM Pilot record where invoice_number is None (Invoiced = No).
2. Check whether the WO number appears as a po_number on any non-FM Pilot portal record. If it does, the WO has already been submitted elsewhere — skip it.
3. Everything remaining is an unbilled WO.

The cross-portal PO check uses a pre-built set — lookup is O(1) per WO.

Result 2 — Unsubmitted Invoices (all portals)

Every normalized portal invoice number is added to a single set at startup. CieTrade AR records are then checked against that set.

1. Build portal_invoice_numbers — a set of all normalized invoice numbers across all portals.
2. For each CieTrade AR record, check if its invoice number is not in that set.
3. Skip AR records already closed: paid, void, voided, closed, written off, write-off.
4. Everything remaining (not found on any portal, not already closed in AR) is flagged as unsubmitted.

Result 3 — Amount Discrepancies (all portals)

A second index maps each normalized invoice number to its portal record(s). When an AR invoice is found in this index, amounts are compared.

1. Build portal_by_invoice — a dict of invoice_number → [NormalizedInvoice, …].
2. For each CieTrade AR record that has a portal match, compare amounts.
3. Threshold: > $0.01 difference (floating point tolerance).
4. Exclude portal records where is_paid = True — amounts are already settled.
5. Exclude portal records with status in: voided, void, abandoned, cancelled, canceled.

Why 14 results dropped to 5

The original run returned 14 discrepancies. 9 were Coupa records with is_paid = True or status = voided. Adding the two exclusion guards brought the count down to the 5 genuinely actionable discrepancies.

Exclusion Reference

Condition	Applies To	Reason Excluded
AR status: paid, void, closed, written off	Result 2	Already settled in CieTrade — no portal submission needed
Portal is_paid = True	Result 3	Portal confirms payment; amount difference is historical, not actionable
Portal status: voided, abandoned, cancelled	Result 3	Portal closed the record; amount is no longer relevant
invoice_number is None (portal)	Results 2 & 3	Portal uses its own ID scheme (e.g. 12USP6532630) — no CieTrade match is possible