Search Results new_attribute_mean

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:

• AHL_WORKORDERS (synonym) and its reporting counterpart AHL_WORKORDERS_V, supplying WORKORDER_ID, JOB_NUMBER, ORGANIZATION_ID, ITEM_INSTANCE_ID, and INVENTORY_ITEM_ID.
• CSI_ITEM_INSTANCES and CSI_ITEM_INSTANCES_H (synonyms), the current and historical item instance records holding INSTANCE_ID, INSTANCE_NUMBER, SERIAL_NUMBER, and MFG_SERIAL_NUMBER_FLAG.
• CSI_IEA_VALUES_H (and its current counterpart CSI_IEA_VALUES), the historical instance attribute values that provide OLD_ATTRIBUTE_VALUE and NEW_ATTRIBUTE_VALUE.
• CSI_TRANSACTIONS and CSI_TXN_TYPES (synonyms), providing the TRANSACTION_ID that anchors each change record.
• MTL_SYSTEM_ITEMS_KFV / MTL_SYSTEM_ITEMS_VL, supplying CONCATENATED_SEGMENTS, INVENTORY_ITEM_ID, and ORGANIZATION_ID.
• FND_LOOKUP_VALUES_VL, joined twice as FNDV and FNDL to translate old and new attribute codes into OLD_ATTRIBUTE_MEAN and NEW_ATTRIBUTE_MEAN using lookup type AHL_SERIALNUMBER_TAG.
• AHL_OSP_ORDERS_B and AHL_OSP_ORDER_LINES (synonyms), referenced in the documented metadata for work order and outside-processing context.

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.