Results for “source_sr_category_id”
20 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MSC_ST_ITEM_CATEGORIES is an Oracle Advanced Supply Chain Planning (MSC) staging table that serves as the temporary landing and validation surface for data destined for the production table MSC_ITEM_CATEGORIES. During a data collection run, the MSC collection program writes extracted source records into this staging table, validates each row against planning business rules and referential constraints, sets processing flags, and then transfers successfully validated records into the permanent planning table. This two-phase design isolates the collection and validation workload from the live planning structures and allows failed rows to remain in staging with diagnostics rather than corrupting the target table.
From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone, meaning the foreign-key analysis did not expose it as a classical hub, link, or satellite. The single documented foreign key, COMPANY_ID referencing PN_COMPANIES_ALL in the same MSC schema, suggests the table behaves more like a staging satellite keyed on the item/category/instance combination rather than a hub entity in its own right. This classification should be treated as a modeling suggestion only, since staging tables intentionally denormalize attributes that would otherwise be split across hubs, links, and satellites in a canonical warehouse design.
Key Information Stored
The table holds 39 documented columns in the 12.2.2 schema under owner MSC. The most operationally significant columns are:
- INVENTORY_ITEM_ID and ORGANIZATION_ID — the item and organization context for the category assignment being staged.
- SR_CATEGORY_SET_ID and SR_CATEGORY_ID — the source category set and category identifiers being collected.
- CATEGORY_SET_NAME, CATEGORY_NAME, and DESCRIPTION — denormalized descriptive attributes carried through from the source.
- ENABLED_FLAG, DISABLE_DATE, START_DATE_ACTIVE, END_DATE_ACTIVE, and SUMMARY_FLAG — effective-dating and status attributes governing the category assignment.
- SR_INSTANCE_ID and SR_INSTANCE_CODE — the source instance from which the record was extracted, essential for multi-instance collections.
- REFRESH_ID — the collection refresh cycle that produced the row.
- PROCESS_FLAG, ERROR_TEXT, and MESSAGE_ID — validation outcome fields that determine whether the row is promoted or retained for diagnosis.
- COMPANY_ID and COMPANY_NAME — the sole documented foreign key path (COMPANY_ID → PN_COMPANIES_ALL), tying the row to a planning company.
- REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and PROGRAM_UPDATE_DATE — concurrent program audit lineage.
The table is documented without a declared surrogate primary key; identity is effectively the combination of source instance, item, organization, and category, with the SOURCE_* columns (SOURCE_ORG_ID, SOURCE_INVENTORY_ITEM_ID, SOURCE_SR_CATEGORY_SET_ID, SOURCE_SR_CATEGORY_ID) preserving the original source keys for reconciliation back to the upstream system.
Common Use Cases and Queries
The primary use case is monitoring collection health. DBAs and planning administrators query staging rows filtered by PROCESS_FLAG to identify failed records and inspect ERROR_TEXT. A typical pattern is:
- SELECT REQUEST_ID, PROCESS_FLAG, ERROR_TEXT, COUNT(*) FROM MSC.MSC_ST_ITEM_CATEGORIES WHERE REFRESH_ID = :refresh GROUP BY REQUEST_ID, PROCESS_FLAG, ERROR_TEXT;
- SELECT * FROM MSC.MSC_ST_ITEM_CATEGORIES WHERE PROCESS_FLAG = 'E' OR ERROR_TEXT IS NOT NULL;
- Comparing staged rows to the promoted rows in MSC_ITEM_CATEGORIES to detect records that were silently dropped during validation.
- Auditing multi-instance collections by grouping on SR_INSTANCE_CODE and SR_INSTANCE_ID.
- Reconciling SOURCE_* columns against the originating EBS item/category tables to confirm source-to-planning mapping.
Because this is a staging object, rows are typically purged or overwritten each refresh cycle; reporting queries should therefore be scoped by REFRESH_ID or REQUEST_ID.
Related Objects
- MSC_ITEM_CATEGORIES — the production target table that receives validated rows from this staging table.
- PN_COMPANIES_ALL — referenced via COMPANY_ID, providing the planning company master.
- MSC_ST_ITEM_CATEGORIES ITSELF participates in the broader MSC_ST_* staging family (e.g., MSC_ST_ITEMS, MSC_ST_CATEGORIES, MSC_ST_ORG_CATEGORIES) which are collected in the same refresh cycle.
- EGP_SYSTEM_ITEMS_B / MTL_SYSTEM_ITEMS_B — the source item masters whose INVENTORY_ITEM_ID values are staged here.
- MTL_CATEGORIES_B and MTL_CATEGORY_SETS_B — source category and category set definitions.
- MSC_SYSTEM_ITEMS and MSC_CATEGORIES — fully planned counterparts depending on validated category assignments.
- FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to trace the collection concurrent program that populated the staging rows.
-
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_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