{$page}

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 updated with Warehouses, but before the changes for Profits. If the Control database is post-Profits view the inventory_out_of_balance_repair page. If the Control database is pre-Warehouses view the inventory_out_of_balance_repair - pre-Warehouses page.

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.

code_formatsql

–01

–Fix InventoryLog Entries with incorrect InventoryID due to old defect

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

–Fix InventoryLog Entries with incorrect InventoryID due to old defect

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

–Round all InventoryLog amount to 4 digits and convert NULLs to zeros

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) )

     OR QuantityBilled IS NULL OR  QuantityReceivedOnly IS NULL

     OR QuantityReserved IS NULL OR QuantityOnOrder IS NULL

;

–04

–Recompute InventoryItem Amounts to be in sync with the InventoryLog entries

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 I.ClassTypeID = 12200 and

    ( ( 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

–Recompute Inventory Division Summary Amounts to be in sync with the sum of the Inventory entries

UPDATE Inventory

SET QuantityBilled = Coalesce(ID.QuantityBilled,0),

     QuantityReceivedOnly = Coalesce(ID.QuantityReceivedOnly,0),

     QuantityReserved = Coalesce(ID.QuantityReserved,0),

     QuantityOnOrder = Coalesce(ID.QuantityOnOrder,0)

FROM Inventory I

     LEFT JOIN (SELECT   PartID, WarehouseID, DivisionID,

                         ROUND(SUM(QuantityBilled),4) QuantityBilled, 

                         ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly, 

                         ROUND(SUM(QuantityReserved),4) QuantityReserved, 

                         ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder

                FROM     Inventory 

                WHERE    ClassTypeID = 12200

                GROUP BY PartID, WarehouseID, DivisionID ) ID ON (ID.PartID = I.PartID AND ID.DivisionID = I.DivisionID)

WHERE I.ClassTypeID = 12201 and

    ( ( ABS(COALESCE(I.QuantityBilled, 0) - COALESCE(ID.QuantityBilled, 0)) > 0.00001 )

      OR ( ABS(COALESCE(I.QuantityReceivedOnly, 0) - COALESCE(ID.QuantityReceivedOnly, 0)) > 0.00001 )

      OR ( ABS(COALESCE(I.QuantityReserved, 0) - COALESCE(ID.QuantityReserved, 0)) > 0.00001 )

      OR ( ABS(COALESCE(I.QuantityOnOrder, 0) - COALESCE(ID.QuantityOnOrder, 0)) > 0.00001 ) )

;

–06

–Recompute Inventory Warehouse Summary Amounts to be in sync with the sum of the Inventory entries

UPDATE Inventory

SET QuantityBilled = Coalesce(IW.QuantityBilled,0),

     QuantityReceivedOnly = Coalesce(IW.QuantityReceivedOnly,0),

     QuantityReserved = Coalesce(IW.QuantityReserved,0),

     QuantityOnOrder = Coalesce(IW.QuantityOnOrder,0)

FROM Inventory I

     LEFT JOIN (SELECT   PartID, WarehouseID, 

                         ROUND(SUM(QuantityBilled),4) QuantityBilled, 

                         ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly, 

                         ROUND(SUM(QuantityReserved),4) QuantityReserved, 

                         ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder

                FROM     Inventory 

                WHERE    ClassTypeID = 12200

                GROUP BY PartID, WarehouseID ) IW ON (IW.PartID = I.PartID AND IW.WarehouseID = I.WarehouseID)

WHERE I.ClassTypeID = 12202 and

    ( ( ABS(COALESCE(I.QuantityBilled, 0) - COALESCE(IW.QuantityBilled, 0)) > 0.00001 )

      OR ( ABS(COALESCE(I.QuantityReceivedOnly, 0) - COALESCE(IW.QuantityReceivedOnly, 0)) > 0.00001 )

      OR ( ABS(COALESCE(I.QuantityReserved, 0) - COALESCE(IW.QuantityReserved, 0)) > 0.00001 )

      OR ( ABS(COALESCE(I.QuantityOnOrder, 0) - COALESCE(IW.QuantityOnOrder, 0)) > 0.00001 ) )

;

–07

–Round all Inventory amount to 4 digits and convert NULLs to zeros

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) )

;

–08

–Recompute the QuantityOnHand amounts in the InventoryLog to equal QuantityBilled+QuantityReceivedOnly

UPDATE InventoryLog

SET QuantityOnHand = Coalesce(QuantityBilled,0)+Coalesce(QuantityReceivedOnly,0)

WHERE ABS( (Coalesce(QuantityBilled,0)+Coalesce(QuantityReceivedOnly,0)) - Coalesce(QuantityOnHand,0) ) > 0.00001

;

–09

–Recompute the QuantityAvailable amounts in the InventoryLog to equal QuantityOnHand+QuantityReserved

UPDATE InventoryLog

SET QuantityAvailable = Coalesce(QuantityOnHand,0)-Coalesce(QuantityReserved,0)

WHERE ABS( (Coalesce(QuantityOnHand,0)-Coalesce(QuantityReserved,0)) - Coalesce(QuantityAvailable,0) ) > 0.00001

;

–10

–Recompute the QuantityExpected amounts in the InventoryLog to equal QuantityAvailable+QuantityOnOrder

UPDATE InventoryLog

SET QuantityExpected = Coalesce(QuantityAvailable,0)+Coalesce(QuantityOnOrder,0)

WHERE ABS( (Coalesce(QuantityAvailable,0)+Coalesce(QuantityOnOrder,0)) - Coalesce(QuantityExpected,0) ) > 0.00001

;

–11

–Recompute the QuantityOnHand amounts in the Inventory to equal QuantityBilled+QuantityReceivedOnly

UPDATE Inventory

SET QuantityOnHand = Coalesce(QuantityBilled,0)+Coalesce(QuantityReceivedOnly,0)

WHERE ABS( (Coalesce(QuantityBilled,0)+Coalesce(QuantityReceivedOnly,0)) - Coalesce(QuantityOnHand,0) ) > 0.00001

;

–12

–Recompute the QuantityAvailable amounts in the Inventory to equal QuantityOnHand+QuantityReserved

UPDATE Inventory

SET QuantityAvailable = Coalesce(QuantityOnHand,0)-Coalesce(QuantityReserved,0)

WHERE ABS( (Coalesce(QuantityOnHand,0)-Coalesce(QuantityReserved,0)) - Coalesce(QuantityAvailable,0) ) > 0.00001

;

–13

–Recompute the QuantityExpected amounts in the Inventory to equal QuantityAvailable+QuantityOnOrder

UPDATE Inventory

SET QuantityExpected = Coalesce(QuantityAvailable,0)+Coalesce(QuantityOnOrder,0)

WHERE ABS( (Coalesce(QuantityAvailable,0)+Coalesce(QuantityOnOrder,0)) - Coalesce(QuantityExpected,0) ) > 0.00001

code

• Entered : 09/30/2010
• Version :

• Backlinks include_pagepage_componentbacklinks