Pricing Engine

Pricing Engine steps
The pricing engine performs the following functions:

• Prepares the price request structure
  The calling applications submit price requests, which the pricing engine configures into a pricing request structure. The pricing request structure provides information about all the qualifiers and product pricing attributes.
• Selects applicable price lists and modifier lists
  This function selects which price lists are eligible for the current pricing request. It uses the qualifiers and pricing attributes to select an eligible list of prices or modifiers that it can apply to the pricing request lines according to the certain rules.
• Determines base list price
  This function takes the validated price list lines and applies them to the pricing request lines. You can specify the list price on a price list as unit price, percent price, or formula.
• Applies benefits and adjustments (from modifier lists) to list price to determine selling price
  This function takes the validated modifier list lines and applies them to the pricing request lines to provide price adjustments, such as discounts, price breaks, surcharges, coupons, item and term substitutions, and other item discounts. Discount and surcharge modifiers affect the selling price; freight charge modifiers do not affect the selling price.
• Calculates final selling price

Advance Pricing APIs and Key Tables

Oracle Advance Pricing provides a set of APIs which can be used to perform some of the key pricing functions. The below are some of the important APIs.

QP_PREQ_GRP.Price_Request: This is the Price Request API to calculate the base price.

This is a public API that allows you to get a base price and to apply price adjustments, other benefits, and charges to a transaction. This API can be called any module to calculate the base price which are integrated with the Advance Pricing Module.

QP_CUSTOM.Get_Custom_Price : This API works like a hook in Oracle Advance Pricing.

This is function provided by the oracle We need to override the body of this package function in order to add custom functionality. We can add custom code to this customizable function. When the pricing engine executes to calculate the base price , it executes the logic inside this function if it found a formula line in the price list.

QP_CUSTOM_SOURCE.Get_Custom_Attribute_Values : 

Attributes can also be passed to the pricing engine directly, without the need for an attribute mapping rule. In such cases, the Attribute Manager API calls the QP_CUSTOM_SOURCE API, where the user has manually defined the attributes being passed and coded the sourcing of their values.

The user code is written in the package procedure QP_CUSTOM_SOURCE.Get_Custom_Attribute_Values.
The Attribute Manager API program (Build_Contexts), calls this procedure to pick up custom-sourced attributes if the profile option QP: Custom Sourced is set to Yes. The input parameters to QP_CUSTOM_SOURCE are Request Type code and Pricing Type. Typical values of Request Type Codes that can be passed are ONT, ASO, OKC, IC, FTE or MSD. By using the Request_Type_Code, the user can control how the attributes are sourced based on the PTE of the calling application.The Pricing Type can be H (Header) or L (Line) which defines the level of the attribute mapping rule. These attributes and their values are passed to the pricing engine in the same manner as the attributes sourced through attribute mapping rules.

Key Tables

• QP_PRICING_ATTRIBUTES
• QP_LIST_LINES
• QP_LIST_HEADERS_B
• QP_QUALIFIERS
• QP_PRICING_ATTRIBUTES
• QP_LIST_LINES
• QP_LIST_HEADERS_B
• QP_SEGMENTS_B
• QP_PRC_CONTEXTS_B
• QP_PTE_SOURCE_SYSTEMS
• QP_ATTRIBUTE_SOURCING
• QP_PTE_REQUEST_TYPES_B
• QP_PTE_SEGMENTS
• QP_PRICING_PHASES
• QP_EVENT_PHASES
• OE_PRICE_ADJUSTMENTS
• OE_PRICE_ADJ_ATTRIBS
• OE_PRICE_ADJ_ASSOCS

The tables are as follows:
• OE_PRICE_ADJUSTMENTS
– Holds the price adjustments
• OE_PRICE_ADJ_ATTRIBS
– Holds the qualification conditions applied to give the adjustments
• OE_PRICE_ADJ_ASSOCS
– Holds relationships between multiple adjustment records for price breaks, promotional goods and other item benefits
– QP_PREQ_LINES_TMP
– Has Line and Order details
– QP_PREQ_LINES_ATTRS_TMP
– Has product and pricing attribute details of a particular line
– QP_PREQ_RLTD_LINES_TMP
– Has the relation b/w lines in QP_PREQ_LINES_TMP
– QP_PREQ_LDETS_TMP
– Has the details about the modifier line and pricelist line got selected for a particular line
– QP_PREQ_QUAL_TMP
– Has qualifier attribute details of a particular line
Error Handling:
– If the pricing engine call is a success , if the value of x_return_status is FND_API.G_RET_STS_SUCCESS.
• Error Statuses
– G_STATUS_INVALID_PRICE_LIST
– G_STS_LHS_NOT_FOUND
– G_STATUS_FORMULA_ERROR
– G_STATUS_OTHER_ERRORS
– G_STATUS_CALC_ERROR
– G_STATUS_UOM_FAILURE
– G_STATUS_INVALID_UOM
– G_STATUS_ DUP_PRICE_LIST
– G_STATUS_INVALID_UOM_CONV
– G_STATUS_INVALID_INCOMP
– G_STATUS_BEST_PRICE_EVAL_ERROR
– FND_API.G_RET_STS_UNEXP_ERROR
– FND_API.G_RET_STS_ERROR
• Price List fetched for an request line.
– The price_list_id is populated on the price_list_id column in the qp_preq_lines_tmp.
– The List price (undiscounted base price ) is returned in unit_price
– The discounted price (After applying all the discounts/surcharges) is in adjusted_unit_price
– Priced_quantity holds the line_quantity (Order Line quantity) expressed in pricing_uom_code
• Modifiers fetched for an order line.
– qp_preq_ldets_tmp : Hold the information about the applied modifiers
– qp_preq_rtd_lines_tmp : Holds the relationship between the modifiers applied.

Pricing Security

Oracle Advanced Pricing provides an additional level of security called pricing security. Pricing security can be used to restrict pricing activities such as updating and viewing pricing entities to users granted specific access privileges. Pricing security is set up and maintained by a user who is assigned the Oracle Pricing Administrator responsibility.
Security Privileges
Security privileges enable you to define who can access each pricing entity and the level of access permitted: View Only or Maintain. The Pricing Administrator is typically responsible for setting up security privileges. For more information on security privileges and other security features, please check the Oracle Advanced Pricing Implementation Guide.

Price Book in R12

Price Book is a new feature in R12 Pricing , which allows customers to generate and publish documents called price books.It will allows customers to generate and publish lists of products with their related prices.
Types of Price Books:
R12 Price book feature enables us to create the below:
Full Book
We can generate a full price book that lists all products and associated prices as of a specific pricing effective date.
Delta Price Book
A delta price book as of a new pricing effective date that shows only those items whose prices changed since the last full price book was published, in addition any prices for items that were added or deleted.When we generate the delta book, it will be for the same customer and all other input criteria remaining the same.
Publishing Options:
We can publish a price book either immediately or schedule a future publishing date. Advantages of this feature as:

• Generate a hard copy or electronic copy of a price book to view and publish price lists for planning purposes or price management. This will helps the, sales representatives to have a copy of pricing available for the customers on whom they call to make a deal.

• View and publish list and net prices (the actual unit selling price and any freight charges) for all the items purchased from a vendor or sold to a customer.

Price Book is a new Pricing report that displays item list and net prices (for a specific customer) based on a specific pricing date. Within the price book, you can view details of the following:

•   List price
•   Discounts
•   Surcharges
•   Price breaks
•   Freight and special charges

The Price book accepts various inputs for the price calculation.  These inputs include

1.     Pricing effective date.
2.     Pricing attributes.
3.     Qualifiers.

In addition to the above, it also consider the below:

• Pricing Perspective (i.e, request type can be Order or Quote)
• Currency
• Customer
• Customer (account) number
• Operating unit
• Pricing effective date
• Quantity
• products

Advantages :

• Self service
• Provides for dates, customer specific information,and qualifier attributes.
• Multiple publishing options (RTF , Excel or PDF)
• Can create a delta price list and/or full price list.
• Multiple communication methods.
• Can create a delta price list and/or full price list.
• Track pricing changes.

Advantages for different stakeholder:
Customer Service – Customers often want a full listing of their list & net prices. This is an out of the box solution to publish a listing.
Sales / Product Managers – Theyreview existing prices,create a hard copy of current pricing. Review future pricing and the delta between current and future pricing.
Customers – self-service capability to generate and publish a listing of their products and related prices.
APIs to generate or retrieve a price book.
QP_PRICE_BOOK_PUB.Create_Publish_Price_Book
QP_PRICE_BOOK_PUB.Get_Price_Book
Price Book Profile Options
The price book feature uses the following profile options to define the default settings:

• QP: E-mail From Address: This profile option defines the from e-mail address for any e-mails that are sent from the price book feature.
• QP: External Default Pricing Perspective: This profile option sets the default pricing perspective for all external users.
• QP: Inbound XML Messaging Responsibility: This profile option defines the responsibility to be used for inbound XML messages.
• QP: Internal Default Pricing Perspective: This profile option sets the default pricing perspective for all internal users.
• QP: Pricing Perspective Request Type: This profile option defines the mapping between a Pricing Perspective (calling application) and the Request Type that it uses.

How to handle Null Values in Formulas

How to handle Null Values in Formulas
During formula calculation, if a step results in a null value, the formula fails.

Example : consider the formula 1*2, where step 2 is of type Pricing Attribute. The pricing attribute context is Physical Attribute & the pricing attribute is Volume.Hence we must provide the volume at order entry time.
If we can't provide a volume, use the NVL expression in the formula to refer to a step number that evaluates to a non-null value.In this case we can change the formula to 1* NVL(2,3). In the formula line for step number 3, use a non-null value such as the numeric constant of 7. If the user does not provide a value for volume, the pricing engine uses 7 in the formula calculation.
If the expression does not use NVL in the expression, and the step number evaluates to NULL, the entire expression evaluates to NULL, the formula calculation fails, and the calling application processes the failure.

Oracle Pricing Formulas

Overview of Formulas
Formulas consist of mathematical expressions that the pricing engine evaluates to calculate the list prices of items and discounts applied to them. Formulas can be linked to a price list line or modifier line. You can use formulas to:

• Create a price from a computation as an alternative to entering prices in a price list.
• Calculate a price adjustment. (Pricing engine can calculate a discount by attaching a formula to a discount line)
• Set up and maintain formulas based on component types.

When we attach a formula to a price list line, we do not have to enter the price for that line because the pricing engine uses the formula to calculate the final list price of the product or service. There is one exception exists in which we must enter the list price (base price) for the formula to use in it's calculation, if the formula has a list price as Formula Type.
We can attach a formula that has a List Price component to price list lines and to modifier lines.
How a formula calculates the list price:
Dynamic calculation: The dynamic calculations are dynamic in nature. This means the  list price resulting from the formula calculation is not calculated or stored anywhere until the sales order or the quote is entered with that price list line item. When the sales order/quote is entered, the pricing engine evaluates the formula and derive the list price and displays the final list price on the order/quote.
Static calculation: If the formula is attached to a price list line for static calculation of the final list price, we can run a concurrent program at any time to calculate the final list price using the formula up front and not wait until order entry/quote enter time and also store it in the price list.

Seeded Formulas

Oracle Advanced Pricing provides seeded formulas that we can use for different purposes.
Pricing Formula
A formula is a valid mathematical expression that you can use to determine the list prices of items and the discounts applied to those items. The formula lines provide details about each part of the formula.
Note: The concurrent program Build Formula Package should be run if you create or change a formula expression.
A formula can contain any of the following:

• Parentheses: for example, (and)
• Mathematical operators: for example, +, -, /, and *
• Built-in functions: for example, NVL, SQRT, and MOD
• Operands: Operands are step numbers about which you provide more detail. You can use as many step numbers as you need, up to the limit of the field. You can repeat a step number in a formula, for example, 1+2*2.Note: An operand is not a numeric constant. If you want to use a numeric constant in a formula, you must create a step number in the formula expression and then assign the numeric constant to the step number in a formula line

For example, the valid formula (1+2*SQRT(3)) / 4 contains:

• Operands: 1, 2, 3, and 4
• Mathematical operators: +, *, and /
• Built-in function: SQRT
• Parentheses: to group the operands and operators

For each preceding step number, you need to create four formula lines since the formula has four step numbers.
Oracle Advanced Pricing uses the formula line and evaluates it to obtain the value of the operand and calculates a formula. It does not use the face value of the step number.
You assign one of the following types to each formula line:

• Function: The function GET_CUSTOM_PRICE retrieves a price from an external system and uses it in the formula calculation.
• List Price: The price of the item in a specific price list to which you have attached a formula.
• Factor List: Enables you to define pricing conditions that you can link to multiple pricing attributes or a range of these attributes. The pricing engine evaluates the formula, and then chooses one of these factors, depending into which range the actual pricing attribute of the item falls.For example, a step in the formula may have different factors defined for different ranges of glass thickness: a glass with thickness between 0.1 and 0.3 mm has a factor of 3, and a glass with thickness between 0.4 and 0.8 mm has a factor of 5. The pricing engine determines which factor qualifies when it evaluates an order and applies this factor in the formula calculation.You can also relate multiple factor conditions. For example, if the base pricing attribute for glass thickness is between 0.1 and 0.3 mm AND the length of the glass is between 0.5 and 2 m, apply the factor of 3 OR if the base pricing attribute for glass thickness is between 0.4 and 0.8 mm AND the length of the glass is between 0.5 and 2 m, apply the factor of 5.
• Modifier Value: Uses the value entered in the Value field of the modifier line or price break line.
• Numeric Constant: A numeric value.
• Price List Line: The price of the item in a specific line of a specific price list.
• Pricing Attribute: The absolute value of a pricing attribute (such as thickness or height) of an item.

Pricing attributes
Pricing attributes are characteristics of products and services that you can specify when the characteristics help to determine the price of a product or service. Distance, age of a related product, customer class, product family group, and level of service are examples of pricing attributes. You can specify one or a combination of pricing attributes and assign them to a product or service. At order entry time, the pricing engine evaluates the attributes you have specified during formula setup to calculate the price.
You can define as many attributes as you need to meet your pricing business needs. For example, you may use the formula 1*2 to calculate the price of a glass item. Step 1 is a pricing attribute for thickness and step 2 is the list price to calculate the price of a glass item; if 100 is the base price of the glass item and 0.3 is the value of the thickness attribute of the glass, then the pricing engine evaluates the formula as 0.3*100, which is 30.
Updating Formula Prices
Use this process to use static formulas to set list prices. When you attach a formula to a price list line, you can select from the following calculation methods:
Dynamic calculation: When we enter an order/quote, the pricing engine uses the formula to calculate the list price.
Static calculation: Before we enter order/quote , you run a concurrent process that calculates the list price.

Ignore Pricing for Quote/Order Lines

Ignore Pricing for Quote/Order Lines
This is done by using oracle's user hook QP_CUSTOM_IGNORE.IGNORE_ITEMLINE_FOR_PRICING.
I have implemented the same in one of my client. I am sharing my learning below.
When we setup a price list line we use the product attribute as Item Number, Item Category, and All Items. We normally use All Items to apply the same price to all items in your product hierarchy.
In real time scenario, we need to price only certain number of quote/order items using the pricing setup & for the remaining items the price is picked either from some dummy pricelist that will carry certain price or from the pricelist line of the product attribute “All Items” value as 0. These items are completely insignificant to pricing and no more processing is required for these items in the pricing engine call other than fetching a price from some dummy pricelist as explained above.
In many organizations mostly the model items have lot of marketing or manufacturing option items included which have no significance from pricing perspective. Those are to complete the configuration of the items. When we try to price those products, the lines from these items are sourced to pricing for processing and after lot of unnecessary processing; the prices fetched for these lines are zero. Due to this these lines are actually extra burden on pricing and cause performance issues unnecessarily. Especially in case of bigger orders significant amount of time can be saved if pricing engine could ignore these lines.
The Ignore Pricing custom hook provides users an opportunity to inform pricing engine to ignore such lines that could result in substantial performance gain. The benefit can be realized at real time scenario.
QP_CUSTOM_IGNORE package contains the procedures IGNORE_ITEMLINE_FOR_PRICING which is supplied by oracle. The IGNORE_ITEMLINE_FOR_PRICING is a customizable code; need to be customized as per the requirement. It is called when the profile value QP: Custom Ignore Pricing is set to ‘Y’. Pricing engine calls it for each line while sourcing line level attributes. Line structure will be available based on the p_request_type_code parameter in the procedure.
For example OE_ORDER_PUB.G_LINE would be available for ONT request_type_code & ASO_PRICING_INT.G_LINE_REC would be available for ASO request_type_code.
Example:
Ignore the 0$ lines from the quote in order to improve the performance of the pricing call.
We have implemented the below logic in the Ignore Pricing custom hook (QP_CUSTOM_IGNORE. IGNORE_ITEMLINE_FOR_PRICING).
CREATE OR REPLACE PACKAGE BODY qp_custom_ignore
AS
PROCEDURE ignore_itemline_for_pricing (
p_request_type_code IN VARCHAR2,
x_ignore OUT VARCHAR2,
x_default_price_list_id OUT NUMBER
)
IS
l_price_attribute NUMBER;
BEGIN
IF p_request_type_code = 'ASO'
AND NOT (NVL (aso_pricing_int.g_header_rec.attribute_name, 'N') ='Y'
)
THEN
--Custom attribute defined in EGO for base price
l_price_attribute := xx_cus_pkg.get_custom_attribute;
IF NVL (l_price_attribute, 1) = 0
THEN
x_ignore := 'Y';
x_default_price_list_id :=
aso_pricing_int.g_header_rec.price_list_id;
ELSE
x_ignore := 'N';
x_default_price_list_id :=
aso_pricing_int.g_header_rec.price_list_id;
END IF;
ELSE
x_ignore := 'N';
x_default_price_list_id := aso_pricing_int.g_header_rec.price_list_id;
END IF;
EXCEPTION
WHEN OTHERS
THEN
x_ignore := 'N';
x_default_price_list_id := aso_pricing_int.g_header_rec.price_list_id;
END ignore_itemline_for_pricing;
END qp_custom_ignore;