[Home] [Help]
SELECT CR.ROWID ROW_ID, 'CURRENT_BATCH_REMITTED' 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, -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, BAT.NAME BATCH_NAME, BAT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS, -1 BATCH_BANK_ACCOUNT_ID, '-1' 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_PAYMENT_SCHEDULES PS, AR_CASH_RECEIPTS CR, AR_CASH_RECEIPT_HISTORY CRH, AR_CASH_RECEIPT_HISTORY CRH2, AR_RECEIPT_METHODS RM, 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 CRH.STATUS = 'REMITTED' AND CRH.CASH_RECEIPT_HISTORY_ID = CRH2.REVERSAL_CASH_RECEIPT_HIST_ID AND CRH2.STATUS = 'CONFIRMED' AND RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND PS.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID AND BAT.BATCH_ID = CRH.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
SELECT CR.ROWID ROW_ID
, 'CURRENT_BATCH_REMITTED' 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
, -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
, BAT.NAME BATCH_NAME
, BAT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS
, -1 BATCH_BANK_ACCOUNT_ID
, '-1' 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_PAYMENT_SCHEDULES PS
, AR_CASH_RECEIPTS CR
, AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPT_HISTORY CRH2
, AR_RECEIPT_METHODS RM
, 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 CRH.STATUS = 'REMITTED'
AND CRH.CASH_RECEIPT_HISTORY_ID = CRH2.REVERSAL_CASH_RECEIPT_HIST_ID
AND CRH2.STATUS = 'CONFIRMED'
AND RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND PS.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
AND BAT.BATCH_ID = CRH.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
|
|
|
|