ECC Lease Contracts, Revision History

select
x.*
from
(
select * from
		(SELECT  distinct snapshot_id||'-'||khr_id ECC_SPEC_ID,
				snapshot_id
			   ,khr_id
			   ,
				(
				SELECT  contract_number
				FROM    okc_k_headers_all_b
				WHERE   id = khr_id
				) contract_number
			   ,org_id
			   ,org.name operating_unit
			   ,transaction_id
			   ,transaction_type
			   ,before_version_number
			   ,before_snapshot_date
			   ,after_version_number
			   ,after_snapshot_date
			   ,latest_snapshot_date
			   ,revision_source revision_source_code
			   ,flv2.meaning revision_source
			   ,revision_type revision_type_code
			   ,flv3.meaning revision_type
			   ,revision_reason revision_reason_code
			   ,flv1.meaning revsion_reason
			   ,revision_date
			   ,substr (revision_description,1
					   ,50) REVISION_DESCRIPTION
			   ,substr (revision_user,1
					   ,50) REVISION_USER
			   ,org.language language
                           ,revision_creation_date
			   ,revision_completion_date
		FROM    okl_revision_snapshots_all ors
			   ,fnd_lookup_values flv1
			   ,fnd_lookup_values flv2
			   ,fnd_lookup_values flv3
			   ,hr_all_organization_units_tl org
		WHERE   flv3.lookup_type = 'OKL_SNAP_REVISION_TYPE'
				AND  flv3.lookup_code =revision_type
			AND  flv2.lookup_type(+) = 'OKL_SNAP_REVISION_SOURCE'
			AND  flv2.lookup_code(+) =revision_source
			AND  flv1.lookup_type(+) = 'OKL_REBOOK_REASON'
			AND  flv1.lookup_code(+) =revision_reason
				AND  ors.org_id   = org.organization_id
			AND  flv2.language(+) = org.language
			AND  flv1.language(+) =org.language
			AND  flv3.language(+) =org.language
				AND  org.language in ('US') )
		 PIVOT (MAX(REVISION_SOURCE) as REVISION_SOURCE,
				 MAX(REVSION_REASON) as REVSION_REASON,
				 MAX(REVISION_TYPE)  as REVISION_TYPE,
				 MAX(OPERATING_UNIT) as OPERATING_UNIT
			  for LANGUAGE in ('US' "US"))
) x
where
2=2