ECC Enterprise Asset Management, Work Order Operations (Maintenance)

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