ECC Lease Contracts, Lease Batch Details

➜ Back to Library
Description
Categories: Enterprise Command Center
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
Run ECC Lease Contracts, Lease Batch Details and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Download
Related Blitz Reports
ECC Lease Contracts, Lease Meter Errors ECC Lease Contracts, Lease Meter Readings Information Avalara VAT listing ECC Lease Contracts, Lease Vendor Cash Flow OKL Generate Quote Payment Schedule RK: AR Transactions and Lines GL Account Analysis (Distributions) ECC Lease Contracts, Revision History OKL Termination Quotes
Blitz Report™

Blitz Report™ provides multiple benefits: