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 Customers 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 Managers
  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)
• ltscreen shotsgt

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
• ltscreen shotsgt

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 wasnt 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 Managers
  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_lis
  t)
• Use the API to load modifiers for the pricing
  levels and each surcharges(qp_modifiers_pub.proce
  ss_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_con
  texts 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