OracleAppsBlog

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.

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

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:

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;