Search Results cln_itemmst_itemheader_v




Overview
Explore in AI Chat

Overview

The CLN_ITEMMST_ITEMHEADER_V view belongs to the CLN – Supply Chain Trading Connector for RosettaNet product within Oracle E-Business Suite 12.1.1 and 12.2.2. It is owned by the APPS schema and its documented purpose is to supply the header section of the Sync Item XML message exchanged in RosettaNet Partner Interface Process (PIP) transactions. The view consolidates item master definition data with revision data, customer-specific cross references, and hazard classification so that a single query can populate the outbound Sync Item payload. Because RosettaNet messaging requires a consistent, partner-aware representation of an item, the view filters records to service-disabled, inventory-enabled, and customer-orderable items, and constrains the customer cross-reference to the trading partner account returned by CLN_SYNCITEM_PKG.GET_CUST_ACCT_ID. The optional join to MTL_ITEM_REVISIONS, plus the correlated subquery selecting MAX(REVISION), means one row is returned per item exposing only the latest revision. A profile-driven filter, CLN_ITEM_SEND_CUST_XREF_ONLY, allows implementations to restrict output to items that carry a customer item number. Typical users are integration developers, RosettaNet/B2B administrators, and report authors needing the same item header projection used by the connector.

Underlying Base Objects

The documented base objects are MTL_SYSTEM_ITEMS_VL, MTL_CUSTOMER_ITEM_XREFS_V, MTL_ITEM_REVISIONS (synonym), PO_HAZARD_CLASSES_TL (synonym), and the packages CLN_SYNCITEM_PKG, FND_PROFILE, and ARP_ADDR_PKG. MTL_SYSTEM_ITEMS_VL is the primary driver, supplying item attributes and description. MTL_CUSTOMER_ITEM_XREFS_V is outer-joined on INVENTORY_ITEM_ID and constrained to the ordering customer. MTL_ITEM_REVISIONS is outer-joined on both INVENTORY_ITEM_ID and ORGANIZATION_ID, with the revision column further filtered by the maximum-revision subquery. PO_HAZARD_CLASSES_TL is outer-joined by HAZARD_CLASS_ID and restricted to the session language via USERENV('LANG'). CLN_SYNCITEM_PKG provides the current customer account ID used in the MTL_CUSTOMER_ITEM_XREFS_V predicate, while FND_PROFILE supplies the CLN_ITEM_SEND_CUST_XREF_ONLY value that governs whether a customer item number is mandatory in the result set.

Key Columns

Common Use Cases and Queries

The primary use case is generating the Sync Item header for RosettaNet outbound processing and for validating what the connector will publish. A simple query returns the latest revision of each eligible item:

  • SELECT inventory_item_id, organization_id, concatenated_segments, item_description, revision, effectivity_date, customer_item_number FROM cln_itemmst_itemheader_v WHERE organization_id = :org_id;
  • SELECT concatenated_segments, revision, effectivity_date, unit_weight, weight_uom_code FROM cln_itemmst_itemheader_v WHERE inventory_item_id = :item_id;
  • SELECT customer_id, customer_item_number, concatenated_segments FROM cln_itemmst_itemheader_v WHERE customer_item_number IS NOT NULL;

Because the view already enforces the latest-revision logic and the partner cross-reference constraint, developers should not re-apply revision filtering, though adding ORGANIZATION_ID or INVENTORY_ITEM_ID predicates preserves performance. The view is read-only and should be treated as an integration contract when the CLN module is implemented.