AR Bills Receivable Remittance Batch Management

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Bills Receivable Remittance Batch Management Report (XML)
Short Name: ARBRRMBM_XML
DB package: AR_ARBRRMBM_XMLP_PKG
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
Parameter Name SQL text Validation
Ledger Id
 
LOV Oracle
Remittance Bank Account
 
LOV Oracle
Remittance Bank Branch
 
LOV Oracle
Remittance Bank
 
LOV Oracle
Remittance Method
 
LOV Oracle
Include Formatted Batches
 
LOV Oracle
Batch Name High
 
LOV Oracle
Batch Name Low
 
LOV Oracle
Deposit Number High
 
LOV Oracle
Deposit Number Low
 
LOV Oracle
Remittance Date To
 
Date
Remittance Date From
 
Date
Summary or Detailed
 
LOV Oracle
Status
 
LOV Oracle
Order By
 
LOV Oracle