VIEW: APPS.CE_CP_DISC_OVERDUE_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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