ECC Lease Contracts, Lease Meter Readings Information

Description

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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

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
 	       ,ecv.usage_maximum usage_maximum
	       ,ecv.usage_uom usage_uom
	       ,ecv.contract_payment_structure contract_payment_structure
	       ,ecv.end_of_term_basis end_of_term_basis
	       ,ecv.true_up_frequency  true_up_frequency
       ,ecv.contract_structure_meaning contract_structure_meaning
       ,ecv.eot_basis_meaning eot_basis_meaning
       ,ecv.true_up_frequency_meaning   true_up_frequency_meaning
       ,ecv.roll_over roll_over
       ,ecv.rollover_minimum rollover_minimum
       ,ecv.accrued_minimum accrued_minimum
        ,(nvl( (     SELECT  max (counter_reading)
                     FROM    csi_counter_readings csi
                     WHERE   csi.counter_id = ecv.counter_id AND csi.disabled_flag = 'N'
                     AND csi.value_timestamp <= ecv.usage_period_end_date) ,ecv.period_meter_read)
                    -
           nvl ((    SELECT  counter_reading
                     FROM    csi_counter_readings csi
                     WHERE   csi.counter_id = ecv.counter_id
                     AND     nvl (csi.initial_reading_flag ,'N') = 'Y'
                     AND     disabled_flag = 'N')
                    ,0)) previous_max       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     ecv.request_id IS NULL
  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
    ,MAX(EOT_BASIS_MEANING) AS EOT_BASIS_MEANING, MAX(TRUE_UP_FREQUENCY_MEANING) AS TRUE_UP_FREQUENCY_MEANING
    , MAX(CONTRACT_STRUCTURE_MEANING) AS CONTRACT_STRUCTURE_MEANING
		for language_code in  ('US' "US"))
) x
where
2=2

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 ,ecv.usage_maximum usage_maximum ,ecv.usage_uom usage_uom ,ecv.contract_payment_structure contract_payment_structure ,ecv.end_of_term_basis end_of_term_basis ,ecv.true_up_frequency true_up_frequency ,ecv.contract_structure_meaning contract_structure_meaning ,ecv.eot_basis_meaning eot_basis_meaning ,ecv.true_up_frequency_meaning true_up_frequency_meaning ,ecv.roll_over roll_over ,ecv.rollover_minimum rollover_minimum ,ecv.accrued_minimum accrued_minimum ,(nvl( ( SELECT max (counter_reading) FROM csi_counter_readings csi WHERE csi.counter_id = ecv.counter_id AND csi.disabled_flag = 'N' AND csi.value_timestamp <= ecv.usage_period_end_date) ,ecv.period_meter_read) - nvl (( SELECT counter_reading FROM csi_counter_readings csi WHERE csi.counter_id = ecv.counter_id AND nvl (csi.initial_reading_flag ,'N') = 'Y' AND disabled_flag = 'N') ,0)) previous_max 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 ecv.request_id IS NULL 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 ,MAX(EOT_BASIS_MEANING) AS EOT_BASIS_MEANING, MAX(TRUE_UP_FREQUENCY_MEANING) AS TRUE_UP_FREQUENCY_MEANING , MAX(CONTRACT_STRUCTURE_MEANING) AS CONTRACT_STRUCTURE_MEANING 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:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Find out more

Features

Download

FAQ