VIEW: APPS.AR_BOE_AUTO_RECEIPTS_V

Source

View Text - Preformatted

SELECT PS.ROWID ROW_ID , -1 CASH_RECEIPT_ID , NULL RECEIPT_NUMBER , CT.TRX_NUMBER TRX_NUMBER , PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID , PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER , PS.INVOICE_CURRENCY_CODE CURRENCY_CODE , CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , CT.BATCH_SOURCE_ID TRX_BATCH_SOURCE_ID , CT_BS.NAME TRX_BATCH_SOURCE_NAME , CT.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER , PS.AMOUNT_DUE_REMAINING AMOUNT_APPLIED , CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID , CTT.NAME CUSTOMER_TRX_TYPE_NAME , CT.TRX_DATE TRX_DATE , PS.DUE_DATE TERM_DUE_DATE , PS.CUSTOMER_ID CUSTOMER_ID , SUBSTRB(CU_PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , CU.ACCOUNT_NUMBER CUSTOMER_NUMBER , PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID , SU.LOCATION BILLING_LOCATION , CT.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID , CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID , CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID , CT.PAYING_CUSTOMER_ID PAYING_CUSTOMER_ID , SUBSTRB(PAY_CU_PARTY.PARTY_NAME,1,50) PAYING_CUSTOMER_NAME , PAY_CU.ACCOUNT_NUMBER PAYING_CUSTOMER_NUMBER , NULL BANK_BRANCH_ID , BHZP.PARTY_NAME BANK_NAME /*bug6771709*/ , BRHZP.PARTY_NAME BANK_BRANCH_NAME , DECODE (NVL(U.INSTRUMENT_TYPE, P.INSTRUMENT_TYPE), 'BANKACCOUNT', B.MASKED_BANK_ACCOUNT_NUM, 'CREDITCARD', C.MASKED_CC_NUMBER, NULL) BANK_ACCOUNT_NUMBER , BAT.BATCH_ID BATCH_ID , BAT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS , BAT.TYPE TYPE , BAT.NAME BATCH_NAME , BAT.BATCH_DATE BATCH_DATE , PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , NULL RA_STATUS , -1 RECEIVABLE_APPLICATION_ID , -1 APPLIED_PAYMENT_SCHEDULE_ID , -1 CASH_RECEIPT_HISTORY_ID , NULL CRH_STATUS , 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 , CI.CONS_INV_ID CONS_INV_ID , CI.CONS_BILLING_NUMBER CONS_BILLING_NUMBER , CT.PAYMENT_TRXN_EXTENSION_ID PAYMENT_TRXN_EXTENSION_ID FROM AR_PAYMENT_SCHEDULES PS, RA_CUSTOMER_TRX CT, RA_BATCH_SOURCES CT_BS, RA_CUST_TRX_TYPES CTT, HZ_CUST_ACCOUNTS CU, HZ_PARTIES CU_PARTY, HZ_CUST_ACCOUNTS PAY_CU, HZ_PARTIES PAY_CU_PARTY, HZ_CUST_SITE_USES SU, IBY_CREDITCARD C, IBY_EXT_BANK_ACCOUNTS B, IBY_FNDCPT_PMT_CHNNLS_B P, IBY_FNDCPT_PMT_CHNNLS_TL PT, IBY_FNDCPT_TX_EXTENSIONS X, IBY_PMT_INSTR_USES_ALL U, FND_APPLICATION A, HZ_PARTIES BHZP, HZ_PARTIES BRHZP, AR_BATCHES BAT, AR_CONS_INV CI WHERE CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID AND CT_BS.BATCH_SOURCE_ID = CT.BATCH_SOURCE_ID AND CTT.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID AND CTT.TYPE <> 'BR' /* 20-APR-2000 J Rautiainen BR Implementation */ AND CU.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID AND CU.PARTY_ID = CU_PARTY.PARTY_ID(+) AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID AND PAY_CU.CUST_ACCOUNT_ID(+) = CT.PAYING_CUSTOMER_ID AND PAY_CU.PARTY_ID = PAY_CU_PARTY.PARTY_ID(+) AND BAT.BATCH_ID = PS.SELECTED_FOR_RECEIPT_BATCH_ID AND BAT.BATCH_APPLIED_STATUS IN ( 'STARTED_CREATION', 'COMPLETED_CREATION', 'STARTED_APPROVAL' ) AND (X.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID(+)) AND (DECODE(U.INSTRUMENT_TYPE, 'CREDITCARD',U.INSTRUMENT_ID, NULL) = C.INSTRID(+)) AND (DECODE(U.INSTRUMENT_TYPE, 'BANKACCOUNT',U.INSTRUMENT_ID, NULL) = B.EXT_BANK_ACCOUNT_ID(+)) AND (X.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE) AND (X.ORIGIN_APPLICATION_ID = A.APPLICATION_ID) AND (P.PAYMENT_CHANNEL_CODE = PT.PAYMENT_CHANNEL_CODE) AND (PT.LANGUAGE = USERENV('LANG')) AND TRXN_EXTENSION_ID(+) = CT.PAYMENT_TRXN_EXTENSION_ID AND B.BANK_ID=BHZP.PARTY_ID(+) AND B.BRANCH_ID=BRHZP.PARTY_ID(+) AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID UNION ALL /* Bug 2417494 */ SELECT PS.ROWID ROW_ID, CRH.CASH_RECEIPT_ID CASH_RECEIPT_ID, CR.RECEIPT_NUMBER RECEIPT_NUMBER, CT.TRX_NUMBER TRX_NUMBER, PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, CT.BATCH_SOURCE_ID TRX_BATCH_SOURCE_ID, CT_BS.NAME TRX_BATCH_SOURCE_NAME, CT.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER, RA.AMOUNT_APPLIED AMOUNT_APPLIED, CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID, CTT.NAME CUSTOMER_TRX_TYPE_NAME, CT.TRX_DATE TRX_DATE, PS.DUE_DATE TERM_DUE_DATE, PS.CUSTOMER_ID CUSTOMER_ID, SUBSTRB(CU_PARTY.PARTY_NAME,1,50) CUSTOMER_NAME, CU.ACCOUNT_NUMBER CUSTOMER_NUMBER, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, SU.LOCATION BILLING_LOCATION, CT.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID, CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID, CT.PAYING_CUSTOMER_ID PAYING_CUSTOMER_ID, SUBSTRB(PAY_CU_PARTY.PARTY_NAME,1,50) PAYING_CUSTOMER_NAME, PAY_CU.ACCOUNT_NUMBER PAYING_CUSTOMER_NUMBER, NULL BANK_BRANCH_ID, BHZP.PARTY_NAME BANK_NAME, /*bug6771709*/ BRHZP.PARTY_NAME BANK_BRANCH_NAME, DECODE (NVL(U.INSTRUMENT_TYPE, P.INSTRUMENT_TYPE), 'BANKACCOUNT', B.MASKED_BANK_ACCOUNT_NUM, 'CREDITCARD', C.MASKED_CC_NUMBER, NULL) BANK_ACCOUNT_NUMBER, BAT.BATCH_ID BATCH_ID, BAT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS, BAT.TYPE TYPE, BAT.NAME BATCH_NAME, BAT.BATCH_DATE BATCH_DATE, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, RA.STATUS RA_STATUS, RA.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID, RA.APPLIED_PAYMENT_SCHEDULE_ID APPLIED_PAYMENT_SCHEDULE_ID, CRH.CASH_RECEIPT_HISTORY_ID CASH_RECEIPT_HISTORY_ID, CRH.STATUS CRH_STATUS, 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, CI.CONS_INV_ID CONS_INV_ID, CI.CONS_BILLING_NUMBER CONS_BILLING_NUMBER, CT.PAYMENT_TRXN_EXTENSION_ID PAYMENT_TRXN_EXTENSION_ID FROM AR_CASH_RECEIPT_HISTORY CRH, AR_CASH_RECEIPTS CR, AR_RECEIVABLE_APPLICATIONS RA, AR_PAYMENT_SCHEDULES PS, RA_CUSTOMER_TRX CT, RA_BATCH_SOURCES CT_BS, RA_CUST_TRX_TYPES CTT, HZ_CUST_ACCOUNTS CU, HZ_PARTIES CU_PARTY, HZ_CUST_ACCOUNTS PAY_CU, HZ_PARTIES PAY_CU_PARTY, HZ_CUST_SITE_USES SU, IBY_CREDITCARD C, IBY_EXT_BANK_ACCOUNTS B, IBY_FNDCPT_PMT_CHNNLS_B P, IBY_FNDCPT_PMT_CHNNLS_TL PT, IBY_FNDCPT_TX_EXTENSIONS X, IBY_PMT_INSTR_USES_ALL U, FND_APPLICATION A, HZ_PARTIES BHZP, HZ_PARTIES BRHZP, AR_BATCHES BAT, AR_CONS_INV CI WHERE CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND BAT.BATCH_ID = CRH.BATCH_ID AND RA.CASH_RECEIPT_HISTORY_ID (+) = CRH.CASH_RECEIPT_HISTORY_ID AND RA.DISPLAY (+) = 'Y' AND RA.STATUS (+) = 'APP' AND PS.PAYMENT_SCHEDULE_ID (+) = RA.APPLIED_PAYMENT_SCHEDULE_ID AND CT.CUSTOMER_TRX_ID (+) = PS.CUSTOMER_TRX_ID AND CT_BS.BATCH_SOURCE_ID(+) = CT.BATCH_SOURCE_ID AND CTT.CUST_TRX_TYPE_ID(+) = CT.CUST_TRX_TYPE_ID AND NVL(CTT.TYPE,'XX') <> 'BR' /* 20-APR-2000 J Rautiainen BR Implementation */ AND CU.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID AND CU.PARTY_ID = CU_PARTY.PARTY_ID(+) AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID AND (X.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID(+)) AND (DECODE(U.INSTRUMENT_TYPE, 'CREDITCARD',U.INSTRUMENT_ID, NULL) = C.INSTRID(+)) AND (DECODE(U.INSTRUMENT_TYPE, 'BANKACCOUNT',U.INSTRUMENT_ID, NULL) = B.EXT_BANK_ACCOUNT_ID(+)) AND (X.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE) AND (X.ORIGIN_APPLICATION_ID = A.APPLICATION_ID) AND (P.PAYMENT_CHANNEL_CODE = PT.PAYMENT_CHANNEL_CODE) AND (PT.LANGUAGE = USERENV('LANG')) AND TRXN_EXTENSION_ID(+) = CT.PAYMENT_TRXN_EXTENSION_ID AND B.BANK_ID=BHZP.PARTY_ID(+) AND B.BRANCH_ID=BRHZP.PARTY_ID(+) AND PAY_CU.CUST_ACCOUNT_ID(+) = CT.PAYING_CUSTOMER_ID AND PAY_CU.PARTY_ID = PAY_CU_PARTY.PARTY_ID(+) AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID AND BAT.BATCH_APPLIED_STATUS NOT IN ( 'STARTED_CREATION','COMPLETED_CREATION','STARTED_APPROVAL') AND RA.CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID /* Bug 2417494 */

View Text - HTML Formatted

SELECT PS.ROWID ROW_ID
, -1 CASH_RECEIPT_ID
, NULL RECEIPT_NUMBER
, CT.TRX_NUMBER TRX_NUMBER
, PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, CT.BATCH_SOURCE_ID TRX_BATCH_SOURCE_ID
, CT_BS.NAME TRX_BATCH_SOURCE_NAME
, CT.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER
, PS.AMOUNT_DUE_REMAINING AMOUNT_APPLIED
, CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CTT.NAME CUSTOMER_TRX_TYPE_NAME
, CT.TRX_DATE TRX_DATE
, PS.DUE_DATE TERM_DUE_DATE
, PS.CUSTOMER_ID CUSTOMER_ID
, SUBSTRB(CU_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CU.ACCOUNT_NUMBER CUSTOMER_NUMBER
, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, SU.LOCATION BILLING_LOCATION
, CT.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID
, CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CT.PAYING_CUSTOMER_ID PAYING_CUSTOMER_ID
, SUBSTRB(PAY_CU_PARTY.PARTY_NAME
, 1
, 50) PAYING_CUSTOMER_NAME
, PAY_CU.ACCOUNT_NUMBER PAYING_CUSTOMER_NUMBER
, NULL BANK_BRANCH_ID
, BHZP.PARTY_NAME BANK_NAME /*BUG6771709*/
, BRHZP.PARTY_NAME BANK_BRANCH_NAME
, DECODE (NVL(U.INSTRUMENT_TYPE
, P.INSTRUMENT_TYPE)
, 'BANKACCOUNT'
, B.MASKED_BANK_ACCOUNT_NUM
, 'CREDITCARD'
, C.MASKED_CC_NUMBER
, NULL) BANK_ACCOUNT_NUMBER
, BAT.BATCH_ID BATCH_ID
, BAT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS
, BAT.TYPE TYPE
, BAT.NAME BATCH_NAME
, BAT.BATCH_DATE BATCH_DATE
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, NULL RA_STATUS
, -1 RECEIVABLE_APPLICATION_ID
, -1 APPLIED_PAYMENT_SCHEDULE_ID
, -1 CASH_RECEIPT_HISTORY_ID
, NULL CRH_STATUS
, 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
, CI.CONS_INV_ID CONS_INV_ID
, CI.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, CT.PAYMENT_TRXN_EXTENSION_ID PAYMENT_TRXN_EXTENSION_ID
FROM AR_PAYMENT_SCHEDULES PS
, RA_CUSTOMER_TRX CT
, RA_BATCH_SOURCES CT_BS
, RA_CUST_TRX_TYPES CTT
, HZ_CUST_ACCOUNTS CU
, HZ_PARTIES CU_PARTY
, HZ_CUST_ACCOUNTS PAY_CU
, HZ_PARTIES PAY_CU_PARTY
, HZ_CUST_SITE_USES SU
, IBY_CREDITCARD C
, IBY_EXT_BANK_ACCOUNTS B
, IBY_FNDCPT_PMT_CHNNLS_B P
, IBY_FNDCPT_PMT_CHNNLS_TL PT
, IBY_FNDCPT_TX_EXTENSIONS X
, IBY_PMT_INSTR_USES_ALL U
, FND_APPLICATION A
, HZ_PARTIES BHZP
, HZ_PARTIES BRHZP
, AR_BATCHES BAT
, AR_CONS_INV CI
WHERE CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND CT_BS.BATCH_SOURCE_ID = CT.BATCH_SOURCE_ID
AND CTT.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID
AND CTT.TYPE <> 'BR' /* 20-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND CU.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID
AND CU.PARTY_ID = CU_PARTY.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID
AND PAY_CU.CUST_ACCOUNT_ID(+) = CT.PAYING_CUSTOMER_ID
AND PAY_CU.PARTY_ID = PAY_CU_PARTY.PARTY_ID(+)
AND BAT.BATCH_ID = PS.SELECTED_FOR_RECEIPT_BATCH_ID
AND BAT.BATCH_APPLIED_STATUS IN ( 'STARTED_CREATION'
, 'COMPLETED_CREATION'
, 'STARTED_APPROVAL' )
AND (X.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID(+))
AND (DECODE(U.INSTRUMENT_TYPE
, 'CREDITCARD'
, U.INSTRUMENT_ID
, NULL) = C.INSTRID(+))
AND (DECODE(U.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, U.INSTRUMENT_ID
, NULL) = B.EXT_BANK_ACCOUNT_ID(+))
AND (X.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE)
AND (X.ORIGIN_APPLICATION_ID = A.APPLICATION_ID)
AND (P.PAYMENT_CHANNEL_CODE = PT.PAYMENT_CHANNEL_CODE)
AND (PT.LANGUAGE = USERENV('LANG'))
AND TRXN_EXTENSION_ID(+) = CT.PAYMENT_TRXN_EXTENSION_ID
AND B.BANK_ID=BHZP.PARTY_ID(+)
AND B.BRANCH_ID=BRHZP.PARTY_ID(+)
AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID UNION ALL /* BUG 2417494 */ SELECT PS.ROWID ROW_ID
, CRH.CASH_RECEIPT_ID CASH_RECEIPT_ID
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, CT.TRX_NUMBER TRX_NUMBER
, PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, CT.BATCH_SOURCE_ID TRX_BATCH_SOURCE_ID
, CT_BS.NAME TRX_BATCH_SOURCE_NAME
, CT.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER
, RA.AMOUNT_APPLIED AMOUNT_APPLIED
, CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CTT.NAME CUSTOMER_TRX_TYPE_NAME
, CT.TRX_DATE TRX_DATE
, PS.DUE_DATE TERM_DUE_DATE
, PS.CUSTOMER_ID CUSTOMER_ID
, SUBSTRB(CU_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CU.ACCOUNT_NUMBER CUSTOMER_NUMBER
, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, SU.LOCATION BILLING_LOCATION
, CT.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID
, CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CT.PAYING_CUSTOMER_ID PAYING_CUSTOMER_ID
, SUBSTRB(PAY_CU_PARTY.PARTY_NAME
, 1
, 50) PAYING_CUSTOMER_NAME
, PAY_CU.ACCOUNT_NUMBER PAYING_CUSTOMER_NUMBER
, NULL BANK_BRANCH_ID
, BHZP.PARTY_NAME BANK_NAME
, /*BUG6771709*/ BRHZP.PARTY_NAME BANK_BRANCH_NAME
, DECODE (NVL(U.INSTRUMENT_TYPE
, P.INSTRUMENT_TYPE)
, 'BANKACCOUNT'
, B.MASKED_BANK_ACCOUNT_NUM
, 'CREDITCARD'
, C.MASKED_CC_NUMBER
, NULL) BANK_ACCOUNT_NUMBER
, BAT.BATCH_ID BATCH_ID
, BAT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS
, BAT.TYPE TYPE
, BAT.NAME BATCH_NAME
, BAT.BATCH_DATE BATCH_DATE
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, RA.STATUS RA_STATUS
, RA.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID
, RA.APPLIED_PAYMENT_SCHEDULE_ID APPLIED_PAYMENT_SCHEDULE_ID
, CRH.CASH_RECEIPT_HISTORY_ID CASH_RECEIPT_HISTORY_ID
, CRH.STATUS CRH_STATUS
, 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
, CI.CONS_INV_ID CONS_INV_ID
, CI.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, CT.PAYMENT_TRXN_EXTENSION_ID PAYMENT_TRXN_EXTENSION_ID
FROM AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
, AR_RECEIVABLE_APPLICATIONS RA
, AR_PAYMENT_SCHEDULES PS
, RA_CUSTOMER_TRX CT
, RA_BATCH_SOURCES CT_BS
, RA_CUST_TRX_TYPES CTT
, HZ_CUST_ACCOUNTS CU
, HZ_PARTIES CU_PARTY
, HZ_CUST_ACCOUNTS PAY_CU
, HZ_PARTIES PAY_CU_PARTY
, HZ_CUST_SITE_USES SU
, IBY_CREDITCARD C
, IBY_EXT_BANK_ACCOUNTS B
, IBY_FNDCPT_PMT_CHNNLS_B P
, IBY_FNDCPT_PMT_CHNNLS_TL PT
, IBY_FNDCPT_TX_EXTENSIONS X
, IBY_PMT_INSTR_USES_ALL U
, FND_APPLICATION A
, HZ_PARTIES BHZP
, HZ_PARTIES BRHZP
, AR_BATCHES BAT
, AR_CONS_INV CI
WHERE CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND BAT.BATCH_ID = CRH.BATCH_ID
AND RA.CASH_RECEIPT_HISTORY_ID (+) = CRH.CASH_RECEIPT_HISTORY_ID
AND RA.DISPLAY (+) = 'Y'
AND RA.STATUS (+) = 'APP'
AND PS.PAYMENT_SCHEDULE_ID (+) = RA.APPLIED_PAYMENT_SCHEDULE_ID
AND CT.CUSTOMER_TRX_ID (+) = PS.CUSTOMER_TRX_ID
AND CT_BS.BATCH_SOURCE_ID(+) = CT.BATCH_SOURCE_ID
AND CTT.CUST_TRX_TYPE_ID(+) = CT.CUST_TRX_TYPE_ID
AND NVL(CTT.TYPE
, 'XX') <> 'BR' /* 20-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND CU.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID
AND CU.PARTY_ID = CU_PARTY.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID
AND (X.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID(+))
AND (DECODE(U.INSTRUMENT_TYPE
, 'CREDITCARD'
, U.INSTRUMENT_ID
, NULL) = C.INSTRID(+))
AND (DECODE(U.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, U.INSTRUMENT_ID
, NULL) = B.EXT_BANK_ACCOUNT_ID(+))
AND (X.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE)
AND (X.ORIGIN_APPLICATION_ID = A.APPLICATION_ID)
AND (P.PAYMENT_CHANNEL_CODE = PT.PAYMENT_CHANNEL_CODE)
AND (PT.LANGUAGE = USERENV('LANG'))
AND TRXN_EXTENSION_ID(+) = CT.PAYMENT_TRXN_EXTENSION_ID
AND B.BANK_ID=BHZP.PARTY_ID(+)
AND B.BRANCH_ID=BRHZP.PARTY_ID(+)
AND PAY_CU.CUST_ACCOUNT_ID(+) = CT.PAYING_CUSTOMER_ID
AND PAY_CU.PARTY_ID = PAY_CU_PARTY.PARTY_ID(+)
AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID
AND BAT.BATCH_APPLIED_STATUS NOT IN ( 'STARTED_CREATION'
, 'COMPLETED_CREATION'
, 'STARTED_APPROVAL')
AND RA.CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID /* BUG 2417494 */