ECC Lease Contracts, Account Balances Summary, SQL1

Description
Columns: Subledger Balance, Ecc Spec Id, Stream Full Value, Stream Balance, Org Id, Ledger Name, Ledger Currency, Reconciliation Balance, Us Account Group Code ...
Imported from ECC
SELECT * FROM
(SELECT  sum((case when trx_name = 'ACC_STM_BAL'
             then 0
             when trx_name = 'BILL_STM_BAL'
             then 0
             when account_group_code ='UNEARNED_REVENUE'
             then nvl(Entered_cr ,0) - nvl(entered_dr ,0)
             else nvl(entered_dr ,0) - nvl(entered_cr ,0)
        end)) subledger_balance
       ,ort.account_group_code||'-'||ecc.org_id ecc_spec_id
       ,sum (
             (
             SELECT  sum(nvl(amount,0))
             FROM    okl_streams str
                    ,okl_strm_elements ste
                    ,okl_strm_type_b sty
             WHERE   str.id = ste.stm_id
             AND     str.sty_id = sty.id
             AND     str.khr_id = ecc.contract_id
             AND     str.say_code = 'CURR'
             AND     sty.code = ecc.stream_code
             )) stream_full_value
       ,sum(ecc.stream_balance) stream_balance
         ,flv.meaning account_group_code
         ,org_id
         ,ledger_name
         ,ledger_currency
         ,flv.language language_code
         ,(sum((case when trx_name = 'ACC_STM_BAL'
             then 0
             when trx_name = 'BILL_STM_BAL'
             then 0
             when account_group_code ='UNEARNED_REVENUE'
             then nvl(entered_cr ,0) - nvl(entered_dr ,0)
             else nvl(entered_dr ,0) - nvl(entered_cr ,0)
        end))-sum(nvl(ecc.stream_balance,0))) reconciliation_balance
FROM    okl_ecc_acct_recon ecc
       ,okl_report_trx_params ort
       ,okl_reports_b orb
       ,okl_strm_type_b sty
       ,fnd_flex_values ffv
       ,fnd_lookup_values flv
WHERE   processing_status_flag IN ('N','I')
AND     ort.report_id = orb.report_id
AND     orb.report_type_code = 'RECONCILIATION'
AND     ort.sty_id (+) = sty.id
AND     ecc.sty_id = sty.id
AND     ecc.gl_account = ffv.flex_value
AND     ffv.flex_value_id(+) = ort.account_id
AND     flv.lookup_code = ort.account_group_code
AND     flv.lookup_type = 'OKL_REPORT_ACCNT_GROUP'
AND     flv.language in('US')
GROUP BY ort.account_group_code,org_id,ledger_name,ledger_currency,flv.language,flv.meaning)
PIVOT    (MAX(account_group_code) AS ACCOUNT_GROUP_CODE
         for language_code in ('US' "US"))