Search Results msc_st_operation_resources

Overview

MSC_ST_OPERATION_RESOURCES is a staging table in the MSC schema owned by Oracle Advanced Supply Chain Planning (ASCP). Its documented purpose is to serve as the interface through which the collection program validates and processes inbound data before it is promoted into the base planning table MSC_OPERATION_RESOURCES. In the Oracle EBS 12.1.1 and 12.2.2 architectures, this staging mechanism isolates transactional source feeds — originating from Bills of Material, routings, and departmental resource definitions — from the planning engine's production tables, allowing the planning data collection (PDC) process to perform error checking, transformation, and cleansing before committing records.

The table contains 60 documented columns and carries no foreign keys imposed on other tables; the metadata records inbound FK-style references only, linking COMPANY_ID to PN_COMPANIES_ALL, DEPARTMENT_ID to BOM_DEPARTMENTS, and SETUP_ID to BOM_SETUP_TYPES. The heuristic Data Vault classification for this object is standalone, meaning it does not naturally resolve into a classic hub, link, or satellite pattern. As a modeling suggestion, it is best treated as a transient staging artifact rather than a persistent dimensional or integration structure.

Key Information Stored

The staging table carries a composite identifier built from the routing, operation, and resource hierarchy. The most significant columns include:

ROUTING_SEQUENCE_ID, OPERATION_SEQUENCE_ID, RESOURCE_SEQ_NUM — the primary business-key tuple identifying the routing, operation, and resource line.
RESOURCE_ID, RESOURCE_CODE, RESOURCE_TYPE, BASIS_TYPE — the resource being planned and its costing/usage basis.
RESOURCE_USAGE, RESOURCE_UNITS, MAX_RESOURCE_UNITS, UOM_CODE — quantities, capacity, and unit of measure for the resource.
DEPARTMENT_ID, DEPARTMENT_CODE, SETUP_ID, SETUP_CODE — departmental ownership and setup classification.
ORGANIZATION_CODE, ORGANIZATION_ID, COMPANY_ID, COMPANY_NAME — the inventory organization and company context for the feed.
ASSEMBLY_NAME, ROUTING_NAME, ALTERNATE_ROUTING_DESIGNATOR — item and routing identity.
SR_INSTANCE_ID, SR_INSTANCE_CODE, REFRESH_ID — the source system instance and collection refresh cycle.
PROCESS_FLAG, ERROR_TEXT, MESSAGE_ID, ST_TRANSACTION_ID, BATCH_ID — staging control columns for validation outcome and transaction tracking.
SOURCE_ROUTING_SEQUENCE_ID, SOURCE_OPERATION_SEQUENCE_ID, SOURCE_RESOURCE_SEQ_NUM, SOURCE_RESOURCE_ID, SOURCE_DEPARTMENT_ID, SOURCE_ORGANIZATION_ID — the originating key values prior to transformation.
DELETED_FLAG, LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY — standard audit and soft-delete columns.

No surrogate single-column primary key is documented; identification is driven by the routing/operation/resource sequence triplet.

Common Use Cases and Queries

Typical operational and diagnostic scenarios include monitoring collection errors, reconciling source versus staged resource data, and auditing resource capacity feeds. A representative query to identify failed rows is:

SELECT ROUTING_SEQUENCE_ID, OPERATION_SEQUENCE_ID, RESOURCE_CODE, ERROR_TEXT FROM MSC.MSC_ST_OPERATION_RESOURCES WHERE PROCESS_FLAG = 'E' OR ERROR_TEXT IS NOT NULL;
SELECT ORGANIZATION_CODE, RESOURCE_CODE, RESOURCE_USAGE, UOM_CODE FROM MSC.MSC_ST_OPERATION_RESOURCES WHERE BATCH_ID = :batch AND DELETED_FLAG = 'N';
SELECT SR_INSTANCE_CODE, COUNT(*) FROM MSC.MSC_ST_OPERATION_RESOURCES GROUP BY SR_INSTANCE_CODE;

These patterns support PDC troubleshooting, data-quality reporting, and pre-promotion audits before the records are consumed by the planning engine.

Related Objects

The staging table feeds and references several core objects:

MSC_OPERATION_RESOURCES — the target planning table populated after successful validation.
PN_COMPANIES_ALL — joined on COMPANY_ID for legal entity context.
BOM_DEPARTMENTS — joined on DEPARTMENT_ID for department attributes.
BOM_SETUP_TYPES — joined on SETUP_ID for setup classification.
MSC_ST_OPERATION_HEADERS / MSC_ST_ROUTING_SEQUENCES — parent staging feeds sharing the routing/operation key.
MSC_ST_RESOURCES — resource master staging counterpart.
FND_PROGRAM / FND_REQUESTS — linked via PROGRAM_ID, PROGRAM_APPLICATION_ID, and REQUEST_ID for the collection concurrent program.

Table: MSC_ST_OPERATION_RESOURCES 12.1.1
owner:MSC, object_type:TABLE, fnd_design_data:MSC.MSC_ST_OPERATION_RESOURCES, object_name:MSC_ST_OPERATION_RESOURCES, status:VALID, product: MSC - Advanced Supply Chain Planning , description: The staging table used by the collection program to validate and process data for table MSC_OPERATION_RESOURCES. , implementation_dba_data: MSC.MSC_ST_OPERATION_RESOURCES ,
Table: MSC_ST_OPERATION_RESOURCES 12.2.2
owner:MSC, object_type:TABLE, fnd_design_data:MSC.MSC_ST_OPERATION_RESOURCES, object_name:MSC_ST_OPERATION_RESOURCES, status:VALID, product: MSC - Advanced Supply Chain Planning , description: The staging table used by the collection program to validate and process data for table MSC_OPERATION_RESOURCES. , implementation_dba_data: MSC.MSC_ST_OPERATION_RESOURCES ,
SYNONYM: APPS.MSC_ST_OPERATION_RESOURCES 12.1.1
owner:APPS, object_type:SYNONYM, object_name:MSC_ST_OPERATION_RESOURCES, status:VALID,
SYNONYM: APPS.MSC_ST_OPERATION_RESOURCES 12.2.2
owner:APPS, object_type:SYNONYM, object_name:MSC_ST_OPERATION_RESOURCES, status:VALID,
TABLE PARTITION: MSC.MSC_ST_OPERATION_RESOURCES 12.2.2
owner:MSC, object_type:TABLE PARTITION, object_name:MSC_ST_OPERATION_RESOURCES, subobject_name:ST_OPERATION_RESOURCES_LEG, status:VALID,
TABLE PARTITION: MSC.MSC_ST_OPERATION_RESOURCES 12.1.1
owner:MSC, object_type:TABLE PARTITION, object_name:MSC_ST_OPERATION_RESOURCES, subobject_name:ST_OPERATION_RESOURCES_LEG, status:VALID,
VIEW: MSC.MSC_ST_OPERATION_RESOURCES# 12.2.2
owner:MSC, object_type:VIEW, object_name:MSC_ST_OPERATION_RESOURCES#, status:VALID,
VIEW: MSC.MSC_ST_OPERATION_RESOURCES# 12.2.2
PACKAGE BODY: APPS.MSC_CL_ROUTING_ODS_LOAD 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:MSC_CL_ROUTING_ODS_LOAD, status:VALID,
PACKAGE BODY: APPS.GMP_BOM_ROUTING_PKG 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:GMP_BOM_ROUTING_PKG, status:VALID,
12.1.1 DBA Data 12.1.1
12.1.1 DBA Data 12.1.1
Lookup Type: MSC_STAGING_TABLE 12.1.1
product: MSC - Advanced Supply Chain Planning , meaning: MSC_STAGING_TABLE , description: List of staging tables used by Collections ,
PACKAGE BODY: APPS.MSC_CL_ROUTING_ODS_LOAD 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:MSC_CL_ROUTING_ODS_LOAD, status:VALID,
PACKAGE BODY: APPS.GMP_BOM_ROUTING_PKG 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:GMP_BOM_ROUTING_PKG, status:VALID,
Lookup Type: MSC_STAGING_TABLE 12.2.2
product: MSC - Advanced Supply Chain Planning , meaning: MSC_STAGING_TABLE , description: List of staging tables used by Collections ,
PACKAGE BODY: APPS.MSC_CL_GMP_UTILITY 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:MSC_CL_GMP_UTILITY, status:VALID,
PACKAGE BODY: APPS.MSC_CL_GMP_UTILITY 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:MSC_CL_GMP_UTILITY, status:VALID,
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
TABLE: MSC.MSC_ST_OPERATION_RESOURCES 12.1.1
owner:MSC, object_type:TABLE, fnd_design_data:MSC.MSC_ST_OPERATION_RESOURCES, object_name:MSC_ST_OPERATION_RESOURCES, status:VALID,
APPS.MSC_CL_ROUTING_PULL SQL Statements 12.1.1
APPS.MSC_CL_ROUTING_PULL SQL Statements 12.2.2
TABLE: MSC.MSC_ST_OPERATION_RESOURCES 12.2.2
owner:MSC, object_type:TABLE, fnd_design_data:MSC.MSC_ST_OPERATION_RESOURCES, object_name:MSC_ST_OPERATION_RESOURCES, status:VALID,
Lookup Type: MSC_X_SETUP_ENTITY_CODE 12.1.1
product: MSC - Advanced Supply Chain Planning , meaning: MSC_ST_DEMANDS ,
PACKAGE BODY: APPS.MSC_CL_PRE_PROCESS 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:MSC_CL_PRE_PROCESS, status:VALID,
PACKAGE BODY: APPS.MSC_CL_ROUTING_PULL 12.2.2
PACKAGE BODY: APPS.MSC_CL_ROUTING_PULL 12.1.1
PACKAGE BODY: APPS.MSC_CL_PRE_PROCESS 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:MSC_CL_PRE_PROCESS, status:VALID,
Lookup Type: MSC_X_SETUP_ENTITY_CODE 12.2.2
product: MSC - Advanced Supply Chain Planning , meaning: MSC_ST_DEMANDS ,
APPS.MSC_CL_ROUTING_ODS_LOAD SQL Statements 12.1.1
APPS.MSC_CL_ROUTING_ODS_LOAD SQL Statements 12.2.2
12.1.1 FND Design Data 12.1.1
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
12.1.1 DBA Data 12.1.1
12.1.1 DBA Data 12.1.1
12.2.2 FND Design Data 12.2.2
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
APPS.GMP_BOM_ROUTING_PKG dependencies on MSC_ST_OPERATION_RESOURCES 12.2.2
APPS.GMP_BOM_ROUTING_PKG SQL Statements 12.2.2
12.1.1 DBA Data 12.1.1
APPS.MSC_CL_PRE_PROCESS dependencies on MSC_ST_OPERATION_RESOURCES 12.2.2
APPS.MSC_CL_GMP_UTILITY dependencies on MSC_ST_OPERATION_RESOURCES 12.2.2
APPS.MSC_CL_PURGE_STAGING SQL Statements 12.1.1
APPS.MSC_CL_ROUTING_ODS_LOAD dependencies on MSC_ST_OPERATION_RESOURCES 12.2.2
APPS.MSC_CL_PRE_PROCESS dependencies on MSC_ST_OPERATION_RESOURCES 12.1.1
APPS.MSC_CL_GMP_UTILITY dependencies on MSC_ST_OPERATION_RESOURCES 12.1.1
APPS.GMP_BOM_ROUTING_PKG dependencies on MSC_ST_OPERATION_RESOURCES 12.1.1