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

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