ECC Lease Contracts, Revision History

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Snapshot Id, Khr Id, Contract Number, Org Id, Transaction Id, Transaction Type, Before Version Number, Before Snapshot Date, After Version Number ...
Imported from Enterprise Command Center
Description: Contract Revision History
Dataset Key: okl-cv-revisions
Query Procedure: OKL_CV_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: okl_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
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
		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
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV