View: AR_BOE_AUTO_RECEIPTS_V

Product:	AR - Receivables
Description:	(Release 11.5 Only)
Implementation/DBA Data:	APPS.AR_BOE_AUTO_RECEIPTS_V

View Text

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 */

Columns