ECC Enterprise Asset Management, Work Order 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
Run ECC Enterprise Asset Management, Work Order Assets (Maintenance) and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
select * from (SELECT /*+ leading (eccassets,ewod,ewsb,ewstl,msitl) use_nl(eccassets,ewod,ewsb,ewstl,msitl) */ eccassets.ECC_SPEC_ID ,
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,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='EAM_YES_NO' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.WARRANTY_FLAG) as WARRANTY_FLAG  ,
eccassets.WARRANTY_EXPIRATION_DATE  ,
eccassets.ASSET_STATUS ,
eccassets.ASSET_ACTIVE_FLAG  ,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='EAM_YES_NO' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.ASSET_MAINTAINABLE) as ASSET_MAINTAINABLE ,
eccassets.WIP_ACCOUNTING_CLASS ,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='EAM_YES_NO' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.LINEAR_ASSET_FLAG) as LINEAR_ASSET_FLAG ,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='EAM_YES_NO' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.CHECKED_OUT_FLAG) as 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 ,
( Decode (ewod.pending_flag, 'Y', Decode (ewsb.seeded_flag, 'Y',
         Nvl (ewstl.user_defined_status, (select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='WIP_JOB_STATUS'
					and LANGUAGE=msitl.language || Substr(To_char(ewsb.status_id), 1, -1) and LOOKUP_CODE=eccassets.wo_status)),
			 ewstl.user_defined_status) || ' - ' || fnd_msg.eam_pending_text,
				Decode (ewsb.seeded_flag, 'Y', Nvl (ewstl.user_defined_status,
				(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='WIP_JOB_STATUS'
				and LANGUAGE=msitl.language || Substr(To_char(ewsb.status_id), 1, -1) and LOOKUP_CODE=eccassets.wo_status)),
				ewstl.user_defined_status) )) 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,
(select LOOKUP_VALUE from EAM_ECC_ASSETS_TL_STAGING where LOOKUP_TYPE='EAM_YES_NO' and
LANGUAGE=msitl.language and LOOKUP_CODE=eccassets.WO_PLANNED) as WO_PLANNED,
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 , eam_wo_statuses_tl ewstl,   eam_work_order_details ewod, eam_wo_statuses_b ewsb,   (SELECT fnd_message.Get_string ('EAM', 'EAM_PENDING_TEXT')
													   eam_pending_text
									  FROM   dual) fnd_msg  WHERE eccassets.asset_group_id =  msitl.inventory_item_id(+)
      					 AND eccassets.organization_id = msitl.organization_id(+) and eccassets.asset_active_flag ='Y'
						 AND eccassets.wip_entity_id is not null
						 AND eccassets.wip_entity_id = ewod.wip_entity_id(+)
						 AND eccassets.organization_id  = ewod.organization_id(+)
						 AND ewod.user_defined_status_id = ewsb.status_id (+)
						 AND ewsb.status_id = ewstl.status_id (+)
						  AND ( msitl.LANGUAGE = ewstl.LANGUAGE
						 OR ewstl.LANGUAGE IS NULL )
					 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(WO_PLANNED) as WO_PLANNED ,
					max(ASSET_MAINTAINABLE) as ASSET_MAINTAINABLE,
					max(LINEAR_ASSET_FLAG) as LINEAR_ASSET_FLAG,
					max(CHECKED_OUT_FLAG) as CHECKED_OUT_FLAG,
				        max(WARRANTY_FLAG) as   WARRANTY_FLAG,
          				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
Blitz Report™