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,msi,msitl)  cardinality(eccwo 1000) */ ECC_SPEC_ID ,dfv.*,
	ECC_LAST_UPDATE_DATE,
	WIP_ENTITY_ID ,
	eccwo.WIP_ENTITY_NAME ,
	eccwo.ORGANIZATION_ID ,
	eccwo.ORGANIZATION_CODE ,
	eccwo.WO_DESCRIPTION ,
	eccwo.WO_SYSTEM_STATUS_ID ,
  (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='WIP_JOB_STATUS' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.wo_system_status_id) 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 || ' | ' || (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 )|| ' | ' ||
     (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 )|| ' | ' ||
     (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.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
   , (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 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, Assets (Maintenance) ECC Enterprise Asset Management, Work Requests (Maintenance) ECC Enterprise Asset Management, Work Order Operations (Maintenance) ECC Enterprise Asset Management, Stores Work Order (Maintenance) ECC Enterprise Asset Management, Stores Materials (Maintenance) ECC Cost Management, Maintenance ECC Enterprise Asset Management, Work Orders (Maintenance), SQL2 ECC Advanced Pricing, Pricing Qualifiers ECC Discrete Manufacturing, Work Order Delays
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: