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) */
      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,
      (select LOOKUP_VALUE from eam_ecc_wr_tl_staging where LOOKUP_TYPE='WIP_JOB_STATUS' and LANGUAGE=msitl.language and LOOKUP_CODE=eccwr.wo_status_id) 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
      FROM EAM_ECC_WR_TEMP eccwr,mtl_system_items_tl msitl,csi_item_instances cii
      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 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