Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== Control SQL - Invalid GL Account Error ====== ====== Control SQL - Invalid GL Account Error ====== After upgrading to Control 4.5, marking an order Sale, editing, or posting a payment on an Order marked Sale gives an "invalid or unrecognized gl account" error message.\\ ===== Causes ===== There are records in the GLAccount table with classtypeids of 8055 and 8051 with **VALID** GLClassificationTypes. If the records have invalid GLClassificationTypes, see Solution #2 below.\\ \\ There are assigned expense accounts (ClassTypeID: 8051) in the GLAccount table that do not correct information in the GLClassificationType field. As a result, Ledger entries with these gl accounts have invalid GLClassificationTypes as well. In Control 4.40.0912.xxxx, assigned expenses are removed and replaced with an On/Off Balance Sheet system ([[http://control.cyriouswiki.com/Off-Balance Sheet GL Entries|http://control.cyriouswiki.com/Off-Balance+Sheet+GL+Entries]]). The On\Off Balance Sheet update procedure runs a SQL query that changes all assigned expense gl accounts to system expenses using the 9000s GLClassificationType values and then removes the assigned expense accounts from the database. In this case, the entries do not have the correct GLClassificationType and are not updated.\\ ===== Solution ===== \\ 1. Run the following queries to correct:\\ ==== 1a. Correct the assigned expense accounts with incorrect GLClassificationTypes ==== <code>-- For Control 4.4 and later ( Ledger Table update) -- Run this to correct Assigned Expenses accounts missing GLClassificationTypes from the -- GLAccount and Ledger tables select gl.glaccountid, gl.glclassificationtype, ga.glclassificationtype, ga.id from Ledger gl with(nolock) left outer join glaccount ga on ga.id = gl.glaccountid where gl.id > 0 and gl.glclassificationtype > 8999 or glaccountclasstypeid <> 8001 or glaccountid in (5000, 5001, 6000, 6001) group by glaccountid, gl.glclassificationtype, ga.glclassificationtype, ga.id -- UPDATE GLAccount UPDATE GLAccount SET GlClassificationType = 9001, GLClassTypeName = 'Assigned Expense' WHERE ClassTypeID = 8051 AND GlClassificationType IS NULL UPDATE GLAccount SET GlClassificationType = 9002, GLClassTypeName = 'Cost Tracking' WHERE ID = 6002 AND GlClassificationType IS NULL UPDATE GLAccount SET GlClassificationType = 9003, GLClassTypeName = 'Assigned Cost Of Built' WHERE ID = 6001 AND GlClassificationType IS NULL -- UPDATE GL UPDATE Ledger SET GlClassificationType = GA.GlClassificationType, GlClassTypeName = GA.GlClassTypeName FROM Ledger GL LEFT OUTER JOIN GLAccount GA ON GA.ID = GL.GlaccountID WHERE GL.ID > 0 AND ( GL.GlClassificationType IS NULL OR GL.GlClassificationType = 0 OR GL.GlClassificationType = -1 ) ) </code> ==== 1b. Update the Parts and Ledger tables to use the OffBalanceSheet field. ==== <code>-- Update Parts Asset accounts Update Part Set AssetAccountID = 60, AssetAccountClassTypeID = 8001 From Part Left Join GLAccount GLA on part.AssetAccountID = GLA.ID Where (GLA.GLClassificationType in (9000,9001,9002,9003) or Part.AssetAccountID is Null) and TrackCosts = 1 -- Update Part Expense Accounts Update Part Set ExpenseAccountID = 61, ExpenseAccountClassTypeID = 8001 From Part Left Join GLAccount GLA on part.ExpenseAccountID = GLA.ID Where (GLA.GLClassificationType in (9000,9001,9002,9003) or Part.ExpenseAccountID is Null) and TrackCosts = 1 -- Update Part Computed Cost Accounts Update Part Set ComputedCostAccountID = 61, ComputedCostAccountClassTypeID = 8001 From Part Left Join GLAccount GLA on part.ComputedCostAccountID = GLA.ID Where (GLA.GLClassificationType in (9000,9001,9002,9003)) -- Update Ledger Update Ledger Set OffBalanceSheet = 1, GLAccountID = Case when Ledger.GLClassificationType = 9001 then P.ExpenseAccountID when Ledger.GLClassificationType = 9002 then Coalesce(P.AssetAccountID, 60) when Ledger.GLClassificationType = 9003 then 34 end, GLAccountClassTypeID = 8001 From Ledger Left Join Part P on P.ID = Ledger.PartID Where Ledger.GLClassificationType in (9000,9001,9002,9003) Update Ledger Set Ledger.GLClassificationType = GLA.GLClassificationType, Ledger.GLClassTypeName = GLA.GLClassTypeName From Ledger Left Join GlAccount GLA on GLA.ID = Ledger.GLAccountID Where Ledger.GLClassificationType in (9000,9001,9002,9003) </code> \\ 1c. \\ <code>UPDATE glaccount SET classtypeid = 8001 WHERE classtypeid IN (8051, 8055) UPDATE ledger SET glclassificationtype = ga.glclassificationtype , glclasstypename = ga.glclasstypename FROM ledger gl LEFT OUTER JOIN glaccount ga ON ga.id = gl.glaccountid WHERE gl.glclassificationtype <> ga.glclassificationtype UPDATE part SET expenseaccountclasstypeid = 8001 WHERE expenseaccountclasstypeid IN (8051,8055) UPDATE part SET assetaccountclasstypeid = 8001 WHERE assetaccountclasstypeid IN (8051,8055) </code> \\ 2. Restart the SSLIP.\\ ===== Notes ===== The correcting query was added to version 4.5.1005.2501. Upgrading from a version prior to 4.40.0912.xxxx to versions 4.5.1005.2501 and later will not have this issue.