VIEW: APPS.AR_BOE_REMIT_RECEIPTS_NONCBR_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, CE_BANK_ACCT_USES_OU_V BA, CE_BANK_ACCOUNTS CBA, CE_BANK_BRANCHES_V BB, IBY_TRXN_EXTENSIONS_V IBY 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 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' ) UNION ALL SELECT CR.ROWID ROW_ID, 'POTENTIAL_RECEIPTS_FOR_REMIT' SELECTION_TYPE, CR.CASH_RECEIPT_ID CASH_RECEIPT_ID, CR.CURRENCY_CODE CURRENCY_CODE, -1 BATCH_ID, CR.RECEIPT_NUMBER RECEIPT_NUMBER, CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID, -1 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, '-1' BATCH_NAME, '-1' BATCH_APPLIED_STATUS, POT_RMA.REMIT_BANK_ACCT_USE_ID 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_RECEIPT_METHOD_ACCOUNTS CR_RMA, AR_RECEIPT_METHOD_ACCOUNTS POT_RMA, AR_RECEIPT_METHODS RM, AR_RECEIPT_CLASSES RC, AR_CASH_RECEIPT_HISTORY CRH, AR_PAYMENT_SCHEDULES PS, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, IBY_TRXN_EXTENSIONS_V IBY, CE_BANK_ACCT_USES_OU_V BA, CE_BANK_ACCOUNTS CBA, CE_BANK_BRANCHES_V BB WHERE CR_RMA.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID AND CR_RMA.REMIT_BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND POT_RMA.ON_ACCOUNT_CCID = CR_RMA.ON_ACCOUNT_CCID AND POT_RMA.UNAPPLIED_CCID = CR_RMA.UNAPPLIED_CCID AND POT_RMA.UNIDENTIFIED_CCID = CR_RMA.UNIDENTIFIED_CCID AND POT_RMA.RECEIPT_METHOD_ID = CR_RMA.RECEIPT_METHOD_ID AND CR.SELECTED_REMITTANCE_BATCH_ID IS NULL AND RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID AND RC.RECEIPT_CLASS_ID = RM.RECEIPT_CLASS_ID AND RC.REMIT_FLAG = 'Y' AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND CRH.STATUS = 'CONFIRMED' AND CRH.CURRENT_RECORD_FLAG = 'Y' AND PS.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID AND CUST_ACCT.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER AND CUST_ACCT.PARTY_ID = PARTY.PARTY_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

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
, CE_BANK_ACCT_USES_OU_V BA
, CE_BANK_ACCOUNTS CBA
, CE_BANK_BRANCHES_V BB
, IBY_TRXN_EXTENSIONS_V IBY
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 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' ) UNION ALL SELECT CR.ROWID ROW_ID
, 'POTENTIAL_RECEIPTS_FOR_REMIT' SELECTION_TYPE
, CR.CASH_RECEIPT_ID CASH_RECEIPT_ID
, CR.CURRENCY_CODE CURRENCY_CODE
, -1 BATCH_ID
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, -1 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
, '-1' BATCH_NAME
, '-1' BATCH_APPLIED_STATUS
, POT_RMA.REMIT_BANK_ACCT_USE_ID 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_RECEIPT_METHOD_ACCOUNTS CR_RMA
, AR_RECEIPT_METHOD_ACCOUNTS POT_RMA
, AR_RECEIPT_METHODS RM
, AR_RECEIPT_CLASSES RC
, AR_CASH_RECEIPT_HISTORY CRH
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, IBY_TRXN_EXTENSIONS_V IBY
, CE_BANK_ACCT_USES_OU_V BA
, CE_BANK_ACCOUNTS CBA
, CE_BANK_BRANCHES_V BB
WHERE CR_RMA.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID
AND CR_RMA.REMIT_BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND POT_RMA.ON_ACCOUNT_CCID = CR_RMA.ON_ACCOUNT_CCID
AND POT_RMA.UNAPPLIED_CCID = CR_RMA.UNAPPLIED_CCID
AND POT_RMA.UNIDENTIFIED_CCID = CR_RMA.UNIDENTIFIED_CCID
AND POT_RMA.RECEIPT_METHOD_ID = CR_RMA.RECEIPT_METHOD_ID
AND CR.SELECTED_REMITTANCE_BATCH_ID IS NULL
AND RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID
AND RC.RECEIPT_CLASS_ID = RM.RECEIPT_CLASS_ID
AND RC.REMIT_FLAG = 'Y'
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH.STATUS = 'CONFIRMED'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND PS.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_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