This SQL query will insert a Sales Tax Paid GL Account into your Chart of Accounts. This account is necessary for users of the full accounting module, the Sales Tax Paid account is utilized when taxes are specified on a PO, Receiving Document, or Bill. This account was mistaken removed from the OOB v2.72 - OOB v2.75 databases so if you do use full accounting you will need this account.

If this GL Account is not present you will receive a similar error to the one listed below should you not have this GL Account in your system.

The INSERT statement conflicted with the FOREIGN KEY constraint ““FK_Ledger_GLAccount””. The conflict occurred in database ““StoreData””, table ““dbo.GLAccount””, column 'ID'

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable

INSERT INTO [GLAccount]
           ([ID]
           ,[StoreID]
           ,[ClassTypeID]
           ,[ModifiedByUser]
           ,[ModifiedByComputer]
           ,[ModifiedDate]
           ,[SeqID]
           ,[IsSystem]
           ,[IsActive]
           ,[AccountGroupID]
           ,[AccountName]
           ,[Description]
           ,[ExportAccountName]
           ,[ExportAccountNumber]
           ,[ExportGroupID]
           ,[ConsolidateExport]
           ,[Depth]
           ,[PathName1]
           ,[PathName2]
           ,[PathName3]
           ,[PathName4]
           ,[PathName5]
           ,[MasterGroup]
           ,[AllowSubGroups]
           ,[RequireOneMember]
           ,[MemberClassTypeID]
           ,[ConsolidateSubAccounts]
           ,[SortIndex]
           ,[ExportAccountDesc]
           ,[NoExportToExternal]
           ,[AccountType]
           ,[LastCheckNumber]
           ,[GLClassificationType]
           ,[GLClassTypeName]
           ,[IsRoyaltyExempt]
           ,[RoyaltyFee1]
           ,[RoyaltyFee1OV]
           ,[RoyaltyFee2]
           ,[RoyaltyFee2OV]
           ,[AddressID]
           ,[AlwaysRoundUp]
           ,[CapAmount]
           ,[ReferenceID]
           ,[TaxRate]
           ,[AssociatedGLAccountID]
           ,[NonGLAccount]
           ,[AccountGroupClassTypeID]
           ,[VendorID]
           ,[VendorClassTypeID])
     VALUES
           (63
           ,-1
           ,8001
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,1
           ,1
           ,5
           ,'Sales Tax Paid'
           ,'Sales Tax Paid'
           ,NULL
           ,0
           ,0
           ,0
           ,2
           ,'Expense'
           ,'Expense/Other Expenses'
           ,'Expense/Other Expenses'
           ,'Expense/Other Expenses'
           ,'Expense/Other Expenses'
           ,0
           ,0
           ,0
           ,NULL
           ,0
           ,90
           ,NULL
           ,0
           ,0
           ,0
           ,5002
           ,'Expense'
           ,0
           ,0
           ,0
           ,0
           ,0
           ,NULL
           ,0
           ,0
           ,NULL
           ,0
           ,NULL
           ,0
           ,8000
           ,NULL
           ,2000
)

After you execute this query, you may need to update all pre-existing GL entries for GLAccountID = 63 to have a proper GLClassTypeName and GLClassificationType.

UPDATE Ledger
SET GLClassificationType = 5002
, GLClassTypeName = 'Expense'
WHERE GLAccountID = 63
  • Entered : 4/12/2013
You could leave a comment if you were logged in.