Search Results msc_resource_hierarchy_mv




Overview
Explore in AI Chat

Overview

MSC_RESOURCE_HIERARCHY_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite, residing within the MSC (Advanced Supply Chain Planning) product family. In the 12.1.1 and 12.2.2 releases it functions as a planning-engine data structure that stores resource-level allocation and hierarchy information consumed by Advanced Supply Chain Planning and, by extension, by the constrained and unconstrained planning runs that depend on resource capacity and allocation logic. The object carries a documented physical schema of 21 columns in ETRM 12.2.2 and is declared VALID in the data dictionary.

From a heuristic Data Vault modeling perspective, the metadata describes the object as standalone rather than as a hub, link, or satellite. The presence of a RESOURCE_ID, DEPARTMENT_ID, and ORGANIZATION_ID combination with effective and disable dating suggests that, if modeled dimensionally, this object behaves most like a satin-type satellite record keyed on the resource and organization business key, with individual allocation rules and percentages treated as descriptive attributes. This classification is a modeling suggestion rather than an enforced constraint, and the physical implementation remains a denormalized planning materialized view.

Key Information Stored

The columns most relevant to planning and reporting are:

The documented surrogate key is RESOURCE_ID combined with ORGANIZATION_ID and SR_INSTANCE_ID as the practical unique business key; the metadata does not expose a separate single-column surrogate primary key, so any uniqueness constraint must be derived from the resource-organization-instance combination together with the effective dating window.

Common Use Cases and Queries

Typical uses include validating allocation rules applied to constrained resources, reporting capacity allocation by department or demand class, and diagnosing why a planning run over- or under-allocated a resource. A representative query joins the hierarchy to departments and levels:

  • SELECT m.RESOURCE_ID, m.ORGANIZATION_ID, m.ALLOCATION_PERCENT, m.PRIORITY, m.EFFECTIVE_DATE, m.DISABLE_DATE FROM MSC_RESOURCE_HIERARCHY_MV m WHERE m.ORGANIZATION_ID = :org AND TRUNC(SYSDATE) BETWEEN m.EFFECTIVE_DATE AND NVL(m.DISABLE_DATE, SYSDATE+1);
  • SELECT m.RESOURCE_ID, d.DEPARTMENT_CODE, m.ALLOCATION_RULE_NAME FROM MSC_RESOURCE_HIERARCHY_MV m JOIN BOM_DEPARTMENTS d ON d.DEPARTMENT_ID = m.DEPARTMENT_ID;
  • SELECT m.RESOURCE_ID, l.LEVEL_NAME, m.LEVEL_ALLOC_PERCENT, m.MIN_ALLOCATION_PERCENT FROM MSC_RESOURCE_HIERARCHY_MV m JOIN MSD_LEVELS l ON l.LEVEL_ID = m.LEVEL_ID;

Because the object is a materialized view, queries should account for refresh timing; data may lag the underlying base planning tables depending on the refresh schedule configured by the planning administrator.

Related Objects

The FK metadata identifies four referenced objects that are the primary joins for this materialized view:

  • BOM_DEPARTMENTS – joined on DEPARTMENT_ID for department and resource-group attribution.
  • MSC_ALLOC_RULE_TIME_PHASES – joined on TIME_PHASE_ID for time-phase allocation behavior.
  • MSC_TRADING_PARTNER_SITES – joined on PARTNER_SITE_ID for partner and site context.
  • MSD_LEVELS – joined on LEVEL_ID for hierarchy level definitions.

Additional dependent or closely related planning objects include MSC_RESOURCES, which supplies the master resource definitions keyed by RESOURCE_ID, and the broader MSC allocation rule and capacity tables consumed by the Advanced Supply Chain Planning engine. Reporting and extraction routines should treat this object as a read-only planning artifact rather than a maintainable transactional table.