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
Run
ECC Enterprise Asset Management, Work Orders (Maintenance), SQL1 and other Oracle EBS reports with Blitz Report™ on our demo environment
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 , (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_YES_NO' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.ASSET_MAINTAINABLE) as 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 , CASE WHEN eccwo.WO_SCHEDULE_DELAY_FLAG is not null THEN ( SELECT lookup_value FROM EAM_ECC_WO_TL_STAGING WHERE lookup_type = 'EAM_WO_ECC_ALERT_TYPE' AND language = msitl.language AND lookup_code = '40' ) END AS WO_SCHEDULE_DELAY_FLAG , CASE WHEN eccwo.WO_SCHEDULE_DELAY_FLAG is not null THEN 'Schedule Delay' END AS WO_SCHE_DELAY_FLAG_CODE , (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 , CASE WHEN eccwo.wo_schedule_start_horizon = '20' THEN 'Last 21 to Next 21 Days' END as WO_SCH_START_HORIZON_CODE , 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 , CASE WHEN eccwo.PM_WORK_ORDER_FLAG = 'Preventive Maintenance Work' THEN ( SELECT lookup_value FROM EAM_ECC_WO_TL_STAGING WHERE lookup_type = 'EAM_ECC_WO_GENERIC' AND language = msitl.language AND lookup_code = '10' ) END AS PM_WORK_ORDER_FLAG , CASE WHEN eccwo.PM_WORK_ORDER_FLAG = 'Preventive Maintenance Work' THEN 'Preventive Maintenance Work' END AS PM_WORK_ORDER_FLAG_CODE , 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 , CASE WHEN eccwo.ESTIMATED_TOTAL_COST_FLAG = 'Work Orders With Estimated Cost' THEN ( SELECT lookup_value FROM EAM_ECC_WO_TL_STAGING WHERE lookup_type = 'EAM_ECC_WO_GENERIC' AND language = msitl.language AND lookup_code = '20' ) END AS ESTIMATED_TOTAL_COST_FLAG , CASE WHEN eccwo.ESTIMATED_TOTAL_COST_FLAG = 'Work Orders With Estimated Cost' THEN 'Work Orders With Estimated Cost' END AS ESTIMATE_TCOST_FLAG_CODE , eccwo.VARIANCE_TOTAL_COST , eccwo.WO_COST_OVERRUN , CASE WHEN eccwo.COST_OVERRUN_FLAG is not null THEN ( SELECT lookup_value FROM EAM_ECC_WO_TL_STAGING WHERE lookup_type = 'EAM_WO_ECC_ALERT_TYPE' AND language = msitl.language AND lookup_code = '30' ) END AS COST_OVERRUN_FLAG , CASE WHEN eccwo.COST_OVERRUN_FLAG is not null THEN 'Cost Overrun' END AS COST_OVERRUN_FLAG_CODE , 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 , (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_YES_NO' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.CHECKIN_STATUS) as CHECKIN_STATUS , (select LOOKUP_VALUE from eam_ecc_wo_tl_staging where LOOKUP_TYPE='EAM_YES_NO' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.WARRANTY_ACTIVE) as WARRANTY_ACTIVE , eccwo.UNPLANNED_WO_FLAG , CASE WHEN eccwo.RELEASED_WO_FLAG = 'Released' THEN ( SELECT lookup_value FROM EAM_ECC_WO_TL_STAGING WHERE lookup_type = 'WIP_JOB_STATUS' AND language = msitl.language AND lookup_code = '3' ) END AS RELEASED_WO_FLAG , CASE WHEN eccwo.RELEASED_WO_FLAG = 'Released' THEN 'Released' END AS RELEASED_WO_FLAG_CODE , eccwo.OPEN_WO_FLAG , CASE WHEN eccwo.ALERT_FLAG = 'Alerts' THEN ( SELECT lookup_value FROM EAM_ECC_WO_TL_STAGING WHERE lookup_type = 'ALERT_NOLAERT' AND language = msitl.language AND lookup_code = '10' ) WHEN eccwo.ALERT_FLAG = 'NoAlerts' THEN ( SELECT lookup_value FROM EAM_ECC_WO_TL_STAGING WHERE lookup_type = 'ALERT_NOLAERT' AND language = msitl.language AND lookup_code = '20' ) END AS ALERT_FLAG , CASE WHEN eccwo.ALERT_FLAG = 'Alerts' THEN 'Alerts' WHEN eccwo.ALERT_FLAG = 'NoAlerts' THEN 'NoAlerts' END AS ALERT_FLAG_CODE , 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.material_shortage_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.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),'' ),2) END as ALERT_TYPE , case when eccwo.alert_flag <> 'NoAlerts' THEN eccwo.alert_name || '|' || nvl(eccwo.failure_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 ),'') 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(RELEASED_WO_FLAG) as RELEASED_WO_FLAG, max(ALERT_FLAG) as ALERT_FLAG, max(COST_OVERRUN_FLAG) as COST_OVERRUN_FLAG, max(PM_WORK_ORDER_FLAG) as PM_WORK_ORDER_FLAG, max(WO_SCHEDULE_DELAY_FLAG) as WO_SCHEDULE_DELAY_FLAG, max(ESTIMATED_TOTAL_COST_FLAG) as ESTIMATED_TOTAL_COST_FLAG, max(WO_SCHEDULE_START_HORIZON) as WO_SCHEDULE_START_HORIZON, max(WR_REQUEST_PRIORITY) as WR_REQUEST_PRIORITY, max(OPS_COMPLETED) as OPS_COMPLETED, max(ASSET_MAINTAINABLE) as ASSET_MAINTAINABLE, max(CHECKIN_STATUS) as CHECKIN_STATUS, max(WARRANTY_ACTIVE) as WARRANTY_ACTIVE for LANGUAGE in ('US' "US")) ) x where 2=2 |