CREATE PROCEDURE [dbo].[OnlineDominoAvailabilityRateLevel] @ANumberOfRooms SMALLINT, @AAdults SMALLINT,
  @AChildren1 SMALLINT, @AChildren2 SMALLINT, @AStartDate DATETIME, @AEndDate DATETIME,
  @ProductTypeNumber INT , @ErrorLevel INTEGER OUTPUT, @ErrorFilterNumber INTEGER OUTPUT,
  @ErrorDateStamp DATETIME OUTPUT, @ACustomerNumber INTEGER = 0, @AOnlineRoomTypeNumber INTEGER = 0,
  @AHotelNumber VARCHAR(100) = '', @ACustomerRateNumber INT = 0, @AResource TINYINT = 0, @ACheckavailability BIT = 1, 
  @ACountryNumber INT = 1, @AvailabilitySummeryproductMode INT = 2, @IsOwnerBooking BIT = 0, @CampaignCodeText VARCHAR(50) = '', @AChannelType INT = 2 AS

/*
Created by Kent Johansen June 2016

Changes:
  KJ 20240228: Bug: Fix for "No availability shown for online channel". Case #CR240371
  KJ 20210408: Bug: Fix for "Ambiguous column name 'MinPax'."
  KJ 20201019: Ext: Allow owner reservation to be booked with zero price.
  KJ 20201016: Bug/ext: Making sure that Startdate and EndDate is without time part.
  KJ 20191015: Do not show if no adult1 prices
  KJ 20190903: Added Bonuspoint fields
  KJ 20190228: Added channeltype for support for CompanyLogin, OwnerLogin, Campaign
  KJ/TB: 20180423: Added Nochildren
  TBU 20170818: Changed names for room type name
  KC:20170816: Changed: Support for Room in Room when not centralized implemented.
  KJ 20170811: Bug: Fix for "Handle check for total overbook" and multiple other bug fixes
  KJ Reverted some of TBU changes from 1. March.
  TBU20170301: Changed: ORDER BY RoomTypes.HotelNumber, Products.Description, #ProductPrice.RoomPrice
  KJ 20161230: Using UPPER results in table scan. removed to use index
  KJ 20161212: Bug: Fix for "Possible to book room even if hotel is fully booked". Case #CR117223
  TBU 20160922: Added - AND #ProductPrice.RoomPrice <> 0
  TBU 20160922: Added - SET @ErrorLevel = 0
  KJ 20160826: Bug: Fix for "If no availability on departure day then record is not included in resultset"
  KJ 20160825: Bug: Fix for "Maxpax compare not correct - room types where maxpax equals pax not added to result set"
  KJ 20160825: OOO is calculated wrong
  KJ 20160825: Result is empty even if some room types is available
  KJ 20160819: Multiple bugfixes
  KJ 20160711: Implemented centralized allotment
  KJ 20160708: Removed debug info.
*/

SET NOCOUNT ON

DECLARE @tmpNumberOfRooms INT
DECLARE @AInclAllotment BIT

DECLARE @OnlineAvailabilityRateLevel INT
SELECT @OnlineAvailabilityRateLevel = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OnlineAvailabilityRateLevel'

SELECT @AInclAllotment = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VacantRoomsInclAllotment'
SET @AInclAllotment = ISNULL(@AInclAllotment, 1)

DECLARE @TotalNumberOfRooms INT
SELECT @TotalNumberOfRooms = COUNT(*) FROM Rooms WITH (NOLOCK) WHERE HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumber))
SET @TotalNumberOfRooms = ISNULL(@TotalNumberOfRooms, 0)

DECLARE @TotalNumberOfBeds INT
DECLARE @TotalNumberOfRoomsRateLevel INT

SELECT @TotalNumberOfBeds = COUNT(*)
FROM Rooms WITH (NOLOCK)
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
  AND RoomTypes.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumber))
  AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0

SELECT @TotalNumberOfRoomsRateLevel = COUNT(*)
FROM Rooms WITH (NOLOCK)
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
  AND RoomTypes.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumber))
  AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0

CREATE TABLE #ProductPrice
(
  RoomTypeNumber INT,
  ProductNumber INT,
  RoomPriceFirstDay MONEY NULL,
  RoomPrice MONEY NULL,
  RoomPricePerDay MONEY NULL,
  RoomPriceMinTotal MONEY NULL
)

DECLARE @LRoomTypeNumber INT
DECLARE @LProductNumberCSV VARCHAR(MAX)
DECLARE @dt DATETIME
DECLARE @OFSDescreaseVacantRooms BIT
DECLARE @RTNumber INT
DECLARE @ProdNumber INT
DECLARE @CalcTotalPrice MONEY
DECLARE @CalcArrivalPrice MONEY
DECLARE @TotPax INT
DECLARE @ParentRoomType INT

SET @dt = GETDATE()
SET @LProductNumberCSV = ''
SELECT @OFSDescreaseVacantRooms = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='OOSDecreaseVacantRooms'

SET @AStartDate = CAST(CAST(@AStartDate AS DATE) AS DATETIME)
SET @AEndDate = CAST(CAST(@AEndDate AS DATE) AS DATETIME)

SET @TotPax = ISNULL(@AAdults, 0) + ISNULL(@AChildren1, 0) + ISNULL(@AChildren2, 0)

EXEC OnlineGetProduct @ACustomerRateNumber, @ACustomerNumber, @AHotelNumber, @CampaignCodeText, @AStartDate, @AEndDate, @LProductNumberCSV OUTPUT

IF @AOnlineRoomTypeNumber > 0
  SELECT @LRoomTypeNumber = RoomTypeNumber
  FROM OnlineRoomTypes WITH (NOLOCK)
  WHERE OnlineRoomTypeNumber = @AOnlineRoomTypeNumber

SET @LRoomTypeNumber = ISNULL(@LRoomTypeNumber, -1)

CREATE TABLE #tmpRateLevelGetAllocation
(
  DateStamp DATETIME,
  RoomsAllocated INT,
  RateLevelId INT,
  RoomTypeNumber INT,
  ProductNumber INT,
  ATopLevel BIT
)

IF @OnlineAvailabilityRateLevel = 2 -- Centralized
  SET @tmpNumberOfRooms = -1
ELSE
  SET @tmpNumberOfRooms = @ANumberOfRooms

SET @AChannelType = ISNULL(@AChannelType, 2)

INSERT INTO #tmpRateLevelGetAllocation
  EXEC RateLevelGetAllocation @AStartDate, @AEndDate, @dt, @LRoomTypeNumber, @LProductNumberCSV, @tmpNumberOfRooms, @AChannelType, @AHotelNumber

--SELECT * FROM #tmpRateLevelGetAllocation

DECLARE db_cur CURSOR READ_ONLY FAST_FORWARD FOR
  SELECT DISTINCT #tmpRateLevelGetAllocation.RoomTypeNumber, #tmpRateLevelGetAllocation.ProductNumber, ISNULL(RoomTypes.ParentRoomTypeNumber,0) as ParentRoomTypeNumber 
  FROM #tmpRateLevelGetAllocation
  INNER JOIN ROOMTYPES WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
  INNER JOIN OnlineRoomTypes WITH (NOLOCK) ON OnlineRoomTypes.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber  
    AND (OnlineRoomTypes.MaxPaxTotal >= @TotPax
    AND OnlineRoomTypes.MaxAdultPax >= @AAdults 
    AND OnlineRoomTypes.MaxChildren1Pax >= @AChildren1
    AND OnlineRoomTypes.MaxChildren2Pax >= @AChildren2
	  AND ISNULL(OnlineRoomTypes.MinPax, 0) <= @TotPax)
	OR ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
OPEN db_cur

FETCH NEXT FROM db_cur INTO @RTNumber, @ProdNumber, @ParentRoomtype

DECLARE @AAdultPriceIsZero BIT

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @ParentRoomType = 0
  BEGIN
    EXEC ProductPrices @ProdNumber, @AStartDate, @AEndDate, @RTNumber, @ANumberOfRooms, @AAdults, @AChildren1, @AChildren2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
      , 0, 0, 0, 0, 0, 0, 0, 0, -1, @AAdultPriceIsZero OUTPUT
  END
  ELSE
  BEGIN
    EXEC ProductPrices @ProdNumber, @AStartDate, @AEndDate, @RTNumber, @TotPax, 1, 0, 0, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
      , 0, 0, 0, 0, 0, 0, 0, 0, -1, @AAdultPriceIsZero OUTPUT
  END

  IF ISNULL(@AAdultPriceIsZero, 0) = 0 OR @IsOwnerBooking = 1
    INSERT INTO #ProductPrice VALUES (@RTNumber, @ProdNumber, @CalcArrivalPrice, @CalcTotalPrice, 0, 0)

  FETCH NEXT FROM db_cur INTO @RTNumber, @ProdNumber, @ParentRoomtype
END

CLOSE db_cur
DEALLOCATE db_cur

IF (@AChildren1 > 0 OR @AChildren2 > 0)
BEGIN
  --print 'Yes inde'	
  DELETE FROM #ProductPrice 
  WHERE ProductNumber IN 
  (
    SELECT Productnumber 
    FROM Products p WITH (NOLOCK) 
    INNER JOIN ProductExtended pe WITH (NOLOCK) ON p.ProductNumber = pe.FK_ProductNumber 
    WHERE IsNull(pe.NoChildren, 0) = 1
  )
END

CREATE TABLE #tmpAvailability
(
  DateStamp CHAR(8) NOT NULL,
  RoomTypeNumber INT NOT NULL,
  SegmentNumber INT NOT NULL,
  PointOfSaleNumber INT NOT NULL,
  HotelNumber INT NOT NULL,
  SoldRoomType INT NOT NULL,
  ProductNumber INT NOT NULL,
  ReservationGroup INT NOT NULL,
  SoldRooms SMALLINT NULL,
  Adults SMALLINT NULL,
  Children1 SMALLINT NULL,
  Children2 SMALLINT NULL,
  NumberOfConfirmed SMALLINT NULL,
  NumberOfProvisionals SMALLINT NULL,
  NumberOfGuaranteed SMALLINT NULL,
  NumberOfTentative SMALLINT NULL,
  NumberOfWaitinglists SMALLINT NULL,
  NumberOfDayrooms SMALLINT NULL,
  FreeRoomsAllotment SMALLINT NULL,
  TotalRoomsAllotment SMALLINT NULL,
  PaxAllotment SMALLINT NULL,
  NumberOfRoomsSoldToday SMALLINT NULL,
  NumberOfOutOfOrder SMALLINT NULL,
  NumberOfOutForService SMALLINT NULL,
  NumberOfOccupied SMALLINT NULL,
  NumberOfOccupiedGroup SMALLINT NULL,
  SoldPrice MONEY NULL,
  RequestorID INT NULL,
  NumberOfRoomsSoldCourse smallint NULL,
  NumberOfRoomsSoldAttendee smallint NULL,
  NumberOfArrival SMALLINT NULL,
  NumberOfInhouse SMALLINT NULL,
  NumberOfStay SMALLINT NULL,
  NumberOfDeparture SMALLINT NULL,
  ArrivalPax SMALLINT NULL,
  InhousePax SMALLINT NULL,
  StayPax SMALLINT NULL,
  DeparturePax SMALLINT NULL,
  NumberOfWaitingArrival SMALLINT NULL,
  NumberOfTentativeArrival SMALLINT NULL,
  WaitingArrivalPax SMALLINT NULL, 
  TentativeArrivalPax SMALLINT NULL
)

INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @AStartDate, @AEndDate, 1, @AHotelNumber, '', 1, 0, '', @AIncludeOccupiedInOFS = 0

IF @OnlineAvailabilityRateLevel = 1
BEGIN
  SELECT #tmpRateLevelGetAllocation.ProductNumber, #tmpRateLevelGetAllocation.RoomTypeNumber
  INTO #ProductPriceDelete1
  FROM #tmpRateLevelGetAllocation
  LEFT OUTER JOIN #tmpAvailability ON #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
    AND #tmpAvailability.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
  WHERE #tmpRateLevelGetAllocation.DateStamp < @AEndDate
  GROUP BY #tmpRateLevelGetAllocation.DateStamp, #tmpRateLevelGetAllocation.RoomTypeNumber, #tmpRateLevelGetAllocation.ProductNumber, #tmpAvailability.RoomTypeNumber
  HAVING (SUM(#tmpRateLevelGetAllocation.RoomsAllocated) - @ANumberOfRooms -
  (
    SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) +
      CASE WHEN @OFSDescreaseVacantRooms = 1 THEN
        ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
      ELSE
        0
      END +
      CASE WHEN @AInclAllotment = 1 THEN
        ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0)
      ELSE
        0
      END
      FROM #tmpAvailability
      WHERE #tmpAvailability.RequestorId = -2  --  -2 means online reservation
      AND #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
      AND #tmpAvailability.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
      AND #tmpAvailability.ProductNumber = #tmpRateLevelGetAllocation.ProductNumber
  ) < 0) OR
  (@TotalNumberOfBeds > 0 AND @TotalNumberOfBeds - @ANumberOfRooms -
  (
    SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) +
      CASE WHEN @OFSDescreaseVacantRooms = 1 THEN
        ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
      ELSE
        0
      END +
      CASE WHEN @AInclAllotment = 1 THEN
        ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0)
      ELSE
        0
      END
      FROM #tmpAvailability
      INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
        AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
      WHERE #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
  ) < 0)  OR
  (@TotalNumberOfRoomsRateLevel - @ANumberOfRooms -
  (
    SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) +
      CASE WHEN @OFSDescreaseVacantRooms = 1 THEN
        ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
      ELSE
        0
      END +
      CASE WHEN @AInclAllotment = 1 THEN
        ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0)
      ELSE
        0
      END
      FROM #tmpAvailability
      INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
        AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0
      WHERE #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
  ) < 0)

  DELETE #ProductPrice 
  FROM #ProductPrice 
  INNER JOIN #ProductPriceDelete1 ON #ProductPriceDelete1.ProductNumber = #ProductPrice.ProductNumber
    AND #ProductPriceDelete1.RoomTypeNumber = #ProductPrice.RoomTypeNumber
END

IF @OnlineAvailabilityRateLevel = 2 -- Centralized
BEGIN
  SELECT #tmpRateLevelGetAllocation.ProductNumber, #tmpRateLevelGetAllocation.RoomTypeNumber
  INTO #ProductPriceDelete2
  FROM #tmpRateLevelGetAllocation
  LEFT OUTER JOIN #tmpAvailability ON #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
    AND #tmpAvailability.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
  WHERE #tmpRateLevelGetAllocation.DateStamp < @AEndDate
  GROUP BY #tmpRateLevelGetAllocation.DateStamp, #tmpRateLevelGetAllocation.RoomTypeNumber, #tmpRateLevelGetAllocation.ProductNumber, #tmpAvailability.RoomTypeNumber
  HAVING ((SELECT COUNT(*) FROM Rooms WITH (NOLOCK) WHERE RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber) - @ANumberOfRooms - 
  (
      SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + 
	    (CASE WHEN @OFSDescreaseVacantRooms = 1 THEN ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) ELSE ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) END) +
      (CASE WHEN @AInclAllotment = 1 THEN ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) ELSE 0 END)
      FROM #tmpAvailability
      WHERE #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
      AND #tmpAvailability.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
  ) < 0)

  DELETE #ProductPrice 
  FROM #ProductPrice 
  INNER JOIN #ProductPriceDelete2 ON #ProductPriceDelete2.ProductNumber = #ProductPrice.ProductNumber
    AND #ProductPriceDelete2.RoomTypeNumber = #ProductPrice.RoomTypeNumber
	
  SELECT 
    @TotalNumberOfRooms - @ANumberOfRooms - 
    (
        SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + 
	      (CASE WHEN @OFSDescreaseVacantRooms = 1 THEN ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) ELSE ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) END) +
        (CASE WHEN @AInclAllotment = 1 THEN ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) ELSE 0 END)
        FROM #tmpAvailability
        WHERE #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
    ) AS NumberOfRooms
  INTO #tmpAvailabilityDay
  FROM #tmpRateLevelGetAllocation
  LEFT OUTER JOIN #tmpAvailability ON #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
    AND #tmpAvailability.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
  WHERE #tmpRateLevelGetAllocation.DateStamp < @AEndDate
  GROUP BY #tmpRateLevelGetAllocation.DateStamp, #tmpRateLevelGetAllocation.RoomTypeNumber, #tmpRateLevelGetAllocation.ProductNumber, #tmpAvailability.RoomTypeNumber
  HAVING (@TotalNumberOfRooms - @ANumberOfRooms - 
    (
        SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + 
	      (CASE WHEN @OFSDescreaseVacantRooms = 1 THEN ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) ELSE ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) END) +
        (CASE WHEN @AInclAllotment = 1 THEN ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) ELSE 0 END)
        FROM #tmpAvailability
        WHERE #tmpAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation.DateStamp, 112)
    )
  ) < 0
END

IF @OnlineAvailabilityRateLevel = 2
BEGIN
  DECLARE @cnt INT
  SELECT @cnt = COUNT(*) FROM #tmpAvailabilityDay
  IF ISNULL(@cnt, 0) > 0
  BEGIN
    -- Total overbook. New reservations is not allowed
    SET @ErrorLevel = 1
  END
  ELSE
    SET @ErrorLevel = 0
END
ELSE
BEGIN
  -- TBU20160922 Not used in
  SET @ErrorLevel = 0
END

IF @ErrorLevel = 0
BEGIN
  DECLARE @GetVATFrom SMALLINT
  SELECT @GetVATFrom = CONVERT(INT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BookkeepingReport'

  CREATE TABLE #RoomTypeWare
  (
    RoomTypeNumber INT,
    WareNumber INT
  )

  IF @GetVATFrom = 2
  BEGIN
    INSERT INTO #RoomTypeWare
    SELECT rt.RoomTypeNumber, w.WareNumber 
    FROM 
    (
      SELECT RoomTypeNumber, row_number() OVER (ORDER BY SortNumber, Text) AS row_num
      FROM RoomTypes WITH (NOLOCK)
    ) rt    
    INNER JOIN
    (
      SELECT Ware.WareNumber, row_number() OVER (ORDER BY Ware.SortNumber) AS row_num
      FROM Ware WITH (NOLOCK) 
      INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
      WHERE WareGroups.Payment = 2 -- system rooms
      AND Ware.MarkAsDeleted IS NULL
    ) w
    ON rt.row_num = w.row_num
  END
  ELSE
  BEGIN
    INSERT INTO #RoomTypeWare
    SELECT RoomTypeNumber, -1 AS WareNumber
    FROM RoomTypes WITH (NOLOCK)
  END

	select ISNULL(MIN(RoomTypes.OnlinePriority),0) AS MinPriority INTO #MinPriority
	FROM #tmpRateLevelGetAllocation
  INNER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = #tmpRateLevelGetAllocation.ProductNumber
  INNER JOIN #ProductPrice ON #ProductPrice.ProductNumber = #tmpRateLevelGetAllocation.ProductNumber
    AND #ProductPrice.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber 
    AND (@IsOwnerBooking = 1 OR #ProductPrice.RoomPrice <> 0 )
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
  INNER JOIN OnlineRoomTypes WITH (NOLOCK) ON OnlineRoomTypes.OnlineRoomTypeNumber = RoomTypes.OnlineRoomTypeNumber
  GROUP BY RoomTypes.OnlineRoomTypeNumber

  SELECT DISTINCT
    #tmpRateLevelGetAllocation.RoomTypeNumber, #tmpRateLevelGetAllocation.ProductNumber,
    Products.Description, #ProductPrice.RoomPriceFirstDay, #ProductPrice.RoomPrice,
    Products.ProductName, 
    #ProductPrice.RoomPricePerDay, #ProductPrice.RoomPriceMinTotal,
    RoomTypes.HotelNumber, OnlineRoomTypes.OnlineRoomTypeNumber, RoomTypes.OnlinePriority,
    (SELECT [Text] FROM OnlineRoomTypesTranslated WITH (NOLOCK)
    WHERE OnlineRoomTypesTranslated.OnlineRoomTypeNumber = OnlineRoomTypes.OnlineRoomTypeNumber
    AND CountryNumber = @ACountryNumber AND Pax=0) AS RomTypeName,			  
    (SELECT [Text] FROM OnlineRoomTypesTranslated WITH (NOLOCK)
    WHERE OnlineRoomTypesTranslated.OnlineRoomTypeNumber = OnlineRoomTypes.OnlineRoomTypeNumber 
    AND CountryNumber = @ACountryNumber AND Pax=@AAdults + @AChildren1 + @AChildren2) AS RomTypeNamePax,			  
    bpfw.BonusPointEarn, bpfw.BonusPointEarnValue, bpfw.BonusPointSpend, bpfw.BonusPointSpendValue 
  FROM #tmpRateLevelGetAllocation
  LEFT OUTER JOIN #tmpAvailability ON #tmpAvailability.DateStamp = #tmpRateLevelGetAllocation.DateStamp
    AND #tmpAvailability.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
    AND #tmpAvailability.ProductNumber = #tmpRateLevelGetAllocation.ProductNumber
  INNER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = #tmpRateLevelGetAllocation.ProductNumber
  INNER JOIN #ProductPrice ON #ProductPrice.ProductNumber = #tmpRateLevelGetAllocation.ProductNumber
    AND #ProductPrice.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber 
    AND (@IsOwnerBooking = 1 OR #ProductPrice.RoomPrice <> 0)
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
  INNER JOIN OnlineRoomTypes WITH (NOLOCK) ON OnlineRoomTypes.OnlineRoomTypeNumber = RoomTypes.OnlineRoomTypeNumber
  INNER JOIN #RoomTypeWare ON #RoomTypeWare.RoomTypeNumber = #tmpRateLevelGetAllocation.RoomTypeNumber
  OUTER APPLY dbo.BonusPointForWare(#RoomTypeWare.WareNumber, #ProductPrice.RoomPrice, 1) bpfw
  WHERE ISNULL(RoomTypes.OnlinePriority,0) IN (SELECT MinPriority FROM #MinPriority)
  ORDER BY RoomTypes.HotelNumber, Products.Description, #ProductPrice.RoomPrice

  DROP TABLE #MinPriority
  DROP TABLE #RoomTypeWare
END

DROP TABLE #tmpRateLevelGetAllocation
DROP TABLE #tmpAvailability
DROP TABLE #ProductPrice
IF @OnlineAvailabilityRateLevel = 1
  DROP TABLE #ProductPriceDelete1
IF @OnlineAvailabilityRateLevel = 2
BEGIN
  DROP TABLE #ProductPriceDelete2
  DROP TABLE #tmpAvailabilityDay
END