ECC Enterprise Asset Management, Work Orders (Maintenance), SQL1

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: eam-wo
Query Procedure: EAM_ECC_WO_UTIL_PVT.GET_ECC_WO_DATA_LOAD_INFO
Security Procedure: eam_ecc_datasecurity_pkg_pub.GetFilterAttributeValues 
select
x.*
from
(
select * from (
SELECT /*+ leading(eccwo,ewod,ewsb) use_nl(eccwo,ewod,ewsb,ewstl,msi,msitl) cardinality ( eccwo 1000 ) */ ECC_SPEC_ID ,dfv.*,
	ECC_LAST_UPDATE_DATE,
	eccwo.WIP_ENTITY_ID ,
	eccwo.WIP_ENTITY_NAME ,
	eccwo.ORGANIZATION_ID ,
	eccwo.ORGANIZATION_CODE ,
	eccwo.WO_DESCRIPTION ,
	eccwo.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_wo_tl_staging where LOOKUP_TYPE='WIP_JOB_STATUS'
					and LANGUAGE=msitl.language || Substr(To_char(ewsb.status_id), 1, -1) and LOOKUP_CODE=eccwo.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_wo_tl_staging where LOOKUP_TYPE='WIP_JOB_STATUS'
				and LANGUAGE=msitl.language || Substr(To_char(ewsb.status_id), 1, -1) and LOOKUP_CODE=eccwo.wo_status)),
				ewstl.user_defined_status) )) as WO_STATUS ,
	eccwo.WO_PENDING_FLAG ,
  (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='WIP_EAM_WORK_ORDER_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.work_order_type_disp) as WORK_ORDER_TYPE_DISP,
	eccwo.WO_DEPARTMENT ,
	eccwo.FIRM_FLAG ,
	eccwo.ASSET_GROUP ,
	msitl.description ASSET_GROUP_DESCRIPTION ,
	eccwo.ASSET_NUMBER,
	eccwo.ASSET_DESCRIPTION ,
	eccwo.ASSET_ID ,
	eccwo.ASSET_CATEGORY ,
	(select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='MTL_EAM_ASSET_CRITICALITY' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.asset_criticality) as ASSET_CRITICALITY ,
	eccwo.ASSET_AREA ,
	eccwo.WIP_ACCOUNTING_CLASS ,
	eccwo.ASSET_ORGANIZATION_CODE ,
	eccwo.ASSET_ORGANIZATION_ID ,
  (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_ITEM_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.asset_type_id) as ASSET_TYPE ,
	eccwo.ASSET_TYPE_ID ,
	eccwo.ASSET_OWNING_DEPT ,
	eccwo.ASSET_MAINTAINABLE ,
	eccwo.ASSET_ACTIVE_FLAG ,
	eccwo.ASSET_ACTIVITY ,
  (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='MTL_EAM_ACTIVITY_SOURCE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.activity_source_meaning) as ACTIVITY_SOURCE_MEANING ,
  (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='MTL_EAM_ACTIVITY_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.activity_type_disp) as ACTIVITY_TYPE_DISP,
  (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='MTL_EAM_ACTIVITY_CAUSE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.activity_cause_disp) as ACTIVITY_CAUSE_DISP ,
  (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='WIP_EAM_ACTIVITY_PRIORITY' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.priority_disp) as PRIORITY_DISP ,
  (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='BOM_EAM_SHUTDOWN_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.wo_shutdown_type_code) as WO_SHUTDOWN_TYPE ,
	eccwo.WO_SHUTDOWN_TYPE_CODE ,
	eccwo.WO_SHUTDOWN_START_DATE ,
	eccwo.WO_SHUTDOWN_END_DATE ,
	eccwo.SHUTDOWN_HOURS ,
	eccwo.TAGOUT_REQUIRED ,
	(select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_WARRANTY_STATUS' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.warranty_status) as WARRANTY_STATUS ,
	eccwo.WARRANTY_EXP_DATE ,
	eccwo.OP_SEQUENCE_NUM ,
	eccwo.OP_DESCRIPTION ,
	eccwo.OP_DEPT_CODE ,
	eccwo.OP_SHUTDOWN_TYPE ,
	eccwo.OP_SHUTDOWN_TYPE_CODE ,
	eccwo.OP_COMPLETED ,
	eccwo.RESOURCE_SEQ_NUM ,
	eccwo.RESOURCE_ID ,
	eccwo.RESOURCE_TYPE ,
	eccwo.USAGE_RATE ,
	eccwo.ASSIGNED_UNITS ,
	eccwo.INSTANCE_ID ,
	eccwo.SERIAL_NUMBER ,
	eccwo.INSTANCE_NAME ,
	eccwo.EMPLOYEE_NUMBER ,
	eccwo.WO_ACTUAL_START_DATE ,
	eccwo.WO_ACTUAL_COMPLETION_DATE ,
	eccwo.WO_SCHEDULED_START_DATE ,
	eccwo.WO_SCHEDULED_COMPLETION_DATE ,
	eccwo.WO_SCHEDULE_DELAY ,
	eccwo.WO_SCHEDULE_DELAY_FLAG ,
	eccwo.WO_MONTH ,
	eccwo.WO_YEAR ,
	eccwo.WO_QUARTER ,
	(select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_ECC_LOOKUPS' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.wo_schedule_start_horizon) as WO_SCHEDULE_START_HORIZON ,
	eccwo.OP_START_DATE ,
	eccwo.OP_END_DATE ,
	eccwo.OP_MONTH ,
	eccwo.RESOURCE_START_DATE ,
	eccwo.RESOURCE_END_DATE ,
	eccwo.INSTANCE_START_DATE ,
	eccwo.INSTANCE_END_DATE ,
	eccwo.RESOURCE_CODE ,
	eccwo.RESOURCE_DESCRIPTION ,
	eccwo.OP_LONG_DESC ,
	eccwo.RES_SCHEDULED_FLAG ,
	eccwo.RES_UOM ,
	eccwo.WO_DURATION ,
	eccwo.WO_SCHEDULED_DURATION ,
	eccwo.WO_AGE ,
	eccwo.WO_COMPLETED_ON_TIME ,
	eccwo.WO_COMPLETED_LATE ,
	eccwo.WO_COMPLETED ,
	eccwo.PM_SCHEDULE_ID ,
	eccwo.PM_WORK_ORDER_FLAG ,
	eccwo.PM_SCHEDULE_NAME ,
	eccwo.WO_SCHEDULED ,
	eccwo.MAINTENANCE_OBJECT_ID ,
	eccwo.FAILURE_CODE ,
	eccwo.FAILURE_DESC ,
	eccwo.CAUSE_CODE ,
	eccwo.CAUSE_DESC ,
	eccwo.RESOLUTION_CODE ,
	eccwo.RESOLUTION_DESC ,
	eccwo.FAILURE_DATE ,
	eccwo.FAILURE_COMMENTS ,
	eccwo.LAST_UPDATED_BY ,
	eccwo.PROJECT_NAME ,
	eccwo.TASK_NAME ,
	eccwo.TOTAL_MATERIAL_COST ,
	eccwo.TOTAL_LABOR_COST ,
	eccwo.TOTAL_EQUIPMENT_COST ,
	eccwo.ACTUAL_TOTAL_COST ,
	eccwo.ESTIMATED_TOTAL_COST ,
	eccwo.ESTIMATED_TOTAL_COST_FLAG ,
	eccwo.VARIANCE_TOTAL_COST ,
	eccwo.WO_COST_OVERRUN ,
	eccwo.COST_OVERRUN_FLAG ,
	eccwo.TOP_10_HIGH_COST_WO_FLAG ,
	msitl.language LANGUAGE,
	eccwo.MATERIAL_SHORTAGE_CHECK_DATE ,
	(select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_MATERIAL_SHORTAGE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.material_shortage_flag) as MATERIAL_SHORTAGE_FLAG ,
	eccwo.MATERIAL_SHORTAGE ,
	eccwo.MATERIALS ,
	eccwo.RESOURCES ,
	eccwo.OPERATIONAL_LOG_FLAG ,
	eccwo.CHECKIN_STATUS ,
	eccwo.WARRANTY_ACTIVE ,
	eccwo.UNPLANNED_WO_FLAG ,
	eccwo.RELEASED_WO_FLAG ,
	eccwo.OPEN_WO_FLAG ,
	eccwo.ALERT_FLAG ,
	eccwo.ALERT_COUNT ,
  case when eccwo.alert_flag <> 'NoAlerts' THEN
    substr( nvl((select '|' ||lookup_value from eam_ecc_wo_tl_staging where LOOKUP_TYPE = 'EAM_WO_ECC_ALERT_TYPE' and language = msitl.language and lookup_code = eccwo.failure_alert_code),'' )||
    nvl((select '|' ||lookup_value from eam_ecc_wo_tl_staging where LOOKUP_TYPE = 'EAM_WO_ECC_ALERT_TYPE' and language = msitl.language and lookup_code = eccwo.cost_overrun_alert_code ),'' )||
    nvl((select '|' ||lookup_value from eam_ecc_wo_tl_staging where LOOKUP_TYPE = 'EAM_WO_ECC_ALERT_TYPE' and language = msitl.language and lookup_code = eccwo.schedule_delay_alert_code),'' )||
    nvl((select '|' ||lookup_value from eam_ecc_wo_tl_staging where LOOKUP_TYPE = 'EAM_WO_ECC_ALERT_TYPE' and language = msitl.language and lookup_code = eccwo.WO_SHUTDOWN_ALERT_CODE),'' )||
    nvl((select '|' ||lookup_value from eam_ecc_wo_tl_staging where LOOKUP_TYPE = 'EAM_WO_ECC_ALERT_TYPE' and language = msitl.language and lookup_code = eccwo.material_shortage_ALERT_CODE ),'') ,2)
  END as ALERT_TYPE ,
  case when eccwo.alert_flag <> 'NoAlerts' THEN
     eccwo.alert_name || nvl((select '|' || lookup_value from eam_ecc_wo_tl_staging where LOOKUP_TYPE = 'EAM_WO_ECC_ALERT_TYPE' and language = msitl.language and lookup_code = eccwo.cost_overrun_alert_code ),'')||
     nvl((select '|' || lookup_value from eam_ecc_wo_tl_staging where LOOKUP_TYPE = 'EAM_WO_ECC_ALERT_TYPE' and language = msitl.language and lookup_code = eccwo.schedule_delay_alert_code ),'')||
     nvl((select '|' || lookup_value from eam_ecc_wo_tl_staging where LOOKUP_TYPE = 'EAM_WO_ECC_ALERT_TYPE' and language = msitl.language and lookup_code = eccwo.WO_SHUTDOWN_ALERT_CODE ),'')
	END as ALERT_NAME ,
	eccwo.ALERT_DETAILS ,
	eccwo.WR_REQUEST_NUMBER,
	eccwo.WR_REQUEST_PRIORITY ,
	eccwo.CURRENCY ,
	eccwo.IS_MAP_ENABLED,
	nvl(eccwo.WO_COMPLETION_PERCENTAGE,0) as WO_COMPLETION_PERCENTAGE,
  CASE
     WHEN nvl(eccwo.attachments,0) > 0 THEN
				eccwo.attachments
		 ELSE
				null
  END as ATTACHMENTS,
  CASE
     WHEN nvl(eccwo.WO_COMPLETION_PERCENTAGE,0) = 0 THEN
        (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_OP_COMPLETED_INFO' and LANGUAGE=msitl.language and LOOKUP_CODE=1)
     WHEN nvl(eccwo.WO_COMPLETION_PERCENTAGE,0)= 100 THEN
        (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_OP_COMPLETED_INFO' and LANGUAGE=msitl.language and LOOKUP_CODE=3)
     WHEN nvl(eccwo.WO_COMPLETION_PERCENTAGE,0)  > 0 AND nvl(eccwo.WO_COMPLETION_PERCENTAGE,0) < 100 THEN
       (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_OP_COMPLETED_INFO' and LANGUAGE=msitl.language and LOOKUP_CODE=2)
  END as OPS_COMPLETED,
  'line_details_48_mono' as REQUIREMENTS
	FROM EAM_ECC_WO_TEMP eccwo , mtl_system_items_b msi ,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
   , (select ROW_ID "'WO_ROW_ID'",CONTEXT "'WO_CONTEXT'",CONCATENATED_SEGMENTS "'WO_CONCATENATED_SEGMENTS'" from WIP_DISCRETE_JOBS_DFV) dfv
  WHERE eccwo.asset_group_id = msi.inventory_item_id
  and eccwo.organization_id = msi.organization_id
  AND eccwo.wip_entity_id = ewod.wip_entity_id(+)
  AND eccwo.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 msi.inventory_item_id = msitl.inventory_item_id(+)
  and msi.organization_id = msitl.organization_id(+)
  and msitl.language in ('US')
   AND eccwo.wo_rowid = dfv."'WO_ROW_ID'"(+) )PIVOT
             ( max(WO_STATUS) as WO_STATUS ,
             max(WORK_ORDER_TYPE_DISP) as WORK_ORDER_TYPE_DISP,
             max(ASSET_GROUP_DESCRIPTION) as ASSET_GROUP_DESCRIPTION,
             max(ASSET_CRITICALITY) as ASSET_CRITICALITY,
             max(ASSET_TYPE) as ASSET_TYPE,
             max(ACTIVITY_SOURCE_MEANING) as ACTIVITY_SOURCE_MEANING,
             max(ACTIVITY_TYPE_DISP) as ACTIVITY_TYPE_DISP,
             max(ACTIVITY_CAUSE_DISP) as ACTIVITY_CAUSE_DISP,
             max(PRIORITY_DISP) as PRIORITY_DISP,
             max(WO_SHUTDOWN_TYPE) as WO_SHUTDOWN_TYPE ,
             max(WARRANTY_STATUS) as WARRANTY_STATUS ,
             max(MATERIAL_SHORTAGE_FLAG) as MATERIAL_SHORTAGE_FLAG,
             max(ALERT_TYPE) as ALERT_TYPE,
             max(ALERT_NAME) as ALERT_NAME,
	     max(WO_SCHEDULE_START_HORIZON) as WO_SCHEDULE_START_HORIZON,
            max(WR_REQUEST_PRIORITY) as WR_REQUEST_PRIORITY,
            max(OPS_COMPLETED) as OPS_COMPLETED
         for LANGUAGE in ('US' "US"))
) x
where
2=2
Download
 
Related Blitz Reports
ECC Enterprise Asset Management, Work Order Operations (Maintenance) ECC Enterprise Asset Management, Stores Work Order (Maintenance), SQL1 ECC Enterprise Asset Management, Assets (Maintenance) ECC Enterprise Asset Management, Work Requests (Maintenance) ECC Enterprise Asset Management, Work Order Assets (Maintenance) ECC Enterprise Asset Management, Stores Materials (Maintenance) ECC Cost Management, Maintenance ECC Enterprise Asset Management, Stores Work Order (Maintenance), SQL2 ECC Enterprise Asset Management, Work Orders (Maintenance), SQL2
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: