SELECT arf.customer_name C_CUSTOMER_NAME,
arf.receipt_number C_RECEIPT_NUMBER,
arf.receipt_date C_RECEIPT_DATE,
arf.maturity_date C_DUE_DATE,
arf.customer_number C_CUSTOMER_NUMBER,
arf.customer_site_location C_CUSTOMER_SITE,
decode(extn.instrument_type,'BANKACCOUNT',ba.branch_number,null) C_CUST_BANK_BRANCH,
decode(extn.instrument_type,'BANKACCOUNT',ba.branch_number,null)||' '||
arf.customer_bank_account_number C_MICR_NUMBER,
decode(extn.instrument_type,'BANKACCOUNT',bb.address_line1,null) C_CUST_BANK_ADDR1,
decode(extn.instrument_type,'BANKACCOUNT',bb.address_line2,null) C_CUST_BANK_ADDR2,
decode(extn.instrument_type,'BANKACCOUNT',bb.address_line3,null) C_CUST_BANK_ADDR3,
decode(extn.instrument_type,'BANKACCOUNT',bb.city,null) C_CUST_BANK_CITY,
decode(extn.instrument_type,'BANKACCOUNT',bb.state,null) C_CUST_BANK_STATE,
decode(extn.instrument_type,'BANKACCOUNT',bb.zip,null) C_CUST_BANK_ZIP, trx.trx_number C_INV_NUMBER,
trx.trx_date C_INV_DATE,
ra.amount_applied C_INV_APPLIED_AMT,
arf.receipt_currency C_CURRENCY,
cur.name C_CURRENCY_NAME,
loc.address1 C_CUST_ADDRESS1,
loc.address2 C_CUST_ADDRESS2,
loc.address3 C_CUST_ADDRESS3,
loc.city C_CUST_CITY,
loc.state C_CUST_STATE,
loc.postal_code C_CUST_POSTAL_CODE,
cr.amount C_RECEIPT_AMT
FROM ar_automatic_receipts_format_v arf,
ar_receivable_applications ra,
ra_customer_trx trx,
iby_ext_bank_accounts_v ba,
iby_trxn_extensions_v extn,
ce_bank_branches_v bb,
ar_cash_receipts cr,
hz_cust_site_uses su,
hz_cust_acct_sites ad,
hz_party_sites party_site,
hz_locations loc,
fnd_currencies_vl cur
WHERE ra.cash_receipt_id = arf.cash_receipt_id
AND ra.applied_customer_trx_id = trx.customer_trx_id
and arf.payment_trxn_extension_id=extn.trxn_extension_id
and decode(instrument_type,'BANKACCOUNT',extn.instrument_id,null) = ba.ext_bank_account_id(+)
AND ba.branch_party_id = bb.branch_party_id(+)
AND arf.cash_receipt_id = cr.cash_receipt_id
AND cr.customer_site_use_id = su.site_use_id(+)
AND su.cust_acct_site_id = ad.cust_acct_site_id(+)
AND ad.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND arf.receipt_currency = cur.currency_code
AND arf.receipt_batch_id = :p_batch_id |
