Explanation of SQL
This will remove any material inventory usage that is Auto-Generated when an order is mark sale.
The was created in response to a customer who had the option set to auto-generate material usage cards on built, but did not want it set that way. Since this customer did not use the Built status, the query is based on the Sale status.
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
This first query will clear out the usage from the InventoryLog table.
UPDATE InventoryLog SET QuantityBilled = 0, QuantityReceivedOnly = 0, QuantityOnHand = 0, QuantityReserved = 0, QuantityAvailable = 0, QuantityOnOrder = 0, QuantityExpected = 0, UnitCost = 0, Cost = 0 FROM Journal J LEFT JOIN InventoryLog IL ON (IL.ID = J.ID) LEFT JOIN TransHeader TH ON (TH.ID = J.TransactionID) LEFT JOIN Part P ON (P.ID = J.PartID) WHERE J.ClassTypeID = 20530 AND J.Description = 'Posting part usage' AND TH.SaleDate IS NOT NULL AND ( ABS( DATEDIFF(SECOND, TH.SaleDate, J.CompletedDateTime) ) < 60 ) AND TH.ClassTypeID = 10000 AND P.PartType = 0 AND P.TrackInventory = 1
This query will clear out the usage from the PartUsageCard table.
UPDATE PartUsageCard SET Amount = 0, Cost = 0 FROM PartUsageCard PUC LEFT JOIN TransHeader TH ON (TH.ID = PUC.TransHeaderID) LEFT JOIN Part P ON (P.ID = PUC.PartID) WHERE TH.SaleDate IS NOT NULL AND ( ABS( DATEDIFF(SECOND, TH.SaleDate, PUC.PostDate) ) < 60 ) AND TH.ClassTypeID = 10000 AND P.PartType = 0 AND P.TrackInventory = 1
The next series of queries will make sure the inventory numbers are in sync between the Inventory and InventoryLog tables.
--01 UPDATE InventoryLog SET InventoryID = TD.AttachedOrderInvID FROM InventoryLog IL LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID) WHERE IL.TransDetailClassTypeID = 11100 AND ( IL.InventoryID < 0 OR IL.InventoryID IS NULL ) AND TD.AttachedOrderInvID > 0 ; --02 UPDATE InventoryLog SET InventoryID = TP.InventoryID FROM InventoryLog IL LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID) LEFT JOIN TransPart TP ON (TP.ID = TD.AttachedOrderPartID) WHERE IL.TransDetailClassTypeID = 11100 AND ( IL.InventoryID < 0 OR IL.InventoryID IS NULL ) AND TP.InventoryID > 0 ; --03 UPDATE InventoryLog SET QuantityBilled = ROUND(COALESCE(QuantityBilled,0), 4), QuantityReceivedOnly = ROUND(COALESCE(QuantityReceivedOnly,0), 4), QuantityReserved = ROUND(COALESCE(QuantityReserved,0), 4), QuantityOnOrder = ROUND(COALESCE(QuantityOnOrder,0), 4) FROM InventoryLog WHERE ( COALESCE(QuantityBilled,0) ROUND(COALESCE(QuantityBilled,0), 4) ) OR ( COALESCE(QuantityReceivedOnly,0) ROUND(COALESCE(QuantityReceivedOnly,0), 4) ) OR ( COALESCE(QuantityReserved,0) ROUND(COALESCE(QuantityReserved,0), 4) ) OR ( COALESCE(QuantityOnOrder,0) ROUND(COALESCE(QuantityOnOrder,0), 4) ) ; --04 UPDATE Inventory SET QuantityBilled = COALESCE(IL.QuantityBilled,0), QuantityReceivedOnly = COALESCE(IL.QuantityReceivedOnly,0), QuantityReserved = COALESCE(IL.QuantityReserved,0), QuantityOnOrder = COALESCE(IL.QuantityOnOrder,0) FROM Inventory I LEFT JOIN (SELECT InventoryID, PartID, ROUND(SUM(QuantityBilled),4) QuantityBilled, ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly, ROUND(SUM(QuantityReserved),4) QuantityReserved, ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder FROM InventoryLog WHERE InventoryID IS NOT NULL GROUP BY InventoryID, PartID ) IL ON (IL.InventoryID = I.ID) WHERE ( ( ABS(COALESCE(I.QuantityBilled, 0) - COALESCE(IL.QuantityBilled, 0)) > 0.00001 ) OR ( ABS(COALESCE(I.QuantityReceivedOnly, 0) - COALESCE(IL.QuantityReceivedOnly, 0)) > 0.00001 ) OR ( ABS(COALESCE(I.QuantityReserved, 0) - COALESCE(IL.QuantityReserved, 0)) > 0.00001 ) OR ( ABS(COALESCE(I.QuantityOnOrder, 0) - COALESCE(IL.QuantityOnOrder, 0)) > 0.00001 ) ) ; --05 UPDATE Inventory SET QuantityBilled = ROUND(COALESCE(QuantityBilled,0),4), QuantityReceivedOnly = ROUND(COALESCE(QuantityReceivedOnly,0),4), QuantityReserved = ROUND(COALESCE(QuantityReserved,0),4), QuantityOnOrder = ROUND(COALESCE(QuantityOnOrder,0),4) WHERE ( COALESCE(QuantityBilled,0) ROUND(COALESCE(QuantityBilled,0), 4) ) OR ( COALESCE(QuantityReceivedOnly,0) ROUND(COALESCE(QuantityReceivedOnly,0), 4) ) OR ( COALESCE(QuantityReserved,0) ROUND(COALESCE(QuantityReserved,0), 4) ) OR ( COALESCE(QuantityOnOrder,0) ROUND(COALESCE(QuantityOnOrder,0), 4) ) ; --06 UPDATE InventoryLog SET QuantityOnHand = COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0) WHERE ABS( (COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)) - COALESCE(QuantityOnHand,0) ) > 0.00001 ; --07 UPDATE InventoryLog SET QuantityAvailable = COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0) WHERE ABS( (COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)) - COALESCE(QuantityAvailable,0) ) > 0.00001 ; --08 UPDATE InventoryLog SET QuantityExpected = COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0) WHERE ABS( (COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)) - COALESCE(QuantityExpected,0) ) > 0.00001 ; --09 UPDATE Inventory SET QuantityOnHand = COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0) WHERE ABS( (COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)) - COALESCE(QuantityOnHand,0) ) > 0.00001 ; --10 UPDATE Inventory SET QuantityAvailable = COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0) WHERE ABS( (COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)) - COALESCE(QuantityAvailable,0) ) > 0.00001 ; --11 UPDATE Inventory SET QuantityExpected = COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0) WHERE ABS( (COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)) - COALESCE(QuantityExpected,0) ) > 0.00001
After running those queries, the GL for the orders will need to be refreshed. The easiest way to do this is with the “Refresh GL On These Transactions” action in Explorer (when in IDE mode). You can get a list of order that had auto-generated part usage with this query.
Though, it's probably easier to just refresh orders in Sale and Closed status, instead of trying to filter on only the orders that were modified.
SELECT DISTINCT OrderNumber FROM ( SELECT TH.OrderNumber FROM Journal J LEFT JOIN TransHeader TH ON (TH.ID = J.TransactionID) LEFT JOIN Part P ON (P.ID = J.PartID) WHERE J.ClassTypeID = 20530 AND J.Description = 'Posting part usage' AND TH.SaleDate IS NOT NULL AND ( ABS( DATEDIFF(SECOND, TH.SaleDate, J.CompletedDateTime) ) < 60 ) AND TH.ClassTypeID = 10000 AND P.PartType = 0 AND P.TrackInventory = 1 ) T ORDER BY OrderNumber
Version Information
- Entered : //2009
- Version :