A day in the life of an Oracle Applications Consultant

Advanced Pricing

Thursday, January 06, 2005

GET_CUSTOM_PRICE - Do’s and Don’ts

Suv Biswas has posted a very good article written on how to use GET_CUSTOM_PRICE (it is listed under Advanced Pricing section). It gives you a step by step tutorial on how to use GET_CUSTOM_PRICE. I have used this feature in multiple implementations. At one of the client sites, I had some come up with some Dos and Don’ts for the developers using GET_CUSTOM_PRICE. I have re-written it to fit the format of this blog, I think that it would be a nice extension to Suv’s article, I also hope that it might come in handy (and save some time) for those trying to use this functionality.


=> Don’t use global memory structures like OE_ORDER_PUB.G_LINE, ASO_PRICING_INT. G_LINE etc in Get Custom Price.
Since these structures are used to source Pricing and Qualifier Attributes (seeded or custom), there is a tendency to use these in GET CUSTOM PRICE as well. However there are two major differences between the way Sourcing rules for Pricing and Qualifier Attributes are used and the way GET_CUSTOM_PRICE is used.

a) When any module calls Pricing for more than one line it calls QP in a batch. The calling application loads all the pricing and qualifier attributes before the call to QP. As a result when the sourcing for these attributes is called it has access to the global structures (like OE_ORDER_PUB.G_LINE etc.). A structure with all these attributes is used as an input to QP Call.

So when GET_CUSTOM_PRICE gets called, OE_ORDER_PUB.G_LINE (in our example) has information for any one line (typically the last line). The reason is simple. Pricing Attributes and Qualifier Attributes are input parameters for QP.

b) In a lot of implementations, more than one application would use the same Price List (or Modifier). for example, Quoting, iStore and OM would often use the same Price List and Modifiers. You will notice, that while there is an option for writing different sourcing rules for different calling application there is no such thing for GET_CUSTOM_PRICE. The reason is simple (as explained in previous point). The pricing and qualifier attributes are loaded by the calling application. QP just concerns itself with Pricing and Qualifier Attributes which in effect are input parameters to QP. It is specifically done so that various modules can call QP and it is not tightly coupled with any one.

=> Make sure that the pricing attributes used in GET_CUSTOM_PRICE is either used somewhere in an active pricing setup in Setups or the profile option “QP: Build Attributes Mapping Options” is set to No (or QP: Check For Active Flag is set to No).

The profile option “QP: Build Attributes Mapping Options” controls whether all or only the used pricing/qualifier attributes are sourced. Setting the profile option to “No” is pretty wasteful, since there are more than 200 seeded pricing and qualifier attributes and it is highly unlikely that you use more than 50. You might end up creating a dummy price list to use the pricing and qualifier attributes that are not used anywhere else but are used in GET_CUSTOM_PRICE.

=>  If you are using GET_CUSTOM_PRICE in various Pricing Formulae for different functionality, set a flexfield aside to identify the formula.

One of the inputs to GET_CUSTOM_PRICE is pricing_formula_id, but the value of this is likely to change between development, test and production instances, so it is, obviously, not advisable to hard code the pricing_formula_id in the function. The pricing formula name field is update-able, it is possible for someone to inadverdently change it, for the users to demand a more friendly name. The additional advantage of using the flexfield value is that if you have more than one formula using the same functionality you don’t have to code for it.

=>  Minimize number of Loops

One has to loop through the structure “p_req_line_attrs_tbl” to get to the required value. Say :

  FOR i IN 1..p_req_line_attrs_tbl.COUNT
  if p_req_line_attrs_tbl(i).attribute_type = ‘PRICING’ and
    p_req_line_attrs_tbl(i).context = ‘PRICING ATTRIBUTE’ and
    p_req_line_attrs_tbl(i).attribute = ‘PRICING_ATTRIBUTE31’ then
      if X_FORMULA_TYPE = ‘A’then
        x_return_value := xxxx_get_price_details_a ((to_number(p_req_line_attrs_tbl(i).value)));
      elsif X_FORMULA_TYPE = ‘B’ then
        x_return_value := xxxx_get_price_details_b ((to_number(p_req_line_attrs_tbl(i).value)));
        end if;
  end if;

in this case PRICING_ATTRIBUTE31 is setup to source line_id, so we find the required value in p_req_line_attrs_tbl(i).value
If get_custom_price is being used in more than one formula, then make sure that (a) the loop runs only once and (b) you exit the loop as soon as you are done. PL/SQL can be pretty slow, and the time consumed is difficult to find as it does not appear in your standard TKPROF output. Another reason why (as covered in the next point) the value of “QP: Pass Qualifiers to Get_Custom_Price API” should be set to No unless you really require it.

=>  Set correct value for “QP: Pass Qualifiers to Get_Custom_Price API”.

Qualifier Attributes are not automatically available to GET_CUSTOM_PRICE. If you want to use value of a qualifier attribute, make sure that the value of profile option “QP: Pass Qualifiers to Get_Custom_Price API” to ‘Yes’. As explained in the previous point, set the value to No unless it is really required to be ‘Yes’.

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.

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:


  • p_price_formula_id – formula identifier
  • p_list_price – Price List Price of the line to which formula is attached.
  • p_price_effective_date - Current date
  • p_req_line_attrs_tbl -  Pricing Attributes and Product Attributes of the current line
  • 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:

    FUNCTION get_custom_price(
    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

    L_item_cost NUMBER;
    L_item_id NUMBER;
    L_organization_id NUMBER;

    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

    SELECT item_cost
    INTO l_item_cost
    WHERE inventory_items_id = l_inventory_item_id
    AND organization_id =  l_organization_id
    AND cost_type_id = 1;

    End if;

    RETURN l_item_cost;


    END get_custom_price;
    END qp_custom;


    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.

    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


    Function get_weight( p_item_id number, p_organization_id number) is
    l_weight number;
    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;

    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. 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.


    Friday, June 25, 2004

    Oracle Advanced Pricing Module

    This post contains details of a new author who has joined OracleAppsBlog and will be blogging under the Advanced Pricing Category

    Suvabrata Biswas, an Oracle Applications Consultant who runs a consulting company based out of Houston Texas, is joining OracleAppsBlog and will be blogging under the Advanced Pricing Category. This category didn’t exist before so I have added it.

    Suv has broad functional experience in Advanced Pricing, Bills of Materials, Assemble to Order, Quality, Engineering, Inventory, Work in Process, Procurement, Cost Management, Enterprise Asset Management, Order Management, Planning and MultiOrg. He also has broad technical experience with Oracle Application’s technical architecture, Oracle tools and operating systems such as Unix and NT. You should also check out his companies site. You can read Suv’s membership profile here or if you’re a member you can e-mail Suv via the profile e-mail console.

    Welcome to OracleAppsBlog and we look forward to your contributions grin

    Page 1 of 2 pages  1 2 >