ECC Lease Contracts, Contracts (1)

select
x.*
from
(
SELECT * from (
		select a.*,
			case
           when (a.chr_Sts_code in ('TERMINATED','EXPIRED')
                OR (a.snapshot_count = 0 and a.version_code <> 'CURRENT')) then 'Y'
           else 'N'
           end terminated_flag
       from
       	(select rcpt_v.contract_number||'-'||rcpt_v.version_code ecc_spec_id,
--                               rcpt_v.*,
	        rcpt_v.major_version,
		rcpt_v.version_code,
		rcpt_v.khr_id,
		rcpt_v.contract_number,
		rcpt_v.language,
		rcpt_v.org_id,
		rcpt_v.operating_unit,
		rcpt_v.revision_contract_number,
		rcpt_v.revision_contrct_status,
		rcpt_v.contract_description,
		rcpt_v.start_date,
		rcpt_v.end_date,
		rcpt_v.chr_sts_code,
		rcpt_v.chr_sts_meaning,
		rcpt_v.customer,
		rcpt_v.account_number,
		rcpt_v.product_name,
		rcpt_v.book_classificaton,
		rcpt_v.term,
		rcpt_v.user_name,
		rcpt_v.pre_tax_irr,
		rcpt_v.after_tax_irr,
		rcpt_v.implicit_interest_rate,
		rcpt_v.booking_yield,
		rcpt_v.legal_entity_name,
		rcpt_v.ledger_name,
		rcpt_v.last_activation_date,
		rcpt_v.accrual_status,
		rcpt_v.currency_code,
		rcpt_v.legal_address,
		rcpt_v.bill_to_address,
      case
        when VERSION_CODE ='CURRENT' then ( SELECT  count (1)
        FROM    okl_revision_snapshots_all
        WHERE   khr_id = rcpt_v.khr_id_number and after_version_number = 'Current')
        else
			  (SELECT  count (1)
			        FROM    okl_revision_snapshots_all
			        WHERE   khr_id =rcpt_v.khr_id_number
			        AND     before_version_number = to_char (rcpt_v.major_version))
        end snapshot_count
			  FROM OKL_ECC_CONT_VERSION_HIST_V rcpt_v, okl_ecc_cv_process_t process_t
			 WHERE rcpt_v.khr_id_number = process_t.khr_id
			   AND process_t.incremental_flag =  'N') a
			   where language in ('US'))
           	         PIVOT (MAX(OPERATING_UNIT) as OPERATING_UNIT,
			        MAX(CHR_STS_MEANING) AS CHR_STS_MEANING,
			        MAX(CONTRACT_DESCRIPTION) AS CONTRACT_DESCRIPTION,
                                MAX(MAJOR_VERSION) AS MAJOR_VERSION
			        for LANGUAGE in ('US' "US") )
) x
where
2=2