Skip to main content

Price breaks based on Weight?

This post describes how to use the attribute mapping method in Oracle Advanced Pricing to implement a complex contract pricing rule
Recently I came across the following pricing requirement that seemed to be tricky at first glance: -
Use price breaks based on a weight of an item as defined in a DFF. 
  1. An item from Inventory is assigned to a contract line and has a quantity of 1 with a UOM of Each
  2. Each item has a break price (either by item category or the item number) based on the weight. E.g., price is $1000 < 5000 tons and $1500 if weight is between 5001 and 9999 tons and $2000 if > 10000 tons.
The Advanced Pricing allows you to hook up your SQL code to drive pricing. The attribute mapping feature of Oracle Advanced Pricing helped us to implement the above pricing rules by adding a few lines of SQL code.
First, we cannot use the standard price break functionality to achieve this since standard price break uses volume or quantity as basis. We followed the steps below:

Step 1: Create a function to get the weight of the item using item id

e.g.,
Function get_weight( p_item_id number, p_organization_id number) is
l_weight number;
begin
select attribute5 - DFF that stores weight
into l_weight
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_organization_id
return l_weight;
when others then
return 0;
End 

Step 2: Create a pricing attribute called weight (datatype: Number)

Navigation: Pricing Manager > Setup > Attribute Management > Contexts and Attribute

Step 3: Setup Attribute Mapping for this pricing attribute

Navigation: Pricing Manager > Setup > Attribute Management > Attribute Linking and Mapping > (B) Link Attributes
Attribute Mapping Method: Attribute Mapped
Level: Line
User Source Type: PL/SQL API
User Value String:pkg_name.get_weight(okc_price_pub.g_contract_info.inventory_item_id, okc_price_pub.g_contract_info.inv_org_id)

Step 4: Run ‘Build Attribute Mapping Rule’ concurrent program

Navigation: Pricing Manager > Setup > Atrribute Management > Attribute Linking and Mapping > Tools Menu

Step 5: Create Formula that uses weight to calculate price

Navigation: Pricing Manager > Pricing Formulas > Formula Setup
One of the formula line will be this newly created pricing attribute. Now the actual formula will depend on the exact requirement for calculation. For example, the formula for the example you have given can be:
Price = WT_FACTOR
Where WT_FACTOR is “Factor List” which is defined as follows:
WT_FACTOR = 1000 if weight price attribute is between 0 and 5000
WT_FACTOR = 1500 if weight price attribute is between 5001 and 9999
WT_FACTOR = 2000 if weight price attribute is between 10000 and 99999999
A new Factor List can be created from the formula form by clicking the factor button at the bottom right corner.  The formula is so versatile that you can implement it in many ways. The following formula would give the same result:
Price = WT_FACTOR * 500
Where WT_FACTOR is “Factor List” which is defined as follows:
WT_FACTOR = 2 if weight price attr is between 0 and 5000
WT_FACTOR = 3 if weight price attr is between 5001 and 9999
WT_FACTOR = 4 if weight price attr is between 10000 and 99999999

Step 6: Associate the formula with the item

This association can be in Price List or Modifier depending on the scenario. If this is only used in contract and always priced this way then you can tie this in the price list itself. If it needs more context sensitive treatment then you may have to use a modifier and use the formula in the modifier.

Step 7: Attach this price list to the contract

Attribute Mapping provide provides a logical and highly flexible setup framework that can be used to model even the most complex pricing scenario. GET_CUSTOM_PRICE() is another way of implementing fully customized pricing rules based on SQL query. 

Comments

Post a Comment

Popular posts from this blog

General Ledger FAQ

1.  What responsibility should I use when doing the set up for General Ledger? Use a seeded responsibility like 'Oracle General Ledger Super User'. You may also need to use the System Administrator responsibility. 2.  What are the pre-requisites required to define a new calendar? According to your business needs you need to decide the calendar type required i.e. monthly, weekly or biweekly, the number of periods, adjusting periods and the maximum number of periods within a fiscal year. 3. What are the pre-requisites required to define a new Ledger? Define a Calendar, Chart of Accounts and enable the functional Currency  and convention of subledger accounting method. 4. Why must I check the calendar definition before assigning to a Ledger? The calendar definition cannot be changed once it is assigned to a set of books so it is very important to check that the calendar definition is suitable to the specific business needs, has been d
Post a Comment
Read more

Difference Between MTS, ATO, MTO ,PTO ,CTO and ETO.

 Make-to-stock (MTS) In MTS environments, products are created before receipt of a customer order. Customer orders are then filled from existing stock, and then those stocks are replenished through production orders. MTS environments have the advantage of decoupling manufacturing processes from customer orders. Theoretically, this enables customer orders to be filled immediately from readily available stock. It also allows the manufacturer to organize production in ways that minimize costly changeovers and other disruptions. However, there are risks associated with placing finished goods into inventory without having a firm customer order or an established need. These risks tend to limit MTS environments to simple, low-variety, or commodity products whose demand can be forecasted readily.  Assemble-to-order (ATO) In ATO environments, products are assembled from components after the receipt of a customer order. The key components in the assembly or finishing process are pla
Post a Comment
Read more

Accounting entries in Oracle Purchasing and Payables

This document gives in detail different accounts used and the accounting impact of various transactions that take place in Oracle Purchasing and Oracle Payables. Both Standard costing and Average costing methods are considered. The accounts are Oracle Applications specific and might differ from the conventional accounting names. Examples are given wherever required for better understanding of the concept. The sources of these accounts are given. PURCHASING:  Receiving – For Accrual Process for perpetual Accruals Receipts for inventory purchases are always accrued upon receipt. And also use perpetual accruals for expense purchases you want to record uninvoiced purchase liabilities immediately upon the receipt of the expense goods. Receiving Account (Receiving Account) To record the current balance of the material in receiving and inspection. Where to define in Apps: Define Organization                                          Define Receiving Options
Post a Comment
Read more