Differences
This shows you the differences between two versions of the page.
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**. | ||
+ | |||
+ | |||
+ | |||
+ | This SQL will reclassify the Inventory Adjustment expenses associated with a Part to the GL 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 | ||
+ | | ||
+ | , 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 >= ' | ||
+ | group by Year(EntryDateTime), | ||
+ | order by TheYear, AccountName | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ==SQL to Make the Change== | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | =Version Information= | ||
+ | * Entered : 12/2011 | ||
+ | * Version : Control 4.5+ | ||
+ | |||
+ | |||
+ | |||