Differences

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

Link to this comparison view

control_sql_-_reclassify_historic_inventory_adjustments_to_the_parts_expense_account [2019/01/27 11:29]
control_sql_-_reclassify_historic_inventory_adjustments_to_the_parts_expense_account [2019/01/27 11:29] (current)
Line 1: Line 1:
 +=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==
 +
 +
 +
 +<code sql>
 +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
 +</code>
 +
 +
 +
 +==SQL to Make the Change==
 +
 +
 +
 +<code sql>
 +</code>
 +
 +
 +
 +=Version Information=
 +  * Entered : 12/2011
 +  * Version : Control 4.5+
 +
 +
 +