Product: | AHL - Complex Maintenance Repair and Overhaul |
---|---|
Description: | View based on Serial Number change history |
Implementation/DBA Data: | APPS.AHL_SERIAL_NUMBER_CHANGE_V |
SELECT AWOS.WORKORDER_ID
, AWOS. JOB_NUMBER
, AWOS.ORGANIZATION_ID
, CSII.INSTANCE_ID
, CSII.INSTANCE_NUMBER
, MTSI. CONCATENATED_SEGMENTS
, MTSI.INVENTORY_ITEM_ID
, CSII.SERIAL_NUMBER
, CSII. MFG_SERIAL_NUMBER_FLAG
, CSIH. TRANSACTION_ID
, OLD_SERIAL_NUMBER
, NEW_SERIAL_NUMBER
, OLD_ATTRIBUTE_VALUE
, FNDV. MEANING OLD_ATTRIBUTE_MEAN
, NEW_ATTRIBUTE_VALUE
, FNDL. MEANING NEW_ATTRIBUTE_MEAN
, CSIH. LAST_UPDATE_DATE
FROM AHL_WORKORDERS_V AWOS
, CSI_ITEM_INSTANCES CSII
, MTL_SYSTEM_ITEMS_VL MTSI
, CSI_ITEM_INSTANCES_H CSIH
, CSI_IEA_VALUES_H CSIV
, FND_LOOKUP_VALUES_VL FNDV
, FND_LOOKUP_VALUES_VL FNDL
WHERE AWOS.INVENTORY_ITEM_ID = CSII.INVENTORY_ITEM_ID
AND AWOS.ORGANIZATION_ID = MTSI.ORGANIZATION_ID
AND CSII.INVENTORY_ITEM_ID = MTSI.INVENTORY_ITEM_ID
AND AWOS.ITEM_INSTANCE_ID = CSII.INSTANCE_ID
AND CSII.INSTANCE_ID = CSIH.INSTANCE_ID
AND CSIH.TRANSACTION_ID = CSIV.TRANSACTION_ID(+)
AND CSIV.OLD_ATTRIBUTE_VALUE = FNDV.LOOKUP_CODE(+)
AND FNDV.LOOKUP_TYPE(+) = 'AHL_SERIALNUMBER_TAG'
AND CSIV.NEW_ATTRIBUTE_VALUE = FNDL.LOOKUP_CODE(+)
AND FNDL.LOOKUP_TYPE(+) = 'AHL_SERIALNUMBER_TAG'