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

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