CREATE PROCEDURE dbo.OnlineGetAvailabilityBase21
(
	@StartDate datetime 
	,@EndDate datetime
	,@HotelNumbers varchar(MAX)
	,@NoAdults int
	,@NoChildren1 int
	,@NoChildren2 int
	,@NoOfDays int
	,@NoOfRooms int
	,@IncludePrices bit = 1
	,@CalendarMode bit = 0
	,@CountryNumber int = 1
	,@CustomerRateNumber int = 0
	,@CustomerNumber int = 0
	,@CampaignCodeText varchar(50) = ''
	,@RoomTypeNumber int = NULL
	,@ProductNumber int = NULL
	,@ProductGroupID int = NULL
	,@ProductGroupIDs varchar(MAX) = NULL
)
AS
BEGIN
	-- Change history:
	-- 2022-02-10 MLJ V39 Fixed bug: Handling of release/Cutoff days in rate level was swapped
	-- 2022-01-31 MLJ V38 Fixed bugs: Departure-addon was not added if roomtype was not available on departure date.
	--                                Products with end-date was active one day too long 
	-- 2022-01-18 MLJ V37 Now includes discount in calendar view
	-- 2022-01-13 MLJ V36 Added parameter @ProductGroupIDs
	-- 2022-01-11 MLJ V35: Now supports parameters @ProductNumber = -1 AND @RoomTypeNumber = -1
	-- 2022-01-11 MLJ V34: CampaignProducts are now merged with other products when CampaignCode <> '' and @CustomerRateNumber<>4
	-- 2022-01-10 MLJ V33: Fixed problem duplicate room types
	-- 2022-01-10 MLJ V32: Fixed problem with MaxRooms=0
	-- 2022-01-10 MLJ V31: Added @ProductGroupID parameter
	-- 2021-01-05 MLJ Fixes V30
	-- 2021-12-03 MLJ Support for Yield day color match and other fixes
	-- 2021-11-22 MLJ Fixed errors in support for Yield (2)
	-- 2021-11-22 MLJ Fixed errors in support for Yield
	-- 2021-11-22 MLJ Added support for Yield
	-- 2021-11-15 MLJ Fixed error when RestrictionLength was NULL
	--
	SET NOCOUNT ON;
	--DECLARE @T1 datetime, @T2 datetime, @TTotal1 datetime, @TTotal2 datetime
	--SET @T1 = GETDATE()
	--SET @TTotal1=@T1

	IF @RoomTypeNumber <=0
		SET @RoomTypeNumber = NULL

	DECLARE @MAXINT int = 2147483647
	DECLARE @LastAvailabilityDate datetime = DATEADD(day, @NoOfDays, @EndDate)
	DECLARE @LastAvailabilityDateChar char(8) = CONVERT(CHAR(8), @LastAvailabilityDate, 112)
	DECLARE @StartDateChar char(8) = CONVERT(CHAR(8), @StartDate, 112)
	DECLARE @InclAllotment int = 0;
	SELECT @InclAllotment = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VacantRoomsInclAllotment'
	DECLARE @OOSDecreaseVacantRooms INT = 0;
	SELECT @OOSDecreaseVacantRooms = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='OOSDecreaseVacantRooms';
	DECLARE @OnlineAvailabilityRateLevel INT = 0
	SELECT @OnlineAvailabilityRateLevel = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OnlineAvailabilityRateLevel'
	DECLARE @AddOnFieldsPerPerson Bit = 0
	DECLARE @OnePersonPaysForAll Bit = 0
	IF @IncludePrices=1
	BEGIN
		SELECT @OnePersonPaysForAll=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey ='OnePersonPaysForAll'
		SELECT @AddOnFieldsPerPerson=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AddOnFieldsPerPerson'
	END
	DECLARE @Hotels TABLE
	(
		HotelNumber int NOT NULL PRIMARY KEY,
		HotelCustomerNumber int NOT NULL,
		UseMappingRoomtypes bit NOT NULL
	)
	INSERT INTO @Hotels
	SELECT C.HotelNumber, C.CustomerNumber, CASE WHEN @OnlineAvailabilityRateLevel=0 THEN ISNULL(CAST(dbo.GetHotelSetupKeyValue('OnlineRoomTypeTextPerPAX', C.CustomerNumber) AS bit),0) ELSE 0 END
	FROM CUSTOMER C WITH (NOLOCK) 
	LEFT OUTER JOIN DBO.iter_intlist_to_table(@HotelNumbers) H ON H.number=C.HotelNumber
	WHERE C.CustomerTypeNumber=7 AND ISNULL(C.MARKASDELETED,0)=0
	AND (ISNULL(@HotelNumbers,'') = '' OR H.number=C.HotelNumber)

	DECLARE @Dates TABLE
	(
		DateStamp  datetime NOT NULL PRIMARY KEY,
		_DayOfWeek int NOT NULL,
		DateStampChar char(8) COLLATE DATABASE_DEFAULT NOT NULL
		UNIQUE(DateStampChar)
	)
	DECLARE @Date datetime = @StartDate
	WHILE @Date <= @LastAvailabilityDate
	BEGIN
		INSERT INTO @Dates VALUES (@Date, DatePart(WEEKDAY, @Date), CONVERT(char(8), @Date,112))
		SET @Date = DATEADD(day, 1, @Date)
	END

	DECLARE @TotPax int = @NoAdults+@NoChildren1+@NoChildren2

	--SET @T2 = GETDATE()
	--PRINT 'Initialisation '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
	--SET @T1 = GETDATE()


	DECLARE @TotalNumberOfRooms INT
	SELECT @TotalNumberOfRooms = COUNT(*) 
	FROM Rooms WITH (NOLOCK) 
	INNER JOIN @Hotels H ON H.HotelNumber=ROOMS.HotelNumber


	DECLARE @RoomTypes TABLE
	(
		RoomTypeNumber int NOT NULL PRIMARY KEY,
		RoomCount int,
		OnlineRoomTypeNumber int,
		OnlinePriority int,
		IsBunkBed BIT,
		HotelNumber int NOT NULL,
		UseMappingRoomtypes bit NOT NULL,
		MaxPax int NULL,
		MaxPaxTotal int NULL,
		MaxAdultPax int NULL,
		MaxChildren1Pax int NULL,
		MaxChildren2Pax int NULL,
		MinPax int NULL,
		ResourceTypeNumber int NOT NULL
	)
	INSERT INTO @RoomTypes
	SELECT RT.RoomTypeNumber, R.RoomCount, RT.OnlineRoomTypeNumber, RT.OnlinePriority, 
		ISNULL(CAST(ParentRoomTypeNumber AS bit),0), RT.HotelNumber, ISNULL(H.UseMappingRoomtypes,0), 
		RT.MaxPax, ORT.MaxPaxTotal, ORT.MaxAdultPax, ORT.MaxChildren1Pax, ORT.MaxChildren2Pax, ORT.MinPax, ISNULL(RT.ResourceTypeNumber,0)
	FROM dbo.RoomTypes (NOLOCK) RT 
	INNER JOIN @Hotels H ON H.HotelNumber=RT.HotelNumber
	CROSS APPLY (
		SELECT COUNT(*) RoomCount
		FROM dbo.ROOMS (NOLOCK)
		WHERE RoomTypeNumber=RT.RoomTypeNumber
	) R
	CROSS APPLY (
		SELECT TOP 1 *
		FROM dbo.OnlineRoomTypes (NOLOCK) 
		WHERE RoomTypeNumber=RT.RoomTypeNumber	
		ORDER BY OnlinePriority
	) ORT
	WHERE RT.OnlineRoomTypeNumber > 0

	IF (@@ROWCOUNT=0) AND (@CalendarMode=1)
	BEGIN
		-- Return fully-booked dates
		SELECT DateStamp
		FROM @Dates 
		WHERE DateStamp <= @EndDate
		-- return no-arrival dates
		SELECT CAST(NULL AS datetime)
		WHERE 1=2
		RETURN
	END

	--SET @T2 = GETDATE()
	--PRINT 'Insert @RoomTypes '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
	--SET @T1 = GETDATE()

	DECLARE @ProductArrivalDateDiscount TABLE
	(
		ArrivalDate datetime NOT NULL,
		ProductNumber int NOT NULL,
		DiscountPct money NOT NULL
	)
	DECLARE @ProductCustomerRate TABLE
	(
		ProductNumber int NOT NULL PRIMARY KEY,
		MinimumDaysBeforeArrival int NULL
	)
	IF ISNULL(@ProductNumber,0) > 0
	BEGIN
		INSERT INTO @ProductCustomerRate (ProductNumber) VALUES(@ProductNumber)
	END
	ELSE
	BEGIN
		IF @CustomerRateNumber IN (0,1,2,5)
		BEGIN
			INSERT INTO @ProductCustomerRate
			SELECT P.ProductNumber, proe.ProductOnlineClosedtoArrivaldays
			FROM Products P WITH (NOLOCK)
			INNER JOIN ProductExtended proe WITH (NOLOCK) ON P.ProductNumber = proe.FK_ProductNumber
			WHERE P.ProductDate <= @LastAvailabilityDateChar
			AND P.ProductEndDate >= @StartDateChar
			AND ((@OnlineAvailabilityRateLevel<>0) OR (P.AllowFilterPicassoOnline=1))
			AND ((@OnlineAvailabilityRateLevel<>0) OR (P.DayProduct = 0))
			AND ((@CustomerRateNumber IN (0,5) AND ISNULL(CustomerRate, 0) = 0) OR (@CustomerRateNumber = 1 AND ISNULL(CustomerRate, 0) IN (0,1)) OR (@CustomerRateNumber = 2 AND ISNULL(CustomerRate, 0) = 1))
			AND ISNULL(P.MarkAsDeleted,0)=0
		END
		IF (@CustomerRateNumber IN (3,5)) AND (@CustomerNumber > 0)
		BEGIN
			DECLARE @CustomerProduct TABLE (ProductNumber int NOT NULL PRIMARY KEY, DiscountRooms money NOT NULL, StartDateChar char(8) NOT NULL, EndDate char(8) NOT NULL)
			INSERT INTO @CustomerProduct
				SELECT DISTINCT P.ProductNumber, CF.DiscountRooms, P.ProductDate, P.ProductEndDate
				FROM Products P WITH (NOLOCK)
				INNER JOIN CustomerFieldsPerHotel CF ON CF.CustomerNumber=@CustomerNumber AND P.ProductNumber IN (OvernightProductNumber1,OvernightProductNumber2,OvernightProductNumber3,OvernightProductNumber4,OvernightProductNumber5,OvernightProductNumber6,OvernightProductNumber7,OvernightProductNumber8)
				INNER JOIN @Hotels H ON H.HotelNumber=CF.HotelNumber
				WHERE P.ProductDate <= @LastAvailabilityDateChar
				AND P.ProductEndDate >= @StartDateChar
				AND (@OnlineAvailabilityRateLevel<>0 OR (/*P.AllowFilterPicassoOnline=1 AND*/ P.DayProduct = 0))
				AND ISNULL(P.MarkAsDeleted,0)=0
			MERGE @ProductCustomerRate T
			USING (
				SELECT ProductNumber FROM @CustomerProduct
			) S
			ON (T.ProductNumber=S.ProductNumber)
			WHEN NOT MATCHED BY TARGET THEN
				INSERT (ProductNumber) VALUES (S.ProductNumber)
			;
			IF (@CalendarMode=1) AND (@IncludePrices=1)
			BEGIN
				INSERT @ProductArrivalDateDiscount
				SELECT D.DateStamp, P.ProductNumber, P.DiscountRooms
				FROM @CustomerProduct P
				INNER JOIN @Dates D ON D.DateStampChar >= P.StartDateChar AND D.DateStamp <= DATEADD(day, 1-@NoOfDays, P.EndDate)
			END
		END
		IF ISNULL(@CampaignCodeText,'') <> ''
		BEGIN
			DECLARE @PK_CampaignCode int
			DECLARE @CampaignDiscountPct money

			SELECT @PK_CampaignCode=CampaignCodes.PK_CampaignCode, @CampaignDiscountPct=ISNULL(CampaignCodesDiscount.Value, 0)
			FROM CampaignCodes WITH (NOLOCK)
			INNER JOIN CampaignCodesHotel WITH (NOLOCK) ON CampaignCodesHotel.PK_CampaignCode = CampaignCodes.PK_CampaignCode AND CampaignCodesHotel.EnableAssociatedProducts = 1     
			LEFT OUTER JOIN CampaignCodesDiscount WITH (NOLOCK) ON CampaignCodesDiscount.PK_CampaignCode=CampaignCodes.PK_CampaignCode
			WHERE CampaignCodes.Text=@CampaignCodeText

			DECLARE @CampaignProduct TABLE (ProductNumber int NOT NULL PRIMARY KEY, StartDateChar char(8) NOT NULL, EndDateChar char(8) NOT NULL)
			INSERT INTO @CampaignProduct
				SELECT DISTINCT P.ProductNumber, P.ProductDate, P.ProductEndDate
				FROM Products P WITH (NOLOCK)
				INNER JOIN CampaignCodesProducts WITH (NOLOCK) ON CampaignCodesProducts.PK_CampaignCode=@PK_CampaignCode AND P.ProductNumber = CampaignCodesProducts.FK_productNumber AND CampaignCodesProducts.Enabled = 1
				WHERE P.ProductDate <= @LastAvailabilityDateChar
				AND P.ProductEndDate >= @StartDateChar
				AND ISNULL(P.MarkAsDeleted,0)=0
		
			MERGE @ProductCustomerRate T
			USING (
				SELECT ProductNumber FROM @CampaignProduct
			) S
			ON (T.ProductNumber=S.ProductNumber)
			WHEN NOT MATCHED BY TARGET THEN
				INSERT (ProductNumber) VALUES (S.ProductNumber)
			;
			IF (@CalendarMode=1) AND (@IncludePrices=1) AND (ISNULL(@CampaignDiscountPct,0) > 0)
			BEGIN
				DECLARE @CampaignDates TABLE (DateStamp datetime NOT NULL)

				INSERT @CampaignDates
				SELECT D.DateStamp
				FROM @Dates D
				INNER JOIN [dbo].[CampaignCodes] CC ON CC.PK_CampaignCode=@PK_CampaignCode
					AND (CC.Period = 0 OR CC.FromDate <= D.DateStamp)
					AND (CC.Period = 0 OR CC.ToDate >= D.DateStamp)
					AND (CC.BookingDateActive=0 OR CC.BookingFromDate <= D.DateStamp)
					AND (CC.BookingDateActive=0 OR CC.BookingToDate >= D.DateStamp)
					AND (CC.AllowSunday=1 OR D._DayOfWeek<>1)
					AND (CC.AllowMonday=1 OR D._DayOfWeek<>2)
					AND (CC.AllowTuesday=1 OR D._DayOfWeek<>3)
					AND (CC.AllowWednesday=1 OR D._DayOfWeek<>4)
					AND (CC.AllowThursday=1 OR D._DayOfWeek<>5)
					AND (CC.AllowFriday=1 OR D._DayOfWeek<>6)
					AND (CC.AllowSaturday=1 OR D._DayOfWeek<>7)

				MERGE @ProductArrivalDateDiscount T
				USING (
					SELECT D.DateStamp, P.ProductNumber
					FROM @Dates D
					CROSS APPLY (
						SELECT 1 OK
						FROM @CampaignDates CD 
						WHERE CD.DateStamp>=D.DateStamp AND CD.DateStamp<DATEADD(day, @NoOfDays, D.Datestamp)
						HAVING COUNT(*) > = @NoOfDays
					) X
					INNER JOIN @CampaignProduct P ON P.StartDateChar <= D.DateStampChar AND P.EndDateChar >= D.DateStampChar
				) S
				ON (T.ArrivalDate=S.Datestamp AND T.ProductNumber=S.ProductNumber)
				WHEN NOT MATCHED BY TARGET THEN
					INSERT (ArrivalDate, ProductNumber, DiscountPct) VALUES (S.Datestamp, S.ProductNumber, @CampaignDiscountPct)
				WHEN MATCHED AND (@CampaignDiscountPct > T.DiscountPct) THEN
					UPDATE SET DiscountPct = @CampaignDiscountPct
				;
			END
		END

		IF ISNULL(@ProductGroupID,0) > 0 
		BEGIN
			DELETE FROM @ProductCustomerRate
			WHERE ProductNumber NOT IN (SELECT ProductNumber FROM [dbo].[ProductProductGroupXtab] WHERE [ProductGroupID]=@ProductGroupID)
		END
		IF ISNULL(@ProductGroupIDs,'') <> ''
		BEGIN
			DELETE FROM @ProductCustomerRate
			WHERE ProductNumber NOT IN (
				SELECT ProductNumber FROM [dbo].[ProductProductGroupXtab] X
				INNER JOIN DBO.iter_intlist_to_table(@ProductGroupIDs) I ON I.number=X.ProductGroupID
			)
		END
	END

	--SET @T2 = GETDATE()
	--PRINT 'Insert @CustomerRateNumber '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
	--SET @T1 = GETDATE()

	DECLARE @ProductPriceDay TABLE
	(
		ProductNumber int NOT NULL,
		RoomtypeNumber int NOT NULL,
		DateStamp datetime NOT NULL,
		DepartureDate datetime NOT NULL,
		ArrivalPrice money NOT NULL,
		StayPrice money NOT NULL,
		DeparturePrice money NOT NULL,
		ArrivalAllowed bit NOT NULL,
		MinStayPrice money NOT NULL,
		AdultPriceIsZero bit NOT NULL,
		CheckArrivalPrice money NOT NULL,
		CheckStayPrice money NOT NULL,
		CheckDeparturePrice money NOT NULL,
		StayAllowed bit NOT NULL,
		MinPricePerDay money NOT NULL,
		LastDate datetime NOT NULL,
		RoomsAvailable int NOT NULL
		PRIMARY KEY CLUSTERED (DateStamp, RoomTypeNumber, ProductNumber)
	)
	DECLARE @TotalPrices TABLE
	(
		DateStamp datetime NOT NULL,
		RoomtypeNumber int NOT NULL,
		ProductNumber int NOT NULL,
		Price money NOT NULL,
		RoomsAvailable int NOT NULL
		PRIMARY KEY CLUSTERED (DateStamp, RoomTypeNumber, ProductNumber)
	)
	IF @OnlineAvailabilityRateLevel <= 0
	BEGIN -- @OnlineAvailabilityRateLevel <= 0
		DECLARE @SystemDate DATETIME
		SELECT @SystemDate=CONVERT(DATETIME, SystemDate, 112) FROM System WITH (NOLOCK) WHERE Number=1
		DECLARE @AvailCheckAllTypes BIT = 0
		SELECT @AvailCheckAllTypes=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlineCheckAvailAllTypes' -- Changed OnlineCheckAvailAllTypes to PicassoOnlineCheckAvailAllTypes
		DECLARE @UseAllowZeroPriceOnProduct BIT = 0
		SELECT @UseAllowZeroPriceOnProduct=keyvalue FROM Hotelsetup WITH (NOLOCK) WHERE searchkey ='onlinebookwithzeropriceperdayfromproduct'
		DECLARE @IgnoreRoomtypeResources BIT = 0
		SELECT @IgnoreRoomtypeResources= KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='onlineignoreroomtyperesource' 
		DECLARE @AvailabilitySummeryproductMode int = CASE WHEN @NoChildren1=0 THEN 2 ELSE 0 END

		CREATE TABLE #Yield (
			Datestamp datetime NOT NULL,
			HotelNumber int NOT NULL,
			MinRoomsAvail int NOT NULL,
			MaxRoomsAvail int NOT NULL,
			MinStayPrice money NOT NULL,
			MinPricePerDay money NOT NULL,
			YieldNumber int NOT NULL,
			WeekDays int NOT NULL,
			MinChildrenPriceFound bit NOT NULL
			PRIMARY KEY CLUSTERED (Datestamp, HotelNumber)
		)
		;
		WITH T AS
		(
			SELECT 
				RBSY.DateStamp,
				RBSY.HotelNumber,
				D._DayOfWeek,
				ISNULL(MaxDays, 0) MaxDays, 
				ISNULL(MinDays, 0) MinDays, 
				ISNULL(TimePeriod, 0) TimePeriod, 
				ISNULL(MinRoomsAvail, 0) MinRoomsAvail, 
				ISNULL(MaxRoomsAvail, 0) MaxRoomsAvail, 
				ISNULL(WholeWeeks, 0) WholeWeeks, 
				ISNULL(DaysBeforeArrival, 0) DaysBeforeArrival,
				ISNULL(ArrivalMonday, 0) ArrivalMonday, 
				ISNULL(ArrivalTuesday, 0) ArrivalTuesday, 
				ISNULL(ArrivalWednesday, 0) ArrivalWednesday, 
				ISNULL(ArrivalThursday, 0) ArrivalThursday, 
				ISNULL(ArrivalFriday, 0) ArrivalFriday, 
				ISNULL(ArrivalSaturday, 0) ArrivalSaturday, 
				ISNULL(ArrivalSunday, 0) ArrivalSunday,
				ISNULL(MinStayPrice, 0) MinStayPrice, 
				ISNULL(MinPricePerDay, 0) MinPricePerDay,
				Y.YieldNumber
			FROM RoomBookingStatusYield RBSY WITH (NOLOCK) 
			INNER JOIN @Hotels H ON H.HotelNumber=RBSY.HotelNumber
			INNER JOIN @Dates D ON D.DateStamp=RBSY.DateStamp
			INNER JOIN Yield Y WITH (NOLOCK) ON Y.YieldNumber=RBSY.OnlineYieldFilter AND Y.FilterTypeNumber=1 -- 1 = DELPHI: TYieldFilter = yfPicassoOnline
			WHERE RBSY.OnlineYieldFilter > 0
		)
		INSERT INTO #Yield
		SELECT T.DateStamp, T.HotelNumber, T.MinRoomsAvail, T.MaxRoomsAvail, T.MinStayPrice,
			CASE 
				WHEN T.MinPricePerDay > 0 THEN T.MinPricePerDay
				WHEN YMP.FK_Yield IS NOT NULL THEN
					ISNULL(CASE @NoAdults WHEN 1 THEN YMP.Adult1 WHEN 2 THEN YMP.Adult2 WHEN 3 THEN YMP.Adult3 WHEN 4 THEN YMP.Adult4 WHEN 5 THEN YMP.Adult5 WHEN 6 THEN YMP.Adult6 WHEN 7 THEN YMP.Adult7 WHEN 8 THEN YMP.Adult8 WHEN 9 THEN YMP.Adult9 ELSE 0 END, 0)
					+
					ISNULL(CASE @NoChildren1 WHEN 1 THEN YMP.Child11 WHEN 2 THEN YMP.Child12 WHEN 3 THEN YMP.Child13 WHEN 4 THEN YMP.Child14 WHEN 5 THEN YMP.Child15 WHEN 6 THEN YMP.Child16 WHEN 7 THEN YMP.Child17 WHEN 8 THEN YMP.Child18 WHEN 9 THEN YMP.Child19 ELSE 0 END, 0)
					+
					ISNULL(CASE @NoChildren2 WHEN 1 THEN YMP.Child21 WHEN 2 THEN YMP.Child22 WHEN 3 THEN YMP.Child23 WHEN 4 THEN YMP.Child24 WHEN 5 THEN YMP.Child25 WHEN 6 THEN YMP.Child26 WHEN 7 THEN YMP.Child27 WHEN 8 THEN YMP.Child28 WHEN 9 THEN YMP.Child29 ELSE 0 END, 0)
				ELSE 0
			END MinPricePerDay,
			T.YieldNumber,
			CAST(ArrivalSunday AS int) + 
			CAST(ArrivalMonday AS int) *2 + 
			CAST(ArrivalTuesDay AS int)*4 + 
			CAST(ArrivalWednesDay AS int)*8 + 
			CAST(ArrivalThursday AS int) *16 + 
			CAST(ArrivalFriday AS int) *32 + 
			CAST(ArrivalSaturday AS int) *64 WeekDays,
			CASE WHEN (ISNULL(YMP.Child11, 0) + ISNULL(YMP.Child21, 0)) > 0 THEN 1 ELSE 0 END
		FROM T
		LEFT OUTER JOIN YieldOnlineRoomMinimumPrice YMP WITH (NOLOCK) ON YMP.FK_Yield=T.YieldNumber
		WHERE NOT (
			((TimePeriod = 0) AND (@NoOfDays > MaxDays))
			OR 
			((TimePeriod = 0) AND (@NoOfDays < MinDays))
			OR
			((WholeWeeks=1) AND (@NoOfDays % 7 > 0))
			OR
			((TimePeriod > 0) AND (@NoOfDays <> TimePeriod))
			OR
			((DaysBeforeArrival > 0) AND (DateAdd(DAY, -DaysBeforeArrival, DateStamp) < @SystemDate))
		)
		--SET @T2 = GETDATE()
		--PRINT 'Insert #Yield '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

		DECLARE @YieldAvaliability TABLE  (
			[DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
			[RoomTypeNumber] [int] NOT NULL ,
			[HotelNumber] [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,
			[NumberOfNotSaleable] [smallint] NULL ,
			[NummberOfOccupiedGroup] [smallint] NULL,
			[SoldPrice] [MONEY] NULL
		)

		INSERT INTO @YieldAvaliability
		EXEC GenerateAvailability @StartDate, @LastAvailabilityDate , 0, @HotelNumbers, @SystemDate, 0, 0,'', '', 2, 0, 1, 0, 0, 0

		--SET @T2 = GETDATE()
		--PRINT 'EXEC GenerateAvailability '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		DECLARE @YieldBookedRooms TABLE
		(
			RoomTypeNumber int NOT NULL,
			DateStamp datetime NOT NULL,
			AvailRoomCount int NOT NULL,
			UnavailRoomCount int NOT NULL
			PRIMARY KEY CLUSTERED (RoomTypeNumber, DateStamp)
		)
		INSERT INTO @YieldBookedRooms
		SELECT X.RoomTypeNumber, X.DateStamp, RT.RoomCount-X.UnavailRoomCount, X.UnavailRoomCount
		FROM @RoomTypes RT
		INNER JOIN (
			SELECT RBS.RoomTypeNumber, RBS.DateStamp, 
		    SUM(IsNull(SoldRooms, 0) + IsNull(NumberOfOutOfOrder, 0) + IsNull(NumberOfOutForService, 0) + IsNull(TotalRoomsAllotment, 0)+ IsNull(NumberOfNotSaleable, 0)) UnavailRoomCount
			FROM @YieldAvaliability RBS  
			INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=RBS.RoomTypeNumber
			WHERE RBS.DateStamp BETWEEN @StartDate AND @LastAvailabilityDate
			GROUP BY RBS.RoomTypeNumber, RBS.DateStamp
		) X ON X.RoomTypeNumber=RT.RoomTypeNumber 

		--SET @T2 = GETDATE()
		--PRINT 'Insert @YieldBookedRooms '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

		IF @IgnoreRoomtypeResources=1
		BEGIN
			UPDATE @RoomTypes
			SET ResourceTypeNumber=0
			WHERE ResourceTypeNumber<>0
			--SET @T2 = GETDATE()
			--PRINT 'Update @RoomTypes ResourceTypeNumber '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()
		END
		--DECLARE @YieldOverbookedDates TABLE
		--(
		--	Datestamp datetime NOT NULL, 
		--	HotelNumber int NOT NULL
		--	PRIMARY KEY CLUSTERED (DateStamp,HotelNumber)
		--)
		-- The check for total overbooking has been removed since the original stored procedure OnlineCheckDominoAvailability did not have it
		--INSERT INTO @YieldOverbookedDates
		--SELECT Datestamp, HotelNumber 
		--FROM @YieldBookedRooms YBR
		--INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=YBR.RoomTypeNumber
		--GROUP BY DateStamp, HotelNumber
		--HAVING SUM(UnavailRoomCount) >= @TotalNumberOfRooms

		----SET @T2 = GETDATE()
		----PRINT 'Insert @YieldOverbookedDates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		----SET @T1 = GETDATE()

		CREATE TABLE #YieldVacantRooms 
		(
			[DateStamp] datetime NOT NULL ,
			[RoomTypeNumber] [int] NOT NULL ,
			[OnlineRoomTypeNumber] [int] NOT NULL,
			[OnlinePriority] [int] NOT NULL,
			[HotelNumber] [int] NOT NULL,
			[VacantRooms] [int] NOT NULL,
			[MinRoomsAvail] [int] NOT NULL,
			[MaxRoomsAvail] [int] NOT NULL,
			[MaxRooms] [int] NULL,
			IsBunkBed BIT NULL,
			WeekDays int NOT NULL,
			MinStayPrice money NOT NULL,
			MinPricePerDay money NOT NULL,
			[DateStampChar] char(8) COLLATE DATABASE_DEFAULT NOT NULL,
			YieldNumber int NOT NULL,
			MinChildrenPriceFound bit NOT NULL
			PRIMARY KEY CLUSTERED ([DateStamp], [RoomTypeNumber])
		)

		INSERT INTO #YieldVacantRooms 
		SELECT D.DateStamp, RT.RoomTypeNumber, RT.OnlineRoomTypeNumber, ISNULL(RT.OnlinePriority,0) OnlinePriority, RT.HotelNumber,
		CASE 
			--WHEN OD.DateStamp IS NOT NULL THEN 0
			WHEN @AvailCheckAllTypes=0 THEN RT.RoomCount-@NoOfRooms-ISNULL(BR.UnavailRoomCount,0)
			ELSE RT.RoomCount-ISNULL(BR.UnavailRoomCount,0)
		END,
		ISNULL(Y.MinRoomsAvail,0), ISNULL(Y.MaxRoomsAvail,0), X.MaxRooms, RT.IsBunkBed, Y.WeekDays, Y.MinStayPrice, Y.MinPricePerDay, D.DatestampChar, Y.YieldNumber, Y.MinChildrenPriceFound
		FROM @Dates D
		INNER JOIN @RoomTypes RT ON RT.ResourceTypeNumber=0
		LEFT OUTER JOIN @YieldBookedRooms BR ON BR.DateStamp=D.DateStamp AND BR.RoomTypeNumber=RT.RoomTypeNumber
		INNER JOIN #Yield Y ON Y.Datestamp=D.DateStamp AND Y.HotelNumber=RT.HotelNumber
		LEFT OUTER JOIN YieldOnlineRoomTypesXTab X WITH (NOLOCK) ON X.YieldNumber=Y.YieldNumber AND X.OnlineRoomTypeNumber=RT.OnlineRoomTypeNumber
		--LEFT OUTER JOIN @YieldOverbookedDates OD ON OD.DateStamp=D.DateStamp AND OD.HotelNumber=RT.HotelNumber

		--SET @T2 = GETDATE()
		--PRINT 'Insert #YieldVacantRooms '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()
		IF @AvailCheckAllTypes = 0
		BEGIN
			DELETE FROM #YieldVacantRooms
			WHERE (VacantRooms+@NoOfRooms < MinRoomsAvail)
			OR (VacantRooms+@NoOfRooms > MaxRoomsAvail AND MaxRoomsAvail > 0)
		END
		ELSE
		BEGIN  
			DELETE FROM #YieldVacantRooms
			FROM #YieldVacantRooms VR
			INNER JOIN (
				SELECT DateStamp, HotelNumber
				FROM #YieldVacantRooms 
				GROUP BY DateStamp, HotelNumber
				HAVING (SUM(VacantRooms-CASE WHEN MaxRooms < 0 THEN MaxRooms ELSE 0 END)-@NoOfRooms < MIN(MinRoomsAvail)) 
					OR (SUM(VacantRooms)-@NoOfRooms > MAX(MaxRoomsAvail) AND MAX(MaxRoomsAvail) > 0)
			) X ON X.DateStamp=VR.DateStamp AND X.HotelNumber=VR.HotelNumber
		END

		--SET @T2 = GETDATE()
		--PRINT 'Delete #YieldVacantRooms MinRoomsAvail/MaxRoomsAvail '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

		DECLARE @UseCalendar BIT = NULL
		SELECT @UseCalendar = Keyvalue FROM Hotelsetup WITH (NOLOCK) Where Searchkey = 'OnlineShowCalender'
		IF @UseCalendar=1
		BEGIN
			DELETE FROM #YieldVacantRooms
			FROM #YieldVacantRooms VR
			INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber= VR.RoomTypeNumber
				AND RT.UseMappingRoomtypes=1
				AND ISNULL(RT.MaxPax, 0) < @TotPax 
				AND RT.IsBunkBed=0

			--SET @T2 = GETDATE()
			--PRINT 'Delete #YieldVacantRooms MaxPax '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()

			DELETE FROM #YieldVacantRooms
			FROM #YieldVacantRooms VR
			INNER JOIN @RoomTypes RT ON RT.OnlineRoomTypeNumber= VR.OnlineRoomTypeNumber
			WHERE (RT.MaxPaxTotal < @TotPax AND RT.IsBunkBed=0)
			OR (RT.MaxAdultPax < @NoAdults AND RT.IsBunkBed=0)
			OR (RT.MaxChildren1Pax < @NoChildren1)
			OR (RT.MaxChildren2Pax < @NoChildren2)
			OR (ISNULL(RT.MinPax,0) > @TotPax)

			--SET @T2 = GETDATE()
			--PRINT 'Delete #YieldVacantRooms MaxPax '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()
		END

		DELETE FROM #YieldVacantRooms
		WHERE ( 
			(MaxRooms < 0 AND VacantRooms < MaxRooms ) 
			OR  
			( MaxRooms > 0 AND VacantRooms < 1  )
			OR
			(MaxRooms = 0)
		)

		--SET @T2 = GETDATE()
		--PRINT 'Delete #YieldVacantRooms MaxRooms '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

		DECLARE @YieldSoldOutDates TABLE
		(
			Datestamp datetime NOT NULL,
			HotelNumber int NOT NULL
			PRIMARY KEY CLUSTERED (Datestamp, HotelNumber)
		)
		INSERT INTO @YieldSoldOutDates
		SELECT Datestamp, HotelNumber
		FROM #YieldVacantRooms
		GROUP BY Datestamp, HotelNumber
		HAVING SUM(VacantRooms-CASE WHEN MaxRooms < 0 THEN MaxRooms ELSE 0 END) < @NoOfRooms

		DELETE FROM #YieldVacantRooms 
		FROM #YieldVacantRooms VR
		INNER JOIN @YieldSoldOutDates D ON D.Datestamp=VR.DateStamp AND D.HotelNumber=VR.HotelNumber

		--SET @T2 = GETDATE()
		--PRINT 'Delete #YieldVacantRooms sold out dates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

		DELETE FROM #YieldVacantRooms 
		FROM #YieldVacantRooms VR
		INNER JOIN (
			SELECT VR.OnlineRoomTypeNumber, VR.DateStamp, MIN(VR.OnlinePriority) OnlinePriority
			FROM #YieldVacantRooms VR
			INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=VR.RoomTypeNumber AND RT.UseMappingRoomtypes=1
			GROUP BY VR.OnlineRoomTypeNumber, VR.DateStamp
		) X ON X.OnlineRoomTypeNumber= VR.OnlineRoomTypeNumber AND X.DateStamp=VR.DateStamp
		WHERE VR.OnlinePriority > X.OnlinePriority

		--SET @T2 = GETDATE()
		--PRINT 'Delete #YieldVacantRooms @UseMappingRoomtypes '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

		DELETE FROM #YieldVacantRooms 
		WHERE [VacantRooms]-CASE WHEN MaxRooms < 0 THEN MaxRooms ELSE 0 END <= [MinRoomsAvail]

		--SET @T2 = GETDATE()
		--PRINT 'Delete #YieldVacantRooms [VacantRooms] <= [MinRoomsAvail] '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

		IF @RoomTypeNumber IS NOT NULL
		BEGIN
			DELETE FROM #YieldVacantRooms 
			WHERE RoomTypeNumber<>@RoomTypeNumber
			--SET @T2 = GETDATE()
			--PRINT 'Delete #YieldVacantRooms RoomTypeNumber <> @RoomTypeNumber '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()
		END

		DECLARE @YieldAVailableDates TABLE (
			Datestamp datetime PRIMARY KEY
		)
		INSERT INTO @YieldAVailableDates
		SELECT Datestamp
		FROM #YieldVacantRooms
		GROUP BY DateStamp
		HAVING SUM(VacantRooms) >= @NoOfRooms

		--SET @T2 = GETDATE()
		--PRINT 'Insert @YieldAVailableDates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

		IF @CalendarMode=1
		BEGIN
			-- Select Fully booked dates
			SELECT Datestamp
			FROM @Dates D
			WHERE DateStamp <= @EndDate
			EXCEPT
			SELECT Datestamp
			FROM @YieldAVailableDates

			--SET @T2 = GETDATE()
			--PRINT 'Select Fully booked dates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()
		END
		IF (@IncludePrices=0) AND (@CalendarMode=1)
		BEGIN
			-- Select no-arrival dates
			IF @NoOfDays=1
			BEGIN
				SELECT Datestamp
				FROM @Dates D
				WHERE DateStamp <= @EndDate
				EXCEPT
				SELECT Datestamp
				FROM @YieldAVailableDates
			END
			ELSE 
			BEGIN
				SELECT Datestamp
				FROM @Dates D
				WHERE DateStamp <= @EndDate
				EXCEPT
				SELECT AD.Datestamp
				FROM @YieldAVailableDates AD
				INNER JOIN @YieldAVailableDates AD2 ON AD2.Datestamp >= AD.Datestamp AND AD2.Datestamp < DATEADD(day, @NoOfDays, AD.Datestamp)
				GROUP BY AD.Datestamp
				HAVING COUNT(*) >= @NoOfDays
			END
			--SET @T2 = GETDATE()
			--PRINT 'Select no-arrival dates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()
		END
		IF (@IncludePrices=1) OR (@CalendarMode=0)
		BEGIN
			DECLARE @YieldProducts TABLE
			(
				ProductNumber int NOT NULL PRIMARY KEY,
				ProductName varchar(MAX),
				ProductDate datetime NULL,
				ProductEndDate datetime NULL,
				NameListSetting tinyint NOT NULL,
				WeekDays int NOT NULL,
				Modifier1 Smallint NOT NULL,
				Modifier2 Smallint NOT NULL,
				AllowZeroPrice bit NOT NULL,
				FirstArrivalDate datetime NULL
			)

			INSERT INTO @YieldProducts
			SELECT DISTINCT pro.ProductNumber, ProductName, 
			CONVERT(DATETIME, pro.ProductDate) ProductDate, 
			DATEADD(day, -1, CONVERT(DATETIME, pro.ProductEndDate)) ProductEndDate,
			ISNULL(pro.NameListSetting,0) NameListSetting,
			CAST(ArrivalSunday AS int) + 
			CAST(ArrivalMonday AS int) *2 + 
			CAST(ArrivalTuesDay AS int)*4 + 
			CAST(ArrivalWednesDay AS int)*8 + 
			CAST(ArrivalThursday AS int) *16 + 
			CAST(ArrivalFriday AS int) *32 + 
			CAST(ArrivalSaturday AS int) *64 WeekDays,
			CASE WHEN (ISNULL(pro.AddonType,0) = 0 AND @AddOnFieldsPerPerson = 0) OR (@AddOnFieldsPerPerson = 1 and ISNULL(pro.NameListSetting,0) = 1 and @OnePersonPaysForAll = 0) 
				THEN 1 ELSE 0 END Modifier1,
			CASE WHEN (ISNULL(pro.AddonType,0) = 0 AND @AddOnFieldsPerPerson = 0) OR (@AddOnFieldsPerPerson = 1 and ISNULL(pro.NameListSetting,0) = 1 and @OnePersonPaysForAll = 0) 
				THEN 0 ELSE 1 END Modifier2,
			ISNULL(AllowZeroPrice,0),
			DATEADD(dd, ISNULL(PCR.MinimumDaysBeforeArrival,0), @SystemDate)
			FROM Products pro WITH (NOLOCK)
			INNER JOIN @ProductCustomerRate PCR ON PCR.ProductNumber=pro.ProductNumber
			INNER JOIN ProductExtended proe WITH (NOLOCK) ON pro.ProductNumber = proe.FK_ProductNumber
			INNER JOIN ProductHotelXTAB PX ON PX.ProductNumber=pro.ProductNumber 
			INNER JOIN @Hotels H ON H.HotelCustomerNumber=PX.HotelCustomerNumber
			WHERE (pro.MarkAsDeleted=0 OR pro.MarkAsDeleted IS NULL) 
			AND pro.ProductDate <= @LastAvailabilityDateChar 
			AND pro.ProductEndDate >= @StartDateChar
			AND (pro.MiniMumDays IS NULL OR pro.MiniMumDays <= @NoOfDays)
			AND (pro.MaximumDays IS NULL OR pro.MaximumDays >= @NoOfDays)
			AND (proe.NoChildren IS NULL OR proe.NoChildren=0 OR (@NoChildren1+@NoChildren2)=0)
			AND (@AvailabilitySummeryproductMode<>0 OR ISNULL(proe.SumAdultChild1,0)=0)

			--SET @T2 = GETDATE()
			--PRINT 'Insert Products '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()

			DECLARE @YieldMissingRoomTypeDepartureDates TABLE
			(
				DateStamp datetime NOT NULL,
				RoomTypeNumber int NOT NULL
			)
			;
			WITH T
			AS
			(
				SELECT DATEADD(day, @NoOfDays, DateStamp) DateStamp, RoomTypeNumber
				FROM #YieldVacantRooms 
				WHERE DateStamp <= @EndDate
			)
			INSERT INTO @YieldMissingRoomTypeDepartureDates
			SELECT T.DateStamp, T.RoomTypeNumber 
			FROM T
			LEFT OUTER JOIN #YieldVacantRooms VR2 ON VR2.DateStamp=T.DateStamp AND VR2.RoomTypeNumber=T.RoomTypeNumber
			WHERE VR2.DateStamp IS NULL
			IF @@ROWCOUNT > 0
				INSERT INTO #YieldVacantRooms ([DateStamp],[RoomTypeNumber],[OnlineRoomTypeNumber],[OnlinePriority],[HotelNumber],[VacantRooms],[MinRoomsAvail],[MaxRoomsAvail],[MaxRooms],IsBunkBed,WeekDays,MinStayPrice,MinPricePerDay,[DateStampChar],YieldNumber,MinChildrenPriceFound)
				SELECT DateStamp,RoomTypeNumber,0,0,0,0,0,0,0,0,0,0,0,CONVERT(char(8), DateStamp, 112),0,0
				FROM @YieldMissingRoomTypeDepartureDates
			--SET @T2 = GETDATE()
			--PRINT 'Insert #YieldVacantRooms/@YieldMissingRoomTypeDepartureDates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()
			;
			WITH T
			AS
			(
				SELECT 
				PP.ProductNumber,
				PP.RoomtypeNumber,
				VR.DateStamp,
				DATEADD(day, @NoOfDays, PP.DateStamp) DepartureDate,
				ISNULL(CASE 
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN @NoAdults*PP.AdultPrice1
					WHEN @NoAdults=1 THEN PP.AdultPrice1
					WHEN @NoAdults=2 THEN PP.AdultPrice2
					WHEN @NoAdults=3 THEN PP.AdultPrice3
					WHEN @NoAdults=4 THEN PP.AdultPrice4
					WHEN @NoAdults=5 THEN PP.AdultPrice5
					WHEN @NoAdults=6 THEN PP.AdultPrice6
					WHEN @NoAdults=7 THEN PP.AdultPrice7
					WHEN @NoAdults=8 THEN PP.AdultPrice8
					WHEN @NoAdults=9 THEN PP.AdultPrice9
				END,0) AdultPrice,
				ISNULL(CASE 
					WHEN @NoChildren1=0 THEN 0
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN @NoChildren1*PP.Children1Price1
					WHEN @NoChildren1=1 THEN PP.Children1Price1
					WHEN @NoChildren1=2 THEN PP.Children1Price2
					WHEN @NoChildren1=3 THEN PP.Children1Price3
					WHEN @NoChildren1=4 THEN PP.Children1Price4
					WHEN @NoChildren1=5 THEN PP.Children1Price5
					WHEN @NoChildren1=6 THEN PP.Children1Price6
					WHEN @NoChildren1=7 THEN PP.Children1Price7
					WHEN @NoChildren1=8 THEN PP.Children1Price8
					WHEN @NoChildren1=9 THEN PP.Children1Price9
				END,0) Children1Price,
				ISNULL(CASE 
					WHEN @NoChildren2=0 THEN 0
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN @NoChildren2*PP.Children2Price1
					WHEN @NoChildren2=1 THEN PP.Children2Price1
					WHEN @NoChildren2=2 THEN PP.Children2Price2
					WHEN @NoChildren2=3 THEN PP.Children2Price3
					WHEN @NoChildren2=4 THEN PP.Children2Price4
					WHEN @NoChildren2=5 THEN PP.Children2Price5
					WHEN @NoChildren2=6 THEN PP.Children2Price6
					WHEN @NoChildren2=7 THEN PP.Children2Price7
					WHEN @NoChildren2=8 THEN PP.Children2Price8
					WHEN @NoChildren2=9 THEN PP.Children2Price9
				END,0) Children2Price,
				ISNULL(CASE
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.AdultArrivalAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.AdultArrivalAddon
					WHEN @NoAdults=1 THEN PP.AdultArrivalAddon
					WHEN @NoAdults=2 THEN PP.AdultArrivalAddon2
					WHEN @NoAdults=3 THEN PP.AdultArrivalAddon3
					WHEN @NoAdults=4 THEN PP.AdultArrivalAddon4
					WHEN @NoAdults=5 THEN PP.AdultArrivalAddon5
					WHEN @NoAdults=6 THEN PP.AdultArrivalAddon6
					WHEN @NoAdults=7 THEN PP.AdultArrivalAddon7
					WHEN @NoAdults=8 THEN PP.AdultArrivalAddon8
					WHEN @NoAdults=9 THEN PP.AdultArrivalAddon9
				END,0)*(@NoAdults * P.Modifier1 + P.Modifier2) AdultArrivalAddon,
				ISNULL(CASE 
					WHEN @NoChildren1=0 THEN 0
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children1ArrivalAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.Children1ArrivalAddon
					WHEN @NoChildren1=1 THEN PP.Children1ArrivalAddon
					WHEN @NoChildren1=2 THEN PP.Children1ArrivalAddon2
					WHEN @NoChildren1=3 THEN PP.Children1ArrivalAddon3
					WHEN @NoChildren1=4 THEN PP.Children1ArrivalAddon4
					WHEN @NoChildren1=5 THEN PP.Children1ArrivalAddon5
					WHEN @NoChildren1=6 THEN PP.Children1ArrivalAddon6
					WHEN @NoChildren1=7 THEN PP.Children1ArrivalAddon7
					WHEN @NoChildren1=8 THEN PP.Children1ArrivalAddon8
					WHEN @NoChildren1=9 THEN PP.Children1ArrivalAddon9
				END,0)*(@NoChildren1 * P.Modifier1 + P.Modifier2) Children1ArrivalAddon,
				ISNULL(CASE 
					WHEN @NoChildren2=0 THEN 0
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children2ArrivalAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.Children2ArrivalAddon
					WHEN @NoChildren2=1 THEN PP.Children2ArrivalAddon
					WHEN @NoChildren2=2 THEN PP.Children2ArrivalAddon2
					WHEN @NoChildren2=3 THEN PP.Children2ArrivalAddon3
					WHEN @NoChildren2=4 THEN PP.Children2ArrivalAddon4
					WHEN @NoChildren2=5 THEN PP.Children2ArrivalAddon5
					WHEN @NoChildren2=6 THEN PP.Children2ArrivalAddon6
					WHEN @NoChildren2=7 THEN PP.Children2ArrivalAddon7
					WHEN @NoChildren2=8 THEN PP.Children2ArrivalAddon8
					WHEN @NoChildren2=9 THEN PP.Children2ArrivalAddon9
				END,0)*(@NoChildren2 * P.Modifier1 + P.Modifier2) Children2ArrivalAddon,
				ISNULL(CASE
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.AdultStayAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.AdultStayAddon
					WHEN @NoAdults=1 THEN PP.AdultStayAddon
					WHEN @NoAdults=2 THEN PP.AdultStayAddon2
					WHEN @NoAdults=3 THEN PP.AdultStayAddon3
					WHEN @NoAdults=4 THEN PP.AdultStayAddon4
					WHEN @NoAdults=5 THEN PP.AdultStayAddon5
					WHEN @NoAdults=6 THEN PP.AdultStayAddon6
					WHEN @NoAdults=7 THEN PP.AdultStayAddon7
					WHEN @NoAdults=8 THEN PP.AdultStayAddon8
					WHEN @NoAdults=9 THEN PP.AdultStayAddon9
				END,0)*(@NoAdults * P.Modifier1 + P.Modifier2) AdultStayAddon,
				ISNULL(CASE 
					WHEN @NoChildren1=0 THEN 0
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children1StayAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.Children1StayAddon
					WHEN @NoChildren1=1THEN PP.Children1StayAddon
					WHEN @NoChildren1=2 THEN PP.Children1StayAddon2
					WHEN @NoChildren1=3 THEN PP.Children1StayAddon3
					WHEN @NoChildren1=4 THEN PP.Children1StayAddon4
					WHEN @NoChildren1=5 THEN PP.Children1StayAddon5
					WHEN @NoChildren1=6 THEN PP.Children1StayAddon6
					WHEN @NoChildren1=7 THEN PP.Children1StayAddon7
					WHEN @NoChildren1=8 THEN PP.Children1StayAddon8
					WHEN @NoChildren1=9 THEN PP.Children1StayAddon9
				END,0)*(@NoChildren1 * P.Modifier1 + P.Modifier2) Children1StayAddon,
				ISNULL(CASE 
					WHEN @NoChildren2=0 THEN 0
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children2StayAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.Children2StayAddon
					WHEN @NoChildren2=1 THEN PP.Children2StayAddon
					WHEN @NoChildren2=2 THEN PP.Children2StayAddon2
					WHEN @NoChildren2=3 THEN PP.Children2StayAddon3
					WHEN @NoChildren2=4 THEN PP.Children2StayAddon4
					WHEN @NoChildren2=5 THEN PP.Children2StayAddon5
					WHEN @NoChildren2=6 THEN PP.Children2StayAddon6
					WHEN @NoChildren2=7 THEN PP.Children2StayAddon7
					WHEN @NoChildren2=8 THEN PP.Children2StayAddon8
					WHEN @NoChildren2=9 THEN PP.Children2StayAddon9
				END,0)*(@NoChildren2 * P.Modifier1 + P.Modifier2) Children2StayAddon,
				ISNULL(CASE
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.AdultDepartureAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.AdultDepartureAddon
					WHEN @NoAdults=1 THEN PP.AdultDepartureAddon
					WHEN @NoAdults=2 THEN PP.AdultDepartureAddon2
					WHEN @NoAdults=3 THEN PP.AdultDepartureAddon3
					WHEN @NoAdults=4 THEN PP.AdultDepartureAddon4
					WHEN @NoAdults=5 THEN PP.AdultDepartureAddon5
					WHEN @NoAdults=6 THEN PP.AdultDepartureAddon6
					WHEN @NoAdults=7 THEN PP.AdultDepartureAddon7
					WHEN @NoAdults=8 THEN PP.AdultDepartureAddon8
					WHEN @NoAdults=9 THEN PP.AdultDepartureAddon9
				END,0)*(@NoAdults * P.Modifier1 + P.Modifier2) AdultDepartureAddon,
				ISNULL(CASE 
					WHEN @NoChildren1=0 THEN 0
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children1DepartureAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.Children1DepartureAddon
					WHEN @NoChildren1=1 THEN PP.Children1DepartureAddon
					WHEN @NoChildren1=2 THEN PP.Children1DepartureAddon2
					WHEN @NoChildren1=3 THEN PP.Children1DepartureAddon3
					WHEN @NoChildren1=4 THEN PP.Children1DepartureAddon4
					WHEN @NoChildren1=5 THEN PP.Children1DepartureAddon5
					WHEN @NoChildren1=6 THEN PP.Children1DepartureAddon6
					WHEN @NoChildren1=7 THEN PP.Children1DepartureAddon7
					WHEN @NoChildren1=8 THEN PP.Children1DepartureAddon8
					WHEN @NoChildren1=9 THEN PP.Children1DepartureAddon9
				END,0)*(@NoChildren1 * P.Modifier1 + P.Modifier2) Children1DepartureAddon,
				ISNULL(CASE 
					WHEN @NoChildren2=0 THEN 0
					WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children2DepartureAddon
					WHEN @AddOnFieldsPerPerson=0 THEN PP.Children2DepartureAddon
					WHEN @NoChildren2=1 THEN PP.Children2DepartureAddon
					WHEN @NoChildren2=2 THEN PP.Children2DepartureAddon2
					WHEN @NoChildren2=3 THEN PP.Children2DepartureAddon3
					WHEN @NoChildren2=4 THEN PP.Children2DepartureAddon4
					WHEN @NoChildren2=5 THEN PP.Children2DepartureAddon5
					WHEN @NoChildren2=6 THEN PP.Children2DepartureAddon6
					WHEN @NoChildren2=7 THEN PP.Children2DepartureAddon7
					WHEN @NoChildren2=8 THEN PP.Children2DepartureAddon8
					WHEN @NoChildren2=9 THEN PP.Children2DepartureAddon9
				END,0)*(@NoChildren2 * P.Modifier1 + P.Modifier2) Children2DepartureAddon,
				CASE WHEN VR.WeekDays=0 THEN P.WeekDays 
					WHEN P.WeekDays=0 THEN VR.WeekDays
					ELSE P.WeekDays & VR.WeekDays
				END WeekDays,
				P.AllowZeroPrice,
				VR.MinStayPrice,
				VR.MinPricePerDay,
				P.FirstArrivalDate,
				VR.MinChildrenPriceFound,
				DATEADD(day, @NoOfDays-1, PP.DateStamp) LastDate,
				VR.VacantRooms-CASE WHEN VR.MaxRooms<0 THEN VR.MaxRooms ELSE 0 END RoomsAvailable
				FROM #YieldVacantRooms VR
				INNER JOIN dbo.ProductPrice PP WITH (NOLOCK) ON PP.RoomtypeNumber=VR.RoomTypeNumber AND PP.DateStamp=VR.DateStampChar
				INNER JOIN @YieldProducts P ON P.ProductNumber=PP.ProductNumber 
					AND (P.ProductDate IS NULL OR P.ProductDate<=PP.DateStamp) 
					AND (P.ProductEndDate IS NULL OR P.ProductEndDate >= PP.DateStamp)
				WHERE PP.DateStamp BETWEEN @StartDate AND @LastAvailabilityDate
			)
			INSERT INTO @ProductPriceDay
			SELECT T.ProductNumber,T.RoomtypeNumber,T.DateStamp, T.DepartureDate,
				X.ArrivalPrice,
				X.StayPrice,
				X.DeparturePrice,
				CASE 
					WHEN T.Roomsavailable <= 0 THEN 0
					WHEN FirstArrivalDate IS NOT NULL AND DateStamp < FirstArrivalDate THEN 0
					WHEN X.ArrivalPrice<=0 AND (@UseAllowZeroPriceOnProduct=0 OR AllowZeroPrice=0) THEN 0
					WHEN WeekDays=0 THEN 1
					WHEN (WeekDays & CASE DATEPART(WEEKDAY, Datestamp) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 4 WHEN 4 THEN 8 WHEN 5 THEN 16 WHEN 6 THEN 32 WHEN 7 THEN 64 END) <> 0 THEN 1
					ELSE 0 END ArrivalAllowed,
				MinStayPrice,
				0,
				CASE WHEN MinChildrenPriceFound=1 THEN X.ArrivalPrice ELSE AdultPrice+AdultArrivalAddon END,
				CASE WHEN MinChildrenPriceFound=1 THEN X.StayPrice ELSE AdultPrice+AdultStayAddon END,
				CASE WHEN MinChildrenPriceFound=1 THEN X.DeparturePrice ELSE AdultDepartureAddon END,
				CASE 
					WHEN T.Roomsavailable <= 0 THEN 0
					WHEN X.StayPrice <= 0 AND (@UseAllowZeroPriceOnProduct=0 OR AllowZeroPrice=0) THEN 0
					ELSE 1 END StayAllowed,
				MinPricePerDay,
				LastDate,
				T.Roomsavailable
			FROM T
			CROSS APPLY (
				SELECT AdultPrice+Children1Price+Children2Price+AdultArrivalAddon+Children1ArrivalAddon+Children2ArrivalAddon ArrivalPrice,
					AdultPrice+Children1Price+Children2Price+AdultStayAddon+Children1StayAddon+Children2StayAddon StayPrice,
					AdultDepartureAddon+Children1DepartureAddon+Children2DepartureAddon DeparturePrice
			) X

			--SET @T2 = GETDATE()
			--PRINT 'Insert ProductPrices '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()

			IF @NoOfDays=1 
			BEGIN
				;
				WITH T
				AS
				(
					SELECT 
					PP.ProductNumber,
					PP.RoomtypeNumber,
					PP.DateStamp,
					PP.ArrivalPrice+ISNULL(PP2.DeparturePrice,0) DayPrice,
					PP.CheckArrivalPrice+ISNULL(PP2.CheckDeparturePrice,0) CheckDayPrice,
					PP.MinStayPrice,
					PP.MinPricePerDay,
					PP.RoomsAvailable
					FROM @ProductPriceDay PP
					LEFT OUTER JOIN @ProductPriceDay PP2 ON PP2.ProductNumber=PP.ProductNumber AND PP2.RoomtypeNumber=PP.RoomtypeNumber AND PP2.DateStamp=DATEADD(day, 1, PP.DateStamp)
					WHERE PP.DateStamp <= @EndDate
					AND PP.ArrivalAllowed=1
				)
				INSERT INTO @TotalPrices
				SELECT T.DateStamp, T.RoomTypeNumber, T.ProductNumber, T.DayPrice, T.RoomsAvailable
				FROM T
				WHERE @NoOfRooms*CheckDayPrice >= MinStayPrice
				AND @NoOfRooms*DayPrice >= MinPricePerDay
			END
			ELSE
			BEGIN
				;
				WITH T
				AS
				(
					SELECT 
					PP.ProductNumber,
					PP.RoomtypeNumber,
					PP.DateStamp,
					SUM(CASE PP2.DateStamp
						WHEN PP.DateStamp THEN PP2.ArrivalPrice
						WHEN PP.DepartureDate THEN PP2.DeparturePrice
						ELSE PP2.StayPrice
						END) TotalPrice,
					SUM(CASE PP2.DateStamp
						WHEN PP.DateStamp THEN PP2.CheckArrivalPrice
						WHEN PP.DepartureDate THEN PP2.CheckDeparturePrice
						ELSE PP2.CheckStayPrice
						END) CheckTotalPrice,
					SUM(CASE PP2.DateStamp
					    WHEN PP.DateStamp THEN PP.ArrivalAllowed
						WHEN PP.DepartureDate THEN 0
						ELSE PP2.StayAllowed
						END) DayCount,
					MAX(CASE PP2.DateStamp
						WHEN PP.LastDate THEN PP2.MinStayPrice
						ELSE 0
						END) MinStayPrice,
					MAX(CASE PP2.DateStamp
						WHEN PP.LastDate THEN PP2.MinPricePerDay
						ELSE 0
						END) MinPricePerDay,
					MIN(CASE PP2.DateStamp
						WHEN PP.DateStamp THEN PP2.ArrivalPrice
						WHEN PP.DepartureDate THEN @MAXINT
						ELSE PP2.StayPrice
						END) RoomMinPricePerDay,
					MIN(CASE PP2.DateStamp
						WHEN PP.DepartureDate THEN @MAXINT
						ELSE PP2.RoomsAvailable
						END) RoomsAvailable
					FROM @ProductPriceDay PP
					INNER JOIN @ProductPriceDay PP2 ON PP2.ProductNumber=PP.ProductNumber AND PP2.RoomtypeNumber=PP.RoomtypeNumber AND PP2.DateStamp BETWEEN PP.DateStamp AND PP.DepartureDate
					WHERE PP.DateStamp <= @EndDate
					AND PP.ArrivalAllowed=1
					GROUP BY PP.ProductNumber, PP.RoomTypeNumber, PP.DateStamp
				)
				INSERT INTO @TotalPrices
				SELECT T.DateStamp, T.RoomTypeNumber, T.ProductNumber, T.TotalPrice, T.RoomsAvailable
				FROM T
				WHERE T.DayCount >= @NoOfDays 
				AND @NoOfRooms*T.CheckTotalPrice >= T.MinStayPrice
				AND @NoOfRooms*T.RoomMinPricePerDay >= T.MinPricePerDay
			END

			--SET @T2 = GETDATE()
			--PRINT 'Insert Prices '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()

			DECLARE @ProductMatchDayColor BIT = 1
			SELECT @ProductMatchDayColor = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ProductMatchDayColor'
			IF @ProductMatchDayColor=1
			BEGIN
				DECLARE @NoColorMeansAllColors BIT = 1
				SELECT @NoColorMeansAllColors = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoColorMeansAllColors'

				DECLARE @DayColors TABLE
				(
					Datestamp datetime NOT NULL,
					HotelNumber int NOT NULL,
					ColorID int NULL,
					PRIMARY KEY CLUSTERED (Datestamp, HotelNumber)
				)
				INSERT INTO @DayColors
				SELECT DISTINCT T.DateStamp, RT.HotelNumber, Y.ColorID
				FROM @TotalPrices T
				INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=T.RoomtypeNumber
				LEFT OUTER JOIN (
					SELECT DateStamp, HotelNumber, ColorID 
					FROM RoomBookingStatusYield WITH (NOLOCK)
					INNER JOIN ColorRGB (NOLOCK) ON ISNULL(DayColor, 0) = ((256 * 256 * BLUE) + (256 * GREEN) + RED)
					INNER JOIN Color (NOLOCK) ON ColorRGBID = FK_ColorRGBID  
				) Y ON Y.Datestamp=T.DateStamp AND Y.HotelNumber=RT.HotelNumber

				--SET @T2 = GETDATE()
				--PRINT 'Insert @DayColors '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
				--SET @T1 = GETDATE()

				UPDATE @DayColors
				SET ColorID=X.ColorID
				FROM @DayColors DC
				CROSS APPLY (
					SELECT ColorID 
					FROM RoomBookingStatusYield RBSY WITH (NOLOCK) 
					INNER JOIN ColorRGB (NOLOCK) ON ISNULL(DayColor, 0) = ((256 * 256 * BLUE) + (256 * GREEN) + RED)
					INNER JOIN Color (NOLOCK) ON ColorRGBID = FK_ColorRGBID  
					WHERE RBSY.HotelNumber=DC.HotelNumber 
					AND RBSY.DateStamp=DC.Datestamp
				) X
				WHERE DC.ColorID IS NULL

				--SET @T2 = GETDATE()
				--PRINT 'Update 1 @DayColors '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
				--SET @T1 = GETDATE()

				IF EXISTS(SELECT * FROM @DayColors WHERE ColorID IS NULL)
					UPDATE @DayColors
					SET ColorID=Y.FK_ColorID
					FROM @DayColors DC
					CROSS APPLY (
						SELECT SUM(VC.VacantRooms) ColorVacantRooms
						FROM #YieldVacantRooms VC
						INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=VC.RoomTypeNumber AND RT.HotelNumber=DC.HotelNumber
						WHERE VC.DateStamp=DC.Datestamp
					) X
					CROSS APPLY (
					  SELECT TOP 1 FK_ColorID 
					  FROM SalesChannelExtraFields S WITH (NOLOCK)
					  INNER JOIN @Hotels H ON H.HotelCustomerNumber=S.CustomerNumber AND H.HotelNumber=DC.HotelNumber
					  WHERE S.Availability > 0
					  AND S.Availability < ColorVacantRooms
					  ORDER BY S.Availability DESC
					) Y
					WHERE DC.ColorID IS NULL

				--SET @T2 = GETDATE()
				--PRINT 'Update 2 @DayColors '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
				--SET @T1 = GETDATE()

				DECLARE @DayColorsMatch TABLE
				(
					Datestamp datetime NOT NULL,
					HotelNumber int NOT NULL,
					ProductNumber int NOT NULL
					PRIMARY KEY CLUSTERED (Datestamp, HotelNumber, ProductNumber)
				)
				;WITH T
				AS
				(
					SELECT DISTINCT T.DateStamp, RT.HotelNumber, T.ProductNumber
					FROM @TotalPrices T
					INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=T.RoomtypeNumber
				)
				INSERT INTO @DayColorsMatch
				SELECT T.DateStamp, T.HotelNumber, T.ProductNumber
				FROM T
				INNER JOIN @DayColors DC ON DC.Datestamp=T.DateStamp AND DC.HotelNumber=T.HotelNumber
				LEFT OUTER JOIN ProductColorXTAB X WITH (NOLOCK) ON X.ProductNumber=T.ProductNumber
				WHERE X.ColorID IS NULL
				AND 
				(
					(@NoColorMeansAllColors=1)
					OR
					(DC.ColorID = 0)
				)
				UNION
				SELECT T.DateStamp, T.HotelNumber, T.ProductNumber
				FROM T
				INNER JOIN @DayColors DC ON DC.Datestamp=T.DateStamp AND DC.HotelNumber=T.HotelNumber
				INNER JOIN ProductColorXTAB X WITH (NOLOCK) ON X.ProductNumber=T.ProductNumber AND (X.ColorID=DC.ColorID OR DC.ColorID=0)

				--SET @T2 = GETDATE()
				--PRINT 'Insert @DayColorsMatch '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
				--SET @T1 = GETDATE()

				DELETE FROM @TotalPrices
				FROM @TotalPrices P
				INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=P.RoomtypeNumber
				LEFT OUTER JOIN @DayColorsMatch M ON M.Datestamp=P.DateStamp AND M.HotelNumber=RT.HotelNumber AND M.ProductNumber=P.ProductNumber
				WHERE M.Datestamp IS NULL

				--SET @T2 = GETDATE()
				--PRINT 'Delete @@TotalPrices '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
				--SET @T1 = GETDATE()
			END
		END
		DROP TABLE #Yield
		DROP TABLE #YieldVacantRooms 

	END
	ELSE
	BEGIN -- @OnlineAvailabilityRateLevel > 0
		DECLARE @BookingDate DATETIME=GETDATE()
		DECLARE @ChannelType INT = CASE WHEN @CustomerNumber > 0 THEN 4 WHEN @CampaignCodeText <> '' THEN 16 ELSE 2 END

		DECLARE @tmpNumberOfRooms int = @NoOfRooms
		IF @OnlineAvailabilityRateLevel = 2 -- Centralized
		  SET @tmpNumberOfRooms = -1

		DECLARE @TopRateLevelId INT

		DECLARE @IsBW BIT = 0
		SELECT @IsBW = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'IsBestWestern'

		IF @IsBW = 1
		BEGIN
		  SELECT TOP 1 @TopRateLevelId = FKRateLevelId 
		  FROM RateLevelChannelRateLevelXTab WITH (NOLOCK)
		  INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelId
			--AND RateLevelChannel.UseInPicasso = 1
			AND RateLevelChannel.ChannelType & @ChannelType > 0 --bitwise compare
		  ORDER BY SortNumber
		END
		ELSE
		  SET @TopRateLevelId = -1

		--SET @T2 = GETDATE()
		--PRINT 'General Top '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		SET @TotalNumberOfRooms = ISNULL(@TotalNumberOfRooms, 0)


		CREATE TABLE #RateLevelAvailability
		(
		  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
		)

		INSERT INTO #RateLevelAvailability
		  EXEC GenerateAvailability @StartDate, @LastAvailabilityDate, 1, @HotelNumbers, '', 1, 0, '', @AIncludeOccupiedInOFS = 0

		--SET @T2 = GETDATE()
		--PRINT 'GenerateAvailability '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		DECLARE @RateLevelTotalSoldOutDates TABLE
		(
			DateStamp datetime NOT NULL,
			PRIMARY KEY CLUSTERED (DateStamp)
		)
		IF @OnlineAvailabilityRateLevel = 2 -- Centralized
		BEGIN
			INSERT INTO @RateLevelTotalSoldOutDates
			SELECT #RateLevelAvailability.DateStamp
			FROM #RateLevelAvailability
			GROUP BY #RateLevelAvailability.DateStamp
			HAVING (@TotalNumberOfRooms - @NoOfRooms - 
				(
					ISNULL(SUM(#RateLevelAvailability.SoldRooms), 0) + ISNULL(SUM(#RateLevelAvailability.NumberOfOutOfOrder), 0) + 
						(CASE WHEN @OOSDecreaseVacantRooms = 1 THEN ISNULL(SUM(#RateLevelAvailability.NumberOfOutForService), 0) ELSE ISNULL(SUM(#RateLevelAvailability.NumberOfOccupied), 0) END) +
					(CASE WHEN @InclAllotment = 1 THEN ISNULL(SUM(#RateLevelAvailability.TotalRoomsAllotment), 0) ELSE 0 END)
				) < 0)  

			--SET @T2 = GETDATE()
			--PRINT 'Insert @RateLevelTotalSoldOutDates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
			--SET @T1 = GETDATE()
		END

		DECLARE @RateLevelRoomsUnAvailable TABLE
		(
			DateStamp datetime NOT NULL,
			RoomTypeNumber int NOT NULL,
			RoomsUnavailable int
			PRIMARY KEY CLUSTERED (DateStamp, RoomTypeNumber)
		)
		INSERT INTO @RateLevelRoomsUnAvailable
		SELECT #RateLevelAvailability.DateStamp, #RateLevelAvailability.RoomTypeNumber, 
			SUM(ISNULL(#RateLevelAvailability.SoldRooms,0)) + SUM(ISNULL(#RateLevelAvailability.NumberOfOutOfOrder,0)) + 
			(CASE WHEN @OOSDecreaseVacantRooms = 1 THEN SUM(ISNULL(#RateLevelAvailability.NumberOfOutForService,0)) ELSE SUM(ISNULL(#RateLevelAvailability.NumberOfOccupied,0)) END) +
			(CASE WHEN @InclAllotment = 1 THEN SUM(ISNULL(#RateLevelAvailability.TotalRoomsAllotment,0)) ELSE 0 END)
		FROM #RateLevelAvailability
		INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=#RateLevelAvailability.RoomTypeNumber
		GROUP BY #RateLevelAvailability.DateStamp, #RateLevelAvailability.RoomTypeNumber

		--SET @T2 = GETDATE()
		--PRINT 'Insert @RateLevelRoomsUnAvailable '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		IF NOT EXISTS(SELECT * FROM @ProductCustomerRate)
		BEGIN
			INSERT INTO @ProductCustomerRate (ProductNumber) 
			SELECT ProductNumber FROM Products
			--SET @T2 = GETDATE()
			--PRINT 'Insert @ProductCustomerRate from all products '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
			--SET @T1 = GETDATE()
		END

		CREATE TABLE #tmpRateLevelGetAllocation1
		(
			DateStamp datetime NOT NULL, 
			RateLevelId int NOT NULL,
			ProductNumber int NOT NULL,
			RoomTypeNumber int NOT NULL,
			RoomsAllocated int NOT NULL,
			RoomCount int NOT NULL,
			RestrictionType int NOT NULL,
			RestrictionLength int NOT NULL,
			RowIndex int NOT NULL 
			PRIMARY KEY CLUSTERED ( DateStamp, RateLevelId,RoomTypeNumber,ProductNumber)
		)
		INSERT INTO #tmpRateLevelGetAllocation1
		SELECT 
		RateLevelRoomTypeRestrictionXTab.DateStamp, 
		RateLevelRoomTypeXTab.FKRateLevelId RateLevelId,
		Products.ProductNumber,
		RoomTypes.RoomTypeNumber,
		ISNULL(CASE WHEN @TopRateLevelId = RateLevelChannelRateLevelXTab.FKRateLevelId THEN RoomTypes.RoomCount
			ELSE RateLevelRoomTypeRestrictionXTab.RoomsAllocated
		END,0) RoomsAllocated,
		RoomTypes.RoomCount,
		ISNULL(RateLevelRestriction.RestrictionType,0) RestrictionType,
		ISNULL(RateLevelRoomTypeRestrictionXTab.RestrictionLength,0) RestrictionLength,
		ROW_NUMBER() OVER (PARTITION BY RateLevelRoomTypeRestrictionXTab.DateStamp, RateLevelRoomTypeXTab.FKRateLevelId,RateLevelRoomTypeXTab.FKRoomTypeNumber,RateLevelRoomTypeXTab.Id ORDER BY RateLevelRestrictionRateChannelXTab.Priority DESC) RowIndex
		FROM RateLevelRoomTypeRestrictionXTab WITH (NOLOCK)
		INNER JOIN RateLevelRoomTypeXTab WITH (NOLOCK) ON RateLevelRoomTypeXTab.Id = RateLevelRoomTypeRestrictionXTab.FKRateLevelRoomTypeXTabId
			AND RateLevelRoomTypeXTab.FKRoomTypeNumber > 0
		INNER JOIN RateLevelRestriction WITH (NOLOCK) ON RateLevelRestriction.Id = RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId
		INNER JOIN RateLevel WITH (NOLOCK) ON RateLevel.RateLevelId = RateLevelRoomTypeXTab.FKRateLevelId
		INNER JOIN Products WITH (NOLOCK) ON Products.FKRateLevelId = RateLevel.RateLevelId
		INNER JOIN @ProductCustomerRate PCR ON (PCR.ProductNumber=Products.ProductNumber)
		INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = RateLevel.RateLevelId
		INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
		--  AND RateLevelChannel.UseInPicasso = 1
			AND RateLevelChannel.ChannelType & @ChannelType > 0 --bitwise compare
		INNER JOIN @RoomTypes RoomTypes ON RoomTypes.RoomTypeNumber = RateLevelRoomTypeXTab.FKRoomTypeNumber
		INNER JOIN RateLevelRestrictionRateChannelXTab WITH (NOLOCK) ON RateLevelRestrictionRateChannelXTab.FKRateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
			AND RateLevelRestrictionRateChannelXTab.FKRateLevelRestrictionId = RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId
		INNER JOIN RateLevelChannelCustomerXTab WITH (NOLOCK) ON RateLevelChannelCustomerXTab.FKRateLevelChannelId=RateLevelChannel.RateLevelChannelId 
		INNER JOIN @Hotels H ON H.HotelCustomerNumber = RateLevelChannelCustomerXTab.FKCustomerNumber
		WHERE RateLevelRoomTypeRestrictionXTab.DateStamp BETWEEN @StartDate AND @LastAvailabilityDate
		AND ((RoomTypes.IsBunkBed=1)
			OR
			(
				RoomTypes.MaxPaxTotal >= @TotPax
				AND RoomTypes.MaxAdultPax >= @NoAdults 
				AND RoomTypes.MaxChildren1Pax >= @NoChildren1
				AND RoomTypes.MaxChildren2Pax >= @NoChildren2
				AND ISNULL(RoomTypes.MinPax, 0) <= @TotPax
			)
		)

		--SET @T2 = GETDATE()
		--PRINT 'Insert #tmpRateLevelGetAllocation1 '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		DECLARE @RateLevelSoldOutRooms TABLE
		(
			DateStamp datetime NOT NULL,
			ProductNumber int NOT NULL, 
			RoomTypeNumber int NOT NULL
			PRIMARY KEY CLUSTERED (DateStamp, RoomTypeNumber, ProductNumber)
		)


		IF @OnlineAvailabilityRateLevel = 1
		BEGIN

			DECLARE @TotalNumberOfBeds INT
			DECLARE @TotalNumberOfRoomsRateLevel INT

			SELECT @TotalNumberOfBeds = COUNT(*)
			FROM Rooms WITH (NOLOCK)
			INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
				AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
			INNER JOIN @Hotels H ON H.HotelNumber=RoomTypes.HotelNumber

			SELECT @TotalNumberOfRoomsRateLevel = COUNT(*)
			FROM Rooms WITH (NOLOCK)
			INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
				AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0
			INNER JOIN @Hotels H ON H.HotelNumber=RoomTypes.HotelNumber

		  INSERT INTO @RateLevelSoldOutRooms (DateStamp,ProductNumber,RoomTypeNumber)
		  SELECT #tmpRateLevelGetAllocation1.DateStamp, #tmpRateLevelGetAllocation1.ProductNumber, #tmpRateLevelGetAllocation1.RoomTypeNumber
		  FROM #tmpRateLevelGetAllocation1
		  GROUP BY #tmpRateLevelGetAllocation1.DateStamp, #tmpRateLevelGetAllocation1.RoomTypeNumber, #tmpRateLevelGetAllocation1.ProductNumber
		  HAVING (SUM(#tmpRateLevelGetAllocation1.RoomsAllocated) - @NoOfRooms -
		  (
			SELECT ISNULL(SUM(#RateLevelAvailability.SoldRooms), 0) + ISNULL(SUM(#RateLevelAvailability.NumberOfOccupied), 0) + ISNULL(SUM(#RateLevelAvailability.NumberOfOutOfOrder), 0) +
			  CASE WHEN @OOSDecreaseVacantRooms = 1 THEN
				ISNULL(SUM(#RateLevelAvailability.NumberOfOutForService), 0)
			  ELSE
				0
			  END +
			  CASE WHEN @InclAllotment = 1 THEN
				ISNULL(SUM(#RateLevelAvailability.TotalRoomsAllotment), 0)
			  ELSE
				0
			  END
			  FROM #RateLevelAvailability
			  WHERE #RateLevelAvailability.RequestorId = -2  --  -2 means online reservation
			  AND #RateLevelAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation1.DateStamp, 112)
			  AND #RateLevelAvailability.RoomTypeNumber = #tmpRateLevelGetAllocation1.RoomTypeNumber
			  AND #RateLevelAvailability.ProductNumber = #tmpRateLevelGetAllocation1.ProductNumber
		  ) < 0) OR
		  (@TotalNumberOfBeds - @NoOfRooms -
		  (
			SELECT ISNULL(SUM(#RateLevelAvailability.SoldRooms), 0) + ISNULL(SUM(#RateLevelAvailability.NumberOfOccupied), 0) + ISNULL(SUM(#RateLevelAvailability.NumberOfOutOfOrder), 0) +
			  CASE WHEN @OOSDecreaseVacantRooms = 1 THEN
				ISNULL(SUM(#RateLevelAvailability.NumberOfOutForService), 0)
			  ELSE
				0
			  END +
			  CASE WHEN @InclAllotment = 1 THEN
				ISNULL(SUM(#RateLevelAvailability.TotalRoomsAllotment), 0)
			  ELSE
				0
			  END
			  FROM #RateLevelAvailability
			  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #RateLevelAvailability.RoomTypeNumber
				AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
			  WHERE #RateLevelAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation1.DateStamp, 112)
		  ) < 0)  OR
		  (@TotalNumberOfRoomsRateLevel - @NoOfRooms -
		  (
			SELECT ISNULL(SUM(#RateLevelAvailability.SoldRooms), 0) + ISNULL(SUM(#RateLevelAvailability.NumberOfOccupied), 0) + ISNULL(SUM(#RateLevelAvailability.NumberOfOutOfOrder), 0) +
			  CASE WHEN @OOSDecreaseVacantRooms = 1 THEN
				ISNULL(SUM(#RateLevelAvailability.NumberOfOutForService), 0)
			  ELSE
				0
			  END +
			  CASE WHEN @InclAllotment = 1 THEN
				ISNULL(SUM(#RateLevelAvailability.TotalRoomsAllotment), 0)
			  ELSE
				0
			  END
			  FROM #RateLevelAvailability
			  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #RateLevelAvailability.RoomTypeNumber
				AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0
			  WHERE #RateLevelAvailability.DateStamp = CONVERT(CHAR(8), #tmpRateLevelGetAllocation1.DateStamp, 112)
		  ) < 0)

		END

		IF @OnlineAvailabilityRateLevel = 2 -- Centralized
		BEGIN
		;
		  INSERT INTO @RateLevelSoldOutRooms (DateStamp,ProductNumber,RoomTypeNumber)
		  SELECT #tmpRateLevelGetAllocation1.DateStamp, #tmpRateLevelGetAllocation1.ProductNumber, #tmpRateLevelGetAllocation1.RoomTypeNumber
		  FROM #tmpRateLevelGetAllocation1
		  LEFT OUTER JOIN @RateLevelRoomsUnAvailable A ON A.DateStamp=#tmpRateLevelGetAllocation1.DateStamp AND A.RoomTypeNumber=#tmpRateLevelGetAllocation1.RoomTypeNumber
		  INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=#tmpRateLevelGetAllocation1.RoomTypeNumber
			AND (RT.RoomCount < ISNULL(A.RoomsUnavailable,0)+@NoOfRooms)

		--SET @T2 = GETDATE()
		--PRINT 'Insert @RateLevelSoldOutRooms '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		END

		CREATE TABLE #RateLevelArrivalDates
		(
			DateStamp datetime NOT NULL, 
			DepartureDate datetime NOT NULL, 
			ProductNumber int NOT NULL,
			RoomTypeNumber int NOT NULL
			PRIMARY KEY CLUSTERED ( RoomTypeNumber,ProductNumber, DateStamp, DepartureDate)
		)
		;
		WITH T
		AS
		(
			SELECT *, DATEADD(DAY, @NoOfDays, DateStamp) DepartureDate
			FROM #tmpRateLevelGetAllocation1
		)
		INSERT INTO #RateLevelArrivalDates
		SELECT A.DateStamp, A.DepartureDate, A.ProductNumber, A.RoomTypeNumber
		FROM T A
		CROSS APPLY (
			SELECT 1 OK
			FROM #tmpRateLevelGetAllocation1 A2
			LEFT OUTER JOIN @RateLevelSoldOutRooms SOR ON SOR.DateStamp=A2.DateStamp AND SOR.RoomTypeNumber=A2.RoomTypeNumber AND SOR.ProductNumber=A2.ProductNumber
			LEFT OUTER JOIN @RateLevelTotalSoldOutDates TSOD ON TSOD.DateStamp=A2.DateStamp
			WHERE A2.RateLevelId=A.RateLevelId
			AND A2.RoomTypeNumber=A.RoomTypeNumber
			AND A2.DateStamp BETWEEN A.DateStamp AND A.DepartureDate
			--AND A2.RowIndex=1
			AND (
				(A2.RateLevelId = @TopRateLevelId AND A2.RoomCount >= @tmpNumberOfRooms)
				OR 
				(A2.RateLevelId <> @TopRateLevelId AND (A2.RoomsAllocated >= @tmpNumberOfRooms OR A2.DateStamp= A.DepartureDate))
			)
			AND (
			  ((A2.RestrictionType <> 3) OR (A.DateStamp <> A2.DateStamp)) -- Closed to arrival
			  AND ((A2.RestrictionType <> 5) OR (A.DepartureDate <> A2.DateStamp)) -- Closed to departure
			  AND (A2.RestrictionType <> 1 OR ((A.DepartureDate = A2.DateStamp))) -- not closed - closed on departure date is ok
			  AND ((A2.RestrictionType <> 4) OR (A.DateStamp <> A2.DateStamp) 
			   OR ((A2.RestrictionType = 4) AND (A.DateStamp = A2.DateStamp AND A2.RestrictionLength <= @NoOfDays))) -- MLOS
			  AND ((A2.RestrictionType <> 6) OR (A.DateStamp <> A2.DateStamp) 
			   OR ((A2.RestrictionType = 6) AND (A.DateStamp = A2.DateStamp AND A2.RestrictionLength >= @NoOfDays))) -- MaxLOS
			  AND ((A2.RestrictionType <> 7) OR (A.DateStamp <> A2.DateStamp) 
			   OR ((A2.RestrictionType = 7) AND (A.DateStamp = A2.DateStamp AND A2.RestrictionLength = @NoOfDays))) -- ExactLOS
			  AND ((A2.RestrictionType <> 8) OR (A.DateStamp <> A2.DateStamp) 
			   OR ((A2.RestrictionType = 8) AND (A.DateStamp = A2.DateStamp AND A2.RestrictionLength > DATEDIFF(DAY, @BookingDate, A.DateStamp)))) -- CufOffs
			  AND ((A2.RestrictionType <> 9) OR (A.DateStamp <> A2.DateStamp) 
			   OR ((A2.RestrictionType = 9) AND (A.DateStamp = A2.DateStamp AND A2.RestrictionLength < DATEDIFF(DAY, @BookingDate, A.DateStamp)))) -- Release days
			)
			AND (SOR.DateStamp IS NULL OR A.DepartureDate = A2.DateStamp)
			AND (TSOD.DateStamp IS NULL OR A.DepartureDate = A2.DateStamp)
			HAVING COUNT(DISTINCT A2.DateStamp) > @NoOfDays
		) X
		WHERE (@RoomTypeNumber IS NULL OR A.RoomTypeNumber=@RoomTypeNumber)
		--AND X.OK=1

		--SET @T2 = GETDATE()
		--PRINT 'Insert #RateLevelArrivalDates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		IF @CalendarMode=1
		BEGIN
			-- Select Fully booked dates
			SELECT Datestamp
			FROM @Dates
			WHERE DateStamp <= @EndDate
			EXCEPT
			SELECT A.DateStamp
			FROM #tmpRateLevelGetAllocation1 A
			LEFT OUTER JOIN @RateLevelSoldOutRooms SOR ON SOR.DateStamp=A.DateStamp AND SOR.RoomTypeNumber=A.RoomTypeNumber AND SOR.ProductNumber=A.ProductNumber
			LEFT OUTER JOIN @RateLevelTotalSoldOutDates TSOD ON TSOD.DateStamp=A.DateStamp
			WHERE SOR.DateStamp IS NULL
			AND TSOD.DateStamp IS NULL

			--SET @T2 = GETDATE()
			--PRINT 'Select Fully booked dates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
			--SET @T1 = GETDATE()
		END

		CREATE TABLE #RateLevelDateRoomProduct
		(
			DateStampChar char(8) COLLATE DATABASE_DEFAULT NOT NULL, 
			DateStamp datetime NOT NULL, 
			RoomTypeNumber int NOT NULL,
			ProductNumber int NOT NULL
			PRIMARY KEY CLUSTERED ( DateStamp, RoomTypeNumber,ProductNumber)
		)
		INSERT INTO #RateLevelDateRoomProduct
		SELECT DISTINCT D.DateStampChar, D.DateStamp, DRP.RoomTypeNumber,DRP.ProductNumber
		FROM #RateLevelArrivalDates DRP
		INNER JOIN @Dates D ON D.DateStamp BETWEEN DRP.DateStamp AND DRP.DepartureDate

		--SET @T2 = GETDATE()
		--PRINT 'Insert #RateLevelDateRoomProduct '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		CREATE TABLE #RateLevelTempProductPrice
		(
			ProductNumber int NOT NULL,
			RoomtypeNumber int NOT NULL,
			DateStamp datetime NULL,
			AdultPrice money NOT NULL,
			Children1Price money NOT NULL,
			Children2Price money NOT NULL,
			AdultArrivalAddon money NOT NULL,
			Children1ArrivalAddon money NOT NULL,
			Children2ArrivalAddon money NOT NULL,
			AdultStayAddon money NOT NULL,
			Children1StayAddon money NOT NULL,
			Children2StayAddon money NOT NULL,
			AdultDepartureAddon money NOT NULL,
			Children1DepartureAddon money NOT NULL,
			Children2DepartureAddon money NOT NULL
		)
		INSERT INTO #RateLevelTempProductPrice
			SELECT 
			PP.ProductNumber,
			PP.RoomtypeNumber,
			DRP.DateStamp,
			ISNULL(CASE 
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN @NoAdults*PP.AdultPrice1
				WHEN @NoAdults=1 THEN PP.AdultPrice1
				WHEN @NoAdults=2 THEN PP.AdultPrice2
				WHEN @NoAdults=3 THEN PP.AdultPrice3
				WHEN @NoAdults=4 THEN PP.AdultPrice4
				WHEN @NoAdults=5 THEN PP.AdultPrice5
				WHEN @NoAdults=6 THEN PP.AdultPrice6
				WHEN @NoAdults=7 THEN PP.AdultPrice7
				WHEN @NoAdults=8 THEN PP.AdultPrice8
				WHEN @NoAdults=9 THEN PP.AdultPrice9
			END,0) AdultPrice,
			ISNULL(CASE 
				WHEN @NoChildren1=0 THEN 0
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN @NoChildren1*PP.Children1Price1
				WHEN @NoChildren1=1 THEN PP.Children1Price1
				WHEN @NoChildren1=2 THEN PP.Children1Price2
				WHEN @NoChildren1=3 THEN PP.Children1Price3
				WHEN @NoChildren1=4 THEN PP.Children1Price4
				WHEN @NoChildren1=5 THEN PP.Children1Price5
				WHEN @NoChildren1=6 THEN PP.Children1Price6
				WHEN @NoChildren1=7 THEN PP.Children1Price7
				WHEN @NoChildren1=8 THEN PP.Children1Price8
				WHEN @NoChildren1=9 THEN PP.Children1Price9
			END,0) Children1Price,
			ISNULL(CASE 
				WHEN @NoChildren2=0 THEN 0
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN @NoChildren2*PP.Children2Price1
				WHEN @NoChildren2=1 THEN PP.Children2Price1
				WHEN @NoChildren2=2 THEN PP.Children2Price2
				WHEN @NoChildren2=3 THEN PP.Children2Price3
				WHEN @NoChildren2=4 THEN PP.Children2Price4
				WHEN @NoChildren2=5 THEN PP.Children2Price5
				WHEN @NoChildren2=6 THEN PP.Children2Price6
				WHEN @NoChildren2=7 THEN PP.Children2Price7
				WHEN @NoChildren2=8 THEN PP.Children2Price8
				WHEN @NoChildren2=9 THEN PP.Children2Price9
			END,0) Children2Price,
			ISNULL(CASE
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.AdultArrivalAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.AdultArrivalAddon
				WHEN @NoAdults=1 THEN PP.AdultArrivalAddon
				WHEN @NoAdults=2 THEN PP.AdultArrivalAddon2
				WHEN @NoAdults=3 THEN PP.AdultArrivalAddon3
				WHEN @NoAdults=4 THEN PP.AdultArrivalAddon4
				WHEN @NoAdults=5 THEN PP.AdultArrivalAddon5
				WHEN @NoAdults=6 THEN PP.AdultArrivalAddon6
				WHEN @NoAdults=7 THEN PP.AdultArrivalAddon7
				WHEN @NoAdults=8 THEN PP.AdultArrivalAddon8
				WHEN @NoAdults=9 THEN PP.AdultArrivalAddon9
			END,0)*(@NoAdults * P.Modifier1 + P.Modifier2) AdultArrivalAddon,
			ISNULL(CASE 
				WHEN @NoChildren1=0 THEN 0
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children1ArrivalAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.Children1ArrivalAddon
				WHEN @NoChildren1=1 THEN PP.Children1ArrivalAddon
				WHEN @NoChildren1=2 THEN PP.Children1ArrivalAddon2
				WHEN @NoChildren1=3 THEN PP.Children1ArrivalAddon3
				WHEN @NoChildren1=4 THEN PP.Children1ArrivalAddon4
				WHEN @NoChildren1=5 THEN PP.Children1ArrivalAddon5
				WHEN @NoChildren1=6 THEN PP.Children1ArrivalAddon6
				WHEN @NoChildren1=7 THEN PP.Children1ArrivalAddon7
				WHEN @NoChildren1=8 THEN PP.Children1ArrivalAddon8
				WHEN @NoChildren1=9 THEN PP.Children1ArrivalAddon9
			END,0)*(@NoChildren1 * P.Modifier1 + P.Modifier2) Children1ArrivalAddon,
			ISNULL(CASE 
				WHEN @NoChildren2=0 THEN 0
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children2ArrivalAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.Children2ArrivalAddon
				WHEN @NoChildren2=1 THEN PP.Children2ArrivalAddon
				WHEN @NoChildren2=2 THEN PP.Children2ArrivalAddon2
				WHEN @NoChildren2=3 THEN PP.Children2ArrivalAddon3
				WHEN @NoChildren2=4 THEN PP.Children2ArrivalAddon4
				WHEN @NoChildren2=5 THEN PP.Children2ArrivalAddon5
				WHEN @NoChildren2=6 THEN PP.Children2ArrivalAddon6
				WHEN @NoChildren2=7 THEN PP.Children2ArrivalAddon7
				WHEN @NoChildren2=8 THEN PP.Children2ArrivalAddon8
				WHEN @NoChildren2=9 THEN PP.Children2ArrivalAddon9
			END,0)*(@NoChildren2 * P.Modifier1 + P.Modifier2) Children2ArrivalAddon,
			ISNULL(CASE
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.AdultStayAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.AdultStayAddon
				WHEN @NoAdults=1 THEN PP.AdultStayAddon
				WHEN @NoAdults=2 THEN PP.AdultStayAddon2
				WHEN @NoAdults=3 THEN PP.AdultStayAddon3
				WHEN @NoAdults=4 THEN PP.AdultStayAddon4
				WHEN @NoAdults=5 THEN PP.AdultStayAddon5
				WHEN @NoAdults=6 THEN PP.AdultStayAddon6
				WHEN @NoAdults=7 THEN PP.AdultStayAddon7
				WHEN @NoAdults=8 THEN PP.AdultStayAddon8
				WHEN @NoAdults=9 THEN PP.AdultStayAddon9
			END,0)*(@NoAdults * P.Modifier1 + P.Modifier2) AdultStayAddon,
			ISNULL(CASE 
				WHEN @NoChildren1=0 THEN 0
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children1StayAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.Children1StayAddon
				WHEN @NoChildren1=1THEN PP.Children1StayAddon
				WHEN @NoChildren1=2 THEN PP.Children1StayAddon2
				WHEN @NoChildren1=3 THEN PP.Children1StayAddon3
				WHEN @NoChildren1=4 THEN PP.Children1StayAddon4
				WHEN @NoChildren1=5 THEN PP.Children1StayAddon5
				WHEN @NoChildren1=6 THEN PP.Children1StayAddon6
				WHEN @NoChildren1=7 THEN PP.Children1StayAddon7
				WHEN @NoChildren1=8 THEN PP.Children1StayAddon8
				WHEN @NoChildren1=9 THEN PP.Children1StayAddon9
			END,0)*(@NoChildren1 * P.Modifier1 + P.Modifier2) Children1StayAddon,
			ISNULL(CASE 
				WHEN @NoChildren2=0 THEN 0
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children2StayAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.Children2StayAddon
				WHEN @NoChildren2=1 THEN PP.Children2StayAddon
				WHEN @NoChildren2=2 THEN PP.Children2StayAddon2
				WHEN @NoChildren2=3 THEN PP.Children2StayAddon3
				WHEN @NoChildren2=4 THEN PP.Children2StayAddon4
				WHEN @NoChildren2=5 THEN PP.Children2StayAddon5
				WHEN @NoChildren2=6 THEN PP.Children2StayAddon6
				WHEN @NoChildren2=7 THEN PP.Children2StayAddon7
				WHEN @NoChildren2=8 THEN PP.Children2StayAddon8
				WHEN @NoChildren2=9 THEN PP.Children2StayAddon9
			END,0)*(@NoChildren2 * P.Modifier1 + P.Modifier2) Children2StayAddon,
			ISNULL(CASE
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.AdultDepartureAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.AdultDepartureAddon
				WHEN @NoAdults=1 THEN PP.AdultDepartureAddon
				WHEN @NoAdults=2 THEN PP.AdultDepartureAddon2
				WHEN @NoAdults=3 THEN PP.AdultDepartureAddon3
				WHEN @NoAdults=4 THEN PP.AdultDepartureAddon4
				WHEN @NoAdults=5 THEN PP.AdultDepartureAddon5
				WHEN @NoAdults=6 THEN PP.AdultDepartureAddon6
				WHEN @NoAdults=7 THEN PP.AdultDepartureAddon7
				WHEN @NoAdults=8 THEN PP.AdultDepartureAddon8
				WHEN @NoAdults=9 THEN PP.AdultDepartureAddon9
			END,0)*(@NoAdults * P.Modifier1 + P.Modifier2) AdultDepartureAddon,
			ISNULL(CASE 
				WHEN @NoChildren1=0 THEN 0
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children1DepartureAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.Children1DepartureAddon
				WHEN @NoChildren1=1 THEN PP.Children1DepartureAddon
				WHEN @NoChildren1=2 THEN PP.Children1DepartureAddon2
				WHEN @NoChildren1=3 THEN PP.Children1DepartureAddon3
				WHEN @NoChildren1=4 THEN PP.Children1DepartureAddon4
				WHEN @NoChildren1=5 THEN PP.Children1DepartureAddon5
				WHEN @NoChildren1=6 THEN PP.Children1DepartureAddon6
				WHEN @NoChildren1=7 THEN PP.Children1DepartureAddon7
				WHEN @NoChildren1=8 THEN PP.Children1DepartureAddon8
				WHEN @NoChildren1=9 THEN PP.Children1DepartureAddon9
			END,0)*(@NoChildren1 * P.Modifier1 + P.Modifier2) Children1DepartureAddon,
			ISNULL(CASE 
				WHEN @NoChildren2=0 THEN 0
				WHEN @OnePersonPaysForAll = 0 AND P.NameListSetting = 1 THEN PP.Children2DepartureAddon
				WHEN @AddOnFieldsPerPerson=0 THEN PP.Children2DepartureAddon
				WHEN @NoChildren2=1 THEN PP.Children2DepartureAddon
				WHEN @NoChildren2=2 THEN PP.Children2DepartureAddon2
				WHEN @NoChildren2=3 THEN PP.Children2DepartureAddon3
				WHEN @NoChildren2=4 THEN PP.Children2DepartureAddon4
				WHEN @NoChildren2=5 THEN PP.Children2DepartureAddon5
				WHEN @NoChildren2=6 THEN PP.Children2DepartureAddon6
				WHEN @NoChildren2=7 THEN PP.Children2DepartureAddon7
				WHEN @NoChildren2=8 THEN PP.Children2DepartureAddon8
				WHEN @NoChildren2=9 THEN PP.Children2DepartureAddon9
			END,0)*(@NoChildren2 * P.Modifier1 + P.Modifier2) Children2DepartureAddon
		FROM dbo.ProductPrice PP 
		INNER JOIN #RateLevelDateRoomProduct DRP ON DRP.ProductNumber=PP.ProductNumber AND DRP.RoomTypeNumber=PP.RoomtypeNumber AND DRP.DateStampChar=PP.DateStamp
		INNER JOIN (
			SELECT pro.ProductNumber, 
			ISNULL(pro.NameListSetting,0) NameListSetting,
			CASE WHEN (ISNULL(pro.AddonType,0) = 0 AND @AddOnFieldsPerPerson = 0) OR (@AddOnFieldsPerPerson = 1 and ISNULL(pro.NameListSetting,0) = 1 and @OnePersonPaysForAll = 0) 
				THEN 1 ELSE 0 END Modifier1,
			CASE WHEN (ISNULL(pro.AddonType,0) = 0 AND @AddOnFieldsPerPerson = 0) OR (@AddOnFieldsPerPerson = 1 and ISNULL(pro.NameListSetting,0) = 1 and @OnePersonPaysForAll = 0) 
				THEN 0 ELSE 1 END Modifier2
			FROM Products pro WITH (NOLOCK)
			INNER JOIN ProductExtended pe WITH (NOLOCK) ON pro.ProductNumber = pe.FK_ProductNumber AND (ISNULL(pe.NoChildren, 0) = 0 OR (@NoChildren1+@NoChildren2)=0)
		) P ON P.ProductNumber=PP.ProductNumber

		--SET @T2 = GETDATE()
		--PRINT 'Insert #RateLevelTempProductPrice '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()


		INSERT INTO @ProductPriceDay
		SELECT DRP.ProductNumber,DRP.RoomtypeNumber,DRP.DateStamp, DATEADD(day, @NoOfDays, DRP.DateStamp) DepartureDate,
			ISNULL(AdultPrice+Children1Price+Children2Price+AdultArrivalAddon+Children1ArrivalAddon+Children2ArrivalAddon,0) ArrivalPrice,
			ISNULL(AdultPrice+Children1Price+Children2Price+AdultStayAddon+Children1StayAddon+Children2StayAddon,0) StayPrice,
			ISNULL(AdultDepartureAddon+Children1DepartureAddon+Children2DepartureAddon,0) DeparturePrice,
			CASE WHEN AdultPrice+Children1Price+Children2Price > 0 THEN 1 ELSE 0 END, 0, CASE WHEN AdultPrice>0 THEN 0 ELSE 1 END,
			0,0,0,1,0,0,
			RT.RoomCount-ISNULL(U.RoomsUnavailable,0) RoomsAvailable
		FROM #RateLevelDateRoomProduct DRP
		INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=DRP.RoomTypeNumber
		LEFT OUTER JOIN @RateLevelRoomsUnAvailable U ON U.RoomTypeNumber=DRP.RoomTypeNumber AND U.Datestamp=DRP.Datestamp
		LEFT OUTER JOIN #RateLevelTempProductPrice PP ON PP.DateStamp=DRP.DateStamp AND PP.ProductNumber=DRP.ProductNumber AND PP.RoomtypeNumber=DRP.RoomTypeNumber

		--SET @T2 = GETDATE()
		--PRINT 'Insert @ProductPriceDay '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		IF @NoOfDays=1
		BEGIN
			;
			WITH T
			AS
			(
				SELECT 
				PP.ProductNumber,
				PP.RoomtypeNumber,
				PP.DateStamp,
				PP.ArrivalPrice+PP2.DeparturePrice DayPrice,
				PP.RoomsAvailable
				FROM #RateLevelArrivalDates A
				INNER JOIN @ProductPriceDay PP ON PP.DateStamp=A.DateStamp AND PP.RoomtypeNumber=A.RoomTypeNumber AND PP.ProductNumber=A.ProductNumber AND PP.AdultPriceIsZero=0
				INNER JOIN @ProductPriceDay PP2 ON PP2.ProductNumber=PP.ProductNumber AND PP2.RoomtypeNumber=PP.RoomtypeNumber AND PP2.DateStamp=DATEADD(day, 1, PP.DateStamp)
				WHERE PP.DateStamp <= @EndDate
				
			)
			INSERT INTO @TotalPrices
			SELECT T.DateStamp, T.RoomTypeNumber, T.ProductNumber, T.DayPrice, T.RoomsAvailable
			FROM T
			INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=T.RoomtypeNumber
			WHERE DayPrice > 0
		END
		ELSE
		BEGIN
			;
			WITH T
			AS
			(
				SELECT 
				A.ProductNumber,
				A.RoomtypeNumber,
				A.DateStamp,
				SUM(CASE PP2.DateStamp
					WHEN A.DateStamp THEN PP2.ArrivalPrice
					WHEN A.DepartureDate THEN PP2.DeparturePrice
					ELSE PP2.StayPrice
					END) TotalPrice,
				MAX(CASE PP2.DateStamp
					WHEN A.DateStamp THEN PP2.AdultPriceIsZero
					WHEN A.DepartureDate THEN 0
					ELSE PP2.AdultPriceIsZero
					END) AdultPriceIsZero,
				COUNT(*) DayCount,
				MIN(CASE PP2.DateStamp
					WHEN A.DepartureDate THEN 2147483647
					ELSE PP2.RoomsAvailable
					END) RoomsAvailable
				FROM #RateLevelArrivalDates A
				INNER JOIN @ProductPriceDay PP2 ON PP2.ProductNumber=A.ProductNumber AND PP2.RoomtypeNumber=A.RoomtypeNumber AND PP2.DateStamp BETWEEN A.DateStamp AND A.DepartureDate
				AND A.DateStamp <= @EndDate
				GROUP BY A.ProductNumber, A.RoomTypeNumber, A.DateStamp
			)
			INSERT INTO @TotalPrices
			SELECT T.DateStamp, T.RoomTypeNumber, T.ProductNumber, T.TotalPrice, T.RoomsAvailable
			FROM T
			WHERE DayCount = @NoOfDays+1 -- There must exist a price for all the days in the stay
			AND AdultPriceIsZero=0
		END

		--SET @T2 = GETDATE()
		--PRINT 'Insert @TotalPrices '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
		--SET @T1 = GETDATE()

		DROP TABLE #tmpRateLevelGetAllocation1
		DROP TABLE #RateLevelArrivalDates
		DROP TABLE #RateLevelAvailability
		DROP TABLE #RateLevelTempProductPrice
		DROP TABLE #RateLevelDateRoomProduct
	END

	DECLARE @MinPrices TABLE (
		Datestamp datetime NOT NULL PRIMARY KEY,
		Price money
	)
	IF @CalendarMode=1
	BEGIN
		IF (@IncludePrices=1) OR (@OnlineAvailabilityRateLevel>0)
		BEGIN
			INSERT INTO @MinPrices
			SELECT DateStamp, MIN(CASE WHEN D.DiscountPct IS NULL THEN T.Price ELSE T.Price*(100-D.DiscountPct)/100 END)
			FROM @TotalPrices T
			LEFT OUTER JOIN @ProductArrivalDateDiscount D ON D.ArrivalDate=T.DateStamp AND D.ProductNumber=T.ProductNumber
			GROUP BY DateStamp
			ORDER BY DateStamp

			--SET @T2 = GETDATE()
			--PRINT 'Insert @MinPrices '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()

			-- Select no-arrival dates
			SELECT DateStamp
			FROM @Dates
			WHERE DateStamp <= @EndDate
			EXCEPT
			SELECT DateStamp
			FROM @MinPrices

			--SET @T2 = GETDATE()
			--PRINT 'Select no-arrival dates '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar)
			--SET @T1 = GETDATE()
		END
		IF @IncludePrices=1
		BEGIN
			SELECT *
			FROM @MinPrices

			--SET @T2 = GETDATE()
			--PRINT 'Select @MinPrices '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
			--SET @T1 = GETDATE()
		END
	END
	ELSE
	BEGIN

		SELECT 
			T.RoomTypeNumber
			,T.ProductNumber
			,ISNULL(P.[Description],'') AS [Description]
			,CAST(NULL AS money) RoomPriceFirstDay
			,T.Price*@NoOfRooms AS RoomPrice
			,ISNULL(P.ProductName,'') AS ProductName
			,CAST(NULL AS money) RoomPricePerDay
			,CAST(NULL AS money) RoomPriceMinTotal
			,ISNULL(PE.NoHostelDiscount,0) NoHostelDiscount
			,RT.HotelNumber AS HotelNumber
			,RT.OnlineRoomTypeNumber
			,CAST(ISNULL(RT.OnlinePriority,0) AS tinyint) OnlinePriority
			,ISNULL(TXT1.[Text],'') AS RomTypeName
			,ISNULL(TXT2.[Text],'') AS RomTypeNamePax
			,CASE WHEN RT.IsBunkBed=1 THEN @NoOfRooms ELSE @NoAdults END AS Adults
			,T.RoomsAvailable
		FROM @TotalPrices T
		INNER JOIN Products P WITH (NOLOCK) ON P.ProductNumber=T.ProductNumber
		INNER JOIN ProductExtended PE WITH (NOLOCK) ON P.ProductNumber = PE.FK_ProductNumber
		INNER JOIN @RoomTypes RT ON RT.RoomTypeNumber=T.RoomtypeNumber
		INNER JOIN @Hotels H ON H.HotelNumber=RT.HotelNumber
		CROSS APPLY (
			SELECT CASE WHEN @NoOfRooms > 1 AND @TotPax = 1 AND H.UseMappingRoomtypes = 1 THEN @NoOfRooms ELSE @TotPax END TotPaxDynamicText
		) XX
		LEFT OUTER JOIN OnlineRoomTypesTranslated TXT1 WITH (NOLOCK) ON TXT1.OnlineRoomTypeNumber = RT.OnlineRoomTypeNumber AND TXT1.CountryNumber = @CountryNumber AND pax=0
		LEFT OUTER JOIN OnlineRoomTypesTranslated TXT2 WITH (NOLOCK) ON TXT2.OnlineRoomTypeNumber = RT.OnlineRoomTypeNumber AND TXT2.CountryNumber = @CountryNumber AND TXT2.pax=XX.TotPaxDynamicText
		ORDER BY RT.HotelNumber, T.ProductNumber, T.RoomTypeNumber, P.[Description]

		--SET @T2 = GETDATE()
		--PRINT 'Select result '+CAST(DATEDIFF(ms, @T1, @T2) AS varchar) 
		--SET @T1 = GETDATE()

	END
	--SET @TTotal2 =  GETDATE()
	--PRINT 'Total '+CAST(DATEDIFF(ms, @TTotal1, @TTotal2) AS varchar) 
END
