Search Results ic_tran_vw1




Overview
Explore in AI Chat

Overview

IC_TRAN_VW1 is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the GMI (Process Manufacturing Inventory) product family. Its documented purpose is succinct: it is a transaction view. In practical terms, IC_TRAN_VW1 presents a consolidated, real-time read interface over Process Manufacturing inventory transactions by combining two physically separate transaction stores—pending transactions and completed transactions—into a single logical result set. This consolidation is significant because GMI records inventory activity in stages: transactions are first staged in the pending table and only later finalized into the completed table. The view eliminates the need for downstream reports, interfaces, and integrations to query both stores independently and reconcile the results.

The view is documented as VALID in ETRM 12.2.2 and is available in both 12.1.1 and 12.2.2 environments. Because it exposes a single uniform transaction stream keyed by TRANS_ID, it functions as a stable reporting abstraction that shields consumers from the underlying staging model.

Underlying Base Objects

IC_TRAN_VW1 is defined over two documented base objects, both exposed in the APPS schema as synonyms: IC_TRAN_CMP (completed transactions) and IC_TRAN_PND (pending transactions). The view text is a UNION ALL of two SELECT statements. The first branch reads from IC_TRAN_PND with the filter DELETE_MARK = 0, excluding logically deleted staging rows. The second branch reads from IC_TRAN_CMP and hard-codes the COMPLETED_IND column to the literal value 1, while the pending branch does not project a completed indicator in the same way, leaving transactions in the pending store distinguishable from completed ones.

Both branches project an identical column list, which preserves positional and type compatibility required by UNION ALL. Because IC_TRAN_CMP is presumed to hold only finalized records, no DELETE_MARK filter is applied to that branch. The result is a view whose cardinality equals the number of non-deleted pending rows plus all completed rows.

Key Columns

Common Use Cases and Queries

The view is typically used for transaction inquiries, reconciliation between pending and completed activity, and feeding downstream extracts. A common pattern filters by item, organization, or date range.

  • List pending versus completed counts: SELECT COMPLETED_IND, COUNT(*) FROM APPS.IC_TRAN_VW1 GROUP BY COMPLETED_IND;
  • Retrieve transactions for an item and organization: SELECT TRANS_ID, DOC_TYPE, TRANS_QTY, TRANS_UM, TRANS_DATE FROM APPS.IC_TRAN_VW1 WHERE ITEM_ID = :item AND ORGN_CODE = :orgn ORDER BY TRANS_DATE;
  • Audit completed-only activity: SELECT * FROM APPS.IC_TRAN_VW1 WHERE COMPLETED_IND = 1 AND TRANS_DATE >= :from_date;

Because the view reads directly from live transaction tables, queries should always be constrained by date or organization to avoid full scans across both stores.