Advanced Pricing - An Implementation Example Barbara Mackenzie Master Lock Company February 13, 2002

1. Advanced Pricing - An Implementation ExampleBarbara MackenzieMaster Lock CompanyFebruary 13, 2002

2. Agenda Company background How we price (simplified) Design for V11 Implementation

3. Master Lock Mid-size manufacturer of keyed and combination locks Uncomplicated product But many kinds of customers mass retailers hardware chains, coops, buying groups independent hardware stores industrial suppliers institutions: schools, hospitals, prisons locksmiths Pricing must reflect differing costs of doing business

4. Oracle Apps Use GL, AR, Order Entry and Inventory Converted from 10.7 to 11.5 began in March 2001 went live December 31 Going-in plan for pricing use the conversion process as-is rework our pricing following conversion this did not work

5. Revised pricing Goals Achieve acceptable performance conversion process pricing execution in Order Entry and Order Import Position for using new pricing functionality Many decisions were based on what worked at the time we needed it to

6. How we price Discounted list prices for volume product Quantity discounting for low-volume make-to-order product Net pricing for OEM, institutional product Cost + pricing for sales to sister companies

7. Discounted list prices Computed from a base price (“suggested list price”) 4 pricing levels, assigned by class of trade and ship-to Each level has a multiplier applied to the base price Example: Base price – 9.46 Level1 multiplier – .46 Customer’s price is 4.35 Level2 multiplier – .475 4.49

8. Additional adjustments Surcharge for quantities less than a case Promotional discounts as appropriate Trade show discount New product promotions Back-to-school promotions

9. How we did it in 10.7 Lack of cascading discounts was an obstacle Prices were pre-computed and entered as end-item prices 8 prices per item – 4 Pricing Levels, Case and Each prices for each level “Octupled” the size of each price list Base prices were on the Pricing Manager’s spreadsheet, not in the database Promotions were entered as discounts

10. First trial conversion Price list conversion took more than 24 hours (for reasons there is not enough time to explain, we had a large number of price lists in 10.7) Converted to a mess of pricing attributes in 11.4 did not work was impossible to explain to users without waiting to find out if the converted data could work, we decided to scrap the converted data and re-implement our requirements

11. Design Use an item category to identify which items get retail pricingthis was already in place Load the price list with the base price Use buckets to cascade the discounts and surcharges The Pricing Level discount in Bucket 1 The Each surcharge in Bucket 2 All promotions in Bucket 3

12. Spreadsheet(qp_modifiers.xls)

13. Implementation All setup was done for both US and Canadian currencies Price lists are straightforward Using Item-based prices Create modifiers to do the discounted list price and each surcharge Build our own qualifier for “ask-for” promotions

14. Qualifier Attributes Add 2 attributes to the seeded ones: Pricing level Promotion Add to Qualifier Context flexfield definition Define data sources in Attribute Mapping Pricing level is stored in a Site Use attributecustom function retrieves the value using context of the current order “Ask-for” promotion is stored in an Order attributeno function is needed since the data is in the order context (oe_order_pub.g_hdr) <screen shots>

15. Discounted List Price Modifier (Bucket 1) List qualifier: price lists which use this pricing One modifier line per discount level Item category restricts the modifier to the right products Pricing level qualifier selects which modifier line applies Rounding quirks: The .46 multiplier started as a 54% discount To deal with rounding, it was changed to 53.99999% <screen shots>

16. Each surcharge modifier (Bucket 2) Not elegant, but it works Same list qualifiers as the pricing level Based on case size but UOM conversions are not supported for modifier quantities So, we populated an item category with the case size (trigger on mtl_uom_conversions) And defined a modifier line for each case size No line qualifiers are needed In the process of replacing this structure with another qualifier attribute and custom function

17. Promotions (Bucket 3) Promotions have to be automatic to be bucketed Ask-for wasn’t behaving predictably in early tests So, use an order header attribute to request promotions Define a qualifier attribute on the order attribute The promotion qualifier is a list qualifier So, if the promotion name is entered on the order, the promotion is automatically applied to each line

18. Conversion strategy Copy list prices from our Pricing Manager’s spreadsheet to a work table Rename SO_PRICE_LIST_LINES prior to conversion Create an empty SO_PRICE_LIST_LINES Leave discounts mostly unchanged Use the API to load the list prices from the working table(qp_price_list_pub.process_price_list) Use the API to load modifiers for the pricing levels and each surcharges(qp_modifiers_pub.process_modifiers) Converted discountschanged to promotionsall lines set to bucket 3disable, pending user review and update Write an interface to qp_preq_grp.price_request for custom programs qp_attr_mapping_pub.build_contexts is very useful This interface proved invaluable in regression testing

19. Summary We adapted V10.7 function to meet our requirements V10.7 data converted poorly into V11 It was better to re-implement than to force the conversion to work The variety of options in Advanced Pricing can be confusing Let your requirements lead you