How To Get Inventory Item Id In Pricing Request Api In Oracle Apps R12

20/34

Get Custom Price

This chapter covers the following topics:

Overview of Get_Custom_Price Implementation
Implementing Get_Custom_Price
Get_Custom_Price_Customized

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:

Set up a formula having a formula line of type Function.
Set the system profile option QP: Get Custom Price Customized to Yes at the Site level. If the pricing engine evaluates custom code within the GET_CUSTOM_PRICE function and the profile option is No, the formula calculation fails, and the calling application processes the failure.

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:

p_price_formula_id: Primary key of the formula that uses the Get_Custom_Price function.
p_list_price: List price on the price list line to which the formula using Get_Custom_Price is attached. May be null.
p_price_effective_date: Pricing Effective Date, the date for which the pricing engine is evaluating the formula.
p_req_line_attrs_tbl: PL/SQL table of records containing context, attribute, and attribute value records for product and pricing attributes and a column indicating the type (Product Attribute or Pricing Attribute). The engine passes only the pricing and product Attributes of the price list line to which the formula is attached.

Note: The step numbers for a formula are available in the GET_CUSTOM_PRICE API.

Implementing Get_Custom_Price

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;
```
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.
Create a formula to use the get_custom_price function.

The following image depicts the Pricing Formulas window:

Pricing Formulas window

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

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

Price Lists window

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:

p_price_formula_id: Primary key of formula that uses Get_Custom_Price function.
p_list_price: List price of the price list line to which the formula using Get_Custom_Price is attached. This can have null value.
p_price_effective_date: Date of formula evaluation by the pricing engine.
p_req_line_attrs_tbl: A PL/SQL table of records containing context, attribute, attribute value records for product and pricing attributes and a column indicating type (product attribute or pricing attribute). The engine passes the pricing attributes and product attributes of the current line to which the formula is attached.

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.