ECC Lease Contracts, Service

select
x.*
from
(
SELECT * from (SELECT /*+ leading(process_t) push_pred(service) use_nl(process_t service) */  service.*
		  FROM OKL_ECC_CV_SERVICE_V service, okl_ecc_cv_process_t process_t
		 WHERE service.dnz_chr_id = process_t.khr_id
		   AND process_t.incremental_flag = 'N'
		   AND language in ('US')
		   )
		   PIVOT (MAX(LINE_NAME) as LINE_NAME ,
		          MAX(LINE_STATUS_MEANING) as LINE_STATUS_MEANING,
                          MAX(MAJOR_VERSION) as MAJOR_VERSION
		          for LANGUAGE in ('US' "US"))
) x
where
2=2