Payables & Cash Management

Get Involved. Join the Conversation.


    Clare Farris
    Cash to General Ledger reconciliation
    Topic posted August 16, 2019 by Clare FarrisRed Ribbon: 250+ Points, tagged Cash Management 
    62 Views, 4 Comments
    Cash to General Ledger reconciliation
    Cash to General Ledger reconciliation


    When we run the Cash to General Leger reconciliation report for one period we are only able to see unreconciled items for the one period we run the report for. 

    When we run the same report for a cumulative period ie from inception to the current period to show all unreconciled transactions (which is what we want) the GL balance is incorrect. The balance shown is the aggregate of all month's GL balances the report is run for ie report is run for Jan to Feb then the GL balance = Jan closing GL balance $1,000 + Feb closing GL balance $1,000 = total GL balance $2,000 rather than showing the closing balance for the current month ie $1,000.  This makes the report unusable.

    How do we get around this issue as there will likely always be unreconciled amounts spanning across a month end?



    R13 - 19B



    • Jon Hayes

      Hi Clare,

      We had the same issue. We had to create a custom version of the data model and make some amendments - I have attached a copy of it, so feel free to use it. If you decide to use it; ensure it is saved into a Shared Folders/Custom area of BI catalog and not overwriting the original.

      For reference, the following changes were made:

      1. Amended any references to 'BETWEEN :GC_FROM_DATE AND :GC_TO_DATE' to '<= :GC_TO_DATE' in all 4 data sets - this resolves the issue of previous period unreconciled transactions not being considered.
      2. Amended the opening line of the Q_CLOSE_BAL_BOOK dataset From: 'NVL(SUM((nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0))-(nvl(glb.begin_balance_cr,0) + nvl(glb.period_net_cr,0))),0) CLOSE_BAL_BOOK' To: 'To:NVL(SUM(nvl(glb.period_net_dr,0)-nvl(glb.period_net_cr,0)),0) CLOSE_BAL_BOOK' - this changes the query so that it sums just the Period Net figures and so the report correctly shows Ending Balance for the End Period you select.

      We created a copy of the ESS process for 'Cash to General Ledger Reconciliation Report' and assigned the new report & data model.

      Hope this helps. We have only just gone live with CM, and these changes were made in the last few days. No issues so far, but just to be mindful that further tweaks to the data model may be required.

      Kind Regards,



      • Clare Farris

        Thank you so much Jon. I will pass this information on to our reporting guy and ask him to make the necessary changes. 

        I can't open the attachment though.

        • Jon Hayes

          Hi Clare,

          The attachment needs to be imported to OTBI - I don't believe you can open it on a local machine. To open it in OTBI; Navigate to the BI folder you wish to save it in, then click the Unacrhive button and select it from your local directory - it will then appear as a Data Model in BI catalog.

          Kind Regards,