Search Results transaction_display_date




Overview
Explore in AI Chat

Overview

GME_INV_TXNS_ERES_V is an APPS-owned database view in Oracle EBS Release 12.1.1 and 12.2.2, delivered under the GME (Process Manufacturing Process Execution) product family. Its documented purpose is to supply supplementary inventory transaction data by way of a UNION ALL of MTL_MATERIAL_TRANSACTIONS and MTL_MATERIAL_TRANSACTIONS_TEMP. The view therefore exposes both pending and completed material movements through a single, presentation-friendly projection, making it well suited to concurrent-program reporting, e-records style audit extracts, and integration interfaces that must reconcile in-flight transactions against posted ones.

Because a temp row has no TRANSACTION_ID, the view emits NULL TRANSACTION_ID for the temporary branch while carrying TRANSACTION_TEMP_ID; the converse applies to the persistent branch. This deliberate symmetry lets a caller query one object across the full transaction lifecycle rather than switching sources mid-process.

Underlying Base Objects

The ETRM 12.2.2 metadata records the following referenced base objects: MTL_MATERIAL_TRANSACTIONS, MTL_MATERIAL_TRANSACTIONS_TEMP, MTL_LOT_NUMBERS, MTL_MATERIAL_STATUSES_TL, MTL_TRANSACTION_REASONS, MTL_TRANSACTION_TYPES, and the MTL_ITEM_LOCATIONS_KFV view, with formatting assistance from the FND_DATE package. The synonym chain resolves to the MTL base objects, while MTL_ITEM_LOCATIONS_KFV supplies the concatenated locator flexfield description. All lot, reason, locator, and status joins are outer-joined ((+)) to the transaction source, and MTL_MATERIAL_STATUSES_TL is additionally restricted by USERENV('LANG') so that the status description is returned in the session language. MTL_TRANSACTION_TYPES is the one inner join, guaranteeing a transaction type name for every row.

Key Columns

Organizational context is provided by ORGANIZATION_ID, INVENTORY_ITEM_ID, SUBINVENTORY_CODE, LOCATOR_ID, and the concatenated LOCATOR. Transaction identity is carried by TRANSACTION_ID, TRANSACTION_TEMP_ID, ORIGINAL_TRANSACTION_TEMP_ID, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_ACTION_ID, and the source columns (TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_NAME, TRX_SOURCE_LINE_ID, SOURCE_LINE_ID).

Quantity and timing information appears as TRANSACTION_QUANTITY, TRANSACTION_UOM, PRIMARY_QUANTITY, SECONDARY_UOM_CODE, SECONDARY_TRANSACTION_QUANTITY, and TRANSACTION_DATE, the latter accompanied by the formatted TRANSACTION_DISPLAY_DATE. Lot-centric attributes include LOT_NUMBER, PARENT_LOT_NUMBER (the column commonly sought by genealogy and traceability queries), GRADE_CODE, LOT_STATUS_ID/LOT_STATUS, and LOT_EXPIRATION_DATE with its display counterpart. Reason codes are exposed through REASON_ID and REASON_NAME.

Common Use Cases and Queries

  • Pending-versus-posted reconciliation: rows where TRANSACTION_ID IS NULL represent temp transactions, while rows where TRANSACTION_TEMP_ID IS NULL are posted to the interface.
  • Genealogy and parent lot traceability using PARENT_LOT_NUMBER.
  • Lot status and expiry reporting against LOT_STATUS and LOT_EXPIRATION_DATE.
  • Reason-code driven quality extracts via REASON_NAME.
  • Locator-level movement summaries using LOCATOR and SUBINVENTORY_CODE.

A representative query retrieving parent lot information is:

SELECT organization_id, inventory_item_id, lot_number, parent_lot_number, transaction_quantity, transaction_uom, transaction_display_date, transaction_type_name FROM apps.gme_inv_txns_eres_v WHERE parent_lot_number IS NOT NULL AND organization_id = :p_org ORDER BY lot_number, transaction_display_date;

To isolate unposted activity, add AND transaction_id IS NULL; to review committed history only, add AND transaction_temp_id IS NULL.