ECC Lease Contracts, Contracts (1)
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Contract Version History
Dataset Key: okl-cv-contract-history
Query Procedure: OKL_CV_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
Description: Contract Version History
Dataset Key: okl-cv-contract-history
Query Procedure: OKL_CV_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
Run
ECC Lease Contracts, Contracts (1) and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |