Results for “ic_loct_inv”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IC_LOCT_INV is the location inventory table in the Oracle Process Manufacturing (GMI) inventory module, owned by the GMI schema and valid in Oracle EBS 12.1.1 and 12.2.2. It stores on-hand inventory quantities by item, warehouse, lot, and storage location, serving as the authoritative record of where a given lot of a given item physically resides and how much is available at that spot within a warehouse. Because it is scoped granularly to the location level rather than just the warehouse level, it supports detailed floor-level material control, lot tracking, and quality-driven quarantine within OPM facilities.

The heuristic Data Vault classification for this object is link. This reflects its structure: IC_LOCT_INV resolves the many-to-many relationship between inventory transactions and physical storage positions, connecting items, lots, warehouses, and lot statuses through its primary key. This classification is a modeling suggestion and should be treated as a starting interpretation rather than a fixed design mandate.

Key Information Stored

The primary key, IC_LOCT_INV_PK, is the composite business key rather than a surrogate identifier. It comprises four columns: ITEM_ID, WHSE_CODE, LOT_ID, and LOCATION. Together these uniquely define a single inventory position, so a distinct row exists for each combination of item, warehouse, lot, and location.

The quantity-bearing columns, LOCT_ONHAND and LOCT_ONHAND2, hold the on-hand balances at the location; the presence of a secondary on-hand column supports dual-unit or alternate reporting scenarios common in OPM. LOT_STATUS records the status of the lot at that location (for example, released, quarantined, or restricted), and QCHOLD_RES_CODE captures a quality hold resource reference. WHSE_CODE identifies the warehouse and LOCATION the specific storage position within it. ITEM_ID and LOT_ID tie the balance to a specific item and lot.

Supplementary columns include TEXT_CODE for the associated descriptive text, MATERIAL_TRANSACTION_ID linking to the generating transaction, and the standard audit and EBS bookkeeping fields (LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID). DELETE_MARK supports logical deletion, and MIGRATED_IND flags records loaded through data migration.

Common Use Cases and Queries

Typical reporting includes on-hand inventory by location, lot status reconciliation, quality-hold impact analysis, and reconciliation of physical counts to system balances. A representative query reproduces the primary key lookup and joins to master tables:

• On-hand by item and warehouse: SELECT ITEM_ID, WHSE_CODE, LOT_ID, LOCATION, LOCT_ONHAND FROM GMI.IC_LOCT_INV WHERE ITEM_ID = :p_item AND WHSE_CODE = :p_whse;
• Quarantined or quality-held stock: SELECT * FROM GMI.IC_LOCT_INV WHERE QCHOLD_RES_CODE IS NOT NULL;
• Inventory by lot status: SELECT LOT_STATUS, SUM(LOCT_ONHAND) FROM GMI.IC_LOCT_INV GROUP BY LOT_STATUS;
• Active (non-deleted) balances: SELECT * FROM GMI.IC_LOCT_INV WHERE NVL(DELETE_MARK, 0) = 0;

These patterns are frequently wrapped in views or extracts feeding inventory valuation and cycle-count processes.

Related Objects

The foreign keys define the primary dependencies and join paths:

• IC_LOTS_MST — joined on ITEM_ID and LOT_ID; supplies item and lot master detail.
• IC_WHSE_MST — joined on WHSE_CODE; defines warehouse attributes.
• IC_LOTS_STS — joined on LOT_STATUS; provides lot status descriptions.
• IC_TEXT_HDR — joined on TEXT_CODE; resolves descriptive text.
• QC_HRES_MST — joined on QCHOLD_RES_CODE; resolves quality hold resources.

Through these relationships IC_LOCT_INV anchors location-level inventory reporting across the OPM inventory and quality domains.