Search Results msc_item_categories




Overview
Explore in AI Chat

Overview

The MSC_ITEM_CATEGORIES table is a core data repository within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) module. It serves as the central planning engine's counterpart to the inventory-centric MTL_ITEM_CATEGORIES table. Its primary role is to store and manage the assignment of items to categories within specific category sets, as defined and used by the planning processes. This categorization is fundamental for grouping items with similar planning attributes, enabling aggregate reporting, demand forecasting, and the execution of planning rules based on logical item groupings. The table's structure supports a multi-organization and multi-instance architecture, which is critical for deployments utilizing Oracle's Shared Service Planning model.

Key Information Stored

The table's composite primary key defines the unique granularity of each record, consisting of ORGANIZATION_ID, SR_INSTANCE_ID, INVENTORY_ITEM_ID, CATEGORY_SET_ID, and SR_CATEGORY_ID. This structure ensures a single item can be assigned to only one category within a given category set for a specific organization and source instance. Key columns include SR_INSTANCE_ID, which identifies the source system instance from which the data originated (e.g., a specific manufacturing or inventory instance). INVENTORY_ITEM_ID and ORGANIZATION_ID together identify the specific item. CATEGORY_SET_ID references the defined grouping structure (e.g., "Planning" or "Purchasing" category set), and SR_CATEGORY_ID holds the identifier for the actual category within that set to which the item belongs. The table acts as a junction between planning items and their categorical classifications.

Common Use Cases and Queries

A primary use case is generating reports on planned items grouped by category for analysis, such as summarizing total planned supply or demand by product family. Data in this table is essential for planners running scenarios where planning parameters or strategies are applied at the category level. Common queries involve joining to item and category master tables. For example, to list all planned items with their category assignments for a specific category set, a typical SQL pattern would be:

  • SELECT mic.organization_id, mic.inventory_item_id, msi.concatenated_segments, mc.category_name
  • FROM msc_item_categories mic,
  • msc_system_items msi,
  • msc_categories mc
  • WHERE mic.inventory_item_id = msi.inventory_item_id
  • AND mic.organization_id = msi.organization_id
  • AND mic.sr_instance_id = msi.sr_instance_id
  • AND mic.sr_category_id = mc.category_id
  • AND mic.category_set_id = <desired_category_set_id>;

This table is also critical for troubleshooting data synchronization issues between inventory master data (MTL_ITEM_CATEGORIES) and the planning repository.

Related Objects

The most direct relationship, as documented in the foreign key, is with MSC_CATEGORY_SETS (via CATEGORY_SET_ID), which defines the valid category sets for planning. The table has intrinsic relationships with MSC_SYSTEM_ITEMS (via INVENTORY_ITEM_ID, ORGANIZATION_ID, and SR_INSTANCE_ID) and MSC_CATEGORIES (via SR_CATEGORY_ID). For data population and synchronization, it is closely tied to the MSC_ITEM_CATEGORIES_PKG package and the underlying collection processes that pull data from the source instances. From a reporting perspective, planners often access this data indirectly through planning-specific views or analytical dashboards built upon this foundational table.