Results for “msc_assignment_sets”

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

Overview

MSC_ASSIGNMENT_SETS is a table in the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) product within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the assignment of sourcing rules or bills of distribution to items, organizations, categories, or at the global level. In practice, this table functions as the container that defines an assignment set — a named grouping of sourcing assignments that determines which sourcing logic an organization, item, or category should use during planning. Assignment sets are fundamental to the sourcing engine in ASCP, because they let planners apply a consistent set of sourcing rules or a bill of distribution across a defined scope without redefining rules individually. Oracle's ETRM documentation classifies this object as hub-leaning under heuristic Data Vault modeling, meaning it behaves much like a hub entity: it holds a stable, uniquely identifiable business concept (the assignment set) referenced by several dependent tables.

Key Information Stored

The table contains 34 documented columns. The most important include:

• ASSIGNMENT_SET_ID — the surrogate primary key, enforced by the unique index MSC_ASSINGMENT_SETS_PK and additionally by MSC_ASSIGNMENT_SETS_U1.
• ASSIGNMENT_SET_NAME — the human-readable business identifier, enforced as a unique business key through MSC_ASSIGNMENT_SETS_U2.
• DESCRIPTION — free-text description of the assignment set.
• SR_INSTANCE_ID — the source instance identifier linking the record to its originating source system.
• SR_ASSIGNMENT_SET_ID — the corresponding assignment set identifier on the source side, used for synchronization.
• ASSIGNMENT_ASSOCIATION_TYPE — indicates the level at which the set is associated (item, organization, category, or global).
• DELETED_FLAG — soft-delete marker indicating whether the set has been logically removed.
• COLLECTED_FLAG — indicates whether the record was collected from the source system during data collection.
• REFRESH_NUMBER — tracks the collection or refresh cycle to which the record belongs.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program audit columns identifying the process that created or last updated the record.
• Standard WHO columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) and the ATTRIBUTE1–ATTRIBUTE15 descriptive flexfield columns for extensibility.

Common Use Cases and Queries

Assignment sets are referenced when validating or troubleshooting sourcing logic in ASCP. A typical query lists assignment sets used by a plan, enabled and not soft-deleted:

SELECT a.assignment_set_id, a.assignment_set_name, a.assignment_association_type
FROM msc_assignment_sets a
JOIN msc_plans p ON p.assignment_set_id = a.assignment_set_id
WHERE a.deleted_flag = 'N' AND p.plan_id = :plan_id;

Planners and developers also use the table to confirm collection status via COLLECTED_FLAG and REFRESH_NUMBER, to identify globally scoped assignments through ASSIGNMENT_ASSOCIATION_TYPE, and to drive reports that compare source (SR_ASSIGNMENT_SET_ID) and destination assignment definitions. Data fix and reconciliation scripts rely on the business-key indexes to resolve duplicates by ASSIGNMENT_SET_NAME.

Related Objects

The most significant related objects are the tables that reference MSC_ASSIGNMENT_SETS through foreign keys:

• MSC_PLANS.ASSIGNMENT_SET_ID — links each plan to the assignment set it uses for sourcing.
• MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID — the detail table holding individual sourcing assignments belonging to a set.

Because these are the documented FK relationships, MSC_PLANS and MSC_SR_ASSIGNMENTS are the primary join partners for reporting and validation queries. The two unique indexes, MSC_ASSIGNMENT_SETS_U1 and MSC_ASSIGNMENT_SETS_U2, further define how the table is looked up by ID and by name in standard ASCP processing.