---
name: tomago_exo_data_mapping
description: EXO CSV → Evolution field mapping + resolution keys for the Tomago invoice import (non-obvious gotchas)
metadata: 
  node_type: memory
  type: reference
  originSessionId: d960bc1e-a472-45f8-b83d-4eff8e8cafd6
  modified: 2026-07-26T09:34:58.168Z
---

EXO export CSVs (see `evolution/cron/TIS_TMP/`) are **headerless positional dumps** roughly aligned to `invoices`/`invoiceitems` column order — but several fields must be TRANSFORMED, not copied:

- **Client**: EXO **debtor** id → `contacts.clientid` via `contacts.exoDebtorRef = <exo debtor id> AND is_client=1` (NEW dedicated col added 2026-07-25; `resolveClient` matches it). **NOT `externalRef2` anymore** — externalRef2 holds the EXO **creditor** id on supplier rows, and the debtor/creditor namespaces **collide across *different* companies** (e.g. EXO id 1061 = "Lowes Petroleum" creditor but = "APPS" debtor), so matching externalRef2 would mis-post invoices to the wrong contact. A company is ONE dual-role contact (`is_client=1 AND is_supplier=1`); Xero enforces unique contact Name so duplicate company rows break sync. `exoDebtorRef` is backfilled from externalRef2 for all pre-existing clients. `contacts` PK is `clientid` (not `id`), MyISAM, no unique index. NOT `contacts.externalRef` (holds company name) and NOT `addressbook` (contact people). See `projects/tis/reports/migrate_missing_clients.sql`. [[tis_project_location]]
- **Line item**: EXO stock code → `inventory.id` (stored as string in `invoiceitems.itemid`) via `inventory.code = <exo code>`. `inventory.externalref` is NOT the EXO code (it holds category ints, all 41,997 populated). Unmatched → MISC `inventory.id 28103`.
- **Income account**: force all lines to **Default Sales** `accounts.id=1` (`externalref=200` = Xero code); EXO GL code (e.g. 11200) is parked in the CSV `cat` slot — ignore it.
- **Tax**: EXO tax id → Evolution `tax` table: GST=4, WET=5, ABNW=6, FREE=7. (CSV sample `taxId=10` @10% → GST 4.)
- **Header total gotcha**: EXO export `total` is **GST-inclusive**; Evolution `invoices.total` is the **ex-GST subtotal**, `tax`=GST, `balance`=gross−paid−credits. A positional copy mis-posts every invoice — let the `invoice` class recompute from lines.
- `createdate` in export is garbage (`50:38.8`, Excel time artifact) — ignore. `branch` → 1 (Head Office), not CSV's 0.
- **Idempotency**: dedupe on `invoices.externalTenant='MYOB_EXO' AND externalRef=<exo invoice no>`. Verified 2026-07-26: re-running exoimport over the top of an already-loaded tenant creates **zero** duplicate references.
- **`DR_TRANS.ALLOCATEDBAL` is the ALLOCATED (paid) amount, NOT the outstanding balance** (proved 2026-07-26: 1,041 of 1,055 pre-30/06 fully-unpaid open invoices carry `AMOUNT > ALLOCATEDBAL` and were correctly included). So the open-items filter `AMOUNT > ALLOCATEDBAL` is CORRECT, but three column mappings were **inverted**: `balance=ALLOCATEDBAL`, `paid=AMOUNT-ALLOCATEDBAL`, `paidstatus=1 WHEN ALLOCATEDBAL<=0` (flags "paid" when nothing is allocated). Correct = `balance=AMOUNT-ALLOCATEDBAL`, `paid=ALLOCATEDBAL`, `paidstatus=1 WHEN AMOUNT-ALLOCATEDBAL<=0`. Masked today because exoimport recomputes paid/balance from lines (all land `paid=0`) — bites when payment import is enabled; `paidstatus` is what AR screens gate on.
- **HEADER AND LINE EXTRACTS MUST SHARE ONE WHERE CLAUSE.** The lines query filtered pre-cutoff rows on `ALLOCATEDBAL > 0` (= *has been paid*) while the header used `AMOUNT > ALLOCATEDBAL` (= *still outstanding*) — near-complementary sets. Old fully-unpaid invoices got a header with **no lines** → import as **$0.00 invoices** (the exact population an open-items migration exists for); old fully-paid got orphan lines. Both extracts now carry an identical `DECLARE @cutoff` + WHERE. **Corrected queries live at `projects/tis/reports/exo_extract_invoices.sql` and `exo_extract_invoiceitems.sql`** — those files are the source of truth; SSMS holds a copy.
- **Positional column contract**: header consumed at 0-idx 0/9/13/18/21/22/28/30/36 (`INV_*` consts), lines at 3/8/10/12/14/16/17/18/20 (`IT_*` consts), both verified aligned 2026-07-26. Never insert or reorder a SELECT column without updating the consts. Header cols `contactid`/`jobid`/`staffid`/`branch`/`cat`/`contractid` carry raw EXO ids that mean nothing in Evolution — harmless only because the importer ignores those indexes.
- **Stale error text — FIXED 2026-07-26** (`exoimport.php` :14 header comment and :295 message now say `exoDebtorRef`; working copy on `staging`, uncommitted).
- **Two files** (invoices + invoiceitems) exported together; items link to headers via **EXO invoice id** = invoiceitems CSV col 4 (0-idx 3) → invoices CSV col 1 (0-idx 0).

**Prod DB access**: `claude` user now has SELECT on `304-1779936994` (added 2026-07-18); default is control-DB-only per [[prod_mysql_access]]. Project context in [[tomago_exo_import]].