PSI Knowledge Base

Project Explorer

17 min readEdit this page

Project Explorer

Project Explorer is PSI’s comprehensive machine project tracking and customer analytics platform. It visualizes 25+ years of project data (2000-present) across 3,084 projects, providing customer lifetime value modeling, fleet health tracking, parts analysis, and lead time benchmarking.

ResourceURL
Productionhttps://projects.progressivesurface.com
RepositoryProgressiveSurface/project-explorer
Azure Appps-project-explorer.azurewebsites.net
Portal EntryListed on PSI Portal

Features

Views & Dashboards

Project Explorer provides 11 distinct views accessible from the view switcher toolbar:

ViewPurpose
TimelineHorizontal year-by-year timeline of all projects with clickable cards
TableSearchable, sortable data table (TanStack Table) with virtual scrolling
GridCard grid layout with machine type grouping
EquipmentFleet view — every physical machine by serial number, with retrofit chain
WarrantyActive/expiring/expired warranty tracking with calendar timeline
Customer 360Parent company hierarchy, revenue breakdown, LTV, fleet health scores
ObsolescenceComponent age tracking, EOL reference data, retrofit recommendations
PartsPart frequency analysis, BOM comparison (Jaccard similarity), reuse scoring
Lead TimeYear trends, budget variance analysis, machine type stats, labor benchmarks
OutreachPrioritized customer action queue with CSV export for sales/service outreach
SalesQuote pipeline, conversion analytics, customer deep-dive, orders, trends

UI Layout

The app uses a CSS Grid shell with three rows for maximum content area:

+------------------------------------------------------------------+
| HEADER (48px) compact: logo | search | alert-bell | stats | user |
+------------------------------------------------------------------+
| TOOLBAR (40px) view-switcher | year-range | type-filter          |
+------------------------------------------------------------------+
| CONTENT (1fr = ~992px on 1080p)                                  |
|   Active view fills this with h-full flex flex-col                |
+------------------------------------------------------------------+
  • 88px chrome (header + toolbar) leaves ~992px content on 1080p
  • All views use h-full flex flex-col with flex-shrink-0 headers and flex-1 overflow-auto content
  • No calc(100vh-Npx) anywhere — scroll containers are flex-driven
  • Alert Dashboard renders as a right-side slide-over panel (not stacked)
  • Project Detail Panel renders as a full-height overlay with backdrop

Customer 360 / Lifetime Value

The Customer 360 view provides deep analytics per customer:

  • Parent Company Grouping: Automatically groups account locations into parent companies (e.g., all Pratt & Whitney sites)
  • Revenue Breakdown: Original machines, retrofits, and Total Invoiced (from invoiceline.csv — all billing including machines, parts, and service)
  • Composite Tier Scoring: Strategic / Core / Developing / Transactional based on four weighted factors
  • Fleet Health Score: 0-100 score based on component age across all customer machines. Upstream component/PRIMS inputs are produced by the psi-data-pipeline and published into app-facing artifacts; scraper-owned source tables now live in psi_ingest.db before being copied into rebuildable analytics outputs.
  • Engagement Timeline: Order history, retrofits, warranty events
  • LTV Projection: Forward-looking revenue estimate based on fleet age, parts consumption trends, and retrofit probability
  • Intermediary Attribution: Revenue from distributor accounts (e.g., Ferguson, Vallen) is split and attributed to end-customer accounts

Composite Scoring System

Customer tiers are determined by a 4-factor weighted composite score:

FactorWeightWhat It Measures
Revenue25%Logarithmic scale of total business value (max baseline $50M)
Recency25%Exponential decay from most recent engagement (project, invoice, or quote)
Aftermarket30%Invoice volume per machine, order frequency, revenue trend (3yr vs prior 3yr)
Fleet20%Machine count (log), retrofit activity, fleet health score

Tier thresholds: Strategic >= 70, Core >= 45, Developing >= 20, Transactional < 20

Key design decisions:

  • Revenue uses max(totalOrderValue, totalInvoiced) — not summed, since they overlap
  • Aftermarket trend defaults to 0 with no invoice history (no free points)
  • Fleet health defaults to 0 when no machines on file (no free points)
  • getQuoteRevenueForAccount() returns data when totalInvoiced > 0 even if quoteCount === 0 (many accounts have invoices without quotes)

Quote Revenue Integration

Real quote-to-order data from AFTEC, processed via scripts/process-quotes.mjs:

  • 22,364 spare parts quotes from spquote.csv (dgwReportFiles)
  • 163,306 invoice line items from invoiceline.csv spanning 2005-2026 ($859M total invoiced)
  • 54,970 quote line items from spquoteline.csv (part-level detail on open/won quotes)
  • 3,492 won quotes definitively linked via Invoice → Quote No field
  • SPN attribution via SPN.QUOTES.1287 export (machine-to-quote linkage)
  • Seller name resolution via EMPLOYEE.PUBLIC.1287 (human-readable names instead of employee IDs)
  • Open sales orders via VB_OPENORDLIST subroutine call
  • Quote-to-SO linkage via SOREQ table export (bidirectional quote-order mapping)
  • AFTEC crosswalk: CUSTOMER.1287 → ACCOUNT.1287 mapping with fuzzy name-matching fallback
  • Attribution hierarchy: Machine (SPN) → Site (ShipTo) → Customer → Unmatched
  • Quote intelligence: Duplicate/revision detection, behavior flags (part mining, declining engagement, growth)

Intermediary / Distributor Attribution

Some customers order through distributors (e.g., Ferguson, Vallen, Fastenal). The intermediary_mapping.json config defines 15 intermediary accounts with their end-customer mappings. Revenue from intermediary invoices is split proportionally across mapped end-customer accounts:

  • Intermediary invoiced revenue is divided equally among mapped end-customers
  • Each end-customer’s record is augmented with attributedRevenue and intermediarySources
  • The Customer 360 view shows “attributed” amounts in blue with source distributor names
  • 15 intermediaries currently mapped (Ferguson → Standard Aero/GE/Howmet, Vallen → Pratt/Honeywell, etc.)

Alert Dashboard

Slide-over notification panel (not stacked in page flow) with four alert categories:

  • Warranty: Expiring warranties (30/60/90 day windows)
  • Obsolescence: Machines approaching component end-of-life
  • Engagement: Customers with declining activity or long gaps
  • Retrofit: Machines with aging/critical components that need upgrading

Alert bell in header shows count badge with pulse animation for critical alerts. Panel slides in from the right without pushing content.

Parts Analysis

Five sub-tabs within the Parts view:

  • Part Frequency: How often each part appears across all BOMs, with reuse categorization (Standard / Common / Rare)
  • BOM Comparison: Cross-project Jaccard similarity to find structurally similar machines
  • Duplicates: Duplicate part detection across projects
  • Kits: Kit analysis for common part groupings
  • Catalog: Searchable part catalog across all BOMs

Lead Time Analytics

Four sub-tabs within the Lead Time view:

  • Year Trends: Average lead time by year with on-time rate and schedule slip
  • Budget Variance: Budgeted vs actual hours per department across all 10 cost centers
  • Machine Types: Lead time distribution by machine type with predictability scoring (coefficient of variation)
  • Labor Benchmarks: Average actual hours per department per machine type (3+ projects required)

Outreach Planner

Prioritized customer action queue for sales and service teams:

  • Ranks customers by engagement gap, fleet age, and revenue potential
  • Filterable by action type (warranty follow-up, retrofit opportunity, re-engagement)
  • CSV export for integration with CRM workflows
  • Action items auto-generated from alert triggers

Sales Analytics Dashboard

Six-tab dashboard powered by quote_revenue.json data, providing end-to-end visibility into the spare parts quoting pipeline:

Pipeline Tab

  • KPI cards: Open count, open value, average quote age, quotes >90 days, stale value
  • Aging bucket bar chart: Visual distribution across 0-30d, 30-90d, 90-180d, 180d+ buckets
  • Sortable customer pipeline table: Open quotes per customer with age, value, seller attribution
  • Stale quote highlighting: Amber indicators for quotes >90 days without conversion
  • CSV export: Download filtered pipeline data for CRM workflows

Conversion Tab

  • Quote-to-win funnel visualization with status breakdown (open/ordered/won/closed)
  • Year-over-year conversion rate trends
  • Status distribution across the full quote lifecycle

Customers Tab

  • Customer ranking table: Sortable by quote count, win rate, quoted/won/invoiced value
  • Behavior flag badges: Part Mining (red), High Revisions (amber), Declining (amber), Growing (green)
  • SPN machine attribution: Expandable rows showing quote volume per machine serial number
  • Crosswalk source badges: aftec (green), name-matched (blue), unmatched (orange)
  • Account Deep Dive panel: Full-screen overlay with:
    • Complete quote timeline with stale highlighting and line item drilldown
    • Quote-to-order conversion funnel specific to the account
    • Part overlap analysis across quotes (from line items)
    • Behavior flag explanations with tooltips
    • Yearly revenue bar chart (quoted vs won)
    • Comparable accounts with similar quote volume for benchmarking

Sellers Tab

  • Seller performance ranking by won value, quote count, and win rate
  • Human-readable seller names resolved from EMPLOYEE.PUBLIC.1287

Orders Tab

  • KPI cards: Open order count, total value, shipped value, remaining, breakdown by type (Spare/Warranty)
  • Sortable table: SO#, date, customer, PO#, type badge, value, shipped, remaining, linked quotes
  • Links open sales orders back to originating quotes via SOREQ mapping
  • KPI cards: Current year quotes, won value, YoY quote growth, YoY won growth, invoiced total
  • 3-layer bar chart: Quoted (blue), Won (green), Invoiced (gray) per year
  • Summary table: Year-by-year breakdown with win rate, quote/invoice ratio, conversion velocity
  • Conversion velocity metric: Average days from quote to invoice for won quotes

Quote Status Classification

Quotes are classified into four statuses:

StatusCriteria
WonQuote number appears in invoiceline.csv
OrderedQuote linked to an open sales order via SOREQ
OpenQuote exists in OPEN.QUOTE.HEAD.1287 and is ⇐365 days old
ClosedNot in ERP or >365 days old without a win

A stale flag is set on any non-won quote older than 90 days, enabling visual indicators without removing quotes from the pipeline.

Quote Intelligence (Behavior Flags)

Per-customer behavioral analysis to answer “why is conversion low?”:

FlagTriggerSignificance
Possible Part Mining10+ quotes, <5% win rate, <2% won valueCustomer may be harvesting part numbers/pricing
High Revision Rate>50% of quotes are revisions of earlier quotesWin rate may be artificially deflated by counting revisions
Declining EngagementLast 2 years <50% of prior 2 years’ quote volumeCustomer engagement is dropping
Growing AccountRecent won value exceeds prior by 20%+Account is trending positively

Duplicate detection groups quotes by base number (stripping alpha suffixes like 15124A, 15124B) and finds overlapping-part quotes within 60-day windows. The adjustedWinRate corrects for revision inflation.

Quality Intelligence Dashboard

Integrates with Redbook RFC data (processed via scripts/process-redbook.mjs):

  • Quality alert generation (active RFCs, high-cost issues, recurring patterns)
  • Department-level analysis of quality issues
  • Cost trend tracking (material + labor waste)
  • Links to related projects and machines

Technology Stack

ComponentTechnology
FrameworkReact 18.3
LanguageTypeScript 5.6 (strict mode)
Build ToolVite 6
StylingTailwindCSS 3
TableTanStack Table 8 + React Virtual 3
AuthenticationAzure AD / MSAL 3.30
ServerExpress 4 + SQLite (better-sqlite3)
TestingVitest 4 + Testing Library + jest-dom
IconsLucide React
Node.js>=24.0.0

Data Architecture

Data Sources

All data is loaded client-side from static files in /public/RawData/:

FileRecordsDescription
Project1287List.xml3,084Project master data from AFTEC
Account1287List.xml~4,800Account/customer master data
userlist.csv~200Employee ID → name mapping
projbudget.csv~3,000Budget data (pipe-delimited, 25 columns)
VendorList.xml~800Vendor information
control_components.json1,548 projectsBOM data with component details (7.3MB), built from pipeline outputs that now source scraper-owned tables from psi_ingest.db before publish
quote_revenue.json22,364 quotesPre-processed quote/invoice revenue data
intermediary_mapping.json15 entriesDistributor → end-customer revenue attribution config
redbook_data.jsonvariesPre-processed Redbook RFC quality data

Pre-processing Scripts

ScriptPurposeData Sources
scripts/process-quotes.mjsQuote revenue attribution, orders, intelligencespquote.csv, invoiceline.csv, spquoteline.csv, CUSTOMER.1287, ACCOUNT.1287, OPEN.QUOTE.HEAD.1287, SPN.QUOTES.1287, EMPLOYEE.PUBLIC.1287, SOREQ, VB_OPENORDLIST subroutine
scripts/process-redbook.mjsRedbook RFC quality dataRedbook API export, RFC classification data

Data Flow

dgwReportFiles (S:\UnixShare)    PSI.UniData.API
    | spquote.csv                    | CUSTOMER.1287
    | invoiceline.csv                | ACCOUNT.1287
    | spquoteline.csv                | OPEN.QUOTE.HEAD.1287
    |                                | SPN.QUOTES.1287
    |                                | EMPLOYEE.PUBLIC.1287
    |                                | SOREQ
    |                                | VB_OPENORDLIST (subroutine)
     +--------+-----------------------+
              |
     +--------v--------------------+
     | process-quotes.mjs         |  (local pre-processing)
     | process-redbook.mjs        |
     +--------+--------------------+
             |
    +--------v--------------------+      +----------------------+
    | quote_revenue.json         |      | Project1287List.xml  |
    | redbook_data.json          |      | Account1287List.xml  |
    | intermediary_mapping.json  |      | projbudget.csv       |
    +--------+--------------------+      | control_components   |
             |                          +----------+-----------+
              +-----------+--------------------+
                         |
                +--------v--------+
                |  React App      |  (client-side parsing)
                |  dataService.ts |
                +--------+--------+
                         |
                +--------v--------+
                |  11 View Modes  |
                |  (Timeline,     |
                |   Customer 360, |
                |   Sales...)     |
                +-----------------+

Pipeline note: App-facing static outputs remain the consumer contract, but scraper-owned upstream tables are now staged durably in psi_ingest.db and only then copied into rebuildable analytics artifacts/published files.

AFTEC Number Systems

Understanding the crosswalk is critical for data integration:

ID TypeFormatExampleSource
AccountNumber4-digit1030ACCOUNT.1287 record ID
CustomerNumber6-digit100631CUSTOMER.1287 record ID
ProjectNumber4-digit2356PROJECT.1287 (Job number)
SPNS + digitsS2586Serial/Part Number (machine identity)
ContactNumber5-digit09313CONTACT.1287 record ID
ShipToNumbercompany!customer!sequence1!100631!1Ship-to site identifier

Crosswalk: CUSTOMER.1287 field 3 → AccountNumber. ACCOUNT.1287 field 17 → CustomerNumber(s). This bidirectional mapping links 6-digit customer IDs (from quotes/invoices) to 4-digit account IDs (from projects).

Project Structure

project-explorer/
+-- .github/workflows/
|   +-- azure-app-service.yml    # CI/CD pipeline
+-- public/RawData/              # Static data files (9 files)
+-- scripts/
|   +-- process-quotes.mjs       # Quote revenue pre-processor
|   +-- process-redbook.mjs      # Redbook RFC quality processor
+-- server.js                    # Express server (SQLite + static files)
+-- vitest.config.ts             # Test configuration
+-- src/
    +-- App.tsx                  # CSS Grid shell, data loading, view routing
    +-- auth.ts                  # Azure AD / MSAL configuration
    +-- main.tsx                 # Entry point
    +-- types/index.ts           # All TypeScript interfaces (~450 types)
    +-- services/
    |   +-- dataService.ts       # Data parsing, business logic, LTV, scoring
    |   +-- machineStatusService.ts # Shared machine status (SQLite)
    |   +-- __tests__/
    |       +-- dataService.test.ts # 96 unit tests for pure functions
    +-- test/
    |   +-- setup.ts             # jest-dom/vitest setup
    |   +-- fixtures.ts          # Builder functions (buildProject, buildBudget, etc.)
    +-- components/
        +-- Header.tsx           # Compact 48px header (search + alert bell + stats)
        +-- FilterBar.tsx        # 40px toolbar (year range, type filters)
        +-- AlertDashboard.tsx   # Slide-over notification panel
        +-- Timeline.tsx         # Horizontal timeline view
        +-- ProjectCard.tsx      # Individual project card
        +-- ProjectDetailPanel.tsx # Full-height overlay detail panel
        +-- CumulativeGraph.tsx  # Analytics graph
        +-- LoadingSpinner.tsx   # Loading state
        +-- customers/
        |   +-- CustomerView.tsx # Customer 360 with LTV + scoring
        +-- equipment/
        |   +-- EquipmentFleet.tsx # Equipment fleet view
        +-- leadtime/
        |   +-- LeadTimeView.tsx # Lead time analytics (4 sub-tabs)
        +-- obsolescence/
        |   +-- ObsolescenceReport.tsx
        |   +-- MachineHealth.tsx
        |   +-- index.ts
        +-- outreach/
        |   +-- OutreachPlanner.tsx # Prioritized action queue
        +-- parts/
        |   +-- PartsView.tsx    # Part frequency + 5 sub-tabs
        |   +-- BomComparison.tsx
        |   +-- DuplicateDetection.tsx
        |   +-- KitAnalysis.tsx
        |   +-- PartCatalog.tsx
        +-- quality/
        |   +-- QualityDashboard.tsx # Redbook RFC integration
        +-- sales/
        |   +-- SalesView.tsx      # Sales dashboard (6 tabs, ~1,300 lines)
        |   +-- AccountDeepDive.tsx # Customer deep-dive overlay panel
        |   +-- OrdersTab.tsx      # Standalone orders component
        |   +-- QuoteLineItems.tsx # Quote line item drilldown sub-table
        +-- views/
        |   +-- TableView.tsx    # Data table
        |   +-- GridView.tsx     # Card grid
        |   +-- ViewSwitcher.tsx # View switcher toolbar
        +-- warranty/
            +-- WarrantyTracker.tsx
~13,900 lines of TypeScript

Testing

Project Explorer uses Vitest with Testing Library, following the PSI Web App Testing standards:

AreaDetails
FrameworkVitest 4 (globals mode)
DOMjsdom environment
Assertions@testing-library/jest-dom/vitest
FixturesBuilder pattern: buildProject(), buildUser(), buildBudget(), buildMachineComponent()
CoverageV8 provider, scoped to dataService.ts with ratcheting thresholds
Tests96 unit tests covering 25+ exported pure functions

Running Tests

npm test              # Single run
npm run test:watch    # Watch mode
npm run test:coverage # With V8 coverage report

Coverage Thresholds (ratchet floor)

MetricThreshold
Statements19%
Branches21%
Functions23%
Lines20%

Coverage is scoped to dataService.ts (~2,600 lines). Thresholds increase as more tests are added.

Production Configuration

SettingValue
Azure App Serviceps-project-explorer
Resource GroupPS-WEBAPPS
RuntimeNode 24 (Linux)
Custom Domainprojects.progressivesurface.com
Private Endpoint IP10.160.140.10
Private Endpoint SubnetPS-ProdData
VNet IntegrationPS-VNMAIN / PS-WebApps
Managed Identitye5791f95-8e1d-4151-9f47-2e0456c19e39
Entra App ID971a34d6-004c-40c0-86d9-f610d83a26ca
SSLWildcard cert from ps-certificates-kv

Network Architecture

Internet (blocked)
       |
       X (Public network access: Enabled, Default action: Deny)
       |
+------+----------------------------------------+
|   Azure App Service: ps-project-explorer      |
|   projects.progressivesurface.com             |
|                                               |
|   VNet Integration: PS-VNMAIN / PS-WebApps    |
+------+----------------------------------------+
       |
       | Private Endpoint (10.160.140.10)
       | PS-ProdData subnet
       |
+------+----------------------------------------+
|              PSI Internal Network             |
|                                               |
|  Users access via:                            |
|  - Onsite workstations                        |
|  - VPN connection                             |
|  - PSI Portal (portal.progressivesurface.com) |
+-----------------------------------------------+

Deployment

CI/CD Pipeline

GitHub Actions workflow on [self-hosted, psi-internal] runner, triggers on push to master:

  1. Checkout code
  2. Setup Node.js 24
  3. npm ci — install all dependencies
  4. npm run build — TypeScript check + Vite production build
  5. npm ci --omit=dev — reinstall production deps only
  6. zip -r deploy.zip dist server.js package.json node_modules public
  7. az login --identity — managed identity auth
  8. Configure app settings (WEBSITE_RUN_FROM_PACKAGE=1, disable Oryx build)
  9. az webapp deployment source config-zip — deploy zip package (10 min timeout)

Pre-processing (manual, before deploy)

Quote revenue and quality data must be regenerated when new source data is available:

# Quote revenue — requires S:\UnixShare and API access
node scripts/process-quotes.mjs
# Output: public/RawData/quote_revenue.json (~2 minutes)
 
# Redbook quality data
node scripts/process-redbook.mjs
# Output: public/RawData/redbook_data.json

Local Development

# Clone repository
git clone https://progressivesurface.ghe.com/ProgressiveSurface/project-explorer.git
cd project-explorer
 
# Install dependencies
npm install
 
# Start dev server (frontend only)
npm run dev
# Opens at http://localhost:5173
 
# Start with Express server (machine status API)
npm run build && npm start
# Serves at http://localhost:8080
 
# Build for production
npm run build
 
# Run tests
npm test
 
# Run linter
npm run lint

Key Design Patterns

CSS Grid App Shell

The root layout uses h-screen grid grid-rows-[48px_40px_1fr] overflow-hidden. Header and toolbar are fixed-height rows; content fills remaining space. All views use h-full flex flex-col with flex-shrink-0 for stat cards/tabs and flex-1 overflow-auto for scrollable content. No calc(100vh-Npx) anywhere.

Overlay Panels

Both the Alert Dashboard and Project Detail Panel render as fixed overlays (not in page flow). Alert panel slides from the right at z-50; detail panel overlays with semi-transparent backdrop. Content height is constant regardless of panel state.

Retrofit Detection

Projects with 5-digit numbers starting with 9 are automatically flagged as retrofits and linked to parent machines via serial number extraction from descriptions.

Client-Side Data Processing

All XML/CSV parsing happens in the browser via dataService.ts. No server-side database is required for core project data — only the machine status feature uses SQLite on the Express server.

Parent Company Grouping

The Customer 360 view uses name-based heuristics to group individual account locations into parent companies (e.g., “Pratt & Whitney - Columbus” and “Pratt & Whitney - East Hartford” roll up to “Pratt & Whitney”).

Quote Revenue Attribution Hierarchy

  1. Machine: Quote linked to SPN(s) via SPN.QUOTES.1287 export → attribute to those specific machines
  2. Machine (inferred): Single-machine customer → auto-attribute all quotes
  3. Site: Group by ShipTo number when multiple machines exist
  4. Customer: AFTEC crosswalk (CUSTOMER.1287 → ACCOUNT.1287), with fuzzy name-matching fallback
  5. Unmatched: No crosswalk or name match → visible in unmatched bucket for manual review

Revenue Data Sources

Two overlapping data sources for revenue:

  • Project orderTotal (from Project1287List.xml): The order value at time of order for each machine/retrofit
  • totalInvoiced (from invoiceline.csv via quote_revenue.json): All billing — machines, parts, service, everything

These overlap but don’t perfectly align (different systems, timing, scope). The app shows both without trying to subtract one from the other. Scoring uses max(orderValue, invoiced) to avoid double-counting.

TypeScript Strict Mode

noUnusedLocals and noUnusedParameters are enforced. All data types are defined in src/types/index.ts (~500 lines of type definitions including QuoteStatus, QuoteLineItem, OpenOrder, QuotePatterns, BehaviorFlags).

Quote Revenue Pipeline Details

The process-quotes.mjs script is the bridge between AFTEC ERP data and the Project Explorer UI:

Input Sources

SourceTypeRecordsKey Fields
spquote.csvdgwReportFiles22,364Quote No, Date, Amount, Customer No, Ship info
invoiceline.csvdgwReportFiles163,306Customer No, SO No, Invoice Date, Ext Price, Quote No
spquoteline.csvdgwReportFiles54,970Quote No, Part No, Qty, Price (line items)
CUSTOMER.1287API export912Field 3 = AccountNumber (crosswalk)
ACCOUNT.1287API export4,851Field 17 = CustomerNumbers, Field 20 = SPNs
OPEN.QUOTE.HEAD.1287API export31,128Open quote IDs (status determination)
SPN.QUOTES.1287API exportvariesSPN → quote number linkage (machine attribution)
EMPLOYEE.PUBLIC.1287API exportvariesEmployee ID → first/last name (seller resolution)
SOREQAPI exportvariesSO line → quote number bidirectional mapping
VB_OPENORDLISTSubroutine callvariesAll open sales orders with SO#, value, shipped, type

Processing Pipeline Steps

  1. Load CSV files — spquote, invoiceline, spquoteline from dgwReportFiles
  2. Export ERP tables — CUSTOMER, ACCOUNT, OPEN.QUOTE.HEAD via paginated API
  3. SPN attribution — Fetch SPN.QUOTES.1287, build quote-to-machine mapping
  4. Seller resolution — Fetch EMPLOYEE.PUBLIC.1287, map employee IDs to names
  5. Open orders — Call VB_OPENORDLIST subroutine for active sales orders
  6. SOREQ linkage — Export SOREQ table for quote-to-SO bidirectional mapping
  7. Process quotes — Status determination, crosswalk, line items, stale flags
  8. Fuzzy matching — Name-based fallback for unmatched customers (strip INC/LLC/CORP, case-fold)
  9. Quote intelligence — Duplicate/revision detection, behavior flag computation per customer
  10. Output — Write quote_revenue.json with all enrichment data

Status Determination Logic

For each quote in spquote.csv:
  1. If Quote No appears in invoiceline.csv -> status: 'won'
  2. If Quote No linked to open SO via SOREQ -> status: 'ordered'
  3. If Quote No in OPEN.QUOTE.HEAD.1287 AND <=365 days old -> status: 'open'
  4. Otherwise -> status: 'closed'

Additional flags:
  - stale: true when non-won quote is >90 days old
  - ageDays: days since quote date
  - lineItems: part-level detail stored for open and won quotes (abbreviated keys)

Output Stats (as of February 2026)

MetricValue
Total quotes processed22,364
Won (invoiced)3,492
Open (active in ERP, ⇐365d)varies
Ordered (linked to open SO)varies
Closed (expired/lost)varies
Won quote value$18.3M
Total invoiced revenue$859M
Crosswalk match rate96.5%+ (improved with fuzzy matching)

Server Component

server.js provides:

  • Static file serving: Serves the Vite build output (dist/) and public/ data files
  • Machine Status API: SQLite database for shared machine status tracking
    • GET /api/machine-status/:serialNumber — get machine status
    • POST /api/machine-status — update machine status
    • Falls back gracefully if better-sqlite3 native module is unavailable
  • Service Model API: Stores customer service model preferences
    • POST /api/service-model — save service model selection per account

Implementation Phases

Project Explorer was built incrementally across 5 major phases:

PhaseFeatureKey Deliverables
1AShared Machine StatusSQLite + Express API + localStorage cache
1BCustomer 360Revenue breakdown, contacts, engagement timeline
1CAlert DashboardWarranty, obsolescence, engagement, retrofit alerts
2AFull Fleet ComponentsManufacturer EOL reference data
2BCustomer LTV ModelprojectedCustomerLTV function
2CService SpectrumOwn / service-contract / managed / pay-per-part models
3APart Frequency DashboardPartsView with reuse categorization
3BBOM ComparisonCross-project Jaccard similarity by machine type
3CStandard vs Custom BOMGreen/yellow/red reuse color coding
4ALead Time AnalyticsYear trends, budget variance, machine type stats
4BLabor BenchmarksAvg hours per dept per machine type
5APart Proliferation EngineDuplicate detection, kit analysis, searchable catalog
5BOutreach PlannerPrioritized action queue with CSV export
5CQuality IntelligenceRedbook RFC integration, quality alerts
6ASales Analytics DashboardPipeline, conversion, customers, sellers, orders, trends
6A.1Pipeline Data QualitySPN attribution, seller names, stale flags, fuzzy matching
6A.2Quote EnrichmentLine items, open orders, SOREQ linkage, quote intelligence
6A.3Conversion IntelligenceBehavior flags, duplicate detection, account deep-dive

Last updated: February 2026

Graph View

Backlinks