Payables & Cash Management

Get Involved. Join the Conversation.

Topic

    Sylvain Lemay CPA CA
    Payable aging by entered currency
    Topic posted August 9, 2019 by Sylvain Lemay CPA CAGold Medal: 3,500+ Points, last edited August 9, 2019, tagged Financials, Payables, Period Close / Reconciliation, Reports, Sample Reports 
    28 Views, 4 Comments
    Title:
    Payable aging by entered currency
    Summary:
    AP Aging by entered currency - We are trying to rencile our revaluation for our AP control account
    Content:

    Oracle cloud does not seem to have out of the box an AP Aging by entered currency

    We would need this report to reconcile our month end control account from the subledger with our GL account and our control account contains mix currency

    We have tried with the Payable Open item reevaluation report but this is too huge and unable to work with

    We have tried with an OTBI report but still not conclusive

    I believe a standard report Payable by original currency would give us what we need but it does not seem to be available. I presume we will need to create a BIP from the original Aging?

    Any ideas? Any reports, suggestion

    Thanks

    Comment

     

    • Becky Alvarez

      Have you followed the steps in Chapter 6 of this White Paper that Can be found under the White Papers for Financials Cloud?  The AP User Guide will tell you that the AP Aging report shows Ledger Currency so you can reconcile to the ledger in the ledger currency.  I am not sure why you need it in a different currency than the ledger you are trying to balance to.

      Review the attached closing white paper and perhaps it will offer some assistance.  AP starts in Chapter 6.

    • Sylvain Lemay CPA CA

      Hello Becky,

      Thanks for the info - but  this is not what I am looking for (I know how to reconcile Payable to the ledger ;-) )

      What we need and the reasoning is: We have Payable in entered currency CAD and USD in a CAD ledger and the same in a USD ledger

      At month end we revalue our payable balances USD to CAD for our CAD ledger (at exchange rate month end rate)  this generates a journal entry every month and in our payable reconciliation it shows as a difference. We still need demonstrate that our GL balance reconcile with our payable subledger (subledger Payable CAD balance + (subledger Payable USD entered balance * (USD/CAD month end exchange rate) = GL AP control account

      As part of our month we take or USD payable balance and apply the month end rate this would give us the CAD balance and if we add our CAD payables this should give us our GL ledger balance. At this point there is no report providing this standard within Oracle

      We need to do the same for our USD ledger. This is also a reconciliation that we have to provide to our auditor quarterly and annually. So we perform this reconciliation monhtly

      To perform easily this reconciliation an aging in entered currency would provide for us with the mean to reconcile

      Hope this explanation helps to what we are trying to achieve

      Thanks

       

    • Alexey Shtrakhov

      With my expirience we customized this report mentioned by you: Payable Open item reevaluation report.

      We:
      - filtered out all the transactions in Ledger currency leaving only foreign currency transactions
      - made list more excel friendly for further analysis reducing different summary levels and bringing some additional columns on line level
      - adding/improving summary by currency / by account at the end of the report

      => now you perhaps could try OTBI based solution by buildting report based on "Payables Invoices - Trial Balance Real Time". Then you could limit there which currency you want to reconcile. I have used this for example to create trial balance based on supplier country and it worked fine. But of course this you will need to construct from the very beginning.

      Alexey

      • Sylvain Lemay CPA CA

        Thanks alot Alexey. We will look into the proposed solution with a customization of Payable Open item reevaluation report as this would really help what we are trying to achieve

        For the proposition for OTBI, we already tried this but did not work out as expected

        Thanks

        Sylvain