Results for “msc_assignment_sets_u2”

12 results




Overview
Explore in AI Chat

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

Overview

MSC.MSC_ASSIGNMENT_SETS is a transactional data table in the Advanced Supply Chain Planning (ASCP) schema. It stores the assignment of sourcing rules or bills of distribution to an item, organization, category, or at the global level. Records in this table are originated either at the planning server or within a source application instance, and the table therefore acts as a consolidation point between planning data and upstream source systems. Physically, the table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and its 34 documented columns follow Oracle EBS standards for Who columns, concurrent request columns, and a descriptive flexfield (ATTRIBUTE_CATEGORY plus ATTRIBUTE1 through ATTRIBUTE15).

From a data-modeling perspective, the heuristic Data Vault classification mined from the foreign-key structure is hub-leaning. This suggests MSC_ASSIGNMENT_SETS can be modelled as a hub keyed on ASSIGNMENT_SET_ID, with dependent attributes and references from satellite or link structures. It is important to recognize that the table functions as a header-like container: the actual assignment detail is held in MSC_SR_ASSIGNMENTS, while MSC_PLANS references an assignment set to govern planning behavior.

Key Information Stored

The following columns represent the most operationally significant attributes documented for this table:

  • ASSIGNMENT_SET_ID — Surrogate primary key of the table, enforced by the primary key constraint MSC_ASSINGMENT_SETS_PK and by the unique index MSC_ASSIGNMENT_SETS_U1. This is the column returned when a user searches for "msc_assignment_sets_u1".
  • ASSIGNMENT_SET_NAME — The business-facing name of the assignment set (VARCHAR2(34)); it is the second business-key candidate, enforced by unique index MSC_ASSIGNMENT_SETS_U2.
  • SR_INSTANCE_ID — Identifies the source application instance from which the record was collected; it participates in the non-unique index MSC_ASSIGNMENTS_SETS_U3 alongside SR_ASSIGNMENT_SET_ID.
  • SR_ASSIGNMENT_SET_ID — The assignment set identifier as it exists in the source application instance, used to reconcile the planning copy with the originating system.
  • DESCRIPTION — Free-text description of the assignment set (VARCHAR2(80)).
  • DELETED_FLAG — Indicates whether the row is no longer valid; a value of SYS_YES marks the row for deletion during the next collection cycle.
  • REFRESH_NUMBER — Refresh or collection generation number used to track data currency.
  • COLLECTED_FLAG — Flag indicating the collection status of the record.
  • ASSIGNMENT_ASSOCIATION_TYPE — Classifies the association type of the assignment set (for example, the level at which sourcing is applied).
  • Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN provide audit traceability.
  • Concurrent Who columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE identify the concurrent program that created or last modified the row.
  • Descriptive flexfield columns — ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 support extensible, customer-defined attributes.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios revolve around identifying which assignment sets exist, which source instance they came from, and which plans or assignments depend on them. A common lookup by name is:

  • SELECT assignment_set_id, assignment_set_name, sr_instance_id, deleted_flag FROM msc.msc_assignment_sets WHERE assignment_set_name = :name;
  • SELECT * FROM msc.msc_assignment_sets WHERE assignment_set_id = :id; — direct access via MSC_ASSIGNMENT_SETS_U1.
  • Filtering active versus logically deleted records: WHERE deleted_flag IS NULL OR deleted_flag <> sys_yes;
  • Reconciling planning data with source systems using SR_INSTANCE_ID and SR_ASSIGNMENT_SET_ID, which is the access path provided by index MSC_ASSIGNMENTS_SETS_U3.
  • Auditing collection lineage through REFRESH_NUMBER and the concurrent Who columns to determine when an assignment set was last refreshed.

Because ASCP is a planning schema, queries against this table are most often executed within the planning server context or through the source application interfaces rather than directly by end users.

Related Objects

The following objects are the most significant dependencies for MSC_ASSIGNMENT_SETS:

  • MSC.MSC_PLANS — References MSC_ASSIGNMENT_SETS.ASSIGNMENT_SET_ID; each plan is associated with an assignment set that governs its sourcing behavior.
  • MSC.MSC_SR_ASSIGNMENTS — References MSC_ASSIGNMENT_SETS.ASSIGNMENT_SET_ID; holds the assignment of sourcing rules or bills of distribution within a set.
  • Primary key MSC_ASSINGMENT_SETS_PK — Enforces uniqueness on ASSIGNMENT_SET_ID.
  • Unique index MSC_ASSIGNMENT_SETS_U1 — The unique access path on ASSIGNMENT_SET_ID.
  • Unique index MSC_ASSIGNMENT_SETS_U2 — The unique access path on ASSIGNMENT_SET_NAME.
  • Non-unique index MSC_ASSIGNMENTS_SETS_U3 — Supports joins on SR_INSTANCE_ID and SR_ASSIGNMENT_SET_ID.