CREATE PROCEDURE DBO.InactivateSegment 
  @OldSegment SMALLINT,
  @NewSegment SMALLINT AS

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

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

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

  DELETE FROM AllotmentSegXTab
  WHERE 
    SegmentNumber = @OldSegment AND
    AllotmentNumber IN (SELECT AllotmentNumber
                        FROM AllotmentSegXTab A2 WITH (NOLOCK)
                        WHERE A2.SegmentNumber = @NewSegment)

  UPDATE AllotmentSegXTab
  SET SegmentNumber =  @NewSegment
  WHERE SegmentNumber = @OldSegment


  DELETE FROM ProductSegmentsXtab
  WHERE 
    SegmentNumber = @OldSegment AND
    ProductNumber IN    (SELECT ProductNumber
                         FROM ProductSegmentsXtab A2 WITH (NOLOCK)
                         WHERE A2.SegmentNumber = @NewSegment)

  UPDATE ProductSegmentsXtab
  SET SegmentNumber =  @NewSegment
  WHERE SegmentNumber = @OldSegment

  DELETE FROM SegmentPointOfSaleXTab
  WHERE 
    SegmentNumber = @OldSegment AND 
    SegmentNumber IN    (SELECT SegmentNumber
                         FROM SegmentPointOfSaleXTab A2 WITH (NOLOCK)
		         WHERE A2.SegmentNumber = @NewSegment)

  UPDATE SegmentPointOfSaleXTab
  SET SegmentNumber =  @NewSegment
  WHERE SegmentNumber = @OldSegment

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

-- 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 SegmentProductTypesXTab
  WHERE Segment = @OldSegment

  DELETE FROM YieldSegmentXtab
  WHERE Segment = @OldSegment

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

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

  DELETE FROM MISBudget
  WHERE SegmentNumber =@OldSegment

  /*
  DELETE FROM RoomBookingStatus
  WHERE SegmentNumber = @OldSegment

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

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

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

  UPDATE Availability
  SET FK_SegmentNumber =  @NewSegment
  WHERE FK_SegmentNumber = @OldSegment

  UPDATE Customer
  SET SegmentNumber =  @NewSegment
  WHERE SegmentNumber = @OldSegment

  UPDATE CustomerOrdreBook
  SET SegmentNumber =  @NewSegment
  WHERE SegmentNumber = @OldSegment

  UPDATE MISTable
  SET SegmentNumber =  @NewSegment
  WHERE SegmentNumber = @OldSegment

  UPDATE ProductTypes
  SET SegmentNumber =  @NewSegment
  WHERE SegmentNumber = @OldSegment

  UPDATE Reservations
  SET MarketSegment =  @NewSegment
  WHERE MarketSegment = @OldSegment

  UPDATE MISHistory
  SET FK_PointOfSaleNumber = @NewSegment
  WHERE FK_PointOfSaleNumber = @OldSegment

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