Search Results pricing_attribute1

Overview

CLN_PROCAT_ITEM_CATEGORIES_V is an APPS-owned database view in Oracle E-Business Suite 12.1.1 and 12.2.2, delivered as part of the CLN product family — Supply Chain Trading Connector for RosettaNet. The view exposes the relationship between price list headers, priced inventory items, customer item cross-references, and the item categories assigned to those items within a specific functional category set. Its role is to support the RosettaNet trading partner integration flow, where catalog and pricing information must be resolved against the correct category hierarchy and customer-specific item identifiers.

Unlike a conventional master-detail reporting view, CLN_PROCAT_ITEM_CATEGORIES_V is a DISTINCT projection of a heavily joined query, meaning it materialises a deduplicated result set at runtime. This design is deliberate: the view simultaneously joins pricing, item master, customer, and category management structures, and the DISTINCT clause prevents duplicate rows arising from the multi-branch join paths. The view is documented as VALID in the ETRM metadata, confirming that its dependencies resolve correctly in the supported EBS releases.

Underlying Base Objects

The documented base objects referenced by this view span four Oracle EBS product schemas, accessed through APPS synonyms and views. The pricing side is represented by QP_LIST_LINES and QP_PRICING_ATTRIBUTES, which provide the price list line and its product attribute linkage. The item master side draws on MTL_SYSTEM_ITEMS_B_KFV (a key flexfield view), MTL_ITEM_CATEGORIES, MTL_CATEGORIES_V, MTL_CATEGORY_SETS_B, MTL_CATEGORIES_TL, and MTL_DEFAULT_CATEGORY_SETS. Customer-specific item information is sourced from MTL_CUSTOMER_ITEMS and MTL_CUSTOMER_ITEM_XREFS. Trading partner account data is obtained from HZ_CUST_ACCOUNTS.

The join logic is dense. QP_PRICING_ATTRIBUTES is restricted to PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1' with EXCLUDER_FLAG = 'N', and the price list line type is limited to 'PLL' and 'PBH'. The category side is constrained by MTL_DEFAULT_CATEGORY_SETS having FUNCTIONAL_AREA_ID = 7, identifying the purchasing or cataloging functional area. The customer item cross-reference joins are outer joins, allowing price list lines without a customer-specific item mapping to remain in the result set. Language filtering on MTL_CATEGORIES_TL is applied through USERENV('LANG'), ensuring the category description is returned in the session language.

A correlated subquery against QP_LIST_LINES and QP_PRICING_ATTRIBUTES restricts output to lines whose PRODUCT_PRECEDENCE equals the maximum precedence available for the same list header and product attribute value. This enforces a single winning pricing line per item, using an INDEX hint on QP_PRICING_ATTRIBUTES_N2.

Key Columns

• LIST_HEADER_ID — The identifier of the price list header from QP_LIST_LINES. This is the primary grouping key for downstream pricing or catalog processing.
• CATEGORY_DESCRIPTION — The translated category name from MTL_CATEGORIES_TL, returned for the session language. This is the human-readable label used in RosettaNet catalog exchanges.
• CATEGORY_ID — The internal category identifier from MTL_CATEGORIES_V, which links the item to a node in the category hierarchy defined by the category set structure.

Common Use Cases and Queries

The view is typically consumed by RosettaNet catalog and price synchronization processes that need to associate priced items with their category description. A common query retrieves the category context for a given price list header:

• SELECT list_header_id, category_id, category_description FROM cln_procat_item_categories_v WHERE list_header_id = :p_list_header_id;
• SELECT category_id, category_description FROM cln_procat_item_categories_v GROUP BY category_id, category_description ORDER BY category_description;

Because the view already applies the customer item outer join and the precedence subquery, callers should avoid re-applying DISTINCT or precedence logic in wrapper queries. For performance, filtering by LIST_HEADER_ID is strongly recommended, as the correlated subquery on QP_PRICING_ATTRIBUTES is the most expensive component of the execution path.