Search Results mtl_object_genealogy_pk

Overview

MTL_OBJECT_GENEALOGY is an Oracle Inventory (INV) table that stores genealogy relationships between an object and its parents. It records the lineage or "where-used" structure of tracked entities within the supply chain, enabling organizations to trace how a given object (such as a serialized unit, lot, or assembly) was derived from, or consumed into, one or more parent objects. The table resides in the INV schema and holds 39 columns in the documented ETRM 12.2.2 physical schema, with the same definition applicable to EBS 12.1.1.

The table is populated by inventory transactions and supports both discrete and process manufacturing traceability requirements. Under a heuristic Data Vault classification, the mined foreign-key structure suggests a satellite-leaning model. In practice, MTL_OBJECT_GENEALOGY behaves as a link-like associative structure capturing the relationship between an object and its parent, carrying descriptive and audit attributes. Its effective-dating via START_DATE_ACTIVE and END_DATE_ACTIVE further supports a satellite interpretation for tracking changes over time. This classification is a modeling suggestion only; the operational role is that of a transaction-driven genealogy record.

Key Information Stored

The most significant columns capture object identity, parentage, and the transaction that established the relationship:

• OBJECT_ID — Surrogate/business identifier of the child object whose genealogy is being recorded.
• OBJECT_TYPE — The type/category of the child object (for example, serial, lot, or assembly).
• PARENT_OBJECT_ID — Identifier of the parent object that the child object derives from.
• PARENT_OBJECT_TYPE — The type/category of the parent object.
• START_DATE_ACTIVE — Effective start date of the genealogy relationship; part of the primary key.
• END_DATE_ACTIVE — Effective end date of the relationship, supporting temporal history.
• GENEALOGY_ORIGIN — Indicates the source or mechanism that created the genealogy record.
• GENEALOGY_TYPE — Classifies the nature of the parent-child relationship.
• ORIGIN_TXN_ID — Foreign key to MTL_MATERIAL_TRANSACTIONS, identifying the originating transaction that established the genealogy.
• UPDATE_TXN_ID — The transaction that last modified the genealogy record.
• OBJECT_ID2 / OBJECT_TYPE2 and PARENT_OBJECT_ID2 / PARENT_OBJECT_TYPE2 — Additional qualified object identifiers supporting composite or secondary genealogy references.
• LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, CREATION_DATE — Standard audit columns.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — Concurrent program and request tracking columns.

The documented primary key is MTL_OBJECT_GENEALOGY_PK, composed of START_DATE_ACTIVE, OBJECT_ID, and PARENT_OBJECT_ID. This composite key establishes the business-key identity of each relationship and distinguishes it from single-column surrogates used elsewhere.

Common Use Cases and Queries

MTL_OBJECT_GENEALOGY underpins product traceability, recall management, and quality investigations. Typical scenarios include tracing a finished serial number back to its component lots, or identifying all downstream assemblies affected by a defective component.

A forward trace (parent to children) follows the relationship from a known parent object:

• SELECT OBJECT_ID, OBJECT_TYPE, PARENT_OBJECT_ID FROM MTL_OBJECT_GENEALOGY WHERE PARENT_OBJECT_ID = :parent_id AND (END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE);

A reverse trace (child to parents) reconstructs the as-built configuration of an object:

• SELECT PARENT_OBJECT_ID, PARENT_OBJECT_TYPE, GENEALOGY_TYPE FROM MTL_OBJECT_GENEALOGY WHERE OBJECT_ID = :object_id;

Joining to MTL_MATERIAL_TRANSACTIONS via ORIGIN_TXN_ID enriches genealogy rows with transaction date, quantity, and transaction type, which is essential for audit and compliance reporting. Reporting use cases include recall impact analysis, warranty root-cause investigation, and lot-to-serial pedigree reports.

Related Objects

The most significant related objects, based on the documented foreign-key and relationship metadata, include:

• MTL_MATERIAL_TRANSACTIONS — Referenced by MTL_OBJECT_GENEALOGY.ORIGIN_TXN_ID; provides the originating inventory transaction detail.
• MTL_MATERIAL_TRANSACTIONS (via UPDATE_TXN_ID) — Supplies the transaction that last updated the genealogy row.
• MTL_SYSTEM_ITEMS_B — Generally used to resolve item context for tracked objects, linked through item identifiers derived from object records.
• MTL_SERIAL_NUMBERS — Commonly associated for serial-tracked genealogy, relating serial identifiers to object IDs.
• MTL_LOT_NUMBERS — Associated for lot-tracked genealogy and expiration-related traceability.
• MTL_OBJECT_GENEALOGY (self-referencing traversal) — Used recursively to walk multi-level parent-child chains.
• INV Genealogy APIs and concurrent programs — Populate and maintain genealogy records during transaction processing.

These relationships make MTL_OBJECT_GENEALOGY a central reference for supply chain traceability reporting and audit within Oracle Inventory.