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.

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|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.

1. Run the following queries to correct:

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>

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.

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.

You could leave a comment if you were logged in.