A day in the life of an Oracle Applications Consultant

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;