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.