ECC Enterprise Asset Management, Work Order Operations (Maintenance)
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: eam-wo-op
Query Procedure: EAM_ECC_WO_OP_UTIL_PVT.GET_ECC_WO_OP_DL_INFO
Security Procedure: eam_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Dataset Key: eam-wo-op
Query Procedure: EAM_ECC_WO_OP_UTIL_PVT.GET_ECC_WO_OP_DL_INFO
Security Procedure: eam_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Enterprise Asset Management, Work Order Operations (Maintenance) and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( SELECT src.* ,dfv2.* FROM ( Select /*+ leading(eccwo,ewod,ewsb) use_nl(eccwo,ewod,ewsb,ewstl,msi,msitl) cardinality ( eccwo 1000 ) */ eccwo.ECC_SPEC_ID, eccwo.WO_OP_ROWID, eccwo.WO_OP_RES_ROWID, eccwo.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_op_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_op_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.WORK_ORDER_TYPE, (select LOOKUP_VALUE from eam_ecc_wo_op_tl_staging where LOOKUP_TYPE='WIP_EAM_WORK_ORDER_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.work_order_type) as WORK_ORDER_TYPE_DISP, eccwo.WO_DEPARTMENT, eccwo.ASSET_GROUP, eccwo.ASSET_GROUP_ID, msitl.description ASSET_GROUP_DESCRIPTION, eccwo.ASSET_NUMBER, eccwo.ASSET_DESCRIPTION, eccwo.ASSET_ID, eccwo.ASSET_CATEGORY, eccwo.ASSET_CRITICALITY_CODE, (select LOOKUP_VALUE from eam_ecc_wo_op_tl_staging where LOOKUP_TYPE='MTL_EAM_ASSET_CRITICALITY' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.asset_criticality_code) as ASSET_CRITICALITY, eccwo.ASSET_ORGANIZATION_CODE, eccwo.ASSET_ORGANIZATION_ID, (select LOOKUP_VALUE from eam_ecc_wo_op_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_op_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, eccwo.ASSET_AREA, eccwo.PARENT_ASSET, eccwo.WO_PRIORITY, (select LOOKUP_VALUE from eam_ecc_wo_op_tl_staging where LOOKUP_TYPE='WIP_EAM_ACTIVITY_PRIORITY' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.wo_priority) as WO_PRIORITY_DISP, (select LOOKUP_VALUE from eam_ecc_wo_op_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_HOURS, eccwo.OP_SEQUENCE_NUM, eccwo.OP_DESCRIPTION, eccwo.OP_DEPT_CODE, (select LOOKUP_VALUE from eam_ecc_wo_op_tl_staging where LOOKUP_TYPE='BOM_EAM_SHUTDOWN_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.op_shutdown_type_code) as OP_SHUTDOWN_TYPE, eccwo.OP_SHUTDOWN_TYPE_CODE, eccwo.OP_SHUTDOWN_HOURS, eccwo.OP_COMPLETED, eccwo.RESOURCE_SEQ_NUM, eccwo.RESOURCE_ID, eccwo.RESOURCE_TYPE, (select LOOKUP_VALUE from eam_ecc_wo_op_tl_staging where LOOKUP_TYPE='BOM_RESOURCE_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.resource_type) as RESOURCE_TYPE_DISP, eccwo.RESOURCE_REQUIRED, eccwo.ASSIGNED_UNITS, eccwo.INSTANCES_ASSIGNED, eccwo.RES_USG_START_DATE, eccwo.RES_USG_COMPLETION_DATE, eccwo.ASSIGNED_HOURS, 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.OP_START_DATE, eccwo.OP_END_DATE, eccwo.OP_ACTUAL_START_DATE, eccwo.OP_ACTUAL_END_DATE, eccwo.OP_DURATION, eccwo.RESOURCE_START_DATE, eccwo.RESOURCE_END_DATE, eccwo.RES_DURATION, eccwo.INSTANCE_START_DATE, eccwo.INSTANCE_END_DATE, eccwo.INST_DURATION, eccwo.RESOURCE_CODE, eccwo.RESOURCE_DESCRIPTION, eccwo.OP_LONG_DESC, eccwo.RES_SCHEDULED_FLAG, eccwo.RES_UOM, eccwo.RES_SCHEDULED, eccwo.RES_SCHEDULE_SEQ_NUM, eccwo.RES_CHARGE_TYPE, (select LOOKUP_VALUE from eam_ecc_wo_op_tl_staging where LOOKUP_TYPE='BOM_AUTOCHARGE_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwo.res_charge_type) as RES_CHARGE_CODE, eccwo.RES_STANDARD_RATE_FLAG, eccwo.CHARGED_UNITS, eccwo.COMP_REQUIRED_FLAG, eccwo.COMP_REQUIRED, 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, msitl.language LANGUAGE, eccwo.MATERIAL_SHORTAGE_CHECK_DATE, eccwo.MATERIAL_SHORTAGE_FLAG, eccwo.MATERIAL_SHORTAGE, eccwo.UNPLANNED_WO_FLAG, eccwo.RELEASED_WO_FLAG, eccwo.OPEN_WO_FLAG, eccwo.ALERT_FLAG, eccwo.ALERT_COUNT, eccwo.ALERT_TYPE, eccwo.ALERT_NAME, eccwo.ALERT_DETAILS, CASE WHEN nvl(eccwo.ATTACHMENTS,0) > 0 THEN eccwo.ATTACHMENTS ELSE null END as ATTACHMENTS FROM EAM_ECC_WO_OP_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 WHERE ewod.wip_entity_id = eccwo.wip_entity_id and ewod.organization_id = eccwo.organization_id and ewod.user_defined_status_id = ewsb.status_id and ewsb.status_id = ewstl.status_id (+) and 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 = ewstl.LANGUAGE or ewstl.LANGUAGE IS NULL ) and msitl.language in ('US') ) src ,(select ROWIDTOCHAR(ROWID) as "'WO_OP_RES_ROW_ID'", ATTRIBUTE_CATEGORY as "'WO_OP_RES_CONTEXT_VALUE'",ATTRIBUTE5 as "'WO_OP_RES_ACTIVITY_GROUP_ID'",DECODE (col.ATTRIBUTE_CATEGORY,'1',ATTRIBUTE1,'3',ATTRIBUTE1,'4',ATTRIBUTE1, NULL) as "'WO_OP_RES_FACTORY_PLANNER_RESOURCE_TYPE'",ATTRIBUTE2 as "'WO_OP_RES_PRINCIPAL_RESOURCE'",ATTRIBUTE3 as "'WO_OP_RES_RESOURCE_PRIORITY'",ATTRIBUTE4 as "'WO_OP_RES_RESOURCE_STEP_NUMBER'" from WIP_OPERATION_RESOURCES col) dfv2 WHERE SRC.LANGUAGE IN ('US') AND src.wo_op_res_rowid=dfv2."'WO_OP_RES_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(WO_PRIORITY_DISP) as WO_PRIORITY_DISP, max(WO_SHUTDOWN_TYPE) as WO_SHUTDOWN_TYPE, max(OP_SHUTDOWN_TYPE) as OP_SHUTDOWN_TYPE, max(RESOURCE_TYPE_DISP) as RESOURCE_TYPE_DISP, max(MATERIAL_SHORTAGE_FLAG) as MATERIAL_SHORTAGE_FLAG, max(asset_maintainable) as ASSET_MAINTAINABLE, max(RES_CHARGE_CODE) as RES_CHARGE_CODE for LANGUAGE in ('US' "US")) ) x where 2=2 |