ECC Inventory Management, Inventory Trace Suppliers

select
x.*
from
(
 SELECT * FROM (select * from
		  (SELECT /*+ leading(map,mstl) use_nl(map mstl) cardinality(map 10) index(map) */ ecc_spec_id
      , document_type
      , order_number
      , document_source
      , vendor_site_code
      , org_id
      , txn_org_id
      , transaction_date
      , map.inventory_item_id
      , lot_number
      , vendor_lot_number
      , vendor_name
      , organization_name
      , subinventory_code
      , locator
      , locator_id
      , deliver_type
      , transaction_id
      , user_name
      , item
      , organization_code
      , hu.language
	  , CASE when (('Y'='Y') ) THEN 'Y'
  							ELSE 'N'	END  onhand_enable
FROM    inv_ecc_tracesuppliers_map map
      , mtl_system_items_tl mstl
      , hr_all_organization_units_tl hu
      , fnd_lookup_values flv1
      , fnd_lookup_values flv2
WHERE   map.inventory_item_id = mstl.inventory_item_id
AND     map.org_id = mstl.organization_id
AND     map.org_id = hu.organization_id
AND     hu.language in ('US')
AND     hu.language = mstl.language
AND     flv1.lookup_type = 'INV_ECC_TRACK_TRACE_NODETYPE'
AND     flv1.view_application_id = 700
AND     flv1.meaning = map.document_type
AND     map.process_flag='N'
AND     flv1.language = 'US'
AND     mstl.organization_id = nvl ('', mstl.organization_id)
AND     flv2.lookup_type = 'INV_ECC_TRACK_TRACE_NODETYPE'
AND     flv2.view_application_id = 700
AND     flv2.lookup_code = flv1.lookup_code
AND     flv2.language = hu.language))
	  PIVOT (
                        MAX ( DOCUMENT_TYPE ) AS DOCUMENT_TYPE , MAX( ORGANIZATION_NAME ) AS ORGANIZATION_NAME   , MAX( DELIVER_TYPE ) AS DELIVER_TYPE
						FOR language  IN ('US' "US")) 
) x
where
2=2