ECC Enterprise Asset Management, Work Requests (Maintenance)

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: eam-wr
Query Procedure: EAM_ECC_WR_UTIL_PVT.GET_ECC_WR_DATA_LOAD_INFO
Security Procedure: eam_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
SELECT * from (SELECT /*+ leading(eccwr,cii,ewod,ewsb) use_nl(eccwr,cii,ewod,ewsb,ewstl,msitl) cardinality ( eccwr 1000 ) */
      eccwr.wr_request_number ,
      (select LOOKUP_VALUE from eam_ecc_wr_tl_staging where LOOKUP_TYPE='WIP_EAM_WORK_REQ_STATUS' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwr.wr_status_id) as WR_STATUS ,
      eccwr.wr_status_id,
      (select LOOKUP_VALUE from eam_ecc_wr_tl_staging where LOOKUP_TYPE='WIP_EAM_WORK_REQ_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwr.wr_request_type_id) as WR_REQUEST_TYPE ,
      eccwr.wr_request_type_id,
      eccwr.wr_department,
      (select LOOKUP_VALUE from eam_ecc_wr_tl_staging where LOOKUP_TYPE='WIP_EAM_ACTIVITY_PRIORITY' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwr.wr_priority_id) as WR_PRIORITY,
      eccwr.wr_priority_id,
      eccwr.wr_description,
      eccwr.organization_id,
      eccwr.wr_organization_code,
      eccwr.maintenance_object_id,
      eccwr.asset_number,
      eccwr.asset_description,
      eccwr.asset_organization_id,
      eccwr.asset_organization_code,
      eccwr.asset_group,
      msitl.description ASSET_GROUP_DESCRIPTION ,
      eccwr.asset_category,
      (select LOOKUP_VALUE from eam_ecc_wr_tl_staging where LOOKUP_TYPE='MTL_EAM_ASSET_CRITICALITY' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwr.asset_criticality) as ASSET_CRITICALITY,
      eccwr.asset_area,
      (select LOOKUP_VALUE from eam_ecc_wr_tl_staging where LOOKUP_TYPE='EAM_ITEM_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwr.asset_type) as ASSET_TYPE ,
      eccwr.wip_entity_id,
      eccwr.wip_entity_name,
      eccwr.wo_organization_id,
      eccwr.wo_description,
      ( Decode (ewod.pending_flag, 'Y', Decode (ewsb.seeded_flag, 'Y',
         Nvl (ewstl.user_defined_status, (select LOOKUP_VALUE from eam_ecc_wr_tl_staging where LOOKUP_TYPE='WIP_JOB_STATUS'
					and LANGUAGE=msitl.language || Substr(To_char(ewsb.status_id), 1, -1) and LOOKUP_CODE=eccwr.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_wr_tl_staging where LOOKUP_TYPE='WIP_JOB_STATUS'
				and LANGUAGE=msitl.language || Substr(To_char(ewsb.status_id), 1, -1) and LOOKUP_CODE=eccwr.wo_status)),
				ewstl.user_defined_status) )) as WO_STATUS ,
      eccwr.wo_status_id,
      eccwr.wo_scheduled_start_date,
      eccwr.wo_scheduled_completion_date,
      eccwr.wo_actual_start_date,
      eccwr.wo_actual_completion_date,
      eccwr.wr_requested_for,
      eccwr.wr_request_start_date,
      eccwr.wr_request_by_date,
      eccwr.wr_attribute_category,
      eccwr.work_request_id,
      eccwr.wr_created_by,
      eccwr.wr_creation_date,
      eccwr.wr_last_update_login,
      eccwr.ecc_spec_id,
      eccwr.ecc_last_update_date,
      msitl.language language,
      eccwr.awaiting_wo_flag,
  		CASE
     		WHEN nvl(eccwr.attachments,0) > 0 THEN
					eccwr.attachments
		 		ELSE
					null
  		END as ATTACHMENTS
      FROM EAM_ECC_WR_TEMP eccwr,mtl_system_items_tl msitl,csi_item_instances cii
	  ,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 eccwr.maintenance_object_id = cii.instance_id
      AND eccwr.organization_id = cii.last_vld_organization_id
      AND cii.inventory_item_id = msitl.inventory_item_id(+)
      AND cii.last_vld_organization_id = msitl.organization_id(+)
	  AND eccwr.wip_entity_id = ewod.wip_entity_id(+)
	  AND eccwr.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 msitl.language  in ('US') ) PIVOT (max(WR_STATUS) as WR_STATUS ,
              max(WR_REQUEST_TYPE) as WR_REQUEST_TYPE,
              max(WR_PRIORITY) as WR_PRIORITY,
              max(ASSET_CRITICALITY) as ASSET_CRITICALITY,
	      max(ASSET_GROUP_DESCRIPTION) as ASSET_GROUP_DESCRIPTION,
              max(ASSET_TYPE) as ASSET_TYPE,
              max(WO_STATUS) as WO_STATUS for LANGUAGE in ('US' "US"))
) x
where
2=2