ECC Lease Contracts, Contract Details

select
x.*
from
(
SELECT * FROM
( SELECT spec_id ECC_SPEC_ID,
        contract_id,
        contract_number,
        event_id,
        code_combination_id,
        gl_account,
        accounted_dr,
        accounted_cr,
        gl_acct_description,
        flv.meaning TRX_NAME,
        nvl(flv.language,'US') language_code,
        ear.customer_name,
        ear.stream_code,
        ear.transaction_number,
        currency_code,
        booked_amount,
        ear.party_id,
        ear.trx_type_id,
        khr_sts.meaning contract_status,
        stream_type_purpose,
        stream_balance,
        org_id,
        ledger_name,
        conversion_rate_date,
        conversion_type,
        conversion_rate,
        entered_dr,
        entered_cr,
        ledger_currency,
        decode(fund_req_num,'0',null,fund_req_num) fund_req_num,
        decode(funded_amount,0,null,funded_amount) funded_amount,
        receipt_amt_applied
FROM    okl_ecc_acct_recon    ear,
        fnd_lookup_values flv
        ,okc_statuses_tl           khr_sts  --bug 30786225
WHERE   flv.lookup_code = ear.trx_name
AND     khr_sts.code = ear.contract_status
AND     flv.language= khr_sts.language
and    flv.lookup_type = 'OKL_TRANSACTION_TYPE_CLASS'
--and    nvl(trx_type_id,1) <>0
and    nvl(flv.language,'US') in('US')
and    ear.processing_status_flag ='N')
PIVOT  (MAX(TRX_NAME) AS TRX_NAME,
        MAX(CONTRACT_STATUS) AS CONTRACT_STATUS
FOR language_code IN('US' "US"))
) x
where
2=2