CREATE PROCEDURE [dbo].[OTAGetRestrictionInfoForChannelDetailed] @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumber INT, @ARateLevelChannelId INT, @ARateLevelIdCSV VARCHAR(1000) AS 
/*
  Created by Kent Johansen, May 2020
  Changes:
  
*/

CREATE TABLE #tmpInventory
(
  SortNumber INT,
  DateStamp DATETIME,
  Name VARCHAR(50),
  RateLevelId INT,
  Allocated INT,
  Type VARCHAR(10),
  RoomTypeNumber INT,
  ProdcutNumber INT,
  RestrictionType SMALLINT,
  RestrictionLength INT,
  RateLevelRestrictionId INT
)

INSERT INTO #tmpInventory
  EXEC OTAGetInventory @AStartDate, @AEndDate, @AHotelNumber, @ARateLevelChannelId, 1, 0, -1, 0, 0

SELECT COUNT(*) AS cnt, DateStamp, RateLevelChannel.RateLevelChannelId, RateLevelChannel.Name AS RateLevelChannelName, 
  #tmpInventory.Name AS RateLevelName, #tmpInventory.Type, #tmpInventory.RoomTypeNumber,
  RateLevelRestriction.RestrictionType, RateLevelRestriction.Id AS RateLevelRestrictionId, RateLevelRestriction.Name AS RateLevelRestrictionName
FROM #tmpInventory
INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = #tmpInventory.RateLevelId
  AND (@ARateLevelIdCSV = '' OR RateLevelChannelRateLevelXTab.FKRateLevelId IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARateLevelIdCSV)))
INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
  AND ISNULL(RateLevelChannel.MultipleRestrictions, 0) = 0
INNER JOIN RateLevelRestriction WITH (NOLOCK) ON RateLevelRestriction.Id = #tmpInventory.RateLevelRestrictionId
WHERE RateLevelChannel.RateLevelChannelId = @ARateLevelChannelId
GROUP BY DateStamp, RateLevelChannel.RateLevelChannelId, RateLevelChannel.Name, 
  #tmpInventory.Name, #tmpInventory.Type, #tmpInventory.RoomTypeNumber,
  RateLevelRestriction.RestrictionType, RateLevelRestriction.Id, RateLevelRestriction.Name

DROP TABLE #tmpInventory