View: CE_AR_FC_RECEIPTS_V

Product:	CE - Cash Management
Description:	Forecasting source - AR receipts
Implementation/DBA Data:	APPS.CE_AR_FC_RECEIPTS_V

View Text

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

Columns