Results for “serial_number”

2 results




Overview
Explore in AI Chat

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

Overview

PJM_UNIT_SERIAL_HISTORY is a transactional audit table owned by the PJM (Project Manufacturing) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It records the historical change trail of associations between inventory serial numbers and Project Manufacturing unit numbers. When a serialized item is re-associated from one unit number to another — typically during contract manufacturing, configuration changes, or rework — Oracle writes a record capturing both the prior (OLD_UNIT_NUMBER) and the current (NEW_UNIT_NUMBER) values. This preserves a complete lineage of the serial-to-unit relationship, which is essential in regulated industries such as aerospace, defense, and electronics, where traceability of a physical unit's configuration history must be demonstrable.

Under the heuristic Data Vault classification derived from its foreign-key structure, the table is best modeled as a link — it resolves relationships between MTL_SERIAL_NUMBERS and PJM_UNIT_NUMBERS and carries the change delta directly on the link itself. It is not a pure hub or satellite; its composite key contains descriptive change attributes rather than a single surrogate identifier.

Key Information Stored

The documented schema contains 10 columns. The most significant are:

  • INVENTORY_ITEM_ID — identifies the specific serialized item (from MTL_SYSTEM_ITEMS_B). Part of the composite primary key.
  • SERIAL_NUMBER — the serial number of the affected material, joined back to MTL_SERIAL_NUMBERS. Part of the composite primary key.
  • ORGANIZATION_ID — the inventory organization in which the change occurred; scopes the record to a specific operating unit/warehouse.
  • OLD_UNIT_NUMBER — the unit number the serial was previously associated with. Part of the primary key and references PJM_UNIT_NUMBERS.
  • NEW_UNIT_NUMBER — the unit number the serial is now associated with. Part of the primary key and references PJM_UNIT_NUMBERS.
  • CREATION_DATE, CREATED_BY — the WHO/WHEN audit of the original change event.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit and session columns.

The surrogate primary key is documented as PJM_UNIT_SERIAL_HISTORY_PK, implemented over (INVENTORY_ITEM_ID, SERIAL_NUMBER, OLD_UNIT_NUMBER, NEW_UNIT_NUMBER). A unique index, PJM_UNIT_SERIAL_HISTORY_U1, mirrors the same four-column business key, confirming that a given serial may transition from any old unit to any new unit only once.

Common Use Cases and Queries

The primary use case is traceability reporting: determining which unit number a serialized component was attached to at a point in time. A typical query correlates the history to the current serial record and the current unit master:

  • Reverse trace: given a SERIAL_NUMBER, list all OLD to NEW unit transitions ordered by CREATION_DATE to reconstruct configuration lineage.
  • Forward trace: given a unit number, identify every serial that has ever been assigned to it via OLD_UNIT_NUMBER or NEW_UNIT_NUMBER.
  • Audit reconciliation: compare NEW_UNIT_NUMBER against the current PJM_UNIT_NUMBERS assignment in MTL_SERIAL_NUMBERS to detect drift.
  • Quality and warranty investigations: correlate change events with shipment, receipt, or NCR timestamps.

A representative query joining back to the serial master is: SELECT h.serial_number, h.old_unit_number, h.new_unit_number, h.creation_date FROM pjm_unit_serial_history h WHERE h.inventory_item_id = :item AND h.serial_number = :serial ORDER BY h.creation_date;

Related Objects

  • MTL_SERIAL_NUMBERS — joined on SERIAL_NUMBER + INVENTORY_ITEM_ID + ORGANIZATION_ID; provides the live serial master record.
  • PJM_UNIT_NUMBERS — referenced twice via OLD_UNIT_NUMBER and NEW_UNIT_NUMBER; the unit master carrying project and configuration attributes.
  • MTL_SYSTEM_ITEMS_B — resolves INVENTORY_ITEM_ID to item description and attributes.
  • PJM_UNIT_SERIAL_NUMBERS (current association table) — the live counterpart whose changes are chronicled here.
  • PJM_PROJECTS / PJM_UNIT_EFFECTIVITY — provide project and effectivity context for unit numbers in reporting joins.
  • MTL_MATERIAL_TRANSACTIONS — commonly joined for the transaction that triggered the unit reassignment.