Search Results msc_job_requirement_ops




Overview
Explore in AI Chat

Overview

MSC_JOB_REQUIREMENT_OPS is a table in the MSC schema (Oracle Advanced Supply Chain Planning) that stores the component and operation-level material requirements derived from job or work order processing during a planning run. It is one of the planning engine's internal staging tables, populated by the ASCP plan generation process, and holds the requirements tied to each operation step of a discrete or process job. In the Oracle EBS 12.1.1 and 12.2.2 data model it acts as a bridge between the transactional job structure in the source instance (typically Manufacturing or a legacy MES loaded through MSC staging) and the planning engine's in-memory netting logic. The table contains 51 documented columns and is owned by the MSC schema. From a Data Vault modeling perspective, the metadata heuristic classifies this object as standalone, meaning it does not carry a single dominant parent-child FK dependency and should be modeled as an independent satellite-like structure rather than a link table. The only documented foreign key is DEPARTMENT_ID, which references BOM_DEPARTMENTS.

Key Information Stored

The primary key is composite. The unique index MSC_JOB_REQUIREMENT_OPS_U1 covers (PLAN_ID, SR_INSTANCE_ID, TRANSACTION_ID, OPERATION_SEQ_NUM, COMPONENT_ITEM_ID, PRIMARY_COMPONENT_ID, COMPONENT_SEQUENCE_ID, SOURCE_PHANTOM_ID). These columns form the business-key candidates that uniquely identify a requirement row. The most important columns fall into three groups:

Audit columns include LAST_UPDATE_DATE, CREATED_BY and REQUEST_ID. Fifteen ATTRIBUTE columns provide descriptive flexibility.

Common Use Cases and Queries

This table is queried primarily by the planning engine itself during MRP/DRP runs, but it is also used by supply chain analysts for exception reporting and troubleshooting component-level shortages.

  • Component shortage analysis for a given plan: SELECT COMPONENT_ITEM_ID, QUANTITY_PER_ASSEMBLY FROM MSC_JOB_REQUIREMENT_OPS WHERE PLAN_ID = :p AND ORGANIZATION_ID = :o.
  • Operation lead time impact: aggregate OPERATION_LEAD_TIME_PERCENT by OPERATION_SEQ_NUM to understand capacity loading.
  • Effectivity validation: filter rows where EFFECTIVITY_DATE is not null to identify components with date-driven requirements.
  • Department load reporting: join DEPARTMENT_ID to BOM_DEPARTMENTS to group requirements by work center department.

Typical reporting scenarios include pre-run requirement validation, post-plan exception queries, and reconciliation of ASCP output back to WIP or BOM structures.

Related Objects

The documented FK relationship is DEPARTMENT_ID referencing BOM_DEPARTMENTS. The following additional objects are directly relevant because they share the same planning-key structure or source the same requirements:

  • BOM_DEPARTMENTS — the parent for DEPARTMENT_ID and the source of work-center department attributes.
  • MSC_JOB_REQUIREMENTS — the header-level requirement table that pairs with the operation-level rows here.
  • MSC_PLANS — provides the PLAN_ID context and plan-level parameters.
  • MSC_SYSTEM_ITEMS — resolves COMPONENT_ITEM_ID to item attributes.
  • MSC_OPERATIONS — supplies OPERATION_SEQ_NUM and routing lead time defaults.
  • WIP_DISCRETE_JOBS_ V (source view) — the source of TRANSACTION_ID, JOB_ID, and job status from the manufacturing application.
  • BOM_COMPONENTS — the source of COMPONENT_SEQUENCE_ID and quantity-per-assembly data.
  • MTL_SYSTEM_ITEMS_B — the base item master behind COMPONENT_ITEM_ID and PRIMARY_COMPONENT_ID.

These joins are the standard pattern for reconstructing a full planning requirement view from the MSC staging layer.