Search Results qp_pricing_attributes_n2




Overview
Explore in AI Chat

Overview

APPS.CLN_PROCAT_ITEM_CATEGORIES_V is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 Advanced Pricing (QP) and Order Management (ONT) schemas. It exposes the relationship between price list lines and the item categories assigned to the items referenced by those lines, filtered to the Pricing functional area. The view is designed to answer the question: "For a given price list line, which product categories (within the Pricing category set) are associated with its referenced item, and at what product precedence was that line resolved?"

Because pricing in EBS frequently uses category-based price list lines alongside item-specific lines, this view provides a flattened, category-aware perspective that joins pricing attributes, item master, customer item cross-references, and item category assignments into a single result set. It is primarily consumed by custom reports, extensions, and integrations that need to reconcile price list line definitions with the merchandise hierarchy used for pricing.

Underlying Base Objects

The view is defined over the following documented base objects:

The view filters MDC.FUNCTIONAL_AREA_ID = 7, restricting the categorization to the Pricing functional area, and joins MTL_CATEGORIES_TL on USERENV('LANG') so that the category description is returned in the session language.

Key Columns

  • LIST_HEADER_ID — identifier of the price list header to which the line belongs.
  • CATEGORY_ID — the numeric identifier of the item category assigned to the item on the price list line.
  • CATEGORY_DESCRIPTION — the translated category name from MTL_CATEGORIES_TL, useful for direct reporting.

Additional columns derived implicitly through the joins include product precedence, customer item identifiers, and item identifiers, though the view's SELECT list as documented returns only the three columns above.

Notably, the view contains a correlated subquery on QP_PRICING_ATTRIBUTES that resolves the maximum PRODUCT_PRECEDENCE for the line, and it carries the hint /*+ INDEX(qp_pricing_attributes_n2) */. This directly explains the user's search term: qp_pricing_attributes_n2 is the non-unique index on QP_PRICING_ATTRIBUTES that the view relies on to efficiently locate the qualifying product attribute rows and compute the max product precedence. Performance tuning of this view typically involves verifying that this index exists and is being used by the optimizer.

Common Use Cases and Queries

Typical use cases include generating category-level price list extracts, validating that pricing category assignments align with price list lines, and diagnosing why a given category-based line did or did not qualify at order entry.

Example query to list categories per price list header:

  • SELECT list_header_id, category_id, category_description FROM apps.cln_procat_item_categories_v WHERE list_header_id = :p_list_header_id;

Example query to find price lists that price a specific category:

  • SELECT DISTINCT list_header_id FROM apps.cln_procat_item_categories_v WHERE category_id = :p_category_id;

When tuning these queries in 12.1.1 or 12.2.2, confirm that the qp_pricing_attributes_n2 index is present and that statistics on QP_LIST_LINES and QP_PRICING_ATTRIBUTES are current, since the correlated subquery drives cost in the execution plan.