Results for “cln_itemmst_ctlg_cats_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CLN_ITEMMST_CTLG_CATS_V is a read-only database view owned by the APPS schema within the Oracle E-Business Suite environment. It belongs to the CLN product family, formally identified as the Supply Chain Trading Connector for RosettaNet. As its description states, the view presents the available item catalog categories within an organization. In practice, it serves as a denormalized, integration-friendly surface that exposes the relationship between stocked items defined in Oracle Inventory and the catalog groups to which those items are assigned, returning the human-readable concatenated catalog category identifier rather than the raw internal numeric key.

The view is especially relevant in RosettaNet-based B2B integration flows, where outbound or inbound catalog messages must reference item categories in a form partner systems can interpret. Because the underlying catalog group table uses a key flexfield structure, a "flat" concatenated representation is required for transmission and reporting. This view delivers exactly that projection, making it a convenient building block for interface programs, trading partner extracts, and operational reports that need a compact catalog category listing.

Underlying Base Objects

The view text documents a two-object join. Both referenced objects are exposed through synonyms and resolve to Oracle Inventory base tables:

• MTL_SYSTEM_ITEMS_B (alias MSIB) — the master item definition table, restricted in the view to the organization and catalog group assignment attributes. It supplies ORGANIZATION_ID and ITEM_CATALOG_GROUP_ID.
• MTL_ITEM_CATALOG_GROUPS_KFV (alias MICGK) — a key flexfield view over the item catalog groups entity. The _KFV suffix indicates it exposes the CONCATENATED_SEGMENTS column, which renders the flexfield segments as a single delimited string.

The join predicate links MSIB.ITEM_CATALOG_GROUP_ID to MICGK.ITEM_CATALOG_GROUP_ID. The SELECT DISTINCT clause eliminates duplicate rows that arise because many items in the same organization typically share one catalog group. This is the key behavior to understand: although the view is built over an item table, the DISTINCT collapses it down to a distinct list of organization/catalog-group combinations, not a per-item listing.

Key Columns

• ORGANIZATION_ID — the inventory organization in which the catalog category is in use. Sourced from MTL_SYSTEM_ITEMS_B and critical for multi-org filtering.
• ITEM_CATALOG_GROUP_ID — the internal surrogate key identifying the catalog group. Useful for joins back to catalog-group base tables and for foreign-key style references.
• CONCATENATED_SEGMENTS — the flexfield-rendered, user-visible category name derived from MTL_ITEM_CATALOG_GROUPS_KFV. This is the value most commonly consumed by external systems and reports.

Common Use Cases and Queries

Typical scenarios include populating category lists for RosettaNet catalog documents, validating that a category exists in an organization before publishing, and building reference extracts for trading partners. Because the object is a view, it should only ever be queried, never modified.

A straightforward listing for one organization:

• SELECT organization_id, item_catalog_group_id, concatenated_segments FROM apps.cln_itemmst_ctlg_cats_v WHERE organization_id = :org_id ORDER BY concatenated_segments;

To enumerate all categories across organizations:

• SELECT organization_id, concatenated_segments FROM apps.cln_itemmst_ctlg_cats_v ORDER BY organization_id, concatenated_segments;

To resolve a category name to its internal key for downstream processing:

• SELECT item_catalog_group_id FROM apps.cln_itemmst_ctlg_cats_v WHERE organization_id = :org_id AND concatenated_segments = :category_name;

Because the view returns DISTINCT combinations, callers should not assume one row per item. Where per-item catalog assignments are required, query MTL_SYSTEM_ITEMS_B directly and join to MTL_ITEM_CATALOG_GROUPS_KFV using the documented predicate.