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 |
