OracleAppsBlog

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:

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

END get_custom_price;
END qp_custom;

1. Suv,

   Very neat article.

   In your get_custom_price function example you have used

   -----
   L_item_id := OE_ORDER_PUB.G_LINE.inventory_item_id;
   L_organization_id := OE_ORDER_PUB.G_LINE.ship_to_org_id;
   ------

   We did the exact same thing on a project and I wanted to share our learning from it.

   This works very well when one line is priced. However if you are pricing an entire order (say using Price Order), then OE_ORDER_PUB.G_LINE structure can keep information on only one line, and regardless of which line is being priced by QP (since OM submits the entire batch to QP together) you will get the same ‘item number’ and ‘Warehouse’ (usually the one pertaining to the line where your cursor is, or the last line).

   As I said, we made the same mistake, and learnt that we should only use Pricing/Qualifier Attributes in Get Custom Price. The other obvious advantage of using just the Pricing/Qualifier Attribute is that you can use the same function regardless of which module is trying to price (in our case Order Capture, iStore etc used the same price list).

   Again this was a very well written and educating article.

   regards,

   Pankaj

   Posted by Pankaj Jain  on  12/26  at  10:39 PM

2. I’ve put the profile ‘QP: Get Custom Price Customized’ to ‘Yes’ and when I go to the Formula Setup form, I can’t get the formula type ‘function’ appearing even if the lookup for it is enabled. Any idea on how to resolve this issue?

   Posted by  on  01/16  at  09:53 AM

Add Comment Information Here

Please note that comments will only be accepted from valid members of this site who provide feedback that is beneficial to readers of the blog. ALL comments (even those from members) will be subject to moderation.