Results for “msc_resource_requirements”

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

Overview

MSC_RESOURCE_REQUIREMENTS is a table in the MSC schema belonging to the Oracle Advanced Supply Chain Planning (ASCP) product within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the resource requirements generated by existing supplies in a plan. Every time ASCP schedules a planned order, work order, or other supply that consumes manufacturing resources, the resulting capacity consumption is written to this table as one or more resource requirement rows. The table therefore acts as the planning engine's record of how much of each resource — machine, labor, or other capacity unit — is required, across which time buckets, and at which routing operation.

From a dimensional modeling perspective, the documented foreign key structure suggests classifying this object as satellite-leaning. It carries descriptive and measure attributes attached to a primary supply event, with a small number of self-references and lookups to routing and resource definition tables. Analysts modeling the ASCP schema in a data vault or star schema should treat MSC_SUPPLIES as the driving hub/link candidate and this table as the satellite holding resource-hour measures keyed to that supply.

Key Information Stored

The table contains 99 documented columns. The unique index MSC_RESOURCE_REQUIREMENTS_U1 on (PLAN_ID, TRANSACTION_ID, SR_INSTANCE_ID) serves as the documented business-key candidate, while the declared primary key MSC_RESOURCE_REQUIREMENTS_PK spans (PLAN_ID, SR_INSTANCE_ID, TRANSACTION_ID).

• PLAN_ID — identifies the ASCP plan that owns the requirement; nearly every access path filters on this column.
• TRANSACTION_ID — surrogate identifier for the requirement row, part of the primary key.
• SR_INSTANCE_ID — source instance identifier, distinguishing data originating from different source systems; part of the primary key and unique index.
• SUPPLY_ID — the supply (planned order, work order, purchase order) whose execution generates this resource requirement; the main join back to MSC_SUPPLIES.
• ROUTING_SEQUENCE_ID and OPERATION_SEQUENCE_ID — locate the requirement on the routing and its operation sequence.
• RESOURCE_ID and RESOURCE_SEQ_NUM — identify which resource is consumed at that operation.
• DEPARTMENT_ID and SETUP_ID — department and setup context for the operation, referencing BOM_DEPARTMENTS and BOM_SETUP_TYPES.
• START_DATE, END_DATE, BKT_START_DATE, BKT_END_DATE — the requirement window and the planning bucket boundaries used for capacity reporting.
• RESOURCE_HOURS, SET_UP, TEAR_DOWN, TOTAL_RESOURCE_HOURS — the core measures: productive, setup, and teardown hours.
• ASSIGNED_UNITS and DAILY_RESOURCE_HOURS — multi-resource assignment and per-day capacity load.
• PARENT_ID — self-referencing link used to express nested or parent-child requirement structures.
• STATUS, APPLIED, FIRM_FLAG, SCHEDULE_FLAG — control the planning state and firming behavior of the requirement.

Common Use Cases and Queries

The most frequent use case is capacity analysis: determining overloaded resources in a plan by summing resource hours per resource across buckets and comparing against available capacity. A typical pattern:

• Filter by PLAN_ID and START_DATE/BKT_START_DATE to isolate current bucket requirements.
• Aggregate RESOURCE_HOURS and TOTAL_RESOURCE_HOURS by RESOURCE_ID, DEPARTMENT_ID, or ORGANIZATION_ID.
• Join to MSC_SUPPLIES on PLAN_ID and SUPPLY_ID to attribute load to a specific supply or order type (via SUPPLY_TYPE).
• Join to MSC_OPERATION_RESOURCES and BOM_DEPARTMENTS for resource and department names in reporting.

Other scenarios include drill-down from a supply to its operation-level resource breakdown, reconciliation of plan resource hours against actual work order charges, bottleneck identification using MINIMUM_CAPACITY, MAXIMUM_CAPACITY, REMAINING_CAPACITY, and OVERLOADED_CAPACITY, and extraction into a data warehouse for long-term capacity trending. Because the table is plan-scoped, queries should always constrain PLAN_ID to avoid cross-plan contention.

Related Objects

The documented foreign key relationships identify the following significant dependencies:

• MSC_SUPPLIES — joined on PLAN_ID and SUPPLY_ID; the parent supply that generated the requirement.
• MSC_ROUTINGS — joined on PLAN_ID and ROUTING_SEQUENCE_ID; defines the routing sequence.
• MSC_OPERATION_RESOURCE_SEQS — joined on PLAN_ID, ROUTING_SEQUENCE_ID, SR_INSTANCE_ID, OPERATION_SEQUENCE_ID, and RESOURCE_SEQ_NUM; ties the requirement to the operation resource sequence.
• MSC_OPERATION_RESOURCES — joined on PLAN_ID, OPERATION_SEQUENCE_ID, ROUTING_SEQUENCE_ID, RESOURCE_SEQ_NUM, RESOURCE_ID, ALTERNATE_NUM, and SR_INSTANCE_ID; supplies the resource definition used at the operation.
• MSC_RESOURCE_REQUIREMENTS (self-reference) — via PLAN_ID and PARENT_ID; supports hierarchical requirement structures.
• BOM_DEPARTMENTS — referenced via DEPARTMENT_ID for department attributes.
• BOM_SETUP_TYPES — referenced via SETUP_ID for setup classification.

These objects collectively allow full reconstruction of how a supply consumes manufacturing capacity within an ASCP plan.