merge_divisions

Risk of Data Corruption if Run Improperly
SQL
Version Information
Discussion
Explanation of SQL

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.
Risk of Data Corruption if Run Improperly

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.
SQL

-- 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
Version Information

Entered : 7/2012
Version : 4.6+
Table of Contents

Explanation of SQL

Risk of Data Corruption if Run Improperly

SQL

Version Information
