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

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)