ECC Lease Contracts, Contract Account Balances Summary

select
x.*
from
(
SELECT * from
(SELECT CONTRACT_NUMBER,
GL_ACCOUNT,
GL_ACCT_DESCRIPTION,
ENTERED_DR,
ENTERED_CR,
STREAM_CODE,
decode(deal_type,'LOAN-REVOLVING',decode(stream_type_purpose,'PRINCIPAL_CATCHUP',0,
                                  (select sum(amount) from okl_trx_ap_invs_all_b
                                  where khr_id =ocs.contract_id
                                  and trx_status_code in('PROCESSED','APPROVED')
                                  and ocs.stream_type_purpose='PRINCIPAL_BALANCE')),
                 'LOAN',        decode(stream_type_purpose,'PRINCIPAL_CATCHUP',0,stream_full_value),
                                  stream_full_value) STREAM_FULL_VALUE,
subledger_balance,
spec_id  ecc_spec_id,
contract_status,
stream_type_purpose,
decode(stream_type_purpose,'FUNDING',decode(fundable_amount,0,0,stream_balance),
                            stream_balance) stream_balance,
(flv.meaning) account_group_code,
abs_subledger_balance,
org_id,
ledger_name,
ledger_currency,
conversion_rate_date,
conversion_type,
conversion_rate,
decode(stream_type_purpose,'FUNDING',decode(fundable_amount,0,0,(stream_balance * nvl(conversion_rate,1))),
                           (stream_balance*nvl(conversion_rate,1)))acctd_stream_balance, -- bug 30719664
(stream_full_value * nvl(conversion_rate,1)) acctd_stream_full_value,
(subledger_balance * nvl(conversion_rate,1)) acctd_subledger_balance,
flv.language language_code,
fundable_amount,
funded_amount,
fundable_remaining,
fundable_remaining * nvl(conversion_rate,1) acctd_fundable_amount,
ocs.account_group_code account_code,
decode(account_group_code,'UNBILLED_RECEIVABLES',(nvl(stream_balance,0)-nvl(subledger_balance,0))
                         ,'RESIDUAL',abs(nvl(stream_balance,0)-nvl(subledger_balance,0))
                         ,'UNEARNED_REVENUE',((nvl(stream_balance,0)-nvl(subledger_balance,0)))
                         ,'FUNDABLE_ASSET',abs((nvl(stream_balance,0)-nvl(subledger_balance,0)))
                         ,'LOAN_PRINCIPAL',((nvl(stream_balance,0)-nvl(subledger_balance,0)))
                         ,'REV_LOAN_PRINCIPAL',((nvl(stream_balance,0)-nvl(subledger_balance,0)))
                         ,nvl(stream_balance,0)) unbilled_receivable_bar,
decode(account_group_code,'UNBILLED_RECEIVABLES',(nvl(stream_balance,0)-nvl(subledger_balance,0))
                         ,'RESIDUAL',abs(nvl(stream_balance,0)-nvl(subledger_balance,0))
                         ,'UNEARNED_REVENUE',((nvl(stream_balance,0)-nvl(subledger_balance,0)))
                         ,'FUNDABLE_ASSET',abs((nvl(stream_balance,0)-nvl(subledger_balance,0)))
                         ,'LOAN_PRINCIPAL',((nvl(stream_balance,0)-nvl(subledger_balance,0)))
                         ,'REV_LOAN_PRINCIPAL',((nvl(stream_balance,0)-nvl(subledger_balance,0)))
                         ,nvl(stream_balance,0))* nvl(conversion_rate,1) acctd_recv_bar,
currency_code,
nvl(subledger_balance,0)-nvl(stream_balance,0) reconciliation_balance,
(select max(actual_principal_balance_date) from
          OKL_CNTRCT_BALANCES_ALL where khr_id= ocs.contract_id) principal_update_date,
interest_calculation_method,
revenue_recognition_method,
bc.meaning book_classification,
deal_type,
principal_basis_code,
pbc.meaning principal_basis,
receipt_amt_applied
from OKL_ECC_ARCON_CONTRACT_SUMM ocs,
    fnd_lookup_values flv,
    fnd_lookup_values  bc,
    fnd_lookup_values pbc
    where 1 =1
    and   flv.lookup_code = ocs.account_group_code
    and   flv.lookup_type = 'OKL_REPORT_ACCNT_GROUP'
    and    deal_type= bc.lookup_code
    and    bc.lookup_type='OKL_BOOK_CLASS'
    and    bc.language= flv.language
    and    nvl(pbc.lookup_type,'OKL_PRINCIPAL_BASIS_CODE')='OKL_PRINCIPAL_BASIS_CODE'
    and    pbc.lookup_code(+) = principal_basis_code
    and    nvl(pbc.language,flv.language)= flv.language --included nvl BUG 31665696
    and    deal_type in('LOAN','LEASEDF','LOAN-REVOLVING')
    and   flv.language in('US')
 
   )
PIVOT
         (MAX(account_group_code) AS ACCOUNT_GROUP_CODE,
         MAX(book_classification) AS BOOK_CLASSIFICATION,
         MAX(principal_basis) AS principal_basis
         -- MAX(stream_description) AS stream_description,
        for language_code in ('US' "US"))
) x
where
2=2