Inventory & Work Order Reference
How inventory flows through AFTEC: tables, work order types, transaction codes, and floor stock classification.
Work Order Numbering & Prefixes
Work orders in AFTEC follow a prefix-based naming convention that determines their type and cost attribution:
| Prefix | Type | Example | Description | Job-Costed? |
|---|---|---|---|---|
F | Fabrication | F96145 | Machine/weld shop WOs for manufactured parts | Yes — linked to a job via JOB.WO.XREF |
G | Assembly | G10871 | Mechanical/electrical assembly WOs | Yes |
E | Electrical | E99101 | Electrical panel build WOs | Yes |
D | Design/Draft | D96634 | Design/drafting WOs | Yes |
B | ? | B17491 | Seen in transaction data, rare | Unknown |
C | ? | C69144 | Seen in transaction data, rare | Unknown |
{job} | Base | 2399 | The job-level WO itself | Yes |
{job}M | Miscellaneous | 2399M | Misc/floor stock WO for a job | Partially — costs hit the job but may not tie to specific BOM items |
{job}C | Controls | 2399C | Controls-related WO for a job | Yes |
{job}R | Rework? | 2399R | Seen on some jobs | Yes |
90000+ | Mixed | 92950 | Not necessarily non-job. 76% of 90000-range WOs in WIPLEDGER ARE tied to customer jobs (F-prefix fab WOs like F93760). The remaining 24% are SPEC.WO departmental overhead buckets (see below). | Varies — job-linked WOs hit COGS; SPEC.WOs hit departmental overhead |
{5-digit}W | Warranty | 1069W | Warranty work orders (4-digit job + W suffix). Not floor stock. | Yes — tied to original job |
Key Insight: Cost Attribution on 90000-Range WOs
Correction (April 2026): The 90000-range WOs were previously documented as “non-job floor stock.” Data analysis of WIPLEDGER (9,935 WOs in this range) shows 76% are tied to customer jobs via F-prefix fabrication WOs. Only 24% are jobless SPEC.WO departmental overhead buckets.
From the Feb 2026 inventory meeting (Tyler DeWitt, John Tymes, Adam Devereaux, Colin Orlando):
John Tymes: “If it’s a job work order, then that’s cost of goods attributed to a specific job. If there are these floor stock work orders… those are not job-work related. We could tally a cost for over any period of time automatically.”
Colin Orlando: “If our COGS are wrong, our profitability is also wrong. So gotta figure that one out.”
Three cost leakage paths identified:
- M work orders (e.g., 2399M) — costs partially attributed to jobs
- SPEC.WO departmental overhead WOs (e.g., 91995, 91996, 92000, 92001, 92950) — costs go to departmental overhead, not customer job COGS. These carry material ($2.0M), labor ($1.6M), and sub-assembly ($749K) costs — they are departmental overhead buckets, not traditional floor stock.
- GL account direct issues — parts issued to a GL account without any WO at all
SPEC.WO Departmental Overhead Buckets
The SPEC.WO entries in the 90000-range are long-lived departmental work orders that absorb both material and labor. They are NOT floor stock in the MRP sense (MRP type “F” is floor stock — see below).
| WO | Material $ | Labor $ | SubAssy $ | Labor Hrs | Active Since |
|---|---|---|---|---|---|
| 91996 | $1,430K | $323K | $359K | 10,165 | FY2019+ |
| 92001 | $261K | $122K | $226K | 4,093 | FY2019+ |
| 92802 | $56K | $82K | $3K | 2,016 | FY2023 |
| 92851 | $20K | $11K | $39K | 501 | FY2023 |
339 total SPEC.WO entries, $4.4M grand total (45% material, 38% labor, 17% sub-assembly). Top consumed parts are wear parts, blast media, filters, and fixtures — process services and maintenance for blast/spray operations. Annual spend growing: $135K (2016) to $581K (2024).
Actual Floor Stock (MRP Type “F”)
AFTEC classifies floor stock parts using MRP type “F” — 884 parts, $331K on-hand inventory. These are low-cost shop consumables (wire, cable, conduit, drill bits, cable ties, cord grips) that are grabbed off the shelf without WO issue transactions. Almost zero overlap with SPEC.WO consumption.
AFTEC Inventory Tables
Core Tables
| Table | Key Format | Purpose |
|---|---|---|
| PRODUCT | 1!{partNumber} | Part master — description (F1), UM (F3), MRP type (F10: P=purchased, M=manufactured), INC/OBS flag (F8) |
| ITEMQTY | 1!{partNumber} | Current inventory quantities — all qty fields are MD3 (÷1000) |
| INVHIST | 1!{key} | Transaction history — linked list via FPTRP (F18) pointer |
| INVXREF | 1!{partNumber} | Index into INVHIST — F1 = head of linked list for a part |
| OPENWO | 1!{woNumber} | Open (active) work orders — F1=Job, F2=Part, F12=Type (P/N) |
| OPENWO.1287 | 1!{woNumber} | PSI extension fields for open WOs |
| JOB.WO.XREF | 1!{job} | All WOs for a job — F1 contains VM-delimited WO list |
ITEMQTY Field Positions (Dictionary: all MD3)
| Position | Name | Description |
|---|---|---|
| F1 | ONHAND | Quantity on hand |
| F2 | ALLOC.SALES | Allocated to sales orders (shipped) |
| F3 | ALLOC.BACKORD | Allocated to sales back orders (open) |
| F4 | OPEN.PO | Open purchase order quantity |
| F5 | PO.INTRANS | PO quantity in transit |
| F6 | JOBPO | PO on order with job number |
| F7 | OPEN.WO | Open work order quantity |
| F9 | ALLOC.WIP | Allocated to work-in-process |
| F10 | IN.PO.REC | Quantity in PO receiving |
| F12 | BULK.LOC | Bulk storage bin location |
| F13 | PICK.LOC | Pick location |
| F15 | OUTSIDE | Quantity in outside operations |
| F17 | LAST.COUNT | Last cycle count quantity |
| F18 | LAST.DATE | Last date (D2/ format) |
| F21 | CCDATE | Last cycle count date (D2/) |
| F25 | GROSSQTY | Gross quantity |
INVHIST Field Positions
| Position | Name | Conversion | Description |
|---|---|---|---|
| F3 | PGM.NAME | — | Program that created the transaction |
| F7 | TRANS.DATE | D2/ | Transaction date (days since 12/31/1967) |
| F9 | QUANTITY | MD3 | Quantity (÷1000) |
| F10 | REF | — | Reference — WO number, PO number |
| F11 | TYPE | — | Transaction type code (see below) |
| F12 | AVG.COST | MD4 | Average cost (÷10000) |
| F13 | LOC | — | Location (e.g., “STOCK”) |
| F14 | LOT.NO | — | Lot number |
| F18 | FPTRP | — | Forward pointer — next transaction key in linked list |
| F22 | BINS | — | Bin location |
| F42 | GL.DOCNO | — | General ledger document number |
| F51 | USERNAME | — | User who performed the transaction |
AFTEC Numeric Conversions
| Code | Meaning | Example |
|---|---|---|
| MD3 | 3 implied decimals | Raw 3388000 → 3388.000 (÷1000) |
| MD4 | 4 implied decimals | Raw 12701 → 1.2701 (÷10000) |
| MD2 | 2 implied decimals | Raw 350 → 3.50 (÷100) |
| D2/ | Date | Days since 12/31/1967 → MM/DD/YYYY |
Inventory Transaction Type Codes
From INVHIST F11 (TYPE field). Codes may be stored with or without leading zeros.
| Code | Name | Direction | Description |
|---|---|---|---|
| 01 | PO Receipt | + | Purchase order received into stock |
| 02 | PO Return | - | Returned to vendor |
| 03 | PO Receipt (WO) | + | PO receipt against a work order |
| 04 | WO Issue | - | Generic work order issue |
| 05 | Transfer | ± | Inventory transfer |
| 06 | Adjustment | ± | Manual adjustment |
| 07 | Sales Order | - | Issued to sales order |
| 08 | Sales Return | + | Customer return |
| 09 | Scrap | - | Scrapped |
| 10 | PO Issue | - | PO issued |
| 11 | WO Material Issue | - | Material issued to a work order (most common) |
| 12 | WO Mat Return | + | Material returned from a work order |
| 13 | Transfer Out | - | Transfer out of location |
| 14 | Transfer In | + | Transfer into location |
| 15 | Physical Count | ± | Physical count adjustment |
| 20 | Cycle Count Adj | ± | Cycle count adjustment — key for inventory accuracy |
| 21 | FS Issue | - | Field service issue |
| 22 | FS Return | + | Field service return |
| 29 | Inventory Transfer | ± | Inter-location transfer |
| 31 | WO Issue (Fabrication) | - | Material issued to fabrication WO |
| 32 | WO Return (Fabrication) | + | Material returned from fabrication WO |
| 41 | Scrap (WO) | - | Scrapped against a work order |
VB_GET.ITEMHIST Subroutine
Returns 24-month inventory history. All data is in arg 1, not split between args.
Call: VB_GET.ITEMHIST(partNumber, allData_OUT, unused)
Output format: arg 1 contains 10 field-mark (char 254) delimited sections, each value-mark (char 253) delimited by period:
| Section | Content |
|---|---|
| FM[0] | Headers — period labels (“24 Month Totals”, “Month 1 02/26”, etc.) |
| FM[1] | Balance — end-of-month balance |
| FM[2] | Beginning Balance |
| FM[3] | Receipts |
| FM[4] | Issues |
| FM[5] | FS Issues (Field Service) |
| FM[6] | Sales |
| FM[7] | Scrap |
| FM[8] | (reserved) |
| FM[9] | Adjustments |
Monthly values are pre-formatted decimals (e.g., “4005.000”). The totals row (index 0) uses raw integers.
Inventory Investigation Findings (Feb 2026)
WO Reference Analysis
Across A-class parts (069995, 048754, 039496, 065133, 065134), inventory transactions reference:
| Category | Count | Description |
|---|---|---|
| F-prefix (Fabrication) | 771 txns / 525 WOs | Job-costed — tied to specific machines |
| E-prefix (Electrical) | 218 txns / 198 WOs | Job-costed — panel builds for specific machines |
| 4-digit (Direct Job) | 19 txns / 17 WOs | Direct issues to job numbers (2299, 2331, 2338, etc.) |
| 90000+ (SPEC.WO) | 6 txns / 4 WOs | Departmental overhead — process services/maintenance (not job-costed) |
| Other (D, B, C prefix) | 5 txns | Rare, likely overhead |
Cycle Count Accuracy (Tyler DeWitt)
| Period | A-Class Accuracy |
|---|---|
| Start of 2025 | ~73% |
| End of 2025 | ~mid 80% |
| Counting cadence | A=quarterly, B=semi-annual, C=annual |
Chronic Problem Parts
See C:\git\Inventory\A-Class Inventory Analysis Report.md for the full 34-part repeat offender analysis.
Key patterns:
- Phoenix Contact terminals (069995, 072212-072214): CHRONIC_SHRINKAGE + WO_OVERPICK_LIKELY — workers take more than BOM quantity
- Allen-Bradley I/O modules (048754, 039496): FLIP_FLOP — adjustments swing positive and negative
WIPLEDGER — WO Cost Ledger
The WIPLEDGER table (and CSV export wipledger.csv) contains per-WO cost breakdowns by fiscal period.
CSV location: \\ad.ptihome.com\DFS\DATA\unixshare\dgwreportfiles\wipledger.csv (99,461 rows)
Key format: {WO}-{FiscalPeriod} (e.g., F54424-2307, 92950-2509)
| Column | Description |
|---|---|
| Work Order | WO number (F54424, 92950, etc.) |
| Fiscal Period | YYMM format (2509 = Sept 2025) |
| Job No | Job number (blank for floor stock) |
| Part No | Part number or SPEC.WO |
| Type | P=Production, N=Non-assembly |
| Material $ | Material cost |
| MaterialOH $ | Material overhead |
| Labor $ | Labor cost |
| LaborOH $ | Labor overhead |
| Outside $ | Outside services cost |
| OutsideOH $ | Outside overhead |
| SubAssy $ | Sub-assembly cost |
| Labor Hrs | Labor hours |
SPEC.WO Cost Example (92950)
| Period | Material $ | Labor $ | Outside $ | Direct $ |
|---|---|---|---|---|
| 2509 | $199.98 | $0 | $0 | $0 |
| 2510 | $1,824.43 | $0 | $0 | $50.90 |
| 2511 | $983.80 | $28.83 | $0 | $31.05 |
| 2512 | $1,135.51 | — | — | — |
| 2601 | $887.15 | — | — | — |
| 2602 | $686.51 | $0 | $0 | $13.33 |
Key finding: WO numbers in the 90000-99999 range overlap with F-prefix fabrication WOs. F92945 is a Fabrication WO tied to job 2395, while 92945 without prefix is a SPEC.WO departmental overhead WO. The prefix matters — most 90000-range WOs with an F-prefix are job-costed manufacturing WOs, not departmental overhead.
OPENWO Table — Contains ALL Work Orders (Not Just Open)
Despite its name, the AFTEC OPENWO table contains both open and closed/historical work orders. The nightly CSV export (openwo.csv) has 519K rows, of which 514K are Complete and only ~1,178 are Released/Open.
| WO Status | Count |
|---|---|
| Complete | 514,011 |
| Released | 1,178 |
| Other | ~4,096 |
The 90000-99999 range includes two distinct populations: (1) F-prefix fabrication WOs tied to customer jobs (76% of WOs in this range), and (2) SPEC.WO departmental overhead WOs with no job number (24%), used for process services and maintenance. The OPENWO CSV export contains very few 90000-range records; WIPLEDGER is the authoritative source with 9,935 WOs in this range.
IBM (Issued But Missing) — Material Tracking
Last Verified: 2026-03-12 — 112 IBM records found across all WOs, 10 on active (Released) WOs.
IBM = a component part that was issued to a work order (pulled from inventory) but is physically unaccounted for on the shop floor. This is tracked at the WIPBMF component level, not at the WO header level.
WIPBMF Fields (WIP Bill of Materials)
| Field | Position | Description |
|---|---|---|
IBM.QTY | F31 | Issued But Missing quantity (MD3 = ÷1000) |
| (line note) | F27 | Single-line BOM component note |
RTS | F29 | Return to Stock flag (1 char) |
RTS.QTY | F30 | Return to Stock quantity (MD3) |
QTY.ALLOC | F15 | Quantity allocated to WO |
QTY.PER | F7 | Quantity per assembly (BOM qty) |
WIPBMF key format: 1!{WO_NUMBER}!{PART_NUMBER}
WIPBMF.NOTES Table (BOM Line Notes)
A separate table (not a field within WIPBMF) that stores multi-value notes per BOM line. Same key format as WIPBMF: 1!{WO_NUMBER}!{PART_NUMBER}.
- Field 1 contains value-mark-separated (char 253) notes
- Created and consumed by the WIPBOM desktop app (PSI.All →
PSI.WorkInProcessBOM.View) - Notes are generic BOM line notes — not IBM-specific, but often contain context about IBM issues
- The API returns both
lineNote(F27) andnotes(WIPBMF.NOTES) on IBM endpoint responses
OPENWO.1287 Related Fields
| Field | Position | Description | Values |
|---|---|---|---|
ISSUE.STAT | F11 | Pick/Issue Status | 1=Partial, 2=Fully Issued, 3=Complete |
WO.NOTES | F13 | Work Order Notes | Free text (often empty) |
SHORT.MTL | F18 | Short Material Flag | Y/blank |
WIP.ISS.LOC | F14 | WIP Issue Location | e.g. “2386 SKID A”, “E CART 5” |
WIP.MTL | F16 | Component summary | e.g. “COMP:52” (52 components) |
How to Query IBM Data
Via API (preferred):
| Endpoint | Description |
|---|---|
GET /api/ibm/dev/active | All IBM items on active (Released) work orders |
GET /api/ibm/dev/all | All IBM items across all WOs (active + completed) |
GET /api/ibm/dev/job/{jobNumber} | IBM items for a specific job |
GET /api/ibm/dev/summary | Counts by WO status, grouped by job |
Via raw UniData command (VB_SYSTEMSTAT.REV1):
LIST WIPBMF WITH IBM.QTY # "" AND WITH WOSTAT = "R" WONO PRODNO PDESC JOBNO IBM.QTY TOXML
Key Subroutines
| Subroutine | Description |
|---|---|
VB_WIPBMF | WIP BOM for single WO — returns header + component lines with qty/alloc/issued |
VB_WIPBMF.REV5 | Enhanced version with WO/WOLIST/JOB/JOBWRITE modes, bin/picking info, hold status |
Data Availability
- Not in any CSV export — IBM.QTY exists only in the live UniData WIPBMF table
- Not in Fabric/Power BI — no WIPBMF table in the ProgressiveDataSet
- No MCP tool — not yet surfaced in the PSI Machine Intelligence MCP server
API Endpoints
Inventory Endpoints
| Endpoint | Description |
|---|---|
GET /api/inventory/dev/{pn}/status | Current on-hand, allocated, available, cost, bins |
GET /api/inventory/dev/{pn}/transactions?max=N | INVHIST linked list traversal with decoded types |
GET /api/inventory/dev/{pn}/monthly | 24-month history from VB_GET.ITEMHIST |
Cost Attribution & Classification Endpoints
Note: These endpoints were originally built around the assumption that 90000-range WOs were “floor stock.” The naming is legacy — the data shows most 90000-range WOs are job-linked fabrication WOs. The SPEC.WO entries (no job) are departmental overhead for process services/maintenance.
| Endpoint | Description |
|---|---|
GET /api/floorstock/dev/{pn}/cost-breakdown?max=N | Classify transactions by WO type, compute cost per category + leakage % |
GET /api/floorstock/dev/{pn}/classified-transactions?max=N | Same as above + full classified transaction list |
GET /api/floorstock/dev/floor-stock-wos | List SPEC.WO departmental overhead WOs (90000-99999 range, no job) |
GET /api/floorstock/dev/{job}/cost-attribution | Job-level WO classification and cost attribution |
IBM (Issued But Missing) Endpoints
| Endpoint | Description |
|---|---|
GET /api/ibm/dev/active | All IBM items on active (Released) work orders |
GET /api/ibm/dev/all | All IBM items across all WOs (active + completed) |
GET /api/ibm/dev/job/{jobNumber} | IBM items for a specific job |
GET /api/ibm/dev/summary | Counts by WO status, grouped by job |
Useful Subroutines
| Subroutine | Purpose |
|---|---|
VB_PARTINFO.REV2 | Part info + standard cost (field 5 of output) |
VB_GET.ITEMHIST | 24-month history (all data in arg 1) |
VB_ITEMQTY_DETAIL.REV2 | Job-level on-hand/on-order breakdown |
VB_INVINQ.2 | Bin/lot detail (3 args, returns empty for some parts) |
Data Sources
All ERP data is exported nightly to \\ad.ptihome.com\DFS\DATA\unixshare\dgwreportfiles\. Key files for inventory/WO analysis:
| File | Rows | Description |
|---|---|---|
inventoryhistory.csv | 4.1M | Full INVHIST transaction history |
openwo.csv | 519K | All work orders (open + closed) |
wipledger.csv | 99K | WIP ledger — per-WO cost by fiscal period |
wiplabor.csv | 253K | WIP labor detail |
wiprouteline.csv | 927K | WIP routing operations |
inventory.csv | 17K | Current inventory with bin/lot detail |
partmaster.csv | 421K | Part master with cost, lead time, ABC class |
jobparts.csv | 203K | Job-to-parts mapping |
These same files are imported into Microsoft Fabric’s ProgressiveDataSet via the on-premises data gateway, refreshed daily at 5:00 AM ET.
Last updated: April 2026 — Corrected 90000-range WO classification (76% are job-linked, not floor stock). Added SPEC.WO departmental overhead documentation. Added MRP type “F” floor stock reference.