From my first day in IT, I kept hearing the same thing: “we're going to implement an MRP — it will run recursively from the society that sells to the end customer all the way down to purchasing.” It sounded ambitious. Initially I didn't fully grasp what it meant.
As I started building queries and studying the SageX3 database structure, I understood why it hadn't happened: the processing time would have been prohibitive. By the time the code finished running through Syracuse, new orders would already have come in.
The challenge was similar to the Dynamic Planning Matrix — planning production across multiple items and sites — but with a critical difference: these items had a Bill of Materials. Finished products shared a common core, but differed in their finishing. Planning from descriptions alone was unreliable. Descriptions are vague and inconsistent. Codes are not.
The existing approach to material separation was guided by descriptions. The error rate reflected that.
Rather than attempting a full recursive MRP — which would have been slow and complex — I kept it simple. I wouldn't traverse the entire BOM. I would only look at the finishing layer, where the differences between products actually live. The shared core, identical across all products, wasn't worth the processing cost.
The system would work from SageX3 BOM data, but filtered and targeted. Codes only — no descriptions.
The planning matrix was extended to query the SageX3 BOM for each item, extracting the finishing components that differentiated one product from another. The result was fed into a set of sheets, one per machine in the production process. Each sheet was populated sequentially — some automatically, some semi-automatically — using item codes: finished products on some sheets, raw materials on others.
The sheets gave each step in the process a single source of truth: a unique code. Whoever was separating materials no longer had to interpret a description — they followed a code.
Material separation errors dropped to near zero almost immediately. When you separate by code, there is no ambiguity. Planning time fell from days to one hour.
The BOM query was intentionally shallow — finishing layer only. A full recursive traversal of the BOM was never implemented, which means the system wouldn't catch issues deeper in the component tree. For the products in scope, this was sufficient. For a more complex product mix it would not be.
The MRP project the group had been pursuing was still stagnant when I left. The estimated execution time for a full run was 3.5 days — which makes it functionally useless for operational planning. The processing problem was never solved.
The same prototype ceiling applies here: Excel and ODBC, fast to build and deploy, but not a permanent architecture.
The MRP ambition the group had from the beginning remains valid. The groundwork is there — BOM structure in SageX3, SQL queries that can traverse it, and a proven pattern from this project. What it needs is a proper implementation outside of Excel, with processing moved to the database layer.