Results for “msc_items”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC.MSC_ITEMS is a collection-staging table within the Advanced Supply Chain Planning (MSC) product of Oracle E-Business Suite, documented as VALID in release 12.2.2 (and applicable to 12.1.1). As stated in the ETRM metadata, the table "is used by the collection program to keep track of all inventory item identifiers generated at planning server." In practical terms, it is the planning-server registry of items that the collections engine has identified and staged for use within the MSC schema. Rather than holding transactional supply and demand, it holds the item identity mapping that links back to the source inventory definition maintained in Oracle Inventory.

From a heuristic Data Vault perspective, the FK/PK relationship data classifies this object as standalone, which suggests a modeling treatment as a hub for the inventory item business key. The table carries no documented foreign-key dependencies to other MSC objects, reinforcing its role as a foundational reference entity rather than a link or satellite.

Key Information Stored

The documented physical schema defines 28 columns. The most significant are:

• INVENTORY_ITEM_ID — the primary key component (constraint MSC_ITEMS_PK) and the surrogate identifier passed through from the source system. It is also a unique-index candidate via MSC_ITEMS_U1.
• ITEM_NAME — the user-facing item identifier; a second business-key candidate, enforced by unique index MSC_ITEMS_U2.
• DESCRIPTION — the item description as captured during collection.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard "Who" audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the "When" concurrent-program context, identifying the collection run that generated or refreshed the row.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield (DFF) columns available for client-specific extensibility.

The distinction between the surrogate primary key (INVENTORY_ITEM_ID) and the alternate business key (ITEM_NAME) is material for deduplication and reconciliation logic.

Common Use Cases and Queries

Typical uses include validating that a specific item was collected into the planning schema before diagnosing plan failures, driving item-level reporting joins against MSC demand and supply tables, and auditing which collection program populated a given item.

• Existence check for a planning item: SELECT inventory_item_id, item_name FROM msc.msc_items WHERE item_name = :item_name;
• Trace the collection run: select REQUEST_ID, PROGRAM_ID, CREATION_DATE and join to the concurrent request tables to find the originating program.
• Reconciliation to Inventory: join MSC_ITEMS.INVENTORY_ITEM_ID to MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID to compare descriptions or flags.
• Freshness reporting: filter on LAST_UPDATE_DATE to identify items not refreshed by the latest collection cycle.

Related Objects

The documented relationship data classifies MSC_ITEMS as standalone with no FK dependencies, so the following associations are driven by shared keys rather than enforced constraints:

• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID; the master item definition that MSC_ITEMS mirrors.
• MTL_SYSTEM_ITEMS_TL — joined on INVENTORY_ITEM_ID for translated names and descriptions.
• MSC_SYSTEM_ITEMS — the MSC-side planning item attribute table keyed by the same identifier.
• FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to identify the collection run.
• FND_APPLICATION / FND_PROGRAM — joined on PROGRAM_APPLICATION_ID and PROGRAM_ID to resolve the collecting program.
• FND_USER — joined on CREATED_BY and LAST_UPDATED_BY for audit attribution.