Differences
This shows you the differences between two versions of the page.
— |
control_sql_-_fix_incorrect_gl_inventory_and_cost_entries_when_wip_orders_voided [2019/01/27 11:28] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | =Explanation of SQL= | ||
+ | |||
+ | |||
+ | |||
+ | When an order in WIP is voided, any parts not already marked as used should be returned to inventory. | ||
+ | |||
+ | |||
+ | |||
+ | =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= | ||
+ | |||
+ | |||
+ | |||
+ | The following SQL will **identify** cost and iventory GL entries associated with voided orders that were in WIP when voided. | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | select TH.OrderNumber, | ||
+ | TH.OrderCreatedDate, | ||
+ | GL.ID as GLID, GL.EntryDateTime, | ||
+ | (select AccountName from GLAccount where ID = GLAccountID) as AccountName, | ||
+ | GL.PartID, | ||
+ | (select ItemName from Part where ID = GL.PartID) as Part, | ||
+ | GL.GLClassificationType, | ||
+ | GL.JournalID | ||
+ | from TransHeader TH | ||
+ | join GL on GL.TransactionID = TH.ID | ||
+ | where TH.StatusID = 9 | ||
+ | and TH.TransactionType in (1,6) | ||
+ | and TH.BuiltDate is null | ||
+ | and GL.GLClassificationType in (1003, 5001) | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | The following SQL will **zero** cost and iventory GL entries associated with voided orders that were in WIP when voided. | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | update GL | ||
+ | set Amount = 0, ModifiedDate = GetDate() | ||
+ | where ID in | ||
+ | ( | ||
+ | select GLID | ||
+ | from | ||
+ | ( | ||
+ | | ||
+ | | ||
+ | GL.ID as GLID, GL.EntryDateTime, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | from TransHeader TH | ||
+ | join GL on GL.TransactionID = TH.ID | ||
+ | where TH.StatusID = 9 | ||
+ | and TH.TransactionType in (1,6) | ||
+ | and TH.BuiltDate is null | ||
+ | and GL.GLClassificationType in (1003, 5001) | ||
+ | ) TempA | ||
+ | ) | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Version Information ===== | ||
+ | * Entered : 11/18/2011 | ||
+ | * Version : 4.5+ | ||
+ | |||
+ | |||
+ | |||