Results for “msc_allocation_assignments”

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

Overview

MSC_ALLOCATION_ASSIGNMENTS is a table in the MSC schema belonging to the Advanced Supply Chain Planning (ASCP) product within Oracle E-Business Suite, present in both the 12.1.1 and 12.2.2 releases. The table describes allocation assignments — records that bind an allocation rule to the specific planning entities (resource groups, departments, organizations, resources, items, or categories) over which the associated rule is applied. Allocation rules define how constrained supply planning distributes resource capacity across competing demands; the assignment records stored here are what determine the scope within which each rule becomes active during a planning run.

The table is owned by MSC and is documented as VALID in the ETRM reference for 12.2.2, with 32 physical columns. Because its primary key is a surrogate identifier and nearly all of its non-key columns are foreign key references to descriptive dimension tables, the heuristic Data Vault classification is link. As a modeling suggestion, this reflects its role as a many-to-many intersection between allocation rules and the planning entities they govern.

Key Information Stored

The surrogate primary key is ASSIGNMENT_ID, enforced by the unique index MSC_ALLOCATION_ASSIGNMENTS_PK. A second unique index, MSC_ALLOCATION_ASSIGNMENTS_U1, also keys on ASSIGNMENT_ID and therefore represents the only documented business-key candidate; no natural composite unique key is exposed in the metadata. The most operationally significant columns are:

• ASSIGNMENT_ID — surrogate primary key identifying each assignment row.
• ASSIGNMENT_TYPE — classifies the assignment, determining which of the subordinate columns carry meaning.
• ALLOCATION_RULE_NAME — foreign key to MSC_ALLOCATION_RULES, naming the rule being assigned.
• RESOURCE_GROUP — the resource group scope for the assignment.
• SR_INSTANCE_ID — source instance identifier; with RESOURCE_GROUP, participates in the composite foreign key to MSC_RESOURCE_GROUPS.
• DEPARTMENT_ID — foreign key to BOM_DEPARTMENTS, identifying the department scope.
• ORGANIZATION_ID — the organization (warehouse) affected by the assignment.
• RESOURCE_ID — the specific resource within a resource group.
• INVENTORY_ITEM_ID — item-level scope for the assignment.
• CATEGORY_NAME / CATEGORY_SET_ID — category-level scope, together identifying the category and its set.
• Attribute columns (ATTRIBUTE_CATEGORY and ATTRIBUTE1–15) — descriptive flexfield storage for customer-defined extensions.
• Standard who-columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — audit and concurrency tracking.

Common Use Cases and Queries

The most frequent access pattern is resolving which allocation rules apply to a given planning entity before or after a plan run. A typical query joins the assignment to its rule and resource group parents:

• List all rules assigned to a resource group: SELECT a.ASSIGNMENT_ID, a.ALLOCATION_RULE_NAME, a.RESOURCE_GROUP, a.SR_INSTANCE_ID FROM MSC.MSC_ALLOCATION_ASSIGNMENTS a WHERE a.RESOURCE_GROUP = :resource_group;
• Identify assignments scoped to a department: SELECT * FROM MSC.MSC_ALLOCATION_ASSIGNMENTS a, BOM.BOM_DEPARTMENTS d WHERE a.DEPARTMENT_ID = d.DEPARTMENT_ID;
• Audit assignments by type to validate that every allocation rule in MSC_ALLOCATION_RULES has at least one assignment.
• Report the effective scope (organization, item, category) of each rule for capacity-planning documentation.
• Extract flexfield attribute values for custom reporting or integration with external planning tools.

Because allocation assignments are consumed during ASCP plan generation, they are also commonly examined when diagnosing unexpected resource-load results or rules that appear not to fire.

Related Objects

The documented foreign key relationships identify the core related objects:

• MSC_ALLOCATION_RULES — joined on ALLOCATION_RULE_NAME; supplies the rule definition each assignment references.
• MSC_RESOURCE_GROUPS — joined on SR_INSTANCE_ID and RESOURCE_GROUP; supplies the resource group context.
• BOM_DEPARTMENTS — joined on DEPARTMENT_ID; supplies department descriptions for departmental assignments.
• MSC_ALLOCATION_ASSIGNMENTS_PK / _U1 — the unique indexes that enforce identity on ASSIGNMENT_ID.
• Additional planning-side references such as organization, item, and resource master tables, which are reached through the ORGANIZATION_ID, INVENTORY_ITEM_ID, and RESOURCE_ID columns respectively.