ECC Enterprise Asset Management, Assets (Maintenance)

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: eam-asset
Query Procedure: EAM_ECC_ASSETS_UTIL_PVT.GET_ECC_ASSET_DATA_LOAD_INFO
Security Procedure: eam_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
select * from (SELECT /*+ leading (eccassets,msitl) */ eccassets.ECC_SPEC_ID ,dfv.*,
eccassets.ECC_LAST_UPDATE_DATE,
eccassets.ASSET_ORGANIZATION_CODE ,
eccassets.ORGANIZATION_ID  ,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='EAM_ITEM_TYPE' and LANGUAGE=eccassets.language and
LOOKUP_CODE=eccassets.ASSET_TYPE_ID) as ASSET_TYPE   ,
eccassets.ASSET_TYPE_ID ,
eccassets.ASSET_NUMBER ,
eccassets.ASSET_DESCRIPTION,
eccassets.ASSET_ID ,
eccassets.SERIAL_NUMBER,
eccassets.ASSET_GROUP,
msitl.description as ASSET_GROUP_DESCRIPTION,
eccassets.ASSET_CATEGORY ,
eccassets.ASSET_CATEGORY_DESCRIPTION  ,
eccassets.ASSET_OWNING_DEPT ,
eccassets.ASSET_AREA  ,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='MTL_EAM_ASSET_CRITICALITY' and
LANGUAGE= msitl.language and LOOKUP_CODE=eccassets.ASSET_CRITICALITY) as ASSET_CRITICALITY,
eccassets.WARRANTY_FLAG  ,
eccassets.WARRANTY_EXPIRATION_DATE  ,
eccassets.ASSET_STATUS ,
eccassets.ASSET_ACTIVE_FLAG  ,
eccassets.ASSET_MAINTAINABLE ,
eccassets.WIP_ACCOUNTING_CLASS ,
eccassets.LINEAR_ASSET_FLAG ,
eccassets.CHECKED_OUT_FLAG ,
eccassets.PLANNED_WORK ,
eccassets.PLANNED_WORK_DATE ,
eccassets.ASSET_SHUTDOWN  ,
eccassets.PARENT_ASSET_NUMBER ,
eccassets.PARENT_ASSET_DESCRIPTION ,
eccassets.SUBINVENTORY_CODE ,
eccassets.LOCATOR ,
eccassets.ADDRESS,
msitl.language LANGUAGE ,
eccassets.WIP_ENTITY_NAME  ,
eccassets.WIP_ENTITY_ID ,
eccassets.WO_ORGANIZATION_ID,
eccassets.WO_ORGANIZATION_CODE ,
eccassets.WO_DESCRIPTION ,
eccassets.WO_SYSTEM_STATUS_ID ,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='WIP_JOB_STATUS' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.WO_SYSTEM_STATUS_ID) as WO_STATUS ,
eccassets.WO_PENDING_FLAG   ,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='WIP_EAM_WORK_ORDER_TYPE' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.WO_TYPE_DISP) as WO_TYPE_DISP ,
eccassets.WO_DEPARTMENT ,
eccassets.WO_FIRM_FLAG ,
eccassets.ACTIVITY  ,
eccassets.ACTIVITY_ID ,
eccassets.ACTIVITY_SOURCE_MEANING ,
eccassets.ACTIVITY_TYPE_DISP ,
eccassets.ACTIVITY_CAUSE_DISP,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='WIP_EAM_ACTIVITY_PRIORITY' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.PRIORITY_DISP) as PRIORITY_DISP ,
eccassets.ACTIVITY_OWNINING_DEPT ,
eccassets.WO_TAGOUT_REQUIRED ,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='EAM_WARRANTY_STATUS' and LANGUAGE=msitl.language
and LOOKUP_CODE=eccassets.WO_WARRANTY_STATUS) as WO_WARRANTY_STATUS ,
eccassets.WO_ACTUAL_START_DATE ,
eccassets.WO_ACTUAL_COMPLETION_DATE ,
eccassets.WO_SCHEDULED_START_DATE,
eccassets.WO_SCHEDULED_COMPLETION_DATE  ,
eccassets.FAILURE_CODE   ,
eccassets.FAILURE_DESC  ,
eccassets.CAUSE_CODE  ,
eccassets.CAUSE_DESC  ,
eccassets.RESOLUTION_CODE ,
eccassets.RESOLUTION_DESC ,
eccassets.FAILURE_DATE ,
eccassets.FAILURE_COMMENTS  ,
eccassets.DAYS_BETWEEN_FAILURE ,
eccassets.TIME_TO_REPAIR  ,
eccassets.MTBF  ,
eccassets.MTTR ,
eccassets.READING_BETWEEN_FAILURES ,
eccassets.METER  ,
eccassets.UOM,
eccassets.PROJECT_NAME ,
eccassets.TASK_NAME  ,
eccassets.TOTAL_MATERIAL_COST ,
eccassets.TOTAL_LABOR_COST   ,
eccassets.TOTAL_EQUIPMENT_COST ,
eccassets.ACTUAL_TOTAL_COST  ,
eccassets.ESTIMATED_TOTAL_COST ,
eccassets.ALERT_FLAG ,
eccassets.ALERT_COUNT   ,
eccassets.ALERT_TYPE  ,
eccassets.ALERT_NAME ,
eccassets.ALERT_DETAILS,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='BOM_EAM_SHUTDOWN_TYPE' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.WO_SHUTDOWN_TYPE_CODE) as WO_SHUTDOWN_TYPE_CODE,
eccassets.WO_SHUTDOWN_TYPE  ,
eccassets.WO_SHUTDOWN_START_DATE  ,
eccassets.WO_SHUTDOWN_END_DATE,
eccassets.WO_SHUTDOWN_HOURS,
eccassets.WO_SCHEDULE_DELAY  ,
eccassets.PM_SCHEDULE_ID  ,
eccassets.PM_WORK_ORDER_FLAG   ,
eccassets.PM_SCHEDULE_NAME  ,
eccassets.WO_COST_PERIOD_MONTH ,
eccassets.WO_COST_PERIOD_YEAR ,
eccassets.WO_COST_PERIOD ,
eccassets.WO_COST_PERIOD_START_DATE ,
eccassets.PM_WO_ACTUAL_TOTAL_COST ,
eccassets.MTBF_HRS,
eccassets.ASSET_CATEGORY_ID,
eccassets.ASSET_GROUP_ID,
eccassets.PM_SCHEDULE_DELAY,
eccassets.WO_COMPLETED_ON_TIME,
eccassets.PM_WO_COMPLETED_ON_TIME,
eccassets.PM_WORK_ORDER,
eccassets.FA_ASSET_CATEGORY,
eccassets.FA_ASSET_NUMBER,
eccassets.PN_LOCATION_ID,
eccassets.PN_LOCATION_NAME,
eccassets.PN_LOCATION_CODE,
eccassets.OPEN_WORK_ORDERS  ,
eccassets.OPEN_WORK_REQUESTS ,
eccassets.ACTIVITY_START_DATE ,
eccassets.ACTIVITY_END_DATE  ,
eccassets.ACTIVITY_LAST_SVC_START_DATE   ,
eccassets.ACTIVITY_LAST_SVC_END_DATE ,
eccassets.ACTIVITY_NEXT_SVC_START_DATE,
eccassets.ACTIVITY_NEXT_SVC_END_DATE,
eccassets.IS_ASSET_COST,
eccassets.IS_SHUTDOWN_REQUIRED,
eccassets.IS_MTBF,
eccassets.ACTUAL_ASSET_DOWNTIME_HRS,
eccassets.COST_CATEGORY,
eccassets.PM_ACTIVITY_LAST_SVC_DATE,
eccassets.OPS_DEVIATED,
eccassets.UPDATE_BOM_ROUTING_LINK,
eccassets.CREATE_ACTIVITY_LINK,
eccassets.ASSET_ATTRIBUTES,
eccassets.WO_SCHEDULE_DEVIATION,
eccassets.ACTUAL_ASSET_COST,
eccassets.WO_SORT_ORDER,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='EAM_ECC_LOOKUPS' and LANGUAGE=msitl.language and
LOOKUP_CODE=eccassets.PM_WO_FLAG) as  PM_WO_FLAG   ,
eccassets.CURRENCY,
eccassets.MAINTENANCE_OBJECT_ID,
eccassets.IS_MAP_ENABLED ,
'Create_Activity_48_blue' as CREATE_WO_LINK,
'Create_WorkRequest_48_blue' as CREATE_WR_LINK FROM EAM_ECC_ASSETS_TRACKER eccassets, mtl_system_items_tl msitl
				       , (select ROW_ID "'INSTANCE_ROW_ID'",CONTEXT_VALUE "'INSTANCE_CONTEXT_VALUE'",LAST_MAINTENANCE_DONE_BY_NAME_ "'INSTANCE_LAST_MAINTENANCE_DONE_BY_NAME_'",CONCATENATED_SEGMENTS "'INSTANCE_CONCATENATED_SEGMENTS'" from CSI_ITEM_INSTANCES_DFV) dfv    WHERE eccassets.asset_rowid = dfv."'INSTANCE_ROW_ID'"(+)   AND eccassets.asset_group_id =  msitl.inventory_item_id(+)
      					 AND eccassets.organization_id = msitl.organization_id(+) and eccassets.asset_active_flag ='Y'
					 AND msitl.language in ('US') )PIVOT (max(ASSET_TYPE) as  ASSET_TYPE,
				   	max(ASSET_GROUP_DESCRIPTION) as  ASSET_GROUP_DESCRIPTION,
					max(ASSET_CRITICALITY) as  ASSET_CRITICALITY,
					max(WO_STATUS) as  WO_STATUS,
					max(WO_TYPE_DISP) as WO_TYPE_DISP,
					max(PRIORITY_DISP) as PRIORITY_DISP ,
					max(WO_SHUTDOWN_TYPE_CODE) as WO_SHUTDOWN_TYPE_CODE ,
					max(WO_WARRANTY_STATUS) as WO_WARRANTY_STATUS ,
          				max(PM_WO_FLAG) as PM_WO_FLAG for LANGUAGE in ('US' "US"))
          				ORDER BY PLANNED_WORK_DATE asc,PM_ACTIVITY_LAST_SVC_DATE asc 
) x
where
2=2