Results for “mtl_lot_conv_audit_details”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_LOT_CONV_AUDIT_DETAILS is an Oracle Inventory (INV) audit table that stores the detailed, line-level records produced when lot-level quantity conversions are performed in Oracle E-Business Suite. Its documented description is "Audit Detail Record," positioning it as a subordinate audit trail to the lot conversion audit header. The table is owned by the INV schema and is marked VALID in the ETRM metadata for both 12.1.1 and 12.2.2, with the physical schema documented across 19 columns in the 12.2.2 model.

Functionally, the table captures the before-and-after quantity positions for a specific lot, revision, organization, subinventory, LPN, and locator combination, together with the transaction quantity that triggered the update. It therefore serves as the evidentiary record for reconciling what the on-hand secondary and primary quantities were prior to the conversion, what they became afterward, and what transaction quantity was applied. This makes it valuable for inventory reconciliation, audit, and quantity-conversion troubleshooting, particularly in environments using dual-unit-of-measure (primary and secondary UOM) tracking.

The ETRM relationship data classifies this object heuristically as standalone in its Data Vault modeling suggestion. That is, the mined foreign-key structure did not surface strong outbound dependencies suitable for modeling as a hub or a link. A modeling exercise might instead treat it as a satellite-style record keyed to the conversion audit activity, but the metadata does not provide a definitive Data Vault assignment beyond the standalone classification.

Key Information Stored

The table is anchored by its primary key, CONV_AUDIT_DETAIL_ID, which is a surrogate identifier populated from the MTL_LOT_CONV_AUDIT_DETAILS_PK unique index. This is the only documented unique index and is therefore the sole business-key candidate identified in the metadata. The foreign relationship to the audit header is carried by CONV_AUDIT_ID, which links each detail row back to its parent conversion audit record.

The most significant dimensions and quantity columns include:

• CONV_AUDIT_ID — Durable link to the parent lot conversion audit entry.
• REVISION — The lot revision affected by the conversion.
• ORGANIZATION_ID — The inventory organization in which the conversion occurred.
• SUBINVENTORY_CODE — The subinventory holding the affected quantity.
• LOCATOR_ID and LPN_ID — The locator and license plate number (LPN) narrowing the exact storage position.
• OLD_PRIMARY_QTY / OLD_SECONDARY_QTY — The quantity in primary and secondary units of measure before the conversion.
• NEW_PRIMARY_QTY / NEW_SECONDARY_QTY — The quantity in primary and secondary units after the conversion.
• TRANSACTION_PRIMARY_QTY / TRANSACTION_SECONDARY_QTY — The transaction quantities applied to effect the change.
• TRANSACTION_UPDATE_FLAG — Indicator of whether the underlying transaction update was applied.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS Who columns providing audit provenance and timestamps.

Common Use Cases and Queries

Typical uses include tracing quantity differences after a lot conversion, validating conversion accuracy against on-hand balances, and supporting internal or external audit requests. A common query joins detail rows to their header via CONV_AUDIT_ID to reconstruct the full conversion event, while filtering on ORGANIZATION_ID, SUBINVENTORY_CODE, or a lot/revision combination narrows the scope.

• Reconciliation queries comparing OLD_PRIMARY_QTY against NEW_PRIMARY_QTY to detect unexpected or zero-delta conversions.
• Audit reports extracting all detail rows for a given CONV_AUDIT_ID or date range using CREATION_DATE.
• Exception monitoring where TRANSACTION_UPDATE_FLAG indicates an incomplete or failed update.
• Secondary UOM analysis comparing primary-versus-secondary deltas for dual-UOM items.

A representative pattern is: SELECT d.* FROM mtl_lot_conv_audit_details d WHERE d.conv_audit_id = :p_audit_id ORDER BY d.conv_audit_detail_id; joined where needed to header metadata for transaction context.

Related Objects

The ETRM metadata describes this object as structurally standalone, so its principal relationship is an implicit parent-child link to the lot conversion audit header rather than a documented foreign key. The most significant related objects include:

• Lot conversion audit header table (INV) — joined on CONV_AUDIT_ID to obtain the parent conversion event context.
• MTL_LOT_CONV_AUDIT_DETAILS_PK — the unique index/constraint enforcing CONV_AUDIT_DETAIL_ID.
• MTL_ONHAND_QUANTITIES / MTL_ONHAND_QUANTITIES_DETAIL — the on-hand tables reconciled against conversion results, matched on ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID, and inventory item keys.
• MTL_ITEM_LOCATIONS — resolves LOCATOR_ID to a human-readable locator.
• MTL_SECONDARY_INVENTORIES — resolves SUBINVENTORY_CODE.
• WMS_LPN / LPN-related tables — resolve LPN_ID for license plate context.
• MTL_LOT_CONVERSION APIs (public INV conversion programs) — the process that generates these audit rows.
• FND_USER — resolves CREATED_BY and LAST_UPDATED_BY to user identities.