ECC Lease Contracts, Lease Batch Details
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
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 |
|
LOV |