Search Results ic_item_whs




Overview
Explore in AI Chat

Overview

The IC_ITEM_WHS table is a core data object within the Process Manufacturing Inventory (GMI) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It serves as a junction table that establishes and manages the relationship between items and warehouses, defining which items are stocked in which storage locations. Its primary role is to maintain the valid combinations of items and warehouses, which is a fundamental prerequisite for inventory transactions, such as receipts, issues, and transfers, within the Process Manufacturing environment. The table's structure enforces referential integrity through its primary and foreign keys, ensuring data consistency across the inventory schema.

Key Information Stored

The table's structure is defined by its primary key, which consists of two columns: ITEM_ID and WHSE_CODE. The ITEM_ID column is a foreign key that links to the item master tables (IC_ITEM_MST_B and IC_ITEM_MST), uniquely identifying a manufactured or purchased item. The WHSE_CODE column is a foreign key that links to the warehouse master table (IC_WHSE_MST), uniquely identifying a storage location or warehouse. While the provided ETRM metadata indicates the description field is marked "*NOT USED*", the table's essential purpose is to store the existence of this relationship. Additional descriptive or control columns may exist in a full implementation but are not detailed in the provided excerpt.

Common Use Cases and Queries

This table is central to validation and reporting on item availability by location. A common use case is verifying if an item is valid for a specific warehouse before executing a transaction. For reporting, it is frequently joined with master tables to generate lists of items per warehouse. Sample SQL patterns include validating an item-warehouse combination and generating an item warehouse listing.

  • Validation Query: SELECT COUNT(*) FROM GMI.IC_ITEM_WHS WHERE ITEM_ID = :p_item_id AND WHSE_CODE = :p_whse_code;
  • Reporting Query: SELECT i.item_no, w.whse_code, w.description FROM GMI.IC_ITEM_WHS iw JOIN GMI.IC_ITEM_MST_B i ON iw.item_id = i.item_id JOIN GMI.IC_WHSE_MST w ON iw.whse_code = w.whse_code ORDER BY w.whse_code, i.item_no;

Related Objects

IC_ITEM_WHS has defined relationships with several key master tables in the GMI schema, as indicated by its foreign key constraints. Its primary dependencies are:

  • IC_ITEM_MST_B / IC_ITEM_MST: These item master tables provide the definitive item definition. The ITEM_ID in IC_ITEM_WHS must exist in these parent tables.
  • IC_WHSE_MST: The warehouse master table. The WHSE_CODE in IC_ITEM_WHS must be a valid warehouse defined in this table.

As a foundational junction table, it is also likely referenced by various transaction and status tables within the Process Manufacturing Inventory module, such as lot or on-hand quantity tables, to enforce location-specific rules.