This query helps find the unreconciled bank statement lines through the Oracle cloud Database which are lying unreconcilled for a particular bank account. We have included all important columns from Bank statement headers and lines tables which we though will be important in identifying the bank statement lines. This information and extract is specially helpful to run before the month end process to enable quality cash reconcillation processes.
SELECT CBA.bank_account_name,
CSH.STATEMENT_NUMBER,
CSH.CURRENCY_CODE,
CSH.STATEMENT_DATE,
CSH.RECON_STATUS_CODE,
CSH.STATEMENT_ENTRY_TYPE,
CSL.recon_reference RECONCILATION_REFERENCE,
CSL.value_date TRANSACTION_DATE,
CSL.RECON_STATUS,
CSL.flow_indicator,
CSL.trx_amount TRANSACTION_AMOUNT,
CSL.amount,
CSL.end_to_end_id,
CSL.payment_info_identifier,
CSL.reversal_reason_code,
CSL.orig_bank_account_id SOURCE_BANK_ACCOUNT
FROM ce_bank_branches_v CBBV,
ce_bank_accounts CBA,
ce_statement_lines CSL,
ce_statement_headers CSH
WHERE CBBV.branch_party_id = CBA.bank_branch_id
AND CSL.statement_header_id = CSH.statement_header_id
AND CSH.bank_account_id = CBA.bank_account_id
AND CBA.bank_account_name like ‘CITI%’
AND CSL.RECON_STATUS not in (‘REC’)