ECC Lease Contracts, Lease Batch Details

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Record Type, Contract Number, Khr Id, Batch Id, Batch Name, Batch Reference External, Batch Date, Reading Source Code, Batch Status ...
Imported from Enterprise Command Center
Dataset Key: okl-batch-details
Query Procedure: OKL_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: okl_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
select * from (SELECT  'OKL - '|| to_char (ccr.khr_id)|| to_char (crb.id) ecc_spec_id
				       ,'OKL' record_type
				       ,ccr.contract_number
				       ,ccr.khr_id
				       ,crb.id batch_id
				       ,crb.batch_name
				       ,crb.batch_reference_external
				       ,crb.batch_date
				       ,flv.meaning batch_status_code
              			       ,decode (crb.batch_reference_external,NULL,flv2.lookup_code ,crb.batch_reference_external) reading_source_code
				       ,crb.batch_status_code batch_status
				       ,decode(crb.batch_reference_external,null,flv2.meaning,crb.batch_reference_external) source_type_code
				       ,pov.vendor_name
				       ,oeu.vendor_id vendor_id
				       ,pov.segment1 vendor_number
				       ,(SELECT count(1) FROM okl_counter_readings ccr WHERE ccr.ocb_id = crb.id) total_reads
				       ,(SELECT count(1) FROM okl_counter_readings ccr WHERE ccr.ocb_id = crb.id AND ccr.trx_status_code = 'PROCESSED') total_passed
				       ,(SELECT count(1) FROM okl_counter_readings ccr WHERE ccr.ocb_id = crb.id AND ccr.trx_status_code = 'ERROR') total_error
				       ,(SELECT count(1) FROM okl_counter_readings ccr WHERE ccr.ocb_id = crb.id AND ccr.trx_status_code IN ('PASSED','PENDING')) total_pending
				       ,(SELECT count(1) FROM okl_counter_readings ccr WHERE ccr.ocb_id = crb.id AND ccr.trx_status_code = ('DISCARDED')) total_discarded
				       ,(SELECT count(1)  FROM okl_counter_readings ccr WHERE ccr.ocb_id = crb.id AND ccr.trx_status_code = ('WARNING')) total_warning
				       ,flv1.description update_reads
				       ,(SELECT  inv_organization_id FROM    okc_k_headers_all_b  WHERE   id = ccr.khr_id) org_id
				        ,flv.language language_code
				FROM    okl_ctr_reading_batches_all crb
				       ,okl_counter_readings ccr
				       ,okl_ecc_usage_vendor_v oeu
				       ,po_vendors pov
				       ,fnd_lookup_values flv
				       ,fnd_lookup_values flv1
				       ,fnd_lookup_values flv2
				WHERE   crb.id (+) = ccr.ocb_id
				AND     oeu.contract_id (+) = ccr.khr_id
				AND     oeu.asset_line_id (+) = ccr.kle_id
				AND     pov.vendor_id (+) = oeu.vendor_id
				AND     flv.LOOKUP_TYPE ='OKL_METER_READS_STATUS_TYPE'
				AND     flv.LOOKUP_CODE = crb.batch_status_code
				and     nvl(flv.END_DATE_ACTIVE,sysdate+1) >= sysdate
				AND     flv1.LOOKUP_TYPE ='OKL_ECC_ACTION_EVENTS'
				AND     flv1.LOOKUP_CODE = 'UPDATE'
			        and     nvl(flv1.END_DATE_ACTIVE,sysdate+1) >= sysdate
				AND     flv2.LOOKUP_TYPE = 'OKL_ECC_MM_SRC_MAPING'
				AND     flv2.LOOKUP_CODE = crb.source_type_code
				and 		nvl(flv2.END_DATE_ACTIVE,sysdate+1) >= sysdate
				AND     flv2.language = flv1.language
				AND     flv.language = flv1.language(+)
				AND     flv.language in ('US')
                                AND     nvl(ccr.value_timestamp,batch_date) > (sysdate -365*nvl(fnd_profile.value ('OKL_ECC_MM_HIST_YEARS'),1))
				GROUP BY ccr.contract_number
				        ,ccr.khr_id
				        ,crb.id
				        ,crb.batch_name
				        ,crb.batch_reference_external
				        ,crb.batch_date
				        ,flv.meaning
				        ,crb.source_type_code
				        ,pov.vendor_name
				        ,oeu.vendor_id
				        ,flv1.description
				        ,flv2.meaning
					,crb.batch_status_code
					,flv2.lookup_code
				        ,pov.segment1,flv.language)
					pivot
				        (max(batch_status_code) as batch_status_code,
					 max(source_type_code) as source_type_code ,
	 max(update_reads) as update_reads for language_code 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