Search Results msc_st_resource_instance_reqs




Overview
Explore in AI Chat

Overview

MSC_ST_RESOURCE_INSTANCE_REQS is a staging table within the MSC schema of Oracle Advanced Supply Chain Planning (ASCP). It serves as an interface layer that holds resource instance requirement records collected from source systems before they are validated, transformed, and loaded into the planning engine's operational tables. In Oracle EBS 12.1.1 and 12.2.2, staging tables of this type form part of the Collections and data transformation pipeline, bridging transactional execution data from manufacturing and production systems into the planning data model.

The table stores resource instance-level requirements — granular records describing how individual resources (machines, equipment, labor) are consumed at specific operations over defined time windows. With 45 documented columns, the structure is denormalized by design, carrying both raw identifiers and descriptive codes to facilitate validation during the load process.

From a Data Vault modeling perspective, the heuristic classification for this object is standalone, meaning the mining process identified no strong hub, link, or satellite role based on its foreign key structure. This classification suggests the table functions as an integration or staging construct rather than a conformed dimensional entity. Modelers should treat it as an ephemeral load table rather than a persistent business entity.

Key Information Stored

The table's primary business identifiers center on the resource instance context. RESOURCE_ID and RES_INSTANCE_ID together define which resource and instance the requirement applies to, while ORGANIZATION_ID and DEPARTMENT_ID scope the record to a specific organizational unit. The foreign key to BOM_DEPARTMENTS via DEPARTMENT_ID and to PN_COMPANIES_ALL via COMPANY_ID confirms that departmental and company context is stored directly.

The metadata does not document a surrogate primary key or unique index. In practice, staging tables of this family typically rely on the combination of RESOURCE_ID, RES_INSTANCE_ID, ORGANIZATION_ID, SUPPLY_ID, OPERATION_SEQ_NUM, and RESOURCE_SEQ_NUM as the business-key candidate, with PARENT_ID and PARENT_SEQ_NUM establishing hierarchical relationships between parent and child resource instances.

Common Use Cases and Queries

Typical usage centers on planning data collection diagnostics and troubleshooting. Planners and technical analysts query this table to inspect what resource instance demand was staged, whether records processed successfully, and what errors blocked loading.

A representative pattern filters failed or pending rows:

  • SELECT RESOURCE_ID, RES_INSTANCE_ID, ORGANIZATION_ID, PROCESS_FLAG, ERROR_TEXT FROM MSC.MSC_ST_RESOURCE_INSTANCE_REQS WHERE PROCESS_FLAG IS NULL OR ERROR_TEXT IS NOT NULL;
  • Aggregating resource load by organization and window: group by ORGANIZATION_ID, RESOURCE_ID, summing RESOURCE_INSTANCE_HOURS between START_DATE and END_DATE.
  • Correlating with a concurrent request via REQUEST_ID to determine which planning run staged a given batch of records.
  • Validating departmental references against BOM_DEPARTMENTS and company references against PN_COMPANIES_ALL before load.

Reporting use cases include resource capacity analysis, source system reconciliation, and error trend monitoring across collection cycles.

Related Objects

The documented foreign key relationships connect this table to two master reference objects:

  • BOM_DEPARTMENTS — joined on MSC_ST_RESOURCE_INSTANCE_REQS.DEPARTMENT_ID = BOM_DEPARTMENTS.DEPARTMENT_ID, resolving departmental ownership of the resource instance.
  • PN_COMPANIES_ALL — joined on MSC_ST_RESOURCE_INSTANCE_REQS.COMPANY_ID = PN_COMPANIES_ALL.COMPANY_ID, resolving the company context.

Beyond the documented FKs, functionally related objects in the same staging and planning family include MSC_ST_RESOURCES, MSC_ST_RESOURCE_INSTANCES (or equivalent interface tables), MSC_ST_WIP_ENTITIES, and the operational planning tables in the MSC schema into which clean records are ultimately loaded. The parent-child columns PARENT_ID and PARENT_SEQ_NUM imply self-referencing relationships within this same table. Downstream consumers include ASCP concurrent programs that read staged rows based on REFRESH_ID and BATCH_ID and promote validated data into the planning engine, while audit columns tie back to FND_CONCURRENT_REQUESTS through REQUEST_ID.