Home » Blog » Unit Accounts and Beginning Balances in Dynamics GP

Microsoft Dynamics GP has a great feature called Unit Accounts, which can be used to store nonfinancial or statistical data. Unit Accounts are set up in their own window on the financial cards menu and can be used for reporting in Management Reporter, FRx and other reporting tools. For example, many health care providers track patient days and need to produce financial reports with that information.
One issue that can arise with unit accounts is the year-to-date amounts roll over to a beginning balance. If you do not want a beginning balance, there’s an issue. Unfortunately, there is no way within GP to clear the beginning balance for a unit account.
So what can you do? You’ll need to use SQL Server Management Studio or Query Analyzer, depending on the version of SQL Server you are using, to clear the beginning balances.

The first thing you will want to do before ever running a SQL statement to change information is back up your database!

Next, you can use this query to find accounts with beginning balances. You’ll need to modify the query based on the fiscal year.
/* FIND UNIT ACCOUNTS WITH BEGINNING BALANCES */
SELECT GL00100.ACTINDX, GL10110.YEAR1, GL00100.ACCTTYPE, GL10110.PERIODID,
GL00105.ACTNUMST, GL10110.PERDBLNC FROM GL00100 INNER JOIN GL10110 ON GL00100.ACTINDX =
GL10110.ACTINDX INNER JOIN GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX WHERE
GL00100.ACCTTYPE = 2 AND GL10110.PERIODID = 0 AND GL10110.YEAR1 = 2014
Once records are returned, you can run another query to update the beginning balances to 0.
/* UPDATE BEGINNING BALANCES FOR UNIT ACCOUNTS */
UPDATE a SET a.perdblnc = 0 FROM GL10110 a INNER JOIN GL00100 b on a.ACTINDX = b.ACTINDX WHERE
a.PERIODID = 0 and b.ACCTTYPE = 2 and a.YEAR1 = 2014
Shown below is the same window as above but with a beginning balance of 0.

If this issue recurs each year, contact us for assistance in creating a process that can automatically update your unit accounts’ beginning balances.

OAG International is an experienced Microsoft Competency Partner with offices in San Francisco, CA specializing in affordable Microsoft Dynamics GP consulting, development and deployment on premise or in the Azure Cloud..
Contact us for a complimentary needs analysis and to learn how you can benefit from our lower prices, faster service and the strongest commitment to customer satisfaction around.

Schedule Analysis

Microsoft Dynamics GP Resources Library

Have you considered a cloud-based Microsoft Dynamics GP solution?

Microsoft Dynamic GP Cloud Hosting

Want to hear how we've helped other customers like you?

helped other customers like you

Moving from a legacy system?

moving from legacy system

Questions?

Contact Oag
 
 

Copyright © 2015 OAGint.com - All Rights Reserved