Search Results substitution_value




Overview
Explore in AI Chat

Overview

APPS.QPBV_TERM_SUBSTITUTIONS is a reporting and integration view in Oracle E-Business Suite Advanced Pricing (Oracle Pricing) that exposes a filtered subset of pricing list lines. Specifically, the view returns only those rows from the pricing list line table whose LIST_LINE_TYPE_CODE equals 'TSN', which denotes term substitution lines. Term substitutions are used in the pricing engine to define conditional replacements — for example, substituting one value, attribute, or product context for another during price list evaluation, or expressing incompatibility groupings that govern how pricing terms interact.

The view is owned by APPS and is documented in ETRM for releases 12.1.1 and 12.2.2. Its principal role is to give reports, concurrent programs, and integration interfaces a pre-filtered, denormalized projection of substitution definitions without requiring callers to know or hard-code the TSN discriminator against the base table. Because the view does not join reference tables, it exposes the raw substitution columns directly, which makes it suitable for bulk extraction to external pricing, order capture, or data-warehouse systems.

Underlying Base Objects

The view is defined over a single documented base object:

  • QP_LIST_LINES (SYNONYM) — the pricing list line table holding all line types (price list lines, modifiers, and term substitution lines) for pricing lists and agreements.

The relationship is a simple restriction: the view is defined as SELECT ... FROM qp_list_lines WHERE list_line_type_code = 'TSN'. No joins, unions, or aggregations are present, so the view does not multiply rows and preserves all audit and origin-system columns of the underlying line. Because it references the synonym rather than the table directly, the view remains subject to the same APPS-level grants and synonym resolution as other Advanced Pricing views.

Key Columns

Common Use Cases and Queries

Typical uses include auditing active substitutions, troubleshooting conflicting pricing behavior via incompatibility groups, and extracting substitution definitions for integration. Effective-dating filters are common.

  • Listing all substitutions on a given list:

SELECT list_line_id, substitution_context, substitution_attribute, substitution_value FROM apps.qpbv_term_substitutions WHERE list_header_id = :p_header_id ORDER BY list_line_no;

  • Finding substitutions by value:

SELECT list_header_id, list_line_id, substitution_attribute, substitution_value FROM apps.qpbv_term_substitutions WHERE substitution_value = :p_value;

  • Retrieving currently active substitutions:

SELECT list_header_id, substitution_value, start_date_active, end_date_active FROM apps.qpbv_term_substitutions WHERE TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, TRUNC(SYSDATE));

  • Inspecting incompatibility groupings:

SELECT incompatibility_grp_code, COUNT(*) FROM apps.qpbv_term_substitutions GROUP BY incompatibility_grp_code;

Because the view has no joins, these queries are inexpensive and can be safely used in high-volume extraction and reconciliation routines.