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