Search Results gmi_sublot_generate




Overview
Explore in AI Chat

Overview

GMI_SUBLOT_GENERATE is a table owned by the GMI schema (Process Manufacturing Inventory) within Oracle E-Business Suite, and it exists in both release 12.1.1 and 12.2.2. Its documented purpose is to hold the values for the next sublot to be generated for a given lot. In process manufacturing, a single lot may be subdivided into multiple sublots that are tracked independently through production, quality, and inventory transactions. Rather than requiring users to assign each sublot suffix manually, the application consults GMI_SUBLOT_GENERATE to determine the next available suffix, thereby maintaining uniqueness and a deterministic numbering sequence per item and lot.

The table is documented as a standalone object, meaning the ETRM metadata does not identify any foreign key relationship to another table. Under the heuristic Data Vault classification mined from the FK structure, GMI_SUBLOT_GENERATE would most naturally be modeled as a satellite, since it stores descriptive, mutable state — specifically the next suffix value — attached to a business key composed of ITEM_ID and LOT_NO rather than representing an independent hub or a relationship link. This classification is a modeling suggestion only; the physical implementation remains a conventional relational table with no enforced parent-child constraints.

The table is compact, with a documented physical schema of eight columns and a single unique index, GMI_SUBLOT_GENERATE_PK, declared on (ITEM_ID, LOT_NO).

Key Information Stored

The primary key constraint GMI_SUBLOT_GENERATE_PK is defined on the composite of ITEM_ID and LOT_NO. Notably, this composite is also the only documented unique index, which means the same two columns serve simultaneously as the physical primary key and as the business-key candidate — there is no separate surrogate key column such as a sequence-generated ID. Consequently, the grain of the table is one row per item and lot combination.

  • ITEM_ID — identifier of the item to which the sublot numbering rule applies; part of the composite primary key.
  • LOT_NO — the lot number for which sublots are being generated; the second component of the composite primary key.
  • NEXT_SUBLOT_SUFFIX — the substantive payload of the table, holding the suffix value that will be assigned to the next sublot created for the item/lot pair. This is the column that is read and then advanced as sublots are generated.
  • CREATED_BY, CREATION_DATE — standard Oracle EBS audit columns recording the user and timestamp of initial row creation.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — audit columns capturing the identity, timestamp, and login context of the most recent modification, which will correspond to the last suffix increment.

Because the audit columns follow the standard EBS who-column pattern, they support row-level accountability auditing familiar to EBS administrators and report developers.

Common Use Cases and Queries

The principal use case is sublot generation during process manufacturing execution. When a lot is split or a sublot is created in Process Manufacturing Inventory, the application reads NEXT_SUBLOT_SUFFIX, applies it to the new sublot, and increments the stored value. A second use case is troubleshooting sublot numbering conflicts, where an administrator inspects the stored suffix to understand why a particular sublot identifier was issued.

Typical query patterns include retrieving the current counter for a specific item and lot:

  • SELECT ITEM_ID, LOT_NO, NEXT_SUBLOT_SUFFIX FROM GMI.GMI_SUBLOT_GENERATE WHERE ITEM_ID = :item_id AND LOT_NO = :lot_no;
  • A reporting query listing all lots with a pending suffix, ordered by LOT_NO, to validate counter state prior to a production run.
  • An audit query using LAST_UPDATED_BY and LAST_UPDATE_DATE to identify who last advanced a given counter and when.

Where a counter appears stale or duplicated, DBAs should verify that generation logic is being invoked through the supported application path rather than direct inserts, since the table has no enforced foreign keys to validate ITEM_ID or LOT_NO against their respective masters.

Related Objects

Because the metadata classifies GMI_SUBLOT_GENERATE as standalone, there are no documented foreign key relationships to enumerate. However, the following objects are the most significant in practice, joined on the columns identified above:

  • GMI_ITEM_MST — item master, joined to GMI_SUBLOT_GENERATE on ITEM_ID to resolve item names, descriptions, and item-level inventory attributes.
  • GMI_LOTS (or the corresponding lot master) — joined on ITEM_ID and LOT_NO to obtain lot attributes such as lot status, expiration, and creation details.
  • GMI_SUBLOTS (or the sublot inventory table) — the transaction-level table where generated sublots are recorded, correlated by ITEM_ID and LOT_NO, with the suffix sourced from NEXT_SUBLOT_SUFFIX.
  • GMI_LOT_GENEALOGY — the genealogy table, where the generated suffix is analyzed to trace sublot parent-child lineage.
  • GMI_SUBLOT_GENERATE_PK — the unique index enforcing the (ITEM_ID, LOT_NO) business key.

No public APIs or views referencing this table are documented in the supplied metadata; integration developers should treat it as an internal GMI implementation detail and access sublot numbering through supported Process Manufacturing Inventory APIs rather than direct SQL.