This sql query shows the Important columns from Bank Statement headers table and bank statement lines table in oracle Fusion for a particular Bank account. This query forms critical base for the Oracle cash management. We have already applied filters based on Bank account but the filters based on amount, transaction date and statement number can be easily added here. Querying through the database is much faster than through the front end to find a particular statement line.
SELECT CBA.bank_account_name,
CSH.STATEMENT_NUMBER,
CSH.CURRENCY_CODE,
CSH.STATEMENT_DATE,
CSH.RECON_STATUS_CODE,
CSH.STATEMENT_ENTRY_TYPE,
CSH.CREATED_BY,
CSL.recon_reference RECONCILATION_REFERENCE,
CSL.value_date TRANSACTION_DATE,
CSL.flow_indicator,
CSL.trx_amount TRANSACTION_AMOUNT,
CSL.amount,
CSL.accnt_servicer_ref,
CSL.customer_reference,
CSL.end_to_end_id,
CSL.payment_info_identifier,
CSL.reversal_reason_code,
CSL.orig_bank_account_id SOURCE_BANK_ACCOUNT,
CSL.addenda_txt,
CSL.attribute1,
CSL.attribute2,
CSL.attribute3,
CSL.attribute4,
CSL.attribute5,
CSL.attribute6,
CSL.attribute7,
CSL.attribute8
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%’