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

➜ Back to Library
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
               ,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
Download
Related Blitz Reports
ECC Enterprise Asset Management, Work Orders (Maintenance), SQL1 ECC Enterprise Asset Management, Work Order Assets (Maintenance) ECC Enterprise Asset Management, Work Order Operations (Maintenance) ECC Enterprise Asset Management, Work Requests (Maintenance) ECC Enterprise Asset Management, Assets (Maintenance) EAM Work Orders ECC Enterprise Asset Management, Stores Work Order (Maintenance), SQL3 ECC Discrete Manufacturing, Work Orders EAM Weekly Schedule
Blitz Report™

Blitz Report™ provides multiple benefits: