Running a Reconciliation

Step 1: Download Portal Export Files

Download the latest export from each connected portal and drop the files into data/portal_files/:

Portal	Where to export from	Expected filename pattern
FM Pilot	Work Orders → Export CSV	Contains fm pilot or fmpilot
Ariba	Invoices → Export	Contains ariba
Coupa	Invoice Lines → Export	Contains coupa or invoice_line_list

Auto-detection

The tool detects which portal a file belongs to automatically from the filename — no renaming needed. Files in data/portal_files/ are gitignored (they contain customer data).

Step 2: Run

Option A: Claude Code command

/reconcile

The command auto-detects which directory has files (data/portal_files/ for production, specs/sample-data/ for testing) and shows a summary of results.

Option B: Command line

# Production (files in data/portal_files/)
uv run python reconcile.py

# Sample/test data
uv run python reconcile.py --portal-dir specs/sample-data/

# Specific portals only
uv run python reconcile.py --portals ariba,coupa,fm_pilot

# Include paid/zero-balance AR records (default: open only)
uv run python reconcile.py --all-ar

Step 3: Review Output Files

Output files are written to output/:

result1_unbilled_wos.xlsx — Unbilled Work Orders

FM Pilot work orders that are open and have not yet been invoiced in CieTrade.

Key columns: portal_invoice_id (WO#), status, amount (Final Cost if priced), address, city, state

Action: For each row, create the corresponding invoice in CieTrade.

No customer name in FM Pilot

The FM Pilot export does not include a customer name field — all WOs in this export are JLL accounts. The address and city fields identify the specific service location.

result2_unsubmitted_invoices.xlsx — Unsubmitted Invoices

CieTrade AR invoices that were not found in any connected portal.

Key columns: invoice_number, customer, amount, balance, invoice_date, due_date, location_name

Action: Submit the invoice to the appropriate customer portal.

Why is this list large?

With only 3 of 8 portals connected, most JLL accounts (Corrigo, etc.) will appear here. The count will drop significantly as remaining portals are connected. See Portal Coverage.

result3_amount_discrepancies.xlsx — Amount Discrepancies

Invoices matched between CieTrade and a portal where amounts differ by more than $0.01. Paid and voided portal invoices are automatically excluded.

Key columns: invoice_number, ar_amount, portal_amount, difference, portal, portal_status, Group #

Action: For each row, determine whether the discrepancy is in CieTrade (correct it there) or on the portal side (raise a dispute).

Group # column

When a customer submits one portal invoice under a CieTrade Group # (rather than the individual billsheet numbers), the row's Group # column carries that group number and the amount comparison is done against the summed CT total for the whole group. On the dashboard, the "Invoices Needing Action" view shows this Group # column immediately after Third Party. See Group Number Matching.

Sample Output Summary

Loaded portal files:
  fm_pilot (672 records from 5.28.26 PULL FM PILOT WORK ORDERS.CSV)
  ariba (216 records from ARIBA INVOICES 5.28.26 PULL.xlsx)
  coupa (37754 records from Invoice Lines - Coupa.xlsx)
Loading CieTrade AR from PostgreSQL (open invoices only (balance > 0))...
  20750 AR records loaded.

Result 1: 282 unbilled WOs | Result 2: 20645 unsubmitted invoices | Result 3: 5 amount discrepancies

Output files written to: /path/to/portal-reconciliation/output

Known Data Quality Notes

Issue	Detail
Future-dated invoice	Invoice 994873 (JLL – CyrusOne) has invoice_date = 2027-03-01 — data entry error in CieTrade. Will appear in Result 2 until corrected directly in CieTrade.
FM Pilot empty Final Cost	Work orders in In Progress or pre-quote stages don't have a Final Cost yet — amount = None is expected for those rows.