Search Results qpfv_term_substitutions




Overview
Explore in AI Chat

Overview

The APPS.QPFV_TERM_SUBSTITUTIONS view is a read-only reporting object within the QP – Advanced Pricing module of Oracle E-Business Suite, available in both release 12.1.1 and 12.2.2. It exposes the list lines that define Term Substitution modifiers, meaning pricing adjustments in which one pricing attribute value is conditionally replaced or overridden by an alternative value during pricing engine evaluation. The view carries a status of VALID and is owned by the APPS schema.

Because the view is defined with a WITH READ ONLY clause, it cannot be used to insert, update, or delete pricing data; it functions purely as a query surface for reporting, integration, and diagnostic purposes. Its primary value is that it presents substitution logic in a denormalized, human-readable form: substitution contexts and attribute codes are expanded through the QP_QP_FORM_PRICING_ATTR package, and lookup codes are translated to their meanings, sparing the developer or analyst from manually decoding raw QP_LIST_LINES rows.

Underlying Base Objects

The view is defined over two documented objects:

  • QP_LIST_LINES (SYNONYM) — the principal base table supplying all modifier line data. The view restricts this table to rows where LIST_LINE_TYPE_CODE = 'TSN', which is the system code for Term Substitution line types.
  • QP_QP_FORM_PRICING_ATTR (PACKAGE) — invoked twice within the SELECT list to resolve attribute display information and attribute values dynamically at query time using functions such as GET_ATTRIBUTE and GET_ATTRIBUTE_VALUE.

Because row filtering is applied directly to QP_LIST_LINES, the view is tightly coupled to the underlying list line identifiers (LIST_HEADER_ID, LIST_LINE_ID) that link substitution modifiers to their parent price lists in QP_LIST_HEADERS.

Key Columns

Common Use Cases and Queries

Typical scenarios include auditing which substitutions exist for a given price list, verifying the substitution values applied to a pricing attribute, and troubleshooting unexpected pricing results by confirming date ranges and incompatibility groups.

Retrieve all substitutions for a specific list:

SELECT list_line_no,
       substitution_context,
       substitution_attribute,
       substitution_value,
       start_date_active,
       end_date_active
FROM   apps.qpfv_term_substitutions
WHERE  list_header_id = :p_list_header_id
ORDER BY list_line_no;

Search for a specific substitution value across all active lines:

SELECT list_header_id,
       list_line_no,
       substitution_attribute,
       substitution_value
FROM   apps.qpfv_term_substitutions
WHERE  substitution_value = :p_substitution_value
AND    SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE + 1);

Because attribute and lookup values are resolved through package calls at execution time, queries returning many rows may incur additional overhead; filtering by LIST_HEADER_ID or SUBSTITUTION_VALUE where possible is recommended for performance.