View: AHL_UNIT_INSTALLED_DETAILS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve Install Base item instance details.
Implementation/DBA Data:	APPS.AHL_UNIT_INSTALLED_DETAILS_V

View Text

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
, KFV.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
FROM CSI_ITEM_INSTANCES CSI
, MTL_SYSTEM_ITEMS_KFV KFV
, 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 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(+)

Columns