Results for “pmifv_lot_event_v”

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

Overview

PMIFV_LOT_EVENT_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PMI – Process Manufacturing Intelligence product family. Its stated purpose is to provide consolidated access to inventory events involving each lot. Rather than requiring the report author or integration developer to query the underlying pending and completed inventory transaction tables separately, the view furnishes a unified, denormalized result set in which lot-level transaction activity is presented alongside descriptive attributes of both the item and the lot itself.

The view is defined with a UNION ALL construct that merges two otherwise identical query blocks, one drawing from pending inventory transactions and the other from completed transactions. This design allows consumers to see the full lifecycle of lot movement — transactions still awaiting completion as well as those already posted — through a single, stable interface. The view is marked WITH READ ONLY and is reported as VALID, confirming that it is intended strictly for query and reporting purposes and cannot be used as a DML target.

Within Oracle EBS releases 12.1.1 and 12.2.2 the view retains the same definitional behavior. It is commonly leveraged in process manufacturing reporting, lot genealogy inquiries, inventory event analytics, and downstream data extraction for warehousing or business intelligence layers.

Underlying Base Objects

The view is constructed over four documented synonym-referenced base objects:

• IC_TRAN_PND — the pending inventory transaction table. The first SELECT of the UNION ALL reads from this object and derives its quantity measures via SUM(T.TRANS_QTY) and SUM(T.TRANS_QTY2).
• IC_TRAN_CMP — the completed inventory transaction table. The second SELECT reads from this object with an identical column projection, except that the COMPLETED_IND position is populated with the literal 1 rather than a stored column value.
• IC_ITEM_MST — the item master. Joined on ITEM.ITEM_ID = T.ITEM_ID to supply item number, description, and unit-of-measure attributes.
• IC_LOTS_MST — the lot master. Joined on LOT.ITEM_ID = T.ITEM_ID AND LOT.LOT_ID = T.LOT_ID to supply lot number, sublot number, and lot status.

Both SELECT blocks exclude rows where T.LOT_ID <> 0, ensuring that only genuine lot-controlled transactions are surfaced. Each block groups by item, lot, transaction date, organization, document type, document ID, line ID, item descriptive columns, lot descriptive columns, warehouse, location, QC grade, lot status, and reason code, so that quantities are aggregated per transaction line and lot.

Key Columns

• PRODUCT_ITEM_ID / PRODUCT_LOT_ID (documented as ITEM_ID and LOT_ID) — surrogate keys for the item and lot involved in the event.
• TRANS_DATE / TRANSACTION_DATE — the effective date of the inventory transaction.
• ORGN_CODE / ORGANIZATION_CODE — the inventory organization in which the event occurred.
• DOC_TYPE / DOCUMENT_TYPE and DOC_ID / DOCUMENT_ID — the transaction source document category and its identifier, with LINE_ID identifying the specific document line.
• ITEM_NO, ITEM_DESC1, ITEM_UM, ITEM_UM2 — item number, primary description, primary unit of measure, and the secondary unit of measure.
• LOT_NO, SUBLOT_NO, LOT_STATUS, QC_GRADE — lot identity and quality-related attributes, including the sublot designation, the current lot status code, and the assigned QC grade.
• SUM_TRANS_QTY1 / TRANSACTION_QUANTITY — the aggregated primary-quantity movement for the grouped event.
• SUM_TRANS_QTY2 / TRANSACTION_QUANTITY_UOM2 — the aggregated secondary-quantity movement. This is the column the user searched for, and it carries the total quantity expressed in the secondary unit of measure defined on the item.
• WHSE_CODE / WAREHOUSE, LOCATION, REASON_CODE — the physical warehouse and location holding the lot, plus the transaction reason code where applicable.
• COMPLETED_IND / TRANSACTION_COMPLE — an indicator distinguishing pending activity from completed activity; the completed branch always yields 1, while the pending branch returns the stored column value.

Common Use Cases and Queries

The primary use case is lot-centric inventory reporting, such as identifying every transaction that touched a given lot across pending and completed states, or reconciling secondary-quantity movements by item and organization. Analysts frequently filter on SUM_TRANS_QTY2 to validate secondary UOM totals against the item master definition.

A typical query retrieving aggregated secondary quantities for a specific lot is:

• SELECT item_no, lot_no, orgn_code, doc_type, trans_date, SUM_TRANS_QTY2 FROM apps.pmifv_lot_event_v WHERE lot_no = :lot AND orgn_code = :org ORDER BY trans_date;

To isolate pending versus completed events, filter on COMPLETED_IND:

• SELECT lot_no, doc_id, line_id, SUM_TRANS_QTY1, SUM_TRANS_QTY2 FROM apps.pmifv_lot_event_v WHERE completed_ind = 0;

Because the view applies GROUP BY over the transaction lines, consumers should treat each returned row as an aggregated event rather than a raw transaction record, and should avoid further unrestricted aggregation without first confirming the intended granularity.