Search Results whse_item_id

Overview

The PS_WHSE_EFF table is a core data structure within the Oracle E-Business Suite (EBS) Process Manufacturing (GMP) module, specifically for Process Planning. Its primary function is to manage and store the effectivity, or the active relationship, between manufacturing plants and warehouses. This table defines which warehouses are valid and available for use by a specific plant, establishing a critical link in the supply chain and material flow for process manufacturing operations. The presence of an effectivity ID and date-effective columns (implied by the table's purpose and name) suggests it supports managing changes to these relationships over time, allowing for historical tracking and future-dated implementations.

Key Information Stored

The table's structure centers on identifying the linked entities and the nature of their relationship. Based on the provided metadata, key columns include the primary identifier (PSWHSE_EFF_ID), the plant code (PLANT_CODE), the warehouse code (WHSE_CODE), and a warehouse item identifier (WHSE_ITEM_ID). The primary keys indicate that the unique relationship is defined by the combination of Plant, Warehouse, and a specific Item. Other significant columns, common in effectivity tables, would typically include effective and inactive dates (EFFDT, INACTIVE_DT), a text code for descriptions (TEXT_CODE), and standard who-columns for auditing (CREATION_DATE, LAST_UPDATE_DATE, etc.).

Common Use Cases and Queries

This table is essential for validating material transactions and planning activities. A common use case is determining the valid warehouses for issuing components to a production batch at a given plant. Reporting often involves listing all active plant-warehouse relationships or identifying the default warehouse for a specific item at a plant. Sample SQL patterns include fetching active relationships for a specific plant or validating a warehouse for a transaction.

• To find all active warehouses for a plant 'P001':
  SELECT whse_code FROM gmp.ps_whse_eff WHERE plant_code = 'P001' AND SYSDATE BETWEEN effdt AND NVL(inactive_dt, SYSDATE+1);
• To validate a specific plant-warehouse-item combination:
  SELECT 'VALID' FROM gmp.ps_whse_eff WHERE plant_code = :p_plant AND whse_code = :p_whse AND whse_item_id = :p_item_id AND SYSDATE BETWEEN effdt AND NVL(inactive_dt, SYSDATE+1);

Related Objects

As indicated by its foreign key constraints, PS_WHSE_EFF is integrally connected to master data tables across EBS. It references the plant master (SY_ORGN_MST and SY_ORGN_MST_B) and the warehouse master (IC_WHSE_MST). Its link to the item master (IC_ITEM_MST and IC_ITEM_MST_B) via WHSE_ITEM_ID is crucial for item-specific warehouse assignments. The foreign key to PS_TEXT_HDR allows for descriptive text associated with the relationship. This table is a central hub that enforces referential integrity between organizational structures, inventory locations, and items within the Process Manufacturing footprint.