AR Bank Risk

select
        bra1.bank_name,
        bra1.bank_branch_name,
        cba1.bank_account_name,
        cba1.bank_account_num,
        bra1.address_line1,
        bra1.address_line2,
        bra1.address_line3,
        bra1.zip,
        bra1.city,
        bra1.state,
        bra1.country,
        bat.name,
        cas.currency_code,
        crh1.trx_date,
        cas.receipt_number,
        pay.due_date,
  decode(bra2.instrument_type,'BANKACCOUNT',bra2.bank_name,'CREDITCARD',bra2.card_issuer_name,'DEBITCARD',bra2.card_issuer_name,null) bank_name,
       cas.amount,
        (crh1.acctd_amount + nvl(crh1.acctd_factor_discount_amount,0)) func_curr
from    ar_cash_receipt_history crh1,
        ar_cash_receipts cas,
        ar_payment_schedules pay,
        ar_batches bat,
        ce_bank_branches_v bra1,
                ce_bank_accounts    cba1,
        ce_bank_acct_uses  acc1,
         iby_trxn_extensions_v                 extn,
         iby_fndcpt_payer_assgn_instr_v        bra2
where   bra1.branch_party_id            = :P_bnk_branch_id
&lp_bank_account
and     crh1.cash_receipt_id            = cas.cash_receipt_id
and     crh1.factor_flag                = 'Y'
and     crh1.current_record_flag        = 'Y'
and     crh1.status             ='CLEARED'
  and (cas.cash_receipt_id, bat.batch_id) in
      (select distinct crh2.cash_receipt_id, crh2.batch_id from
       ar_cash_receipt_history crh2
               where crh2 . status = 'REMITTED' )
and     cas.remit_bank_acct_use_id      = acc1.bank_acct_use_id
and     cba1.bank_branch_id             = bra1.branch_party_id
and           cba1.bank_account_id =         acc1.bank_account_id
and     cas.payment_trxn_extension_id    = extn.trxn_extension_id(+)
and     extn.instrument_id                    = bra2.instrument_id(+)
and     cas.cash_receipt_id             = pay.cash_receipt_id(+)
 order by bra1.bank_name, bra1.bank_branch_name,
 cba1.bank_account_name, bat.name, pay.due_date