Automated tiered pricing: Custom script for distribution management drives margin gains and reduces manual overrides

[donnasql]

We recently automated our tiered pricing structure for distribution operations using a custom BPM data directive. Our business model requires dynamic pricing based on customer segments and order quantity thresholds, but manual pricing was consuming 3-4 hours daily and causing margin inconsistencies.

The implementation uses a pre-processing directive on OrderDtl that evaluates customer segment classifications (Gold/Silver/Bronze) and applies tiered discounts based on order line quantities. We maintain pricing tiers in a custom UD table that maps product categories to quantity breakpoints and discount percentages per segment.

The directive fires on OrderDtl.Update before save, performs a customer segment lookup from the CustomerSegment UD table, checks the order quantity against threshold ranges, and calculates the appropriate unit price adjustment. Implementation took about two weeks including testing across our 1,200 active distribution customers. Results have been impressive - pricing accuracy improved to 99.8%, manual intervention dropped by 85%, and we’re seeing 4-6% margin improvement from consistent application of our pricing strategy.

[robert_expert]

We used a hybrid approach. Our UD_PricingTiers table has these key fields: ProdCategory (links to Epicor ProdGrup), CustomerSegment (Gold/Silver/Bronze), QtyThreshold (breakpoint), DiscountPct, and EffectiveDate. We kept Epicor’s native product groups for the category dimension but added our own segment classification in a separate UD_CustomerSegment table that links to Customer.CustNum. This gave us flexibility to reclassify customers without touching the pricing matrix.

[kevin_ops]

How do you handle price overrides for special customer agreements? We have about 15% of our customers with negotiated pricing that would conflict with automated tiering. Also curious about performance - with 1,200 customers, are you seeing any lag when the directive fires on large orders with multiple line items?

[brianadmin]

Good questions. For special agreements, we added an “Override” flag in the customer segment record. If Override=true, the directive skips automated pricing and preserves any manually entered prices. This handles contract customers cleanly. Performance has been solid - the directive completes in under 200ms per line item even on orders with 50+ lines. We optimized the UD table lookups with proper indexes on ProdCategory and CustomerSegment fields.

[betty_builder]

Great discussion here. To address the questions comprehensively:

Tiered Pricing Logic: We use a straightforward threshold evaluation with SQL-style range queries. The directive queries the UD_PricingTiers table with WHERE conditions that match product category, customer segment, and quantity ranges. For threshold boundaries, we use “greater than or equal to” logic, so an order quantity of exactly 100 units qualifies for the 100+ tier. This eliminates ambiguity.

Here’s the core logic pattern:

// Pseudocode - Pricing calculation steps:
1. Retrieve customer segment from UD_CustomerSegment table
2. Get product category from Part.ProdCode field
3. Query UD_PricingTiers WHERE Category=X, Segment=Y, Qty<=OrderQty
4. Sort results by QtyThreshold DESC, select top 1 (highest applicable tier)
5. Calculate: BasePrice * (1 - DiscountPct/100)
// See: Pricing Configuration Guide Section 3.4

Customer Segment Lookup: This happens first in the directive flow. We join Customer and UD_CustomerSegment tables using CustNum as the key. If no segment record exists, we default to “Standard” pricing (no tier discounts). The lookup is cached per order header to avoid repeated queries for multi-line orders.

Order Quantity Thresholds: Our threshold structure is simple - we define breakpoints at 10, 50, 100, 250, 500 units with progressively higher discounts. The directive evaluates OrderDtl.OrderQty against these ranges. We also implemented a “cumulative quantity” option that sums quantities across all lines of the same product category, which some customers requested for consolidated pricing.

Pre-Processing Directive: Critical choice here. We used pre-processing (before save) rather than post-processing because we need to modify the unit price before Epicor’s pricing engine calculates extended amounts and taxes. The directive updates OrderDtl.DocUnitPrice directly, and Epicor’s standard logic handles the rest. This keeps our customization minimal and maintainable.

Regarding Advanced Pricing module - we evaluated it but found it too rigid for our segment-based tiering model. The native module excels at contract pricing and promotional discounts but doesn’t elegantly handle dynamic segment classification with quantity thresholds. Custom directive gave us exactly the flexibility we needed.

One lesson learned: thoroughly test with your actual order entry workflows. We initially had issues with quote conversions where the directive wasn’t firing properly. Had to add quote-to-order conversion as a separate trigger point. Also, document your UD table structure extensively - future maintenance depends on clear understanding of the pricing matrix design.

The 4-6% margin improvement came from consistent application of our intended pricing strategy. Previously, manual pricing was inconsistent - some CSRs were too generous with discounts, others too conservative. Automation eliminated that variability and ensured every order got exactly the pricing it deserved based on segment and volume.