Open Forum

sub account postings on balance sheet

  • 1.  sub account postings on balance sheet

    Posted 02-13-2018 09:30 AM
    ​We have just implemented SL and have mistakenly posted to various balance sheet accounts using subaccounts.  So we have a bank account 1000-00 but have posted to 1000-02, 1000-03, 1000-04 etc.  And the same with various other balance sheet accounts that should only use the 00 sub account.

    This is only in the first 6 weeks of activity since going live so I would like to correct all the data.  In order to get all the accounts corrected would I be accurate in thinking that if I correct all the GLTran records with the correct sub account and update the AcctHist to have the correct PTD and YTD balances that will take care of all the tables needed to?

    I have about 5 accounts I need to correct.


    Bill Welch
    Just Dough It, Inc
    Camdenton MO

  • 2.  RE: sub account postings on balance sheet

    Posted 02-13-2018 09:45 AM
    Hi Bill,

    It sounds like you are planning on updating the subaccounts using SQL?  One issue would be if the transactions came from another module, then that would need to be updated as well......i.e. - if a cash transaction was done, then CATran and GLTran would need to be updated and integrity check run on CATran along with updating AcctHist.......

    If it were me, I would do 1 of the following:
    • Not many transactions for a particular account / subaccount - reverse the transaction then do the correct one.
    • A lot of transactions for a particular account / subaccount
      • Just do a net JE to correct with support attached OR
      • Create a QQ to pull the transactions and reverse / redo in 1 transaction

    By doing the above, it creates a audit trail and minimizes chance of mistakes.  I know it might be time consuming but I prefer the audit trail and while I can and do update using SQL, I am very particular about what gets updated via SQL......

    I would also recommend using Validate Acct / Sub (in GL Setup)....this will only allow certain subaccounts to be used with a particular account and prevents users from posting to incorrect subaccounts.

    If you have questions or need assistance, let me know.


    Michael O'Neill
    Eastex Telephone Cooperative
    Henderson TX

  • 3.  RE: sub account postings on balance sheet

    Posted 02-14-2018 07:58 AM
    ​STOP!  I've been using SL for over 20 years and I still get nervous doing SQL updates and only do them if I know for a fact it won't screw up the database.  Run the tran history so you know what to reclass, make journal entries and let the system do what it does.  Living with a messed up database is a never-ending headache.

    The biggest problems I've seen with SL happen when people don't just let it do what it does and try to outsmart it.

    Pete Draigh
    IT Systems Manager
    Caledonia MI

  • 4.  RE: sub account postings on balance sheet

    Posted 02-14-2018 08:52 AM
    ​Actually that is what I decided to do, I was just throwing it out to see if anyone had a better solution or if there were programs such as the Vendor Change that provides a smooth correction process.

    I have been through a lot of data conversions and implementations and know that there are ways to update tables if you create your sql statements correctly and test them extensively.   And I definitely never update or make changes to a database if I do not know for a fact that the scripts will not create any issues or compromise the database.  But I do agree that in many cases people do not do that and they can make a mess of the database.

    But that being said I appreciate your response and understand your first shout out of STOP : )

    Bill Welch
    Just Dough It, Inc
    Camdenton MO

  • 5.  RE: sub account postings on balance sheet

    Posted 02-14-2018 08:58 AM
    I would use either the Professional Services Tools Library (PSTL) or NexVue's Keychange product to combine the subaccounts. Both of these tools work very nicely. PSTL can do subaccount combinations (combine two subaccounts - for ALL accounts) and NexVue's Keychange can do Account/Sub combo changes (combine two subaccounts - for only specific accounts).

    Amy Junkins
    Solomon Cloud Solutions
    Findlay OH

  • 6.  RE: sub account postings on balance sheet

    Posted 02-14-2018 10:39 AM
    We have been extremely pleased with Plumbline's GL Mover, GL Erase, Description Changer add-ons.  They're not that expensive and are way more effective and appropriate than trying to move data around with JE's.

    I'm not a SQL expert by any stretch of the imagination but I would urge EXTREME caution in trying to modify the database unless you are certain you fully understand all the links to the data fields that might be involved

    Jon Wissler
    Vice President and CFO
    Pacific Northwest Generating Cooperative
    Portland OR