=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

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

  • Entered : 7/2012
  • Version : 4.6+

Related SQLs

You could leave a comment if you were logged in.