Search Results msc_item_id_lid




Overview
Explore in AI Chat

Overview

MSC_ITEM_ID_LID is a table in the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) module in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to record the correspondence between source-instance item identifiers and the internal planning identifiers used by ASCP. Because planning data is collected from one or more source EBS instances into the MSC schema, the collection program must translate each operational item identifier into a planning-side identifier that is unambiguous across instances. MSC_ITEM_ID_LID is the lookup table that preserves that translation.

The table is documented as VALID with a three-column physical schema (SR_INVENTORY_ITEM_ID, SR_INSTANCE_ID, INVENTORY_ITEM_ID) and a composite primary key constraint, MSC_ITEM_ID_LID_PK, defined on (SR_INVENTORY_ITEM_ID, SR_INSTANCE_ID). Under the heuristic Data Vault classification mined from the foreign-key structure, the object is identified as a standalone table — it is not modelled as a hub, link, or satellite. In practice this means it behaves as a flat mapping or cross-reference table rather than a component of an integrated Data Vault model, and any Data Vault implementation around ASCP data should treat it as a utility lookup rather than a core entity.

Key Information Stored

Only three columns are documented for this object, and all three are relevant to its function:

  • SR_INVENTORY_ITEM_ID — the item identifier as it exists in the source (operational) instance. This is the item key from the source system's inventory item master.
  • SR_INSTANCE_ID — the identifier of the source instance from which the item was collected. Combined with SR_INVENTORY_ITEM_ID, it guarantees uniqueness, since the same numbered item can exist independently in multiple source instances.
  • INVENTORY_ITEM_ID — the planning-side identifier assigned to the item within MSC. This is the surrogate key consumed by planning engines, plan tables, and supply/demand records.

The surrogate primary key as documented is the composite MSC_ITEM_ID_LID_PK over (SR_INVENTORY_ITEM_ID, SR_INSTANCE_ID). There is no separately documented single-column surrogate; the composite pair is both the primary key and the natural business key identifying a source item. INVENTORY_ITEM_ID is not part of the primary key but carries the mapping target, functioning effectively as the value the table resolves to during collection and planning processing.

Common Use Cases and Queries

The principal use case is resolving a source item to its planning identifier during collection and plan execution, and performing the reverse translation when reporting planning results back in operational terms. A typical lookup resolves a known source item to its planning key:

  • SELECT inventory_item_id FROM msc.msc_item_id_lid WHERE sr_instance_id = :p_instance AND sr_inventory_item_id = :p_item;

A reverse resolution — given a planning identifier and instance, find the source item — supports reconciliation reports and data-quality checks:

  • SELECT sr_inventory_item_id FROM msc.msc_item_id_lid WHERE sr_instance_id = :p_instance AND inventory_item_id = :p_plan_item;

Joining this table to planning item tables allows reports to display source item numbers alongside planning data, and joining to source-instance definitions provides the instance name for multi-instance environments. A common diagnostic query identifies items whose mapping is missing or duplicated, which typically indicates incomplete collection or an instance-collection failure:

  • SELECT sr_instance_id, COUNT(*) FROM msc.msc_item_id_lid GROUP BY sr_instance_id HAVING COUNT(*) = 0;

Because the table is small and highly selective on its composite key, lookups are inexpensive and it is frequently used inside collection programs, plan refresh routines, and custom reports that reconcile MSC planning output with source EBS item masters.

Related Objects

MSC_ITEM_ID_LID sits between the source instance's item master and the MSC planning item structures. The most significant related objects, joined on the documented columns, include:

  • MSC_SYSTEM_ITEMS (or the equivalent MSC planning item table) — joined on INVENTORY_ITEM_ID to obtain planning item attributes.
  • MSC_SR_INSTANCES / MSC_SR_INSTANCE — joined on SR_INSTANCE_ID to resolve the source instance name and collection status.
  • The source instance's inventory item table (for example, MTL_SYSTEM_ITEMS_B in the operational instance) — joined on SR_INVENTORY_ITEM_ID to retrieve source item descriptions.
  • MSC_ITEM_CATEGORIES and related MSC item attribute tables — joined on INVENTORY_ITEM_ID for planning category data.
  • MSC_SUPPLIES and MSC_DEMANDS — joined on INVENTORY_ITEM_ID to interpret plan records in source item terms.

These relationships are logical join paths rather than enforced foreign keys; the documented FK structure classifies the object as standalone. The table therefore serves as the authoritative mapping reference whenever MSC planning data must be translated to or from source EBS item identifiers.