Explanation of SQL
Prior to mid 2009, Control only checked if the entire GL entry was in balance, not if the Off-Balance sheet portion and on-balance sheet portion were both in balance by themselves. It was therefore possible to set up parts that used On-Balance Sheet Inventory and Off-Balance Sheet Expenses (then called computed costs).
This SQL is used to identify GL entries where the On-Balance sheet and Off-Balance sheet portions don't independently sum to zero.
Risk of Data Corruption if Run Improperly
The selection of this data for examination poses no risks. However, some of the queries following it do alter data irrevocably and should only be used by a skilled technician.
SQL
-- GL Out of Balance By Order DECLARE @StartDate DateTime SET @StartDate = '1/1/2000' SELECT (SELECT DivisionName AS Division FROM EmployeeGroup WHERE OOBGL.DivisionID = EmployeeGroup.ID) AS Division, TransHeader.OrderNumber, TransHeader.BillNumber, OOBGL.OOBAmount, OffBalanceSheet, Account.CompanyName, TransHeader.Description, TransHeader.AccountID, OOBGL.TransHeaderID, (SELECT MAX(EntryDateTime) FROM Ledger GL2 WHERE GL2.TransactionID = OOBGL.TransHeaderID) AS LastGLEntryDate FROM ( SELECT COALESCE(DivisionID, 10) AS DivisionID, COALESCE (TransactionID, 0) AS TransHeaderID, SUM(Amount) AS OOBAmount, OffBalanceSheet FROM Ledger WHERE EntryDateTime >= @StartDate GROUP BY COALESCE(DivisionID, 10), COALESCE (TransactionID, 0), OffBalanceSheet HAVING (SUM(Amount)>0) ) AS OOBGL LEFT OUTER JOIN TransHeader ON TransHeader.ID = OOBGL.TransHeaderID LEFT OUTER JOIN Account ON TransHeader.AccountID = Account.ID ORDER BY Division, OrderNumber, BillNumber, OffBalanceSheet
Possible Fixes
The following SQLs may be used to fix certain Off-Balance problems, but should only be used by a skilled technician. Your data should be backed up first as these changes are irrevocable in the current data!
Set Bills to Only Use Real Costs
Bills should never interact with off-balance sheet costs, so this query adjust the GL of all Bill entries that are set as OffBalanceSheet = 1 to zero.
UPDATE Ledger SET OffBalanceSheet = 0 WHERE OffBalanceSheet = 1 AND TransactionID IN (SELECT ID FROM TransHeader WHERE TransactionType = 8)
Update Off-Balance Orders Based on the current Part Settings
This query updates the GL to fix off-balance entries.
UPDATE Ledger SET OffBalanceSheet = (SELECT abs(1-AccrueCosts) FROM Part WHERE Part.ID = Ledger.PartID) WHERE OffBalanceSheet = (SELECT AccrueCosts FROM Part WHERE Part.ID = Ledger.PartID) AND PartID IS NOT NULL AND TransactionID IN ( SELECT DISTINCT TransactionID FROM ( SELECT SUM(amount) Amount, transactionid, ItemName, OffBalanceSheet, AccrueCosts, (SELECT OrderNumber FROM TransHeader WHERE ID = TransactionID) AS OrderNumber FROM ledger JOIN part ON ledger.partid = part.id WHERE OffBalanceSheet = AccrueCosts GROUP BY transactionid, ItemName, OffBalanceSheet, AccrueCosts HAVING SUM(amount) 0 -- order by OrderNumber desc ) TempA )
Version Information
- Entered : 6/2010
- Version : 4.4+