eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_BOE_REMIT_RECEIPTS_CBUNR_V

Source

View Text - Preformatted


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' )

View Text - HTML Formatted

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' )