Search Results msc_category_set_id_lid_u1

Overview

MSC.MSC_CATEGORY_SET_ID_LID is a cross-reference (interface) table in the Oracle Advanced Supply Chain Planning (ASCP) schema. It is populated by the collections program and maintains the correspondence between category set identifiers defined on a source application instance and the planning-server identifiers generated for those same category sets. In Oracle EBS 12.1.1 and 12.2.2, this mapping is essential because category sets originate in source systems such as Oracle Inventory or legacy feeder systems, where they are identified by a source-specific SR_CATEGORY_SET_ID. The planning server, however, requires a stable, internally generated key (CATEGORY_SET_ID) to associate planning data — demand, supply, and item-category relationships — with the correct category set across the collected dataset.

The table is classified as standalone under Data Vault heuristics, based on its FK structure. As a modeling suggestion, this object most closely resembles a link table: it connects a source-instance/ category-set business key on one side to the planning-server surrogate on the other, carrying no descriptive attributes of its own. It is a pure relationship table rather than a hub or satellite.

Key Information Stored

The documented physical schema contains exactly three columns, all NUMBER datatypes. The primary key is MSC_CATEGORY_SET_ID_LID_PK, composed of SR_CATEGORY_SET_ID and SR_INSTANCE_ID.

• SR_CATEGORY_SET_ID — Mandatory. The category set identifier exactly as it exists in the source application instance; the source-side half of the foreign-to-local mapping.
• SR_INSTANCE_ID — Mandatory. The source application instance identifier, distinguishing which collected instance the category set originated from. Because a single planning server may collect from multiple source instances, this column is critical to disambiguate otherwise-identical source category set IDs.
• CATEGORY_SET_ID — The category set identifier generated at the planning server. This is the surrogate used internally by all downstream planning tables to reference the category set.

The unique index MSC_CATEGORY_SET_ID_LID_U1 on (SR_INSTANCE_ID, SR_CATEGORY_SET_ID) is the business-key candidate: it enforces that a given source instance cannot map the same source category set to two different planning IDs. The _LID suffix denotes a "local identifier" mapping table.

Common Use Cases and Queries

Typical usage centers on resolving identifiers during planning data validation and reporting. Analysts join this table to source-side category set definitions to translate a planning category set back to its originating instance and code.

• Resolve a planning category set to its source:

SELECT m.CATEGORY_SET_ID,
       m.SR_INSTANCE_ID,
       m.SR_CATEGORY_SET_ID
FROM   MSC.MSC_CATEGORY_SET_ID_LID m
WHERE  m.CATEGORY_SET_ID = :planning_id;

• Detect duplicate or missing mappings for a collection cycle: group by SR_INSTANCE_ID and SR_CATEGORY_SET_ID to confirm uniqueness.
• Cross-instance reconciliation: compare the count of distinct source category sets against mapped planning IDs to verify the collector completed successfully.
• Reporting joins: relate category-level planning output (e.g., item-to-category planning relationships) to human-readable source category set names by joining through SR_INSTANCE_ID and SR_CATEGORY_SET_ID.

Related Objects

No database objects are referenced (foreign keys) by MSC_CATEGORY_SET_ID_LID, consistent with its standalone/ link classification. It is referenced by the following object:

• MSC.MSC_CATEGORY_SET_ID_LID# — the shadow table accompanying the main table (denoted by the # suffix), supporting Oracle's table-shadowing/ change-tracking mechanism used by the collection and refresh process.

Logically, the CATEGORY_SET_ID column is consumed by ASCP planning tables that reference category sets, while SR_INSTANCE_ID correlates with the MSC source instance definitions used by collection. When tracing dependencies, DBAs should follow SR_INSTANCE_ID to the applicable instance registry and treat CATEGORY_SET_ID as the local planning surrogate used throughout the MSC schema.