Search Results msc_st_routing_operations




Overview
Explore in AI Chat

Overview

MSC_ST_ROUTING_OPERATIONS is a staging (interface) table in the MSC schema, the schema owned by Oracle Advanced Supply Chain Planning (ASCP). Its documented purpose is to serve as the collection staging area into which source-system data is loaded, validated, and subsequently processed into the permanent planning table MSC_ROUTING_OPERATIONS. In Oracle EBS 12.1.1 and 12.2.2, this pattern is standard across ASCP: the collection program extracts manufacturing routing and operation data from source instances, writes it to MSC_ST_* tables, applies validation and error reporting, then transfers clean rows to the corresponding MSC_* planning tables.

Each row represents a single routing operation step as collected from a source application or external system, including its sequencing, department assignment, yields, durations, and effective dates. The table holds 55 documented columns and is classified heuristically as standalone in the Data Vault model — that is, it exhibits no enforced foreign-key dependency to other hub or link structures within the vault design. A standalone classification suggests treating it as an independent staging or reference entity rather than as a strict hub, link, or satellite, though the two documented foreign keys (DEPARTMENT_ID and COMPANY_ID) provide dimensional context.

Key Information Stored

The table carries both surrogate/technical keys and business-identifying columns. The principal identifying columns include:

Technical audit columns such as LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID, and BATCH_ID support traceability of the concurrent collection run.

Common Use Cases and Queries

The most frequent operational use is diagnosing collection failures. Rows with a non-null ERROR_TEXT or an unsuccessful PROCESS_FLAG indicate records that could not be promoted to the planning table, so a typical diagnostic query filters on these columns:

  • SELECT OPERATION_SEQUENCE_ID, ROUTING_NAME, OPERATION_SEQ_NUM, ERROR_TEXT FROM MSC_ST_ROUTING_OPERATIONS WHERE ERROR_TEXT IS NOT NULL;
  • Reconciling staged counts against the target: join to MSC_ROUTING_OPERATIONS on OPERATION_SEQUENCE_ID to confirm every valid row was transferred for a given BATCH_ID or REQUEST_ID.
  • Reporting on routing yield and duration by organization and department, joining DEPARTMENT_ID to BOM_DEPARTMENTS to resolve department names.
  • Auditing a specific collection run: filtering by SR_INSTANCE_ID and BATCH_ID to isolate all operations gathered from one source instance.

Related Objects

Several objects are directly implicated by the documented relationship data and the staging pattern:

  • MSC_ROUTING_OPERATIONS — the target planning table; the staging table validates data for and populates it via OPERATION_SEQUENCE_ID.
  • BOM_DEPARTMENTS — referenced by MSC_ST_ROUTING_OPERATIONS.DEPARTMENT_ID; join to resolve department names and attributes.
  • PN_COMPANIES_ALL — referenced by MSC_ST_ROUTING_OPERATIONS.COMPANY_ID; provides company context for the collected routing.
  • The parent staging tables MSC_ST_ROUTINGS / MSC_ST_ROUTING_SEQUENCES, which supply the ROUTING_SEQUENCE_ID hierarchy.
  • The ASCP collection concurrent programs and worker packages in the MSC schema that read, validate, and post these rows.

Because the table is classified standalone, integrators should treat it as a transient staging surface rather than a durable modeling entity, and query it primarily for validation, reconciliation, and collection diagnostics.