AR Automatic Clearing for Receipts

select
        ba.bank_acct_use_id     bank_account_id,
        bb.bank_name            bank_name,
        bb.branch_number                bank_number,
        cba.bank_account_num    bank_account_number,
        cba.bank_account_name   bank_account_name,
        cba.currency_code       bank_account_currency,
        l1.meaning||
                decode( crh.status,
                        'CLEARED', decode( crh.factor_flag,
                                        'Y', ' ' || l2.meaning,
                                        ''),
                        '') status,
         cr.receipt_number                                           receipt_number,
         to_char(ps.due_date,'dd-mm-yyyy')              maturity_date,
         rm.name                                                        payment_method,
         cr.currency_code                                                         currency_code,
        crh.amount                                                  amount,
        crh.acctd_amount                                            func_amount,
	AR_ARZACS_XMLP_PKG.C_REC1_p C_REC1
from
        ar_lookups l1,
        ar_lookups l2,
        ar_payment_schedules ps,
        ar_cash_receipts cr,
        ar_cash_receipt_history crh,
        ar_receipt_methods rm,
        ce_bank_accounts cba,
        ce_bank_acct_uses ba,
        ce_bank_branches_v bb
 where
   &p_auto_clear_receipts
    crh.cash_receipt_id = cr.cash_receipt_id
   and  cr.cash_receipt_id = ps.cash_receipt_id(+)
   and  cr.receipt_method_id = rm.receipt_method_id
   and  cr.remit_bank_acct_use_id = ba.bank_acct_use_id (+)
   and  ba.bank_account_id = cba.bank_account_id (+)
   and  bb.bank_party_id = cba.bank_branch_id
   and  crh.status = l1.lookup_code
   and  l1.lookup_type = 'RECEIPT_CREATION_STATUS'
   and  l2.lookup_type = 'REMITTANCE_METHOD'
   and  l2.lookup_code = 'FACTORING'
   and  crh.cash_receipt_history_id  > :MAX_CRH_ID
  order by
        bb.bank_name,
        cba.bank_account_num,
        crh.status,
        decode(crh.factor_flag, 'Y', 1, 'N', 2),
        cr.currency_code,
        cr.receipt_number