Results for “source_sr_category_set_id”
26 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
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.
-
The staging table used by the collection program to validate and process data for table MSC_CATEGORY_SETS.
-
The staging table used by the collection program to validate and process data for table MSC_ITEM_CATEGORIES.
-
Table: MSC_LOCAL_ID_MISC 12.1.1
MSC_LOCAL_ID_MISC table stores the mappings between the Local IDs and the User Defined Keys for Sourcing Rules, Projects, Tasks and Categories. The table is striped by ENTITY_NAME.
-
The staging table used by the collection program to validate and process data for table MSC_CATEGORY_SETS.
-
The staging table used by the collection program to validate and process data for table MSC_ITEM_CATEGORIES.
-
Table: MSC_LOCAL_ID_MISC 12.2.2
MSC_LOCAL_ID_MISC table stores the mappings between the Local IDs and the User Defined Keys for Sourcing Rules, Projects, Tasks and Categories. The table is striped by ENTITY_NAME.
-
VIEW: MSC.MSC_LOCAL_ID_MISC# 12.2.2
-
VIEW: MSC.MSC_LOCAL_ID_MISC# 12.2.2
-
TABLE: MSC.MSC_LOCAL_ID_MISC 12.1.1
-
TABLE: MSC.MSC_LOCAL_ID_MISC 12.2.2
-
eTRM - MSC Tables and Views 12.1.1
This table contains the mapping between user-defined zone and included regions
-
eTRM - MSC Tables and Views 12.2.2
This table contains the mapping between user-defined zone and included regions