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

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: eam-store-wo
Query Procedure: EAM_ECC_STORES_WO_UTIL_PVT.GET_ECC_STORES_WO_DL_INFO
Security Procedure: eam_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC Enterprise Asset Management, Stores Work Order (Maintenance), SQL1 and other Oracle EBS reports with Blitz Report™ on our demo environment
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
               ,eswt.material_shortage_flag
               ,unplanned_material_usage_flag
               ,material_allocated_flag
               ,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
               ,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
										  for LANGUAGE in ('US' "US"))
) x
where
2=2
Blitz Report™