Differences

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

Link to this comparison view

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. ​ The Inventory Activity correctly did so, but a bug in Control caused the GL to record a charge against inventory in in expenses. ​ These entries should have been zero.
 +
 +
 +
 +=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,​ TH.BuiltDate,​ TH.VoidedDate,​ TH.TotalPrice,​
 +  GL.ID as GLID, GL.EntryDateTime,​ GL.Amount, GL.GLAccountID, ​
 +  (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)
 +</​code>​
 +
 +
 +
 +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
 +  (
 +     ​select TH.OrderNumber, ​
 +     ​TH.OrderCreatedDate,​ TH.BuiltDate,​ TH.VoidedDate,​ TH.TotalPrice,​
 +     GL.ID as GLID, GL.EntryDateTime,​ GL.Amount, GL.GLAccountID, ​
 +     ​(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)
 +     ) TempA
 +)
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 11/18/2011
 +  * Version : 4.5+
 +
 +
 +