Company Type
Columbia Pipe, a commercial pipe distributor (250K active SKUs, 18 DCs); rescue of a failed JDE EnterpriseOne implementation; the SIOP framework was architected inside the recovery.
What Drove the Need
JDE ranks ABC by sales dollars, margin, or cost — never by unit volume — so high-volume/low-cost pipe items were systematically mis-stocked
Procurement planning and purchasing execution for 250K SKUs across 18 DCs were manual and error-prone
Business Impact
~99% of manual procurement planning and purchasing execution eliminated (Case 21)
Monthly ABC re-ranking cut from 16+ hours to ~9 minutes with a single custom index
Systems/Data Integration
Writes ABC rankings, lead times, safety stock, and planning codes directly to JDE item/branch and planning tables under pessimistic locking
Companion to the SIOP algorithm suite (SE-12) and DSI barcoding scripts
Design
Build order: user-defined object types → functions → procedures → main scripts, so every procedure composes tested primitives
Seven user-defined object and table types; a core package holding all hard-coded values and shared functions; Oracle analytic CUME_DIST() over the sales ledger by branch and item for the cumulative demand distribution
Purchasing lead time derived from receipt history (greatest of ceiling median and ceiling average + 2 days, floored at 3); monthly forecast spread to weeks by workday share via a two-pass calendar algorithm
BULK COLLECT … LIMIT batching with per-batch commits; pessimistic locking on every JDE update; every written value validated against its UDC table
Developed in DV; logic and test-scenario review with Central Planning power users; DBA code review; promotion to CRP with power-user execution rights; then production
Technology Stack
Oracle PL/SQL — user-defined objects, packages, procedures, functions
JDE EnterpriseOne Oracle schema (F42199 sales ledger, F4102 item/branch, F43090, F41001, F0007)
Custom index F42199_CPSCPME01
High-Volume/Low-Cost Inventory ABC Analysis Algorithm
PL/SQL analytic engine ranking every item/branch by unit volume with monthly recategorization — A/B items forecasted, C items on reorder points — feeding planning codes automatically into JDE. Ran hourly through monthly with full change-audit and run logs.
