ECC Lease Contracts, Lease Meter Readings Information

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Asset Number, Contract Termination Status, Contract Number, Contract Id, Contract Operating Unit Id, Org Id, Contract Status Code, Contract Currency, Contract Template Indicator ...
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
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 In Action
Blitz Report™

Blitz Report™ provides multiple benefits: