Search Results msc_category_set_id_lid




Overview
Explore in AI Chat

Overview

MSC_CATEGORY_SET_ID_LID is a table in the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) product within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to support the collections program by maintaining the correspondence between source instance identifiers and planning identifiers for category sets. In other words, it functions as an identifier-mapping table that lets ASCP translate a category set defined in a source (transactional) application into the internal planning identifier used within the MSC planning schema.

From a Data Vault modeling perspective, this object reads most naturally as a link table. The heuristic classification supplied with the ETRM metadata labels it "standalone," meaning no foreign keys are physically enforced toward other MSC tables. Even so, its composite key of SR_CATEGORY_SET_ID and SR_INSTANCE_ID expresses a many-to-many style relationship bridged by the assigned planning CATEGORY_SET_ID, which is characteristic of a link structure rather than a hub or satellite. The table is refreshed and maintained by the collection process during data pull from source instances.

Key Information Stored

The documented physical schema contains only three columns, which is typical for an identifier-translation bridge. The most important pieces of information are:

  • SR_CATEGORY_SET_ID — the category set identifier as it exists in the source instance. This is the source-side half of the mapping.
  • SR_INSTANCE_ID — identifies the source instance (that is, the specific transactional system or organization source) from which the category set was collected. This disambiguates the same numeric category set ID appearing in more than one source.
  • CATEGORY_SET_ID — the planning-side identifier assigned within the MSC schema, used internally by ASCP and related planning engine logic.

The primary key is MSC_CATEGORY_SET_ID_LID_PK, defined over the composite of SR_CATEGORY_SET_ID and SR_INSTANCE_ID. A separate unique index, MSC_CATEGORY_SET_ID_LID_U1, is defined over the same two columns in the order (SR_INSTANCE_ID, SR_CATEGORY_SET_ID). This means the surrogate/business identifier is effectively the combination of the source category set and its originating instance, not a single generated number. CATEGORY_SET_ID is the mapped value rather than part of the key. Because no foreign keys are documented, referential integrity is managed by the collection program rather than by database constraints.

Common Use Cases and Queries

The principal use case is joining planning-level category set data back to its source context. A typical query resolves the planning ID into its originating source and source ID:

  • Given a planning CATEGORY_SET_ID, look up SR_INSTANCE_ID and SR_CATEGORY_SET_ID to trace the record to its source.
  • Given a source category set and instance, retrieve the planning CATEGORY_SET_ID to join into planning tables.
  • Reconciliation reporting that verifies every collected source category set has been mapped, or that detects duplicates across instances.
  • Troubleshooting collection errors where a category set appears missing or mismatched in ASCP output.

A representative pattern joins on the composite key columns, for example selecting CATEGORY_SET_ID from MSC_CATEGORY_SET_ID_LID where SR_INSTANCE_ID and SR_CATEGORY_SET_ID match the source values, and aggregating counts by SR_INSTANCE_ID to confirm collection completeness.

Related Objects

Owing to its identifier-mapping role, this table is referenced indirectly by the collection program and by planning entities that carry a category set. Significant related objects include:

  • MSC category set definition tables that store the planning-side CATEGORY_SET_ID.
  • Source category set tables in the transactional application, matched via SR_CATEGORY_SET_ID and SR_INSTANCE_ID.
  • The MSC collection/interface programs that populate and refresh this mapping.
  • Source instance registry tables that define SR_INSTANCE_ID.
  • Planning-item and category-assignment tables that consume the translated CATEGORY_SET_ID.

Because the metadata documents no enforced foreign keys, joins to these objects depend on application logic rather than database constraints.