aftec-customer-fields

AFTEC Customer Identification: CUSTOMER.NO vs ACCOUNT.NO

Critical gotcha when querying PROJECT.1287 by customer. PSI projects can be linked to a customer via either CUSTOMER.NO or ACCOUNT.NO — and many projects only have ONE populated. Searching by only one field misses projects.

---

The Two Fields

PROJECT.1287 has both:

Field	Source	Numbering	Example
CUSTOMER.NO	AFTEC CUSTOMER table key	6-digit (100xxx, 101xxx, etc.)	100513 = “GKN AEROSPACE”
ACCOUNT.NO	Redbook accounts lookup (/api/redbook/dev/lookups/accounts)	4-digit	0397 = “GKN Aerospace (El Cajon)”

The two numbering systems are completely separate — there is no mapping table between them.

Why It Matters

Some projects have one field populated, some have the other, some have both. Filtering by only one misses projects.

Real Example: GKN Aerospace El Cajon

8 known machines at GKN El Cajon:

Job	CUSTOMER.NO	ACCOUNT.NO	Resolved Customer Name
1382	100513	0397	GKN AEROSPACE
1383	100513	0397	GKN AEROSPACE
1384	100513	0397	GKN AEROSPACE
1795	(empty)	0397	(empty)
1836	(empty)	0397	(empty)
1962	(empty)	0397	(empty)
2096	100513	0397	GKN AEROSPACE
2280	100513	0397	GKN AEROSPACE

A search by CUSTOMER.NO=100513 finds only 5 of the 8 machines. The 3 newer machines (1795, 1836, 1962) have CUSTOMER.NO blank but ACCOUNT.NO=0397, so they’re missed.

The Correct Pattern

When searching for a customer’s projects in PROJECT.1287:

# Wrong — misses projects with empty CUSTOMER.NO
projects = [p for p in all_projects if p['CUSTOMER.NO'] == customer_no]
 
# Right — checks both fields
account_codes = get_account_codes_for_customer(customer_name)  # from redbook lookups
projects = [
    p for p in all_projects
    if p['CUSTOMER.NO'] in customer_numbers
    or p['ACCOUNT.NO'] in account_codes
]

Resolving customer name to numbers

1. Customer numbers (CUSTOMER.NO) — Resolve via project info endpoint which reads CUSTOMER table:

   • GET /api/project/dev/{job}/info → customerName and customerNumber
   • Caching tip: load all PROJECT.1287, group by CUSTOMER.NO, batch-resolve names

2. Account codes (ACCOUNT.NO) — Look up in redbook accounts lookup:

   • GET /api/redbook/dev/lookups/accounts returns array of { code, description }
   • Match description against customer name (case-insensitive contains)

Why This Happens

Theory: AFTEC has evolved over time. Older projects (pre ~2008) consistently filled CUSTOMER.NO. Newer projects sometimes only fill ACCOUNT.NO — possibly because ACCOUNT.NO is what the AR/AP system uses for billing, while CUSTOMER.NO was the older shipping address pointer.

This needs verification with PSI accounting/AFTEC team — but the data quality issue is real and affects any customer search.

Implications for Tools

The current MCP tool get_customer_360 (in psi-machine MCP server) only resolves via CUSTOMER.NO and therefore misses projects like 1795, 1836, 1962 for customers like GKN El Cajon. This should be fixed by:

1. After resolving customer name → CUSTOMER.NO list (current logic)
2. ALSO match the customer name against redbook accounts → ACCOUNT.NO list
3. Filter projects where EITHER CUSTOMER.NO or ACCOUNT.NO matches

Related Pages

• PSI.UniData.API — REST API for AFTEC data
• PSI Machine Intelligence MCP Server — get_customer_360 tool needs this fix
• Spare Parts Data Model — Another AFTEC data documentation page