CREATE PROCEDURE [dbo].[GA_AllotmentIntoBookingStatus] @SpecifyOnRoomTypeSegPos BIT, @FormatedStartDate CHAR(8), @FormatedEndDate CHAR(8), @TheHotelNumber INT AS
SET NOCOUNT ON
/*
DECLARE @AllotmentNumber INT,
@RoomTypeNumber INT,
@DateStamp CHAR(8),
@InitialCount SMALLINT,
@CurrentCount SMALLINT,
@TotalChange SMALLINT,
@HotelNumber INT,
@SegmentNumber INT,
@PointOfSaleNumber INT

DECLARE #AllotCur CURSOR FAST_FORWARD FOR

  SELECT AllotmentLines.AllotmentNumber,RoomTypeNumber,DateStamp, IsNull(InitialCount, 0),IsNull(CurrentCount, 0),IsNull(TotalChange, 0), HotelNumber FROM AllotmentLines (NOLOCK)  -- NOT #AllotmentLines because we need the released info!
  INNER JOIN Allotments (NOLOCK) ON Allotments.AllotmentNumber = AllotmentLines.AllotmentNumber
  WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND  ((HotelNumber = @TheHotelNumber AND @TheHotelNumber > -1) OR (@TheHotelNumber = -1) OR (HotelNumber IS NULL))  -- minus 1 is all
  AND (MarkAsDeleted IS NULL OR CONVERT(DATETIME, DateStamp, 112) < MarkAsDeleted)

OPEN #AllotCur
FETCH NEXT FROM #AllotCur
INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp, @InitialCount, @CurrentCount, @TotalChange, @HotelNumber

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  IF (@InitialCount + @TotalChange - @CurrentCount > 0) OR (@InitialCount + @TotalChange <> 0)  -- JP 16/07-2002 don't count negative numbers. TODO: should fix them instead of just skipping them!
  BEGIN
    IF IsNull(@HotelNumber, 0) = 0
      SET @HotelNumber = 0
    IF @SpecifyOnRoomTypeSegPos = 1
      SELECT TOP 1 @SegmentNumber = SegmentNumber, @PointOfSaleNumber = PointOfSaleNumber FROM #RoomBookingStatus (NOLOCK)
      WHERE RoomTypeNumber = @RoomTypeNumber AND DateStamp = @DateStamp AND HotelNumber=@HotelNumber OPTION (KEEP PLAN)
    ELSE
    BEGIN
      SELECT TOP 1 @SegmentNumber = Number FROM Segments (NOLOCK) WHERE Number>0 and (MarkAsDeleted='0') OR (MarkAsDeleted is null)  -- Get any SEG to put it on. Only used when checking for overbook
      SELECT TOP 1 @PointOfSaleNumber = Number FROM PointOfSales (NOLOCK) WHERE Number>0 and (MarkAsDeleted='0') OR (MarkAsDeleted is null) -- Get any POS to put it on. Only used when checking for overbook
    END

    IF @HotelNumber=NULL
      SET @HotelNumber=0

    IF @SpecifyOnRoomTypeSegPos = 1
    BEGIN
      UPDATE #RoomBookingStatus SET
        FreeRoomsAllotment = FreeRoomsAllotment + (@InitialCount + @TotalChange - @CurrentCount),
        TotalRoomsAllotment = TotalRoomsAllotment + (@InitialCount + @TotalChange)
      WHERE RoomTypeNumber = @RoomTypeNumber AND DateStamp = @DateStamp AND SegmentNumber = @SegmentNumber AND PointOfSaleNumber = @PointOfSaleNumber AND HotelNumber = @HotelNumber OPTION (KEEP PLAN)
      if @@rowcount = 0 -- Nothing was updated -> insert row!
        INSERT INTO #RoomBookingStatus (DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment, SoldPrice)
        VALUES (@DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @InitialCount + @TotalChange - @CurrentCount, @InitialCount + @TotalChange, 0)
    END
    ELSE  -- NOT @SpecifyOnRoomTypeSegPos
    BEGIN
      UPDATE #RoomBookingStatus2 SET
        FreeRoomsAllotment = FreeRoomsAllotment + (@InitialCount + @TotalChange - @CurrentCount),
        TotalRoomsAllotment = TotalRoomsAllotment + (@InitialCount + @TotalChange)
      WHERE DateStamp = @DateStamp AND HotelNumber = @HotelNumber AND RoomTypeNumber=@RoomTypeNumber OPTION (KEEP PLAN)
      if @@rowcount = 0 -- Nothing was updated -> insert row!
        INSERT INTO #RoomBookingStatus2 (DateStamp,RoomTypeNumber,HotelNumber,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment)
        VALUES (@DateStamp, @RoomTypeNumber, @HotelNumber, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @InitialCount + @TotalChange - @CurrentCount, @InitialCount + @TotalChange)
    END
  END

  FETCH NEXT FROM #AllotCur
  INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp, @InitialCount, @CurrentCount, @TotalChange, @HotelNumber
END

CLOSE #AllotCur
DEALLOCATE #AllotCur
*/





