ECC Enterprise Asset Management, Assets (Maintenance)

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: eam-asset-details
Query Procedure: EAM_ECC_ASSETS_UTIL_PVT.GET_ECC_ASSET_DETAILS_DL_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,msitl) */ eccassets.ECC_SPEC_ID ,dfv.*,
eccassets.ECC_LAST_UPDATE_DATE,
eccassets.ASSET_ORGANIZATION_CODE ,
eccassets.ORGANIZATION_ID  ,
eccassets.ASSET_NUMBER ,
eccassets.ASSET_DESCRIPTION,
eccassets.ASSET_CATEGORY ,
eccassets.ASSET_OWNING_DEPT ,
eccassets.ASSET_GROUP,
eccassets.ASSET_GROUP_ID,
msitl.description as ASSET_GROUP_DESCRIPTION,
(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.IS_ASSET_COST,
eccassets.IS_SHUTDOWN_REQUIRED,
eccassets.ACTUAL_ASSET_DOWNTIME_HRS,
eccassets.PM_ACTIVITY_LAST_SVC_DATE,
eccassets.PARENT_ASSET_NUMBER ,
eccassets.PARENT_ASSET_DESCRIPTION ,
(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 ,
(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.PLANNED_WORK ,
eccassets.PLANNED_WORK_DATE ,
eccassets.ASSET_ID ,
eccassets.SERIAL_NUMBER,
eccassets.ASSET_TYPE_ID ,
eccassets.MAINTENANCE_OBJECT_ID,
eccassets.ACTUAL_ASSET_COST,
msitl.language LANGUAGE,
'Create_Activity_48_blue' as CREATE_WO_LINK,
'Create_WorkRequest_48_blue' as CREATE_WR_LINK,
eccassets.ASSET_AREA,
eccassets.SUBINVENTORY_CODE ,
eccassets.LOCATOR ,
eccassets.ADDRESS,
eccassets.ASSET_ATTRIBUTES,
eccassets.FA_ASSET_CATEGORY,
eccassets.FA_ASSET_NUMBER,
eccassets.PN_LOCATION_ID,
eccassets.PN_LOCATION_NAME,
eccassets.IS_MTBF,
eccassets.MTBF_HRS,
eccassets.MTBF  ,
eccassets.MTTR ,
eccassets.CURRENCY,
(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   ,
(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,
eccassets.PN_LOCATION_CODE,
eccassets.ASSET_CATEGORY_ID,
eccassets.OPEN_WORK_ORDERS  ,
eccassets.OPEN_WORK_REQUESTS ,
eccassets.ASSET_SHUTDOWN   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 ( select ROWID "ROW_ID",CONTEXT "CONTEXT_VALUE",ATTRIBUTE16 "LAST_MAINTENANCE_DONE_BY_NAME_",ATTRIBUTE16 "CONCATENATED_SEGMENTS" from CSI_ITEM_INSTANCES )) dfv  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 null
					 AND msitl.language in ('US')  AND eccassets.asset_rowid = dfv."'INSTANCE_ROW_ID'"(+) )PIVOT (max(ASSET_TYPE) as  ASSET_TYPE,
				   	max(ASSET_GROUP_DESCRIPTION) as  ASSET_GROUP_DESCRIPTION,
					max(ASSET_CRITICALITY) as  ASSET_CRITICALITY,
					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(PRIORITY_DISP) as PRIORITY_DISP  for LANGUAGE in ('US' "US"))
          				ORDER BY PLANNED_WORK_DATE asc,PM_ACTIVITY_LAST_SVC_DATE asc 
) x
where
2=2