=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.

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)

The following SQL will zero cost and iventory GL entries associated with voided orders that were in WIP when voided.

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
)

Version Information