Results for “msc_sr_assignments”

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

Overview

MSC_SR_ASSIGNMENTS is a core table in the Oracle Advanced Supply Chain Planning (ASCP) module, owned by the MSC schema. It stores the assignment of sourcing rules or bills of distribution to specific planning entities — items, organizations, categories, or at the global level. In effect, the table defines which sourcing rule applies to what, forming the junction between sourcing definitions and the planning objects they govern. Because sourcing rules drive replenishment, transfer, and procurement suggestions across the plan, MSC_SR_ASSIGNMENTS is a foundational input to ASCP's planning engine.

Each row represents one assignment record. The table contains 46 documented columns and is uniquely identified by the primary key MSC_SR_ASSIGNMENTS_PK on ASSIGNMENT_ID. Two unique indexes exist: MSC_SR_ASSIGNMENTS_U1 on ASSIGNMENT_ID and MSC_SR_ASSIGNMENTS_U2 on the composite (SR_ASSIGNMENT_INSTANCE_ID, SR_ASSIGNMENT_ID). Based on the foreign-key topology — a surrogate primary key with multiple outbound references to dimension-like tables and one inbound reference from MSC_ITEM_SOURCING — the heuristic Data Vault classification is satellite-leaning. In a Data Vault model this object would typically be modeled as a satellite attached to a sourcing-rule hub and related assignment-set links, capturing the descriptive assignment context rather than acting as an independent business hub.

Key Information Stored

The most operationally significant columns are:

• ASSIGNMENT_ID — Surrogate primary key; unique identifier for each assignment row.
• SR_ASSIGNMENT_ID and SR_ASSIGNMENT_INSTANCE_ID — Business-key candidates forming the MSC_SR_ASSIGNMENTS_U2 unique index; represent the sourcing-rule assignment identity across instances.
• ASSIGNMENT_TYPE — Distinguishes whether the assignment is at item, organization, category, or global level.
• SOURCING_RULE_ID and SOURCING_RULE_TYPE — Foreign key to MSC_SOURCING_RULES; identifies the rule or bill of distribution being assigned.
• ASSIGNMENT_SET_ID — Foreign key to MSC_ASSIGNMENT_SETS; groups assignments into reusable sets.
• ORGANIZATION_ID — The inventory organization to which the assignment applies.
• INVENTORY_ITEM_ID — The item to which the assignment applies.
• CATEGORY_SET_ID and CATEGORY_NAME — Category-level targeting of the assignment.
• SR_INSTANCE_ID — Source instance identifier, supporting multi-instance planning.
• PARTNER_ID and SHIP_TO_SITE_ID — Trading partner and ship-to site references.
• DELETED_FLAG, COLLECTED_FLAG, REFRESH_NUMBER — Collection and refresh status indicators used during data collection from source systems.
• Standard EBS audit columns (LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID) and 15 descriptive ATTRIBUTE columns.

Common Use Cases and Queries

Typical uses include verifying that every planned item has a valid sourcing assignment, reconciling collected assignments against source definitions, and reporting assignment coverage by organization or category.

Join assignments to their sourcing rules:

• SELECT a.ASSIGNMENT_ID, a.SOURCING_RULE_ID, a.ASSIGNMENT_TYPE, r.SOURCING_RULE_NAME FROM MSC.MSC_SR_ASSIGNMENTS a, MSC.MSC_SOURCING_RULES r WHERE a.SOURCING_RULE_ID = r.SOURCING_RULE_ID;

Identify unassigned items by organization:

• SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID FROM MSC.MSC_SR_ASSIGNMENTS WHERE DELETED_FLAG = 'N' AND COLLECTED_FLAG = 'Y';

Detect recently refreshed records for incremental processing using REFRESH_NUMBER and LAST_UPDATE_DATE. Assignment sets can be resolved by joining ASSIGNMENT_SET_ID to MSC_ASSIGNMENT_SETS to report which set a rule belongs to.

Related Objects

• MSC_SOURCING_RULES — joined via SOURCING_RULE_ID; the rule being assigned.
• MSC_ASSIGNMENT_SETS — joined via ASSIGNMENT_SET_ID; groups related assignments.
• MSC_ITEM_SOURCING — references MSC_SR_ASSIGNMENTS.ASSIGNMENT_ID; consumes resolved sourcing per item.
• MSC_TRADING_PARTNERS — joined via PARTNER_ID.
• MSC_TRADING_PARTNER_SITES — joined via SHIP_TO_SITE_ID.
• MSC_CATEGORY_SETS — joined via CATEGORY_SET_ID for category-level assignments.
• Additional optional references include JG_ZZ_VAT_ALLOC_RULES via ALLOCATION_RULE_ID and CZ_ITEM_TYPES via ITEM_TYPE_ID.