VIEW: APPS.AHL_UNIT_INSTALLED_DETAILS_V

Source

View Text - Preformatted

SELECT csi.instance_id csi_item_instance_id, csi.object_version_number csi_object_version, csi.instance_number csi_instance_number, csi.inventory_item_id, csi.last_vld_organization_id inventory_org_id, (select organization_code from mtl_parameters mp where organization_id = csi.last_vld_organization_id) organization_code, csi.unit_of_measure uom_code, csi.quantity, csi.serial_number, csi.mfg_serial_number_flag, csi.inventory_revision revision, csi.lot_number, csi.Install_date, csi.location_id csi_location_id, ahl_util_uc_pkg.getcsi_locationDesc(csi.location_id, csi.location_type_code, csi.inv_organization_id, csi.inv_subinventory_name, csi.inv_locator_id, csi.wip_job_id) Location_description, f.meaning Status, kfv.concatenated_segments Item_Number, mtl.description Item_description, to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date, ciea2.attribute_value serialnum_tag_code, f1.meaning serialnum_tag_meaning, mat.description condition, p. party_type, p.party_id owner_id, p.party_number owner_number, p.party_name owner_name, p.object_version_number csi_party_object_version_num, decode(csi.location_type_code, 'WIP',wip.wip_entity_name, ahl_util_uc_pkg.getcsi_locationCode(csi.location_id, csi.location_type_code)) Owner_Site_Number, csi.wip_job_id wip_entity_id, wip.wip_entity_name, csi.context, csi.attribute1, csi.attribute2, csi.attribute3, csi.attribute4, csi.attribute5, csi.attribute6, csi.attribute7, csi.attribute8, csi.attribute9, csi.attribute10, csi.attribute11, csi.attribute12, csi.attribute13, csi.attribute14, csi.attribute15, csi.attribute16, csi.attribute17, csi.attribute18, csi.attribute19, csi.attribute20, csi.attribute21, csi.attribute22, csi.attribute23, csi.attribute24, csi.attribute25, csi.attribute26, csi.attribute27, csi.attribute28, csi.attribute29, csi.attribute30 from csi_item_instances csi, mtl_system_items_kfv kfv, mtl_system_items_tl mtl, mtl_material_statuses mat, csi_lookups f, csi_inst_party_details_v p, csi_inst_extend_attrib_v ciea1, csi_inst_extend_attrib_v ciea2, fnd_lookups f1, wip_entities wip WHERE csi.inventory_item_id = kfv.inventory_item_id and csi.last_vld_organization_id = kfv.organization_id and kfv.inventory_item_id = mtl.inventory_item_id and kfv.organization_id = mtl.organization_id and mtl.language = userenv('LANG') and csi.INSTANCE_CONDITION_ID = mat.status_id(+) and csi.instance_usage_code = f.lookup_code(+) and f.lookup_type(+) = 'CSI_INSTANCE_USAGE_CODE' and csi.instance_id = p.instance_id(+) and p.relationship_type_code(+) = 'OWNER' and csi.instance_id = ciea1.instance_id(+) and ciea1.attribute_code(+) = 'AHL_MFG_DATE' and ciea1.attribute_level(+) = 'GLOBAL' and csi.instance_id = ciea2.instance_id(+) and ciea2.attribute_code(+) = 'AHL_TEMP_SERIAL_NUM' and ciea2.attribute_level(+) = 'GLOBAL' and f1.lookup_type(+) = 'AHL_SERIALNUMBER_TAG' and f1.lookup_code(+) = ciea2.attribute_value and csi.wip_job_id = wip.wip_entity_id(+)

View Text - HTML Formatted

SELECT CSI.INSTANCE_ID CSI_ITEM_INSTANCE_ID
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION
, CSI.INSTANCE_NUMBER CSI_INSTANCE_NUMBER
, CSI.INVENTORY_ITEM_ID
, CSI.LAST_VLD_ORGANIZATION_ID INVENTORY_ORG_ID
, (SELECT ORGANIZATION_CODE
FROM MTL_PARAMETERS MP
WHERE ORGANIZATION_ID = CSI.LAST_VLD_ORGANIZATION_ID) ORGANIZATION_CODE
, CSI.UNIT_OF_MEASURE UOM_CODE
, CSI.QUANTITY
, CSI.SERIAL_NUMBER
, CSI.MFG_SERIAL_NUMBER_FLAG
, CSI.INVENTORY_REVISION REVISION
, CSI.LOT_NUMBER
, CSI.INSTALL_DATE
, CSI.LOCATION_ID CSI_LOCATION_ID
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONDESC(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE
, CSI.INV_ORGANIZATION_ID
, CSI.INV_SUBINVENTORY_NAME
, CSI.INV_LOCATOR_ID
, CSI.WIP_JOB_ID) LOCATION_DESCRIPTION
, F.MEANING STATUS
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, MTL.DESCRIPTION ITEM_DESCRIPTION
, TO_DATE(CIEA1.ATTRIBUTE_VALUE
, 'DD/MM/YYYY') MFG_DATE
, CIEA2.ATTRIBUTE_VALUE SERIALNUM_TAG_CODE
, F1.MEANING SERIALNUM_TAG_MEANING
, MAT.DESCRIPTION CONDITION
, P. PARTY_TYPE
, P.PARTY_ID OWNER_ID
, P.PARTY_NUMBER OWNER_NUMBER
, P.PARTY_NAME OWNER_NAME
, P.OBJECT_VERSION_NUMBER CSI_PARTY_OBJECT_VERSION_NUM
, DECODE(CSI.LOCATION_TYPE_CODE
, 'WIP'
, WIP.WIP_ENTITY_NAME
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONCODE(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE)) OWNER_SITE_NUMBER
, CSI.WIP_JOB_ID WIP_ENTITY_ID
, WIP.WIP_ENTITY_NAME
, CSI.CONTEXT
, CSI.ATTRIBUTE1
, CSI.ATTRIBUTE2
, CSI.ATTRIBUTE3
, CSI.ATTRIBUTE4
, CSI.ATTRIBUTE5
, CSI.ATTRIBUTE6
, CSI.ATTRIBUTE7
, CSI.ATTRIBUTE8
, CSI.ATTRIBUTE9
, CSI.ATTRIBUTE10
, CSI.ATTRIBUTE11
, CSI.ATTRIBUTE12
, CSI.ATTRIBUTE13
, CSI.ATTRIBUTE14
, CSI.ATTRIBUTE15
, CSI.ATTRIBUTE16
, CSI.ATTRIBUTE17
, CSI.ATTRIBUTE18
, CSI.ATTRIBUTE19
, CSI.ATTRIBUTE20
, CSI.ATTRIBUTE21
, CSI.ATTRIBUTE22
, CSI.ATTRIBUTE23
, CSI.ATTRIBUTE24
, CSI.ATTRIBUTE25
, CSI.ATTRIBUTE26
, CSI.ATTRIBUTE27
, CSI.ATTRIBUTE28
, CSI.ATTRIBUTE29
, CSI.ATTRIBUTE30
FROM CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV KFV
, MTL_SYSTEM_ITEMS_TL MTL
, MTL_MATERIAL_STATUSES MAT
, CSI_LOOKUPS F
, CSI_INST_PARTY_DETAILS_V P
, CSI_INST_EXTEND_ATTRIB_V CIEA1
, CSI_INST_EXTEND_ATTRIB_V CIEA2
, FND_LOOKUPS F1
, WIP_ENTITIES WIP
WHERE CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.LAST_VLD_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND KFV.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND MTL.LANGUAGE = USERENV('LANG')
AND CSI.INSTANCE_CONDITION_ID = MAT.STATUS_ID(+)
AND CSI.INSTANCE_USAGE_CODE = F.LOOKUP_CODE(+)
AND F.LOOKUP_TYPE(+) = 'CSI_INSTANCE_USAGE_CODE'
AND CSI.INSTANCE_ID = P.INSTANCE_ID(+)
AND P.RELATIONSHIP_TYPE_CODE(+) = 'OWNER'
AND CSI.INSTANCE_ID = CIEA1.INSTANCE_ID(+)
AND CIEA1.ATTRIBUTE_CODE(+) = 'AHL_MFG_DATE'
AND CIEA1.ATTRIBUTE_LEVEL(+) = 'GLOBAL'
AND CSI.INSTANCE_ID = CIEA2.INSTANCE_ID(+)
AND CIEA2.ATTRIBUTE_CODE(+) = 'AHL_TEMP_SERIAL_NUM'
AND CIEA2.ATTRIBUTE_LEVEL(+) = 'GLOBAL'
AND F1.LOOKUP_TYPE(+) = 'AHL_SERIALNUMBER_TAG'
AND F1.LOOKUP_CODE(+) = CIEA2.ATTRIBUTE_VALUE
AND CSI.WIP_JOB_ID = WIP.WIP_ENTITY_ID(+)