View: MTL_EAM_ASSET_ACTIVITIES_V

Product:	INV - Inventory
Description:	View for Asset Activities
Implementation/DBA Data:	APPS.MTL_EAM_ASSET_ACTIVITIES_V

View Text

SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MSI.ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, MSI.CONCATENATED_SEGMENTS ACTIVITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, CII.INSTANCE_NUMBER
, CII.INVENTORY_ITEM_ID
, CII.SERIAL_NUMBER
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, FL2.MEANING ACTIVITY_CAUSE
, EOMD.ACTIVITY_CAUSE_CODE
, FL3.MEANING ACTIVITY_TYPE
, EOMD.ACTIVITY_TYPE_CODE
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, EOMD.OWNING_DEPARTMENT_ID
, EOMD.TAGGING_REQUIRED_FLAG
, FL4.MEANING SHUTDOWN_TYPE
, EOMD.SHUTDOWN_TYPE_CODE
, EOMD.ACCOUNTING_CLASS_CODE
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, FL5.MEANING ACTIVITY_SOURCE
, EOMD.ACTIVITY_SOURCE_CODE
, EAA.LAST_SERVICE_START_DATE
, EAA.LAST_SERVICE_END_DATE
, AG.CONCATENATED_SEGMENTS ASSET_REBUILD_GROUP
, AG.EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_EAM_ASSET_ACTIVITIES EAA
, EAM_ORG_MAINT_DEFAULTS EOMD
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS FL1
, MFG_LOOKUPS FL2
, MFG_LOOKUPS FL3
, MFG_LOOKUPS FL4
, MFG_LOOKUPS FL5
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV AG
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND MSI.ORGANIZATION_ID = EOMD.ORGANIZATION_ID
AND EAA.ACTIVITY_ASSOCIATION_ID = EOMD.OBJECT_ID
AND EOMD.OBJECT_TYPE = 60
AND EAA.MAINTENANCE_OBJECT_TYPE = 3
AND EAA.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND EOMD.ACTIVITY_CAUSE_CODE = FL2.LOOKUP_CODE(+)
AND FL2.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND EOMD.ACTIVITY_TYPE_CODE = FL3.LOOKUP_CODE(+)
AND FL3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND EOMD.SHUTDOWN_TYPE_CODE = FL4.LOOKUP_CODE(+)
AND FL4.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND EOMD.ACTIVITY_SOURCE_CODE = FL5.LOOKUP_CODE(+)
AND FL5.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND EOMD.OWNING_DEPARTMENT_ID = BD.DEPARTMENT_ID(+)
AND CII.INVENTORY_ITEM_ID = AG.INVENTORY_ITEM_ID
AND CII.LAST_VLD_ORGANIZATION_ID = AG.ORGANIZATION_ID UNION ALL SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MSI.ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, MSI.CONCATENATED_SEGMENTS ACTIVITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, NULL
, EAA.MAINTENANCE_OBJECT_ID INVENTORY_ITEM_ID
, NULL
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, FL2.MEANING ACTIVITY_CAUSE
, EOMD.ACTIVITY_CAUSE_CODE
, FL3.MEANING ACTIVITY_TYPE
, EOMD.ACTIVITY_TYPE_CODE
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, EOMD.OWNING_DEPARTMENT_ID
, EOMD.TAGGING_REQUIRED_FLAG
, FL4.MEANING SHUTDOWN_TYPE
, EOMD.SHUTDOWN_TYPE_CODE
, EOMD.ACCOUNTING_CLASS_CODE
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, FL5.MEANING ACTIVITY_SOURCE
, EOMD.ACTIVITY_SOURCE_CODE
, EAA.LAST_SERVICE_START_DATE
, EAA.LAST_SERVICE_END_DATE
, (SELECT AG.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV AG
WHERE AG.INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1) ASSET_REBUILD_GROUP
, (SELECT EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1) EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_EAM_ASSET_ACTIVITIES EAA
, EAM_ORG_MAINT_DEFAULTS EOMD
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS FL1
, MFG_LOOKUPS FL2
, MFG_LOOKUPS FL3
, MFG_LOOKUPS FL4
, MFG_LOOKUPS FL5
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND MSI.ORGANIZATION_ID = EOMD.ORGANIZATION_ID
AND EAA.ACTIVITY_ASSOCIATION_ID = EOMD.OBJECT_ID
AND EOMD.OBJECT_TYPE = 40
AND EAA.MAINTENANCE_OBJECT_TYPE = 2
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND EOMD.ACTIVITY_CAUSE_CODE = FL2.LOOKUP_CODE(+)
AND FL2.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND EOMD.ACTIVITY_TYPE_CODE = FL3.LOOKUP_CODE(+)
AND FL3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND EOMD.SHUTDOWN_TYPE_CODE = FL4.LOOKUP_CODE(+)
AND FL4.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND EOMD.ACTIVITY_SOURCE_CODE = FL5.LOOKUP_CODE(+)
AND FL5.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND EOMD.OWNING_DEPARTMENT_ID = BD.DEPARTMENT_ID(+) UNION ALL SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MSI.ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, MSI.CONCATENATED_SEGMENTS ACTIVITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, CII.INSTANCE_NUMBER
, CII.INVENTORY_ITEM_ID
, CII.SERIAL_NUMBER
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, NULL ACTIVITY_CAUSE
, NULL
, NULL
, NULL
, NULL OWNING_DEPARTMENT
, NULL
, NULL
, NULL
, NULL
, NULL
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, NULL ACTIVITY_SOURCE
, NULL
, EAA.LAST_SERVICE_START_DATE
, EAA.LAST_SERVICE_END_DATE
, AG.CONCATENATED_SEGMENTS ASSET_REBUILD_GROUP
, AG.EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_EAM_ASSET_ACTIVITIES EAA
, MFG_LOOKUPS FL1
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV AG
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND EAA.MAINTENANCE_OBJECT_TYPE = 3
AND EAA.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND CII.INVENTORY_ITEM_ID = AG.INVENTORY_ITEM_ID
AND CII.LAST_VLD_ORGANIZATION_ID = AG.ORGANIZATION_ID
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID
AND EOMD.OBJECT_ID = EAA.ACTIVITY_ASSOCIATION_ID
AND EOMD.OBJECT_TYPE = 60) UNION ALL SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MSI.ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, MSI.CONCATENATED_SEGMENTS ACTIVITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, NULL
, EAA.MAINTENANCE_OBJECT_ID INVENTORY_ITEM_ID
, NULL
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, NULL ACTIVITY_CAUSE
, NULL
, NULL
, NULL
, NULL OWNING_DEPARTMENT
, NULL
, NULL
, NULL SHUTDOWN_TYPE
, NULL
, NULL
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, NULL ACTIVITY_SOURCE
, NULL
, EAA.LAST_SERVICE_START_DATE
, EAA.LAST_SERVICE_END_DATE
, (SELECT AG.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV AG
WHERE AG.INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1) ASSET_REBUILD_GROUP
, (SELECT EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1) EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_EAM_ASSET_ACTIVITIES EAA
, MFG_LOOKUPS FL1
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND EAA.MAINTENANCE_OBJECT_TYPE = 2
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EOMD.OBJECT_ID = EAA.ACTIVITY_ASSOCIATION_ID
AND EOMD.OBJECT_TYPE = 40)

Columns