AFTEC Accounts & Customers — the data model

Who this is for: anyone (human or agent) building against customer/account data — the reconciliation app, the D365 CE migration, reporting. Every claim below was verified against live AFTEC via the UniData API on 2026-07-15; record counts are from that date and drift slowly.

The entity model

ACCOUNT.1287 (4,866)          the relationship/site level — "CHROMALLOY GAS TURBINE CORPORATION · SAN DIEGO"
   ▲ attr 17: multivalue list of customer numbers (reverse link)
   │
CUSTOMER.1287 (916)           the LINK file — one thin record per linked bill-to; field ACCOUNT (attr 3) = account code
   │
CUSTOMER (1,716)              the bill-to MASTER — NAME.1, STATUS, ADDR.1/2, CITY/STATE/ZIP/CNTRY, CONTACT, PHONE.1
   │
SHIPTO (2,071)                ship-to addresses, keyed CO!CUST!SEQ (e.g. 1!100647!1)
ARHIST (130,669)              one record per AR invoice — CUST.NO, INV.DATE, ORIG.AMT

Key format everywhere: CO!KEY — company 1 in practice, so 1!0409 (account), 1!100089 (customer), 1!100647!1 (ship-to). Account numbers are 4-digit; customer (bill-to) numbers are 6-digit; the numbering systems are unrelated (see aftec-customer-fields for the PROJECT.1287 consequence of that).

Linking semantics

• Forward link: CUSTOMER.1287 field ACCOUNT → the account code. No CUSTOMER.1287 record (or a blank/dangling code) = an orphan bill-to.
• Reverse link: ACCOUNT.1287 attribute 17 lists the account’s customer numbers.
• Account metadata (name, location, tier/accountType) is served by the Redbook lookup: GET /api/redbook/dev/lookups/accounts.
• The recon engine’s terms (company_analysis.py in d365-ce-migration): linked = account code lands in the analyzed company’s account set; unlinked = no code; linked-other = a code that points outside that set (in ALL scope: a dangling code). A transacting account = has ≥1 linked bill-to (only 724 of 4,866 accounts transact).
• Same-named accounts are different sites, not duplicates — the VB6 Account Manager (and the recon rail) groups accounts by name with locations beneath (UC.NAME → UC.LOCATION order).

Activity — never trust STATUS

CUSTOMER.STATUS is mostly blank and lies (“A”/active on customers last invoiced in 1989). The trustworthy activity signal is ARHIST: sweep invoices by CUST.NO, take the newest INV.DATE. The recon convention: dormant = no invoice on file, or none in 3+ years.

Data-quality reality: 914 of 1,716 CUSTOMER records have no account link at all, and 702 of those carry invoices — 11,097 of them. These are not dead records; they are live invoicing entities nobody attached to a CRM account. This is the cleanup the Customer Data Reconciliation app exists to vote on.

The go-forward model (AFTEC → BC / D365 CE)

The migration collapses this structure onto four patterns. Modeling and the record lists live in d365-ce-migration/docs/consolidation/ (build_go_forward.py → PSI-Go-Forward-Customers.xlsx).

• Keep rule: if we sold them a machine, they stay a customer — no recency test on the install base. Install base is a set of SERIALS, never a count of jobs: a retrofit modifies a machine that already exists. With no install base at all the invoice date is the only evidence there is, so it must be recent: 36 months, not ten years — and only where a CUSTOMER link exists.
• Consolidation key: company name + site location. Same company at the same site = one customer; at different sites = separate customers sharing a billing customer.
• The state is part of the site. Reducing LOCATION to a town name collapses same-named towns in different states. Pratt & Whitney at Middletown CT, Middletown NY and Middletown PA are three plants and 81 machines. Split on the state only when LOCATION names one outright — a customer’s bill-to state is the remit-to address, so inferring from it splits real single sites.
• Central billing means billing more than one customer. AFTEC has no such feature, so it is inferred from shadow ship-tos — a signal that also fires when a bill-to bills the account it already belongs to, and when it bills exactly one customer (which is just that customer’s own bill-to). Excluding both takes the central bill-to list from 128 to 12.
• Pattern 3 counts own plants only. A customer takes “multiple ship-tos” only if it buys at two or more of its own sites; a pile of third-party drop-ship addresses does not make one.
• A job number ≥ 10000 means “not a new build”, not “retrofit”. That bucket also holds standalone equipment — ShotMeters, LiquiFeeders, standalone RLDs, torches, booths. The discriminator is the serial: PSI machine serials are 4-digit (2,052 of 2,060 new builds; zero new builds have a 6-digit serial), so all 152 six-digit serials on retrofit-class jobs are equipment, not machines.
• PROJECT.1287.ORIG.PROJ and RETROFITS are 0% populated. AFTEC stores no link from a retrofit to the machine it modifies. SERIAL.NO (82% populated) is the only join available.
• From 4,869 accounts: 834 customers, 12 central billing customers, 587 true ship-tos, 3,777 prospects, 201 retired, 60 consolidated away, and PSI’s own accounts split out as internal rather than migrated.
• The 834 split by pattern: 766 Simple · 36 Consolidated billing · 31 Multiple ship-tos · 1 Combination. Pattern 2 is under-detected — 108 more customers are demonstrably billed through a sibling site’s customer record but are not yet flagged, because the ship-to → account join only resolves 539 of 1,466.
• 2 open questions, both the same one: CATERPILLAR U-SERIES PO# and NAO DISBURSEMENT are accounts-payable codes rather than legal entities, and no AFTEC field carries the name they should be created under. Everything else was decided from the data, with the deciding field recorded on the workbook’s Decisions sheet.

The build refuses to write the workbook unless every one of these holds: each account lands on exactly one sheet; no retired account holds a serial; every owned machine serial lands on exactly one customer and none is double-counted as internal; every ship-to link is either kept or explicitly discarded with a reason; every central billing customer bills at least two customers; the retrofit→machine serial join still agrees with 95%+ of the job descriptions that name a parent build; and under 1% of items are left for a human. Treat a passing run as the precondition for trusting the counts above — they were reproduced from the committed script, not transcribed.

Three traps that will bite anyone re-deriving this:

1. GET /api/redbook/dev/lookups/accounts silently excludes the 194 DELETED=Y accounts (4,666 of 4,869). Source any account universe from the raw ACCOUNT.1287 export instead, or your dead-record list comes back empty by construction.
2. The DELETED=Y flag does not mean the account is dead. 42 flagged accounts still own machines or trade — Chromalloy (17 machines), Boeing (10), Sealed Power (10), Pratt & Whitney (8) — and N C I was invoiced 2026-05-28. Treat it as a hint, never as a filter.
3. ACCOUNT.1287 LOCATION is a free-text site label, not a city: Middletown NY, Towanda, PA, AeroFab - Indianapolis, Chengdu AeroTech Mfg. Splitting on a comma matches almost nothing — strip trailing state/country tokens and any - qualifier first. Note it is also not the same as the linked bill-to’s CITY, which is the remit-to address (La Porte site → Pittsburgh AP office); that divergence is itself a consolidated-billing signal.

Related gotcha: a SHIPTO record is often a different company than the bill-to that owns it — 410 of 595 genuine ship-tos are distributor drop-ship addresses, not the customer’s own plants. That is why pattern 3 counts own plants only.

How PSI actually bills a multi-site customer

This is the single most important thing to understand about this data, and nothing in AFTEC states it — it is a convention, applied inconsistently over ~40 years.

ACCOUNT.1287 is the CRM entity: one account per customer SITE. CUSTOMER is the invoicing entity — a type of customer record, not a subordinate “bill-to”. The two registries are parallel, and how they have been built out has drifted badly over the years. Treat neither as authoritative about the other.

For a multi-site customer the shape is:

CUSTOMER 101015  PRATT & WHITNEY, 400 Main St, East Hartford   ← the one billing entity
   │  linked (ARBITRARILY) to exactly one of the sites' accounts:
   └─ ACCOUNT.1287 1514  Pratt & Whitney · East Hartford
   │
   └─ its SHIPTO records ARE THE OTHER SITES, not delivery addresses:
        seq 21  PRATT & WHITNEY ASHEVILLE      → ACCOUNT 4621 · Asheville
        seq 15  PRATT & WHITNEY GRAND PRAIRIE  → ACCOUNT 1515 · Grand Prairie
        seq 6   PRATT & WHITNEY NORTH BERWICK  → ACCOUNT 1517 · North Berwick
        seq 3   PRATT & WHITNEY POWER SYS      → ACCOUNT 1522 · San Antonio
        …23 ship-tos across 27 Pratt & Whitney accounts

An order is sold-to / bill-to 101015, then a ship-to under 101015 is selected to identify the site the machine is actually for — the site that has its own ACCOUNT. So:

• A site’s invoices are invisible from its own account. They are all on 101015. Pratt & Whitney Asheville owns 30 machines and shows no invoice, because every one of its invoices is billed to East Hartford’s customer record.
• A missing last invoice means UNKNOWN, never “never”. 914 CUSTOMER records have no account link at all and 702 of those carry invoices — 11,097 invoices with no CRM account attached.
• The ship-to is the only join back to the site, and it is a weak one: matching a ship-to’s city to a same-company account resolves only 539 of 1,466 ship-tos under a linked billing customer. The rest are naming drift (PW AUTOAIR / PRATT & WHITNEY AUTOAIR, PRATTT & WHITNEY), sites with no account, or third parties sitting under the same customer.
• This IS the four-pattern model’s pattern 2 (consolidated billing). In BC it becomes a real Bill-to Customer No. relationship instead of a ship-to convention.

Do not read a ship-to under a billing customer as a delivery address. Some are (a genuine drop-ship), but under a multi-site customer most are site identifiers.

Machines: a machine is a serial, not a job

• Job number < 10000 = a new build. It mints a serial; that serial is the machine, and it was born on that project.
• Job number ≥ 10000 = everything else. A retrofit is a modifier on an existing serial — it never creates a machine. The same range also holds standalone equipment (ShotMeters, LiquiFeeders, standalone RLDs, torches, booths), studies, tooling and rentals.
• PSI machine serials are 4-digit. 2,052 of the 2,060 new builds that carry one are 4-digit and no new build has a 6-digit serial — so a 6-digit serial on a ≥10000 job is standalone equipment, not a machine.
• PROJECT.1287.ORIG.PROJ and RETROFITS are 0% populated. Nothing stores which machine a retrofit modified. SERIAL.NO (82%) is the only join. Cross-checked against job descriptions that name the parent build in words, the serial join agrees 98.7% of the time.
• A rental is not a sale. Mahle (Delphi Thermal Systems) “has” machines only through rental jobs; it never owned one.
• Progressive Surface’s own accounts are not customers. They hold the Process Services lines and R&D cells. A machine built to stock and later sold shows the sale as the buyer’s own retrofit. Note the derived internal count (~88 serials) is cumulative history, not the live fleet — the real owned/operated fleet is ~30 machines, mostly department 125, and is not documented anywhere yet.
• Watch future dates. PLAN.SHIP.DATE is used when SHIP.DATE is absent, so ~50 builds are dated after today. Those are backlog, not install base.

AR history starts in late 1987

The oldest credible invoice is 1987-10-08 (Pratt & Whitney, CUSTOMER 101001). Nine records claim older first-invoice dates — years like 1106, 1111, 1243, 1319 — and all nine are corrupt century digits on otherwise active accounts. Anything asserting no invoice for a relationship older than 1987 is unprovable, not false.

The link gap — read this before trusting any invoice date

An account’s invoice history is only reachable through a linked AFTEC bill-to, and most accounts have none:

	linked bill-to	share
Go-forward customers	493 of 974	51%
Go-forward prospects	243 of 3,663	7%

So 435 customers own machines and show no invoice — 434 purely because nothing is linked, not because they were never billed (Pratt & Whitney Asheville: 30 machines, no link). It is not an ARHIST cutoff either: ARHIST runs from 1988 and 571 of those 862 machines shipped after that. Treat a missing last invoice as unknown, never as never. This is the same orphan-bill-to problem the reconciliation app exists to fix, seen from the account side.

Reading this data through the UniData API

• Account search (the VB6 Account Manager list contract): GET /api/accounts/search?name={q} — filters DELETED # 'Y', matches UC.NAME LIKE '...q...', ordered BY UC.NAME. Authenticated only in production. Details in unidata-api.
• Whole files (paged): GET /api/data/{table}/export?page=N&pageSize=1000 (raw attributes) or …/export-fields?fields=UC.NAME,UC.LOCATION,DELETED&… (dictionary-evaluated, needed for I-descriptors like UC.NAME). Tables: ACCOUNT.1287, CUSTOMER, CUSTOMER.1287, SHIPTO, ARHIST.
• Single record: GET /api/data/{table}/{key} — URL-encode the ! (1%210409).
• Anonymous /dev/ twins of the data routes still exist but are being phased out; prefer the authenticated routes (Azure CLI can mint per-user tokens — see the developer/CLI auth flow in unidata-api).
• Gotcha: SHIPTO.XREF on CUSTOMER.1287 is sparsely maintained and undercounts — read the SHIPTO file directly.
• Gotcha: the analysis engine caches its AFTEC pulls under d365-ce-migration/data/*.json and reuses them unless run with --refresh.

The legacy oracle

The VB6 Account Manager (PTIManager.exe, frmAccount.frm) is the acceptance oracle for any new account UI: query contract, sort, and name-grouping are source-extracted in C:\GIT\docs\vb6-specs\account-manager.md (+ machine-readable extracts alongside). The diff-test harness is psi-portal/scripts/verify-account-search.mjs.

Related

• unidata-api — API endpoints, auth flows
• psi-portal — the reconciliation app that consumes this model
• aftec-customer-fields — CUSTOMER.NO vs ACCOUNT.NO gotcha on PROJECT.1287
• data-brain — broader data-source map