======  ======



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



<code sql>
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
</code>



This query will clear out the usage from the PartUsageCard table.



<code sql>
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
</code>



The next series of queries will make sure the inventory numbers are in sync between the Inventory and InventoryLog tables.



<code sql>
--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
</code>



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.



<code sql>
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
</code>



===== Version Information =====
  * Entered : __/__/2009
  * Version :