ECC Lease Contracts, Lease Meter Readings Information

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: okl-ecc-reads
Query Procedure: OKL_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: okl_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC Lease Contracts, Lease Meter Readings Information and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT  *
FROM    (SELECT  ecv.contract_id|| '-'|| ecv.contract_line_id|| '-'|| ecv.counter_id|| '-'|| ecv.vendor_site|| '-'|| ecv.period_number|| '-'|| nvl (ecv.id,0) ecc_spec_id
               ,ecv.language_code language_code
               ,ecv.counter_name counter_name
               ,ecv.ubb_price_name ubb_price_name
               ,ecv.price_list_description price_list_description
               ,ecv.usage_item_name1 usage_item_name1
               ,ecv.asset_number asset_number
               ,ecv.item_name item_name
               ,ecv.cons_counter_name cons_counter_name
               ,ecv.counter_description counter_description
               ,ecv.operating_unit operating_unit
               ,ecv.operating_unit_short_code operating_unit_short_code
               ,ecv.contract_status contract_status
               ,decode(ecv.contract_status_code ,'TERMINATED',1,'EXPIRED',1,0) contract_termination_status
               ,ecv.line_status line_status
               ,ecv.contract_number contract_number
               ,ecv.contract_id contract_id
               ,ecv.contract_operating_unit_id contract_operating_unit_id
               ,ecv.org_id org_id
               ,ecv.contract_status_code contract_status_code
               ,ecv.contract_currency contract_currency
               ,ecv.contract_template_indicator contract_template_indicator
               ,ecv.contract_template_name contract_template_name
               ,ecv.contract_signed_date contract_signed_date
               ,ecv.contract_termination_date contract_termination_date
               ,ecv.contract_start_date contract_start_date
               ,ecv.contract_end_date contract_end_date
               ,ecv.customer_account_id customer_account_id
               ,ecv.cust_bill_to_site_use_id cust_bill_to_site_use_id
               ,ecv.contract_orig_system_source contract_orig_system_source
               ,ecv.vendor_program vendor_program
               ,ecv.khr_id khr_id
               ,ecv.contract_line_id contract_line_id
               ,ecv.vendor_program_id vendor_program_id
               ,ecv.contract_financial_product_id contract_financial_product_id
               ,ecv.contract_financial_product contract_financial_product
               ,ecv.contract_fin_prod_desc contract_fin_prod_desc
               ,ecv.contract_legal_entity_id contract_legal_entity_id
               ,ecv.contract_status_type_code contract_status_type_code
               ,ecv.total_number_of_assets total_number_of_assets
               ,ecv.functional_currency functional_currency
               ,ecv.ledger_id ledger_id
               ,ecv.ledger ledger
               ,ecv.contract_sales_rep contract_sales_rep
               ,ecv.legal_entity legal_entity
               ,ecv.customer_party_number customer_party_number
               ,ecv.customer_party_name customer_party_name
               ,ecv.customer_party_type customer_party_type
               ,ecv.customer_sic_code customer_sic_code
               ,ecv.customer_duns_number customer_duns_number
               ,ecv.customer_alias customer_alias
               ,ecv.customer_country customer_country
               ,ecv.customer_address1 customer_address1
               ,ecv.customer_address2 customer_address2
               ,ecv.customer_address3 customer_address3
               ,ecv.customer_address4 customer_address4
               ,ecv.customer_city customer_city
               ,ecv.customer_postal_code customer_postal_code
               ,ecv.customer_state customer_state
               ,ecv.customer_province customer_province
               ,ecv.customer_county customer_county
               ,ecv.customer_sic_code_type customer_sic_code_type
               ,ecv.customer_url customer_url
               ,ecv.customer_email customer_email
               ,ecv.customer_phone customer_phone
               ,ecv.customer_fax customer_fax
               ,ecv.customer_prim_phone_purpose customer_prim_phone_purpose
               ,ecv.customer_prim_phone_type customer_prim_phone_type
               ,ecv.customer_prim_ph_country_code customer_prim_ph_country_code
               ,ecv.customer_prim_ph_area_code customer_prim_ph_area_code
               ,ecv.customer_prim_phone_number customer_prim_phone_number
               ,ecv.customer_prim_phone_extn customer_prim_phone_extn
               ,ecv.customer_home_country customer_home_country
               ,ecv.customer_status customer_status
               ,ecv.customer_party_id customer_party_id
               ,ecv.customer_account_number customer_account_number
               ,ecv.customer_acc_dff_category customer_acc_dff_category
               ,ecv.customer_account_status customer_account_status
               ,ecv.customer_type customer_type
               ,ecv.customer_sales_channel customer_sales_channel
               ,ecv.customer_acc_estab_date customer_acc_estab_date
               ,ecv.customer_account_name customer_account_name
               ,ecv.customer_account_comments customer_account_comments
               ,ecv.customer_last_status_upd_dt customer_last_status_upd_dt
               ,ecv.serial_number serial_number
               ,ecv.counter_id counter_id
               ,ecv.counter_group_id counter_group_id
               ,tl.description frequency
               ,ecv.counter_value_id counter_value_id
               ,ecv.value_timestamp value_timestamp
               ,ecv.counter_reading counter_reading
               ,ecv.net_reading net_reading
               ,ecv.period_number period_number
               ,ecv.period_name period_name
               ,ecv.period_meter_read period_meter_read
               ,ecv.period_due_date period_due_date
               ,ecv.read_received_flag read_received_flag
               ,ecv.period_last_read_dt period_last_read_dt
               ,ecv.last_update_date last_update_date
               ,ecv.asset_id asset_id
               ,ecv.ubb_price_list_id ubb_price_list_id
               ,ecv.usage_line_id usage_line_id
               ,ecv.usage_item_id usage_item_id
               ,ecv.request_id request_id
               ,ecv.last_read_date last_read_date
               ,ecv.usage_period_start_date usage_period_start_date
               ,ecv.usage_period_end_date usage_period_end_date
               ,ecv.missed_read missed_read
               ,ecv.usage_line_start_date usage_line_start_date
               ,ecv.usage_line_end_date usage_line_end_date
               ,ecv.srvc_usage_line_start_date srvc_usage_line_start_date
               ,ecv.srvc_usage_line_end_date srvc_usage_line_end_date
               ,ecv.line_status_code line_status_code
               ,ecv.ctr_period_num ctr_period_num
               ,ecv.usage_item_name usage_item_name
               ,ecv.allowance allowance
               ,ecv.accured_allowance accured_allowance
               ,ecv.overage overage
               ,ecv.overage_rate overage_rate
               ,
                (
                SELECT  count (DISTINCT counter_id)
                FROM    okl_ecc_sch_meters_t esm
                WHERE   esm.contract_id = ecv.contract_id
                GROUP BY contract_id
                ) no_of_counters
               ,ecv.id id
               ,ecv.ocb_id ocb_id
               ,ecv.instance_number instance_number
               ,ecv.tag_number tag_number
               ,ecv.cons_counter_id cons_counter_id
               ,ecv.okl_value_timestamp okl_value_timestamp
               ,ecv.reading_uom_code reading_uom_code
               ,ecv.counter_reading_type counter_reading_type
               ,ecv.okl_counter_reading okl_counter_reading
               ,ecv.counter_meter_type counter_meter_type
               ,ecv.source_transaction_date source_transaction_date
               ,ecv.okl_counter_value_id okl_counter_value_id
               ,ecv.reset_mode reset_mode
               ,ecv.reset_reason reset_reason
               ,ecv.adjustment_type adjustment_type
               ,ecv.adjustment_reading adjustment_reading
               ,ecv.okl_net_reading okl_net_reading
               ,ecv.trx_status_code trx_status
               ,(select meaning from fnd_lookup_values where 	LOOKUP_TYPE ='OKL_METER_READS_STATUS_TYPE' and nvl(END_DATE_ACTIVE,sysdate+1) >= sysdate and LOOKUP_CODE = ecv.trx_status_code and language = language_code ) trx_status_code
               ,ecv.ocr_id ocr_id
               ,ecv.prior_ocb_id prior_ocb_id
               ,ecv.comments comments
               ,ecv.security_group_id security_group_id
               ,ecv.object_version_number object_version_number
               ,ecv.processed_reads processed_reads
               ,ecv.error_reads error_reads
               ,ecv.okl_last_read_date okl_last_read_date
               ,ecv.okl_last_meter_read okl_last_meter_read
               ,ecv.batch_name batch_name
               ,(select meaning from fnd_lookup_values where 	LOOKUP_TYPE ='OKL_METER_READS_STATUS_TYPE' and nvl(END_DATE_ACTIVE,sysdate+1) >= sysdate and LOOKUP_CODE = ecv.batch_status_code and language = language_code )batch_status_code
               ,ecv.batch_date batch_date
               ,CASE
                WHEN    crb.batch_reference_external IS NULL
                        THEN (select meaning from fnd_lookup_values where LOOKUP_TYPE = 'OKL_ECC_MM_SRC_MAPING' and nvl(END_DATE_ACTIVE,sysdate+1) >= sysdate and LOOKUP_CODE = crb.source_type_code and language = language_code)
                ELSE    crb.batch_reference_external END source_type_code
               ,ecv.customer_address customer_address
               ,ecv.state state
               ,ecv.country country
               ,ecv.city city
               ,ecv.address1 address1
               ,ecv.email_address email_address
               ,ecv.primary_phone_line_type primary_phone_line_type
               ,ecv.vendor_phone vendor_phone
               ,ecv.vendor_site vendor_site
               ,ecv.vendor_address vendor_address
               ,ecv.vendor_email vendor_email
               ,ecv.zip zip
               ,ecv.fax fax
               ,ecv.vendor_name vendor_name
               ,ecv.vendor_number vendor_number
               ,ecv.fa_corp_category fa_corp_category
               ,ecv.asset_category asset_category
               ,ecv.manufacturer_name manufacturer_name
               ,ecv.model model
               ,ecv.due_read due_read
               ,ecv.rd_missed_read rd_missed_read
               ,
                (
                SELECT  1
                FROM    dual
                WHERE   ecv.read_received_flag = 'Y'
                AND     ecv.period_due_date <= nvl ((okl_meter_util_pvt.get_usage_end_period_date (ecv.counter_id
                                                                                                      ,ecv.contract_id
                                                                                                      ,ecv.contract_line_id
                                                                                                      ,sysdate))
                                                   ,sysdate)
                ) received_read
               ,ecv.schedule_reads schedule_reads
               ,flv2.description enter_action
               ,flv1.description update_action
        FROM    okl_ecc_mtr_reads_v ecv
               ,mtl_units_of_measure_tl tl
               ,okl_ctr_reading_batches_all crb
               ,fnd_lookup_values flv1
               ,fnd_lookup_Values flv2
        WHERE   tl.uom_code = frequency
        AND     ecv.ocb_id = crb.id (+)
  	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_ACTION_EVENTS'
	AND     flv2.LOOKUP_CODE = 'ENTER'
	AND  nvl(flv2.END_DATE_ACTIVE,sysdate+1) >= sysdate
        AND     flv1.language = ecv.language_code
        AND     flv1.language = flv2.language
        AND     tl.language = ecv.language_code
	AND     ecv.language_code in ('US')
        ) PIVOT (MAX(ITEM_NAME) AS ITEM_NAME, MAX(FREQUENCY) AS FREQUENCY, MAX(CONTRACT_STATUS) AS CONTRACT_STATUS,
		 MAX(COUNTER_NAME) AS COUNTER_NAME, MAX(COUNTER_DESCRIPTION) AS COUNTER_DESCRIPTION, MAX(UBB_PRICE_NAME) AS UBB_PRICE_NAME ,
		 MAX(PRICE_LIST_DESCRIPTION) AS PRICE_LIST_DESCRIPTION , MAX(CONS_COUNTER_NAME) AS CONS_COUNTER_NAME , MAX(OPERATING_UNIT) AS OPERATING_UNIT,
		 MAX(OPERATING_UNIT_SHORT_CODE) AS OPERATING_UNIT_SHORT_CODE, MAX(USAGE_ITEM_NAME1) AS USAGE_ITEM_NAME1, MAX(LINE_STATUS) AS LINE_STATUS,
                 MAX(ENTER_ACTION) AS ENTER_ACTION,MAX(UPDATE_ACTION) AS UPDATE_ACTION,MAX(SOURCE_TYPE_CODE) AS SOURCE_TYPE_CODE,
                 MAX(TRX_STATUS_CODE) AS TRX_STATUS_CODE,MAX(BATCH_STATUS_CODE) AS BATCH_STATUS_CODE
		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
Blitz Report™

Blitz Report™ provides multiple benefits: