Search Results msd_sr_it_all_v




Overview
Explore in AI Chat

Overview

MSD_SR_IT_ALL_V is an Oracle E-Business Suite (EBS) source view owned by the APPS schema and associated with the MSD — Demand Planning product family. Its documented purpose is to associate the Interest Type level to the Top level within the Product Dimension hierarchy. The view was originally designed to be applied to an Oracle Sales Online Applications 11i instance, from which the Interest Type to Top level association information is sourced, and it remains valid in EBS 12.1.1 and 12.2.2 where the MSD schema objects are present.

In practice, the view functions as a level-to-parent mapping source. It exposes a uniform column layout (LEVEL_VALUE_PK, LEVEL_VALUE, PARENT_VALUE_PK, PARENT_VALUE, and five generic ATTRIBUTE columns) that downstream Demand Planning and hierarchical staging processes consume. Because the view returns a DISTINCT result set and a single "null parent" seed row, it is intended to be read as a complete, self-contained level association set rather than as a transactional detail view.

Underlying Base Objects

Two generations of metadata describe the definition of this view. The classic (11i-lineage) view text selects from MTL_CATEGORIES_V, joined to an inline subquery on FND_ID_FLEX_STRUCTURES that filters on the key values ID_FLEX_CODE = 'MCAT', APPLICATION_ID = 401, and ID_FLEX_STRUCTURE_CODE = 'SALES_CATEGORIES'. The join condition matches MTL_CATEGORIES_V.STRUCTURE_ID to the structure's ID_FLEX_NUM, and the query restricts results to rows where SEGMENT2 and SEGMENT3 are null. A UNION over DUAL supplies the null-parent seed row using the MSD_SR_UTIL package functions GET_NULL_PK and GET_NULL_DESC.

The documented 12.2.2 metadata lists a broader set of referenced base objects: ENI_DENORM_HIERARCHIES (synonym), the MSD_SR_UTIL package, MTL_CATEGORIES_B_KFV (view), MTL_CATEGORIES_TL (synonym), MTL_DEFAULT_CATEGORY_SETS (synonym), and DUAL (synonym). This reflects the modernized definition, in which the denormalized hierarchy synonym supplies the structural associations that were previously derived from the category flexfield structure query, while MSD_SR_UTIL continues to provide the primary-key and description generation logic that populates the LEVEL_VALUE_PK, LEVEL_VALUE, PARENT_VALUE_PK, and PARENT_VALUE columns.

Key Columns

  • LEVEL_VALUE_PK — Surrogate primary key for the level member (the Interest Type value). Populated in part through MSD_SR_UTIL.GET_ALL_PRD_PK.
  • LEVEL_VALUE — The descriptive level value or segment value, typically the category segment (MC.SEGMENT1) or its description.
  • PARENT_VALUE_PK — Surrogate key of the parent (Top) level member.
  • PARENT_VALUE — Descriptive value of the parent level, produced via MSD_SR_UTIL.GET_ALL_PRD_DESC.
  • ATTRIBUTE1 – ATTRIBUTE5 — Generic placeholders. In the documented definitions these are returned as NULL, but they are conventionally reserved for consumer-specific extension attributes in the Demand Planning hierarchical loader.

Common Use Cases and Queries

The principal use case is populating or reconciling the Interest Type to Top level association in the Product Dimension during a Demand Planning data load. Analysts also query it to verify which Interest Type values resolve to a given Top level member, and to confirm that the null-parent seed row is present.

  • List all level-to-parent associations: SELECT level_value_pk, level_value, parent_value_pk, parent_value FROM apps.msd_sr_it_all_v ORDER BY parent_value, level_value;
  • Retrieve children of a specific parent: SELECT level_value FROM apps.msd_sr_it_all_v WHERE parent_value = :p_top_level;
  • Verify the seed row exists: SELECT COUNT(*) FROM apps.msd_sr_it_all_v WHERE parent_value_pk IS NULL;
  • Reconcile against the category flexfield, joining on ID_FLEX_STRUCTURE_CODE = 'SALES_CATEGORIES' to confirm the structure numbers align.

Because the view depends on MSD_SR_UTIL package functions, query performance is affected by package function execution per row; bulk loads should therefore be scheduled outside peak concurrency windows.