Search Results pjm_unit_serial_history_pk

Overview

The PJM_UNIT_SERIAL_HISTORY table is a core data object within the Oracle E-Business Suite Project Manufacturing (PJM) module, specifically for versions 12.1.1 and 12.2.2. Its primary function is to serve as an audit trail, logging all historical changes to the association between a serialized inventory item and a project unit number. This tracking is critical in project-centric manufacturing and asset management, where specific serialized components (e.g., engines, major assemblies) are assigned to and transferred between distinct units or assets within a project. The table ensures a complete, immutable record of these assignments for traceability, compliance, and lifecycle management purposes.

Key Information Stored

The table's structure is designed to capture the precise details of each association change. Its composite primary key, PJM_UNIT_SERIAL_HISTORY_PK, uniquely identifies each historical transaction using four columns: INVENTORY_ITEM_ID, SERIAL_NUMBER, OLD_UNIT_NUMBER, and NEW_UNIT_NUMBER. The INVENTORY_ITEM_ID and SERIAL_NUMBER columns identify the specific serialized component involved. The OLD_UNIT_NUMBER and NEW_UNIT_NUMBER columns record the unit numbers from which the serial was disassociated and to which it was newly associated, respectively. This design inherently captures the "before" and "after" state of each change. Additional columns, such as ORGANIZATION_ID, are implied by the foreign key relationships to provide organizational context.

Common Use Cases and Queries

This table is central to audit reporting and troubleshooting serial-unit assignments. A common use case is generating a complete lineage report for a critical serialized component to understand its deployment history across different project units. Another scenario involves reconciling discrepancies between current assignments and historical records. A typical query pattern retrieves the full history for a specific serial number, ordered chronologically to show its progression through various units.

• Serial History Report: SELECT old_unit_number, new_unit_number, [timestamp_column] FROM pjm_unit_serial_history WHERE inventory_item_id = :item_id AND serial_number = :serial AND organization_id = :org_id ORDER BY [timestamp_column];
• Unit Assignment Audit: Identifying all serials ever assigned to a particular unit number by querying where either OLD_UNIT_NUMBER or NEW_UNIT_NUMBER equals the target unit.

Related Objects

The PJM_UNIT_SERIAL_HISTORY table maintains strict referential integrity with key master data tables through documented foreign key constraints. These relationships are essential for data validation and forming accurate joins in reports.

• MTL_SERIAL_NUMBERS: References via INVENTORY_ITEM_ID, SERIAL_NUMBER, and ORGANIZATION_ID. This ensures every history record corresponds to a valid, existing serialized item in inventory.
• PJM_UNIT_NUMBERS (for OLD_UNIT_NUMBER): References via OLD_UNIT_NUMBER. This links to the valid project unit from which the serial was moved.
• PJM_UNIT_NUMBERS (for NEW_UNIT_NUMBER): References via NEW_UNIT_NUMBER. This links to the valid project unit to which the serial was assigned.