Results for “msc_resource_instance_reqs”

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

Overview

The MSC_RESOURCE_INSTANCE_REQS table in the MSC schema belongs to Oracle Advanced Supply Chain Planning (ASCP) within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores granular demand-side records for constrained and unconstrained resource instances generated by the ASCP planning engine. Each row represents a requirement placed on a specific resource instance — for example, a machine, tool, or equipment item — across a planning horizon, capturing the load consumed by discrete jobs, repetitive schedules, flow schedules, and planned orders during a plan run.

Because ASCP performs constraint-based planning against both capacity and resource availability, this table serves as the bridge between planning output (supply and demand schedules) and shop-floor resource consumption. Records are created and refreshed on each plan run, with the REFRESH_NUMBER, APPLIED, UPDATED, and STATUS columns tracking lifecycle state across retrieval from source systems such as MES, WIP, and Oracle Manufacturing.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone rather than a classic hub, link, or satellite. In practice, its composite unique key (PLAN_ID, RES_INST_TRANSACTION_ID, SR_INSTANCE_ID) behaves like a link between a plan, a source instance transaction, and a resource instance, while the descriptive and date attributes act as satellite payload. Modelers should treat it as a transactional link-satellite blend anchored on PLAN_ID and SR_INSTANCE_ID.

Key Information Stored

The table contains 59 documented columns. The most significant are:

• PLAN_ID — the ASCP plan to which the row belongs; a primary partitioning and join key.
• RES_INST_TRANSACTION_ID and SR_INSTANCE_ID — together with PLAN_ID, these form the unique business key (index MSC_RESOURCE_INSTANCE_REQS_U1).
• RES_INSTANCE_ID and RESOURCE_ID — identify the specific resource instance and its resource definition.
• SUPPLY_ID and WIP_ENTITY_ID — link the requirement back to the supply order or work-in-process job consuming the capacity.
• OPERATION_SEQ_NUM and RESOURCE_SEQ_NUM — position the requirement within the routing operation sequence.
• DEPARTMENT_ID — the department owning the resource; a foreign key to BOM_DEPARTMENTS.
• ORGANIZATION_ID — the inventory organization context.
• START_DATE, END_DATE, UNBUCKETED_START_DATE, UNBUCKETED_END_DATE — the planned and unbucketed time windows for the load.
• RESOURCE_INSTANCE_HOURS, DAILY_RES_INSTANCE_HOURS, UNADJUSTED_RESOURCE_HOURS, CAPACITY_CONSUMED, CAPACITY_CONSUMED_RATIO — quantity and intensity of resource consumption.
• SERIAL_NUMBER and EQUIPMENT_ITEM_ID — serialized resource tracking.
• STATUS, APPLIED, UPDATED, REFRESH_NUMBER — ASCP refresh lifecycle control columns.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and LAST_UPDATE_DATE — standard concurrency and audit tracking.

Common Use Cases and Queries

Typical usage centers on capacity reporting, constraint analysis, and integration of ASCP output with MES or scheduling systems. Analysts frequently query load versus available capacity per resource, department, and plan. A representative pattern joins resource requirements to plans and departments:

• Identify overloaded resource instances: SELECT RESOURCE_ID, SUM(CAPACITY_CONSUMED) FROM MSC_RESOURCE_INSTANCE_REQS WHERE PLAN_ID = :p GROUP BY RESOURCE_ID HAVING SUM(CAPACITY_CONSUMED) > :capacity;
• Retrieve requirements per WIP job: join on WIP_ENTITY_ID to WIP_DISCRETE_JOBS.
• Reconcile ASCP-generated requirements with department load in BOM_DEPARTMENTS via DEPARTMENT_ID.
• Detect stale records using REFRESH_NUMBER, APPLIED, and STATUS to isolate rows not yet applied by downstream processes.
• Report serialized equipment utilization by filtering on SERIAL_NUMBER and EQUIPMENT_ITEM_ID.

Because rows are plan-scoped, almost all queries should filter by PLAN_ID and, where relevant, ORGANIZATION_ID.

Related Objects

The documented foreign key exposes one explicit relationship; additional joins derive from shared ASCP columns.

• BOM_DEPARTMENTS — joined via DEPARTMENT_ID to resolve department names and capacity.
• MSC_PLANS — joined via PLAN_ID for plan definition and horizon data.
• MSC_RESOURCE_INSTANCES — joined via RES_INSTANCE_ID for resource instance attributes.
• MSC_SUPPLY / supply detail objects — joined via SUPPLY_ID.
• WIP_DISCRETE_JOBS (WIP schema) — joined via WIP_ENTITY_ID.
• BOM_RESOURCES — joined via RESOURCE_ID for resource definitions.
• MTL_SYSTEM_ITEMS_B — joined via EQUIPMENT_ITEM_ID or ASSEMBLY_ITEM_ID.
• HR_ORGANIZATION_UNITS — related through DEPARTMENT_ID for organizational context.

These relationships make MSC_RESOURCE_INSTANCE_REQS a critical integration point between ASCP planning and execution-side resource management.