Search Results ic_lots_mst

Overview

The IC_LOTS_MST table is a core master data table within the Process Manufacturing Inventory (GMI) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It serves as the central repository for storing detailed information about individual manufacturing or production lots for an item. In process manufacturing, where products are often produced in batches with specific characteristics, this table is critical for tracking lot-specific attributes, quality status, and lifecycle information. Its role is to provide a single source of truth for lot definitions, enabling inventory transactions, quality management, and compliance tracking across the manufacturing and supply chain operations.

Key Information Stored

The table's structure is designed to uniquely identify a lot and store its defining properties. As indicated by its primary keys, a lot is uniquely identified by the combination of `LOT_NO` and `SUBLOT_NO` for a given `ITEM_ID`. Alternatively, the surrogate key `LOT_ID` paired with `ITEM_ID` also serves as a primary key. Key columns and their purposes, inferred from foreign key relationships, include `ITEM_ID` (link to item master), `QC_GRADE` (link to quality grades), `TEXT_CODE` (link to descriptive text), `SHIPVEND_ID` (link to the vendor), and `EXPACTION_CODE` (link to quality expiration actions). The table fundamentally stores data that distinguishes one batch or lot of an item from another, such as creation dates, statuses, and quality-related flags.

Common Use Cases and Queries

This table is essential for lot-based inventory inquiries, quality control, and traceability reports. Common operational and reporting scenarios include querying lot details for a specific item, identifying lots by quality grade, or finding lots approaching expiration. A typical SQL pattern involves joining IC_LOTS_MST with the item master (IC_ITEM_MST) and inventory balance tables (e.g., IC_LOCT_INV).

• Sample Query for Lot Details: SELECT il.lot_no, il.sublot_no, ii.item_no, il.qc_grade FROM gmi.ic_lots_mst il, gmi.ic_item_mst ii WHERE il.item_id = ii.item_id AND ii.item_no = '<ITEM>';
• Use Case - Lot Traceability: Tracing the consumption or production of a specific lot number across transactions by joining to IC_TRAN_PND, IC_TRAN_CMP, or IC_TRAN_ARC.
• Use Case - Quality Hold Management: Identifying all lots with a specific quality grade or expiration action code to manage holds and releases.

Related Objects

IC_LOTS_MST has extensive relationships with other GMI tables, underscoring its central role. It is referenced by numerous transactional and balance tables, forming the backbone of lot-level inventory tracking.

• Primary Dependencies (Foreign Keys To): IC_ITEM_MST_B, IC_ITEM_MST, GMD_GRADES_B, QC_GRAD_MST, QC_ACTN_MST, PO_VEND_MST, IC_TEXT_HDR.
• Referenced By (Foreign Keys From): Key transactional tables including IC_LOCT_INV (on-hand quantities), IC_TRAN_PND/CMP/ARC (transactions), IC_PERD_BAL (period balances), IC_ADJS_JNL (adjustments), and IC_XFER_MST (transfers). It is also linked to quality management tables like QC_SMPL_MST, QC_RSLT_MST, and QC_SPEC_MST.