Search Results mtl_transaction_lots_temp

Overview

GME_INV_TXN_LOTS_TEMP_ERES_V is a supplementary view owned by the APPS schema within the GME (Process Manufacturing Process Execution) product family. It is defined over MTL_TRANSACTION_LOTS_TEMP, the Oracle Inventory staging table that holds lot-level detail for pending material transactions prior to validation and posting. Because MTL_TRANSACTION_LOTS_TEMP stores only the raw lot number and quantities, it lacks the descriptive attributes (lot status, grade, parent lot, expiration, display-formatted dates) that users and external interfaces require. This view fills that gap by joining the temporary transaction tables to MTL_LOT_NUMBERS, MTL_MATERIAL_STATUSES_TL, and MTL_MATERIAL_TRANSACTIONS_TEMP, resolving the lot number into its master definition and translating STATUS_ID into a language-specific status code. The _ERES suffix indicates the view is used by the Electronic Records and Electronic Signatures (ERES) / e-records reporting layer in process manufacturing, where lot genealogy and status must be presented for audit and compliance purposes. In practice it functions as a read-only reporting and integration artifact: it presents the unposted lot transaction image exactly as it appears in the transaction interface, which makes it suitable for pre-post validation reports, interface diagnostics, and external system extracts.

Underlying Base Objects

The view is defined over four referenced objects plus one PL/SQL package, as documented in the ETRM metadata:

• MTL_TRANSACTION_LOTS_TEMP (SYNONYM) — the driving table, supplying GROUP_HEADER_ID, TRANSACTION_TEMP_ID, LOT_NUMBER, TRANSACTION_QUANTITY, SECONDARY_QUANTITY, and PRIMARY_QUANTITY.
• MTL_MATERIAL_TRANSACTIONS_TEMP (SYNONYM) — joined on TRANSACTION_TEMP_ID, providing the inventory item and organization context used to link to the lot master.
• MTL_LOT_NUMBERS (SYNONYM) — joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER, supplying PARENT_LOT_NUMBER, GRADE_CODE, STATUS_ID, and EXPIRATION_DATE.
• MTL_MATERIAL_STATUSES_TL (SYNONYM) — an outer-joined translation table keyed on STATUS_ID and LANGUAGE = USERENV('LANG'), supplying the translated STATUS_CODE.
• FND_DATE (PACKAGE) — invoked as FND_DATE.DATE_TO_DISPLAYDT to render the expiration date in the session's display format.

Because all table references are synonyms, the view resolves through the APPS synonyms to the underlying INV and GME base tables. The join is an equi-join on transaction, item, organization, and lot, with an outer join to the status translation, meaning rows without a matching status row are still returned with a null LOT_STATUS.

Key Columns

• GROUP_HEADER_ID / TRANSACTION_TEMP_ID — identifiers linking the lot row to its transaction staging record and grouping header.
• LOT_NUMBER — the lot entered on the pending transaction.
• PARENT_LOT_NUMBER — the parent lot from the lot master, used in genealogy and split/merge analysis.
• GRADE_CODE — the grade assigned to the lot in the lot master.
• LOT_STATUS_ID / LOT_STATUS — the numeric status identifier and its language-specific status code from MTL_MATERIAL_STATUSES_TL.
• TRANSACTION_QUANTITY, PRIMARY_QUANTITY, SECONDARY_QUANTITY — the transaction quantity in primary and secondary units of measure.
• LOT_EXPIRATION_DATE / LOT_EXPIRATION_DISPLAY_DATE — the raw expiration date and its FND_DATE-formatted display equivalent, ideal for report and page output.

Common Use Cases and Queries

Typical uses include pre-post validation of lot transactions, ERES audit reporting, and troubleshooting failed transaction interface rows. A representative query listing pending lot transactions with status and expiration is:

• SELECT transaction_temp_id, lot_number, lot_status, transaction_quantity, lot_expiration_display_date FROM apps.gme_inv_txn_lots_temp_eres_v WHERE group_header_id = :p_group_header_id;
• SELECT lot_number, parent_lot_number, grade_code, lot_status FROM apps.gme_inv_txn_lots_temp_eres_v WHERE transaction_temp_id = :p_txn_temp_id;
• SELECT lot_status, SUM(transaction_quantity) FROM apps.gme_inv_txn_lots_temp_eres_v GROUP BY lot_status;

Because the view reads only staging tables, results reflect transactions not yet posted and should be validated against the final MTL_MATERIAL_TRANSACTIONS and MTL_TRANSACTION_LOTS_GT records after processing.