Search Results msd_objects_v




Overview
Explore in AI Chat

Overview

MSD_OBJECTS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite environments spanning 12.1.1 and 12.2.2. It resides within the MSD product family, commonly identified as Demand Planning under the Advanced Supply Chain Planning and Demand Planning (MSC/MSD) modules of the Value Chain Planning suite. Its documented purpose is to present a consolidated listing of "all MSD Objects" — a unified registry of the principal Demand Planning entities such as levels, hierarchies, dimensions, and demand plans.

The view plays a metadata-oriented role rather than a transactional one. It abstracts multiple separate MSD tables behind a single, uniform three-column interface, allowing reporting tools, custom concurrent programs, and integration layers to enumerate Demand Planning objects without maintaining separate queries against each underlying source object. Because it is a view and not a table, no data is duplicated; every query resolves dynamically against the underlying MSD and FND objects. In 12.2.2 the object is documented as VALID, with the standard legal notice that the view text is Oracle proprietary and confidential.

Underlying Base Objects

The documented definition of MSD_OBJECTS_V is a UNION of four SELECT statements, each contributing a distinct category of MSD object with a numeric OBJECT_TYPE discriminator. The referenced base objects are:

The FND_LOOKUP_VALUES_VL component is notable because it crosses module boundaries: it is the standard Applications Foundation lookup view rather than an MSD-owned object, which means dimension values are governed by lookup maintenance rather than by Demand Planning setup screens. The MSD-named base objects are exposed as synonyms, indicating that the actual physical tables live in the MSD schema and are referenced from APPS through public or private synonyms.

Key Columns

The view exposes exactly three columns, which are consistent across all four UNION branches:

  • OBJECT_ID — a character representation of the source identifier. The view text applies TO_CHAR to LEVEL_ID, HIERARCHY_ID, and DEMAND_PLAN_ID, while the lookup branch passes LOOKUP_CODE directly. This implicit conversion ensures a uniform VARCHAR2-style key column; consumers should be aware that IDs are not numeric in this view and that the lookup branch uses alphanumeric codes.
  • OBJECT_NAME — the descriptive name of the object: LEVEL_NAME, HIERARCHY_NAME, MEANING, or DEMAND_PLAN_NAME depending on the row's origin. This is the user-facing label suitable for display in lists of values and reports.
  • OBJECT_TYPE — a single-character discriminator identifying the object category: '5' for levels, '4' for hierarchies, '3' for dimensions, and '2' for demand plans. Because OBJECT_TYPE is a string literal rather than a foreign key, no lookup validation is enforced by the database; the meaning of each code must be supplied by the consuming application or report.

No other columns, including descriptive attributes, org context, or audit fields, are exposed. The view is therefore a lightweight enumeration aid rather than a full object catalog.

Common Use Cases and Queries

Typical uses include populating selection lists in custom forms or OAF pages, driving concurrent programs that iterate over Demand Planning hierarchies and levels, and supporting metadata extraction or migration utilities that must classify MSD objects by type. Because ORDER BY is absent from the view definition, any deterministic ordering must be applied by the caller.

  • List all objects with names and type codes:

SELECT object_id, object_name, object_type FROM apps.msd_objects_v ORDER BY object_type, object_name;

  • Retrieve only demand plans (OBJECT_TYPE '2'):

SELECT object_id, object_name FROM apps.msd_objects_v WHERE object_type = '2';

  • Count objects by category, useful for validation or gap analysis:

SELECT object_type, COUNT(*) FROM apps.msd_objects_v GROUP BY object_type ORDER BY object_type;

  • Locate a specific object by name:

SELECT object_id, object_type FROM apps.msd_objects_v WHERE object_name = :p_name;

When embedding these queries, callers should reference the view as APPS.MSD_OBJECTS_V or ensure the APPS schema is on the session's current schema path. Because the view is read-only and derived entirely from other objects, no DML should be attempted against it.