View: CE_CP_DISC_PRIOR_V

Product:	CE - Cash Management
Description:	Cash Positioning Discoverer View for Prior-day Balances
Implementation/DBA Data:	APPS.CE_CP_DISC_PRIOR_V

View Text

SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, SUM(AMOUNT) FROM (SELECT WSH.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, SUM(DECODE(APP.CURRENCY_CODE
, OPEN.CURRENCY_CODE
, APP.AMOUNT
, APP.BASE_AMOUNT)) AMOUNT FROM CE_CP_OPEN_BAL_V OPEN
, CE_AP_FC_PAYMENTS_V APP
, CE_CP_WORKSHEET_HEADERS WSH
WHERE 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 NVL(APP.ACTUAL_VALUE_DATE
, NVL(APP.ANTICIPATED_VALUE_DATE
, NVL(APP.MATURITY_DATE
, APP.PAYMENT_DATE))) > OPEN.STATEMENT_DATE AND NVL(APP.ACTUAL_VALUE_DATE
, NVL(APP.ANTICIPATED_VALUE_DATE
, NVL(APP.MATURITY_DATE
, APP.PAYMENT_DATE))) < TO_DATE(SYSDATE
, 'YYYY/MM/DD') GROUP BY WSH.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID UNION ALL SELECT WSH.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, SUM(DECODE(ARR.CURRENCY_CODE
, OPEN.CURRENCY_CODE
, ARR.AMOUNT
, ARR.BASE_AMOUNT)) AMOUNT FROM CE_CP_OPEN_BAL_V OPEN
, CE_CP_WORKSHEET_HEADERS WSH
, (SELECT BANK_ACCOUNT_ID
, CASH_ACTIVITY_DATE
, CURRENCY_CODE
, SUM(AMOUNT) AMOUNT
, SUM(BASE_AMOUNT) BASE_AMOUNT FROM CE_AR_FC_RECEIPTS_V GROUP BY BANK_ACCOUNT_ID
, CASH_ACTIVITY_DATE
, CURRENCY_CODE) ARR WHERE OPEN.BANK_ACCOUNT_ID = ARR.BANK_ACCOUNT_ID AND OPEN.STATEMENT_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD') AND OPEN.NEXT_STMT_DATE >= TO_DATE(SYSDATE
, 'YYYY/MM/DD') AND ARR.CASH_ACTIVITY_DATE > OPEN.STATEMENT_DATE AND ARR.CASH_ACTIVITY_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD') GROUP BY WSH.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID UNION ALL SELECT WSH.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, SUM(DECODE(PAY.CURRENCY_CODE
, OPEN.CURRENCY_CODE
, PAY.AMOUNT
, PAY.BASE_AMOUNT)) AMOUNT FROM CE_PAY_FC_PAYROLL_V PAY
, CE_CP_OPEN_BAL_V OPEN
, CE_CP_WORKSHEET_HEADERS WSH WHERE PAY.BANK_ACCOUNT_ID IN ( SELECT DISTINCT BANK_ACCOUNT_ID FROM CE_CP_OPEN_BAL_V) AND OPEN.BANK_ACCOUNT_ID = PAY.BANK_ACCOUNT_ID AND OPEN.STATEMENT_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD') AND OPEN.NEXT_STMT_DATE >= TO_DATE(SYSDATE
, 'YYYY/MM/DD') AND PAY.TRX_DATE > OPEN.STATEMENT_DATE AND PAY.TRX_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD') GROUP BY WSH.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID UNION ALL SELECT WSH.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, SUM(XTR.AMOUNT) FROM CE_CP_OPEN_BAL_V OPEN
, CE_XTR_CASHFLOWS_V XTR
, CE_CP_WORKSHEET_HEADERS WSH WHERE 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 XTR.TRX_DATE > OPEN.STATEMENT_DATE AND XTR.TRX_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD') AND NOT (XTR.DDA_DEAL_TYPE = 'EXP' AND XTR.DDA_DEAL_SUBTYPE = 'INDIC') GROUP BY WSH.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID UNION ALL SELECT WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, XTR.AMOUNT FROM CE_CP_OPEN_BAL_V OPEN
, CE_XTR_CASHFLOWS_V XTR
, CE_CP_WORKSHEET_LINES WSL WHERE 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 XTR.TRX_DATE > OPEN.STATEMENT_DATE AND XTR.TRX_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD') AND XTR.AMOUNT >= 0 AND XTR.DDA_DEAL_TYPE = 'EXP' AND XTR.DDA_DEAL_SUBTYPE = 'INDIC' AND WSL.SOURCE_TYPE = 'XTI' AND WSL.INDICATIVE_FLAG = 'Y' UNION ALL SELECT WSL.WORKSHEET_HEADER_ID
, OPEN.BANK_ACCOUNT_ID
, XTR.AMOUNT FROM CE_CP_OPEN_BAL_V OPEN
, CE_XTR_CASHFLOWS_V XTR
, CE_CP_WORKSHEET_LINES WSL WHERE 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 XTR.TRX_DATE > OPEN.STATEMENT_DATE AND XTR.TRX_DATE < TO_DATE(SYSDATE
, 'YYYY/MM/DD') AND XTR.AMOUNT < 0 AND XTR.DDA_DEAL_TYPE = 'EXP' AND XTR.DDA_DEAL_SUBTYPE = 'INDIC' AND WSL.SOURCE_TYPE = 'XTO' AND WSL.INDICATIVE_FLAG = 'Y') GROUP BY WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID

Columns