VIEW: APPS.AR_CC_ERRORS_V

Source

View Text - Preformatted

SELECT NVL(cr.cc_error_code,arp_standard.fnd_message('AR_CC_UNKNOWN_ERROR_CODE')) error_code, NVL(cr.cc_error_text,arp_standard.fnd_message('AR_CC_UNKNOWN_ERROR_TEXT')) error_desc, cr.type category, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CC_TRX_CATEGORY',cr.type) category_dsp, cr.cash_receipt_id trx_id, cr.receipt_number trx_number, cr.receipt_date trx_date, cr.currency_code trx_currency_code, null days_late, party.party_id party_id, party.party_name customer_name, cust.cust_account_id cust_account_id, cust.account_number customer_number, site_uses.site_use_id site_use_id, site_uses.location customer_location, NULL customer_bank_account_id, iby.CARD_NUMBER cc_number, iby.MASKED_CARD_EXPIRYDATE expiration_date, cr.payment_server_order_num payment_server_id, cr.approval_code approval_code, cr.rec_version_number object_version_number FROM ar_cash_receipts cr, ar_cash_receipt_history crh, ar_receipt_methods rm, ar_payment_schedules ps, hz_parties party, hz_cust_accounts cust, hz_cust_site_uses site_uses, ( SELECT c.masked_cc_number CARD_NUMBER, '**/**' MASKED_CARD_EXPIRYDATE, x.TRXN_EXTENSION_ID FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all u, iby_creditcard c WHERE x.instr_assignment_id = u.instrument_payment_use_id AND u.instrument_type = 'CREDITCARD' AND u.instrument_id = c.instrid ) iby WHERE rm.receipt_method_id = cr.receipt_method_id AND cr.cash_receipt_id = crh.cash_receipt_id AND crh.current_record_flag = 'Y' AND crh.status = 'CONFIRMED' AND ps.cash_receipt_id(+) = cr.cash_receipt_id AND cr.pay_from_customer = cust.cust_account_id (+) AND cust.party_id = party.party_id (+) AND cr.customer_site_use_id = site_uses.site_use_id (+) AND cr.payment_trxn_extension_id = iby.TRXN_EXTENSION_ID(+) AND rm.payment_channel_code = 'CREDIT_CARD' AND cr.cc_error_flag = 'Y' AND cr.selected_remittance_batch_id IS NULL UNION ALL Select /*+ leading(trx) */ NVL(trx.cc_error_code,'Unknown') error_code, NVL(trx.cc_error_text,'Unknown Error') error_desc, trx_type.type category, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CC_TRX_CATEGORY',trx_type.type) category_dsp, trx.customer_trx_id trx_id, trx.trx_number trx_number, trx.trx_date trx_date, trx.invoice_currency_code trx_currency_code, (trunc(sysdate)-trunc(ps.due_date)) days_late, party.party_id party_id, party.party_name customer_name, cust.cust_account_id cust_account_id, cust.account_number customer_number, site_uses.site_use_id site_use_id, site_uses.location customer_location, NULL customer_bank_account_id, iby.CARD_NUMBER cc_number, iby.MASKED_CARD_EXPIRYDATE expiration_date, trx.payment_server_order_num payment_server_id, trx.approval_code approval_code, 0 object_version_number FROM ra_customer_trx trx, ra_cust_trx_types trx_type, ar_receipt_methods rm, ar_payment_schedules ps, hz_parties party, hz_cust_accounts cust, hz_cust_site_uses site_uses, ( SELECT c.masked_cc_number CARD_NUMBER, '**/**' MASKED_CARD_EXPIRYDATE, x.TRXN_EXTENSION_ID FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all u, iby_creditcard c WHERE x.instr_assignment_id = u.instrument_payment_use_id AND u.instrument_type = 'CREDITCARD' AND u.instrument_id = c.instrid ) iby WHERE trx.cust_trx_type_id = trx_type.cust_trx_type_id AND trx.cc_error_flag = 'Y' AND trx.receipt_method_id = rm.receipt_method_id AND rm.payment_channel_code = 'CREDIT_CARD' AND ps.status = 'OP' AND PS.gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD') AND ps.selected_for_receipt_batch_id IS NULL AND ps.customer_trx_id = trx.customer_trx_id AND ps.reserved_type IS NULL AND ps.reserved_value IS NULL AND trx.paying_customer_id = cust.cust_account_id (+) AND cust.party_id = party.party_id (+) AND trx.paying_site_use_id = site_uses.site_use_id (+) AND iby.TRXN_EXTENSION_ID(+) = trx.payment_trxn_extension_id

View Text - HTML Formatted

SELECT NVL(CR.CC_ERROR_CODE
, ARP_STANDARD.FND_MESSAGE('AR_CC_UNKNOWN_ERROR_CODE')) ERROR_CODE
, NVL(CR.CC_ERROR_TEXT
, ARP_STANDARD.FND_MESSAGE('AR_CC_UNKNOWN_ERROR_TEXT')) ERROR_DESC
, CR.TYPE CATEGORY
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CC_TRX_CATEGORY'
, CR.TYPE) CATEGORY_DSP
, CR.CASH_RECEIPT_ID TRX_ID
, CR.RECEIPT_NUMBER TRX_NUMBER
, CR.RECEIPT_DATE TRX_DATE
, CR.CURRENCY_CODE TRX_CURRENCY_CODE
, NULL DAYS_LATE
, PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NAME CUSTOMER_NAME
, CUST.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, SITE_USES.SITE_USE_ID SITE_USE_ID
, SITE_USES.LOCATION CUSTOMER_LOCATION
, NULL CUSTOMER_BANK_ACCOUNT_ID
, IBY.CARD_NUMBER CC_NUMBER
, IBY.MASKED_CARD_EXPIRYDATE EXPIRATION_DATE
, CR.PAYMENT_SERVER_ORDER_NUM PAYMENT_SERVER_ID
, CR.APPROVAL_CODE APPROVAL_CODE
, CR.REC_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM AR_CASH_RECEIPTS CR
, AR_CASH_RECEIPT_HISTORY CRH
, AR_RECEIPT_METHODS RM
, AR_PAYMENT_SCHEDULES PS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST
, HZ_CUST_SITE_USES SITE_USES
, ( SELECT C.MASKED_CC_NUMBER CARD_NUMBER
, '**/**' MASKED_CARD_EXPIRYDATE
, X.TRXN_EXTENSION_ID
FROM IBY_FNDCPT_TX_EXTENSIONS X
, IBY_PMT_INSTR_USES_ALL U
, IBY_CREDITCARD C
WHERE X.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID
AND U.INSTRUMENT_TYPE = 'CREDITCARD'
AND U.INSTRUMENT_ID = C.INSTRID ) IBY
WHERE RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS = 'CONFIRMED'
AND PS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID (+)
AND CUST.PARTY_ID = PARTY.PARTY_ID (+)
AND CR.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID (+)
AND CR.PAYMENT_TRXN_EXTENSION_ID = IBY.TRXN_EXTENSION_ID(+)
AND RM.PAYMENT_CHANNEL_CODE = 'CREDIT_CARD'
AND CR.CC_ERROR_FLAG = 'Y'
AND CR.SELECTED_REMITTANCE_BATCH_ID IS NULL UNION ALL SELECT /*+ LEADING(TRX) */ NVL(TRX.CC_ERROR_CODE
, 'UNKNOWN') ERROR_CODE
, NVL(TRX.CC_ERROR_TEXT
, 'UNKNOWN ERROR') ERROR_DESC
, TRX_TYPE.TYPE CATEGORY
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CC_TRX_CATEGORY'
, TRX_TYPE.TYPE) CATEGORY_DSP
, TRX.CUSTOMER_TRX_ID TRX_ID
, TRX.TRX_NUMBER TRX_NUMBER
, TRX.TRX_DATE TRX_DATE
, TRX.INVOICE_CURRENCY_CODE TRX_CURRENCY_CODE
, (TRUNC(SYSDATE)-TRUNC(PS.DUE_DATE)) DAYS_LATE
, PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NAME CUSTOMER_NAME
, CUST.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, SITE_USES.SITE_USE_ID SITE_USE_ID
, SITE_USES.LOCATION CUSTOMER_LOCATION
, NULL CUSTOMER_BANK_ACCOUNT_ID
, IBY.CARD_NUMBER CC_NUMBER
, IBY.MASKED_CARD_EXPIRYDATE EXPIRATION_DATE
, TRX.PAYMENT_SERVER_ORDER_NUM PAYMENT_SERVER_ID
, TRX.APPROVAL_CODE APPROVAL_CODE
, 0 OBJECT_VERSION_NUMBER
FROM RA_CUSTOMER_TRX TRX
, RA_CUST_TRX_TYPES TRX_TYPE
, AR_RECEIPT_METHODS RM
, AR_PAYMENT_SCHEDULES PS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST
, HZ_CUST_SITE_USES SITE_USES
, ( SELECT C.MASKED_CC_NUMBER CARD_NUMBER
, '**/**' MASKED_CARD_EXPIRYDATE
, X.TRXN_EXTENSION_ID
FROM IBY_FNDCPT_TX_EXTENSIONS X
, IBY_PMT_INSTR_USES_ALL U
, IBY_CREDITCARD C
WHERE X.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID
AND U.INSTRUMENT_TYPE = 'CREDITCARD'
AND U.INSTRUMENT_ID = C.INSTRID ) IBY
WHERE TRX.CUST_TRX_TYPE_ID = TRX_TYPE.CUST_TRX_TYPE_ID
AND TRX.CC_ERROR_FLAG = 'Y'
AND TRX.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND RM.PAYMENT_CHANNEL_CODE = 'CREDIT_CARD'
AND PS.STATUS = 'OP'
AND PS.GL_DATE_CLOSED = TO_DATE('4712/12/31'
, 'YYYY/MM/DD')
AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND PS.RESERVED_TYPE IS NULL
AND PS.RESERVED_VALUE IS NULL
AND TRX.PAYING_CUSTOMER_ID = CUST.CUST_ACCOUNT_ID (+)
AND CUST.PARTY_ID = PARTY.PARTY_ID (+)
AND TRX.PAYING_SITE_USE_ID = SITE_USES.SITE_USE_ID (+)
AND IBY.TRXN_EXTENSION_ID(+) = TRX.PAYMENT_TRXN_EXTENSION_ID