ECC Enterprise Asset Management, Stores Work Order (Maintenance), SQL1

select
x.*
from
(
SELECT * from (SELECT  /*+ leading(eswt,wdj,ewod,ewsb) use_nl(eswt,wdj,ewod,ewsb,ewstl,msi,msitl,msitl1) cardinality ( eswt 1000 ) */ ecc_spec_id
               ,
                ecc_last_update_date
               ,eswt.wip_entity_id
               ,wip_entity_name
               ,eswt.organization_id
               ,wo_organization_code
               ,wo_description
               ,msitl.language language
               ,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_SW_TL_STAGING where LOOKUP_TYPE='WIP_JOB_STATUS'
						and LANGUAGE=msitl.language || Substr(To_char(ewsb.status_id), 1, -1) and LOOKUP_CODE=eswt.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_SW_TL_STAGING where LOOKUP_TYPE='WIP_JOB_STATUS'
						and LANGUAGE=msitl.language || Substr(To_char(ewsb.status_id), 1, -1) and LOOKUP_CODE=eswt.wo_status)),
					ewstl.user_defined_status) )) wo_status
               ,activity
               ,activity_id
               ,
                (
                SELECT  lookup_value
                FROM    EAM_ECC_SW_TL_STAGING
                WHERE   lookup_type = 'WIP_EAM_WORK_ORDER_TYPE'
                AND     language = msitl.language
                AND     lookup_code = work_order_type_disp
                ) work_order_type_disp
               ,
                (
                SELECT  lookup_value
                FROM    EAM_ECC_SW_TL_STAGING
                WHERE   lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY'
                AND     language = msitl.language
                AND     lookup_code = work_order_priority
                ) work_order_priority
               ,eswt.asset_number
               ,asset_id
               ,asset_description
               ,asset_group
               ,eswt.asset_group_id
               ,msitl.description asset_group_description
			   ,
				(
				SELECT	lookup_value
				FROM	EAM_ECC_SW_TL_STAGING
				WHERE 	lookup_type = 'MTL_EAM_ASSET_CRITICALITY'
				AND 	language = msitl.language
				AND	lookup_code = asset_criticality
				) asset_criticality
               ,wo_scheduled_start_date
               ,wo_scheduled_completion_date
               ,wo_department
               ,wo_sort_order
               ,operation_seq_num
               ,material_name
               ,eswt.inventory_item_id
               ,uom_code
               ,msitl1.description material_description
               ,subinventory_code
               ,required_quantity
               ,issued_quantity
               ,allocated_quantity
               ,available_quantity
               ,open_quantity
               ,mat_shortage_qty
               ,mat_allocated_qty
               ,mat_unplanned_qty
               ,material_shortage
               ,material_availability
               ,unplanned_material
               ,material_allocated
               ,alert_type
               ,alert_flag
               ,alert_count
               ,alert_name
               ,CASE
				WHEN eswt.material_shortage_flag is not null
				THEN (
                SELECT  lookup_value
                FROM    EAM_ECC_SW_TL_STAGING
                WHERE   lookup_type = 'EAM_ECC_MAT_ALERTS'
                AND     language = msitl.language
                AND     lookup_code = '10'
                 )
				END AS   material_shortage_flag
               ,CASE
				WHEN eswt.unplanned_material_usage_flag is not null
				THEN (
                SELECT  lookup_value
                FROM    EAM_ECC_SW_TL_STAGING
                WHERE   lookup_type = 'EAM_ECC_MAT_ALERTS'
                AND     language = msitl.language
                AND     lookup_code = '20'
                 )
				END AS   unplanned_mat_usage_flag
			   ,CASE
				WHEN eswt.unplanned_material_usage_flag is not null
				THEN 'Unplanned Materials Usage'
			    END AS  UNPLANNED_MATERIAL_CODE
               ,CASE
				WHEN eswt.material_shortage_flag is not null
				THEN 'Materials Shortage'
			    END AS  MATERIAL_SHORTAGE_CODE
			   ,CASE
				WHEN eswt.material_allocated_flag is not null
				THEN (
                SELECT  lookup_value
                FROM    EAM_ECC_SW_TL_STAGING
                WHERE   lookup_type = 'EAM_ECC_MAT_ALERTS'
                AND     language = msitl.language
                AND     lookup_code = '30'
                 )
				END AS   material_allocated_flag
               ,CASE
				WHEN eswt.material_allocated_flag is not null
				THEN 'Allocated'
			    END AS  MATERIAL_ALLOCATED_CODE
			   ,update_asset_link
               ,del_mat_allocat_link
               ,locator
               ,eswt.lot_number
               ,item_category
               ,asset_bom
               ,update_link
               ,copy_asset_link
               ,alert_details
               ,materials_issued
               ,delete_flag
               ,CASE
	          WHEN (nvl(open_quantity,0) > 0)
		  THEN open_quantity || ' ' ||  uom_code
	        END AS material_open   FROM EAM_ECC_STORES_WO_TRACKER eswt,
									 wip_discrete_jobs wdj, 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 , mtl_system_items_tl msitl1 
										  WHERE eswt.wip_entity_id = wdj.wip_entity_id
									 AND eswt.organization_id = wdj.organization_id
									 AND eswt.wip_entity_id = ewod.wip_entity_id(+)
									 AND eswt.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 Nvl (wdj.asset_group_id, wdj.rebuild_item_id) = msi.inventory_item_id
									 AND eswt.organization_id = msi.organization_id
									 AND msi.inventory_item_id = msitl.inventory_item_id(+)
									 AND msi.organization_id = msitl.organization_id(+)
									 AND eswt.inventory_item_id = msitl1.inventory_item_id(+)
									 AND eswt.organization_id = msitl1.organization_id(+)
									 AND msitl.language = msitl1.language
									 AND msitl.language in ('US') )PIVOT (max(MATERIAL_DESCRIPTION) as MATERIAL_DESCRIPTION,
										  max(WO_STATUS) as WO_STATUS,
										  max(WORK_ORDER_TYPE_DISP) as WORK_ORDER_TYPE_DISP,
										  max(WORK_ORDER_PRIORITY) as WORK_ORDER_PRIORITY,
										  max(ASSET_GROUP_DESCRIPTION) as ASSET_GROUP_DESCRIPTION,
										  max(ASSET_CRITICALITY) as ASSET_CRITICALITY,
										  max(unplanned_mat_usage_flag) as unplanned_mat_usage_flag,
										  max(MATERIAL_SHORTAGE_FLAG) as MATERIAL_SHORTAGE_FLAG,
										  max(MATERIAL_ALLOCATED_FLAG) as MATERIAL_ALLOCATED_FLAG
										  for LANGUAGE in ('US' "US"))
) x
where
2=2