CREATE PROCEDURE DBO.InactivatePOS
  @OldPOS SMALLINT,
  @NewPOS SMALLINT AS

/* Created november 2014 by Villy Damsbo   */
/* Tool for inactivating POS. Irreversible */

-------------------------------------

-- 1) X-tables with just two fields and NOT carrying meningful data
-- Two-phase maneuvres: Delete duplicates and update the rest

  DELETE FROM AllotmentPOSXTab
  WHERE 
    PointofSaleNumber = @OldPOS AND 
    AllotmentNumber IN (SELECT AllotmentNumber
                        FROM AllotmentPOSXTab A2 WITH (NOLOCK)
                        WHERE A2.PointofSaleNumber = @NewPOS)

  UPDATE AllotmentPOSXTab
  SET PointofSaleNumber =  @NewPOS
  WHERE PointofSaleNumber = @OldPOS

  DELETE FROM ProductPOSXtab
  WHERE 
    PointofSaleNumber = @OldPOS AND
    ProductNumber IN    (SELECT ProductNumber
                         FROM ProductPOSXtab A2 WITH (NOLOCK)
                         WHERE A2.PointofSaleNumber = @NewPOS)

  UPDATE ProductPOSXtab
  SET PointofSaleNumber =  @NewPOS
  WHERE PointofSaleNumber = @OldPOS

  DELETE FROM SegmentPointOfSaleXTab
  WHERE 
    PointOfSaleNumber = @OldPOS AND 
    SegmentNumber IN    (SELECT SegmentNumber
                         FROM SegmentPointOfSaleXTab A2 WITH (NOLOCK)
		         WHERE A2.PointofSaleNumber = @NewPOS)

  UPDATE SegmentPointOfSaleXTab
  SET PointofSaleNumber =  @NewPOS
  WHERE PointofSaleNumber = @OldPOS

------------------------------------

-- 2) X-tables with more than two fields and carrying meningful data
-- Due to Troels, Robert, others and my own investigations 
-- they are never actually used, so...

  DELETE FROM POSPRODUCTTYPESXTAB
  WHERE POS = @OldPOS 

  DELETE FROM YieldPointOfSalesXtab
  WHERE POS = @OldPOS

------------------------------------

-- 3) Ordinary tables with problematic primary keys
-- Due to Svend, Kåre and my own investigations: 

  DELETE FROM MISBudget
  WHERE POSNumber = @OldPOS 

  /*
  DELETE FROM RoomBookingStatus
  WHERE PointofSaleNumber = @OldPOS

  Daily generated table, Kåre says: "Do not touch!"
  */

------------------------------------

-- 4) Ordinary tables
-- A simple update is sufficient

  UPDATE Availability
  SET FK_PointOfSaleNumber =  @NewPOS
  WHERE FK_PointOfSaleNumber = @OldPOS

  UPDATE Customer
  SET PointOfSaleNumber =  @NewPOS
  WHERE PointOfSaleNumber = @OldPOS

  UPDATE MISTable
  SET PointofSaleNumber =  @NewPOS
  WHERE PointofSaleNumber = @OldPOS  

  UPDATE ProductTypes
  SET PointofSaleNumber =  @NewPOS
  WHERE PointofSaleNumber = @OldPOS 

  UPDATE Reservations
  SET PointofSale =  @NewPOS
  WHERE PointofSale = @OldPOS

-------------------------------------
