Search Results oe_price_adjustments

Overview

The OE_PRICE_ADJUSTMENTS table is a core transactional data store within the Oracle E-Business Suite Order Management (ONT) module. It functions as the central repository for all financial modifications applied to sales orders. Its primary role is to persistently record price adjustments, such as discounts and surcharges, that are calculated by the pricing engine (QP) and applied to order headers or lines. Beyond pure pricing, the table's scope is extended to also store other types of charges and costs associated with the order fulfillment lifecycle, including freight, special charges, tax amounts, and shipping costs captured during ship confirmation. This consolidation makes it the definitive source for analyzing the complete charge composition of any sales order.

Key Information Stored

The table's structure captures the identity, context, and value of each charge. The primary key is PRICE_ADJUSTMENT_ID. Critical foreign key columns, HEADER_ID and LINE_ID, anchor each record to its parent order in OE_ORDER_HEADERS_ALL or OE_ORDER_LINES_ALL, determining if the adjustment is header- or line-level. The ADJUSTED_AMOUNT column stores the monetary value of the charge. To track the pricing source, LIST_HEADER_ID and LIST_LINE_ID link back to the price list and specific line in the QP module (QP_LIST_HEADERS_B, QP_LIST_LINES). The ADJUSTMENT_TYPE_CODE categorizes the record (e.g., as a discount, freight, or tax). For freight and shipping costs, the COST_ID can link to detailed cost records in WSH_FREIGHT_COSTS. Additional columns typically store attributes like the applied arithmetic operator, the adjusted quantity, and the currency.

Common Use Cases and Queries

A primary use case is auditing the complete pricing breakdown for an order. Analysts query this table to validate discount applications, reconcile total charges, and report on freight or tax amounts. It is also crucial for troubleshooting pricing engine output. Common SQL patterns involve joining to order headers and lines. For example, to list all adjustments for a specific order:

• SELECT opa.adjustment_type_code, opa.adjusted_amount, ooha.order_number FROM ont.oe_price_adjustments opa JOIN ont.oe_order_headers_all ooha ON opa.header_id = ooha.header_id WHERE ooha.order_number = '<order_number>';

Another frequent query aggregates total discounts or freight charges by customer or period by joining through OE_ORDER_HEADERS_ALL to the customer (HZ_CUST_ACCOUNTS). Reporting on tax amounts stored in this table is essential for financial compliance.

Related Objects

OE_PRICE_ADJUSTMENTS has extensive relationships within the Order Management schema. Its primary foreign key dependencies, as documented, are:

• OE_ORDER_HEADERS_ALL: Joined via OE_PRICE_ADJUSTMENTS.HEADER_ID for header-level adjustments.
• OE_ORDER_LINES_ALL: Joined via OE_PRICE_ADJUSTMENTS.LINE_ID for line-level adjustments.
• QP_LIST_HEADERS_B and QP_LIST_LINES: Joined via LIST_HEADER_ID and LIST_LINE_ID to reference the source pricing modifiers.
• WSH_FREIGHT_COSTS: Joined via COST_ID for detailed freight charge data.

Furthermore, it is referenced as a parent table by:

• OE_PRICE_ADJ_ASSOCS: Linked via PRICE_ADJUSTMENT_ID and RLTD_PRICE_ADJ_ID to manage associations between related adjustments.
• OE_PRICE_ADJ_ATTRIBS: Linked via PRICE_ADJUSTMENT_ID to store additional attribute information for an adjustment.