Search Results cln_procat_catitem_v

Overview

CLN_PROCAT_CATITEM_V is a PL/SQL view owned by the APPS schema within the Oracle E-Business Suite Release 12.1.1 / 12.2.2 environment. It belongs to the CLN product family — Supply Chain Trading Connector for RosettaNet — which provides the integrations and message payloads required for B2B exchange of catalog and pricing data between trading partners over the RosettaNet protocol. The view consolidates pricing list lines, item master attributes, and customer-specific item cross-references into a single flattened result set suitable for outbound catalog (PIP) publication.

In practice, CLN_PROCAT_CATITEM_V serves as the data source for price-list and item-catalog message generation. By joining the advanced pricing engine's list lines and pricing attributes with the inventory item master and customer item cross-references, it produces a resale-facing view that reports both the supplier's catalog definition and any customer-specific item numbering that must appear in the outbound message. Its DISTINCT clause and hint-driven design indicate that the view is tuned for read-only extraction rather than transactional processing.

Underlying Base Objects

The view is defined over the following documented objects:

• QP_LIST_LINES (synonym) — the pricing list lines; the driving table, filtered to LIST_LINE_TYPE_CODE in ('PLL','PBH').
• QP_PRICING_ATTRIBUTES (synonym) — pricing attribute rows; the view selects only PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1' with EXCLUDER_FLAG = 'N' and a null pricing attribute context.
• MTL_SYSTEM_ITEMS_B_KFV (view) — key-flexfield view of the item master, supplying list price, UOM, MOQ, and concatenated segments.
• MTL_SYSTEM_ITEMS_TL (synonym) — the translated item descriptions.
• MTL_CUSTOMER_ITEMS (synonym) and MTL_CUSTOMER_ITEM_XREFS (synonym) — customer item number definitions and the item-to-customer cross-reference. These are joined with outer-join syntax, so items without customer-specific numbering still appear.
• MTL_ITEM_REVISIONS (synonym) — used in a scalar subquery to return the maximum revision for the item.
• HZ_CUST_ACCOUNTS (synonym) — the trading partner / customer account record.
• FND_PROFILE (package) — referenced in the remaining view logic for profile-based filters.

The view text embeds explicit index hints — QP_LIST_LINES_N1, QP_PRICING_ATTRIBUTES_N2, MTL_SYSTEM_ITEMS_B_U1, MTL_SYSTEM_ITEMS_TL_U1, and notably MTL_CUSTOMER_ITEMS_U2 — indicating the access paths intended by the Oracle development team. The reference to MTL_CUSTOMER_ITEMS_U2 is the link a user encounters when searching for that index name: it is the unique index on the customer item table used to drive the customer item join.

Key Columns

• LIST_HEADER_ID, LIST_LINE_ID — identifiers of the pricing list header and line, the natural key of the price-list entry.
• START_DATE_ACTIVE, END_DATE_ACTIVE, PRODUCT_PRECEDENCE — effective dating and precedence for the price line.
• LIST_PRICE, LIST_PRICE_UOM_CODE — the list price per unit and its pricing UOM.
• INVENTORY_ITEM_ID, ORGANIZATION_ID, CONCATENATED_SEGMENTS — the item identifier, inventory organization, and concatenated key-flexfield item number.
• ITEM_DESCRIPTION — translated item description.
• PRIMARY_UOM_CODE, MINIMUM_ORDER_QUANTITY, MAX_WARRANTY_AMOUNT, SERVICE_ITEM_FLAG — item-level commercial attributes.
• CUSTOMER_ITEM_NUMBER, PARTY_ID — the customer-specific item number (with NVL fallback to concatenated segments) and the party identifier of the customer account.
• REVISION — the maximum item revision derived from MTL_ITEM_REVISIONS.
• CONTEXT and ATTRIBUTE1–ATTRIBUTE15 — the descriptive flexfield columns of the underlying price list line.

Common Use Cases and Queries

The view is typically queried to extract a price list in catalog form for RosettaNet publication, or to reconcile customer item numbering against the supplier's item master. A representative query follows:

• SELECT list_header_id, inventory_item_id, organization_id, concatenated_segments, customer_item_number, list_price, list_price_uom_code, primary_uom_code FROM apps.cln_procat_catitem_v WHERE list_header_id = :p_list_header_id ORDER BY product_precedence;
• SELECT DISTINCT customer_item_number, concatenated_segments, item_description FROM apps.cln_procat_catitem_v WHERE party_id = :p_party_id; — used to validate that the customer-specific numbering is populated before transmitting a catalog.
• SELECT inventory_item_id, organization_id, revision, minimum_order_quantity FROM apps.cln_procat_catitem_v WHERE service_item_flag = 'N';

Because the view applies outer joins to the customer item tables and filters inactive customer items, users should expect rows to be returned even when no customer item number exists; the CUSTOMER_ITEM_NUMBER column falls back to CONCATENATED_SEGMENTS in that case. All access should be granted through the APPS schema or an appropriately privileged CLN integration user.