Procedure gets compile more than once
-
hi there, I hv a proc which uses lot of temp tables and few cursors . I hv use DML and DDL commands on temp table. I would like to know the reason, why SP gets compile more than once
any help ????
thanks
-
Are you using any dynamic SQL i.e. EXEC @SQLCommand or EXEC sp_executesql @SQLCommand type coding?
Without seeing a snippet of the code that might be a reason. Another might be depending on how the code is written SQL may review it and say I have to recompile everytime because of x. If you could post some or all of the code for us to review it might help to answer your question.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
If you are mixing and matching DDL with DML then you are almost certain to get recompiles.
The way that I would approach the problem is to break out the proc so there is one master proc that calls a sub prod for DDL and a separate proc or procs for DML.
You probably can't eliminate the recompiles, but you can limit their scope.
-
Hi
Thnaks for replay.Pls find the code below...
ALTER PROCEDURE IOL_AccomodationSearchResults
@lv_SessionID varchar(50),
@li_ProductXid int,
@li_BrochureXid int,
@li_HolidayXid int,
@li_ClientXid int,
@ld_FromDate datetime,
@ld_ToDate datetime,
@lv_HotelXid varchar(1000),
@li_HotelChainXid int,
@li_SupplierXid int,
@li_PropertyTypeXid int,
@li_RatingXid int,
@lv_Location varchar(20),
@li_CountryXid int,
@li_CityXid int,
@li_CountyXid int,
@li_ZoneXid int,
@lv_HotelFacilities varchar(1000),
@lv_RoomFacilities varchar(1000),
@li_OfferTypeXid int,
@lv_RoomConfiguratrion varchar(100),
@lc_QBDataYN char(1),
@li_NoOfPax tinyint,
@li_CompanyXid int,
@li_LastEditByXid int
AS
DECLARE
@li_StringPos int,
@li_NoOfAdult tinyint,
@li_NoOfChildren tinyint,
@li_Age tinyint,
@li_DayWeek tinyint,
@li_CheckinDay tinyint,
@li_NoOfContracts tinyint,
@li_RConfigDtlsXid tinyint,
@li_ChildAgeFrom tinyint,
@li_ChildAgeTo tinyint,
@li_MinimumStay tinyint,
@li_TempMinStay tinyint,
@li_PerWhatQty tinyint,
@li_Qty int,
@li_AdultChargeTypeXid int,
@li_ChildChargeTypeXid int,
@li_ExtraChildChargeTypeXid int,
@li_ChargeTypeXid int,
@li_MaxXid int,
@li_i int,
@li_Counter int,
@li_j int,
@li_Cnt int,
@li_k int,
@li_l int,
@li_RateXid int,
@li_CombinationRuleCnt int,
@li_QuickBedSupplierXid int,
@li_HotelXid int,
@li_OldHotelXid int,
@li_SHCDataDtlsXid int,
@li_RoomTypeXid int,
@li_RoomConfigurationXid int,
@li_NoOfNights int,
@li_Sequence int,
@li_OldSHCDataDtlXid int,
@li_BaseCurrencyXid int,
@li_SHCDataDtlXid int,
@li_SortHCDataDtlsXid int,
@li_CurrencyXid int,
@li_QuickBedHotelRoomTypeXid int,
@li_OldChargeTypeXid int,
@li_HolidayContractXid int,
@li_OldRConfigDtlsXid int,
@li_HCSupplementXid int,
@li_SupplementXid int,
@li_OriginalChargeTypeXid int,
@li_OldRoomTypeXid int,
@li_OldRoomConfigurationXid int,
@li_OldSequence int,
@li_HolidayDiscountXid int,
@lv_ValidOn char(7),
@lc_BaseContractLevel char(3),
@lc_MinimumStayApplyOn char(1),
@lc_AvailabilityStatus char(1),
@lc_InsertYN char(1),
@lc_ClosePaxConfigTag char(1),
@lc_ClosePaxRoomTypeTag char(1),
@lc_ClosePaxRootTag char(1),
@lc_PayableBy char(1),
@lc_Mandatory char(1),
@lc_PerWhat char(2),
@lc_PercOrAmt char(1),
@lv_BaseContractType varchar(25),
@lv_SellRateString varchar(3000),
@lv_XMLString varchar(8000),
@lv_TempString varchar(8000),
@lv_RoomTypeXid varchar(1000),
@lv_RatesFor varchar(25),
@lv_SubString varchar(20),
@ld_BaseDate datetime,
@ld_StartDate datetime,
@lm_Value money,
@lm_DateRate money,
@lm_ExtraNightRate money,
@lm_RoomRate money,
@lm_Discount money,
@lm_RateDifference money,
@lm_SupplementRate money,
@BookingAcross int,
@li_ToQty int,
@lc_TypeOFQty char(1),
@lv_ContractType varchar(10),
@lv_LastString varchar(8000),
@li_MaxRoomDetailsPid int,
@li_LastHotelXid int,
@ld_SuppToDate datetime
DECLARE @HotelFacilities TABLE
(
FacilitiesXid int
)
DECLARE @RoomFacilities TABLE
(
FacilitiesXid int
)
DECLARE @Hotels TABLE
(
HotelXid int
)
DECLARE @RoomConfiguratrion TABLE
(
Pid int identity,
NoOfAdult tinyint,
NoOfChildren tinyint
)
DECLARE @RoomChargeTypes TABLE
(
Pid int identity,
RoomConfiguratrionXid int,
ChargeTypeXid int,
Age int
)
CREATE TABLE #ContractedHotels
(
Pid int identity,
SHCDataXid int,
HotelXid int,
SupplierXid int,
ChildAgeFrom tinyint,
ChildAgeTo tinyint
)
CREATE TABLE #SortHCDataDtls
(
Pid int identity,
SHCDataDtlXid int,
HolidayContractXid int,
CurrencyXid int,
ContractLevel char(3),
ContractType varchar(25),
[Sequence] int,
HotelXid int,
RoomTypeXid int,
RoomConfigurationXid int,
Dated datetime,
SellRateString varchar(3000),
Status int
)
DECLARE @HotelRoomTypes TABLE
(
Pid int identity,
HotelXid int,
SupplierXid int,
RoomTypeXid int,
ChildAgeFrom tinyint,
ChildAgeTo tinyint
)
CREATE TABLE #Allocation
(
HotelXid int,
AllocationXid int NULL,
FreeSaleYN char(1),
AllocationDate datetime NULL,
AllocationOnXid int NULL,
AllocationQty int NULL,
AllocationStatus char(1),
RoomStatus char(1)
)
DECLARE @Rates TABLE
(
Pid int identity,
ChargeTypeXid int,
AllocationYN char(1),
TypeOfQty char(1),
FromQty int,
ToQty int,
MinimumStay tinyint,
MinimumStayApplyOn char(1),
Rate money,
ExtraNightrate money
)
DECLARE @AgePolicy TABLE
(
Pid tinyint,
RateXid int,
AgeFrom tinyint,
AgeTo tinyint,
RateDifference money
)
DECLARE @TimePolicy TABLE
(
Pid tinyint,
RateXid int,
TimeFrom datetime,
TimeTo datetime,
RateDifference money
)
DECLARE @CombinationRule TABLE
(
Pid tinyint,
ChargeTypeXid int,
Qty tinyint,
ChildAgeFrom tinyint,
ChildAgeTo tinyint,
Value money,
PercOrAmt char(1)
)
CREATE TABLE #TempRates
(
Pid int identity,
SortHCDataDtlsXid int,
CurrencyXid int,
RConfigDtlsXid int,
ChargeTypeXid int,
Rate money,
MinimumStay tinyint,
MinimumStayApplyOn char(1),
ExtraNightRate money
)
CREATE TABLE #FinalRates
(
Pid int identity,
HotelXid int,
RoomTypeXid int,
RoomConfigurationXid int,
[Sequence] tinyint,
HolidayContractXid int,
CurrencyXid int,
Dated datetime,
RConfigDtlsXid int,
ChargeTypeXid int,
Rate money,
MinimumStay tinyint,
MinimumStayApplyOn char(1),
ExtraNightRate money,
ContractType varchar(10)
)
CREATE TABLE #SupplementData
(
HotelXid int,
RoomTypeXid int,
RoomConfigurationXid tinyint,
[Sequence] tinyint,
HolidayContractXid int,
HCSupplementXid int,
SupplementXid int,
FromDate datetime,
ToDate datetime,
NoOfNights int,
RatesFor varchar(25),
RConfigDtlsXid tinyint,
ChargeTypeXid int,
SellRate money,
Qty tinyint,
PayableBy char(1),
Mandatory char(1),
Status char(1)
)
CREATE TABLE #Discounts
(
HotelXid int,
RoomTypeXid int,
RoomConfigurationXid tinyint,
[Sequence] tinyint,
HolidayDiscountXid int,
PerWhat char(2),
PerWhatQty tinyint,
PercOrAmt char(1),
Value money
)
/* DELETE FROM HotelUserSelection For This UserID */
DELETE FROM HotelUserSelection WHERE UserSessionId = @lv_SessionID
DELETE FROM HotelUserSelectionRoomString WHERE UserSessionId = @lv_SessionID
DELETE FROM HotelUserSelectionDtls WHERE UserSessionId = @lv_SessionID
/* DELETE FROM HotelUserSelection For This UserID */
IF(LTRIM(RTRIM(@lv_Location)) <> '')
BEGIN
SELECT @li_CityXid = Pid,
@li_CountryXid = CountryXid,
@li_CountyXid = CountyXid
FROM M_City
WHERE (Code = LTRIM(RTRIM(@lv_Location)))
END
IF (@li_ProductXid IS NULL)
BEGIN
SET @li_ProductXid = -1
END
IF (@li_BrochureXid IS NULL)
BEGIN
SET @li_BrochureXid = -1
END
IF (@li_HolidayXid IS NULL)
BEGIN
SET @li_HolidayXid = -1
END
IF (@li_PropertyTypeXid IS NULL)
BEGIN
SET @li_PropertyTypeXid = -1
END
IF (LTRIM(RTRIM(@lv_HotelXid)) = '')
BEGIN
SET @lv_HotelXid = '-1'
END
ELSE
BEGIN
INSERT INTO @Hotels(HotelXid)
SELECT Val FROM dbo.Fn_Split(@lv_HotelXid, '|')
END
IF (@li_HotelChainXid IS NULL)
BEGIN
SET @li_HotelChainXid = -1
END
IF (@li_SupplierXid IS NULL)
BEGIN
SET @li_SupplierXid = -1
END
IF (@li_PropertyTypeXid IS NULL)
BEGIN
SET @li_PropertyTypeXid = -1
END
IF (@li_RatingXid IS NULL)
BEGIN
SET @li_RatingXid = -1
END
IF (@li_CountryXid IS NULL)
BEGIN
SET @li_CountryXid = -1
END
IF (@li_CityXid IS NULL)
BEGIN
SET @li_CityXid = -1
END
IF (@li_CountyXid IS NULL)
BEGIN
SET @li_CountyXid = -1
END
IF (@li_ZoneXid IS NULL)
BEGIN
SET @li_ZoneXid = -1
END
IF (LTRIM(RTRIM(@lv_HotelFacilities)) = '')
BEGIN
SET @lv_HotelFacilities = '-1'
END
ELSE
BEGIN
INSERT INTO @HotelFacilities(FacilitiesXid)
SELECT Val FROM dbo.Fn_Split(@lv_HotelFacilities, '|')
END
IF (LTRIM(RTRIM(@lv_RoomFacilities)) = '')
BEGIN
SET @lv_RoomFacilities = '-1'
END
ELSE
BEGIN
INSERT INTO @RoomFacilities(FacilitiesXid)
SELECT Val FROM dbo.Fn_Split(@lv_RoomFacilities, '|')
END
IF (@li_OfferTypeXid IS NULL)
BEGIN
SET @li_OfferTypeXid = -1
END
SELECT @li_AdultChargeTypeXid = Pid
FROM M_ChargeType WHERE AdultChild = 'A' AND DefaultYN = 'Y'
SELECT @li_ChildChargeTypeXid = Pid
FROM M_ChargeType WHERE AdultChild='C' AND DefaultYN='Y'
SELECT @li_QuickBedSupplierXid = QuickBedSupplierXid
FROM M_GlobalSettings
SET @BookingAcross = -1
SET @li_NoOfNights = DATEDIFF(dd, @ld_FromDate, @ld_ToDate)
SET @lv_TempString = LTRIM(RTRIM(@lv_RoomConfiguratrion))
IF @lv_TempString <> ''
BEGIN
SELECT @li_StringPos = PATINDEX ('%|%', @lv_TempString)
WHILE @li_StringPos > 0
BEGIN
SET @lv_SubString = SUBSTRING (@lv_TempString,1,(@li_StringPos - 1))
SET @lv_TempString = SUBSTRING (@lv_TempString, (@li_StringPos + 1) , LEN (@lv_TempString))
SET @li_NoOfAdult = CONVERT (Int,@lv_SubString)
SELECT @li_StringPos = PATINDEX ('%|%', @lv_TempString)
IF @li_StringPos = 0
BEGIN
SET @li_NoOfChildren = CONVERT (Int, @lv_TempString)
END
ELSE
BEGIN
SET @lv_SubString = SUBSTRING (@lv_TempString,1,(@li_StringPos - 1))
SET @lv_TempString = SUBSTRING (@lv_TempString , (@li_StringPos + 1) , LEN (@lv_TempString))
SET @li_NoOfChildren = CONVERT (Int,@lv_SubString)
END
INSERT INTO @RoomConfiguratrion(NoOfAdult, NoOfChildren)
VALUES(@li_NoOfAdult, @li_NoOfChildren)
SELECT @li_MaxXid = MAX(Pid) FROM @RoomConfiguratrion
SET @li_i = 1
WHILE (@li_i <= @li_NoOfAdult)
BEGIN
INSERT INTO @RoomChargeTypes(RoomConfiguratrionXid, ChargeTypeXid, Age)
VALUES(@li_MaxXid, @li_AdultChargeTypeXid, 25)
SET @li_i = @li_i + 1
END
SET @li_i = 1
WHILE (@li_i <= @li_NoOfChildren)
BEGIN
SELECT @li_StringPos = PATINDEX ('%|%', @lv_TempString)
IF @li_StringPos = 0
BEGIN
SET @li_Age = CONVERT (int, @lv_TempString)
END
ELSE
BEGIN
SET @lv_SubString = SUBSTRING (@lv_TempString,1,(@li_StringPos - 1))
SET @lv_TempString = SUBSTRING (@lv_TempString, (@li_StringPos + 1) , LEN (@lv_TempString))
SET @li_Age = CONVERT (int, @lv_SubString)
END
INSERT INTO @RoomChargeTypes(RoomConfiguratrionXid, ChargeTypeXid, Age)
VALUES(@li_MaxXid, @li_ChildChargeTypeXid, @li_Age)
SET @li_i = @li_i + 1
END
SELECT @li_StringPos = PATINDEX ('%|%', @lv_TempString)
END
END
/* SELECT ALL HOTELS DEPENDING ON SEARCH CRITERIA */
INSERT INTO #ContractedHotels(HotelXid, SupplierXid, ChildAgeFrom, ChildAgeTo)
SELECT DISTINCT M_SortHotelContractData.HotelXid, M_Hotel.SupplierXid, M_Hotel.ChildAgeFrom, M_Hotel.ChildAgeTo
FROM M_SortHotelContractData
INNER JOIN M_Hotel ON M_Hotel.Pid = M_SortHotelContractData.HotelXid AND M_Hotel.Status = 'A'
LEFT JOIN
(
SELECT M_HotelOpenClose.HotelXid, ISNULL(M_HotelOpenClose.BookingAcross,'Y') AS BookingAcross
FROM
M_HotelOpenClose
WHERE
@ld_FromDate NOT BETWEEN ISNULL(M_HotelOpenClose.FromDate,'1900-01-01') AND ISNULL(M_HotelOpenClose.ToDate,'1900-01-01') AND
@ld_ToDate NOT BETWEEN ISNULL(M_HotelOpenClose.FromDate,'1900-01-01') AND ISNULL(M_HotelOpenClose.ToDate,'1900-01-01') AND
@ld_ToDate >= ISNULL(M_HotelOpenClose.ToDate,'2099-12-31') AND @ld_FromDate <= ISNULL(M_HotelOpenClose.FromDate,'1900-01-01') AND
BookingAcross = 'Y'
)AS OpenClose ON OpenClose.HotelXid = M_Hotel.Pid
LEFT JOIN M_HotelChain ON M_HotelChain.Pid = M_Hotel.HotelChainXid
LEFT JOIN M_PropertyType ON M_PropertyType.Pid = M_Hotel.PropertyTypeXid
LEFT JOIN M_HotelRatings ON M_HotelRatings.HotelXid = M_Hotel.Pid
INNER JOIN M_Country ON M_Country.Pid = M_Hotel.CountryXid
INNER JOIN M_City ON M_City.Pid = M_Hotel.CityXid
LEFT JOIN M_County ON M_County.Pid = M_Hotel.CountyXid
LEFT JOIN M_Zone ON M_Zone.Pid = M_Hotel.ZoneXid
INNER JOIN M_Holidays ON M_Holidays.Pid = M_SortHotelContractData.HolidayXid
INNER JOIN M_Brochures ON M_Brochures.Pid = M_SortHotelContractData.BrochureXid
INNER JOIN M_Products ON M_Products.Pid = M_SortHotelContractData.ProductXid
LEFT JOIN M_HotelFacilities HotelFacilities_1 ON HotelFacilities_1.HotelXid = M_Hotel.Pid AND HotelFacilities_1.EntityType = 'H'
LEFT JOIN M_HotelFacilities HotelFacilities_2 ON HotelFacilities_2.HotelXid = M_Hotel.Pid AND HotelFacilities_2.EntityType = 'T'
LEFT JOIN M_HolidayDiscounts ON M_HolidayDiscounts.EntityType='N' AND M_HolidayDiscounts.EntityXid = M_SortHotelContractData.HolidayContractXid
WHERE ((@ld_FromDate BETWEEN M_SortHotelContractData.FromDate AND M_SortHotelContractData.ToDate) OR
(@ld_ToDate BETWEEN M_SortHotelContractData.FromDate AND M_SortHotelContractData.ToDate)) AND
((M_SortHotelContractData.BuyOrSell = 'S' ) OR
(M_SortHotelContractData.BuyOrSell = 'O')) AND
((M_Hotel.Pid IN (SELECT HotelXid FROM @Hotels)) OR
(@lv_HotelXid = '-1')) AND
-- ((OpenClose.BookingAcross = 'Y') OR (@BookingAcross = -1)) AND
((M_Hotel.HotelChainXid = @li_HotelChainXid) OR
(@li_HotelChainXid = -1)) AND
((M_Hotel.SupplierXid = @li_SupplierXid) OR
(@li_SupplierXid = -1)) AND
((M_Hotel.PropertyTypeXid = @li_PropertyTypeXid) OR
(@li_PropertyTypeXid = -1)) AND
((M_HotelRatings.RatingXid = @li_RatingXid) OR
(@li_RatingXid = -1)) AND
((M_Hotel.CountryXid = @li_CountryXid) OR
(@li_CountryXid = -1)) AND
((M_Hotel.CityXid = @li_CityXid) OR
(@li_CityXid = -1)) AND
((M_Hotel.CountyXid = @li_CountyXid) OR
(@li_CountyXid = -1)) AND
((M_Hotel.ZoneXid = @li_ZoneXid) OR
(@li_ZoneXid = -1)) AND
((M_SortHotelContractData.HolidayXid = @li_HolidayXid) OR
(@li_HolidayXid = -1)) AND
((M_SortHotelContractData.BrochureXid = @li_BrochureXid) OR
(@li_BrochureXid = -1)) AND
((M_SortHotelContractData.ProductXid = @li_ProductXid) OR
(@li_ProductXid = -1)) AND
((HotelFacilities_1.FacilityXid IN (SELECT FacilitiesXid FROM @HotelFacilities)) OR
(@lv_HotelFacilities = '-1')) AND
((HotelFacilities_2.FacilityXid IN (SELECT FacilitiesXid FROM @RoomFacilities)) OR
(@lv_RoomFacilities = '-1')) AND
(((M_HolidayDiscounts.DiscountTypeXid = @li_OfferTypeXid) AND
(@ld_FromDate BETWEEN M_HolidayDiscounts.FromDate and M_HolidayDiscounts.ToDate)) OR
(@li_OfferTypeXid = -1))
/* SELECT ALL HOTELS DEPENDING ON SEARCH CRITERIA */
/* PICK UP ALL CONTRACTS FOR THAT HOTEL SATISFYING OCCUPANCY CRITERIA */
SELECT @li_Cnt = MAX(Pid) FROM @RoomConfiguratrion
SELECT @li_Counter = MAX(Pid) FROM #ContractedHotels
SET @li_i = 1
WHILE (@li_i <= @li_Counter)
BEGIN
SELECT @li_HotelXid = HotelXid,
@li_ChildAgeFrom = ChildAgeFrom,
@li_ChildAgeTo = ChildAgeTo
FROM #ContractedHotels
WHERE (Pid = @li_i)
SET @li_j = 1
WHILE (@li_j <= @li_Cnt)
BEGIN
SELECT @li_NoOfPax = COUNT(*)
FROM @RoomChargeTypes
WHERE (RoomConfiguratrionXid = @li_j)
SELECT @li_NoOfChildren = COUNT(ChargeTypeXid)
FROM @RoomChargeTypes
WHERE (RoomConfiguratrionXid = @li_j) AND
(Age <= ISNULL(@li_ChildAgeTo, Age)) AND
(Age <> 25)
SET @li_NoOfAdult = @li_NoOfPax - @li_NoOfChildren
SET @li_CheckinDay = DATEPART(dw,@ld_FromDate)
SET @ld_BaseDate = @ld_FromDate
WHILE (@ld_BaseDate < @ld_ToDate)
BEGIN
SET @li_DayWeek = DATEPART(dw,@ld_BaseDate)
INSERT INTO #SortHCDataDtls(SHCDataDtlXid, HolidayContractXid, CurrencyXid, ContractLevel, ContractType, HotelXid,
RoomTypeXid, RoomConfigurationXid, Dated, SellRateString, Status)
SELECT M_SortHotelContractDataDtls.Pid, M_SortHotelContractData.HolidayContractXid, AlternateCurrencyXid, ContractLevel, ContractType, @li_HotelXid,
M_SortHotelContractDataDtls.RoomTypeXid, @li_j, @ld_BaseDate, SellRateString, 1
FROM M_SortHotelContractData INNER JOIN M_SortHotelContractDataDtls
ON M_SortHotelContractDataDtls.SortHotelContractDataXid = M_SortHotelContractData.Pid
WHERE (M_SortHotelContractData.HotelXid = @li_HotelXid) AND
(@ld_BaseDate BETWEEN M_SortHotelContractDataDtls.FromDate AND M_SortHotelContractDataDtls.ToDate) AND
(NoOfAdults = @li_NoOfAdult) AND
(NoOfChildrens = @li_NoOfChildren) AND
((SUBSTRING(ValidOn, @li_DayWeek, 1) = 'Y') OR ((SUBSTRING(ValidOn, @li_CheckinDay, 1) = 'Y') AND (M_SortHotelContractData.ValidOnCheckinYN = 'Y')))
ORDER BY HolidayContractXid
SET @ld_BaseDate = DATEADD(dd, 1, @ld_BaseDate)
END
SET @li_j = @li_j + 1
END
SET @li_i = @li_i + 1
END
/* PICK UP ALL CONTRACTS FOR THAT HOTEL SATISFYING OCCUPANCY CRITERIA */
/* ARRANGE ALL CONTRACTS FOR THAT ROOMTYPE FOR THAT DATE BAND */
SET @li_i = 1
SELECT @li_Counter = MAX(Pid) FROM #SortHCDataDtls
WHILE (@li_i <= @li_Counter)
BEGIN
SELECT @li_RoomTypeXid = RoomTypeXid,
@li_RoomConfigurationXid = RoomConfigurationXid,
@li_HotelXid = HotelXid,
@ld_BaseDate = Dated,
@lv_BaseContractType = ContractType,
@li_HolidayContractXid = HolidayContractXid
FROM #SortHCDataDtls
WHERE (Pid = @li_i)
IF(SELECT COUNT(Pid) FROM #SortHCDataDtls
WHERE HolidayContractXid = @li_HolidayContractXid AND RoomTypeXid = @li_RoomTypeXid AND RoomConfigurationXid = @li_RoomConfigurationXid
GROUP BY HolidayContractXid,RoomTypeXid,RoomConfigurationXid) < @li_NoOfNights
BEGIN
DELETE FROM #SortHCDataDtls WHERE HolidayContractXid = @li_HolidayContractXid
END
SELECT @li_Sequence = MAX([Sequence])
FROM #SortHCDataDtls
WHERE (RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
(HotelXid = @li_HotelXid) AND
(Dated = @ld_BaseDate) AND
([Sequence] IS NOT NULL)
IF(@li_Sequence IS NULL)
BEGIN
SET @li_Sequence = 1
END
ELSE
BEGIN
SET @li_Sequence = @li_Sequence + 1
END
UPDATE #SortHCDataDtls
SET [Sequence] = @li_Sequence
WHERE (Pid = @li_i)
SET @ld_BaseDate = NULL
SET @li_i = @li_i + 1
END
/* ARRANGE ALL CONTRACTS FOR THAT ROOMTYPE FOR THAT DATE BAND */
/* CHECK FOR VALID CONTRACTS */
UPDATE #SortHCDataDtls
SET Status = -1
FROM #SortHCDataDtls INNER JOIN
(
SELECT HotelXid, RoomTypeXid, [Sequence]
FROM #SortHCDataDtls
GROUP BY HotelXid, RoomTypeXid, [Sequence]
HAVING COUNT([Sequence]) < @li_NoOfNights
 
AS ContractNotFoundON ContractNotFound.HotelXid = #SortHCDataDtls.HotelXid AND
ContractNotFound.RoomTypeXid = #SortHCDataDtls.RoomTypeXid AND
ContractNotFound.[Sequence] = #SortHCDataDtls.[Sequence]
SELECT @li_Cnt = MAX(Pid) FROM @RoomConfiguratrion
UPDATE #SortHCDataDtls
SET Status = -1
FROM #SortHCDataDtls INNER JOIN
(
SELECT HotelXid
FROM #SortHCDataDtls
GROUP BY HotelXid
HAVING COUNT(DISTINCT RoomConfigurationXid) < @li_Cnt
 
AS PaxConfigNotFoundON PaxConfigNotFound.HotelXid = #SortHCDataDtls.HotelXid
/* CHECK FOR VALID CONTRACTS */
/* SELECT DISTINCT HOTELS WITH ROOMTYPES */
INSERT INTO @HotelRoomTypes(HotelXid, SupplierXid, RoomTypeXid)
SELECT DISTINCT #ContractedHotels.HotelXid, #ContractedHotels.SupplierXid, RoomTypeXid
FROM #SortHCDataDtls INNER JOIN #ContractedHotels ON #ContractedHotels.HotelXid = #SortHCDataDtls.HotelXid
WHERE (Status = 1)
ORDER BY #ContractedHotels.HotelXid, RoomTypeXid
DROP TABLE #ContractedHotels
/* SELECT DISTINCT HOTELS WITH ROOMTYPES */
/* RETURN QUICK BED ROOMTYPES */
IF(@lc_QBDataYN = 'Y')
BEGIN
SET @ld_ToDate = DATEADD(dd, -1, @ld_ToDate)
SELECT DISTINCT QuickBedHotelRoomTypeXid,
CONVERT(VARCHAR(2), @ld_FromDate, 103) + '/' + CONVERT(VARCHAR(3), @ld_FromDate, 107) + '/' + CONVERT(VARCHAR(4), @ld_FromDate, 121) As FromDate,
CONVERT(VARCHAR(2), @ld_ToDate, 103) + '/' + CONVERT(VARCHAR(3), @ld_ToDate, 107) + '/' + CONVERT(VARCHAR(4), @ld_ToDate, 121) As ToDate
FROM @HotelRoomTypes HotelRoomTypes
INNER JOIN M_HotelRoomType ON M_HotelRoomType.HotelXid = HotelRoomTypes.HotelXid AND
M_HotelRoomType.RoomTypeXid = HotelRoomTypes.RoomTypeXid
WHERE (SupplierXid = @li_QuickBedSupplierXid) AND
(QuickBedHotelRoomTypeXid IS NOT NULL)
RETURN
END
/* RETURN QUICK BED ROOMTYPES */
/* CHECK FOR ALLOCATION */
DECLARE C_Allocation CURSOR FOR
SELECT DISTINCT HotelXid
FROM @HotelRoomTypes
WHERE (ISNULL(SupplierXid, -1) <> @li_QuickBedSupplierXid)
OPEN C_Allocation
FETCH C_Allocation INTO @li_HotelXid
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @lv_RoomTypeXid = NULL
SELECT @lv_RoomTypeXid = ISNULL(
@lv_RoomTypeXid + ',' +
CONVERT(VARCHAR(10), RoomTypeXid) ,
CONVERT(VARCHAR(10), RoomTypeXid)
 
FROM @HotelRoomTypes
WHERE (HotelXid = @li_HotelXid)
IF(@lv_RoomTypeXid IS NOT NULL)
BEGIN
INSERT INTO #Allocation
EXEC IOL_AllocationBookingSearchAllocationFN
'H',
@li_HotelXid,
@ld_FromDate,
@ld_ToDate,
@lv_RoomTypeXid,
@li_CompanyXid,
@li_ProductXid,
@li_BrochureXid,
@li_HolidayXid,
@li_ClientXid,
@li_LastEditByXid,
@li_NoOfPax
END
FETCH C_Allocation INTO @li_HotelXid
END
CLOSE C_Allocation
DEALLOCATE C_Allocation
/* CHECK FOR ALLOCATION */
--SELECT * FROM #Allocation
--SELECT * FROM @HotelRoomTypes
--SELECT * FROM #SortHCDataDtls
--RETURN
/* UPDATE STATUS FOR ROOMS WHICH DON'T HAVE ALLOCATION */
UPDATE #SortHCDataDtls
SET Status = -1
WHERE Pid NOT IN (
SELECT DISTINCT #SortHCDataDtls.Pid
FROM #SortHCDataDtls INNER JOIN #Allocation
ON #Allocation.HotelXid = #SortHCDataDtls.HotelXid AND
#Allocation.AllocationOnXid = #SortHCDataDtls.RoomTypeXid
 
AND(HotelXid NOT IN (SELECT HotelXid FROM @HotelRoomTypes WHERE SupplierXid = @li_QuickBedSupplierXid))
/* UPDATE STATUS FOR ROOMS WHICH DON'T HAVE ALLOCATION */
/* CALCULATE RATE */
SET @li_i = 1
SELECT @li_Counter = MAX(Pid) FROM @HotelRoomTypes
WHILE (@li_i <= @li_Counter) -- For Each Hotel And Each RoomType
BEGIN
SELECT @li_HotelXid = HotelXid,
@li_RoomTypeXid = RoomTypeXid,
@li_ChildAgeFrom = ChildAgeFrom,
@li_ChildAgeTo = ChildAgeTo
FROM @HotelRoomTypes
WHERE (Pid = @li_i)
SET @li_j = 1
SELECT @li_Cnt = MAX(Pid) FROM @RoomConfiguratrion
WHILE (@li_j <= @li_Cnt) -- For Each Room Configuration
BEGIN
SELECT @li_NoOfPax = COUNT(*)
FROM @RoomChargeTypes
WHERE (RoomConfiguratrionXid = @li_j)
SELECT @li_NoOfChildren = COUNT(ChargeTypeXid)
FROM @RoomChargeTypes
WHERE (RoomConfiguratrionXid = @li_j) AND
(Age <= ISNULL(@li_ChildAgeTo, Age)) AND
(Age <> 25)
SET @li_NoOfAdult = @li_NoOfPax - @li_NoOfChildren
SELECT @li_NoOfContracts = MAX([Sequence])
FROM #SortHCDataDtls
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j)
SET @li_k = 1
WHILE (@li_k <= @li_NoOfContracts) -- For Each Contract
BEGIN
IF( SELECT COUNT(*) FROM #SortHCDataDtls
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j) AND
([Sequence] = @li_k) AND
(Status = -1)
 
> 0BEGIN
GOTO NEXT_CONTRACT
END
/* CHECK FOR VALID CONTARCT TYPE */
SELECT @lc_BaseContractLevel = ContractLevel,
@lv_BaseContractType = ContractType,
@li_BaseCurrencyXid = CurrencyXid,
@li_HolidayContractXid = HolidayContractXid
FROM #SortHCDataDtls
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j) AND
([Sequence] = @li_k) AND
(Dated = @ld_FromDate)
/*IF
(
SELECT COUNT(*)
FROM #SortHCDataDtls
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j) AND
([Sequence] = @li_k) AND
(ContractType <> @lv_BaseContractType) AND
(HolidayContractXid = @li_HolidayContractXid)
 
>0 BEGIN
GOTO NEXT_CONTRACT
END*/
/* CHECK FOR VALID CONTARCT TYPE */
/* CHECK FOR MIN STAY */
SELECT @li_SHCDataDtlXid = SHCDataDtlXid,
@lv_SellRateString = SellRateString
FROM #SortHCDataDtls
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j) AND
([Sequence] = @li_k) AND
(Dated = @ld_FromDate)
DELETE FROM @Rates
-- Combination Rule String
SELECT @li_StringPos = PATINDEX ('%|%', @lv_SellRateString)
SET @lv_SellRateString = SUBSTRING (@lv_SellRateString, 1, (@li_StringPos - 1))
WHILE (LEN(@lv_SellRateString) > 0)
BEGIN
SET @li_StringPos = PATINDEX ('%^%' , @lv_SellRateString)
IF @li_StringPos > 0
BEGIN
SET @lv_TempString = SUBSTRING (@lv_SellRateString, 1, @li_StringPos - 1)
SET @lv_SellRateString = SUBSTRING (@lv_SellRateString, @li_StringPos + 1, LEN(@lv_SellRateString))
END
ELSE
BEGIN
SET @lv_TempString = @lv_SellRateString
SET @lv_SellRateString = ''
END
-- Rate String
SELECT @li_StringPos = PATINDEX ('%*%', @lv_TempString)
SET @lv_XMLString = SUBSTRING (@lv_TempString, 1, (@li_StringPos - 1))
INSERT INTO @Rates
SELECT ChargeTypeXid,AllocationYN,TypeOfQty,FromQty,
ToQty,MinimumStay,MinimumStayApplyOn,Rate,ExtraNightRate
FROM UF_IOLParseRates(@lv_XMLString)
END
SELECT @li_TempMinStay = MinimumStay,
@lc_MinimumStayApplyOn = MinimumStayApplyOn
FROM @Rates
WHERE (MinimumStay = @li_NoOfNights)
IF(@@ROWCOUNT = 0)
BEGIN
SELECT @li_TempMinStay = MinimumStay,
@lc_MinimumStayApplyOn = MinimumStayApplyOn
FROM @Rates
WHERE (MinimumStay = (SELECT MAX(MinimumStay)
FROM @Rates
WHERE (@li_NoOfNights >= MinimumStay)))
IF(@@ROWCOUNT = 0)
BEGIN
SELECT TOP 1 @li_TempMinStay = Max(MinimumStay),
@lc_MinimumStayApplyOn = MinimumStayApplyOn
FROM @Rates
GROUP BY MinimumStayApplyOn
END
END
/*IF(@@ROWCOUNT = 0)
BEGIN
SELECT @li_TempMinStay = MinimumStay,
@lc_MinimumStayApplyOn = MinimumStayApplyOn
FROM @Rates
WHERE (MinimumStay = (SELECT MAX(MinimumStay)
FROM @Rates
WHERE (@li_NoOfNights BETWEEN ISNULL(FromQty,0) AND ISNULL(ToQty,99))))
IF(@@ROWCOUNT = 0)
BEGIN
SELECT TOP 1 @li_TempMinStay = Max(MinimumStay),
@lc_MinimumStayApplyOn = MinimumStayApplyOn
FROM @Rates
GROUP BY MinimumStayApplyOn
END
END*/
SET @li_MinimumStay = @li_TempMinStay
IF(@lc_MinimumStayApplyOn = 'H')
BEGIN
IF(SELECT COUNT( DISTINCT SHCDataDtlXid) FROM
#SortHCDataDtls
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j) AND
([Sequence] = @li_k)
 
> 1BEGIN
SET @li_OldSHCDataDtlXid = @li_SHCDataDtlXid
WHILE (1 = 1)
BEGIN
SELECT TOP 1 @li_SHCDataDtlXid = SHCDataDtlXid,
@lv_SellRateString = SellRateString
FROM #SortHCDataDtls
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j) AND
([Sequence] = @li_k) AND
(SHCDataDtlXid > @li_OldSHCDataDtlXid)
ORDER BY Pid
IF(@@ROWCOUNT <> 0)
BEGIN
DELETE FROM @Rates
-- Combination Rule String
SELECT @li_StringPos = PATINDEX ('%|%', @lv_SellRateString)
SET @lv_SellRateString = SUBSTRING (@lv_SellRateString, 1, (@li_StringPos - 1))
WHILE (LEN(@lv_SellRateString) > 0)
BEGIN
SET @li_StringPos = PATINDEX ('%^%' , @lv_SellRateString)
IF @li_StringPos > 0
BEGIN
SET @lv_TempString = SUBSTRING (@lv_SellRateString, 1, @li_StringPos - 1)
SET @lv_SellRateString = SUBSTRING (@lv_SellRateString, @li_StringPos + 1, LEN(@lv_SellRateString))
END
ELSE
BEGIN
SET @lv_TempString = @lv_SellRateString
SET @lv_SellRateString = ''
END
-- Rate String
SELECT @li_StringPos = PATINDEX ('%*%', @lv_TempString)
SET @lv_XMLString = SUBSTRING (@lv_TempString, 1, (@li_StringPos - 1))
INSERT INTO @Rates
SELECT ChargeTypeXid,AllocationYN,TypeOfQty,FromQty,
ToQty,MinimumStay,MinimumStayApplyOn,Rate,ExtraNightRate
FROM UF_IOLParseRates(@lv_XMLString)
END
SELECT @li_TempMinStay = MAX(MinimumStay)
FROM @Rates
IF(@li_TempMinStay < @li_MinimumStay)
BEGIN
SET @li_MinimumStay = @li_TempMinStay
END
SET @li_OldSHCDataDtlXid = @li_SHCDataDtlXid
END
ELSE
BEGIN
BREAK
END
END
END
END
/* CHECK FOR MIN STAY */
SET @ld_BaseDate = @ld_FromDate
SET @li_OldSHCDataDtlXid = -1
WHILE (@ld_BaseDate < @ld_ToDate) -- For Each Day
BEGIN
SELECT @li_SortHCDataDtlsXid = Pid,
@li_SHCDataDtlXid = SHCDataDtlXid,
@lv_SellRateString = SellRateString,
@li_CurrencyXid = CurrencyXid
FROM #SortHCDataDtls
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j) AND
([Sequence] = @li_k) AND
(Dated = @ld_BaseDate)
IF(@@ROWCOUNT = 0) -- May be Case If no contract found, don't think will come this case
BEGIN
PRINT 'ERROR'
END
IF(@li_SHCDataDtlXid <> @li_OldSHCDataDtlXid)
BEGIN
DELETE FROM @CombinationRule
DELETE FROM @AgePolicy
DELETE FROM @TimePolicy
DELETE FROM @Rates
-- Combination Rule String
SELECT @li_StringPos = PATINDEX ('%|%', @lv_SellRateString)
SET @lv_TempString = SUBSTRING (@lv_SellRateString, (@li_StringPos + 1) , LEN (@lv_SellRateString))
SET @lv_SellRateString = SUBSTRING (@lv_SellRateString, 1, (@li_StringPos - 1))
INSERT INTO @CombinationRule
SELECT * FROM UF_IOLParseCombinationRule(@lv_TempString)
WHILE (LEN(@lv_SellRateString) > 0)
BEGIN
SET @li_StringPos = PATINDEX ('%^%' , @lv_SellRateString)
IF @li_StringPos > 0
BEGIN
SET @lv_TempString = SUBSTRING (@lv_SellRateString, 1, @li_StringPos - 1)
SET @lv_SellRateString = SUBSTRING (@lv_SellRateString, @li_StringPos + 1, LEN(@lv_SellRateString))
END
ELSE
BEGIN
SET @lv_TempString = @lv_SellRateString
SET @lv_SellRateString = ''
END
-- Rate String
SELECT @li_StringPos = PATINDEX ('%*%', @lv_TempString)
SET @lv_XMLString = SUBSTRING (@lv_TempString, 1, (@li_StringPos - 1))
SET @lv_TempString = SUBSTRING (@lv_TempString, (@li_StringPos + 1) , LEN (@lv_TempString))
INSERT INTO @Rates
SELECT ChargeTypeXid,AllocationYN,TypeOfQty,FromQty,
ToQty,MinimumStay,MinimumStayApplyOn,Rate,ExtraNightRate
FROM UF_IOLParseRates(@lv_XMLString)
SELECT @li_RateXid = MAX(Pid)
FROM @Rates
-- Age Policy String
SELECT @li_StringPos = PATINDEX ('%*%', @lv_TempString)
SET @lv_XMLString = SUBSTRING (@lv_TempString, 1, (@li_StringPos - 1))
SET @lv_TempString = SUBSTRING (@lv_TempString, (@li_StringPos + 1) , LEN (@lv_TempString))
INSERT INTO @AgePolicy
SELECT * FROM UF_IOLParseAgePolicy(@lv_XMLString, @li_RateXid)
-- Time Policy String
INSERT INTO @TimePolicy
SELECT * FROM UF_IOLParseTimePolicy(@lv_TempString, @li_RateXid)
END
SET @li_OldSHCDataDtlXid = @li_SHCDataDtlXid
END
SELECT @li_RConfigDtlsXid = MIN(Pid)
FROM @RoomChargeTypes
WHERE (RoomConfiguratrionXid = @li_j)
SET @li_RConfigDtlsXid = @li_RConfigDtlsXid - 1
SET @li_l = 1
SELECT @li_CombinationRuleCnt = MAX(Pid) FROM @CombinationRule
WHILE (@li_l <= @li_CombinationRuleCnt) -- For Each Combination Rule
BEGIN
SELECT @li_ChargeTypeXid = ISNULL(ChargeTypeXid, -1),
@li_Qty = Qty,
@li_ChildAgeFrom = ChildAgeFrom,
@li_ChildAgeTo = ChildAgeTo,
@lm_Value = Value,
@lc_PercOrAmt = PercOrAmt
FROM @CombinationRule
WHERE (Pid = @li_l)
SELECT @li_OriginalChargeTypeXid = ChargeTypeXid,
@li_Age = Age
FROM @RoomChargeTypes
WHERE (Pid = @li_RConfigDtlsXid + @li_l)
IF(@li_ChargeTypeXid = -1)
BEGIN
SET @li_OriginalChargeTypeXid = -1
END
/* Pick Up Rate */
SELECT @li_RateXid = Pid,
@lm_DateRate = Rate,
@lm_ExtraNightRate = ExtraNightRate,
@li_ToQty = ToQty,
@lc_TypeOFQty = TypeOfQty
FROM @Rates
WHERE ((ChargeTypeXid = @li_ChargeTypeXid) OR (@li_ChargeTypeXid = -1)) AND
((AllocationYN = 'B') OR (AllocationYN = 'Y')) AND
(
((TypeOfQty = 'U') AND
(@li_Qty BETWEEN FromQty AND ToQty)) OR
(((TypeOfQty = 'N') AND
(@li_NoOfNights BETWEEN FromQty AND ToQty AND ExtraNightRate = 0 )) OR
((TypeOfQty = 'N') AND
(@li_NoOfNights >= ToQty AND ExtraNightRate > 0 ))) OR
((TypeOfQty = 'A') AND
(@li_NoOfAdult BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'C') AND
(@li_NoOfChildren BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'P') AND
(@li_NoOfPax BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'T') AND
(@li_Qty BETWEEN FromQty AND ToQty)) OR
((TypeOfQty IS NULL) AND
(FromQty IS NULL) AND (ToQty IS NULL))
 
AND(MinimumStay = @li_MinimumStay)
IF(@@ROWCOUNT = 0)
BEGIN
SELECT @li_RateXid = Pid,
@lm_DateRate = Rate,
@lm_ExtraNightRate = ExtraNightRate,
@li_ToQty = ToQty,
@lc_TypeOFQty = TypeOfQty
FROM @Rates
WHERE ((ChargeTypeXid = @li_ChargeTypeXid) OR (@li_ChargeTypeXid = -1)) AND
((AllocationYN = 'B') OR (AllocationYN = 'N')) AND
(
((TypeOfQty = 'U') AND
(@li_Qty BETWEEN FromQty AND ToQty)) OR
(((TypeOfQty = 'N') AND
(@li_NoOfNights BETWEEN FromQty AND ToQty AND ExtraNightRate = 0 )) OR
((TypeOfQty = 'N') AND
(@li_NoOfNights >= ToQty AND ExtraNightRate > 0 ))) OR
((TypeOfQty = 'A') AND
(@li_NoOfAdult BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'C') AND
(@li_NoOfChildren BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'P') AND
(@li_NoOfPax BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'T') AND
(@li_Qty BETWEEN FromQty AND ToQty)) OR
((TypeOfQty IS NULL) AND
(FromQty IS NULL) AND (ToQty IS NULL))
 
AND(MinimumStay = (SELECT MAX(MinimumStay)
FROM @Rates
WHERE (MinimumStay < @li_MinimumStay)))
IF(@@ROWCOUNT = 0)
BEGIN
SELECT TOP 1 @li_RateXid = Pid,
@lm_DateRate = Rate,
@lm_ExtraNightRate = ExtraNightRate,
@li_ToQty = ToQty,
@lc_TypeOFQty = TypeOfQty
FROM @Rates
WHERE ((ChargeTypeXid = @li_ChargeTypeXid) OR (@li_ChargeTypeXid = -1)) AND
((AllocationYN = 'B') OR (AllocationYN = 'N')) AND
(
((TypeOfQty = 'U') AND
(@li_Qty BETWEEN FromQty AND ToQty)) OR
(((TypeOfQty = 'N') AND
(@li_NoOfNights BETWEEN FromQty AND ToQty AND ExtraNightRate = 0 )) OR
((TypeOfQty = 'N') AND
(@li_NoOfNights >= ToQty AND ExtraNightRate > 0 ))) OR
((TypeOfQty = 'A') AND
(@li_NoOfAdult BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'C') AND
(@li_NoOfChildren BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'P') AND
(@li_NoOfPax BETWEEN FromQty AND ToQty)) OR
((TypeOfQty = 'T') AND
(@li_Qty BETWEEN FromQty AND ToQty)) OR
((TypeOfQty IS NULL) AND
(FromQty IS NULL) AND (ToQty IS NULL))
 
IF (@@ROWCOUNT = 0) OR (@lm_DateRate IS NULL)
BEGIN
UPDATE #SortHCDataDtls
SET Status = -2
WHERE ([Sequence] = @li_k) AND
(HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j)
GOTO NEXT_CONTRACT
END
END
END
/* Pick Up Rate */
/* Age Policy */
IF ((SELECT COUNT(*) FROM @AgePolicy WHERE RateXid = @li_RateXid) > 0 ) AND
((@li_ChildChargeTypeXid = @li_ChargeTypeXid) OR (@li_ExtraChildChargeTypeXid = @li_ChargeTypeXid))
BEGIN
IF(@li_Age BETWEEN ISNULL(@li_ChildAgeFrom, @li_Age) AND ISNULL(@li_ChildAgeTo, @li_Age))
BEGIN
SET @lm_RateDifference = NULL
SELECT @lm_RateDifference = RateDifference
FROM @AgePolicy
WHERE (@li_Age BETWEEN AgeFrom AND AgeTo) AND
(RateXid = @li_RateXid)
IF @@ROWCOUNT = 0
BEGIN
UPDATE #SortHCDataDtls
SET Status = -3
WHERE ([Sequence] = @li_k) AND
(HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j)
GOTO NEXT_CONTRACT
END
END
ELSE
BEGIN
UPDATE #SortHCDataDtls
SET Status = -3
WHERE ([Sequence] = @li_k) AND
(HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j)
GOTO NEXT_CONTRACT
END
SET @lm_DateRate = @lm_DateRate + @lm_RateDifference
END
/* Age Policy */
/* Time Policy */
IF ((SELECT COUNT(*) FROM @TimePolicy WHERE RateXid = @li_RateXid) > 0 )
BEGIN
SET @lm_RateDifference = NULL
SELECT @lm_RateDifference = RateDifference
FROM @TimePolicy
WHERE (CONVERT(DateTime, CONVERT(VARCHAR(5), GETDATE(), 108)) BETWEEN TimeFrom AND TimeTo) AND
(RateXid = @li_RateXid)
IF @@ROWCOUNT = 0
BEGIN
UPDATE #SortHCDataDtls
SET Status = -4
WHERE ([Sequence] = @li_k) AND
(HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j)
GOTO NEXT_CONTRACT
END
SET @lm_DateRate = @lm_DateRate + @lm_RateDifference
END
/* Time Policy */
/* Apply Combination Rule */
IF @lc_PercOrAmt = 'P'
BEGIN
SET @lm_DateRate = @lm_DateRate + (@lm_Value*@lm_DateRate)
END
ELSE
BEGIN
SET @lm_DateRate = @lm_DateRate + @lm_Value
END
/* Apply Combination Rule */
/* Rate Conversions */
SET @lm_DateRate = dbo.UF_ReturnConvertedRate (@li_CurrencyXid, @li_BaseCurrencyXid, @lm_DateRate)
IF(@lm_DateRate IS NULL)
BEGIN
UPDATE #SortHCDataDtls
SET Status = -5
WHERE ([Sequence] = @li_k) AND
(HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j)
GOTO NEXT_CONTRACT
END
/* Rate Conversions */
/* Extra Night Rate Check */
IF (@lv_BaseContractType LIKE '%PH')
BEGIN
IF @li_NoOfNights > @li_ToQty AND @lc_TypeOfQty = 'N' AND @lm_ExtraNightRate > 0
BEGIN
SET @lm_DateRate = @lm_DateRate + ((@li_NoOfNights-@li_ToQty)*@lm_ExtraNightRate)
END
END
ELSE
BEGIN
IF DATEDIFF(dd,@ld_FromDate,@ld_BaseDate) > @li_ToQty - 1 AND @lc_TypeOfQty = 'N' AND @lm_ExtraNightRate > 0
BEGIN
SET @lm_DateRate = @lm_ExtraNightRate
END
END
/* Extra Night Rate Check */
/* INSERT RATE */
INSERT INTO #TempRates(SortHCDataDtlsXid, CurrencyXid, RConfigDtlsXid, ChargeTypeXid, Rate, MinimumStay, MinimumStayApplyOn,ExtraNightRate)
VALUES(@li_SortHCDataDtlsXid, @li_BaseCurrencyXid, (@li_RConfigDtlsXid + @li_l) , @li_OriginalChargeTypeXid, @lm_DateRate , @li_MinimumStay, @lc_MinimumStayApplyOn,@lm_ExtraNightRate)
/* INSERT RATE */
SET @li_l = @li_l + 1
END
IF (@lv_BaseContractType LIKE '%PH')
BEGIN
DELETE FROM #SortHCDataDtls
WHERE ([Sequence] = @li_k) AND
(HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_j) AND
(Pid <> @li_SortHCDataDtlsXid)
GOTO NEXT_CONTRACT
END
SET @ld_BaseDate = DATEADD(dd, 1, @ld_BaseDate)
END
NEXT_CONTRACT:
SET @li_k = @li_k + 1
END
NEXT_ROOMCONFIGURATION:
SET @li_j = @li_j + 1
END
NEXT_ROOMTYPE:
SET @li_i = @li_i + 1
END
/* CALCULATE RATE */
/* SUPPLEMENT */
SET @lv_XMLString = NULL
SELECT @lv_XMLString =
ISNULL(
@lv_XMLString + '!' +
CONVERT(VARCHAR(10), HotelXid) + '!' +
CONVERT(VARCHAR(10), RoomTypeXid) + '!' +
CONVERT(VARCHAR(2), RoomConfigurationXid) + '!' +
CONVERT(VARCHAR(2), [Sequence])+ '!' +
CONVERT(VARCHAR(10), HolidayContractXid) + '!' +
CONVERT(VARCHAR(10), #TempRates.CurrencyXid) + '!' +
CONVERT(VARCHAR(10), MIN(Dated), 121) + '!' +
CONVERT(VARCHAR(10), MAX(Dated), 121),
CONVERT(VARCHAR(10), HotelXid) + '!' +
CONVERT(VARCHAR(10), RoomTypeXid) + '!' +
CONVERT(VARCHAR(2), RoomConfigurationXid) + '!' +
CONVERT(VARCHAR(2), [Sequence])+ '!' +
CONVERT(VARCHAR(10), HolidayContractXid) + '!' +
CONVERT(VARCHAR(10), #TempRates.CurrencyXid) + '!' +
CONVERT(VARCHAR(10), MIN(Dated), 121) + '!' +
CONVERT(VARCHAR(10), MAX(Dated), 121)
 
FROM #SortHCDataDtls INNER JOIN #TempRates
ON #TempRates.SortHCDataDtlsXid = #SortHCDataDtls.Pid
WHERE (#SortHCDataDtls.HolidayContractXid IN ( SELECT DISTINCT HolidayContractXid
FROM #SortHCDataDtls INNER JOIN M_HolidayContractSupplements
ON M_HolidayContractSupplements.EntityXid = #SortHCDataDtls.HolidayContractXid AND
M_HolidayContractSupplements.EntityType = 'N'
WHERE (Dated BETWEEN FromDate AND ToDate) AND
(Status = 1)))
GROUP BY HotelXid, RoomTypeXid, RoomConfigurationXid, [Sequence], HolidayContractXid, #TempRates.CurrencyXid
ORDER BY HotelXid, RoomTypeXid, RoomConfigurationXid, [Sequence]
--SELECT @lv_XMLString, @lv_RoomConfiguratrion
IF(@lv_XMLString IS NOT NULL)
BEGIN
SET @ld_SuppToDate = DATEADD(dd,-1,@ld_ToDate)
INSERT INTO #SupplementData
EXEC IOL_CalculateSupplementSellRate @lv_XMLString, @lv_RoomConfiguratrion,@ld_FromDate,@ld_SuppToDate
END
UPDATE #SortHCDataDtls
SET Status = -6
WHERE (HolidayContractXid IN (SELECT HolidayContractXid FROM #SupplementData
WHERE (Status = -1))) AND
(RoomTypeXid IN (SELECT RoomTypeXid FROM #SupplementData
WHERE (Status = -1))) AND
(RoomConfigurationXid IN (SELECT RoomConfigurationXid FROM #SupplementData
WHERE (Status = -1)))
/* SUPPLEMENT */
/* DISCOUNT */
SET @lv_XMLString = NULL
SELECT @lv_XMLString =
ISNULL(
@lv_XMLString + '!' +
CONVERT(VARCHAR(10), HotelXid) + '!' +
CONVERT(VARCHAR(10), RoomTypeXid) + '!' +
CONVERT(VARCHAR(2), RoomConfigurationXid) + '!' +
CONVERT(VARCHAR(1), [Sequence])+ '!' +
CONVERT(VARCHAR(10), HolidayContractXid) ,
CONVERT(VARCHAR(10), HotelXid) + '!' +
CONVERT(VARCHAR(10), RoomTypeXid) + '!' +
CONVERT(VARCHAR(2), RoomConfigurationXid) + '!' +
CONVERT(VARCHAR(1), [Sequence])+ '!' +
CONVERT(VARCHAR(10), HolidayContractXid)
 
FROM #SortHCDataDtls
WHERE (Pid IN ( SELECT DISTINCT #SortHCDataDtls.Pid
FROM #SortHCDataDtls INNER JOIN M_HolidayDiscounts
ON M_HolidayDiscounts.EntityXid = #SortHCDataDtls.HolidayContractXid AND
M_HolidayDiscounts.EntityType = 'N'
WHERE (#SortHCDataDtls.Dated = @ld_FromDate) AND
(#SortHCDataDtls.Status = 1) AND
(@ld_FromDate BETWEEN ISNULL(M_HolidayDiscounts.TravelFromDate, @ld_FromDate) AND ISNULL(M_HolidayDiscounts.TravelToDate, @ld_ToDate)) AND
(@ld_ToDate BETWEEN ISNULL(M_HolidayDiscounts.TravelFromDate, @ld_FromDate) AND ISNULL(M_HolidayDiscounts.TravelToDate, @ld_ToDate)) AND
(GetDate() BETWEEN ISNULL(M_HolidayDiscounts.BookingFromDate, GetDate()) AND ISNULL(M_HolidayDiscounts.BookingToDate, GetDate())) AND
(GetDate() BETWEEN M_HolidayDiscounts.FromDate AND M_HolidayDiscounts.ToDate)
 
)IF(@lv_XMLString IS NOT NULL)
BEGIN
INSERT INTO #Discounts
EXEC IOL_CalculateDiscount @lv_XMLString, @lv_RoomConfiguratrion, @ld_FromDate, @ld_ToDate
END
/* DISCOUNT */
INSERT INTO #FinalRates
SELECT HotelXid, RoomTypeXid, RoomConfigurationXid, [Sequence], HolidayContractXid,
#TempRates.CurrencyXid, Dated, RConfigDtlsXid, ChargeTypeXid, Rate , MinimumStay, MinimumStayApplyOn, ExtraNightRate, #SortHCDataDtls.ContractType
FROM #SortHCDataDtls INNER JOIN #TempRates
ON #TempRates.SortHCDataDtlsXid = #SortHCDataDtls.Pid
WHERE (#SortHCDataDtls.Status = 1)
ORDER BY HotelXid, RoomTypeXid, RoomConfigurationXid, [Sequence], RConfigDtlsXid, ChargeTypeXid, Dated
DROP TABLE #SortHCDataDtls
SET @li_OldHotelXid = -1
SET @li_OldRoomTypeXid = -1
SET @li_OldRoomConfigurationXid = -1
SET @li_OldSequence = -1
SET @li_k = 0
DECLARE C_XMLString CURSOR FOR
SELECT DISTINCT HotelXid, RoomTypeXid, RoomConfigurationXid, [Sequence], ContractType
FROM #FinalRates ORDER BY HotelXid
OPEN C_XMLString
FETCH C_XMLString INTO @li_HotelXid, @li_RoomTypeXid, @li_RoomConfigurationXid, @li_Sequence, @lv_ContractType
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @lc_ClosePaxConfigTag = 'N'
SET @lc_ClosePaxRoomTypeTag = 'N'
SET @lc_ClosePaxRootTag = 'N'
IF(@li_HotelXid <> @li_OldHotelXid)
BEGIN
SELECT @li_SupplierXid = SupplierXid,
@li_ChildAgeFrom = ChildAgeFrom,
@li_ChildAgeTo = ChildAgeTo
FROM @HotelRoomTypes
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid)
SET @lv_XMLString = '<Root>'
SET @li_OldHotelXid = @li_HotelXid
END
IF(@li_RoomTypeXid <> @li_OldRoomTypeXid)
BEGIN
SELECT @li_QuickBedHotelRoomTypeXid = ISNULL(QuickBedHotelRoomTypeXid, -1)
FROM M_HotelRoomType
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid)
SET @lv_XMLString = @lv_XMLString + '<RT X="' + CONVERT(VARCHAR(20), @li_RoomTypeXid) + '" Q="' + CONVERT(VARCHAR(20), @li_QuickBedHotelRoomTypeXid) + '">'
SET @li_OldRoomTypeXid = @li_RoomTypeXid
END
IF(@li_RoomConfigurationXid <> @li_OldRoomConfigurationXid)
BEGIN
SELECT @li_NoOfPax = COUNT(*)
FROM @RoomChargeTypes
WHERE (RoomConfiguratrionXid = @li_RoomConfigurationXid)
SELECT @li_NoOfChildren = COUNT(ChargeTypeXid)
FROM @RoomChargeTypes
WHERE (RoomConfiguratrionXid = @li_RoomConfigurationXid) AND
(Age <= ISNULL(@li_ChildAgeTo, Age)) AND
(Age <> 25)
SET @li_NoOfAdult = @li_NoOfPax - @li_NoOfChildren
SET @lv_XMLString = @lv_XMLString + '<PC N="' + CONVERT(VARCHAR(2), @li_RoomConfigurationXid) + '"><A>' + CONVERT(VARCHAR(2), @li_NoOfAdult) + '</A><C>' + CONVERT(VARCHAR(2), @li_NoOfChildren) + '</C>'
IF(@li_NoOfChildren > 0)
BEGIN
SET @lv_TempString = NULL
SELECT @lv_TempString =
ISNULL(
@lv_TempString +
'<G>' + CONVERT(VARCHAR(3), Age) + '</G>',
'<G>' + CONVERT(VARCHAR(3), Age) + '</G>'
 
FROM @RoomChargeTypes
WHERE (RoomConfiguratrionXid = @li_RoomConfigurationXid) AND
((ChargeTypeXid = @li_ChildChargeTypeXid) OR
(ChargeTypeXid = @li_ExtraChildChargeTypeXid))
SET @lv_XMLString = @lv_XMLString + '<CA>' + @lv_TempString + '</CA>'
END
SET @li_OldRoomConfigurationXid = @li_RoomConfigurationXid
END
IF(@li_OldSequence <> @li_Sequence)
BEGIN
SET @li_k = @li_k + 1
SET @lv_XMLString = @lv_XMLString + '<SQ N="' + CONVERT(VARCHAR(2), @li_k) + '">'
END
SET @li_OldRConfigDtlsXid = 0
SET @li_NoOfNights = 0
SET @lm_Value = 0
SET @lm_RoomRate = 0
SELECT @li_l = MIN(Pid), @li_MaxXid = MAX(Pid)
FROM #FinalRates
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence)
WHILE(@li_l <= @li_MaxXid)
BEGIN
SELECT @li_HolidayContractXid = #FinalRates.HolidayContractXid,
@li_CurrencyXid = #FinalRates.CurrencyXid,
@ld_BaseDate = #FinalRates.Dated,
@li_RConfigDtlsXid = #FinalRates.RConfigDtlsXid,
@li_ChargeTypeXid = #FinalRates.ChargeTypeXid,
@lm_DateRate = #FinalRates.Rate,
@li_MinimumStay = #FinalRates.MinimumStay,
@lc_MinimumStayApplyOn = #FinalRates.MinimumStayApplyOn,
@lm_ExtraNightRate = #FinalRates.ExtraNightRate
FROM #FinalRates
WHERE (#FinalRates.Pid = @li_l)
IF(@li_OldRConfigDtlsXid <> @li_RConfigDtlsXid)
BEGIN
SET @lv_XMLString = @lv_XMLString + '<CT X="' + CONVERT(VARCHAR(20), @li_ChargeTypeXid) + '">'
SET @li_OldRConfigDtlsXid = @li_RConfigDtlsXid
END
IF(@ld_BaseDate = @ld_FromDate)
BEGIN
SET @lv_XMLString = @lv_XMLString + '<HC X="' + CONVERT(VARCHAR(20), @li_HolidayContractXid) + '" CX="' + CONVERT(VARCHAR(10), @li_CurrencyXid) + '">'
SET @ld_StartDate = @ld_BaseDate
END
SET @lm_Value = @lm_Value + @lm_DateRate
SET @lm_RoomRate = @lm_RoomRate + @lm_DateRate
SET @li_l = @li_l + 1
IF(SELECT COUNT(*)
FROM #FinalRates
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence) AND
(HolidayContractXid = @li_HolidayContractXid) AND
(RConfigDtlsXid = @li_RConfigDtlsXid) AND
(ChargeTypeXid = @li_ChargeTypeXid) AND
(Pid = @li_l)) = 0
BEGIN
/* Add Mandatory Supplement Rate if there */
IF(@li_ChargeTypeXid = -1)
BEGIN
SELECT @lm_SupplementRate = ISNULL(SUM(SellRate * Qty * NoOfNights), 0)
FROM #SupplementData
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence) AND
(HolidayContractXid = @li_HolidayContractXid) AND
(PayableBy = 'M') AND
(Mandatory = 'Y') AND
(Status = 1)
SET @lm_Value = @lm_Value + ISNULL(@lm_SupplementRate, 0)
END
ELSE
BEGIN
-- Age Policy Supplement
SELECT @lm_SupplementRate = ISNULL(SUM(SellRate * NoOfNights), 0)
FROM #SupplementData
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence) AND
(HolidayContractXid = @li_HolidayContractXid) AND
(ChargeTypeXid = @li_ChargeTypeXid) AND
(RConfigDtlsXid = @li_RConfigDtlsXid) AND
(PayableBy = 'M') AND
(Mandatory = 'Y') AND
(Status = 1)
-- Not Age Policy But Supplement is PA, PC
SELECT @lm_SupplementRate = @lm_SupplementRate + ISNULL(SUM(SellRate * NoOfNights), 0)
FROM #SupplementData
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence) AND
(HolidayContractXid = @li_HolidayContractXid) AND
(ChargeTypeXid = @li_ChargeTypeXid) AND
(RConfigDtlsXid IS NULL) AND
(PayableBy = 'M') AND
(Mandatory = 'Y') AND
(Status = 1)
-- Not Age Policy Also Not PA, PC
SELECT @lm_SupplementRate = @lm_SupplementRate + ISNULL(SUM((SellRate * NoOfNights) / @li_NoOfPax), 0)
FROM #SupplementData
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence) AND
(HolidayContractXid = @li_HolidayContractXid) AND
(ChargeTypeXid IS NULL) AND
(RConfigDtlsXid IS NULL) AND
(PayableBy = 'M') AND
(Mandatory = 'Y') AND
(Status = 1)
SET @lm_Value = @lm_Value + ISNULL(@lm_SupplementRate, 0)
END
/* Add Mandatory Supplement Rate if there */
IF (@lv_ContractType LIKE '%PH')
BEGIN
SET @ld_BaseDate = DATEADD(dd,-1,@ld_ToDate)
END
SET @lv_XMLString = @lv_XMLString + '<FD>' + CONVERT(VARCHAR(10), @ld_StartDate, 121) + '</FD>'
SET @lv_XMLString = @lv_XMLString + '<TD>' + CONVERT(VARCHAR(10), @ld_BaseDate, 121) + '</TD>'
SET @lv_XMLString = @lv_XMLString + '<R>' + CONVERT(VARCHAR(10), @lm_Value) + '</R>'
SET @lv_XMLString = @lv_XMLString + '<ST>' + CONVERT(VARCHAR(3), @li_MinimumStay) + '</ST>'
SET @lv_XMLString = @lv_XMLString + '</HC>'
SET @lv_XMLString = @lv_XMLString + '</CT>'
SET @lm_Value = 0
END
/*IF(DATEADD(dd, 1, @ld_BaseDate) = @ld_ToDate)
BEGIN
SET @lv_XMLString = @lv_XMLString + '</CT>'
END*/
SET @ld_BaseDate = NULL
END
/* Built The Supplement String */
SET @lv_XMLString = @lv_XMLString + '<SL>'
DECLARE C_Supplement CURSOR FOR
SELECT DISTINCT HolidayContractXid, HCSupplementXid, SupplementXid
FROM #SupplementData
WHERE(HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence)
OPEN C_Supplement
FETCH C_Supplement INTO @li_HolidayContractXid, @li_HCSupplementXid, @li_SupplementXid
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @lv_XMLString = @lv_XMLString + '<S HCX="' + CONVERT(VARCHAR(10), @li_HCSupplementXid) + '">'
SET @lv_TempString = NULL
SELECT @lv_TempString = ISNULL(
@lv_TempString +
'<SC X="' + CONVERT(VARCHAR(10), ISNULL(ChargeTypeXid, -1)) + '">' +
'<SFD>' + CONVERT(VARCHAR(10), FromDate, 121) + '</SFD>' +
'<STD>' + CONVERT(VARCHAR(10), ToDate, 121) + '</STD>' +
'<Q>' + CONVERT(VARCHAR(3), Qty) + '</Q>' +
'<SR>' + CONVERT(VARCHAR(10), SellRate) + '</SR></SC>' ,
'<SC X="' + CONVERT(VARCHAR(10), ISNULL(ChargeTypeXid, -1)) + '">' +
'<SFD>' + CONVERT(VARCHAR(10), FromDate, 121) + '</SFD>' +
'<STD>' + CONVERT(VARCHAR(10), ToDate, 121) + '</STD>' +
'<Q>' + CONVERT(VARCHAR(3), Qty) + '</Q>' +
'<SR>' + CONVERT(VARCHAR(10), SellRate) + '</SR></SC>')
FROM #SupplementData
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence) AND
(HolidayContractXid = @li_HolidayContractXid) AND
(HCSupplementXid = @li_HCSupplementXid) AND
(SupplementXid = @li_SupplementXid)
SET @lv_XMLString = @lv_XMLString + @lv_TempString + '</S>'
FETCH C_Supplement INTO @li_HolidayContractXid, @li_HCSupplementXid, @li_SupplementXid
END
CLOSE C_Supplement
DEALLOCATE C_Supplement
SET @lv_XMLString = @lv_XMLString + '</SL>'
/* DISCOUNT */
SET @lm_Discount = 0
SELECT @li_HolidayDiscountXid = HolidayDiscountXid,
@lc_PerWhat = PerWhat,
@li_PerWhatQty = PerWhatQty,
@lc_PercOrAmt = PercOrAmt,
@lm_Value = Value
FROM #Discounts
WHERE (HotelXid = @li_HotelXid) AND
(RoomTypeXid = @li_RoomTypeXid) AND
(RoomConfigurationXid = @li_RoomConfigurationXid) AND
([Sequence] = @li_Sequence)
IF(@@ROWCOUNT <> 0)
BEGIN
IF((@lc_PerWhat = 'PN') AND (@li_PerWhatQty IS NOT NULL))
BEGIN
IF(@lc_PercOrAmt = 'P')
BEGIN
SET @li_NoOfNights = DATEDIFF(dd, @ld_FromDate, @ld_ToDate)
SET @lm_Discount = (((@lm_RoomRate * @li_PerWhatQty) / @li_NoOfNights) * @lm_Value ) / 100
END
ELSE
BEGIN
SET @lm_Discount = @lm_Value
END
END
ELSE IF (@lc_PerWhat = 'PB')
BEGIN
IF(@lc_PercOrAmt = 'P')
BEGIN
SET @lm_Discount = (@lm_RoomRate * @lm_Value ) / 100
END
ELSE
BEGIN
SET @lm_Discount = @lm_Value
END
END
END
ELSE
BEGIN
SET @li_HolidayDiscountXid = -1
END
SET @lv_XMLString = @lv_XMLString + '<D X="' + CONVERT(VARCHAR(10), @li_HolidayDiscountXid) + '">' + CONVERT(VARCHAR(10), @lm_Discount) + '</D>'
/* DISCOUNT */
SET @lv_XMLString = @lv_XMLString + '</SQ>'
SET @li_OldSequence = -1
print @lv_XMLString
INSERT INTO HotelUserSelectionRoomString
SELECT @lv_SessionId,@li_HotelXid,@li_RoomTypeXid,@lv_XMLString
SET @lv_XMLString = ''
FETCH C_XMLString INTO @li_HotelXid, @li_RoomTypeXid, @li_RoomConfigurationXid, @li_Sequence, @lv_ContractType
IF(@@FETCH_STATUS <> 0)
BEGIN
SET @lc_ClosePaxConfigTag = 'Y'
SET @lc_ClosePaxRoomTypeTag = 'Y'
SET @lc_ClosePaxRootTag = 'Y'
END
ELSE
BEGIN
IF(@li_HotelXid <> @li_OldHotelXid)
BEGIN
SET @lc_ClosePaxConfigTag = 'Y'
SET @lc_ClosePaxRoomTypeTag = 'Y'
SET @lc_ClosePaxRootTag = 'Y'
SET @li_k = 0
END
ELSE IF(@li_RoomTypeXid <> @li_OldRoomTypeXid)
BEGIN
SET @lc_ClosePaxConfigTag = 'Y'
SET @lc_ClosePaxRoomTypeTag = 'Y'
SET @li_k = 0
END
ELSE IF(@li_RoomConfigurationXid <> @li_OldRoomConfigurationXid)
BEGIN
SET @lc_ClosePaxConfigTag = 'Y'
SET @li_k = 0
END
END
IF(@lc_ClosePaxConfigTag = 'Y')
BEGIN
SET @lv_XMLString = @lv_XMLString + '</PC>'
SET @li_OldRoomConfigurationXid = -1
END
IF(@lc_ClosePaxRoomTypeTag = 'Y')
BEGIN
IF(@li_SupplierXid = @li_QuickBedSupplierXid)
BEGIN
SET @lc_AvailabilityStatus = 'Q'
END
ELSE
BEGIN
IF(SELECT COUNT(*)
FROM #Allocation
WHERE (HotelXid = @li_OldHotelXid) AND
(AllocationOnXid = @li_OldRoomTypeXid) AND
(RoomStatus = 'C')) > 0
BEGIN
SET @lc_AvailabilityStatus = 'C'
END
ELSE IF (SELECT COUNT(*)
FROM #Allocation
WHERE (HotelXid = @li_OldHotelXid) AND
(AllocationOnXid = @li_OldRoomTypeXid) AND
(RoomStatus = 'R')) > 0
BEGIN
SET @lc_AvailabilityStatus = 'R'
END
ELSE IF (SELECT COUNT(*)
FROM #Allocation
WHERE (HotelXid = @li_OldHotelXid) AND
(AllocationOnXid = @li_OldRoomTypeXid) AND
(RoomStatus = 'A')) > 0
BEGIN
SET @lc_AvailabilityStatus = 'A'
END
END
SET @lv_XMLString = @lv_XMLString + '<AS>' + @lc_AvailabilityStatus + '</AS>'
SET @lv_XMLString = @lv_XMLString + '</RT>'
SET @li_OldRoomTypeXid = -1
END
IF(@lc_ClosePaxRootTag = 'Y')
BEGIN
SELECT @lv_LastString = RoomString
FROM
HotelUserSelectionRoomString
WHERE Pid = (SELECT Max(Pid) FROM HotelUserSelectionRoomString WHERE UserSessionId = @lv_SessionId)
UPDATE HotelUserSelectionRoomString
SET RoomString = @lv_LastString + @lv_XMLString + '</Root>'
WHERE Pid = (SELECT Max(Pid) FROM HotelUserSelectionRoomString WHERE UserSessionId = @lv_SessionId)
--Allocation
SET @lv_TempString = NULL
SELECT @lv_TempString = ISNULL(
@lv_TempString + ',' +
--CONVERT(VARCHAR(10), ISNULL(AllocationXid,0)) + '|' +
FreeSaleYN + '|' +
CONVERT(VARCHAR(10), AllocationDate, 121) + '|' +
CONVERT(VARCHAR(10), AllocationOnXid) + '|' +
CONVERT(VARCHAR(3), AllocationQty)+ '|' +
RoomStatus,
--CONVERT(VARCHAR(10), ISNULL(AllocationXid,0)) + '|' +
FreeSaleYN + '|' +
CONVERT(VARCHAR(10), AllocationDate, 121) + '|' +
CONVERT(VARCHAR(10), AllocationOnXid) + '|' +
CONVERT(VARCHAR(3), AllocationQty)+ '|' +
RoomStatus)
FROM #Allocation
WHERE (HotelXid = @li_OldHotelXid) ORDER BY AllocationOnXid, AllocationDate
INSERT INTO HotelUserSelection(
UserSessionId, HotelXid, HotelCode, HotelName, HotelPreferredStatus, PropertyType,
Rating, HotelChain, Supplier, SupplierXid, Distance, Country, CountryXid, County, CountyXid,
City, CityXid, Zone, ZoneXid, RateString, AllocationString, LastEditByXid, CompanyXid)
SELECT @lv_SessionID, @li_OldHotelXid, M_Hotel.Code, M_Hotel.Hotel, M_Hotel.PreferredStatus, M_PropertyType.PropertyType,
NULL, M_HotelChain.HotelChains, M_Supplier.Supplier, M_Supplier.Pid,M_Hotel.DistanceFrom, M_Country.Country, M_Country.Pid, M_County.County, M_County.Pid,
M_City.City, M_City.Pid, M_Zone.Zone, M_Zone.Pid, NULL, @lv_TempString, @li_LastEditByXid, @li_CompanyXid
FROM M_Hotel LEFT JOIN M_PropertyType ON M_PropertyType.Pid = M_Hotel.PropertyTypeXid
LEFT JOIN M_HotelChain ON M_HotelChain.Pid = M_Hotel.HotelChainXid
LEFT JOIN M_Supplier ON M_Supplier.Pid = M_Hotel.SupplierXid
INNER JOIN M_Country ON M_Country.PId = M_Hotel.CountryXid
INNER JOIN M_City ON M_City.Pid = M_Hotel.CityXid
LEFT JOIN M_County ON M_County.Pid = M_Hotel.CountyXid
LEFT JOIN M_Zone ON M_Zone.Pid = M_Hotel.ZoneXid
WHERE (M_Hotel.Pid = @li_OldHotelXid)
SELECT @li_MaxXid = MAX(Pid) FROM HotelUserSelection WHERE UserSessionId = @lv_SessionID
--Rating
SET @lv_TempString = NULL
SELECT @lv_TempString = ISNULL(
@lv_TempString + ',' +
Rating,
Rating
 
FROM M_HotelRatings INNER JOIN M_Ratings ON M_Ratings.Pid = M_HotelRatings.RatingXid
WHERE (M_HotelRatings.HotelXid = @li_OldHotelXid)
UPDATE HotelUserSelection
SET Rating = @lv_TempString
WHERE Pid = @li_MaxXid
SET @lv_XMLString = NULL
END
END
CLOSE C_XMLString
DEALLOCATE C_XMLString
-
Good Lord!!!!!!!Well, make sure your first statement in the procedure is SET NOCOUNT ON, which should speed the thing up.
I would definitely break this monster down a bit.
If nothing else it would make the procedure easier to maintain, plus identifying the various parts triggering the recompile will be easier.
I would have a specific sub proc to generate the temporary tables.
Your IF @variable IS NULL statements could be rolled up into a single select
SELECT @variable1=ISNULL(@variable1,value) , @variable2=ISNULL(@variable2,value)...etc
Don't use @ROWCOUNT<>0, use @ROWCOUNT>0, it is faster.
Apart from that this query looks like something that could be submitted to the site as a competition.
-
umm.. What kind of hardware are you performing this stree test on?? My computer looked at it and promptly turned itself off. I would look at changing the TABLE variables to temp tables and possibly change these to real tables and index them.
I def. agree with David and think you can break this out into SEVERAL smaller sp's.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Just as an fyi, everytime you're going to get ALL KINDS of recompiles on this. You should put all of your CREATE TABLE (which is different then DECLARE @table TABLE) first. You need to eliminate TONS of your IF..THEN logic which can also cause recompiles.
You then need to break up every major piece into one or more subprocedures so the execution plan is the exact same everytime. Basically, just use the main procedure as a "wrapper" that looks at your variables. Once you've figured out what you need to do, execute a small subprocedure that does the exact same thing everytime so it's extremely optimized and never has to recompile. You can also put a lot of this into functions, which means when it hits it inside of an IF it doesn't recompile at that point like a regular procedure will.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply