Open Forum

Expand all | Collapse all

[GL20000] records purged after year end close

  • 1.  [GL20000] records purged after year end close

    Posted Feb 05, 2020 03:41 PM
    At this time of year, we run a script against the [GL20000] table and others to generate a twelve month allocation of last years Actuals to use against our current year Plan numbers for budgeting purposes.

    Unfortunately, we closed for the year before we ran the script so now no records exist with OPENYEAR and TRXDATE of 2019. All that's in the GL20000 are OPENYEAR of 2020

    Is there any way to repopulate the GL20000 with last years records?
    If not, is there an alternative table that would virtually mimic what was in the GL20000?
    Is the GL30000 a virtual copy of records in the GL20000 when the year is closed?

    Thanks again

    ------------------------------
    James Patt
    Programmer
    Western Region OTB Corporation
    Batavia NY
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: [GL20000] records purged after year end close

    GPUG ALL STAR
    Posted Feb 06, 2020 07:42 AM
    Hi @James Patt

    The GL20000 is always only Open Years, but when the year closes all that years transactions move to the GL30000 table so it is an exact duplicate (as long as you have your accounts and transactions set to maintain history).

    At the close the year end balances of all Balance Sheet Accounts are then placed back into the GL20000 as document type BBF (Balance Brought Forward) in Period 0.

    You could actually use the view AccountTransactions, filter it to TRX Date between '01/01/2019' and '12/31/2019' to completely mimic the GL20000 table for 2019.​

    ------------------------------

    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: [GL20000] records purged after year end close

    Posted Feb 06, 2020 09:09 AM
    Although I prefer Jo's methodolgy, another option is to unroll the balances by Reversing Historical Year in the Year-end closing window, depending on what version you guys are on.


    ------------------------------
    Kimberly Lomax
    Manager, Financial Systems
    Rayonier Inc.
    Wildlight FL
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: [GL20000] records purged after year end close

    GPUG ALL STAR
    Posted Feb 07, 2020 04:50 AM
    Edited by Zubin Gidwani Feb 07, 2020 04:51 AM
      |   view attached

    James,

    GP maintains summary tables for the monthly account balances, it is probably more efficient to query those tables rather than re-computing balances from the raw transactions. 

    GL10110 Open Year Account Balances
    GL10111 Historical Year Account Balances

    I've attached 4 sample SQL queries for you to start from.

    Alternatively, you can generate % changes from a prior year using the Budgeting Excel Export wizard in GP
    here is a quick walkthrough tutorial on that process
    https://www.iorad.com/player/1632534/GP-Budgeting-Export-Wizard

    If you need any assistance feel free to ping me directly.

    Zubin









    ------------------------------
    Zubin Gidwani
    Founder
    Dynamic Budgets
    San Francisco-Bay Area CA
    ------------------------------

    Attachment(s)

    txt
    GPAccountBalances.txt   5K 1 version
    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role