ECC Lease Contracts, Lease Meter Errors

select
x.*
from
(
SELECT  * FROM    (
        SELECT  DISTINCT
                to_char (a.id)
                || to_char (a.ocr_id)
                || to_char (a.error_seq_number) ecc_spec_id
               ,a.error_seq_number
               ,a.error_type
               ,a.error_code
               ,CASE
                WHEN    b.trx_status_code = 'DISCARDED'
                        THEN    NULL
                ELSE    a.error_current_yn END error_current_yn
               ,a.message_text
               ,(SELECT  batch_name
                FROM    okl_ctr_reading_batches_all
                WHERE   id = b.ocb_id) batch_name
               ,(SELECT  source_type_code
                FROM    okl_ctr_reading_batches_all
                WHERE   id = b.ocb_id) channel
               ,b.contract_number
               ,cct.name counter_name
               ,to_char (nvl (okl_meter_util_pvt.get_usage_end_period_date (b.counter_id
                                                                           ,b.khr_id
                                                                           ,b.kle_id
                                                                           ,b.value_timestamp)
                             ,b.value_timestamp)
                        ,'yyyy-mm') reading_period
               ,(SELECT  vendor_name
                FROM    po_vendors
                WHERE   vendor_id = oeu.vendor_id) vendor_name
               ,mit.description usage_item_name
               ,mit.language language_code
               ,mit.organization_id org_id
               ,(
                SELECT  fa.manufacturer_name manufacturer_name
                FROM    fa_categories_b cat
                       ,fa_book_controls fbc
                       ,fa_books fb
                       ,fa_additions_b fa
                       ,fa_methods fm
                       ,okc_k_lines_b cle
                       ,okc_k_items item
                WHERE   item.cle_id = cle.id
                AND     fa.asset_id = to_number (item.object1_id1)
                AND     cat.category_id = fa.asset_category_id
                AND     fbc.book_type_code = fb.book_type_code
                AND     nvl (trunc (fbc.date_ineffective)
                            ,trunc (sysdate) + 1) > trunc (sysdate)
                AND     fb.asset_id = fa.asset_id
                AND     fb.transaction_header_id_out IS NULL
                AND     fb.date_ineffective IS NULL
                AND     fb.deprn_method_code = fm.method_code
                AND     fb.life_in_months = fm.life_in_months
                AND     book_class = 'CORPORATE'
                AND     cle.cle_id = b.kle_id
                ) manufacturer_name
        FROM    okl_ctr_reading_errors a
               ,okl_counter_readings b
               ,okl_ecc_usage_vendor_v oeu
               ,csi_counters_tl cct
               ,mtl_system_items_tl mit
        WHERE   a.ocr_id = b.id
        AND     b.khr_id = oeu.contract_id (+)
        AND    nvl(b.value_timestamp,sysdate)> (sysdate -365*nvl(fnd_profile.value ('OKL_ECC_MM_HIST_YEARS'),1))
        AND     cct.counter_id = b.counter_id
        AND     mit.inventory_item_id = b.usage_item_id
        AND     mit.language = cct.language
	AND     mit.language in ('US')
        AND     (((oeu.asset_line_id IS NULL) OR (b.kle_id = oeu.asset_line_id)) AND
                ((oeu.usage_item_id IS NULL) OR (b.usage_item_id = oeu.usage_item_id)))
        AND     a.ocr_id IN (
                SELECT  oce.ocr_id
                FROM    okl_ctr_reading_errors oce
                       ,okl_counter_readings ocr
                WHERE   oce.ocr_id = ocr.id
                )
        ORDER BY ecc_spec_id ASC
                ,error_seq_number ASC
        ) pivot ( max(usage_item_name) as usage_item_name, max(counter_name) as counter_name for language_code in('US' "US"))
) x
where
2=2