ECC Enterprise Asset Management, Stores Materials (Maintenance), SQL1

select
x.*
from
(
SELECT * from ( Select /*+ leading(eccsm,msi,msitl)  cardinality(eccsm 1000) */
       eccsm.MATERIAL_NAME ,
	msitl.description MATERIAL_DESCRIPTION ,
	eccsm.ORGANIZATION_ID  ,
	eccsm.INV_ORGANIZATION_CODE  ,
	eccsm.MATERIAL_UOM,
	eccsm.INVENTORY_ITEM_ID  ,
	(select LOOKUP_VALUE from eam_ecc_tl_staging where LOOKUP_TYPE='EAM_ITEM_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccsm.eam_item_type) as EAM_ITEM_TYPE ,
	eccsm.CROSS_REF_ID  ,
	eccsm.CROSS_REFERENCE ,
       (select mcrtl.description from mtl_cross_references_tl mcrtl where mcrtl.cross_reference_id = eccsm.CROSS_REF_ID and language = msitl.language)
       AS CROSS_REF_DESC,
	eccsm.CROSS_REF_TYPE  ,
	eccsm.MANUFACTURER_ID  ,
	eccsm.MANUFACTURER_NAME ,
	eccsm.MANUFACTURER_PART_NUM,
	eccsm.SUBINVENTORY_CODE  ,
	(select onhand_quantity from EAM_ECC_MAT_AVAIL_QUANTITY where inventory_item_id = eccsm.inventory_item_id
        and organization_id = eccsm.organization_id
        and subinventory_code = eccsm.subinventory_code ) ON_HAND_QTY,
	(select available_quantity from EAM_ECC_MAT_AVAIL_QUANTITY where inventory_item_id = eccsm.inventory_item_id
        and organization_id = eccsm.organization_id
        and subinventory_code = eccsm.subinventory_code ) AVAIL_TO_TRANSACT_QTY ,
	eccsm.RECORD_TYPE ,
	msitl.LANGUAGE ,
	eccsm.ECC_LAST_UPDATE_DATE ,
	eccsm.ECC_SPEC_ID ,
	eccsm.ASSET_BOM  ,
	eccsm.LOCATOR  ,
	eccsm.LOT_NUMBER ,
  NVL(eccsm.SUBINVENTORY_CODE,'NONE') SUBINVENTORY_CODE_STR,
  msitl.long_description LONG_DESCRIPTION,
 (eccsm.INVENTORY_ITEM_ID
  ||'-'
  || eccsm.ORGANIZATION_ID
  ||'-'
  ||  NVL(eccsm.SUBINVENTORY_CODE,'NONE')) INV_ORG_SUBINV_STR
FROM EAM_ECC_STORES_MAT_TEMP eccsm ,mtl_system_items_b msi,mtl_system_items_tl msitl
WHERE eccsm.inventory_item_id = msi.inventory_item_id
and eccsm.organization_id = msi.organization_id
and msi.inventory_item_id = msitl.inventory_item_id(+)
and msi.organization_id = msitl.organization_id(+)
and msitl.language in ('US')  ) PIVOT (max(MATERIAL_DESCRIPTION) as MATERIAL_DESCRIPTION ,
                                      max(LONG_DESCRIPTION) as LONG_DESCRIPTION,
                                      max(EAM_ITEM_TYPE) as EAM_ITEM_TYPE,
                                      max(CROSS_REF_DESC) as CROSS_REF_DESC
                                      for LANGUAGE in ('US' "US"))
) x
where
2=2