Get Custom Price in Oracle Advance Pricing

Get Custom Price in Oracle Advance Pricing
QP_CUSTOM.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. This function is used to capture logic to derive a value that can be substituted as a formula line step value, list price (dynamic or static), or a modifier line (lump sum, percent, new amount).

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.

Step 2:  Define Formula

Define a Pricing Formula and source a formula line as Function.

Step 3:  Find Formula id

Get the Formula_id either from the table or using the examine menu (Help -> Diagnostic -> Examine) in oracle application.

Step 4:  Implement Get_Custom_Price package body.

The QP_CUSTOM.Get_Custom_Price() package specification is seeded as part of the Advanced Pricing installation. We can customize this package as per our requirements and accordingly we need to write the logic in the package 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.
There are two methods to obtain the parameter values :

1. Get from the p_req_line_attrs_tbl parameter (only Pricing and Product Attributes are available in the p_req_line_attrs_tbl parameter).
2. Get from the request type’s global structure. (Limitations explained below)

For example, OE_ORDER_PUB.G_LINE.line_id/ASO-PRICING_INT.G_LINE_REC.QUOTE_LINE_ID (global variable) can be used in the package body to reference an order/quote line and to retrieve information specific to the current order/quote line only(There are limitation of these global variables to use in the get custom price procedure). It is recommended to use the  p_req_line_attrs_tbl parameter from the procedure to refer quote/order lines and calculate the prices.
Sample Package Body
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_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL) RETURN NUMBER IS
BEGIN
RETURN value;
WHEN OTHERS
RETURN NULL;
END get_custom_price;
END qp_custom;
Key Notes :

1. Use a common PL/SQL package for all functions custom pricing functions.
2. Modularize the PL/SQL code outside GET_CUSTOM_PRICE. GET_CUSTOM_PRICE simply becomes a wrapper to the custom function. The custom function can then be reference outside a request for price.

WARNING: Your Get_custom_price routine should not issue any database commit.Issuing a commit will corrupt the Transaction data as well as Pricing data.