Search Results old_item_number

Overview

The view APPS.AHL_SERIAL_NUMBER_CHANGE_V presents a consolidated audit trail of serial number changes performed on item instances within Oracle E-Business Suite. It joins the current instance record, the historical instance record, and the associated transaction to expose both the old and new serial numbers together with the old and new item numbers, attribute values, and the originating source object. In Oracle EBS 12.1.1 and 12.2.2, serial number tracking is central to the Oracle Complex Maintenance, Repair, and Overhaul (CMRO) and Depot Repair flows, where a component may legitimately change its serial number during refurbishment, reconfiguration, or re-identification. The view is the reporting and integration surface for that event class, exposing the relationship between the affected instance and the work order or OSP (outside processing) order line that drove the change. It is documented in the ETRM as VIEW: APPS.AHL_SERIAL_NUMBER_CHANGE_V and is frequently referenced in the context of the AHL_SERIALNUMBER_TAG lookup type, which classifies the attribute values carried on the change record.

Underlying Base Objects

The view is defined over the following documented base objects: AHL_OSP_ORDERS_B, AHL_OSP_ORDER_LINES, AHL_WORKORDERS, CSI_IEA_VALUES, CSI_IEA_VALUES_H, CSI_ITEM_INSTANCES, CSI_ITEM_INSTANCES_H, CSI_TRANSACTIONS, CSI_TXN_TYPES, FND_LOOKUP_VALUES_VL, and the key flexfield view MTL_SYSTEM_ITEMS_KFV. The core of the view is the pairing of CSI_ITEM_INSTANCES (current instance state) with CSI_ITEM_INSTANCES_H (the historical/transactional image that holds the old and new serial numbers). Attribute-level detail (old and new attribute values) is supplied by CSI_IEA_VALUES and CSI_IEA_VALUES_H. The transaction context is anchored by CSI_TRANSACTIONS and CSI_TXN_TYPES, with the constraint that SOURCE_TRANSACTION_TYPE = 'ITEM_SERIAL_CHANGE'. Lookup meanings for the old and new attribute values are resolved against FND_LOOKUP_VALUES_VL using the AHL_SERIALNUMBER_TAG lookup type. The originating object — either a work order or an OSP order — is resolved conditionally via DECODE against AHL_WORKORDERS or AHL_OSP_ORDERS_B joined to AHL_OSP_ORDER_LINES. Item numbers are fetched from MTL_SYSTEM_ITEMS_KFV at the instance's LAST_VLD_ORGANIZATION_ID.

Key Columns

• INSTANCE_ID / INSTANCE_NUMBER — The item instance affected by the serial number change, sourced from CSI_ITEM_INSTANCES.
• MFG_SERIAL_NUMBER_FLAG — Indicates whether the serial number is manufacturer-assigned.
• TRANSACTION_ID — The CSI transaction that recorded the change, linking the record to the transaction history.
• OLD_SERIAL_NUMBER / NEW_SERIAL_NUMBER — The serial number before and after the change, from CSI_ITEM_INSTANCES_H.
• OLD_ATTRIBUTE_VALUE / NEW_ATTRIBUTE_VALUE — The attribute values before and after the change, from CSI_IEA_VALUES_H.
• OLD_ATTRIBUTE_MEAN / NEW_ATTRIBUTE_MEAN — Decoded meanings of the attribute values via the AHL_SERIALNUMBER_TAG lookup.
• OLD_ITEM_NUMBER / NEW_ITEM_NUMBER — Concatenated item segments for the old and new inventory items at the last valid organization.
• OBJECT_TYPE / OBJECT_NUM — The source object, decoded as either 'Workorder' or 'Osp Order', with its corresponding work order name or OSP order number.
• OLD_INVENTORY_ITEM_ID / NEW_INVENTORY_ITEM_ID — Surrogate keys for the old and new items.
• LAST_UPDATE_DATE — Timestamp of the change record, from the historical instance row.

Common Use Cases and Queries

The view is typically used to audit serial number re-identification, to reconcile instaled base configuration against manufacturing records, and to feed downstream reporting or integration into quality and warranty systems. A representative query retrieves the full change history for an instance:

SELECT instance_number,
       old_serial_number,
       new_serial_number,
       old_attribute_mean,
       new_attribute_mean,
       object_type,
       object_num,
       last_update_date
FROM   apps.ahl_serial_number_change_v
WHERE  instance_id = :p_instance_id
ORDER BY last_update_date DESC;

A second common pattern isolates all changes driven by work orders within a date window, which supports maintenance and depot repair analysis:

SELECT instance_number,
       old_serial_number,
       new_serial_number,
       object_num,
       last_update_date
FROM   apps.ahl_serial_number_change_v
WHERE  object_type = 'Workorder'
AND    last_update_date BETWEEN :p_from_date AND :p_to_date;

Because the view resolves item numbers and lookup meanings inline, it is suitable for direct use in BI Publisher reports, OAF/ADF regions, and outbound interfaces without additional joins to MTL_SYSTEM_ITEMS or FND_LOOKUPS.