View: MTL_EAM_ASSET_REBUILDS_DTLS_V

Product:	INV - Inventory
Description:	View shows the details of Assets and Rebuilds.
Implementation/DBA Data:	APPS.MTL_EAM_ASSET_REBUILDS_DTLS_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
, MP.ORGANIZATION_CODE
, 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
, PL.BUILDING||PL.FLOOR||PL.OFFICE PN_LOCATION_NAME
, PL.LOCATION_CODE PN_LOCATION_CODE
, 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
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, MSN_PROD.CURRENT_ORGANIZATION_ID PROD_ORGANIZATION_ID
, OOD.ORGANIZATION_CODE PROD_ORGANIZATION_CODE
, OOD.ORGANIZATION_NAME PROD_ORGANIZATION_NAME
, MSN_PROD.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID
, MSN_PROD.SERIAL_NUMBER EQP_SERIAL_NUMBER
, CIA.FA_ASSET_ID
, FA.ASSET_CATEGORY_ID
, FA.ASSET_NUMBER FA_ASSET_NUMBER
, MSN_PARENT.INVENTORY_ITEM_ID PARENT_ITEM_ID
, MSN_PARENT.SERIAL_NUMBER PARENT_SERIAL_NUMBER
, MSN_PARENT.GEN_OBJECT_ID PARENT_OBJECT_ID
, MSN.GEN_OBJECT_ID
, MSN.CURRENT_STATUS
, ML2.MEANING CURRENT_STATUS_MEANING
, CII.INSTANCE_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATED_BY
, CII.CREATION_DATE
, CII.CREATED_BY
, CII.LAST_UPDATE_LOGIN
, CII.REQUEST_ID
, CII.PROGRAM_APPLICATION_ID
, CII.PROGRAM_ID
, CII.PROGRAM_UPDATE_DATE
FROM MTL_PARAMETERS MP
, ORG_ORGANIZATION_DEFINITIONS OOD
, CSI_ITEM_INSTANCES CII
, EAM_ORG_MAINT_DEFAULTS EOMD
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML
, BOM_DEPARTMENTS BD
, PN_LOCATIONS_ALL PL
, MTL_EAM_LOCATIONS EL
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SERIAL_NUMBERS MSN_PROD
, FA_ADDITIONS_B FA
, MTL_OBJECT_GENEALOGY MOG
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS MSN_PARENT
, MFG_LOOKUPS ML2
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 CII.PN_LOCATION_ID = PL.LOCATION_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 OOD.ORGANIZATION_ID(+) = MSN_PROD.CURRENT_ORGANIZATION_ID
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND CIA.FA_ASSET_ID = FA.ASSET_ID(+)
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.GEN_OBJECT_ID = MOG.OBJECT_ID(+)
AND MOG.PARENT_OBJECT_ID = MSN_PARENT.GEN_OBJECT_ID(+)
AND MOG.GENEALOGY_TYPE(+) = 5
AND SYSDATE >= NVL(MOG.START_DATE_ACTIVE(+)
, SYSDATE)
AND SYSDATE <= NVL(MOG.END_DATE_ACTIVE(+)
, SYSDATE)
AND ML2.LOOKUP_TYPE(+) = 'SERIAL_NUM_STATUS'
AND ML2.LOOKUP_CODE(+) = MSN.CURRENT_STATUS 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
, MP.ORGANIZATION_CODE
, 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
, PL.BUILDING||PL.FLOOR||PL.OFFICE PN_LOCATION_NAME
, PL.LOCATION_CODE PN_LOCATION_CODE
, 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
, NULL OWNING_DEPARTMENT
, MSN_PROD.CURRENT_ORGANIZATION_ID PROD_ORGANIZATION_ID
, OOD.ORGANIZATION_CODE PROD_ORGANIZATION_CODE
, OOD.ORGANIZATION_NAME PROD_ORGANIZATION_NAME
, MSN_PROD.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID
, MSN_PROD.SERIAL_NUMBER EQP_SERIAL_NUMBER
, CIA.FA_ASSET_ID
, FA.ASSET_CATEGORY_ID
, FA.ASSET_NUMBER FA_ASSET_NUMBER
, MSN_PARENT.INVENTORY_ITEM_ID PARENT_ITEM_ID
, MSN_PARENT.SERIAL_NUMBER PARENT_SERIAL_NUMBER
, MSN_PARENT.GEN_OBJECT_ID PARENT_OBJECT_ID
, MSN.GEN_OBJECT_ID
, MSN.CURRENT_STATUS
, ML2.MEANING CURRENT_STATUS_MEANING
, CII.INSTANCE_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATED_BY
, CII.CREATION_DATE
, CII.CREATED_BY
, CII.LAST_UPDATE_LOGIN
, CII.REQUEST_ID
, CII.PROGRAM_APPLICATION_ID
, CII.PROGRAM_ID
, CII.PROGRAM_UPDATE_DATE FROM MTL_PARAMETERS MP
, ORG_ORGANIZATION_DEFINITIONS OOD
, CSI_ITEM_INSTANCES CII
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML
, PN_LOCATIONS_ALL PL
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SERIAL_NUMBERS MSN_PROD
, FA_ADDITIONS_B FA
, MTL_OBJECT_GENEALOGY MOG
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS MSN_PARENT
, MFG_LOOKUPS ML2 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.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.EQUIPMENT_GEN_OBJECT_ID = MSN_PROD.GEN_OBJECT_ID(+)
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND CIA.FA_ASSET_ID = FA.ASSET_ID(+)
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.GEN_OBJECT_ID = MOG.OBJECT_ID(+)
AND MOG.PARENT_OBJECT_ID = MSN_PARENT.GEN_OBJECT_ID(+)
AND MOG.GENEALOGY_TYPE(+) = 5
AND SYSDATE >= NVL(MOG.START_DATE_ACTIVE(+)
, SYSDATE)
AND SYSDATE <= NVL(MOG.END_DATE_ACTIVE(+)
, 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)
AND ML2.LOOKUP_TYPE(+) = 'SERIAL_NUM_STATUS'
AND ML2.LOOKUP_CODE(+) = MSN.CURRENT_STATUS
AND OOD.ORGANIZATION_ID(+) = MSN_PROD.CURRENT_ORGANIZATION_ID

Columns