Results for “source_routing_sequence_id”

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

Overview

MSC_ST_ROUTINGS is a staging table in the MSC schema owned by Oracle Advanced Supply Chain Planning (ASCP). Its documented purpose is to serve as the collection program's temporary landing area where incoming routing data is validated and processed before being written to the permanent planning table, MSC_ROUTINGS. This staging pattern is a familiar characteristic of ASCP collections: source applications such as Oracle Manufacturing, Oracle Bills of Material, and external ERP feeds populate a flat, denormalized staging structure that the collection program then validates, resolves, and promotes to the destination table consumed by the planning engine.

As documented, the table carries 64 columns and holds a foreign key from COMPANY_ID to PN_COMPANIES_ALL, reflecting the multi-company (multi-organization) nature of supply chain planning data. The heuristic Data Vault classification mined from the FK structure is standalone, meaning the object does not resolve cleanly into a hub, link, or satellite pattern on the basis of its foreign key graph; in a Data Vault modeling exercise it would most naturally be treated as a raw staging or landing entity rather than a conformed hub or link.

Key Information Stored

The table retains both the identifier from the source system and the surrogate key that will ultimately be used in planning. The principal columns include:

• ROUTING_SEQUENCE_ID — the surrogate identifier for the routing sequence, the closest documented equivalent to a primary key for the promoted record.
• ASSEMBLY_ITEM_ID and ORGANIZATION_ID — the business keys that tie the routing to a specific item and inventory organization.
• ROUTING_NAME, ROUTING_TYPE, and ALTERNATE_ROUTING_DESIGNATOR — human-readable identification of the routing definition.
• BOM_NAME, BILL_SEQUENCE_ID, and ALTERNATE_BOM_DESIGNATOR — linkage back to the bill of materials associated with the routing.
• CFM_ROUTING_FLAG, CTP_FLAG, and MIXED_MODEL_MAP_FLAG — configuration flags governing capability and mixed-model behavior.
• PROJECT_ID and TASK_ID — project-manufacturing context where the routing belongs to a project or task.
• SR_INSTANCE_ID, SOURCE_ORGANIZATION_ID, and other SOURCE_* columns — the source-system lineage keys used by the collection program to reconcile staging rows with their origin records.
• PROCESS_FLAG, ERROR_TEXT, and MESSAGE_ID — validation status and diagnostic information produced during collection processing.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and BATCH_ID — the concurrent request and batch context in which the row was staged.

Common Use Cases and Queries

The most frequent use of MSC_ST_ROUTINGS is diagnostic. When a routing fails to appear in ASCP, analysts inspect the staging table for unresolved PROCESS_FLAG values or populated ERROR_TEXT. A typical query identifies failed rows for a collection run:

SELECT routing_name, assembly_name, organization_code, process_flag, error_text
FROM   msc_st_routings
WHERE  request_id = :request_id
AND    process_flag IN ('E','2');

A second pattern joins the staging table to its destination to confirm promotion:

SELECT s.routing_name, r.routing_sequence_id
FROM   msc_st_routings s, msc_routings r
WHERE  s.routing_sequence_id = r.routing_sequence_id(+)
AND    r.routing_sequence_id IS NULL;

Reporting use cases include validating multi-organization routing coverage by ORGANIZATION_ID, auditing project-manufacturing routings by PROJECT_ID and TASK_ID, and comparing SOURCE_* keys against the source instance to detect collection gaps.

Related Objects

• MSC_ROUTINGS — the destination planning table populated from this staging object.
• MSC_ST_BILL_OF_MATERIALS — the parallel staging table for BOM data; joins are made through BILL_SEQUENCE_ID.
• MSC_ST_OPERATIONS — staging of routing operations, joined via routing sequence.
• PN_COMPANIES_ALL — referenced by the documented foreign key MSC_ST_ROUTINGS.COMPANY_ID → PN_COMPANIES_ALL.
• MSC_SYSTEM_ITEMS and MSC_ORGANIZATIONS — resolve ASSEMBLY_ITEM_ID and ORGANIZATION_ID.
• FND_CONCURRENT_REQUESTS — resolves REQUEST_ID for collection run auditing.
• MSC_ROUTING_SEQUENCES — the operational routing structure referenced by ROUTING_SEQUENCE_ID.