View: AR_BOE_REMIT_RECEIPTS_CBUNR_V

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

View Text

SELECT CR.ROWID ROW_ID
, 'CURRENT_BATCH_UNREMITTED' SELECTION_TYPE
, CR.CASH_RECEIPT_ID CASH_RECEIPT_ID
, CR.CURRENCY_CODE CURRENCY_CODE
, BAT.BATCH_ID BATCH_ID
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, CR.SELECTED_REMITTANCE_BATCH_ID SELECTED_REMITTANCE_BATCH_ID
, RM.NAME RECEIPT_METHOD_NAME
, CRH.AMOUNT AMOUNT
, CR.FACTOR_DISCOUNT_AMOUNT BANK_CHARGES
, CRH.CASH_RECEIPT_HISTORY_ID CASH_RECEIPT_HISTORY_ID
, NVL( CR.OVERRIDE_REMIT_ACCOUNT_FLAG
, 'Y' ) OVERRIDE_FLAG
, CR.REMIT_BANK_ACCT_USE_ID REMITTANCE_BANK_ACCOUNT_ID
, BB.BRANCH_PARTY_ID REMITTANCE_BANK_BRANCH_ID
, BB.BANK_NAME REMIT_BANK_NAME
, BB.BANK_BRANCH_NAME REMIT_BANK_BRANCH_NAME
, CBA.BANK_ACCOUNT_NUM REMIT_BANK_ACCOUNT_NUMBER
, NULL CUSTOMER_BANK_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, CR.PAY_FROM_CUSTOMER PAY_FROM_CUSTOMER
, CR.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, CR.REVERSAL_DATE REVERSAL_DATE
, NULL CUSTOMER_BANK_NAME
, NULL CUSTOMER_BANK_BRANCH_ID
, NULL CUSTOMER_BANK_BRANCH_NAME
, DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.ACCOUNT_NUMBER
, 'CREDITCARD'
, IBY.CARD_NUMBER
, NULL) CUSTOMER_BANK_ACCOUNT_NUMBER
, CR.RECEIPT_DATE RECEIPT_DATE
, DECODE(CR.TYPE
, 'CASH'
, PS.DUE_DATE
, 'MISC'
, CR.DEPOSIT_DATE
, NULL) MATURITY_DATE
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, BAT.NAME BATCH_NAME
, BAT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS
, -1 BATCH_BANK_ACCOUNT_ID
, RC.REMIT_METHOD_CODE REMIT_METHOD_CODE
, CR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CR.ATTRIBUTE1 ATTRIBUTE1
, CR.ATTRIBUTE2 ATTRIBUTE2
, CR.ATTRIBUTE3 ATTRIBUTE3
, CR.ATTRIBUTE4 ATTRIBUTE4
, CR.ATTRIBUTE5 ATTRIBUTE5
, CR.ATTRIBUTE6 ATTRIBUTE6
, CR.ATTRIBUTE7 ATTRIBUTE7
, CR.ATTRIBUTE8 ATTRIBUTE8
, CR.ATTRIBUTE9 ATTRIBUTE9
, CR.ATTRIBUTE10 ATTRIBUTE10
, CR.ATTRIBUTE11 ATTRIBUTE11
, CR.ATTRIBUTE12 ATTRIBUTE12
, CR.ATTRIBUTE13 ATTRIBUTE13
, CR.ATTRIBUTE14 ATTRIBUTE14
, CR.ATTRIBUTE15 ATTRIBUTE15
, CR.PAYMENT_TRXN_EXTENSION_ID PAYMENT_TRXN_EXTENSION_ID
, RM.PAYMENT_CHANNEL_CODE PAYMENT_CHANNEL_CODE
FROM AR_CASH_RECEIPTS CR
, AR_CASH_RECEIPT_HISTORY CRH
, AR_RECEIPT_METHODS RM
, AR_RECEIPT_CLASSES RC
, AR_PAYMENT_SCHEDULES PS
, AR_BATCHES BAT
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, IBY_TRXN_EXTENSIONS_V IBY
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, CE_BANK_BRANCHES_V BB
WHERE RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID
AND RC.RECEIPT_CLASS_ID = RM.RECEIPT_CLASS_ID
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS = 'CONFIRMED'
AND PS.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
AND BAT.BATCH_ID = CR.SELECTED_REMITTANCE_BATCH_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND BA.BANK_ACCT_USE_ID(+) = CR.REMIT_BANK_ACCT_USE_ID
AND IBY.TRXN_EXTENSION_ID(+)= CR.PAYMENT_TRXN_EXTENSION_ID
AND BA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)
AND BA.BANK_ACCT_USE_ID (+) = CR.REMIT_BANK_ACCT_USE_ID
AND BB.BRANCH_PARTY_ID (+) = CBA.BANK_BRANCH_ID
AND BAT.BATCH_APPLIED_STATUS IN ( 'STARTED_CREATION'
, 'COMPLETED_CREATION'
, 'STARTED_APPROVAL' )

Columns