Tables Used in Pricing

Following Tables used to store all price List Data and used Frequently in reporting and oracle Internal operations

· OE_PRICE_ADJUSTMENTS

This table is used to store price adjustments that have been applied to an order or a line. The column automatic flag indicates if the adjustment was applied automatically or manually. Manual discounts are created with applied_Flag = Y.

SELECT price_adjustment_id, creation_date, header_id "Order Header",

automatic_flag "discount applied automatically",

line_id "ORDER LINE_ID",

orig_sys_discount_ref "Original discount reference",

list_header_id "Header Id of the Modifier",

list_line_id "Line id of the Modifier",

list_line_type_code "Line Type of the Modifier",

accrual_flag "adjustment is accrued", benefit_qty "Quantity accrued",

benefit_uom_code

FROM ont.oe_price_adjustments

· OE_PRICE_ADJ_ATTRIBS

This table stores information on qualifiers and pricing attributes, which to that corresponding price adjustment line qualified for “price_adjustment_id” is link between this table and ont.oe_price_adjustments

SELECT price_adjustment_id,

pricing_attr_value_from "from Value pricing Attribute",

pricing_attr_value_to "To Value pricing Attribute",

comparison_operator "Operators",

flex_title "pricing_context Flex_name", price_adj_attrib_id,

lock_control

FROM ont.oe_price_adj_attribs;

· OE_PRICE_ADJ_ASSOCS

This table stores the association between Order lines and price adjustments and also between price adjustments. One adjustment may be a result of benefit on one or more order lines. “price_adjustment_id” is link between this table and ont.oe_price_adjustments

SELECT line_id "Order Line Id", price_adjustment_id,

rltd_price_adj_id "price_adjustment_id"

FROM ont.oe_price_adj_assocs;

SOME OF THE MAIN PRICING API’s AVAILABLE

Business Object for Pricing Formulas Application Program Interface

· QP_Price_formula_PUB.Get_Price_Formula
The Formula Calculation package retrieves the price formula header and lines for a given formula.
· QP_Price_formula_PUB.Process_Price_Formula
Performs the insert, update, and delete of price formula header and price formula lines.
· QP_Price_formula_PUB.Lock_Price_FormulaLocks price formula header and price formula lines records prior to updates

Get Custom Price Used in Formulas Setup) Application Program Interface

· QP_CUSTOM.Get_Custom_Price
You may add custom code to this customizable function. The pricing engine while evaluating a formula that contains a formula line (step) of type “function” calls this API.

Request Application Program Interface

· QP_PREQ_GRP.Price_Request
The Price Request Application Program Interface (API) is a public API that allows you to get a base price and to apply price adjustments, other benefits, and charges to a transaction.

STEPS FOR PRICING AN ORDER

1. Create a price list (one time)
2. creation of price list headers and lines(associates items to a particular price list)
3. Create pricing formulas (optional)
4. Create price list modifiers/qualifiers (optional)
5. Create an order.
6. Attach the price list to that order (we can associate price list to order type and check the box (enforce price list)).

HOW ORACLE PRICING WORKS

Pricing Engine takes the pricing request from the calling application, selects the applicable price lists and modifier lists and determines the base list price. It then calculates the final selling price by applying the benefits and surcharges from the modifier lists to the base list price

Process flow, Events and the various associated Pricing Phases