Results for “msc_allocation_assignments_v”

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

Overview

MSC_ALLOCATION_ASSIGNMENTS_V is a reporting view owned by the APPS schema within the MSC (Advanced Supply Chain Planning) product family of Oracle E-Business Suite, and is available in releases 12.1.1 and 12.2.2. The view exposes allocation assignment records, which define how sourcing or supply is apportioned against items, organizations, departments, resources, or resource groups. Allocation rules are consumed by Advanced Supply Chain Planning to distribute constrained or unconstrained supply across competing demands during the planning and sourcing calculation cycle.

The view is read-only and is intended for inquiry, reporting, and integration rather than transactional maintenance. It presents a denormalized, presentation-friendly projection of the underlying assignment table by resolving numeric identifiers into human-readable names through calls to the MSC_GET_NAME package. This makes it suitable for direct consumption by BI Publisher reports, Oracle Discoverer workbooks, custom concurrent programs, and outbound interfaces that must display meaningful allocation rule identifiers instead of internal surrogate keys.

Underlying Base Objects

The view is defined over two documented referenced objects. The first is MSC_ALLOCATION_ASSIGNMENTS, resolved as a synonym in the APPS schema, which stores the persisted assignment rows including the ALLOCATION_RULE_NAME value that users search on. The second is the MSC_GET_NAME package, a PL/SQL utility used to translate stored identifiers into descriptive text at query time.

Three translation calls appear in the view text. The ITEM_NAME function resolves INVENTORY_ITEM_ID into an item description, ORG_CODE resolves ORGANIZATION_ID together with SR_INSTANCE_ID into an organization code, and the remaining translation columns for department and resource codes are projected as NULL in the view text and are therefore not populated from the function calls even though the column headings exist. No joins to master tables such as MTL_SYSTEM_ITEMS or HR_ALL_ORGANIZATION_UNITS are performed; all descriptive resolution is delegated to the package. Because the view is a simple projection with scalar function calls, it does not aggregate or restrict rows, so one row in MSC_ALLOCATION_ASSIGNMENTS yields exactly one row in the view.

Key Columns

• ASSIGNMENT_ID — Primary identifier for the allocation assignment record.
• ASSIGNMENT_TYPE — Discriminates the kind of assignment, indicating whether the rule applies to an item, category, organization, department, resource, or resource group.
• ALLOCATION_RULE_NAME — The name of the allocation rule attached to the assignment; this is the column most frequently searched by users.
• INVENTORY_ITEM_ID / ITEM_NAME — The item key and its resolved descriptive name.
• CATEGORY_SET_ID / CATEGORY_NAME — The category set and category to which the assignment applies when the assignment type is category-based.
• SR_INSTANCE_ID / ORGANIZATION_ID / ORGANIZATION_CODE — Source system instance, organization key, and resolved organization code.
• DEPARTMENT_ID / DEPARTMENT_CODE, RESOURCE_ID / RESOURCE_CODE — Department and resource keys with their descriptive counterparts; the code columns are projected as NULL in the view text.
• RESOURCE_GROUP — Grouping of resources to which the allocation applies.
• DESCRIPTION — Descriptive text for the assignment; also projected as NULL in the view text.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle flexible attribute set for extensibility.

Common Use Cases and Queries

Typical usage includes auditing which allocation rules are assigned to which items or organizations, validating rule configuration before a plan run, and building reports that trace allocation rule usage across the supply chain.

To locate every assignment for a named allocation rule:

• SELECT assignment_id, assignment_type, item_name, organization_code, allocation_rule_name FROM msc_allocation_assignments_v WHERE allocation_rule_name = :p_rule_name ORDER BY organization_code, item_name;

To list distinct allocation rules referenced in the planning data:

• SELECT DISTINCT allocation_rule_name FROM msc_allocation_assignments_v ORDER BY allocation_rule_name;

To inspect item-level assignments for a single organization:

• SELECT item_name, allocation_rule_name, resource_group FROM msc_allocation_assignments_v WHERE organization_id = :p_org_id AND assignment_type = 'ITEM';

Callers should note that ORGANIZATION_CODE and ITEM_NAME depend on MSC_GET_NAME execution and may return NULL when the referenced item or organization is not resolvable in the current instance. Because DEPARTMENT_CODE, RESOURCE_CODE, and DESCRIPTION are projected as NULL, reports requiring those values must source them from the base table or an alternative view.