AB CNC Analysis — Tools & Methodology
Complete documentation of every tool, API endpoint, data source, and technique used to produce the AB CNC controller obsolescence analysis. Written so anyone can reproduce or extend this work.
Tools Used
1. PSI.UniData.API (REST API)
URL: https://api.progressivesurface.com
Docs: Swagger UI at root URL
Source: C:\GIT\PSI.UniData.API
The primary data source. Every piece of data in the analysis came through this API, which reads from the AFTEC UniData ERP database on MRP-PROD.
Endpoints Used
| Endpoint | What it provided | Example call |
|---|---|---|
GET /api/project/dev/{job}/info | Machine details: customer, location, machine type, serial, ship date, engineers, order value | /api/project/dev/1405/info |
GET /api/project/dev/search?q={keyword} | Key discovery tool. Searched all PROJECT.1287 descriptions. Found all 75 CNC retrofit projects by searching for “CNC”. | /api/project/dev/search?q=CNC (93 results, 589ms) |
GET /api/project/dev/{job}/lineage | Retrofit linkage: finds successor projects (retrofits) via REF.PROJ.NO chain and serial number matching | /api/project/dev/1507/lineage |
GET /api/project/dev/by-type/{typeCode} | All projects of a machine type — used to find sibling machines for fleet analysis | /api/project/dev/by-type/1!23 |
GET /api/project/dev/machine-types | All 50 machine types with project counts | /api/project/dev/machine-types |
GET /api/bom/dev/job/{job} | Full BOM explosion for a job — used to verify AB CNC parts in machine BOMs | /api/bom/dev/job/1142 (3777 items) |
GET /api/bom/dev/part/{partNumber} | BOM explosion for a specific part — critical for retrofit scope. Exploding the retrofit assembly part shows exactly what the retrofit package contains. | /api/bom/dev/part/285334 (261 items) |
GET /api/parts/dev/{partNumber} | Part details: description, GT code, cost, on-hand | /api/parts/dev/026008 |
GET /api/obsolete-parts/dev/search | Find obsolete parts by description, manufacturer, design category. Used to find all obsolete 8520/8500/9-Series parts. | /api/obsolete-parts/dev/search?description=8520&pageSize=50 |
GET /api/obsolete-parts/dev/by-project/{job} | Obsolete parts in a project BOM — used to verify AB CNC parts are obsolete | /api/obsolete-parts/dev/by-project/2040 |
GET /api/obsolete-parts/dev/{pn}/chain | AI-powered replacement chain using Azure OpenAI to parse PRODUCT.NOTES free text | /api/obsolete-parts/dev/054761/chain |
GET /api/manufacturer/dev/by-part/{pn} | OEM manufacturer details: code, name, design category | /api/manufacturer/dev/by-part/026008 |
GET /api/data/dev/PRODUCT/1!{pn} | Raw PRODUCT table read — used to check INC/OBS flag (attr 8) directly | /api/data/dev/PRODUCT/1!054761 |
GET /api/data/dev/PRODUCT.NOTES/1!{pn} | Raw PRODUCT.NOTES — free-text notes containing replacement part info | /api/data/dev/PRODUCT.NOTES/1!054761 |
Endpoints Created During This Analysis
These endpoints were built specifically to support this analysis and are now permanent parts of the API:
| Endpoint | Why it was created |
|---|---|
GET /api/obsolete-parts/dev/search | No way to find obsolete parts by description/manufacturer before this |
GET /api/obsolete-parts/dev/by-project/{job} | No way to scan a project BOM for obsolete parts |
POST /api/obsolete-parts/dev/by-projects | Batch version for multi-project analysis |
GET /api/obsolete-parts/dev/{pn}/chain | AI-powered replacement chain (Azure OpenAI) |
POST /api/obsolete-parts/dev/warm-cache | Pre-warm AI cache for batch processing |
GET /api/project/dev/search?q={keyword} | Most impactful. No project description search existed. This single endpoint found all 75 CNC retrofits in 589ms — replacing hours of brute-force BOM scanning. |
2. Azure OpenAI (GPT 5.2)
Endpoint: https://adeve-midqp8v8-eastus2.cognitiveservices.azure.com
Model: gpt-5.2-chat
API Version: 2025-04-01-preview
Used for parsing free-text PRODUCT.NOTES into structured replacement part data. The AI extracts part numbers, context, and confidence from notes like “use 054524 for parts list, use 053193 for Installation & Operation.”
Integrated into the API as AzureOpenAiService.cs — a thin HttpClient wrapper that POSTs to the Azure OpenAI chat completions endpoint with response_format: json_object. Results cached for 7 days in ReplacementCacheService.cs.
3. Python Scripts
All scripts are in C:\GIT\PSI.UniData.API\pipeline\. They call the API endpoints above and generate Excel/Word reports.
| Script | Purpose | Output |
|---|---|---|
build_ab_cnc_report.py | First-pass Excel with all 61 machines, grouped by customer/location/type | AB_CNC_Control_Machines.xlsx |
verify_ab_cnc.py | BOM verification: scans all 61 BOMs for AB CNC parts, checks retrofits for controller upgrades | AB_CNC_Verification.xlsx |
ab_cnc_deep_analysis.py | Deep analysis: scans retrofit BOMs, finds other machines with AB CNC, checks sibling projects | AB_CNC_Deep_Analysis.xlsx |
build_ab_cnc_report_docx.py | First Word document with executive summary and Rockwell position | AB_CNC_Obsolescence_Analysis.docx |
build_master_ab_cnc_report.py | Consolidated master report (replaced earlier files). Correct numbers (59 machines, 65 already upgraded). Updated narrative about Rockwell exiting CNC market. | AB_CNC_Master_Report.xlsx + .docx |
build_cnc_comps_report.py | Comparable pricing analysis: each machine matched with closest completed retrofit | AB_CNC_Retrofit_Comps.xlsx + .docx |
deep_bom_comparison.py | Deep BOM comparison: original machine vs its own retrofit to identify scope delta | (console output) |
build_scope_and_wiki.py | Retrofit scope document + wiki page with methodology and lessons learned | AB_CNC_Retrofit_Scope.docx + wiki page |
4. Python Libraries
| Library | Purpose |
|---|---|
openpyxl | Excel file creation with styling, auto-filters, freeze panes, conditional formatting |
python-docx | Word document creation with tables, heading styles, formatted text |
urllib.request | HTTP calls to the PSI.UniData.API (no external dependencies needed) |
ssl | SSL context for HTTPS API calls (self-signed cert handling) |
json | API response parsing |
re | Regex for parsing project descriptions (“CNC/PRIMS Upgrade to 1507” → extract job 1507) |
collections | defaultdict, Counter for grouping and counting |
5. Web Research
Used to validate the obsolescence externally:
| Source | What it provided |
|---|---|
| Rockwell Automation Product Lifecycle Search | Official lifecycle classification definitions (Active → Active Mature → End of Life → Discontinued) |
| Rockwell Legacy Product Support | Legacy support program details, migration pathways |
| CNC Parts Dept | Aftermarket pricing: used 8520-CPUX1 ($700), 8520-ENET ($4,630), 8520-ASM3 ($300) |
| Radwell | Additional surplus parts availability |
| DO Supply | 8520 controller inventory status |
| Direct Azure OpenAI test | Verified API key and endpoint with curl before integrating into the API |
6. PSI Wiki
Repo: C:\GIT\PSI-Wiki-Site (branch: v4)
URL: https://wiki.progressivesurface.com
Pages referenced during analysis:
content/applications/unidata-api.md— API endpoint documentation (updated with new endpoints)content/applications/mcp-server.md— MCP server tool list (51 tools, identifiedassess_obsolescence_risk)content/reference/aftec/parts.md— VB_PRODUCT.REV1 subroutine docs, PRODUCT.NOTES table referencecontent/reference/aftec/general.md— ITEMMANF subroutine docs, cross-reference tablescontent/data/data-brain.md— PRODUCT.1287 field listings
Pages created/updated:
content/analytics/ab-cnc-obsolescence.md— Full analysis documentation (this companion page)content/analytics/ab-cnc-methodology.md— This pagecontent/applications/unidata-api.md— Added obsolete parts + replacement chain endpoint docs
7. GitHub Actions (CI/CD)
Workflow: .github/workflows/deploy-ps-proxy.yml
Runner: Self-hosted on PS-PROXY
Used for deploying API changes to production. The workflow was also modified during this analysis:
- Added a config merge step that merges new config sections from
deploy/appsettings.Production.jsoninto the server’s production config without overwriting existing values. This was needed to deploy the Azure OpenAI API key.
8. UniData / AFTEC (Underlying Database)
All data ultimately comes from the AFTEC UniData database on MRP-PROD, accessed via U2 Toolkit for .NET through the API.
| Table | What it contains | How accessed |
|---|---|---|
| PROJECT.1287 | Project master data: description, customer, machine type, status, dates, engineers | ProjectService → session.CreateUniFile("PROJECT.1287") |
| PRODUCT | Part master data: INC/OBS flag (attr 8), manufacturer class (attr 20) | ObsoletePartsService → session.CreateUniFile("PRODUCT") |
| PRODUCT.NOTES | Free-text notes — contains replacement part references | ReplacementChainService → session.CreateUniFile("PRODUCT.NOTES") |
| ITEMMANF | Part-to-manufacturer mapping: manufacturer code (attr 14), design category (attr 9) | ManufacturerService / ObsoletePartsService |
| MFG.1287 | Manufacturer details: name, address, phone | ManufacturerService |
| CUSTOMER | Customer names | ProjectService (resolves customer numbers) |
| MACHTYPE.1287 | Machine type names | ProjectService (resolves type codes) |
| BOM data | Bill of materials (via VB_BOMX.REV1 subroutine) | BomService |
UniData access patterns:
- SELECT + iterate:
session.CreateUniCommand()→cmd.Command = "SELECT TABLE WITH F8 = 'O'"→selectList.Next()for server-side filtering - Direct read:
session.CreateUniFile("TABLE").Read("1!" + key)for single-record lookups - Subroutine:
SubroutineExecutor.Execute(session, "VB_BOMX.REV1", args)for complex operations
Data Flow
UniData (MRP-PROD)
↓ U2 Toolkit for .NET
PSI.UniData.API (PS-PROXY:443)
↓ REST/JSON over HTTPS
Python Scripts (local)
↓ openpyxl / python-docx
Excel + Word Reports (Desktop → OneDrive)
↓
PSI Wiki (methodology + findings)
Reproduction Steps
To reproduce this analysis:
- Verify API is running:
curl https://api.progressivesurface.com/api/health - Find machines on AB CNC: Search obsolete parts →
GET /api/obsolete-parts/dev/search?description=9%2FSERIES&pageSize=500 - Find CNC retrofits:
GET /api/project/dev/search?q=CNC→ parse descriptions for “Upgrade to {job}” - Cross-reference: Compare machines needing upgrade (INC/OBS = “O” for AB CNC parts) against completed retrofits
- Get comparable pricing:
GET /api/project/dev/{retrofit_job}/info→ orderValue field - Get retrofit scope:
GET /api/bom/dev/part/{retrofit_assembly_part}→ full BOM of what the retrofit package contains - Generate reports: Run
pipeline/build_master_ab_cnc_report.pyandpipeline/build_cnc_comps_report.py
Lessons Learned
-
Search descriptions, not BOMs. Project descriptions (“CNC/PRIMS Upgrade to 1507”) are the source of truth for what work was done. BOM hardware classification is unreliable because retrofit BOMs inherit the original machine’s full BOM.
-
AB exited the CNC market. Searching for ControlLogix/CompactLogix in CNC retrofits returns zero results because the replacements are FANUC and Siemens. CNC and PLC are different product categories.
-
Retrofit BOMs share the original machine’s BOM. In AFTEC, the retrofit project (95877) and the original project (1507) point to the same BOM data (modified in-place). To see the retrofit scope, explode the retrofit assembly part’s BOM, not the project BOM.
-
The project search endpoint was the single most impactful tool. One query (
/api/project/dev/search?q=CNC, 589ms) found all 75 CNC retrofit projects. Without it, brute-force BOM scanning of 474 retrofits took 10+ minutes and still missed results. -
Use the MCP server’s existing tools. The PSI MCP server already has
assess_obsolescence_riskandcompare_projectstools that could shortcut much of this analysis. Future work should use those first.
Related Pages
- AB CNC Obsolescence Analysis — Findings and fleet status
- PSI.UniData.API — API documentation
- PSI MCP Server — 51 tools including obsolescence assessment