View: AR_CC_ERRORS_V

Product:	AR - Receivables
Description:	This view has been used in credit error card correction OA page.
Implementation/DBA Data:	APPS.AR_CC_ERRORS_V

View Text

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.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
, IBY_TRXN_EXTENSIONS_V 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 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.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
, IBY_TRXN_EXTENSIONS_V 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(+)

Columns