Payables & Cash Management

Get Involved. Join the Conversation.

Topic

    Maarten van der Burg
    Bank statement balances check report
    Topic posted September 11, 2019 by Maarten van der BurgSilver Medal: 2,000+ Points, tagged Cash Management, Period Close / Reconciliation, Reports 
    39 Views, 4 Comments
    Title:
    Bank statement balances check report
    Content:

    We want to have a report that shows bank statements for which the subsequent bank statements opening and closing balances do not match, indicating a missing or erroneous statement.

    Currently, I've come up with the below code but there's one problem: If there's multiple statements in each day, this SQL does not work. I've checked some samples and in each case where there were more than 1 statement on a day, it was always only 2. In these cases, the statement that should be seen as the 'first' statement of that day, always had a statement_to date which was smaller than the statement_to date of the subsequent statement.

    Can someone please help me fix this last bit in the SQL (the nesting is a bit over my head).

    Thanks in advance!
    Maarten

    select  distinct cba.bank_account_num
    , substr(cba.BANK_ACCOUNT_NAME,1,6) Legal_Entity
    , cba.bank_account_name
    , o.STMT_FROM_DATE statement
    , o.previous_statement
    , o.opening_balance
    , c.closing_balance
    from ce_bank_accounts cba
    , (select csh.bank_account_id
    , to_char(csh.STMT_FROM_DATE,'dd-mm-yyyy') STMT_FROM_DATE
    , to_char((select max(csh1.STMT_FROM_DATE)
    from ce_statement_headers csh1
    where csh1.bank_account_id = csh.bank_account_id
    and csh1.STMT_FROM_DATE < csh.STMT_FROM_DATE
    and csh1.statement_header_id = (select max(csh2.statement_header_id) 
    from ce_statement_headers csh2 
    where csh2.bank_account_id = csh1.bank_account_id 
    and csh2.stmt_from_date = csh1.stmt_from_date)
    ),'dd-mm-yyyy') previous_statement
    , csb.balance_amount opening_balance
    from ce_statement_headers csh
    , ce_stmt_balances csb
    where csh.statement_header_id = csb.statement_header_id
    and csb.balance_code = 'OPBD') o
    , (select csh.bank_account_id
    , to_char(csh.STMT_FROM_DATE,'dd-mm-yyyy') STMT_FROM_DATE
    , csb.balance_amount closing_balance
    from ce_statement_headers csh
    , ce_stmt_balances csb
    where csh.statement_header_id = csb.statement_header_id
    and csb.balance_code = 'CLBD') c
    where o.bank_account_id = c.bank_account_id
    and o.previous_statement = c.STMT_FROM_DATE
    and o.bank_account_id = cba.bank_account_id
    and o.opening_balance <> c.closing_balance
    and to_date(o.STMT_FROM_DATE,'dd-mm-yyyy') > (:p_fromdate)
    order by cba.bank_account_num, to_date(o.STMT_FROM_DATE,'dd-mm-yyyy') asc

    Comment

     

    • Becky Alvarez

      You may want to try and post this on the Reporting and Analytics Financial forum in addition to this forum in order to cover all your bases.

    • Glen Ryen

      Hi Maarten,

      I think there are some easier ways to go about what you're looking for. First, are you aware that there's a missing statements infotile in the main Cash Balances dashboard?  That will highlight accounts missing end of day bank statements for the last XX days (2 by default, but it's configurable).  Is that really the basic need here for you?

      Second, look into the ce_stmt_import_errors table.  Oracle raises a warning when you load a bank statement and the opening balance doesn't match the closing balance of the last successfully loaded statement for that account.  That warning will be in the import ESS job log file, but also that error table.  Querying that should give you the exceptions you're looking for with less SQL, I'd think.

      If not (and you feel you need to go the custom SQL route), definitely make sure you're not checking intra-day statements.  You want to compare end of day statements only, so look to filter on the intra_day flag in the statement header table.  I'd also suggest trying to re-architect your SQL.  Build a list of account(s) and dates first as one SELECT, then outer join to end of day CE statements where you have them.  Then you can easily see where the date/account combo doesn't have a statement, and/or use analytic functions to look for the prior statement's closing balance and compare it to current statement's opening balance.  I've coded a similar report before that way, though without the balance check portion.

      Does that help?

      Glen

    • Maarten van der Burg

      Hi Glen,

      That does help. Do need some additional pointers though.

      In the cash balances dashboard, all values show 0. This may be due to the fact that there's no Bank Account Group available. Where can I set this up?
      We don't usually pay too much attention to the seeded reports because we manage 350+ bank accounts and the seeded reports usually are focused on 1 account, making it very tedious to use.

      I will explore the import file, but I am not sure it will be water tight because some bank statements are input manually. Will also explore your pointers regarding the SQL, thanks!

      Kind regards,
      Maarten

    • Glen Ryen

      Hi Maarten,

      To create a group, click the 'Show Filters' button just to the right of that drop-down on the Cash Balances page.  Enter some filter criteria and then click the 'Save' button, that will give you a group.  Repeat as needed to define all the groups you'd like.

      But those also wouldn't be factoring into the balances showing as $0.  For that, what balance code(s) are you getting on your BAI2 statements?  Closing Booked (015) and/or Closing Available (045)?  You need to take that BAI2 codes that your banks provide and make sure they're mapped for your bank statement format (FSM task 'Manage Code Map Groups').   Map to the codes for CLBD and/or CLAV, as applicable.  Then you also need to check that the balances cube is using the mapped code, under 'Specify Cash Positioning and Forecasting Options'.  Has that all been set up for you?

      And 350 is certainly a big number of accounts, but still should be manageable.  I've got one client with over 150 accounts, managing mostly with the UI and a small number of custom reports.  OTBI should be very helpful there.  Have you gotten a chance to use that tool to pull together your own ad hoc reports on bank statements?

      Glen