Search Results qp_list_lines_n5

Overview

APPS.QP_MODIFIER_SUMMARY_V is a pricing-related view in Oracle E-Business Suite Advanced Pricing (QP) that exposes the full attribute set of price list lines and modifier list lines — collectively referred to as modifiers — alongside their associated product pricing attributes. Because the view denormalizes the line-level record from QP_LIST_LINES with qualifying product attribute rows from QP_PRICING_ATTRIBUTES, it presents a consolidated picture of any modifier definition, including list prices, price breaks, accruals, rebates, benefit configurations, and the pricing attributes that govern qualification. Its role in Oracle EBS 12.1.1 and 12.2.2 is primarily that of a reporting and integration surface: it allows concurrent programs, OBIEE/BI Publisher extracts, custom DFF reporting, and interface scripts to read modifier and list information without writing multi-table joins against the pricing schema.

Underlying Base Objects

The ETRM metadata documents four referenced base objects: QP_LIST_LINES (SYNONYM), QP_PRICING_ATTRIBUTES (SYNONYM), QP_PRICE_LIST_LINE_UTIL (PACKAGE), and QP_QP_FORM_PRICING_ATTR (PACKAGE). The dominant table is QP_LIST_LINES (aliased ql), which stores every price list line and modifier line. QP_PRICING_ATTRIBUTES (aliased qpa) supplies the qualifying attribute values attached to each line. The view's defining query carries the hint /*+ use_nl(ql, qpa) index(ql QP_LIST_LINES_N5) */, which forces a nested-loop join driven through the QP_LIST_LINES_N5 index. This index — the object the user searched for — is the primary access path for the view and is critical to query performance. It typically supports lookups by list header and line identifiers, allowing the view to retrieve a specific price list or modifier efficiently rather than scanning QP_LIST_LINES. The two packages, QP_PRICE_LIST_LINE_UTIL and QP_QP_FORM_PRICING_ATTR, provide supporting logic used in the pricing UI and API layers; the view itself depends on the same structural relationships these packages assume.

Key Columns

The view exposes the complete column set of QP_LIST_LINES. Identity and audit columns include LIST_LINE_ID, LIST_HEADER_ID, LIST_LINE_NO, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and the WHO/request columns (PROGRAM_APPLICATION_ID, PROGRAM_ID, REQUEST_ID). Pricing structure columns include LIST_LINE_TYPE_CODE, MODIFIER_LEVEL_CODE, AUTOMATIC_FLAG, LIST_PRICE, LIST_PRICE_UOM_CODE, PERCENT_PRICE, OPERAND, ARITHMETIC_OPERATOR, OVERRIDE_FLAG, and PRICE_BREAK_TYPE_CODE. Product and qualification columns include INVENTORY_ITEM_ID, ORGANIZATION_ID, RELATED_ITEM_ID, RELATIONSHIP_TYPE_ID, and the SUBSTITUTION_CONTEXT/ATTRIBUTE/VALUE triplet. Effectivity is controlled by START_DATE_ACTIVE, END_DATE_ACTIVE, EXPIRATION_DATE, and related expiration-period columns. Accrual and benefit functionality is represented by REBATE_TRANSACTION_TYPE_CODE, ESTIM_ACCRUAL_RATE, ACCRUAL_FLAG, ACCRUAL_CONVERSION_RATE, PRORATION_TYPE_CODE, BENEFIT_PRICE_LIST_LINE_ID, BENEFIT_QTY, BENEFIT_UOM_CODE, BENEFIT_LIMIT, CHARGE_TYPE_CODE, and CHARGE_SUBTYPE_CODE. From QP_PRICING_ATTRIBUTES, the view adds PRICING_ATTRIBUTE_ID, PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE, PRODUCT_ATTR_VALUE, PRODUCT_UOM_CODE, and COMPARISON_OPERATOR — the conditions under which the modifier qualifies.

Common Use Cases and Queries

Typical scenarios include auditing all lines belonging to a specific price list, reporting modifiers qualifying on a particular product attribute, and extracting pricing data for downstream analytics. A representative query joining to QP_LIST_HEADERS (not shown in the metadata but the parent of LIST_HEADER_ID) might read:

• SELECT v.list_header_id, v.list_line_id, v.list_line_no, v.inventory_item_id, v.list_price, v.start_date_active, v.end_date_active FROM apps.qp_modifier_summary_v v WHERE v.list_header_id = :p_header_id ORDER BY v.list_line_no;
• SELECT v.list_line_id, v.product_attribute_context, v.product_attribute, v.product_attr_value, v.comparison_operator FROM apps.qp_modifier_summary_v v WHERE v.list_header_id = :p_header_id;
• SELECT v.list_header_id, COUNT(*) FROM apps.qp_modifier_summary_v v WHERE v.modifier_level_code = 'LINE' GROUP BY v.list_header_id;

Because the view is driven through QP_LIST_LINES_N5, predicates on LIST_HEADER_ID and LIST_LINE_ID produce optimal plans, while unconstrained full queries can be costly on large pricing tables. Filtering by list header or line identifier is therefore strongly recommended in production reports.