AR Format Automatic Receipts

➜ Back to Library
Description
Categories: BI Publisher
Application: Receivables
Source: Format Automatic Receipts (XML)
Short Name: ARXAPFRC_XML
DB package: AR_ARXAPFRC_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Download
 
Related Blitz Reports
AR Miscellaneous Receipts AR Customer Upload AR Transactions and Payments AR Direct Debit Pre-Notification Letter AR Unapplied Receipts Register AR Transaction Detail AR Automatic Receipts/Remittances Execution GL Account Analysis (Distributions) XXAR Transaction Upload
Blitz Report™

Blitz Report™ provides multiple benefits: