Get Custom Price

This chapter covers the following topics:

Overview of Get_Custom_Price Implementation

The actual value of a modifier or price is calculated using the Get_Custom_Price function. The following is an example of the Get_Custom_Price function is: my price is 5% less than the competitor's price (your price is 95% of competitor's price), where the competitor's price is drawn from another custom database or a PL/SQL call.

An overview of the tasks that are involved in the implementation of Get_Custom_Price is depicted in the following image.

Overview of Get_Custom_Price Implementation

the picture is described in the document text

The following diagram illustrates the GET_CUSTOM_PRICE function. It shows several formulas that reference price lists and discounts. When the pricing engine evaluates a formula, it passes the formula, price list, and discount information to GET_CUSTOM_PRICE. GET_CUSTOM_PRICE returns a value, and the pricing engine uses that value to calculate the formula and returns the formula value to the calling application.

The text following the diagram explains the GET_CUSTOM_PRICE function in more detail.

Get Custom Price

the picture is described in the document text

To use the Function formula type, modify the function GET_CUSTOM_PRICE. Write custom code in the function body that uses the standard input parameters. You can only write a function body that returns a number.

You can use the GET_CUSTOM_PRICE function in more than one formula. The formula ID is an input parameter that you can use to differentiate code logic among formulas.

To use the return value in a formula, do the following:

GET_CUSTOM_PRICE is in the package QP_CUSTOM and its specification is QPXCUSTS.pls. QP_FORMULA_PRICE_CALC_PVT calls QP_CUSTOM.Get_Custom_Price().

The pricing engine passes the parameters to the function. You may not change the GET_CUSTOM_PRICE parameters that are:

Implementing Get_Custom_Price

  1. Write the extension code in qp_custom.get_custom_price. If you use get_custom_price, then you must create the package body for qp_custom and create a function get_custom_price. The pricing engine calls the application programming interface (API) qp_custom.get_custom_price with the following set of parameters:

    • P_price_formula_id: IN NUMBER

      The formula ID identifies the formula from which the API is called.

    • P_list_price: IN NUMBER

    • P_price_effective_date: IN DATE

    • P_req_line_attrs_tbl: IN QP_FORMULA_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL)

      P_req_line_attrs_tbl provides information such as such as product, pricing attributes, qualifiers and special attributes and contains the following fields:

      • Line_index: Line index of the price request line

      • Attribute_type: Qualifier, product, pricing

      • Context: Context name (for example, Item)

      • Attribute: Attribute name (for example, Pricing_attribute1)

      • Value_from: Attribute value (for example, 149)

    Step Numbers

    Special attributes provide other useful information, such as step numbers for the formula line. A step number is important if more than one step exists in a formula using Get_Custom_Price. The step numbers for a formula are available in the GET_CUSTOM_PRICE API. The step number information is passed as a value in p_req_line_attrs_tbl as a last record for attribute_type=QP_GLOBALS.G_SPECIAL_ATTRIBUTE_TYPE, context=QP_GLOBALS.G_SPECIAL_CONTEXT, attribute=QP_GLOBALS.G_SPECIAL_ATTRIBUTE1.

    A DML (Insert/Update/Delete) operation is not supported in the get_custom_price routine. The pricing engine does not guarantee upgrade possibility if any engine variables are referred to the get_custom_price function.

    Note: The step number information is passed as a value in p_req_line_attrs_tbl as a last record for attribute_type=QP_GLOBALS.G_SPECIAL_ATTRIBUTE_TYPE, context=QP_GLOBALS.G_SPECIAL_CONTEXT, attribute=QP_GLOBALS.G_SPECIAL_ATTRIBUTE1.

    GET_CUSTOM_PRICE example

    CREATE or REPLACE PACKAGE BODY QP_CUSTOM AS
FUNCTION Get_Custom_Price (p_price_formula_id  IN NUMBER,
       p_list_price   IN NUMBER,
       p_price_effective_date IN DATE,
 p_req_line_attrs_tbl IN QP_FORMULA_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL)
RETURN NUMBER
is
BEGIN
if p_price_formula_id = 7538 then 
 return 14.01;
end if;
end get_custom_price;
END QP_CUSTOM;

  2. Set the value of profile QP: Get Custom Price Customized.

    The engine calls the get_custom_price function only if profile QP: Get Custom Price Customized is set to Y. If you set up a formula but not a profile, a runtime error appears. Set the profile at site level.

  3. Create a formula to use the get_custom_price function.

    The following image depicts the Pricing Formulas window:

    Pricing Formulas window

    the picture is described in the document text

    Remember to note the formula_id by using Help, Examine. Use this formula ID in the get_custom_price function to identify the formula.

  4. Attach this formula to the price list line.

    The following image shows the Price Lists window in Oracle Advanced Pricing.

    Price Lists window

    the picture is described in the document text

Get_Custom_Price_Customized

The following sample code shows how the body of the Get_Custom_Price function is coded in the file QPXCUSTB.pls. You must use the function specification of Get_Custom_Price as well as any type definitions from QPXCUSTS.pls.

The parameters to Get_Custom_Price are always fixed and cannot be customized. You can use the input parameters that are passed by the pricing engine in their custom code. The function returns a number. You can code the function to return the desired value which must be a number. The return value is used in the evaluation of the formula.

For example, consider a formula with the expression 1*2 where 1 and 2 are step-numbers. Each step-number corresponds to a formula line. Each formula line has a type.

Step 1 corresponds to a formula line of type numeric constant, with a component of 200, and Step 2 corresponds to a formula line of type function. The value that is returned by the QP_CUSTOM.Get_Custom_Price function is used as the value for this step.

To evaluate the formula, the pricing engine first obtains the value of each step and substitutes the step with its value in the expression. Step 1 is substituted by the value which is 200. Step 2 is substituted with the value returned by Get_Custom_Price which must be customized by the user (the profile option mentioned previously must also be set to Yes to use this Get_Custom_Price functionality).

Assume that Get_Custom_Price is customized as Package Body Qp_custom. The Get_Custom_Price function name and parameters cannot be customized but the body can be been customized. The parameters are:

The parameters are passed to the function by the pricing engine and can be used in the function body.

FUNCTION Get_Custom_Price (p_price_formula_id IN NUMBER, 
    p_list_price IN NUMBER,
    p_price_effective_date IN DATE,
    p_req_line_attrs_tbl IN QP_FORMULA_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL) 
RETURN NUMBER IS
v_requested_item VARCHAR2(240);
v_weight NUMBER;
l_step_number NUMBER;
BEGIN
     IF
     p_price_formula_id = 1726 -- Assume this is the internal Id/primary key for the sample Formula 1*2 
     THEN
         Loop through the PL/SQL table of records passed by the Engine as an input parameter and containing Pricing Attributes and Product          Attributes of the Price List Line or Modifier Line to which the current formula is attached.
FOR  i IN 1.p_req_line_attrs_tbl.count LOOP
     IF p_req_line_attrs_tbl(i).attribute_type = PRODUCT
     AND
     p_req_line_attrs_tbl(i).context = ITEM
     AND
     p_req_line_attrs_tbl(i).attribute = PRICING_ATTRIBUTE1
THEN
     For this combination of Product Context and Attribute, the Attribute Value is the Inventory Item Id v_requested_item:= p_req_line_att      rs_tbl(i).value;
END IF;
     IF p_req_line_attrs_tbl(i).attribute_type = PRICING
     AND
     p_req_line_attrs_tbl(i).context = MIXED
     AND
     p_req_line_attrs_tbl(i).attribute = PRICING_ATTRIBUTE4
 THEN 
     For this combination of Pricing Context and Attribute, let's say, the Attribute Value is the Weight of the item to which the formula       is attached.
     v_weight:= p_req_line_attrs_tbl(i).value; 
     For this combination of Special Context and Attribute, the Attribute Value is the Step Number of the formula line
     IF p_req_line_attrs_tbl(j).attribute_type=QP_GLOBALS.G_SPECIAL_ATTRIBUTE_TYPE
     and p_req_line_attrs_tbl(j).context=QP_GLOBALS.G_SPECIAL_CONTEXT
     and p_req_line_attrs_tbl(j).attribute=QP_GLOBALS.G_SPECIAL_ATTRIBUTE1 THEN
     l_step_number:=p_req_line_attrs_tbl(j).value;
  END IF;
END LOOP; For Loop
RETURN v_weight;
     EXCEPTION
          WHEN OTHERS THEN
               RETURN NULL;
END Get_Custom_Price;
END QP_CUSTOM;

If v_weight has a value 1.2 then Get_Custom_Price returns a value of 1.2. The pricing engine evaluates the formula as 200*1.2 = 240.