This query will MERGE two divisions. More accurately, it sets one division inactive and sets all records using that division to the other one. It will:

• Change GL/Ledger Records
• Change Order and Shipping Records
• Change Company Records
• Move warehouses from the removed Division to the remaining division.
• Change all inventory records for these warehouses to the new division.

High. LOTS of data is modified in this query. If run incorrectly, there is no recovery. Back-up your data first. 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 will merge to divisions into one
-- It assumes no warehouses exist except the default ... or it will merge those too
--
DECLARE @DeletedDivision VARCHAR(30);
DECLARE @RetainedDivision VARCHAR(30);
DECLARE @DeleteWarehouses bit;
--
-- Enter the names of the deleted and retained division.
-- If you want to delete the warehouses in the deleted division and move everything into the
--    default warehouse of the reatined division, set @DeleteWarehouses = 1.  Otherwise the
--    warehouses will just be moved but not deleted.
-- Then click Run.  This should only take a few minutes to execute.
--
-- Note 1: This change is irreversible, so backup first!
-- Note 2: This query assumes Divisions have NOT been spun out in their own table.  If so, replace
--           the table EmployeeGroup with Division in all 3 queries below
--
SET @DeletedDivision = ' 0);
    if (@NewWarehouseID IS NULL)
      RaisError( 'Error: No DEFAULT Warehouse IN Remaining Division Found', 15, 1 );
 
    --
    -- ------------------------------------------------------------
    -- Update the Warehouse IDs
    --
    -- Update the Orders use the new warehouse
    UPDATE TransHeader
    SET WarehouseID = @NewWarehouseID
    WHERE (WarehouseID IS NULL and DivisionID = @NewDivisionID)
       or (WarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
 
    -- change order all line items to use the standard warehouse
    UPDATE TransDetail
    SET WarehouseID = @NewWarehouseID
    WHERE (WarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
 
    -- change PO/Bill line items to use the standard warehouse
    UPDATE VendorTransDetail
    SET WarehouseID = @NewWarehouseID
    WHERE (WarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
 
    -- change all order parts to use the standard warehouse
    UPDATE TransPart
    SET WarehouseID = @NewWarehouseID
    WHERE (WarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
 
    -- set all time cards to use the standard warehouse
    UPDATE PartUsageCard
    SET WarehouseID = @NewWarehouseID
    WHERE (WarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
 
    -- set all inventoryLog records to use the standard warehouse
    UPDATE InventoryLog
    SET ToWarehouseID = @NewWarehouseID
    WHERE (ToWarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
 
    ;
    UPDATE InventoryLog
    SET FromWarehouseID = @NewWarehouseID
    WHERE (FromWarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
 
    -- update GL
    UPDATE Ledger
    SET WarehouseID = @NewWarehouseID
    WHERE (WarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
 
    --
    -- ------------------------------------------------------------
    -- Now update the Inventory IDs
    --
    -- update the InventoryID for the Transpart
    UPDATE Transpart
    SET InventoryID =
        (SELECT ID FROM Inventory
         WHERE Inventory.PartID     =Transpart.PartID
           AND Inventory.WarehouseID=Transpart.WarehouseID
           AND Inventory.ClassTypeID=12200)
    WHERE (PartID IS NOT NULL) and (WarehouseID IS NOT NULL)
 
    -- update the InventoryID for the VendorTransDetail's AttachedOrderInvID
    UPDATE VendorTransDetail
    SET AttachedOrderInvID =
        (SELECT ID FROM Inventory
         WHERE Inventory.PartID     =VendorTransDetail.AttachedOrderPartID
           AND Inventory.WarehouseID=VendorTransDetail.WarehouseID
           AND Inventory.ClassTypeID=12200)
    WHERE (AttachedOrderPartID IS NOT NULL) AND (WarehouseID IS NOT NULL)
 
    -- update the InventoryID and ToInventoryID for the InventoryLog
    UPDATE InventoryLog
    SET InventoryID =
        (SELECT ID FROM Inventory
         WHERE Inventory.PartID     =InventoryLog.PartID
           AND Inventory.WarehouseID=InventoryLog.FromWarehouseID
           AND Inventory.ClassTypeID=12200)
    WHERE (PartID IS NOT NULL) AND (FromWarehouseID IS NOT NULL)
    ;
    UPDATE InventoryLog
    SET ToInventoryID =
        (SELECT ID FROM Inventory
         WHERE Inventory.PartID     =InventoryLog.PartID
           AND Inventory.WarehouseID=InventoryLog.ToWarehouseID
           AND Inventory.ClassTypeID=12200)
    WHERE (PartID IS NOT NULL) AND (ToWarehouseID IS NOT NULL)
    ;
 
    -- update the InventoryID for the Ledger
    UPDATE Ledger
    SET InventoryID =
        (SELECT ID FROM Inventory
         WHERE Inventory.PartID     =Ledger.PartID
           AND Inventory.WarehouseID=Ledger.WarehouseID
           AND Inventory.ClassTypeID=12200)
    WHERE (PartID IS NOT NULL) AND (WarehouseID IS NOT NULL)
 
    -- ------------------------------------------------------------
    -- Now update the inventory records
    --
    -- merge all inventory records into the new warehouse
    UPDATE Inventory
    SET
        QuantityBilled        = (SELECT SUM(QuantityBilled)         FROM Inventory I2
                                 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID
                                    AND (I2.WarehouseID = @NewWarehouseID
                                         OR I2.WarehouseID IN (SELECT ID FROM PricingElement WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID))
                                ),
        QuantityReceivedOnly  = (SELECT SUM(QuantityReceivedOnly)   FROM Inventory I2
                                 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID
                                    AND (I2.WarehouseID = @NewWarehouseID
                                         OR I2.WarehouseID IN (SELECT ID FROM PricingElement WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID))
                                ),
        QuantityOnHand        = (SELECT SUM(QuantityOnHand)         FROM Inventory I2
                                 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID
                                    AND (I2.WarehouseID = @NewWarehouseID
                                         OR I2.WarehouseID IN (SELECT ID FROM PricingElement WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID))
                                ),
        QuantityReserved      = (SELECT SUM(QuantityReserved)       FROM Inventory I2
                                 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID
                                    AND (I2.WarehouseID = @NewWarehouseID
                                         OR I2.WarehouseID IN (SELECT ID FROM PricingElement WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID))
                                ),
        QuantityAvailable     = (SELECT SUM(QuantityAvailable)      FROM Inventory I2
                                 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID
                                    AND (I2.WarehouseID = @NewWarehouseID
                                         OR I2.WarehouseID IN (SELECT ID FROM PricingElement WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID))
                                ),
        QuantityOnOrder       = (SELECT SUM(QuantityOnOrder)        FROM Inventory I2
                                 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID
                                    AND (I2.WarehouseID = @NewWarehouseID
                                         OR I2.WarehouseID IN (SELECT ID FROM PricingElement WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID))
                                ),
        QuantityExpected      = (SELECT SUM(QuantityExpected)       FROM Inventory I2
                                 WHERE I2.ClassTypeID=12200 AND I2.PartID = Inventory.PartID
                                    AND (I2.WarehouseID = @NewWarehouseID
                                         OR I2.WarehouseID IN (SELECT ID FROM PricingElement WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID))
                                )
    WHERE WarehouseID = @NewWarehouseID AND ClassTypeID=12200
    ;
 
    -- Now delete the old warehouses
    DELETE FROM PricingElement
    WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID
 
    -- Now delete the old inventory records
    DELETE FROM Inventory
    WHERE DivisionID = @OldDivisionID
 
    ;
END
ELSE
BEGIN
  -- Reassign the warehouses
  UPDATE PricingElement
  SET DivisionID = @NewDivisionID, ElementName = ElementName + ' (Reassigned)'
  WHERE ClassTypeID = 12700
    AND DivisionID = @OldDivisionID
END
-- recreate the division summary records
UPDATE I1
SET
    QuantityBilled        = (SELECT SUM(QuantityBilled)         FROM Inventory I2
                             WHERE I2.ClassTypeID=12200 AND I2.PartID = I1.PartID AND I2.DivisionID = I1.DivisionID),
    QuantityReceivedOnly  = (SELECT SUM(QuantityReceivedOnly)   FROM Inventory I2
                             WHERE I2.ClassTypeID=12200 AND I2.PartID = I1.PartID AND I2.DivisionID = I1.DivisionID),
    QuantityOnHand        = (SELECT SUM(QuantityOnHand)         FROM Inventory I2
                             WHERE I2.ClassTypeID=12200 AND I2.PartID = I1.PartID AND I2.DivisionID = I1.DivisionID),
    QuantityReserved      = (SELECT SUM(QuantityReserved)       FROM Inventory I2
                             WHERE I2.ClassTypeID=12200 AND I2.PartID = I1.PartID AND I2.DivisionID = I1.DivisionID),
    QuantityAvailable     = (SELECT SUM(QuantityAvailable)      FROM Inventory I2
                             WHERE I2.ClassTypeID=12200 AND I2.PartID = I1.PartID AND I2.DivisionID = I1.DivisionID),
    QuantityOnOrder       = (SELECT SUM(QuantityOnOrder)        FROM Inventory I2
                             WHERE I2.ClassTypeID=12200 AND I2.PartID = I1.PartID AND I2.DivisionID = I1.DivisionID),
    QuantityExpected      = (SELECT SUM(QuantityExpected)       FROM Inventory I2
                             WHERE I2.ClassTypeID=12200 AND I2.PartID = I1.PartID AND I2.DivisionID = I1.DivisionID)
FROM Inventory I1
WHERE ClassTypeID=12201

• Entered : 7/2012
• Version : 4.6+