Results for “allocation_rule_name”

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

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:

• RESOURCE_ID – The resource (machine, person, or equipment) to which the hierarchy and allocation rule applies.
• DEPARTMENT_ID – Foreign key to BOM_DEPARTMENTS, identifying the owning department or resource group.
• ORGANIZATION_ID – The inventory organization context for the resource.
• SR_INSTANCE_ID – The source instance identifier for multi-instance or source-system segregation.
• DEMAND_CLASS – The demand class against which the allocation is scoped, allowing demand-class-specific allocation behavior.
• ALLOCATION_RULE_NAME – The named allocation rule being applied to the resource.
• ASSIGNMENT_TYPE – Indicates how the resource is assigned within the hierarchy.
• ALLOCATION_PERCENT – The percentage of resource capacity allocated to the rule or assignment.
• EFFECTIVE_DATE and DISABLE_DATE – The date-bounded validity window for the rule.
• PRIORITY and SERVICE_LEVEL – Sequencing and service-level parameters influencing allocation decisions.
• TIME_PHASE_ID – Foreign key to MSC_ALLOC_RULE_TIME_PHASES, tying the rule to a specific time-phase definition.
• PARTNER_ID and PARTNER_SITE_ID – Trading partner and site context, with PARTNER_SITE_ID referencing MSC_TRADING_PARTNER_SITES.
• LEVEL_ID, LEVEL_ALLOC_PERCENT, LEVEL_PRIORITY – Hierarchy level identifiers and their associated allocation and priority values, with LEVEL_ID referencing MSD_LEVELS.
• MIN_LEVEL_ALLOC_PERCENT and MIN_ALLOCATION_PERCENT – Minimum allocation thresholds used to enforce floor values during planning.

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.