Differences

This shows you the differences between two versions of the page.

Link to this comparison view

— control_sql_-_invalid_gl_account_error [2019/01/27 11:29] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +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 ([[control:off-balance_sheet_gl_entries|control: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_formattsql|code_formattsql|code format="tsql"]]
 +
 +
 +
 +-- 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_formattsql|code_formattsql|code format="tsql"]]
 +
 +
 +
 +-- 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 format="sql"]]
 +
 +
 +
 +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.