View: CE_CP_DISC_OVERDUE_V

Product:	CE - Cash Management
Description:	Cash Positioning Discoverer View for Overdue Transactions
Implementation/DBA Data:	APPS.CE_CP_DISC_OVERDUE_V

View Text

SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, SUM(AMOUNT)
FROM (SELECT WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, -SUM(DECODE(APP.CURRENCY_CODE
, OPEN.CURRENCY_CODE
, APP.AMOUNT
, APP.BASE_AMOUNT)) AMOUNT
FROM CE_CP_WORKSHEET_LINES WSL
, CE_CP_OPEN_BAL_V OPEN
, CE_AP_FC_PAYMENTS_V APP
WHERE WSL.SOURCE_TYPE = 'APP'
AND WSL.INCLUDE_FLAG = 'Y'
AND WSL.CUT_OFF_DAYS IS NOT NULL
AND OPEN.STATEMENT_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND OPEN.NEXT_STMT_DATE >= TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND OPEN.BANK_ACCOUNT_ID = APP.BANK_ACCOUNT_ID
AND (WSL.PAYMENT_METHOD IS NULL OR APP.PAYMENT_METHOD = WSL.PAYMENT_METHOD)
AND NVL(APP.ACTUAL_VALUE_DATE
, NVL(APP.ANTICIPATED_VALUE_DATE
, NVL(MATURITY_DATE
, PAYMENT_DATE))) > OPEN.STATEMENT_DATE - WSL.CUT_OFF_DAYS
AND NVL(APP.ACTUAL_VALUE_DATE
, NVL(APP.ANTICIPATED_VALUE_DATE
, NVL(MATURITY_DATE
, PAYMENT_DATE))) <= OPEN.STATEMENT_DATE
AND APP.STATUS = 'NEGOTIABLE' GROUP BY WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID UNION ALL SELECT WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, SUM(DECODE(ARR.CURRENCY_CODE
, OPEN.CURRENCY_CODE
, ARR.AMOUNT
, ARR.BASE_AMOUNT)) AMOUNT
FROM CE_CP_WORKSHEET_LINES WSL
, CE_CP_OPEN_BAL_V OPEN
, (SELECT BANK_ACCOUNT_ID
, CASH_ACTIVITY_DATE
, RECEIPT_METHOD_ID
, CURRENCY_CODE
, SUM(AMOUNT) AMOUNT
, SUM(BASE_AMOUNT) BASE_AMOUNT
FROM CE_AR_FC_RECEIPTS_V
WHERE STATUS NOT IN ('CLEARED'
, 'RISK_ELIMINATED')
AND CASH_ACTIVITY_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD') GROUP BY BANK_ACCOUNT_ID
, CASH_ACTIVITY_DATE
, RECEIPT_METHOD_ID
, CURRENCY_CODE) ARR
WHERE WSL.SOURCE_TYPE = 'ARR'
AND WSL.INCLUDE_FLAG = 'Y'
AND WSL.CUT_OFF_DAYS IS NOT NULL
AND OPEN.STATEMENT_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND OPEN.NEXT_STMT_DATE >= TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND OPEN.BANK_ACCOUNT_ID = ARR.BANK_ACCOUNT_ID
AND (WSL.RECEIPT_METHOD_ID IS NULL OR ARR.RECEIPT_METHOD_ID = WSL.RECEIPT_METHOD_ID)
AND ARR.CASH_ACTIVITY_DATE > OPEN.STATEMENT_DATE - WSL.CUT_OFF_DAYS
AND ARR.CASH_ACTIVITY_DATE <= OPEN.STATEMENT_DATE GROUP BY WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID UNION ALL SELECT WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, SUM(XTR.AMOUNT) AMOUNT
FROM CE_CP_OPEN_BAL_V OPEN
, CE_XTR_CASHFLOWS_V XTR
, CE_CP_WORKSHEET_LINES WSL
WHERE WSL.SOURCE_TYPE = 'XTI'
AND WSL.INCLUDE_FLAG = 'Y'
AND WSL.CUT_OFF_DAYS IS NOT NULL
AND OPEN.STATEMENT_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND OPEN.NEXT_STMT_DATE >= TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND OPEN.BANK_ACCOUNT_ID = XTR.BANK_ACCOUNT_ID
AND (WSL.TRX_TYPE IS NULL OR XTR.DDA_DEAL_TYPE = WSL.TRX_TYPE)
AND XTR.TRX_DATE > OPEN.STATEMENT_DATE - WSL.CUT_OFF_DAYS
AND XTR.TRX_DATE <= OPEN.STATEMENT_DATE
AND (WSL.INDICATIVE_FLAG = 'Y' OR NOT (XTR.DDA_DEAL_TYPE = 'EXP'
AND XTR.DDA_DEAL_SUBTYPE = 'INDIC'))
AND XTR.AMOUNT >= 0
AND XTR.RECONCILED_REFERENCE IS NULL GROUP BY WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID UNION ALL SELECT WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, SUM(XTR.AMOUNT) AMOUNT
FROM CE_CP_OPEN_BAL_V OPEN
, CE_XTR_CASHFLOWS_V XTR
, CE_CP_WORKSHEET_LINES WSL
WHERE WSL.SOURCE_TYPE = 'XTO'
AND WSL.INCLUDE_FLAG = 'Y'
AND WSL.CUT_OFF_DAYS IS NOT NULL
AND OPEN.STATEMENT_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND OPEN.NEXT_STMT_DATE >= TO_DATE(SYSDATE
, 'YYYY/MM/DD')
AND OPEN.BANK_ACCOUNT_ID = XTR.BANK_ACCOUNT_ID
AND (WSL.TRX_TYPE IS NULL OR XTR.DDA_DEAL_TYPE = WSL.TRX_TYPE)
AND XTR.TRX_DATE > OPEN.STATEMENT_DATE - WSL.CUT_OFF_DAYS
AND XTR.TRX_DATE <= OPEN.STATEMENT_DATE
AND (WSL.INDICATIVE_FLAG = 'Y' OR NOT (XTR.DDA_DEAL_TYPE = 'EXP'
AND XTR.DDA_DEAL_SUBTYPE = 'INDIC'))
AND XTR.AMOUNT < 0
AND XTR.RECONCILED_REFERENCE IS NULL GROUP BY WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID) GROUP BY WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID

Columns