ECC Enterprise Asset Management, Work Order Assets (Maintenance)

➜ Back to Library
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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,
CASE
	WHEN eccassets.IS_ASSET_COST is not null
	THEN (
                SELECT  lookup_value
                FROM    EAM_ECC_ASSETS_TL_STAGING
                WHERE   lookup_type = 'EAM_ECC_ASSET_GENERIC'
                AND     language = msitl.language
                AND     lookup_code = '10'
                 )
	END AS   IS_ASSET_COST,
CASE
	WHEN eccassets.IS_ASSET_COST is not null
	THEN 'Assets Actual Costs'
	END AS  IS_ASSET_COST_CODE ,
(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,
CASE
	WHEN eccassets.IS_SHUTDOWN_REQUIRED is not null
	THEN (
                SELECT  lookup_value
                FROM    EAM_ECC_ASSETS_TL_STAGING
                WHERE   lookup_type = 'EAM_ECC_ASSET_GENERIC'
                AND     language = msitl.language
                AND     lookup_code = '20'
                 )
	END AS   IS_SHUTDOWN_REQUIRED,
CASE
	WHEN eccassets.IS_SHUTDOWN_REQUIRED is not null
	THEN 'Actual Downtime'
			    END AS  IS_SHTDWN_REQD_CODE ,
CASE
	WHEN eccassets.IS_MTBF is not null
	THEN (
                SELECT  lookup_value
                FROM    EAM_ECC_ASSETS_TL_STAGING
                WHERE   lookup_type = 'EAM_ECC_ASSET_GENERIC'
                AND     language = msitl.language
                AND     lookup_code = '30'
                 )
	END AS   IS_MTBF,
CASE
	WHEN eccassets.IS_MTBF  is not null
	THEN 'MTBF Days'
	END AS  IS_MTBF_CODE ,
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,
					max(IS_ASSET_COST) as  IS_ASSET_COST,
					max(IS_MTBF) as IS_MTBF,
					max(IS_SHUTDOWN_REQUIRED) as IS_SHUTDOWN_REQUIRED	for LANGUAGE in ('US' "US"))
          				ORDER BY PLANNED_WORK_DATE asc,PM_ACTIVITY_LAST_SVC_DATE asc 
) x
where
2=2
Download
 
Related Blitz Reports
ECC Enterprise Asset Management, Assets (Maintenance) ECC Enterprise Asset Management, Work Orders (Maintenance), SQL1 ECC Enterprise Asset Management, Work Order Operations (Maintenance) ECC Enterprise Asset Management, Stores Materials (Maintenance), SQL1 ECC Enterprise Asset Management, Work Requests (Maintenance) ECC Enterprise Asset Management, Stores Work Order (Maintenance), SQL1 ECC Oracle Asset Tracking, Oracle Asset Tracking: Assets ECC Advanced Pricing, Pricing Qualifiers ECC Enterprise Asset Management, Stores Work Order (Maintenance), SQL3
Blitz Report™

Blitz Report™ provides multiple benefits: