{$page}

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.

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.

This first query will clear out the usage from the InventoryLog table.

code_formatsql

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_formatsql

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_formatsql

–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_formatsql

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

• Entered : //2009
• Version :

• Backlinks include_pagepage_componentbacklinks