Search Results trans_qty




Overview
Explore in AI Chat

Overview

IC_TRAN_CMP_VW1 is a read-only database view owned by the APPS schema in Oracle E-Business Suite, classified under the GMI (Process Manufacturing Inventory) product family. Its documented purpose is to present completed inventory transactions in a single, consolidated result set. Rather than querying the base transaction tables individually, applications and reports can resolve a unified picture of finished material movements through this view.

Functionally, the view is a union of two distinct storage areas: transactions that have already been committed to the completed-transaction table (IC_TRAN_CMP) and pending transactions (IC_TRAN_PND) that have been flagged as complete. A literal discriminator column named C is appended to each branch of the union, carrying the value 'C' for rows sourced from the completed table and 'P' for rows sourced from the pending table. This allows downstream consumers to distinguish the origin of any given row without needing to know the physical partitioning. Because it exposes the TRANS_QTY and TRANS_QTY2 columns, it is a frequent target for quantity-based reconciliation, variance analysis, and inventory valuation queries.

Underlying Base Objects

The view is defined exclusively over two APPS synonyms, which resolve to the underlying GMI inventory transaction tables:

  • IC_TRAN_CMP — the completed inventory transaction table. All rows from this object are returned unconditionally in the first branch of the union.
  • IC_TRAN_PND — the pending inventory transaction table. In the second branch, only rows satisfying COMPLETED_IND = 1 and DELETE_MARK = 0 are selected, meaning the view filters pending records to those marked complete and excludes logically deleted rows.

Both branches project an identical, positionally aligned column list, which is a prerequisite for a valid UNION. The view therefore behaves as a superset of committed transactions plus a filtered subset of pending transactions that have attained completed status. Because it is a view and not a materialized object, it inherits the security and read consistency of its base tables and reflects changes in real time.

Key Columns

The view exposes a rich set of transaction attributes. The most operationally significant include:

Common Use Cases and Queries

The view is typically used for inventory transaction reporting, quantity reconciliation, and integration feeds where both posted and completed-pending movements must be seen together. A basic query that summarizes quantity by source and item follows:

  • SELECT ITEM_ID, C, SUM(TRANS_QTY) FROM APPS.IC_TRAN_CMP_VW1 GROUP BY ITEM_ID, C;
  • SELECT * FROM APPS.IC_TRAN_CMP_VW1 WHERE TRANS_QTY > 0 AND TRANS_DATE >= SYSDATE - 30;
  • Filtering on C = 'P' isolates transactions still resident in the pending table, useful for identifying unposted activity.

Because the view spans two large transaction tables, queries should be constrained by date, organization, or item to avoid full scans. It serves as the sanctioned GMI read interface for completed transaction data across EBS 12.1.1 and 12.2.2.