Search Results ic_lots_cpg




Overview
Explore in AI Chat

Overview

IC_LOTS_CPG is a table within the GMI (Process Manufacturing Inventory) module of Oracle E-Business Suite, owned by the GMI schema. Its documented purpose is to store lot information specific to a Consumer Packaged Goods (CPG) installation. It exists as a supplementary lot attribute table rather than the core lot master, meaning it holds installation-specific extensions or operational flags that are only relevant when the CPG feature set is enabled. The table is referenced in both the 12.1.1 and 12.2.2 documentation sets with an identical structure, so the object has remained stable across those releases.

From a heuristic Data Vault modeling perspective, IC_LOTS_CPG is best classified as a satellite. Its two-part primary key (ITEM_ID, LOT_ID) identifies the parent business entity — the item/lot combination — rather than introducing a new business key of its own. The remaining columns are descriptive attributes tied to that key, which is the defining characteristic of a satellite rather than a hub or link. No foreign key relationships were mined beyond the composite key, so the table is treated as standalone from a referential-integrity standpoint, though logically it is dependent on the inventory item and lot entities.

Key Information Stored

The documented physical schema contains nine columns, of which the following are the most operationally significant:

  • ITEM_ID — Surrogate identifier for the inventory item; part of the composite primary key.
  • LOT_ID — Surrogate identifier for the lot; the second component of the composite primary key.
  • IC_MATR_DATE — A maturity date associated with the CPG lot, used in shelf-life and product release logic.
  • IC_HOLD_DATE — The date on which the lot was placed on hold, supporting quality and release decisions.
  • CREATED_BY, CREATION_DATE — Standard Oracle WHO columns capturing the creating user and timestamp.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit trail columns recording the most recent modification and the login session that performed it.

The surrogate primary key is enforced by the IC_LOTS_CPG_PK unique index over (ITEM_ID, LOT_ID). In Data Vault terms, this pair is the business-key candidate for the underlying lot entity; the table does not carry a separate single-column surrogate key of its own. There is no documented unique index beyond IC_LOTS_CPG_PK.

Common Use Cases and Queries

Typical scenarios involve identifying lots that are approaching maturity or currently on hold, and joining this CPG-specific data back to the core lot and item masters for reporting. A representative query follows:

  • Retrieve all held lots for a given item: SELECT ITEM_ID, LOT_ID, IC_HOLD_DATE FROM IC_LOTS_CPG WHERE ITEM_ID = :item_id AND IC_HOLD_DATE IS NOT NULL;
  • Report lots maturing within a date window: SELECT ITEM_ID, LOT_ID, IC_MATR_DATE FROM IC_LOTS_CPG WHERE IC_MATR_DATE BETWEEN :from_date AND :to_date ORDER BY IC_MATR_DATE;
  • Audit recent changes: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY to trace who modified lot attributes and when.

Reporting use cases include shelf-life dashboards, hold/release status reports, and integration extracts feeding quality or warehouse systems that require CPG-specific lot attributes.

Related Objects

Although the metadata documents no explicit foreign keys, the following objects are the most significant dependencies based on the composite key columns:

  • IC_LOTS_MST — Core lot master; join on LOT_ID and ITEM_ID to combine base lot data with CPG attributes.
  • IC_ITEM_MST — Item master; join on ITEM_ID for item descriptions and classifications.
  • IC_LOT_ATTRIBUTES — Lot attribute values; join on LOT_ID and ITEM_ID.
  • MTL_LOT_NUMBERS — Inventory lot numbers; a common cross-module join point on LOT_ID.
  • IC_LOTS_CPG_PK — The unique index enforcing the primary key.

These relationships should be validated against the specific installation, since only the composite key is formally documented.