Search Results msc_st_department_resources




Overview
Explore in AI Chat

Overview

MSC_ST_DEPARTMENT_RESOURCES is a staging table in the MSC schema (Oracle Advanced Supply Chain Planning) that serves as the temporary landing area for department resource data before it is validated and processed into the base table MSC_DEPARTMENT_RESOURCES. In Oracle EBS 12.1.1 and 12.2.2, the collection program extracts resource and department definitions from source systems, writes them into this staging table, applies validation and transformation logic, and then loads the cleansed records into the planning engine's operational tables. This staging pattern isolates unreliable inbound data from the live planning tables and allows the collection program to detect and reject invalid records before they affect planning results.

The table carries a heuristic Data Vault classification of standalone, meaning that from the mined foreign-key structure it does not participate as a hub, link, or satellite within a broader Data Vault model. It is best treated as a transient interface object rather than a subject-oriented data warehouse entity.

Key Information Stored

With 84 documented columns, the table captures resource master data, department context, capacity parameters, costing attributes, and collection-control metadata. The most significant columns include:

The table is standalone in the mined FK structure, so no unique index is documented as the surrogate primary key; PROCESS_FLAG and SR_INSTANCE_ID typically drive the load logic rather than a single surrogate key.

Common Use Cases and Queries

Typical scenarios include diagnosing collection failures, reconciling staging counts against the target table, and reporting on resources pending validation.

  • Identify records that failed validation:
    SELECT * FROM MSC_ST_DEPARTMENT_RESOURCES WHERE PROCESS_FLAG = 'E';
  • Count records by collection run:
    SELECT SR_INSTANCE_ID, REFRESH_ID, COUNT(*) FROM MSC_ST_DEPARTMENT_RESOURCES GROUP BY SR_INSTANCE_ID, REFRESH_ID;
  • Review error messages for a batch:
    SELECT RESOURCE_CODE, DEPARTMENT_CODE, ERROR_TEXT FROM MSC_ST_DEPARTMENT_RESOURCES WHERE BATCH_ID = :batch;
  • Compare staging versus loaded rows:
    SELECT s.RESOURCE_ID, d.RESOURCE_ID FROM MSC_ST_DEPARTMENT_RESOURCES s LEFT JOIN MSC_DEPARTMENT_RESOURCES d ON s.RESOURCE_ID = d.RESOURCE_ID AND s.ORGANIZATION_ID = d.ORGANIZATION_ID;

Related Objects

The most significant related objects are the target table and the two referenced master tables:

  • MSC_DEPARTMENT_RESOURCES — the destination table the staging program loads after validation.
  • BOM_DEPARTMENTS — joined via DEPARTMENT_ID.
  • PN_COMPANIES_ALL — joined via COMPANY_ID.
  • MSC_ST_* collection staging tables — sibling staging objects processed by the same collection program.
  • Collection program APIs — the concurrent programs that read PROCESS_FLAG and ERROR_TEXT and manage the load of this table.