ECC Lease Contracts, Contract Details, SQL1

Description
Columns: Ecc Spec Id, Contract Id, Contract Number, Event Id, Code Combination Id, Gl Account, Accounted Dr, Accounted Cr, Gl Acct Description, Customer Name ...
Imported from ECC
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,  --bug 30855875 ,30858495
        decode(funded_amount,0,null,funded_amount) funded_amount
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    nvl(flv.lookup_type,'OKL_TRANSACTION_TYPE_CLASS') = 'OKL_TRANSACTION_TYPE_CLASS'
and    nvl(trx_type_id,1) <>0
and    nvl(flv.language,'US') in('US')
and    ear.processing_status_flag ='N'
UNION ALL  --bug 30907814
SELECT spec_id ECC_SPEC_ID,
        contract_id,
        contract_number,
        event_id,
        code_combination_id,
        gl_account,
        accounted_dr,
        accounted_cr,
        gl_acct_description,
        try.name TRX_NAME,
        nvl(khr_sts.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
FROM    okl_ecc_acct_recon    ear,
        okl_trx_types_tl try --,
        ,okc_statuses_tl           khr_sts  --bug 30786225
WHERE   try.name = ear.trx_name
AND     khr_sts.code = ear.contract_status
AND     try.language= khr_sts.language
AND     trx_type_id = 0
and    nvl(try.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"))