Search Results mtl_lot_conv_audit




Overview
Explore in AI Chat

Overview

MTL_LOT_CONV_AUDIT is an Inventory (INV) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that serves as the audit trail for lot-level conversion rate changes. As transactional lot conversion data is introduced, modified, or corrected, the application records the before-and-after state of the affected conversion rate in this table, preserving a chronological history of rate adjustments. This supports inventory valuation accuracy, regulatory audit requirements, and reconciliation of cost variances when conversion rates change after transactions have been processed.

From a data modeling perspective, the supplied relationship metadata classifies MTL_LOT_CONV_AUDIT as a standalone object under the Data Vault heuristic. This classification is a suggestion: the table functions as a satellite-style historically tracked structure attached to a conversion event, but because it is enriched by a single documented foreign key (to GMD_EVENT_SPEC_DISP) and lacks a surrogate hub structure, it is most reasonably modeled as an independent audit entity. Practitioners building a Data Vault layer may wish to treat CONVERSION_ID or EVENT_SPEC_DISP_ID as a natural business key feeding a hub, with this table supplying the descriptive satellite context.

Key Information Stored

The table contains 14 documented columns. The most significant are summarized below. The surrogate primary key is CONV_AUDIT_ID, backed by the unique index MTL_LOT_CONV_AUDIT_PK. Because the metadata identifies no additional unique index, there is no independently documented business-key candidate other than the primary key itself.

  • CONV_AUDIT_ID – Surrogate primary key uniquely identifying each audit row (MTL_LOT_CONV_AUDIT_PK).
  • CONVERSION_ID – Reference to the underlying lot conversion record being audited; the natural correlating identifier to source conversion data.
  • CONVERSION_DATE – Date on which the conversion was applied or recorded.
  • UPDATE_TYPE_INDICATOR – Flag describing the nature of the change (for example, insert versus update), enabling filtering by change category.
  • BATCH_ID – Batch identifier grouping the audited conversion activity.
  • REASON_ID – Reason code explaining why the conversion rate was changed.
  • OLD_CONVERSION_RATE – The conversion rate value prior to the change.
  • NEW_CONVERSION_RATE – The conversion rate value after the change.
  • EVENT_SPEC_DISP_ID – Foreign key to GMD_EVENT_SPEC_DISP, tying the audit record to a quality or event specification disposition.
  • Audit columns – CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN capture standard Oracle who-and-when stamping.

Common Use Cases and Queries

Typical uses include reconstructing the history of conversion rate changes for a given lot or batch, explaining cost-variance spikes, and satisfying internal or regulatory audit requests for change traceability. A representative query identifies all rate revisions for a batch:

  • SELECT conv_audit_id, conversion_id, conversion_date, old_conversion_rate, new_conversion_rate, reason_id, update_type_indicator FROM mtl_lot_conv_audit WHERE batch_id = :batch_id ORDER BY conversion_date;
  • Reporting the net rate delta over a period: join and compute new_conversion_rate - old_conversion_rate grouped by REASON_ID.
  • Audit reconciliation: link EVENT_SPEC_DISP_ID to GMD_EVENT_SPEC_DISP to correlate rate changes with quality dispositions.

Related Objects

The documented foreign key establishes the principal dependency from MTL_LOT_CONV_AUDIT to GMD_EVENT_SPEC_DISP via EVENT_SPEC_DISP_ID. Beyond this documented relationship, the table logically correlates with conversion and lot master data through CONVERSION_ID:

  • GMD_EVENT_SPEC_DISP – Joined on EVENT_SPEC_DISP_ID (documented FK).
  • MTL_LOT_CONV_AUDIT_PK – The unique index enforcing the primary key.
  • Lot conversion master tables – Referenceable via CONVERSION_ID to retrieve the current rate.
  • Inventory reason/transaction tables – Correlatable via REASON_ID and BATCH_ID for change justification and grouping.