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.
Here’s a screenshot of a GP window showing a unit account with a beginning balance of 500.
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 = 2018
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 = 2018
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.