AR Automatic Receipts/Remittances Execution

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Automatic Receipts/Remittances Execution Report (XML)
Short Name: ARZCARPO_XML
DB package: AR_ARZCARPO_XMLP_PKG
SELECT	substrb(party.party_name,1,50	)		C_CUSTOMER_NAME, 
	c.account_number				C_CUSTOMER_NUMBER, 
	su.location					C_LOCATION, 
	trx.trx_number				C_TRX_NUMBER, 
	nvl(ex.due_date, trunc(sysdate))			C_DUE_DATE, 
Decode(ext.instrument_type,'BANKACCOUNT',ext.bank_name,'CREDITCARD',ext.card_issuer_name,null)	C_CUSTOMER_BANK_NAME, 
	ex.exception_code				C_EXCEPTION_CODE,
                ex.additional_message			C_ADDL_MESSAGE,
                trx.cc_error_code                                                      CC_ERROR_CODE,
                trx.cc_error_text                                                       CC_ERROR_TEXT,
                 arp_correct_cc_errors.cc_mapping_exist(trx.cc_error_code,ps.class,trx.receipt_method_id) CC_DISPLAY_FLAG,
                trx.cc_error_flag  CC_ERROR_FLAG, 
	AR_ARZCARPO_XMLP_PKG.cf_1formula(arp_correct_cc_errors.cc_mapping_exist( trx.cc_error_code , ps.class , trx.receipt_method_id )) CC_ERROR_DISPLAY_FORMULA, 
	AR_ARZCARPO_XMLP_PKG.c_exception_meaningformula(ex.exception_code, trx.cc_error_code, trx.cc_error_flag, trx.cc_error_text, ex.additional_message) C_EXCEPTION_MEANING
FROM 	ar_autorec_exceptions ex, ar_payment_schedules ps, 
	ra_customer_trx trx, hz_cust_accounts c,
	hz_parties party,
                hz_cust_site_uses su,
	iby_trxn_extensions_v ext
WHERE	ex.payment_schedule_id = ps.payment_schedule_id
AND	ps.customer_trx_id = trx.customer_trx_id
AND	trx.paying_customer_id = c.cust_account_id
AND	c.party_id = party.party_id
AND	trx.paying_site_use_id = su.site_use_id
AND         trx.payment_trxn_extension_id = ext.trxn_extension_id
AND	ex.batch_id = :p_batch_id
AND     ex.request_id = :p_request_id_main
AND     :p_process_type= 'RECEIPT'
UNION
SELECT	substrb(party.party_name,1,50)		C_CUSTOMER_NAME, 
	c.account_number				C_CUSTOMER_NUMBER, 
	su.location				C_LOCATION, 
	cr.receipt_number				C_TRX_NUMBER, 
	nvl(ex.due_date, trunc(sysdate))		C_DUE_DATE, 
	decode(ext.instrument_type,'BANKACCOUNT',bank_name,'CREDITCARD',card_issuer_name,null)				C_CUSTOMER_BANK_NAME, 
	ex.exception_code				C_EXCEPTION_CODE,
                ex.additional_message			C_ADDL_MESSAGE,
                 cr.cc_error_code                                                      CC_ERROR_CODE,
                 cr.cc_error_text                                                       CC_ERROR_TEXT,
           arp_correct_cc_errors.cc_mapping_exist(cr.cc_error_code,cr.type,cr.receipt_method_id) CC_DISPLAY_FLAG,
                cr.cc_error_flag      CC_ERROR_FLAG, 
	AR_ARZCARPO_XMLP_PKG.cf_1formula(arp_correct_cc_errors.cc_mapping_exist ( cr.cc_error_code , cr.type , cr.receipt_method_id )) CC_ERROR_DISPLAY_FORMULA, 
	AR_ARZCARPO_XMLP_PKG.c_exception_meaningformula(ex.exception_code, cr.cc_error_code, cr.cc_error_flag, cr.cc_error_text, ex.additional_message) C_EXCEPTION_MEANING
FROM 	ar_autorec_exceptions ex, 
	ar_cash_receipts cr, hz_cust_accounts c, hz_parties party,
                hz_cust_site_uses su,
               iby_trxn_extensions_v ext
WHERE	ex.cash_receipt_id = cr.cash_receipt_id
AND	cr.pay_from_customer = c.cust_account_id
AND	c.party_id = party.party_id
AND	cr.customer_site_use_id = su.site_use_id(+)
AND          cr.payment_trxn_extension_id = ext.trxn_extension_id 
AND	ex.batch_id = :p_batch_id
AND     ex.request_id = :p_request_id_main
AND     :p_process_type='REMIT'
ORDER BY 1,2,3,4