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
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 |