Search Results trans_stat

Overview

PMIBV_INVENTORY_TRANSACTIONS_V is an APPS-owned database view in Oracle E-Business Suite that consolidates inventory transaction data from the completed transactions table and the pending transactions table. It belongs to the PMI (Process Manufacturing Intelligence) product family, which is documented as obsolete in ETRM 12.2.2, but the underlying object remains valid within the APPS schema and continues to be referenced by legacy process manufacturing reporting and integration components.

The view presents a denormalized, unified feed of inventory movements, exposing a consistent column set regardless of the transaction's lifecycle state. Its design role is to provide downstream consumers — typically OPM (Oracle Process Manufacturing) inventory reports, interfaces, and reconciliation extracts — with a single query point for both posted (completed) and staged (pending) transactions, eliminating the need to union the base tables manually. Because it is defined WITH READ ONLY, it cannot be used as a DML target; it is strictly a query surface.

Underlying Base Objects

The view is defined over two synonyms, both resolving to OPM inventory transaction tables:

• IC_TRAN_CMP (SYNONYM) — the completed (posted) inventory transaction table. All rows are contributed to the view without filtering.
• IC_TRAN_PND (SYNONYM) — the pending (unposted) inventory transaction table. Only rows satisfying COMPLETED_IND = 1 and DELETE_MARK = 0 are included.

Each branch projects an identical column list, and a literal discriminator is prepended: the completed branch tags rows with 'C', and the pending branch tags rows with 'P'. This discriminator surfaces as the SOURCE column. The two result sets are combined using UNION, so duplicate rows across the two tables are eliminated. The view is therefore a horizontally aligned, vertically stacked projection of the two base tables rather than a join.

Key Columns

• SOURCE — Literal flag, 'C' for completed or 'P' for pending, indicating which base table supplied the row.
• TRANS_ID — Transaction identifier, unique within each base table but not guaranteed unique across the union.
• ITEM_ID, LOT_ID, LOCATION — Inventory item, lot, and storage location references.
• CO_CODE, ORGN_CODE, WHSE_CODE — Company, organization, and warehouse context for the movement.
• DOC_ID, DOC_TYPE, DOC_LINE, LINE_TYPE — Source document and line information identifying the originating business event.
• OP_CODE — The operation code associated with the transaction. This is the column referenced in the user's search and identifies the process operation or inventory action performed.
• TRANS_DATE, TRANS_QTY, TRANS_QTY2, TRANS_UM, TRANS_UM2 — Transaction date, primary and secondary quantities, and their units of measure.
• QC_GRADE, LOT_STATUS, TRANS_STAT, REASON_CODE — Quality grade, lot status, transaction status, and reason code attributes.
• GL_POSTED_IND, EVENT_ID — General Ledger posting indicator and the associated event identifier.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY — Standard audit columns inherited from the base tables.

Common Use Cases and Queries

Typical usage includes inventory reconciliation, pending-versus-completed transaction comparison, GL posting status verification, and operation-level material flow analysis. The OP_CODE column is frequently used to filter transactions by process operation.

Listing all transactions for a specific operation code:

• SELECT SOURCE, TRANS_ID, ITEM_ID, ORGN_CODE, WHSE_CODE, OP_CODE, TRANS_DATE, TRANS_QTY, TRANS_UM FROM APPS.PMIBV_INVENTORY_TRANSACTIONS_V WHERE OP_CODE = :op_code ORDER BY TRANS_DATE DESC;

Comparing pending versus completed counts by operation:

• SELECT OP_CODE, SOURCE, COUNT(*) FROM APPS.PMIBV_INVENTORY_TRANSACTIONS_V GROUP BY OP_CODE, SOURCE ORDER BY OP_CODE;

Identifying transactions not yet posted to GL:

• SELECT SOURCE, TRANS_ID, DOC_ID, DOC_TYPE, OP_CODE, GL_POSTED_IND FROM APPS.PMIBV_INVENTORY_TRANSACTIONS_V WHERE NVL(GL_POSTED_IND, 'N') = 'N' ORDER BY TRANS_DATE;

Because the view is read-only and built on two base tables, query performance benefits from filtering on indexed columns such as ITEM_ID, ORGN_CODE, TRANS_DATE, or OP_CODE. Given the obsolete PMI status, consumers should validate continued support against current OPM inventory reporting alternatives.