Search Results msc_item_hierarchy_mv




Overview
Explore in AI Chat

Overview

MSC_ITEM_HIERARCHY_MV is an Advanced Supply Chain Planning (MSC) materialized view owned by the APPS schema in Oracle EBS 12.1.1 and 12.2.2. It stores item-level allocation rule definitions used by the planning engine to distribute constrained supply across demand classes, trading partners, and organizational hierarchies. The object carries twenty documented columns and is classified physically as a table within the ETRM 12.2.2 data model.

The supplied relationship metadata yields a heuristic Data Vault classification of standalone. This modeling suggestion reflects the absence of a parent-child dependency rooted in a single natural hub; instead, the object behaves as a satellite-like structure anchored to combinations of item, organization, allocation rule, and partner attributes. Because the foreign keys point outward to configuration entities rather than to one dominant business key, no hub or link construct is asserted. When reverse-engineering this object, treat the classification as a starting suggestion rather than a normative design.

Functionally, the materialized view supports allocation logic in supply chain planning: it resolves how much of an available quantity is assigned to a given demand class or partner site, subject to priority, effective dating, and service level targets.

Key Information Stored

The most significant attributes fall into four groupings.

Surrogate versus business key: the documented metadata does not expose a single-column surrogate primary key. The closest business-key candidates are the composite of INVENTORY_ITEM_ID, ORGANIZATION_ID, and ALLOCATION_RULE_NAME combined with the effective dating columns. Where unique indexes exist in a given installation, they are validated against this composite rather than against any single column.

Foreign key columns TIME_PHASE_ID, PARTNER_SITE_ID, and LEVEL_ID act as dimensional references; PARTNER_ID links the trading partner, though its FK target is not documented in the supplied metadata.

Common Use Cases and Queries

Planners and reporting developers query this object to audit allocation behavior, trace rule coverage, and explain planning output.

  • Rule inventory by item and organization: SELECT inventory_item_id, organization_id, allocation_rule_name, allocation_percent FROM msc_item_hierarchy_mv WHERE organization_id = :org_id AND effective_date <= SYSDATE AND (disable_date IS NULL OR disable_date > SYSDATE);
  • Partner-level allocation analysis joining MSC_TRADING_PARTNER_SITES on PARTNER_SITE_ID to resolve site names.
  • Time-phase auditing joining MSC_ALLOC_RULE_TIME_PHASES on TIME_PHASE_ID to inspect phased allocation curves.
  • Level-based aggregation joining MSD_LEVELS on LEVEL_ID to roll up allocation percentages by hierarchy level.
  • Service level and priority exception reporting to identify rules whose ALLOCATION_PERCENT falls below MIN_LEVEL_ALLOC_PERCENT.

Because the object is a materialized view, refresh timing should be confirmed before relying on it for near-real-time reporting.

Related Objects

  • MSC_ALLOC_RULE_TIME_PHASES — joined on TIME_PHASE_ID; provides time-phased allocation definitions.
  • MSC_TRADING_PARTNER_SITES — joined on PARTNER_SITE_ID; resolves partner site detail.
  • MSD_LEVELS — joined on LEVEL_ID; supplies hierarchy level definitions used by level allocation columns.
  • MSC_SYSTEM_ITEMS — referenced via INVENTORY_ITEM_ID for item descriptions and planner attributes.
  • MSC_ORG_ASSIGNMENTS — correlates ORGANIZATION_ID to planning organization setup.
  • MSC_TRADING_PARTNERS — candidate parent of PARTNER_ID for partner naming.

Joins should always be filtered on effective dating to avoid returning expired allocation rules.