Results for “object_number”

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

Overview

MTL_OBJECT_NUMBERS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered under the Inventory (INV) product. Its documented purpose is to present the genealogy object numbers maintained by the Oracle EBS genealogy and tracking infrastructure. Genealogy in EBS links lots, serial numbers, and external objects into parent-child relationships so that upstream and downstream traceability can be queried. This view provides a single, consolidated projection of the object numbers that participate in that genealogy, resolving the identifier of each genealogy object (GEN_OBJECT_ID) to its human-readable object number.

Because the view normalizes lot numbers, serial numbers, and user-defined external objects into one structure, it is well suited to reporting and integration scenarios where a caller must resolve an OBJECT_ID and OBJECT_TYPE pair obtained from genealogy tables back to a display value. It also supports generic queries that list all genealogy-tracked entities for an organization and item without requiring the caller to union the underlying tables manually. The view is documented as VALID and is supplied as part of the standard APPS codebase in both 12.1.1 and 12.2.2.

Underlying Base Objects

The view is defined as a UNION ALL over three referenced objects, all accessed through APPS synonyms:

• MTL_LOT_NUMBERS — supplies lot records. GEN_OBJECT_ID is mapped to OBJECT_ID, and LOT_NUMBER to OBJECT_NUMBER. This branch is tagged with OBJECT_TYPE = 1.
• MTL_SERIAL_NUMBERS — supplies serial records. GEN_OBJECT_ID is mapped to OBJECT_ID, and SERIAL_NUMBER to OBJECT_NUMBER. The serial branch is tagged with OBJECT_TYPE = 2, and uses CURRENT_ORGANIZATION_ID and INVENTORY_ITEM_ID.
• MTL_EXT_OBJECTS — supplies genealogy objects created outside the lot and serial tables, such as material defined by external or user-defined genealogy processes. OBJECT_ID and OBJECT_NUMBER are taken directly from MTL_EXT_OBJECTS, with OBJECT_TYPE = 3. ORGANIZATION_ID and INVENTORY_ITEM_ID are returned as -1 in this branch, indicating that the values are not organization- or item-specific.

No filtering predicates are applied in the view text, so all rows from each base object are projected. The UNION ALL preserves duplicates and avoids the sort overhead of a UNION, which makes the view inexpensive for reporting purposes.

Key Columns

• OBJECT_ID — the genealogy object identifier. For lot and serial rows this is GEN_OBJECT_ID; for external object rows it is MTL_EXT_OBJECTS.OBJECT_ID.
• OBJECT_TYPE — discriminator identifying the source of the row: 1 = lot, 2 = serial number, 3 = external genealogy object.
• OBJECT_NUMBER — the printable identifier: LOT_NUMBER, SERIAL_NUMBER, or MTL_EXT_OBJECTS.OBJECT_NUMBER depending on the branch.
• ORGANIZATION_ID — the owning inventory organization. For lots this is the lot's ORGANIZATION_ID; for serials it is CURRENT_ORGANIZATION_ID; for external objects the literal -1 is returned.
• INVENTORY_ITEM_ID — the item associated with the object. Returned from the lot or serial record, and as -1 for the external object branch.

Common Use Cases and Queries

Typical usages include resolving genealogy identifiers during traceability reporting, validating object numbers before feeding them into upstream/downstream genealogy APIs, and building cross-entity listings of all tracked objects for an organization.

Example — list all genealogy objects for an organization and item:

• SELECT object_id, object_type, object_number FROM apps.mtl_object_numbers_v WHERE organization_id = :org_id AND inventory_item_id = :item_id ORDER BY object_type, object_number;

Example — resolve a single genealogy identifier returned from a genealogy query:

• SELECT object_number FROM apps.mtl_object_numbers_v WHERE object_id = :gen_object_id AND object_type = :type;

Example — isolate only serial-tracked objects:

• SELECT object_number, organization_id, inventory_item_id FROM apps.mtl_object_numbers_v WHERE object_type = 2;

When joining the view to genealogy tables, use OBJECT_ID and OBJECT_TYPE together as the composite key to avoid ambiguity between lots, serials, and external objects that could share numeric identifiers. Filter out rows where ORGANIZATION_ID = -1 when the query requires organization-level accuracy, since those rows originate from MTL_EXT_OBJECTS and carry no organization context.