Search Results ic_lots_sts




Overview
Explore in AI Chat

Overview

The IC_LOTS_STS table is a core master data table within the GMI (Process Manufacturing Inventory) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as the definitive repository for lot status codes, which are critical for controlling the lifecycle and usability of manufactured or purchased lots in a process manufacturing environment. This table provides the valid status values—such as Approved, On Hold, Rejected, or Quarantine—that are referenced by numerous transactional and inventory balance tables to enforce business rules related to material movement, quality control, and financial accounting.

Key Information Stored

Based on the provided metadata, the table's structure is centered on the LOT_STATUS column, which serves as the primary key. This column stores the short code representing the status condition. The table's relationships indicate it also stores at least two foreign key references: a TEXT_CODE linking to the IC_TEXT_HDR table for descriptive text and a QCHOLD_RES_CODE linking to the QC_HRES_MST table, which likely defines quality-related hold reasons. The primary purpose of the table is to maintain this controlled list of statuses, ensuring referential integrity and data consistency across the inventory subsystem.

Common Use Cases and Queries

This table is primarily used for validation, reporting, and setup. Common operational scenarios include validating lot status during transaction entry (e.g., preventing the shipment of a lot on hold) and generating reports that summarize inventory by status. A typical query would join IC_LOTS_STS to inventory balance or transaction tables to translate status codes into meaningful descriptions for reporting. For example, to list all lots in a specific status across locations, one might query:

  • SELECT il.lot_no, il.item_id, ils.lot_status FROM ic_loct_inv il, ic_lots_sts ils WHERE il.lot_status = ils.lot_status AND ils.lot_status = 'HOLD';

Administrative use cases involve setting up new status codes via the application's front-end or verifying referential integrity before deleting obsolete statuses.

Related Objects

The IC_LOTS_STS table is a central reference point for the Process Manufacturing inventory schema. As shown in the metadata, it is referenced by a significant number of key transactional and master tables via foreign key constraints. Major related objects include:

  • Inventory Transactions: IC_TRAN_PND, IC_TRAN_CMP, IC_TRAN_ARC, IC_ADJS_JNL, and IC_XFER_MST all reference LOT_STATUS to track the status of lots involved in movements, adjustments, and transfers.
  • Inventory Balances: IC_LOCT_INV (on-hand quantities) and IC_PERD_BAL (period balances) use LOT_STATUS to classify inventory.
  • Item Master: Both IC_ITEM_MST and IC_ITEM_MST_B reference it, potentially for defining default lot statuses at the item level.
  • Supporting Tables: IC_TEXT_HDR for descriptive text and QC_HRES_MST for quality hold reason details.

Any customization or data fix involving lot status must consider this extensive web of dependencies to maintain system integrity.