This series of SQL queries will reset all warehouse records back to the default. In essence, it reassigns all past and current warehouses records to the default (ID=10) warehouse.

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 query assumes there is only one inventory record for each warehouse in the database. We have encountered a number of cases where (probably due to some historical issue) certain parts may have multiple warehouse entries. This causes the merge process to fail (since it doesn't know which Inventory record is the one to use) and must be resolved first.

To identify any of the problems, run the following SQL. If it finds any results, they must be cleared before the subsequent queries will run successfully. This query checks to make sure you do not have more than 1 warehouse inventory record for ID = 10, if you did then when we attempt to consolidate we would run into errors. If you do have any records show up you must merge the duplicate warehouse summary records into each other.

SELECT ID, ClassTypeID, ItemName, PartID, WarehouseID, *
FROM Inventory
WHERE PartID IN
(
    SELECT PartID
    FROM Inventory
    WHERE WarehouseID=10 AND ClassTypeID=12200
    GROUP BY PartID
    HAVING COUNT(*) > 1
)
AND WarehouseID=10 AND ClassTypeID=12200
ORDER BY Inventory.ItemName

The following SQL can be run to merge all the warehouse entries into the default warehouse. It also sets this warehouse active and all the others inactive. It does not deactivate warehouses, however. If this is desired it should be done manually afterwards under Setup | Accounting Setup | General.

--
-- this set of queries merges all warehouse items back to the default warehouse (10)
--
-- --------------------------------------------------------------------------------------
-- set all warehouses but 10 inactive, and make 10 the default
-- --------------------------------------------------------------------------------------
UPDATE PricingElement
SET IsActive = 0, IsDefault = 0
WHERE classtypeid = 12700 AND ID  10
;
UPDATE PricingElement
SET IsActive = 1, IsDefault = 1
WHERE classtypeid = 12700 AND ID = 10
-- --------------------------------------------------------------------------------------
-- change all the InventoryID fields to use the Inventory for the default warehouse
-- --------------------------------------------------------------------------------------
-- update the InventoryID for the Transpart
UPDATE Transpart
SET InventoryID =
    (SELECT ID FROM Inventory WHERE Transpart.PartID=Inventory.PartID AND Inventory.WarehouseID=10 AND Inventory.ClassTypeID=12200)
WHERE (PartID IS NOT NULL)
-- update the InventoryID for the VendorTransDetail's AttachedOrderInvID
UPDATE VendorTransDetail
SET AttachedOrderInvID =
    (SELECT ID FROM Inventory WHERE VendorTransDetail.AttachedOrderPartID=Inventory.PartID AND Inventory.WarehouseID=10 AND Inventory.ClassTypeID=12200)
WHERE (AttachedOrderPartID IS NOT NULL)
-- update the InventoryID for the InventoryLog
UPDATE InventoryLog
SET InventoryID =
    (SELECT ID FROM Inventory WHERE InventoryLog.PartID=Inventory.PartID AND Inventory.WarehouseID=10 AND Inventory.ClassTypeID=12200)
WHERE (PartID IS NOT NULL)
-- update the InventoryID for the Ledger
UPDATE Ledger
SET InventoryID =
    (SELECT ID FROM Inventory WHERE Ledger.PartID=Inventory.PartID AND Inventory.WarehouseID=10 AND Inventory.ClassTypeID=12200)
WHERE (PartID IS NOT NULL)
-- --------------------------------------------------------------------------------------
-- change all the warehouses to use the default
-- --------------------------------------------------------------------------------------
-- change all orders to use the standard warehouse
UPDATE TransHeader
SET WarehouseID = 10
WHERE (WarehouseID  10) OR (WarehouseID IS NULL)
-- change order all line items to use the standard warehouse
UPDATE TransDetail
SET WarehouseID = 10
WHERE (WarehouseID  10) OR (WarehouseID IS NULL)
-- change PO/Bill line items to use the standard warehouse
UPDATE VendorTransDetail
SET WarehouseID = 10
WHERE (WarehouseID  10) OR (WarehouseID IS NULL)
-- change all order parts to use the standard warehouse
UPDATE TransPart
SET WarehouseID = 10
WHERE (WarehouseID  10) OR (WarehouseID IS NULL)
-- set all time cards to use the standard warehouse
UPDATE PartUsageCard
SET WarehouseID = 10
WHERE (WarehouseID  10) OR (WarehouseID IS NULL)
-- set all inventoryLog records to use the standard warehouse
UPDATE InventoryLog
SET ToWarehouseID = 10
WHERE (ToWarehouseID  10) AND (ToWareHouseID  IS NOT NULL)
;
UPDATE InventoryLog
SET FromWarehouseID = 10
WHERE (FromWarehouseID  10) AND (FromWareHouseID  IS NOT NULL)
-- merge all inventory records into the default warehouse
UPDATE Inventory
SET
    QuantityBilled        = (SELECT SUM(QuantityBilled)         FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityReceivedOnly  = (SELECT SUM(QuantityReceivedOnly)   FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityOnHand        = (SELECT SUM(QuantityOnHand)         FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityReserved      = (SELECT SUM(QuantityReserved)       FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityAvailable     = (SELECT SUM(QuantityAvailable)      FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityOnOrder       = (SELECT SUM(QuantityOnOrder)        FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityExpected      = (SELECT SUM(QuantityExpected)       FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID)
WHERE WarehouseID = 10 AND ClassTypeID=12200
;
UPDATE Inventory
SET
    QuantityBilled        = 0,
    QuantityReceivedOnly  = 0,
    QuantityOnHand        = 0,
    QuantityReserved      = 0,
    QuantityAvailable     = 0,
    QuantityOnOrder       = 0,
    QuantityExpected      = 0
WHERE ((WarehouseID  10) OR (WarehouseID = NULL)) AND ClassTypeID=12200
-- merge the summary inventory record into the default warehouse
UPDATE Inventory
SET
    QuantityBilled        = (SELECT SUM(QuantityBilled)         FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityReceivedOnly  = (SELECT SUM(QuantityReceivedOnly)   FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityOnHand        = (SELECT SUM(QuantityOnHand)         FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityReserved      = (SELECT SUM(QuantityReserved)       FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityAvailable     = (SELECT SUM(QuantityAvailable)      FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityOnOrder       = (SELECT SUM(QuantityOnOrder)        FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID),
    QuantityExpected      = (SELECT SUM(QuantityExpected)       FROM Inventory I2 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID)
WHERE ClassTypeID=12201
;
-- update GL
UPDATE Ledger
SET WarehouseID = 10
WHERE (WarehouseID  10) AND (WarehouseID IS NOT NULL)

After the Warehouse history has all been reassigned, the only remnants of the non-default warehouse are in the PricingElement and the Inventory Record. These can then be deleted, but only AFTER the above queries have successfully run.

-- 1. delete the unused warehouses
DELETE
FROM PricingElement
WHERE classtypeid = 12700 AND ID  10
;
-- 2. delete the unused Inventory records
DELETE FROM Inventory
WHERE WarehouseID  10 AND classtypeid IN (12200,12202)
;
-- 3. delete division summary inventory records
DELETE FROM Inventory
WHERE DivisionID  10 AND ClassTypeID = 12201
;
-- 4. Delete all divisions except default 10.
UPDATE Employee
SET GroupID = 10
WHERE GroupID IN (SELECT ID FROM EmployeeGroup WHERE IsDivision = 1 AND ID > 10)
;
UPDATE TransHeader
SET ShipFromDivisionID = 10
WHERE ShipFromDivisionID  10
;
UPDATE TransHeader
SET DivisionID = 10
WHERE DivisionID  10
;
UPDATE Ledger
SET DivisionID = 10
WHERE DivisionID  10
;
UPDATE Journal
SET DivisionID = 10 
WHERE DivisionID  10 
;
UPDATE Account
SET DivisionID = 10
WHERE DivisionID  10
;
DELETE FROM EmployeeGroup
WHERE IsDivision = 1 AND ID > 10
;

This query will show the total number of division summary, warehouse summary, and inventory records for each part.If you see any number > 1 in any rows then that is bad.

SELECT
ID
, ItemName
, (SELECT COUNT(*) FROM Inventory WHERE PartID = part.ID AND inventory.ClassTypeID = 12201) AS DivisionSummary
, (SELECT COUNT(*) FROM Inventory WHERE PartID = part.ID AND inventory.ClassTypeID = 12202) AS WarehouseSummary
, (SELECT COUNT(*) FROM Inventory WHERE PartID = part.ID AND inventory.ClassTypeID = 12200) AS InventoryRecord
FROM Part
WHERE ID > 0 AND PartType = 0 AND TrackInventory = 1

• Entered : 10/2010
• Version : 4.5+