View: MTL_EAM_ASSET_NUMBERS_V

Product:	INV - Inventory
Description:	View for Asset Numbers
Implementation/DBA Data:	APPS.MTL_EAM_ASSET_NUMBERS_V

View Text

SELECT CII.ROWID ROW_ID
, CII.INVENTORY_ITEM_ID
, CII.SERIAL_NUMBER
, CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT
, CII.INV_SUBINVENTORY_NAME CURRENT_SUBINVENTORY_CODE
, CII.INV_LOCATOR_ID AS CURRENT_LOCATOR_ID
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, CII.CONTEXT
, CII.ATTRIBUTE1
, CII.ATTRIBUTE2
, CII.ATTRIBUTE3
, CII.ATTRIBUTE4
, CII.ATTRIBUTE5
, CII.ATTRIBUTE6
, CII.ATTRIBUTE7
, CII.ATTRIBUTE8
, CII.ATTRIBUTE9
, CII.ATTRIBUTE10
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, CII.ATTRIBUTE16
, CII.ATTRIBUTE17
, CII.ATTRIBUTE18
, CII.ATTRIBUTE19
, CII.ATTRIBUTE20
, CII.ATTRIBUTE21
, CII.ATTRIBUTE22
, CII.ATTRIBUTE23
, CII.ATTRIBUTE24
, CII.ATTRIBUTE25
, CII.ATTRIBUTE26
, CII.ATTRIBUTE27
, CII.ATTRIBUTE28
, CII.ATTRIBUTE29
, CII.ATTRIBUTE30
, MP.MAINT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, CII.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, CII.PN_LOCATION_ID PN_LOCATION_ID
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, ML.MEANING ASSET_CRITICALITY
, EOMD.ACCOUNTING_CLASS_CODE WIP_ACCOUNTING_CLASS_CODE
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, EOMD.AREA_ID
, EL.LOCATION_CODES EAM_LOCATION
, EOMD.OWNING_DEPARTMENT_ID OWNING_DEPARTMENT_ID
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, MSN_PROD.CURRENT_ORGANIZATION_ID PROD_ORGANIZATION_ID
, MSI_PROD.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID
, DECODE(MSI_PROD.EQUIPMENT_TYPE
, NULL
, NULL
, 1
, MSN_PROD.SERIAL_NUMBER
, NULL) EQP_SERIAL_NUMBER
, CIA.FA_ASSET_ID
, CII.SERIAL_NUMBER MAINTAINED_UNIT
, CII.INSTANCE_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, CII.CHECKIN_STATUS
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN
FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, EAM_ORG_MAINT_DEFAULTS EOMD
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML
, BOM_DEPARTMENTS BD
, MTL_EAM_LOCATIONS EL
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SERIAL_NUMBERS MSN_PROD
, MTL_SYSTEM_ITEMS MSI_PROD
WHERE MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND NVL(CII.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND CII.INSTANCE_ID = EOMD.OBJECT_ID (+)
AND EOMD.OBJECT_TYPE = 50
AND (EOMD.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID)
AND CII.ASSET_CRITICALITY_CODE = ML.LOOKUP_CODE(+)
AND ML.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND BD.DEPARTMENT_ID(+) = EOMD.OWNING_DEPARTMENT_ID
AND EOMD.AREA_ID = EL.LOCATION_ID(+)
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.EQUIPMENT_GEN_OBJECT_ID = MSN_PROD.GEN_OBJECT_ID(+)
AND MSN_PROD.CURRENT_ORGANIZATION_ID = MSI_PROD.ORGANIZATION_ID(+)
AND MSN_PROD.INVENTORY_ITEM_ID = MSI_PROD.INVENTORY_ITEM_ID(+)
AND MSI_PROD.EQUIPMENT_TYPE(+) = 1
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND SYSDATE >= NVL(CIA.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(CIA.ACTIVE_END_DATE(+)
, SYSDATE) UNION ALL SELECT CII.ROWID ROW_ID
, CII.INVENTORY_ITEM_ID
, CII.SERIAL_NUMBER
, CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT
, CII.INV_SUBINVENTORY_NAME CURRENT_SUBINVENTORY_CODE
, CII.INV_LOCATOR_ID AS CURRENT_LOCATOR_ID
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, CII.CONTEXT
, CII.ATTRIBUTE1
, CII.ATTRIBUTE2
, CII.ATTRIBUTE3
, CII.ATTRIBUTE4
, CII.ATTRIBUTE5
, CII.ATTRIBUTE6
, CII.ATTRIBUTE7
, CII.ATTRIBUTE8
, CII.ATTRIBUTE9
, CII.ATTRIBUTE10
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, CII.ATTRIBUTE16
, CII.ATTRIBUTE17
, CII.ATTRIBUTE18
, CII.ATTRIBUTE19
, CII.ATTRIBUTE20
, CII.ATTRIBUTE21
, CII.ATTRIBUTE22
, CII.ATTRIBUTE23
, CII.ATTRIBUTE24
, CII.ATTRIBUTE25
, CII.ATTRIBUTE26
, CII.ATTRIBUTE27
, CII.ATTRIBUTE28
, CII.ATTRIBUTE29
, CII.ATTRIBUTE30
, MP.MAINT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, CII.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, CII.PN_LOCATION_ID PN_LOCATION_ID
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, ML.MEANING ASSET_CRITICALITY
, NULL WIP_ACCOUNTING_CLASS_CODE
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, NULL
, NULL EAM_LOCATION
, NULL OWNING_DEPARTMENT_ID
, NULL OWNING_DEPARTMENT
, MSN_PROD.CURRENT_ORGANIZATION_ID PROD_ORGANIZATION_ID
, MSI_PROD.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID
, DECODE(MSI_PROD.EQUIPMENT_TYPE
, NULL
, NULL
, 1
, MSN_PROD.SERIAL_NUMBER
, NULL) EQP_SERIAL_NUMBER
, CIA.FA_ASSET_ID
, CII.SERIAL_NUMBER MAINTAINED_UNIT
, CII.INSTANCE_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, CII.CHECKIN_STATUS
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SERIAL_NUMBERS MSN_PROD
, MTL_SYSTEM_ITEMS MSI_PROD WHERE MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND NVL(CII.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND CII.ASSET_CRITICALITY_CODE = ML.LOOKUP_CODE(+)
AND ML.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.EQUIPMENT_GEN_OBJECT_ID = MSN_PROD.GEN_OBJECT_ID(+)
AND MSN_PROD.CURRENT_ORGANIZATION_ID = MSI_PROD.ORGANIZATION_ID(+)
AND MSN_PROD.INVENTORY_ITEM_ID = MSI_PROD.INVENTORY_ITEM_ID(+)
AND MSI_PROD.EQUIPMENT_TYPE(+) = 1
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND SYSDATE >= NVL(CIA.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(CIA.ACTIVE_END_DATE(+)
, SYSDATE)
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID
AND EOMD.OBJECT_ID = CII.INSTANCE_ID
AND EOMD.OBJECT_TYPE = 50)

Columns