Search Results qc_tran_vw2




Overview
Explore in AI Chat

Overview

QC_TRAN_VW2 is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the GMD (Process Manufacturing Product Development) product family. Its documented purpose is a where-used view: it reports the transactional lineage of material and lot movements, identifying for a given source item/lot the corresponding target item/lot and the documents through which that relationship was created. The view is therefore oriented toward genealogy, batch traceability, and quality-driven investigation rather than toward transactional data entry.

In EBS 12.1.1 and 12.2.2 the object retains the same owner and definition; the 12.2.2 documented metadata lists it as VALID with the same referenced base objects. Because it is a view and not a table, it carries no storage of its own, and it is typically consumed by reports, inquiries, and custom SQL that need to reconcile production, process order, and inventory adjustment activity in a single result set.

Underlying Base Objects

Per the documented metadata, QC_TRAN_VW2 is defined over six referenced base objects:

  • IC_TRAN_VW1 (VIEW) — the inventory transaction view supplying item, lot, transaction quantity, UOM, and document context.
  • IC_TRAN_PND (SYNONYM) — pending inventory transactions, providing the transaction identifier and status fields.
  • PM_MATL_DTL (SYNONYM) — process material detail lines, used twice in the view (aliased C and D) to distinguish the source line and the target line.
  • PM_BTCH_HDR (SYNONYM) — batch header, supplying the batch number exposed as DOC_NO.
  • OP_ORDR_DTL and OP_ORDR_HDR (SYNONYMS) — process order detail and header, used for the process-order branch of the union.

The view text is a three-branch UNION. The first branch joins IC_TRAN_VW1 to IC_TRAN_PND, PM_MATL_DTL (twice), and PM_BTCH_HDR for DOC_TYPE 'PROD', restricting the source material line to LINE_TYPE = -1 and the target line to LINE_TYPE IN (1, 2). The second branch covers DOC_TYPE 'OPSO' (process orders) via OP_ORDR_DTL and OP_ORDR_HDR. The third branch covers negative-quantity inventory adjustments (DOC_TYPE IN ('ADJI','ADJR')), where the source and target item/lot collapse to the same values and LINE_TYPE is hard-coded to -1.

Key Columns

Common Use Cases and Queries

The view is commonly used to trace where a component lot was consumed into a parent batch, to reconcile pending and completed transactions across production and process orders, and to include negative inventory adjustments in traceability reporting.

Typical query pattern:

  • SELECT item_id, lot_id, target_item_id, target_lot_id, doc_type, doc_no, trans_qty, trans_um FROM apps.qc_tran_vw2 WHERE lot_id = :lot_id;
  • SELECT * FROM apps.qc_tran_vw2 WHERE doc_type = 'PROD' AND doc_id = :batch_id ORDER BY trans_date;

Because the view joins several transaction-level objects and uses DISTINCT, filtering on ITEM_ID, LOT_ID, DOC_ID, or DOC_TYPE is recommended to keep execution plans efficient. As a view, it is read-only and should be queried rather than modified.