VIEW: APPS.AHL_SERIAL_NUMBER_CHANGE_V

Source

View Text - Preformatted

SELECT csii.instance_id, csii.instance_number, csii.mfg_serial_number_flag, csh.transaction_id, csh.old_serial_number, csh.new_serial_number, csatvalh.old_attribute_value, fndoldat.meaning old_attribute_mean, csatvalh.new_attribute_value new_attribute_value, fndnewat.meaning new_attribute_mean, csh.last_update_date, (SELECT concatenated_segments FROM mtl_system_items_kfv WHERE inventory_item_id = csh.old_inventory_item_id AND organization_id = csii.last_vld_organization_id ) old_item_number, (SELECT concatenated_segments FROM mtl_system_items_kfv WHERE inventory_item_id = csh.new_inventory_item_id AND organization_id = csii.last_vld_organization_id ) NEW_ITEM_NUMBER , DECODE (source_line_ref, 'AHL_PRD_WO', 'Workorder', 'AHL_OSP_ORDER_LINES', 'Osp Order' ) object_type, DECODE (source_line_ref, 'AHL_PRD_WO', (SELECT workorder_name FROM ahl_workorders WHERE workorder_id = source_line_ref_id ) , 'AHL_OSP_ORDER_LINES', (SELECT osp.osp_order_number FROM ahl_osp_orders_b osp, ahl_osp_order_lines ospl WHERE ospl.osp_order_line_id = source_line_ref_id AND osp.osp_order_id = ospl.osp_order_id ) ) object_num , csh.old_inventory_item_id, csh.new_inventory_item_id FROM csi_item_instances csii , csi_item_instances_h csh , csi_iea_values csatval , csi_iea_values_h csatvalh , fnd_lookup_values_vl fndnewat, csi_transactions cst , fnd_lookup_values_vl fndoldat, csi_txn_types cstrntyp WHERE csatvalh.transaction_id(+) = csh.transaction_id AND csii.instance_id = csatval.instance_id(+) AND csii.instance_id = csh.instance_id AND csh.transaction_id = cst.transaction_id AND csatvalh.new_attribute_value = fndnewat.lookup_code(+) AND fndnewat.lookup_type(+) = 'AHL_SERIALNUMBER_TAG' AND csatvalh.old_attribute_value = fndoldat.lookup_code(+) AND fndoldat.lookup_type(+) = 'AHL_SERIALNUMBER_TAG' AND cst.transaction_type_id = cstrntyp.transaction_type_id AND cstrntyp.source_transaction_type = 'ITEM_SERIAL_CHANGE'

View Text - HTML Formatted

SELECT CSII.INSTANCE_ID
, CSII.INSTANCE_NUMBER
, CSII.MFG_SERIAL_NUMBER_FLAG
, CSH.TRANSACTION_ID
, CSH.OLD_SERIAL_NUMBER
, CSH.NEW_SERIAL_NUMBER
, CSATVALH.OLD_ATTRIBUTE_VALUE
, FNDOLDAT.MEANING OLD_ATTRIBUTE_MEAN
, CSATVALH.NEW_ATTRIBUTE_VALUE NEW_ATTRIBUTE_VALUE
, FNDNEWAT.MEANING NEW_ATTRIBUTE_MEAN
, CSH.LAST_UPDATE_DATE
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = CSH.OLD_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = CSII.LAST_VLD_ORGANIZATION_ID ) OLD_ITEM_NUMBER
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = CSH.NEW_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = CSII.LAST_VLD_ORGANIZATION_ID ) NEW_ITEM_NUMBER
, DECODE (SOURCE_LINE_REF
, 'AHL_PRD_WO'
, 'WORKORDER'
, 'AHL_OSP_ORDER_LINES'
, 'OSP ORDER' ) OBJECT_TYPE
, DECODE (SOURCE_LINE_REF
, 'AHL_PRD_WO'
, (SELECT WORKORDER_NAME
FROM AHL_WORKORDERS
WHERE WORKORDER_ID = SOURCE_LINE_REF_ID )
, 'AHL_OSP_ORDER_LINES'
, (SELECT OSP.OSP_ORDER_NUMBER
FROM AHL_OSP_ORDERS_B OSP
, AHL_OSP_ORDER_LINES OSPL
WHERE OSPL.OSP_ORDER_LINE_ID = SOURCE_LINE_REF_ID
AND OSP.OSP_ORDER_ID = OSPL.OSP_ORDER_ID ) ) OBJECT_NUM
, CSH.OLD_INVENTORY_ITEM_ID
, CSH.NEW_INVENTORY_ITEM_ID
FROM CSI_ITEM_INSTANCES CSII
, CSI_ITEM_INSTANCES_H CSH
, CSI_IEA_VALUES CSATVAL
, CSI_IEA_VALUES_H CSATVALH
, FND_LOOKUP_VALUES_VL FNDNEWAT
, CSI_TRANSACTIONS CST
, FND_LOOKUP_VALUES_VL FNDOLDAT
, CSI_TXN_TYPES CSTRNTYP
WHERE CSATVALH.TRANSACTION_ID(+) = CSH.TRANSACTION_ID
AND CSII.INSTANCE_ID = CSATVAL.INSTANCE_ID(+)
AND CSII.INSTANCE_ID = CSH.INSTANCE_ID
AND CSH.TRANSACTION_ID = CST.TRANSACTION_ID
AND CSATVALH.NEW_ATTRIBUTE_VALUE = FNDNEWAT.LOOKUP_CODE(+)
AND FNDNEWAT.LOOKUP_TYPE(+) = 'AHL_SERIALNUMBER_TAG'
AND CSATVALH.OLD_ATTRIBUTE_VALUE = FNDOLDAT.LOOKUP_CODE(+)
AND FNDOLDAT.LOOKUP_TYPE(+) = 'AHL_SERIALNUMBER_TAG'
AND CST.TRANSACTION_TYPE_ID = CSTRNTYP.TRANSACTION_TYPE_ID
AND CSTRNTYP.SOURCE_TRANSACTION_TYPE = 'ITEM_SERIAL_CHANGE'