Friday, November 12, 2004
Basic Vs Advanced Pricing
A very common frequently asked question about Oracle Advanced Pricing is: “What are the additional benefits of Oracle Advanced Pricing over the basic Pricing that are included with Order Management ?”. I will try to list down the additional features that Advanced pricing provides. The list is probably not comprehensive but will cover the major differences. Any suggestion/comments on this list for missing features are welcome.
Due to some technical difficulties in putting tables wider than 520 pixels here I moved the comparison table here.
e-mail this article • Administration •Frequently Asked Questions (FAQ) •Industries •Manufacturing •Modules •Advanced Pricing •Contracts •Marketing •Order Management •Purchasing (PUR) •Sales • (0) Comments • Bookmark this to del.icio.us • Permalink
Monday, October 25, 2004
Get_Custom_Price() API : The Power of Oracle Advanced Pricing
In my previous post I tried to demonstrate one powerful extensibility feature of Oracle Advanced Pricing. Get_Custom_Price public API is another such feature that finds its use in many tailor-made, complex pricing requirements. Get_Custom_Price API provides a user hook enabling us to retrieve pricing information from an external system and to use it in a formula. Oracle white paper Don’t Customize, Extend! in Metalink demonstrated the use of it with sample code. Here we will take a very common and simple pricing scenario to show the use of this public API.
Apply 10% Margin on cost of the item. (Costing Method = Standard)
Step 1: Setup system profile
Set the profile option ‘QP: Get Custom Price Customized’ to ‘Yes’. If the profile is
set to ‘Yes’, then the pricing engine would execute this function if it is attached to a formula.
Navigation: System Administrator > Profile > System
Step 2: Define Formula
Define a Pricing Formula and source a formula line as Function.
Navigation: Pricing Formulas > Formulas Setup
Step 3: Find Formula id
Find the Formula_id using Examine Feature.
Navigation: Help > Diagnostics > Examine
Step 4: Create Get_Custom_Price package body.
The QP_CUSTOM.Get_Custom_Price() package specification is seeded as part of the Advanced Pricing installation. Users wishing to customize this package should write their own body for this package specification.
Get_Custom_Price Function parameters are:
All the parameters are passed to the function by the Pricing Engine and can be used in the function body. OE_ORDER_PUB.G_LINE.line_id and other global variables can be used in the package body to reference an order line and to retrieve information specific to current order line.
A typical Get_Custom_Price package body may look like:
CREATE OR REPLACE PACKGE BODY QP_CUSTOM AS
P_pricing_formula_id IN NUMBER,
P_list_price IN NUMBER,
P_price_effective_date IN DATE,
P_req_line_attrs_tbl IN qp_formula_pricecalc_pvt.req_line_attrs_tbl) RETURN NUMBER IS
C_cost_plus_formula CONSTANT NUMBER := 6248;
L_item_id := OE_ORDER_PUB.G_LINE.inventory_item_id;
L_organization_id := OE_ORDER_PUB.G_LINE.ship_to_org_id;
If p_price_formula_id = c_cost_plus_formula THEN
WHERE inventory_items_id = l_inventory_item_id
AND organization_id = l_organization_id
AND cost_type_id = 1;
e-mail this article • Modules •Advanced Pricing •Order Management • (2) Comments • Bookmark this to del.icio.us • Permalink
Thursday, October 21, 2004
Pricing Scenario: How to use 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.
- An item from Inventory is assigned to a contract line and has a quantity of 1 with a UOM of Each
- 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
Function get_weight( p_item_id number, p_organization_id number) is
select attribute5 - DFF that stores weight
where inventory_item_id = p_item_id
and organization_id = p_organization_id
when others then
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
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. I will show the use of it in my next post. Metalink contains a very well-written white Paper (1.44MB) that explains the use of this functionality.
e-mail this article • Industries •Manufacturing •Modules •Advanced Pricing •Contracts •Order Management • (0) Comments • Bookmark this to del.icio.us • Permalink
Wednesday, July 21, 2004
New Oracle Tutor, OPM, Order Management and Logistics Author
This post contains details of a new author that has joined OracleAppsBlog who has experience in the areas of Oracle Tutor, Process Manufacturing, Order Management and Logistics
Chris Killington - a technical writer and consultant who works directly for companies implementing Oracle 11i Tutor, as well as a Tutor Trainer/Consultant/Technical Writer for Oracle Corporation (when required) - has joined OracleAppsBlog as an author. He has worked in the Process Manufacturing, Airline and Education industries using the Tutor methodology to identify business processes, documenting them in the Tutor format, creating student guides, building online document repositories and conducting end user training. Chris has also worked as an OPM4.10.10 Order Management and Logistics consultant in Malaysia, and Order Management Consultant in China.
His Tutor experience spans 5 years. In this time Chris has implemented Tutor in Australia, New Zealand, China and Malaysia and has used the courseware output, from these implementations, to ‘speed up’ the learning curve for implementation teams in Europe and the United states.
I wanted to welcome Chris to the Blog and introduce him to all the other members. It’s not often you come across someone with experience in Oracle Tutor and I am sure his contributions in this area will be very useful to readers.