VIEW: APPS.AR_CHARGEBACKS_V

Source

View Text - Preformatted

SELECT CT.ROWID ROW_ID , CT.TRX_NUMBER CB_NUMBER , CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , CT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID , CT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , CT.TRX_DATE TRX_DATE , CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , CT.ATTRIBUTE1 ATTRIBUTE1 , CT.ATTRIBUTE2 ATTRIBUTE2 , CT.ATTRIBUTE3 ATTRIBUTE3 , CT.ATTRIBUTE4 ATTRIBUTE4 , CT.ATTRIBUTE5 ATTRIBUTE5 , CT.ATTRIBUTE6 ATTRIBUTE6 , CT.ATTRIBUTE7 ATTRIBUTE7 , CT.ATTRIBUTE8 ATTRIBUTE8 , CT.ATTRIBUTE9 ATTRIBUTE9 , CT.ATTRIBUTE10 ATTRIBUTE10 , CT.ATTRIBUTE11 ATTRIBUTE11 , CT.ATTRIBUTE12 ATTRIBUTE12 , CT.ATTRIBUTE13 ATTRIBUTE13 , CT.ATTRIBUTE14 ATTRIBUTE14 , CT.ATTRIBUTE15 ATTRIBUTE15 , CT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , CT.LAST_UPDATE_DATE LAST_UPDATE_DATE , CT.LAST_UPDATED_BY LAST_UPDATED_BY , CT.CREATION_DATE CREATION_DATE , CT.CREATED_BY CREATED_BY , CT.CREATED_FROM CREATED_FROM , CT.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE , CT.PROGRAM_ID PROGRAM_ID , CT.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID , CT.REQUEST_ID REQUEST_ID , CT.DOC_SEQUENCE_ID DOC_SEQUENCE_ID , CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE , CT.TERM_ID TERM_ID , CT.COMMENTS CB_COMMENTS , CT.DEFAULT_USSGL_TRX_CODE_CONTEXT DEFAULT_USSGL_TRX_CODE_CONTEXT , CT.DEFAULT_USSGL_TRANSACTION_CODE DEFAULT_USSGL_TRANSACTION_CODE , CT.REASON_CODE REASON_CODE , REASON.MEANING REASON_MEANING , REASON.DESCRIPTION REASON_DESCRIPTION , CTT.NAME TRANSACTION_TYPE , CTT.CREATION_SIGN CREATION_SIGN , CTT.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVER_APPS_FLAG , CTT.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APP_ONLY_FLAG , CTLGD.GL_POSTED_DATE GL_POSTED_DATE , CTLGD.CODE_COMBINATION_ID CODE_COMBINATION_ID , CTLGD.GL_DATE GL_DATE , PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL , PS.AMOUNT_DUE_ORIGINAL * NVL(PS.EXCHANGE_RATE,1) ACCTD_AMOUNT_DUE_ORIGINAL , PS.DUE_DATE DUE_DATE /* USE THESE COLUMNS TO IDENTIFY THE CHARGEBACKS USED FOR */ /* RECEIPT */ , ADJ.ASSOCIATED_CASH_RECEIPT_ID ASSOCIATED_CASH_RECEIPT_ID , ADJ.PAYMENT_SCHEDULE_ID ADJ_PAYMENT_SCHEDULE_ID FROM RA_CUSTOMER_TRX CT , RA_CUST_TRX_LINE_GL_DIST CTLGD , RA_CUST_TRX_TYPES CTT , AR_LOOKUPS REASON , AR_PAYMENT_SCHEDULES PS , AR_ADJUSTMENTS ADJ_DEL , AR_ADJUSTMENTS ADJ WHERE /* CHOOSE THOSE CHARGEBACKS THAT ARE NOT DELETED */ ADJ.CHARGEBACK_CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND ADJ.RECEIVABLES_TRX_ID = -11 AND ADJ_DEL.RECEIVABLES_TRX_ID(+) = -12 AND ADJ_DEL.CUSTOMER_TRX_ID(+) = ADJ.CHARGEBACK_CUSTOMER_TRX_ID AND ADJ_DEL.CUSTOMER_TRX_ID IS NULL /* CHECK OUT THAT CHARGEBACK IS COMPLETE */ AND CT.COMPLETE_FLAG = 'Y' /* CHOOSE THE LATEST ROW FROM RA_CUST_TRX_LINE_GL_DIST */ /* THERE SHOULD BE ONLY ONE WITH LATEST_REC_FLAG=Y FOR EACH CB */ AND CTLGD.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND CTLGD.ACCOUNT_CLASS = 'REC' AND CTLGD.LATEST_REC_FLAG = 'Y' /* RA_CUST_TRX_TYPES */ AND CTT.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID /* REASON CODE */ AND CT.REASON_CODE = REASON.LOOKUP_CODE (+) AND REASON.LOOKUP_TYPE (+) = 'ADJUST_REASON' AND REASON.ENABLED_FLAG (+) = 'Y' /* PAYMENT SCHEDULES */ AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID

View Text - HTML Formatted

SELECT CT.ROWID ROW_ID
, CT.TRX_NUMBER CB_NUMBER
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, CT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CT.TRX_DATE TRX_DATE
, CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CT.ATTRIBUTE1 ATTRIBUTE1
, CT.ATTRIBUTE2 ATTRIBUTE2
, CT.ATTRIBUTE3 ATTRIBUTE3
, CT.ATTRIBUTE4 ATTRIBUTE4
, CT.ATTRIBUTE5 ATTRIBUTE5
, CT.ATTRIBUTE6 ATTRIBUTE6
, CT.ATTRIBUTE7 ATTRIBUTE7
, CT.ATTRIBUTE8 ATTRIBUTE8
, CT.ATTRIBUTE9 ATTRIBUTE9
, CT.ATTRIBUTE10 ATTRIBUTE10
, CT.ATTRIBUTE11 ATTRIBUTE11
, CT.ATTRIBUTE12 ATTRIBUTE12
, CT.ATTRIBUTE13 ATTRIBUTE13
, CT.ATTRIBUTE14 ATTRIBUTE14
, CT.ATTRIBUTE15 ATTRIBUTE15
, CT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CT.LAST_UPDATED_BY LAST_UPDATED_BY
, CT.CREATION_DATE CREATION_DATE
, CT.CREATED_BY CREATED_BY
, CT.CREATED_FROM CREATED_FROM
, CT.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, CT.PROGRAM_ID PROGRAM_ID
, CT.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CT.REQUEST_ID REQUEST_ID
, CT.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, CT.TERM_ID TERM_ID
, CT.COMMENTS CB_COMMENTS
, CT.DEFAULT_USSGL_TRX_CODE_CONTEXT DEFAULT_USSGL_TRX_CODE_CONTEXT
, CT.DEFAULT_USSGL_TRANSACTION_CODE DEFAULT_USSGL_TRANSACTION_CODE
, CT.REASON_CODE REASON_CODE
, REASON.MEANING REASON_MEANING
, REASON.DESCRIPTION REASON_DESCRIPTION
, CTT.NAME TRANSACTION_TYPE
, CTT.CREATION_SIGN CREATION_SIGN
, CTT.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVER_APPS_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APP_ONLY_FLAG
, CTLGD.GL_POSTED_DATE GL_POSTED_DATE
, CTLGD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CTLGD.GL_DATE GL_DATE
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_ORIGINAL * NVL(PS.EXCHANGE_RATE
, 1) ACCTD_AMOUNT_DUE_ORIGINAL
, PS.DUE_DATE DUE_DATE /* USE THESE COLUMNS TO IDENTIFY THE CHARGEBACKS USED FOR */ /* RECEIPT */
, ADJ.ASSOCIATED_CASH_RECEIPT_ID ASSOCIATED_CASH_RECEIPT_ID
, ADJ.PAYMENT_SCHEDULE_ID ADJ_PAYMENT_SCHEDULE_ID
FROM RA_CUSTOMER_TRX CT
, RA_CUST_TRX_LINE_GL_DIST CTLGD
, RA_CUST_TRX_TYPES CTT
, AR_LOOKUPS REASON
, AR_PAYMENT_SCHEDULES PS
, AR_ADJUSTMENTS ADJ_DEL
, AR_ADJUSTMENTS ADJ
WHERE /* CHOOSE THOSE CHARGEBACKS THAT ARE NOT DELETED */ ADJ.CHARGEBACK_CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND ADJ.RECEIVABLES_TRX_ID = -11
AND ADJ_DEL.RECEIVABLES_TRX_ID(+) = -12
AND ADJ_DEL.CUSTOMER_TRX_ID(+) = ADJ.CHARGEBACK_CUSTOMER_TRX_ID
AND ADJ_DEL.CUSTOMER_TRX_ID IS NULL /* CHECK OUT THAT CHARGEBACK IS COMPLETE */
AND CT.COMPLETE_FLAG = 'Y' /* CHOOSE THE LATEST ROW
FROM RA_CUST_TRX_LINE_GL_DIST */ /* THERE SHOULD BE ONLY ONE WITH LATEST_REC_FLAG=Y FOR EACH CB */
AND CTLGD.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CTLGD.ACCOUNT_CLASS = 'REC'
AND CTLGD.LATEST_REC_FLAG = 'Y' /* RA_CUST_TRX_TYPES */
AND CTT.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID /* REASON CODE */
AND CT.REASON_CODE = REASON.LOOKUP_CODE (+)
AND REASON.LOOKUP_TYPE (+) = 'ADJUST_REASON'
AND REASON.ENABLED_FLAG (+) = 'Y' /* PAYMENT SCHEDULES */
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID