OracleAppsBlog
A day in the life of an Oracle Applications Consultant

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.

Scenario:

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

image

Step 3:  Find Formula id

Find the Formula_id using Examine Feature.

Navigation:  Help > Diagnostics > Examine

image

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:

    CREATE OR REPLACE PACKGE BODY QP_CUSTOM AS
    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;

    BEGIN

    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
    FROM CST_ITEM_COST_TYPE_V
    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;

    WHEN OTHERS
    RETURN NULL;

    END get_custom_price;
    END qp_custom;

     

    Posted by Suv Biswas on 10/25 at 07:55 AM
    1. Suv,

      Very neat article.

      In your get_custom_price function example you have used

      ——-
      L_item_id := OE_ORDER_PUB.G_LINE.inventory_item_id;
      L_organization_id := OE_ORDER_PUB.G_LINE.ship_to_org_id;
      ———

      We did the exact same thing on a project and I wanted to share our learning from it.

      This works very well when one line is priced. However if you are pricing an entire order (say using Price Order), then OE_ORDER_PUB.G_LINE structure can keep information on only one line, and regardless of which line is being priced by QP (since OM submits the entire batch to QP together) you will get the same ‘item number’ and ‘Warehouse’ (usually the one pertaining to the line where your cursor is, or the last line).

      As I said, we made the same mistake, and learnt that we should only use Pricing/Qualifier Attributes in Get Custom Price. The other obvious advantage of using just the Pricing/Qualifier Attribute is that you can use the same function regardless of which module is trying to price (in our case Order Capture, iStore etc used the same price list).

      Again this was a very well written and educating article.

      regards,

      Pankaj

      Posted by Pankaj Jain  on  12/27  at  06:39 AM
    2. I’ve put the profile ‘QP: Get Custom Price Customized’ to ‘Yes’ and when I go to the Formula Setup form, I can’t get the formula type ‘function’ appearing even if the lookup for it is enabled. Any idea on how to resolve this issue?

      Posted by .(JavaScript must be enabled to view this email address)  on  01/16  at  05:53 PM

    Add Comment Information Here

    Please note that comments will only be accepted from valid members of this site who provide feedback that is beneficial to readers of the blog. ALL comments (even those from members) will be subject to moderation.

    Smileys

    Notify me of follow-up comments?

    Submit the word you see below: