Results for “msc_operation_resource_seqs”

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

Overview

MSC_OPERATION_RESOURCE_SEQS is a table in the MSC schema owned by Oracle Advanced Supply Chain Planning (ASCP). It stores the operation resources associated with each routing operation within a planning context. In the manufacturing model, a routing defines the sequence of operations required to produce an item, and each operation may consume one or more resources — labor, machines, or equipment. This table captures the resource-level detail for those operations, including scheduling flags, resource offsets, department assignments, and cumulative percentage allocations.

The table resides in the MSC (planning) schema rather than the BOM (manufacturing) schema because ASCP maintains a denormalized, plan-scoped snapshot of routing and resource data. The PLAN_ID column is the partition discriminator that isolates data belonging to one planning run, simulation set, or collection from another. Records are populated through the planning data collection process, which copies source routing and resource information from BOM into the MSC staging and planning tables.

From a dimensional modeling perspective, the heuristic Data Vault classification for this object is hub-leaning. The composite primary key is composed entirely of business identifiers rather than a generated surrogate, which suggests treating the object as a hub or a hub-adjacent reference structure rather than a pure satellite or link.

Key Information Stored

The physical table contains 25 documented columns. The primary key, defined by the constraint MSC_OPERATION_RESOURCE_SEQS_PK, is a five-part composite: PLAN_ID, ROUTING_SEQUENCE_ID, SR_INSTANCE_ID, OPERATION_SEQUENCE_ID, and RESOURCE_SEQ_NUM. Each component is meaningful: PLAN_ID identifies the planning run or simulation, SR_INSTANCE_ID identifies the source application instance from which the data was collected, ROUTING_SEQUENCE_ID groups operations belonging to a common routing, OPERATION_SEQUENCE_ID identifies the parent operation, and RESOURCE_SEQ_NUM orders the resources within that operation.

A secondary unique index, MSC_OPERATION_RESOURCE_SEQS_U1, extends this key with NEW_PLAN_ID and SIMULATION_SET_ID, confirming that the table supports plan-copy and simulation scenarios where the same routing may exist in multiple planning contexts simultaneously. This index represents an alternate business-key candidate.

The most operationally significant columns include:

• SCHEDULE_FLAG — indicates whether the resource participates in scheduling calculations, distinguishing bottleneck resources from those that do not constrain the schedule.
• RESOURCE_OFFSET_PERCENT — the percentage offset within the operation at which the resource is required, used for detailed scheduling and capacity loading.
• CUMMULATIVE_PCT — cumulative percentage used to distribute operation duration or quantity across multiple resources.
• DEPARTMENT_ID — the department responsible for the resource; a foreign key to BOM_DEPARTMENTS.
• ACTIVITY_GROUP_ID and ORGANIZATION_ID — organizational context, with ORGANIZATION_ID identifying the inventory organization.
• REFRESH_NUMBER — tracks the collection refresh that produced the row.
• NEW_PLAN_ID, NEW_PLAN_LIST, APPLIED, SIMULATION_SET_ID — control plan-copy and simulation behavior.
• Standard audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical reporting scenarios include resource load analysis, bottleneck identification, and capacity planning. A common query retrieves all resources for a given routing operation within a specific plan:

SELECT resource_seq_num, department_id, schedule_flag, resource_offset_percent, cummulative_pct FROM msc_operation_resource_seqs WHERE plan_id = :plan_id AND routing_sequence_id = :routing_id AND operation_sequence_id = :op_seq ORDER BY resource_seq_num;

Planners also join this table to bOM resource definitions to compare planning snapshots against source manufacturing data, or aggregate resource counts by department and organization to evaluate capacity distribution. The NEW_PLAN_ID and SIMULATION_SET_ID columns are used when comparing a baseline plan against a simulation to determine which resource definitions changed.

Related Objects

The table participates in a tightly coupled hierarchy within MSC:

• MSC_ROUTING_OPERATIONS — parent table joined on PLAN_ID, ROUTING_SEQUENCE_ID, SR_INSTANCE_ID, and OPERATION_SEQUENCE_ID.
• MSC_OPERATION_RESOURCES — child table joined on the same four columns plus RESOURCE_SEQ_NUM; holds the definitive resource definition.
• MSC_RESOURCE_REQUIREMENTS — child table joined on the full five-column key, storing computed resource requirements generated during planning.
• BOM_DEPARTMENTS — referenced via DEPARTMENT_ID to resolve department names.

These relationships make MSC_OPERATION_RESOURCE_SEQS a central junction between routing structure, resource definitions, and derived capacity requirements in the ASCP data model.