Search Results lot_expiration_display_date




Overview
Explore in AI Chat

Overview

GME_INV_TXN_LOTS_ERES_V is a supplementary reporting view owned by the APPS schema within the Oracle E-Business Suite Process Manufacturing (GME — Process Manufacturing Process Execution) product family. It presents lot-level detail for inventory material transactions, consolidating both pending (temporary) and posted transaction records into a single, query-friendly result set. The view is defined as a UNION ALL over the temporary and permanent lot transaction tables, allowing downstream reports, integrations, and e-records (ERES) processing to retrieve lot attributes such as lot number, parent lot, grade, lot status, transaction quantity, and expiration date without needing to join multiple tables directly. The name suffix _ERES_V indicates its typical role as a supporting data source for electronic records and signatures and associated compliance reporting. Because lot status is a critical compliance attribute in process manufacturing, this view is frequently the object referenced when searching on lot_status.

Underlying Base Objects

The view is defined over the following documented base objects, all exposed to APPS through synonyms: MTL_TRANSACTION_LOTS_TEMP, MTL_TRANSACTION_LOT_NUMBERS, MTL_LOT_NUMBERS, MTL_MATERIAL_STATUSES_TL, MTL_MATERIAL_TRANSACTIONS_TEMP, MTL_MATERIAL_TRANSACTIONS, and the FND_DATE package.

The first branch of the union draws from MTL_TRANSACTION_LOTS_TEMP joined to MTL_MATERIAL_TRANSACTIONS_TEMP by TRANSACTION_TEMP_ID, and to MTL_LOT_NUMBERS by inventory item, organization, and lot number. The second branch draws from MTL_TRANSACTION_LOT_NUMBERS joined to MTL_MATERIAL_TRANSACTIONS by TRANSACTION_ID, and to MTL_LOT_NUMBERS in the same manner. In both branches, MTL_LOT_NUMBERS is outer-joined to MTL_MATERIAL_STATUSES_TL on STATUS_ID and to the user's session language (USERENV('LANG')) to resolve the translated lot status code. Expiration dates are additionally formatted through FND_DATE.DATE_TO_DISPLAYDT. This structure lets the view expose both unposted and posted lot transactions through one interface.

Key Columns

Common Use Cases and Queries

Typical uses include lot traceability reports, material status/quality reports, and ERES compliance extracts that must show the status of each lot at the time of transaction. Because the view already resolves the status code and joins lot attributes, it is well suited to ad hoc SQL and concurrent program data sources.

To retrieve transactions for a specific lot status:

  • SELECT transaction_id, lot_number, lot_status, transaction_quantity, lot_expiration_display_date FROM apps.gme_inv_txn_lots_eres_v WHERE lot_status = 'Available';
  • SELECT lot_number, parent_lot_number, grade_code, lot_status FROM apps.gme_inv_txn_lots_eres_v WHERE inventory_item_id IS NOT NULL;
  • SELECT lot_status_id, lot_status, COUNT(*) FROM apps.gme_inv_txn_lots_eres_v GROUP BY lot_status_id, lot_status ORDER BY lot_status;

Queries should account for both union branches; filtering on TRANSACTION_TEMP_ID isolates pending transactions, while filtering on TRANSACTION_ID isolates posted transactions.