VIEW: APPS.CE_CP_DISC_PRIOR_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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