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.

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

1a. Correct the assigned expense accounts with incorrect GLClassificationTypes

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

1b. Update the Parts and Ledger tables to use the OffBalanceSheet field.

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


1c.

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)


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.