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.
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:
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
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
C_cost_plus_formula CONSTANT NUMBER := 6248;
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
WHERE inventory_items_id = l_inventory_item_id
AND organization_id = l_organization_id
AND cost_type_id = 1;