You’re reconciling General Ledger accounts, and lo and behold, your AR is out of balance! It can often be tedious and frustrating to identify such unexplained variances. You expect the balance in your General Ledger account for the AR control account to tie to your AR Trial Balance report at all times!
When the GL balance differs from the report balance, often the issue is that one or more GL Journal Transaction have been posted to the control account. Since there is no way for a Sage 500 GL Journal Transaction to update any other module, any GL Transaction postings to the related AR control account will cause reconciliation issues. This same scenario could exist for Accounts Payable, Cash, Inventory, etc.
One way to help identify these issues is to take advantage of an option you may not be using in Sage 500: Custom Fields. In GL, as with other modules, you have a handful of Custom Fields. These are not truly “custom” fields, but rather they are part of the native Sage 500 database schema. You can use these fields to track additional data for which there is no standard field in the software. Once setup, these Custom fields will automatically be available in several native Sage 500 reports, and with a little knowledge of SQL they can easily be added to the SQL views used by Business Insights Explore and Analyze tasks.
For today’s example, we will create a custom field called “Control Account” within the General Ledger module.
Navigate to General Ledger > Maintenance > GL Setup > Set Up GL Options.
Once in that task, click on the “Custom Fields” button.
Select the “Usage” drop-down field for the first unused row that appears in the “Set Up Custom Fields” grid. In the example below, we’re focusing on the 2nd row. Select either “Required” to force users to enter a value for new GL Accounts created hereafter, or “Optional”. We want the Control Account field to be required for all new GL Accounts entered from now on, so we’ve selected “Required”.
Enter “Control Account” as the name for the new field in the 15-character Title box.
In the “Data Type” drop-down list we’ve selected “Validated”, which will force the users to select one of the predefined values we define by clicking on the “Values” button. NOTE: There will always be a blank <NULL> Value available for every Custom field, so in this case where we only need two options to define whether the account is or is not a control account, we need to enter only one additional Value of ‘Y’.
Click the “Finish and Exit” button (CTRL-SHIFT-F) to close the “Set Up Custom Field Values” window.
Click the “Finish and Exit” button (CTRL-SHIFT-F) to close the “Set Up Custom Fields” window.
Click the “Finish and Exit” button (CTRL-SHIFT-F) to close the “Set Up GL Options” window.
Launch General Ledger > Maintenance > Maintain GL Accounts > Maintain Accounts and type in or lookup your AR account.
Click on the Custom Fields button and select ‘Y’ from the drop-down in the Control Account field.
Click the “Finish and Exit” button (CTRL-SHIFT-F) to close the “Custom Fields” window.
Click the “Finish” button (CTRL-F) to close the “Maintain Accounts” window.
In SQL Server Management Studio, ALTER (or have your Sage Reseller ALTER) the SQL view that is used by the native Explore GL Transactions task (vdvglAccountTran) to add the new custom field:
******************************************************************************************
Copyright RKL eSolutions LLC 2016
DISCLAIMER: Changes to standard Sage 500 views will need to be reviewed
each time you upgrade, as the new version's native view will overwrite
your old modified version!
*****************************************************************************************
USE [MAS500_app] -- UPDATE to your Database name as needed
GO
********** Object: View [dbo].[vdvglAccountTran] Script Date: 05/26/2016 15:28:52 ***********
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* vdvglAccountTran - CREATE VIEW */
ALTER VIEW [dbo].[vdvglAccountTran] AS SELECT dbo.tglAcctRef.AcctRefCode, dbo.tglAcctRef.Description AS AcctRefDesc,
dbo.tciBatchLog.BatchNo, dbo.tciBatchLog.PostUserID,
dbo.tciBatchLog.BatchID, dbo.tglTransaction.CreateDate, dbo.tglTransaction.CreateType, dbo.vListValidationString.LocalText AS CreateTypeAsText,
dbo.tglTransaction.CurrExchRate, dbo.tglTransaction.CurrID, dbo.tglTransaction.ExtCmnt, dbo.tglJournal.Description AS JrnlDesc, dbo.tglJournal.JrnlID,
dbo.tglTransaction.JrnlNo, dbo.tglTransaction.PostAmt, dbo.tglTransaction.PostAmtHC, dbo.tglTransaction.PostCmnt, dbo.tglTransaction.PostDate,
dbo.tglTransaction.PostQty, dbo.tsmModuleDef.ModuleID AS SourceModule, dbo.tglTransaction.TranDate, dbo.tglTransaction.TranNo,
dbo.tglTransaction.glTranKey, dbo.tglTransaction.TranKey, dbo.tglTransaction.TranType AS TranTypeNo, dbo.tciBatchLog.BatchKey,
dbo.tglJournal.JrnlKey, dbo.tglAccount.GLAcctKey, dbo.tglAccount.CompanyID, dbo.vFormattedGLAcct.FormattedGLAcctNo,
dbo.vFormattedGLAcct.Segment1, dbo.vFormattedGLAcct.Segment1Desc, dbo.vFormattedGLAcct.Segment2, dbo.vFormattedGLAcct.Segment2Desc,
dbo.vFormattedGLAcct.Segment3, dbo.vFormattedGLAcct.Segment3Desc, dbo.vFormattedGLAcct.Segment4, dbo.vFormattedGLAcct.Segment4Desc,
dbo.vFormattedGLAcct.Segment5, dbo.vFormattedGLAcct.Segment5Desc, dbo.vFormattedGLAcct.Segment6, dbo.vFormattedGLAcct.Segment6Desc,
dbo.vFormattedGLAcct.Segment7, dbo.vFormattedGLAcct.Segment7Desc, dbo.vFormattedGLAcct.Segment8, dbo.vFormattedGLAcct.Segment8Desc,
dbo.vFormattedGLAcct.Segment9, dbo.vFormattedGLAcct.Segment9Desc, dbo.vFormattedGLAcct.Segment10, dbo.vFormattedGLAcct.Segment10Desc,
dbo.vFormattedGLAcct.Segment11, dbo.vFormattedGLAcct.Segment11Desc, dbo.vFormattedGLAcct.Segment12,
dbo.vFormattedGLAcct.Segment12Desc, dbo.vFormattedGLAcct.Segment13, dbo.vFormattedGLAcct.Segment13Desc,
dbo.vFormattedGLAcct.Segment14, dbo.vFormattedGLAcct.Segment14Desc, dbo.vFormattedGLAcct.Segment15,
dbo.vFormattedGLAcct.Segment15Desc, dbo.tglAccount.Description AS GLAcctDesc, dbo.tglAccount.GLAcctNo, dbo.tglFiscalPeriod.FiscPer,
dbo.tglFiscalPeriod.FiscYear, dbo.tglFiscalPeriod.FiscYearPer, dbo.tsmLocalString.LocalText AS TranTypeDesc,
dbo.tciBatchLog.SourceCompanyID,tapVoucher.VouchNo,
dbo.tglAccount.UserFld1 AS 'BudgetMgr', -- add GL Account Custom Field 1 for Budget Manager
dbo.tglAccount.UserFld2 AS 'ControlAcct' -- add GL Account Custom Field 2 for Control Account
FROM dbo.tsmLocalString RIGHT OUTER JOIN
dbo.tglJournal RIGHT OUTER JOIN
dbo.vListValidationString INNER JOIN
dbo.tglTransaction ON dbo.vListValidationString.DBValue = dbo.tglTransaction.CreateType LEFT OUTER JOIN
dbo.tglAccount LEFT OUTER JOIN
dbo.tglFiscalPeriod ON dbo.tglAccount.CompanyID = dbo.tglFiscalPeriod.CompanyID ON dbo.tglTransaction.GLAcctKey = dbo.tglAccount.GLAcctKey AND
dbo.tglTransaction.FiscPer = dbo.tglFiscalPeriod.FiscPer AND dbo.tglTransaction.FiscYear = dbo.tglFiscalPeriod.FiscYear LEFT OUTER JOIN
dbo.tciTranType ON dbo.tglTransaction.TranType = dbo.tciTranType.TranType LEFT OUTER JOIN
dbo.vFormattedGLAcct ON dbo.tglTransaction.GLAcctKey = dbo.vFormattedGLAcct.GLAcctKey ON dbo.tglJournal.JrnlKey = dbo.tglTransaction.JrnlKey ON
dbo.tsmLocalString.StringNo = dbo.tciTranType.TranDescStrNo LEFT OUTER JOIN
dbo.tciBatchLog ON dbo.tglTransaction.BatchKey = dbo.tciBatchLog.BatchKey LEFT OUTER JOIN
dbo.tsmModuleDef ON dbo.tglTransaction.SourceModuleNo = dbo.tsmModuleDef.ModuleNo LEFT OUTER JOIN
dbo.tglAcctRef ON dbo.tglTransaction.AcctRefKey = dbo.tglAcctRef.AcctRefKey
LEFT OUTER JOIN dbo.tapVoucher ON dbo.tglTransaction.TranKey = dbo.tapVoucher.VoucherKey AND
dbo.tglTransaction.BatchKey = dbo.tapVoucher.BatchKey
WHERE (dbo.vListValidationString.TableName = 'tglTransaction') AND (dbo.vListValidationString.ColumnName = 'CreateType')
GO
*****************************************************************************************
Once you compile the altered view, the new field should be available in General Ledger > Insights > Explore > GL Account Transactions.
Add two filters to the Explore view to limit the results
You may also want to add the “Posted By” field to display the User who posted the GL Journal Transaction by clicking on the “Edit Columns”
Now chase down this rogue ‘admin’ user and ask him or her to never post a GL Journal Transaction to a Control Account again!