Table of Contents

{$page}

Explanation of SQL

Due to various issues in the past, it is possible for the numbers in Inventory table to not match with sum of the numbers in the Inventory Log table. This query will edit the Inventory table to match the Inventory Log table.

Notes: These queries are designed for Control databases prior to the version with Warehouses. If the Control database is pre-Profits view the inventory_out_of_balance_repair_-_pre-profits page. If the Control database is post-Profits view the inventory_out_of_balance_repair page.

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

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

Version Information