SELECT CRH.ACCTD_AMOUNT , CRH.AMOUNT , CR.CURRENCY_CODE , CRH.TRX_DATE , APS.DUE_DATE , CR.ACTUAL_VALUE_DATE , (SELECT BAU.BANK_ACCOUNT_ID FROM CE_BANK_ACCT_USES_ALL BAU WHERE BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID) , CR.RECEIPT_METHOD_ID , DECODE(ARP_CASHBOOK.RECEIPT_DEBIT_MEMO_REVERSED(CR.CASH_RECEIPT_ID) , 'Y' , 'REVERSED' , CRH.STATUS) , CR.ANTICIPATED_CLEARING_DATE , CR.ORG_ID , CR.RECEIPT_NUMBER , CR.CASH_RECEIPT_ID , NVL(CR.ACTUAL_VALUE_DATE , CR.ANTICIPATED_CLEARING_DATE) FROM AR_CASH_RECEIPTS_ALL CR , AR_CASH_RECEIPT_HISTORY_ALL CRH , AR_PAYMENT_SCHEDULES_ALL APS WHERE CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND (APS.ORG_ID = CRH.ORG_ID OR APS.ORG_ID IS NULL) AND APS.CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID AND (CRH.ORG_ID = CR.ORG_ID OR CRH.ORG_ID IS NULL) AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.STATUS NOT IN ('REVERSED' , 'APPROVED') AND CRH.REVERSAL_GL_DATE IS NULL AND NVL(CR.ACTUAL_VALUE_DATE , CR.ANTICIPATED_CLEARING_DATE) IS NOT NULL AND NOT EXISTS (SELECT NULL FROM CE_BANK_ACCOUNTS BA , CE_BANK_ACCT_USES_ALL BAU2 WHERE BA.BANK_ACCOUNT_ID = BAU2.BANK_ACCOUNT_ID AND BAU2.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND BA.NETTING_ACCT_FLAG = 'Y') UNION ALL SELECT CRH.ACCTD_AMOUNT , CRH.AMOUNT , CR.CURRENCY_CODE , CRH.TRX_DATE , APS.DUE_DATE , CR.ACTUAL_VALUE_DATE , (SELECT BAU.BANK_ACCOUNT_ID FROM CE_BANK_ACCT_USES_ALL BAU WHERE BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID) , CR.RECEIPT_METHOD_ID , DECODE(ARP_CASHBOOK.RECEIPT_DEBIT_MEMO_REVERSED(CR.CASH_RECEIPT_ID) , 'Y' , 'REVERSED' , CRH.STATUS) , CR.ANTICIPATED_CLEARING_DATE , CR.ORG_ID , CR.RECEIPT_NUMBER , CR.CASH_RECEIPT_ID , APS.DUE_DATE FROM AR_CASH_RECEIPTS_ALL CR , AR_CASH_RECEIPT_HISTORY_ALL CRH , AR_PAYMENT_SCHEDULES_ALL APS WHERE CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND (APS.ORG_ID = CRH.ORG_ID OR APS.ORG_ID IS NULL) AND APS.CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID AND (CRH.ORG_ID = CR.ORG_ID OR CRH.ORG_ID IS NULL) AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.STATUS NOT IN ('REVERSED' , 'APPROVED') AND CRH.REVERSAL_GL_DATE IS NULL AND NVL(CR.ACTUAL_VALUE_DATE , CR.ANTICIPATED_CLEARING_DATE) IS NULL AND APS.DUE_DATE IS NOT NULL AND NOT EXISTS (SELECT NULL FROM CE_BANK_ACCOUNTS BA , CE_BANK_ACCT_USES_ALL BAU2 WHERE BA.BANK_ACCOUNT_ID = BAU2.BANK_ACCOUNT_ID AND BAU2.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND BA.NETTING_ACCT_FLAG = 'Y') UNION ALL SELECT CRH.ACCTD_AMOUNT , CRH.AMOUNT , CR.CURRENCY_CODE , CRH.TRX_DATE , APS.DUE_DATE , CR.ACTUAL_VALUE_DATE , (SELECT BAU.BANK_ACCOUNT_ID FROM CE_BANK_ACCT_USES_ALL BAU WHERE BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID) , CR.RECEIPT_METHOD_ID , DECODE(ARP_CASHBOOK.RECEIPT_DEBIT_MEMO_REVERSED(CR.CASH_RECEIPT_ID) , 'Y' , 'REVERSED' , CRH.STATUS) , CR.ANTICIPATED_CLEARING_DATE , CR.ORG_ID , CR.RECEIPT_NUMBER , CR.CASH_RECEIPT_ID , CRH.TRX_DATE FROM AR_CASH_RECEIPTS_ALL CR , AR_CASH_RECEIPT_HISTORY_ALL CRH , AR_PAYMENT_SCHEDULES_ALL APS WHERE CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND (APS.ORG_ID = CRH.ORG_ID OR APS.ORG_ID IS NULL) AND APS.CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID AND (CRH.ORG_ID = CR.ORG_ID OR CRH.ORG_ID IS NULL) AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.STATUS NOT IN ('REVERSED' , 'APPROVED') AND CRH.REVERSAL_GL_DATE IS NULL AND NVL(CR.ACTUAL_VALUE_DATE , CR.ANTICIPATED_CLEARING_DATE) IS NULL AND APS.DUE_DATE IS NULL AND NOT EXISTS (SELECT NULL FROM CE_BANK_ACCOUNTS BA , CE_BANK_ACCT_USES_ALL BAU2 WHERE BA.BANK_ACCOUNT_ID = BAU2.BANK_ACCOUNT_ID AND BAU2.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND BA.NETTING_ACCT_FLAG = 'Y')