Results for “msd_category_sets_v”

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

Overview

MSD_CATEGORY_SETS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the MSD product family, which covers Demand Planning functionality within the Oracle Advanced Planning and Scheduling / Demantra-adjacent stack. As documented in the ETRM metadata, the view's stated purpose is to expose Demand Planning category sets for consumption by planning engines, concurrent programs, and downstream integrations.

The view functions as a simplified projection rather than a full-fledged denormalized reporting object. Its role is to present a minimal, stable interface to category set reference data so that Demand Planning logic does not need to query the broader MTL_CATEGORY_SETS structure directly. Because it is a view and not a table, it carries no independent storage, no independent DBA maintenance requirements, and no independent index structures. Its validity status of VALID confirms that the underlying definition resolves cleanly against the referenced synonym in a standard installation.

Users searching for category_set_name encounter this view because it is one of the few MSD-owned objects that exposes CATEGORY_SET_NAME in a compact form, making it convenient for joins and lookups where the full category set record is not required.

Underlying Base Objects

The ETRM documentation records a single referenced base object: MTL_CATEGORY_SETS, exposed in the APPS schema as a synonym. Despite the synonym notation, MTL_CATEGORY_SETS is the inventory module's master category set definition table, and it resides physically in the INV schema with a public or APPS synonym providing access.

The view definition is documented as a straightforward two-column projection:

• SELECT CATEGORY_SET_ID, CATEGORY_SET_NAME FROM MTL_CATEGORY_SETS

No filtering predicates, joins, or aggregation appear in the documented view text. Consequently, MSD_CATEGORY_SETS_V returns one row per category set defined in the inventory category set table, including flexible structure category sets, that are visible to the APPS schema. Because the view performs no restriction, any row-level security or operating unit filtering present in the base table continues to apply through normal Oracle EBS data access mechanisms.

This one-to-one relationship means the view inherits the cardinality, uniqueness, and integrity constraints of MTL_CATEGORY_SETS, where CATEGORY_SET_ID is the primary key and CATEGORY_SET_NAME carries a uniqueness constraint.

Key Columns

The view exposes exactly two columns according to the documented metadata:

• CATEGORY_SET_ID — The numeric primary key of the category set. This is the value used as a foreign key in category assignment tables, item-category relationships, and planning setup records. It is the join key of choice when relating this view to MTL_CATEGORIES, MTL_ITEM_CATEGORIES, or planning-specific setup tables.
• CATEGORY_SET_NAME — The descriptive, user-defined name of the category set. This is the column matched when searching for "category_set_name" and is the value presented to users in Oracle Forms, OAF pages, and planning workbenches. Names are typically unique within the category set namespace, though naming conventions depend on installation.

No additional attributes such as description, enabled flag, or structure identifier are exposed. Consumers requiring those attributes must query MTL_CATEGORY_SETS directly.

Common Use Cases and Queries

The view is most commonly used for name-to-ID resolution during Demand Planning data loads and for validation lookups in custom concurrent programs and reports. A typical lookup retrieves the identifier for a known name:

• SELECT CATEGORY_SET_ID FROM MSD_CATEGORY_SETS_V WHERE CATEGORY_SET_NAME = :p_name;

The inverse pattern lists available category sets for a value set or LOV:

• SELECT CATEGORY_SET_NAME FROM MSD_CATEGORY_SETS_V ORDER BY CATEGORY_SET_NAME;

Integration queries frequently join the view to category-level data to produce human-readable extracts:

• SELECT v.CATEGORY_SET_NAME, c.CATEGORY_NAME FROM MSD_CATEGORY_SETS_V v, MTL_CATEGORIES c WHERE c.CATEGORY_SET_ID = v.CATEGORY_SET_ID;

Because the view is a thin projection, performance characteristics mirror those of MTL_CATEGORY_SETS. Queries filtering on CATEGORY_SET_ID benefit from the base table's primary key index, while name-based filters rely on the unique index supporting CATEGORY_SET_NAME. In high-volume planning loads, resolving names to IDs once and caching them is preferable to repeated single-row lookups against the view.