SELECT inline.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM
, inline.BANK_STMT_NUM BANK_STMT_NUM
, inline.BANK_STMT_DATE BANK_STMT_DATE
, inline.STATUS STATUS
, inline.control_begin_balance BEGIN_BALANCE
, inline.control_end_balance CLOSE_BALANCE
FROM (
SELECT &lc_uploaded_select_columns
, TRUNC(statement_date) BANK_STMT_DATE
, &gc_status_current STATUS
, control_begin_balance
, control_end_balance
FROM ce_statement_headers_int uploaded
WHERE &lc_uploaded_where_conditions
AND uploaded.statement_date = (SELECT MIN(inner_uploaded.statement_date)
FROM ce_statement_headers_int inner_uploaded
WHERE inner_uploaded.bank_branch_name = :gc_bank_branch_name
AND inner_uploaded.bank_account_num = uploaded.bank_account_num
GROUP BY inner_uploaded.bank_account_num )
ORDER BY statement_date
) inline
UNION ALL
SELECT inline.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM
, inline.statement_number BANK_STMT_NUM
, inline.statement_date BANK_STMT_DATE
, inline.STATUS STATUS
, inline.control_begin_balance BEGIN_BALANCE
, inline.control_end_balance CLOSE_BALANCE
FROM
( SELECT &lc_latest_bank_acc_num BANK_ACCOUNT_NUM
, statement_number
, statement_date
, &gc_status_latest STATUS
, control_begin_balance
, control_end_balance
FROM ce_statement_headers latest
&lc_latest_bank_acc_from
WHERE statement_date < (SELECT MIN(TRUNC(uploaded.statement_date))
FROM ce_statement_headers_int uploaded
WHERE &lc_uploaded_where_conditions
AND uploaded.bank_account_num = &lc_latest_bank_acc_num
GROUP BY uploaded.bank_account_num )
&lc_latest_bank_acc_where
) inline,
( SELECT &lc_latest_bank_acc_num BANK_ACCOUNT_NUM
, MAX(statement_date) statement_date
FROM ce_statement_headers latest
&lc_latest_bank_acc_from
WHERE statement_date < (SELECT MIN(TRUNC(uploaded.statement_date))
FROM ce_statement_headers_int uploaded
WHERE &lc_uploaded_where_conditions
AND uploaded.bank_account_num = &lc_latest_bank_acc_num
GROUP BY uploaded.bank_account_num )
&lc_latest_bank_acc_where
GROUP BY &lc_latest_bank_acc_num
) inline1
WHERE inline.statement_date = inline1.statement_date
AND inline.BANK_ACCOUNT_NUM = inline1.BANK_ACCOUNT_NUM |