Search Results msc_st_category_sets




Overview
Explore in AI Chat

Overview

MSC_ST_CATEGORY_SETS is a staging table in the MSC schema owned by Oracle Advanced Supply Chain Planning (ASCP). Its documented purpose is to hold inbound category set data collected from a source instance so that the collection program can validate and process it before merging the records into the MSC_CATEGORY_SETS base table. The table is populated during data collection from a source ERP instance (identified by SR_INSTANCE_ID) and acts as a transient landing area: rows are validated, flagged, and then consumed by the collection process that loads or refreshes the corresponding planning records.

The provided heuristic Data Vault classification for MSC_ST_CATEGORY_SETS is standalone, with only one documented foreign key (COMPANY_ID referencing PN_COMPANIES_ALL). Read as a modeling suggestion, this indicates the table does not sit in a multi-parent dependency web; it can be treated as a single-source satellite of the category set entity, keyed by its staging transaction identity and source instance rather than fanning out through hub-and-link joins. Practically, this means joins to dimension tables should be driven by the business keys (CATEGORY_SET_NAME, SR_CATEGORY_SET_ID) and the source instance, not by complex relational chains.

Key Information Stored

The documented physical schema contains 28 columns. The most significant are:

  • CATEGORY_SET_ID — the planning-side identifier for the category set, the primary surrogate key for the destination MSC_CATEGORY_SETS row.
  • SR_CATEGORY_SET_ID and SOURCE_SR_CATEGORY_SET_ID — the source-system business keys; these are the primary candidates for identifying a category set as it exists in the originating instance and for tracing lineage during re-collection.
  • CATEGORY_SET_NAME — the business name of the category set, typically the human-readable unique identifier.
  • DESCRIPTION, CONTROL_LEVEL, DEFAULT_FLAG, DELETED_FLAG — descriptive and control attributes that determine how the category set behaves (level of control, default designation, soft-delete status).
  • SR_INSTANCE_ID, SR_INSTANCE_CODE — identify the source instance from which the record was collected; essential for multi-instance planning environments.
  • REFRESH_ID, REQUEST_ID, BATCH_ID — collection-run context, tying each row to a specific refresh cycle, concurrent request, and batch.
  • PROCESS_FLAG, ERROR_TEXT, MESSAGE_ID — processing state and diagnostics; ERROR_TEXT carries validation failures.
  • ST_TRANSACTION_ID, DATA_SOURCE_TYPE — the staging transaction identity and origin type of the record.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard audit columns.

Common Use Cases and Queries

Typical uses centre on diagnosing collection failures and confirming what was loaded. To find failed rows for a run:

SELECT CATEGORY_SET_NAME, SR_CATEGORY_SET_ID, ERROR_TEXT
FROM MSC_ST_CATEGORY_SETS
WHERE PROCESS_FLAG = 'E' AND REFRESH_ID = :refresh_id;

To compare staging against the base table:

SELECT s.CATEGORY_SET_NAME, s.SR_CATEGORY_SET_ID
FROM MSC_ST_CATEGORY_SETS s
WHERE NOT EXISTS (SELECT 1 FROM MSC_CATEGORY_SETS c
  WHERE c.CATEGORY_SET_ID = s.CATEGORY_SET_ID);

Rows pending processing are equally common queries, filtered by PROCESS_FLAG and BATCH_ID, and joining COMPANY_ID to PN_COMPANIES_ALL to resolve the owning company.

Related Objects

  • MSC_CATEGORY_SETS — the base table this staging table validates and loads into.
  • PN_COMPANIES_ALL — referenced via COMPANY_ID.
  • Other MSC_ST_* staging tables populated within the same refresh/collection run.
  • MSC collection program definitions (concurrent programs referenced by PROGRAM_ID / PROGRAM_APPLICATION_ID).
  • Refresh-run metadata keyed by REFRESH_ID and REQUEST_ID.