Search Results new_serial_number




Overview
Explore in AI Chat

Overview

The AHL_SERIAL_NUMBER_CHANGE_V view in the APPS schema is a reporting and integration construct belonging to the AHL (Complex Maintenance Repair and Overhaul) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a consolidated, denormalized history of serial number changes associated with maintenance work orders, joining work order context, item instance data, transaction history, and lookup-based attribute translations into a single queryable surface.

The view's principal purpose is to expose the audit trail of serial number assignment and reassignment events — including the transition from an OLD_SERIAL_NUMBER value to a NEW_SERIAL_NUMBER value — alongside the descriptive item, organization, and job context required by users investigating equipment history. It also surfaces serial-number tag attribute values (via lookup type AHL_SERIALNUMBER_TAG), enabling reporting on configuration attributes that qualified a given change. Because it encapsulates multiple joins that would otherwise require deep knowledge of the CSI and AHL schemas, the view is typically consumed by custom reports, OBIEE/BI Publisher extracts, and downstream integrations that need serialization lineage without directly querying the transactional tables.

The view is documented as VALID in the ETRM reference for 12.2.2 and is owned by APPS, meaning it is a seeded, supported object rather than a customer extension.

Underlying Base Objects

The view text is defined over several core objects, either directly or through synonyms. The documented base objects referenced by the view include:

Joins are performed on INVENTORY_ITEM_ID, ORGANIZATION_ID, and INSTANCE_ID, with outer joins ((+)) applied to the lookup and attribute value tables so that serial changes without matching lookup definitions are not suppressed.

Key Columns

  • WORKORDER_ID / JOB_NUMBER — identifies the maintenance work order within which the serial number change was recorded.
  • ORGANIZATION_ID / CONCATENATED_SEGMENTS — the inventory organization and concatenated item identifier for the affected item.
  • INSTANCE_ID / INSTANCE_NUMBER / SERIAL_NUMBER — the item instance and its current serial number.
  • MFG_SERIAL_NUMBER_FLAG — indicates whether the serial number was manufacturer-assigned.
  • TRANSACTION_ID — the CSI transaction that recorded the change.
  • OLD_SERIAL_NUMBER / NEW_SERIAL_NUMBER — the before-and-after serial values central to the view's purpose. The search term new_serial_number maps directly to the NEW_SERIAL_NUMBER column.
  • OLD_ATTRIBUTE_VALUE / NEW_ATTRIBUTE_VALUE — coded serial tag attributes, with OLD_ATTRIBUTE_MEAN and NEW_ATTRIBUTE_MEAN providing the translated lookup meanings.
  • LAST_UPDATE_DATE — timestamp of the change event, used for chronological ordering and incremental refresh.

Common Use Cases and Queries

Typical uses include auditing serial number re-identification, tracing equipment history for a work order, and extracting change events for data warehousing. The following query retrieves recent serial number changes for a specific job:

  • SELECT job_number, instance_number, old_serial_number, new_serial_number,
       old_attribute_mean, new_attribute_mean, last_update_date
  FROM apps.ahl_serial_number_change_v
 WHERE job_number = :p_job_number
 ORDER BY last_update_date DESC;

To search explicitly by the new serial value — the column implied by the search term new_serial_number:

  • SELECT job_number, concatenated_segments, old_serial_number, new_serial_number,
       last_update_date
  FROM apps.ahl_serial_number_change_v
 WHERE new_serial_number = :p_new_serial_number
   AND organization_id = :p_org_id;

For incremental integration, filter on the timestamp column:

  • SELECT workorder_id, instance_id, transaction_id, old_serial_number,
       new_serial_number, last_update_date
  FROM apps.ahl_serial_number_change_v
 WHERE last_update_date >= :p_since_date;

Because the view is a simple join-based construct over transactional history tables, query performance benefits from filtering on ORGANIZATION_ID, INVENTORY_ITEM_ID, or LAST_UPDATE_DATE. Users leveraging the R12.2 online patching architecture should note that the view is defined in the APPS schema and is subject to the editioning model applied to EBS 12.2 objects.