Search Results oefv_price_adjustments




Overview
Explore in AI Chat

Overview

OEFV_PRICE_ADJUSTMENTS is an APPS-owned, read-only view in the Oracle E-Business Suite Order Entry (OE) module. It exposes order-level price adjustment records—commonly referred to as discounts, surcharges, or charges—that have been applied to sales order lines. The view was introduced as part of a retrofitted reporting construct, meaning it consolidates adjustment data already maintained in the transactional pricing tables into a denormalized, user-friendly format suitable for ad hoc reporting, concurrent programs, and integration extracts.

Because the view joins transactional adjustment rows to their parent header, line, discount definition, and lookup records, it presents a self-contained representation of each adjustment without requiring the requester to navigate Oracle's normalized pricing schema. This is particularly valuable when business users or downstream systems need to report on which discounts were applied to which orders and lines without authoring complex multi-table joins. The view is defined WITH READ ONLY, ensuring that no DML can be issued against it and that it is safe for query-only reporting roles.

Underlying Base Objects

The documented ETRM metadata for release 12.2.2 identifies the following referenced base objects: OE_ORDER_HEADERS_ALL (as a synonym), OE_PRICE_ADJUSTMENTS (as a synonym), and QP_LIST_HEADERS_TL (as a synonym). These correspond to the core Order Management header, the price adjustment transaction table, and the Advanced Pricing list header translation table respectively.

The view text recorded in the ETRM extract shows the view is constructed over the transactional tables SO_PRICE_ADJUSTMENTS (alias PRADJ), SO_DISCOUNTS (alias DISCOUNT), SO_DISCOUNT_LINES (alias DISCOUNTLN), SO_HEADERS_ALL (alias HEADER), SO_LINES_ALL (alias LINE), and SO_LOOKUPS (alias LOOKUP). The joins link each price adjustment to its discount definition, to its optional discount line, to the sales order header and line, and to a YES_NO lookup that resolves the AUTOMATIC_FLAG into a readable value. The QP_LIST_HEADERS_TL synonym reflects the pricing list header used by later releases to describe the source discount or promotion list. The mix of OE_ and SO_ prefixes reflects the historical naming overlap between Order Management and the legacy Sales Order tables, which Oracle maintains in parallel.

Key Columns

  • PRICE_ADJUSTMENT_ID — Primary identifier for the price adjustment record.
  • AUTOMATICALLY_APPLIED — Readable YES/NO value derived from the AUTOMATIC_FLAG via SO_LOOKUPS, indicating whether the adjustment was applied automatically by the pricing engine or entered manually.
  • PERCENT — The percentage value of the adjustment where applicable.
  • _DF — Literal descriptor '_DF:OE:SO_PRICE_ADJUSTMENTS:PRADJ' identifying the source context of the row.
  • DISCOUNT_NAME — The name of the discount, promotion, or price adjustment source. This is the column users search for when looking up a specific discount by name.
  • ORDER_NUMBER — Sales order number from SO_HEADERS_ALL.
  • LINE_NUMBER — Line number within the sales order.
  • DISCOUNT_ID — Foreign key to the discount definition in SO_DISCOUNTS.
  • DISCOUNT_LINE_ID — Foreign key to the discount line in SO_DISCOUNT_LINES, when applicable.
  • HEADER_ID — Foreign key to the sales order header.
  • LINE_ID — Foreign key to the sales order line.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — Audit columns recording who created and last modified the adjustment and when.

Common Use Cases and Queries

A frequent requirement is to locate every order line on which a particular discount was applied, particularly when the discount is identified by name rather than by internal identifier. Because the view exposes DISCOUNT_NAME directly, users can filter without joining to the discount master.

SELECT order_number, line_number, discount_name,
       percent, automatically_applied
FROM   apps.oefv_price_adjustments
WHERE  discount_name LIKE '%PROMO%';

Another common scenario is auditing automatic versus manual adjustments for a given order:

SELECT order_number, line_number, discount_name, automatically_applied
FROM   apps.oefv_price_adjustments
WHERE  order_number = :p_order_number
ORDER  BY line_number;

Integration and reconciliation programs use the view to extract adjustment activity for a period by leveraging the audit date columns, or join HEADER_ID and LINE_ID back to OE_ORDER_LINES_ALL for extended reporting. Because the view is read-only and based on transactional tables, queries should be filtered by date or order range to avoid full-table scans on high-volume Order Management implementations.