ECC Enterprise Asset Management, Work Requests (Maintenance)

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,
      CASE
		WHEN eccwr.awaiting_wo_flag = 'Awaiting Work Order'
		THEN (
			SELECT  lookup_value
            FROM    eam_ecc_wr_tl_staging
            WHERE   lookup_type = 'EAM_ECC_WR_GENERIC'
            AND     language = msitl.language
            AND     lookup_code = '10'
            )
		END AS	   AWAITING_WO_FLAG ,
	CASE
	WHEN eccwr.awaiting_wo_flag = 'Awaiting Work Order'
	THEN 'Awaiting Work Order'
	END AS  AWAITING_WO_FLAG_CODE  ,
  		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,
	      max(AWAITING_WO_FLAG) as AWAITING_WO_FLAG	 for LANGUAGE in ('US' "US"))
) x
where
2=2