ECC Lease Contracts, Revision History

Description
Categories: Enterprise Command Center
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
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