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