Results for “cln_procat_itemcategory_v”

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

Overview

CLN_PROCAT_ITEMCATEGORY_V is an APPS-owned database view within the Oracle E-Business Suite Release 12.1.1 / 12.2.2 environment. It belongs to the CLN product family, formally designated as the Supply Chain Trading Connector for RosettaNet. The view exposes item-to-category assignments drawn from the default item category set, resolving both the concatenated category name and the translated category description for a given inventory item and organization. Within the RosettaNet integration flow, this view serves as a denormalized read layer over the standard Oracle Inventory category model, allowing Trading Partner and Product Catalog (PROCAT) processing logic to retrieve category identifiers, names, and descriptions without joining the underlying category tables directly.

The documentation note describes the view as "a view of the available currencies the price list can be converted into." This description is inconsistent with the actual view text, which selects item category columns (INVENTORY_ITEM_ID, ORGANIZATION_ID, CATEGORY_ID, CATEGORY_NAME, CATEGORY_DESCRIPTION) rather than any currency-related data. The implementation excerpt is authoritative, and consumers should treat the view strictly as an item category resolution view. The apparent description mismatch is a documentation artifact and does not affect runtime behavior. The user search term "category_description" maps directly to the MCT.DESCRIPTION column exposed as CATEGORY_DESCRIPTION.

Underlying Base Objects

The view is defined over five documented base objects, three of which are synonyms and one a view:

• MTL_ITEM_CATEGORIES (SYNONYM) — the primary item-category assignment table, aliased MIC. Supplies INVENTORY_ITEM_ID, ORGANIZATION_ID, CATEGORY_ID, and CATEGORY_SET_ID.
• MTL_CATEGORIES_V (VIEW) — aliased MC. Supplies the concatenated category segment string exposed as CATEGORY_NAME and links to the category structure.
• MTL_CATEGORY_SETS_B (SYNONYM) — aliased MCSB. Provides the base category set definition and STRUCTURE_ID used to join the categories view.
• MTL_DEFAULT_CATEGORY_SETS (SYNONYM) — aliased MDC. Restricts the result set to the default category set for the functional area.
• MTL_CATEGORIES_TL (SYNONYM) — aliased MCT. The translation table supplying the language-specific DESCRIPTION column.

Joins are driven by CATEGORY_SET_ID across MIC, MDC, and MCSB, by STRUCTURE_ID between MC and MCSB, and by CATEGORY_ID between MIC, MC, and MCT. The predicate MDC.FUNCTIONAL_AREA_ID = 7 confines the output to the Product Catalog functional area, and MCT.LANGUAGE = USERENV('LANG') enforces session-language filtering.

Key Columns

• INVENTORY_ITEM_ID — the unique identifier of the inventory item.
• ORGANIZATION_ID — the inventory organization in which the item-category assignment applies.
• CATEGORY_ID — the category identifier from the default category set.
• CATEGORY_NAME — the concatenated category segment string derived from MTL_CATEGORIES_V.
• CATEGORY_DESCRIPTION — the translated description of the category from MTL_CATEGORIES_TL, filtered by the session language.

Common Use Cases and Queries

Typical usages include RosettaNet product catalog extraction, item categorization reporting, and integration staging. A common query retrieves category assignments for a specific item and organization:

• SELECT category_id, category_name, category_description FROM cln_procat_itemcategory_v WHERE inventory_item_id = :p_item AND organization_id = :p_org;
• SELECT inventory_item_id, category_name, category_description FROM cln_procat_itemcategory_v WHERE category_id = :p_category;
• SELECT category_description, COUNT(*) FROM cln_procat_itemcategory_v GROUP BY category_description;

Because the view enforces functional area 7 and session language, no additional filtering on category set or language is normally required. Performance depends on the item category indexes on MTL_ITEM_CATEGORIES; filtering by INVENTORY_ITEM_ID and ORGANIZATION_ID is recommended for large volumes.