AR Bills Receivable Remittance Batch Management

SELECT
trx.customer_trx_id     br_id,
trx.trx_number  br_number,
aba.bank_name   br_bank_name,
aba.bank_account_name br_bank_account_name,
substrb(party.party_name,1,50)  br_customer_name,
rc.account_number       br_customer_number,
bat.batch_id    br_batch_id,
trx.term_due_date       br_maturity_date,
sched.amount_due_remaining      br_amount,
rmt.name                br_pay_mtd,
rmethod_lc.meaning    br_remit_mtd
FROM
hz_cust_accounts rc,
hz_parties party,
ra_customer_trx trx,
ar_payment_schedules sched,
ar_batches bat,
ar_receipt_methods rmt,
iby_trxn_extensions_v        extn,
iby_ext_bank_accounts_v      aba,
ar_lookups rmethod_lc
WHERE
trx.customer_trx_id = sched.customer_trx_id
and rc.cust_account_id = trx.drawee_id
and rc.party_id = party.party_id
and sched.reserved_type ='REMITTANCE'
and sched.reserved_value = bat.batch_id
AND  trx.payment_trxn_extension_id = extn.trxn_extension_id(+)
AND  extn.instrument_id = aba.bank_account_id(+) 
and rmethod_lc.lookup_code(+)       = bat.remit_method_code
and rmethod_lc.lookup_type(+)       = 'REMITTANCE_METHOD'
and bat.receipt_method_id = rmt.receipt_method_id and
bat.batch_id = :B_batch_id
UNION
SELECT
trx.customer_trx_id     br_id,
trx.trx_number  br_number,
aba.bank_name   br_bank_name,
aba.bank_account_name br_bank_account_name,
substrb(party.party_name,1,50)        br_customer_name,
rc.account_number      br_customer_number,
bat.batch_id    br_batch_id,
trx.term_due_date       br_maturity_date,
sched.amount_due_remaining   br_amount,
rmt.name                br_pay_mtd,
rmethod_lc.meaning    br_remit_mtd
FROM
hz_cust_accounts rc,
hz_parties party,
ra_customer_trx trx,
ar_batches bat,
ar_receipt_methods rmt,
iby_trxn_extensions_v        extn,
iby_ext_bank_accounts_v      aba,
ar_lookups rmethod_lc,
ar_transaction_history rah,
ar_payment_schedules sched
WHERE   bat.batch_id = rah.batch_id
AND trx.customer_trx_id = sched.customer_trx_id
AND        ((bat.remit_method_code = 'STANDARD' AND rah.event = 'REMITTED')
          OR (bat.remit_method_code = 'FACTORING'
                 AND bat.with_recourse_flag = 'Y'
                 AND rah.event = 'FACTORED')
          OR (bat.remit_method_code = 'FACTORING'
                 AND bat.with_recourse_flag = 'N'
                 AND rah.event = 'CLOSED'))
AND     rah.customer_trx_id  = trx.customer_trx_id
AND  trx.payment_trxn_extension_id = extn.trxn_extension_id(+)
AND  extn.instrument_id = aba.bank_account_id(+) 
and rmethod_lc.lookup_code(+)       = bat.remit_method_code
and rmethod_lc.lookup_type(+)       = 'REMITTANCE_METHOD'
and bat.receipt_method_id = rmt.receipt_method_id
and  rc.cust_account_id = trx.drawee_id
and rc.party_id = party.party_id 
 and bat.batch_id=:B_batch_id