SELECT
(SELECT bank_account_num
FROM ce_bank_accounts
WHERE bank_account_id = sh.bank_account_id) AS account_number,
(SELECT bank_name
FROM ce_bank_accounts
WHERE bank_account_id = sh.bank_account_id) AS bank_name,
TO_CHAR(sh.statement_date, ‘YYYY-MM-DD’) AS statement_date,
(SELECT NVL(SUM(et.amount), 0)
FROM ce_external_transactions et
WHERE et.bank_account_id = sh.bank_account_id
AND et.transaction_date < TO_DATE(:from_date, ‘YYYY-MM-DD’)) AS opening_balance,
(SELECT NVL(SUM(sl.amount), 0)
FROM ce_statement_lines sl
WHERE sl.statement_id = sh.statement_id
AND sl.transaction_date BETWEEN TO_DATE(:from_date, ‘YYYY-MM-DD’) AND TO_DATE(:to_date, ‘YYYY-MM-DD’)) AS transactions_in_period,
(SELECT NVL(SUM(sl.amount), 0)
FROM ce_statement_lines sl
WHERE sl.statement_id = sh.statement_id
AND sl.transaction_date <= TO_DATE(:to_date, ‘YYYY-MM-DD’)) AS closing_balance,
(SELECT NVL(SUM(rh.amount), 0)
FROM ce_recon_history rh
WHERE rh.bank_account_id = sh.bank_account_id
AND rh.reconciliation_date <= TO_DATE(:to_date, ‘YYYY-MM-DD’)) AS reconciled_balance
FROM
ce_statement_headers sh
WHERE
sh.statement_date BETWEEN TO_DATE(:from_date, ‘YYYY-MM-DD’) AND TO_DATE(:to_date, ‘YYYY-MM-DD’)
AND sh.bank_account_id IN
(SELECT bank_account_id
FROM ce_bank_accounts
WHERE bank_name = :bank_name)
ORDER BY
account_number, statement_date;