=Explanation of SQL=

Prior to 2011, Adjustments made to inventory were posted to the system GL Account called (appropriately) Inventory Adjustments. It is thought more desirable to have those expenses charged to the material's expense account, and so that switch was made in Control 4.5 in late 2010.

This SQL will reclassify the Inventory Adjustment expenses associated with a Part to the GL Account currently identified as the Part's Expense Account.

Note: This will also reclassify Inventory Adjustment expenses associated with the Historic Inventory recreation queries in this WIKI.

=Risk of Data Corruption if Run Improperly=

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.

=SQL=

SQL to Identify the Changes that Will be Made

SELECT 
   YEAR(EntryDateTime) AS TheYear
   , ExpenseAccount.AccountName
   , SUM(amount) AS Total
FROM GL
LEFT JOIN Part ON Part.ID = GL.PartID
LEFT JOIN GLAccount AS ExpenseAccount ON ExpenseAccount.ID = Part.ExpenseAccountID
WHERE GLAccountID = 33
AND EntryDateTime >= '1/1/2009'
GROUP BY YEAR(EntryDateTime), AccountName
ORDER BY TheYear, AccountName

SQL to Make the Change