Epicor 9.05技术问询：计算客户产品促销折扣价并生成价格表

Hey there! Let’s work through this Epicor 9.05 pricing calculation challenge together—this is a super common scenario, so I’ve got some practical steps and examples to help you nail it.

First, it’s critical to replicate the pricing hierarchy Epicor uses when generating sales order prices—this is the key to getting accurate results that match what users see when creating orders directly in the system.

Understand Epicor’s Pricing Priority #

Epicor 9.05 typically applies prices/discounts in this order (double-check your company’s setup in Sales Management > Setup > Pricing > Price Lists to confirm):

• Customer-specific fixed promotional prices (active, date-valid)
• Customer-specific percentage discounts (including tiered quantity-based discounts)
• Global promotional prices
• Base product price from the Part table

Step-by-Step Calculation Logic #

Here’s how to translate that hierarchy into a query:

1. Join core tables: Link Customer, Part, and pricing-related tables like CustomerPrice (for fixed promos) and CustomerDiscount (for percentage breaks).
2. Prioritize active fixed promos: Check for valid customer-product promo prices that are within their effective/expiration date window.
3. Apply discounts if no fixed promo exists: If there’s no active fixed price, take the part’s base price and apply the customer’s applicable discount. For tiered discounts, filter to the relevant quantity threshold (use the minimum tier if you’re calculating a base price without order context).
4. Fallback to base prices: If neither promos nor discounts apply, use the part’s base price.

Example SQL Query (Tweak to Your Schema) #

This simplified query implements the logic above—adjust table/field names to match your Epicor 9.05 database:

SELECT
    c.CustNum,
    c.Name AS CustomerName,
    p.PartNum,
    p.Description AS ProductDescription,
    -- Calculate final price using priority hierarchy
    COALESCE(
        -- First: active customer-specific fixed promo price
        cp.Price,
        -- Second: base price minus customer discount (if applicable)
        p.Price * (1 - ISNULL(c.DiscountPercent, 0)/100),
        -- Fallback: part's base price
        p.Price
    ) AS CalculatedSalesPrice,
    -- Include promo details for transparency
    cp.EffectiveDate AS PromoEffectiveDate,
    cp.ExpirationDate AS PromoExpirationDate,
    c.DiscountPercent AS CustomerDiscountPercent
FROM
    Customer c
-- Use CROSS JOIN to get all customer-product pairs; switch to INNER JOIN if you only want existing relationships
CROSS JOIN
    Part p
LEFT JOIN
    CustomerPrice cp 
    ON c.CustNum = cp.CustNum 
    AND p.PartNum = cp.PartNum
    -- Filter only active promos
    AND GETDATE() BETWEEN cp.EffectiveDate AND cp.ExpirationDate
WHERE
    -- Add filters for active customers/products as needed
    c.Status = 'Active'
    AND p.Status = 'Active'

Note: For tiered discounts, add a join to CustomerDiscount and use a CASE statement to select the correct discount based on your target quantity. For example:

CASE 
    WHEN @TargetQty >= cd.MinQty THEN cd.DiscountPercent 
    ELSE c.DiscountPercent 
END AS AppliedDiscount

Storing Prices for Your Internal Portal #

Once you have accurate calculated prices, here’s how to set up the independent table:

1. Create the portal table: Include fields like CustNum, PartNum, CalculatedSalesPrice, LastUpdated, and EffectiveDate to track freshness.
2. Automate refreshes: Use Epicor’s Task Agent or a SQL Server Agent job to run the calculation query on a schedule (e.g., nightly) to keep the portal table up-to-date.
3. Handle edge cases: Add logic to flag expired promos, update prices when part base prices change, or when customer discounts are modified.

Critical Validation Steps #

• Test with known values: Run the query against a few customer-product pairs where you know the expected sales order price to confirm your logic matches Epicor’s behavior.
• Check customizations: If your Epicor instance has custom pricing rules, adjust the query to account for those (e.g., custom fields for special discounts).
• Date filter rigor: Never skip the effective/expiration date checks—this ensures you don’t use expired promotions.

内容的提问来源于stack exchange，提问作者Anthony Doherty