Table of Contents

=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:

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

code_formatsql

– 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 );
  1. -
  1. - ————————————————————
  1. - Update the Warehouse IDs
  1. -
  1. - 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))
  1. - 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))
  1. - 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))
  1. - 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))
  1. - 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))
  1. - 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))
  1. - update GL
  UPDATE Ledger
  SET WarehouseID = @NewWarehouseID
  WHERE (WarehouseID in (select ID from PricingElement where ClassTypeID = 12700 and DivisionID = @OldDivisionID))
  1. -
  1. - ————————————————————
  1. - Now update the Inventory IDs
  1. -
  1. - 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)
  1. - 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)
  1. - 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)
  ;
  1. - 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)
  1. - ————————————————————
  1. - Now update the inventory records
  1. -
  1. - 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
  ;
  1. - Now delete the old warehouses
  DELETE from PricingElement
  WHERE ClassTypeID = 12700 AND DivisionID = @OldDivisionID
  1. - Now delete the old inventory records
  DELETE From Inventory
  WHERE DivisionID = @OldDivisionID
  ;

end

else

begin

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

code

Version Information