VIEW: APPS.CE_AR_FC_RECEIPTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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