Results for “expire_date”

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

Overview

IC_ITEM_INV_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the GMI (Process Manufacturing Inventory) product family. Its documented purpose is to supply OnHand and Commit quantities for an item, and it is referenced by the OPM Allocation Engine when evaluating available inventory during allocation and reservation processing.

The view consolidates two distinct inventory states into a single result set through a UNION ALL. Inventory physically present in a warehouse location is returned through the first branch, while quantities tied up in pending, unprocessed inventory transactions are returned through the second branch as commit quantities. This design allows the Allocation Engine, and any reporting query that mirrors its logic, to see both the stock that exists today and the stock that has been committed or is in flight but not yet posted.

Underlying Base Objects

The view is defined over five documented base objects, all exposed through the APPS schema as synonyms: IC_LOTS_MST, IC_LOCT_INV, IC_LOTS_STS, IC_TRAN_PND, and IC_ITEM_MST. IC_LOTS_MST is the lot master, providing LOT_NO, SUBLOT_NO, LOT_CREATED, EXPIRE_DATE, and QC_GRADE. IC_LOCT_INV holds location-level on-hand balances and is the source of LOCT_ONHAND, LOCT_ONHAND2, WHSE_CODE, and LOCATION. IC_LOTS_STS provides LOT_STATUS, which is joined with an outer join and filtered so that only lot statuses flagged as order-processable (ORDER_PROC_IND is null or 1) and not rejected (REJECTED_IND is null or 0) are returned. IC_TRAN_PND contains pending transactions and drives the commit-quantity branch, filtered where DELETE_MARK = 0, COMPLETED_IND = 0, and TRANS_QTY is negative. IC_ITEM_MST is included in the second branch to validate that the transaction item exists in the item master.

Lots with INACTIVE_IND set active and on-hand quantity greater than zero populate the on-hand columns; the commit columns are populated only from the pending-transaction branch.

Key Columns

• ITEM_ID — The inventory item identifier, the primary join key to the item master.
• LOT_NO / SUBLOT_NO / LOT_ID — The lot and sublot identifiers under which the quantity is held.
• LOT_STATUS — The status of the lot as recorded in the lot status master.
• LOT_CREATED / EXPIRE_DATE / QC_GRADE — Lot creation date, expiry date, and quality grade.
• WHSE_CODE / LOCATION — The warehouse and location holding the quantity.
• LOCT_ONHAND / LOCT_ONHAND2 — The on-hand quantity and its second (dual) unit of measure representation; zero in the commit branch.
• COMMIT_QTY / COMMIT_QTY2 — The committed quantity and its second unit of measure representation, populated from the pending transaction quantity; zero in the on-hand branch.

Common Use Cases and Queries

The most frequent use is availability checking for allocation. A query for a specific item aggregates on-hand and commit quantities by warehouse and location:

• SELECT item_id, whse_code, location, SUM(LOCT_ONHAND) onhand, SUM(COMMIT_QTY) commit_qty FROM apps.ic_item_inv_v WHERE item_id = :item_id GROUP BY item_id, whse_code, location;
• Lot-level inquiries, such as those supporting expiration review, filter on EXPIRE_DATE across the on-hand branch.
• Reconciliation reports compare LOCT_ONHAND against periodic physical counts, while COMMIT_QTY supports analysis of pending, incomplete negative transactions that have not yet relieved inventory.
• Integration extracts use the view as a single source for both stock-on-hand and in-transit commitment figures in OPM environments.

Because the view performs the relevant status filtering internally, consumers should not reapply ORDER_PROC_IND or REJECTED_IND predicates; doing so may inadvertently exclude rows the view intends to include.