CREATE PROCEDURE dbo.GenerateAvailability @StartDate datetime, @EndDate datetime, @OnlyAllotment bit, @TheHotelNumber VARCHAR(100), @RealSystemDateStr CHAR (8) = '', @SpecifyOnRoomTypeSegPos BIT = 1, @NightAudit BIT = 0, @ARoomTypeStr VARCHAR(100) = '', @ASoldRoomType INT = 2, @AIncludeOccupiedInOFS BIT = 0, @CalculateOccupied BIT = 1, @AUpdateRoomBookingStatus BIT = 0, @ARegenerateRoomBookingStatus BIT = 0 AS
SET NOCOUNT ON
/*
    CREATED BY JACOB PEDERSEN along time ago
   4.3:
   BA/20130925: #ProcessedRoomLines created a constrained primary key. All constraints must be unique
                in the database. Therefore the primary key is now a name less key so MSSQL assigns it
                a name it self.
   KC/20130920: Fixing error regarding indexes on #ProcessedRoomLines
   KC/20120305:	Including count of owner bookings.
   BA/20110902: CR033070: Creating a reservation on Hotel A with an Allotment, but having MixedResources
                enabled and booking the Rooms on the Roomline fra Hotel B makes the SoldRooms column
                on the RoomTypes plan fail. The SoldRooms goes to Hotel A and not Hotel B as it should.
   BA/20100928: CR016840: Some scenarious can make it look like that a Double Booking has occured. It 
                can be done by moving an Inhouse reservation to an room where a guest has just left. 
                Therefore only activate the Double Booking Calculations if it is activated in the
                HotelSetup.
   BA/20100518/ DoubleBooking/Do Count multiple res.no. on same rooms as one Sold Room!
      20100519: We must only count SoldRooms pr. room even if two or more ReferenceNumbers exists 
                in the room, but all other Fields in the ROOMBOOKINGSTATUS table must be counted as
                it used to be done. This can lead to a mismatch between adding NumberOfConfirmed,
                NumberOfProvisionals, NumberOfGuaranteed, etc. and SoldRooms.
                Also we must include ArrivalTime and DepartureTime due to the existence of dayrooms. 
                We cannot do calculate SoldRooms enterily on ArrivalDate and DepartureDate.
   3.39 - 4.2:
   KJ 20090901: Disable save availability for now
   BA 20090609: CR008736/Reenabled due to a lot of negativ allotment all over the system
   KJ 20090518: Added save availability in table Availability as part of nightaudit. 
   AV 2008 1030 dont say = null, say is null. Else sql will fail
   4.1; 
   BA 20090401: CR007579: Try to aviod Lock time out when opening the Roomtypes plan
   BA 20090317: CR006600/Roomtypes plan calculated Insale price wrong. The Insale price (Sold rooms) only showed the price for one room and not for all rooms.
   3.39: 
   JP 20070807: BUG: When CopySave to a new reservation allotment gets modified eventhough you tell it not to because of this code. Let's find the problems in the source instead of patching our code in this way. Unclear if the following line is a good idea (SET @TotalChange = @InitialCount - @CurrentCount)
   JP 20070725: BUG: handle a end date before start date by swapping dates
   KJ 20070716: Added
    for selecting only some roomtypes
    3.38:
    JP 20070501: CLEANUP
    JP 20070411: BUG: fix for dayrooms in the future
    JP 20070215: EXT: AllotmentsDeletedBefore
    JP 20060627: OPTIMIZE: added dbo. to tables, views and stored procedures
    3.37:
    3.36:
    JP 20060901: BUG: dayrooms on departuredate was not counted correctly at Fangel
    JP 20060523: BUG: runofhouse not performed because currentcount was used. using 0 will make allotmentmodify be called instead
    JP 20060504: BUG: causes left over allotment in release period when changing roomtype. run of house is not done!
    JP 20060427: BUG: KA: when not MPMS, remove hotelnumber from resources
    JP 20060427: BUG: code which called ga_allotmentrelease made SP take 24 seconds (even when not called), where changing code to a simple update/insert line made it take 8 seconds. Weird
    JP 20060320: BUG: fix for missing rows in AllotmentLines table giving wrong bookingstatus. the code now detects missing rows and adds them before releasing allotment
    JP 20051205: OPTIMIZE: do not save data in roombookingstatus for future since it is never used anyways
    JP 20051122: BUG: Segment and PointOfSale still causes problems
    JP 20051115: BUG: Cannot handle empty Segment or PointOfSale tables
    JP 20051026: OPTIMIZE: less reading of data in the past, plus cleaned up whole proc with proper identation and removed old remarked code 
    JP 20051025: OPTIMIZE: NO NEED TO READ DATA FROM RESERVATIONS WHEN LOOKING IN THE PAST. MUCH FASTER OCCUPANCY PLAN NOW
    JP 20051014: EXT:  DO NOT DELETE FROM ROOMBOOKINGSTATUS AND RoomBookingStatusInsale WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS
    JP 20050927: OPTIMIZE: use BETWEEN for datestamp
    JP 20050926: OPTIMIZE: removed code that fixes negative allotment. It is simply too slow now when calling function. allotment should never be negative!
    3.35:
    JP 20050912 BUG: X - 0 = 0 then X is 0 too!
    JP 20050725 BUG: test for fetch_status <> -2
    JP 20050613 ext: changed so CurrentCount is not used from AllotmentLines table
    JP 20050519 bug: NumberOfDayRooms might be NULL
    JP 20050318 bug: look at VacantRoomsInclAllotment hotelsetting (bel-air)
    AV 20050131 changed: put af default constraint on hotelnumber in roombookingstatus insale table when creating it
    JP 20040109 changed: optimized: minor changes to make smaller SQL's
    JP 20041206 bug: insale prices. NULL -> 0
    JP 20041206 bug: only change insale when called from nightaudit and not when regen. bookingstatus
    JP 20041202 changed: use of @NightAudit instead of selecting it from database
    NB 20041125 bug: Select at end only if not called from Nigthaudit
    JP 20041119 changed: soldprice from firstprice and not totalprice
    AV 20041118 changed: allotment section: if pos and seg number is null then set them to single seg and pos number (Happens when no bookings exist in period but allotments exist )
    JP 20041115 changed: fixed bug in 1.07 when hotelnumber, in Allotment table, was null
    JP 20041103 changed: renamed GenerateAvailabilityView to TGenerateAvailabilityView
    JP 20041101 changed: finished SoldPrice
    JP 20041029 changed: added SoldPrice
    JP 20041020 changed: fixed allotment bug at Skallerup
    JP 20040915 changed: fixed bug when @SpecifyOnRoomTypeSegPos=0
    JP 20040715 changed: fixed Released period can be modified -> not released allotment. ReleaseAllotment routine is called again always
    JP 20040623 changed: fixed PaxAllotment which was multiplied with itself pretty much
    JP 20040617 changed: fixed so avail can be recalced in the past and does not wipe allotment reservations (bornholm)
    JP 20040614 changed: fixed: problem with outoforder not being linked correctly to hotel when SP is called for all hotels (-1)
    JP 20040526 changed: optimized: Removed NumberOfOutOfHouseDayRooms field
    JP 20040525 changed: optimized RoomBookingStatusInsale table so it does not contain 0's.     Changed so roombookingstatus is correctly divided on hotelnumber instead of -1 when chain.     Use of own view instead of TMultiPurposeView
    JP 20040518 changed: less locking of AllotmentLines table by using PaxAllotment from RoomBookingStatus instead. Removed indexes as they slowed down the last select from temp table when order by datestamp. Only release allotment in the nightaudit
    JP 20040517 changed: do not delete all from RoomBookingStatusInsale, only current hotelnumber!
    JP 20040504+0506 changed. allotment divided on hotelnumber, outoforder on allotment (removed again), outoforder per hotel. Updated since allotmentlines are not divided on hotelnumber we have to remove it completly and do something else in Picasso. Tough luck :(
    JP 20040430 changed. fixed insale when allotment not incl
    JP 20040428 changed. moved code from AllotmentModify
    JP 20040419 dayroom changes
    LAST UPDATED: 31/03-2004 Jacob Pedersen.   122655 times and counting ;)

    NOTES:
      - OnlyAllotment means that only the AllotmentLines table is updated with CurrentCount, Adults, Children1+2. RoomBookingStatus is not modified <--- DOES NOT WORK ANYMORE SO IT IS DISABLED!
      - DO NOT REMOVE COLLATE FROM MASTER DATABASE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
      - There is a huge problem in the whole database design and this stored proc. We count the rooms looking at the whole stay period of a room. Here are two senarios:
        1. If we have 2 linked guests where their period are apart, the room will be blocked in the hole.
        2. If we have linked guests which have different statuses we have no way to make sure that bookingstatus is even counted correctly or columns like Tentative and Waitinglist contain the right numbers. Consider this.
            A person is here for 1 day. But he wishes to stay for another, but the hotel is booked so they add a second row to the room (linked guest) which is then set Waitinglist.  This SP cannot handle this at all. So for now they must be created as single subres and not linked subres
*/

-- BA/090401: CR007579: Try to aviod Lock time out when opening the Roomtypes plan
DECLARE @LockTimeOut INT
SET @LockTimeOut = @@LOCK_TIMEOUT
SET LOCK_TIMEOUT 10000 --nb20060217 Do not remove locks will be caused 


DECLARE
@FormatedStartDate  CHAR (8),
@FormatedEndDate    CHAR (8),
@SystemDateStr      CHAR (8),
-- BA/20100518: DoubleBooking/Count as one
@IsDoubleBooking    BIT,
@DoubleBookingCount INT,
-- BA/20100928: CR016840
@IsDoubleBookingEnabled BIT,
-- BA/20110902: CR033070
@BookMixedResourcesEnabled BIT,
@TempHotelNumber INT,
@IncludeOccupiedInOFS BIT 

SELECT @IncludeOccupiedInOFS = KeyValue FROM HotelSetup  WITH (NOLOCK) WHERE SEARCHKEY = 'IncludeOccupiedInOFS'
SET @IncludeOccupiedInOFS = ISNULL(@IncludeOccupiedInOFS, 0)

SELECT @IsDoubleBookingEnabled = KeyValue FROM HotelSetup  WITH (NOLOCK) WHERE SEARCHKEY = 'EnableDoubleBooking'
SET @IsDoubleBookingEnabled = ISNULL(@IsDoubleBookingEnabled, 0)


IF @EndDate > @StartDate
BEGIN
  SET @FormatedStartDate = CONVERT (CHAR(8), @StartDate, 112)
  SET @FormatedEndDate = CONVERT (CHAR(8), @EndDate, 112)
END
ELSE -- Swap dates
BEGIN
  SET @FormatedStartDate = CONVERT (CHAR(8), @EndDate, 112)
  SET @FormatedEndDate = CONVERT (CHAR(8), @StartDate, 112)
END

IF @ARegenerateRoomBookingStatus = 1
	SET @SystemDateStr = @FormatedStartDate
ELSE
	SELECT @SystemDateStr = SystemDate FROM dbo.System (NOLOCK) WHERE Number = 1  -- Only one record

DECLARE
@DADATE VARCHAR(255),
@TINT INT
SET @TINT = 0
select @DADATE=convert(varchar, getdate (),121)
print '1.00: '+@DADATE

-- Cleanup OOO lines without roomnumber

/*select rl.LINENUMBER INTO #TEMP  from reservationroomline rl (nolock)
inner join RESERVATIONROOMLINEXTAB rlx (nolock) on rl.LINENUMBER = rlx.LINENUMBER and rlx.referencenumber = 0
where ISNULL(RoomNumber, 0) = 0

delete from RESERVATIONROOMLINEXTAB
where LINENUMBER IN (SELECT LINENUMBER from #TEMP)

delete from RESERVATIONROOMLINE
where LINENUMBER IN (SELECT LINENUMBER from #TEMP)

drop table #TEMP
*/

CREATE TABLE #HotelNumbers(
    listpos int NOT NULL,
    number  int NOT NULL
  )
IF @TheHotelNumber = '-1'
	SET @TheHotelNumber = ''

  IF @TheHotelNumber <> ''
	INSERT INTO #HotelNumbers (listpos, number)
	SELECT ListPos, Number FROM DBO.iter_intlist_to_table(@TheHotelNumber)


IF @FormatedEndDate >= @SystemDateStr  -- NO NEED TO READ DATA FROM RESERVATIONS WHEN LOOKING IN THE PAST. MUCH FASTER OCCUPANCY PLAN NOW
BEGIN
--  IF @OnlyAllotment <> 0      -- JP 17/6-2002 doesn't work anymore as we are using temp tables!
--    SET @OnlyAllotment = 0    --
  CREATE TABLE #AllotmentLines (
	AllotmentNumber INT,
	RoomTypeNumber INT,
	DateStamp CHAR (8) COLLATE DATABASE_DEFAULT NOT NULL,
	CurrentCount SMALLINT
  )
  
  -- BA/20100518: DoubleBooking/Count as one.
  CREATE TABLE #ProcessedRooms
    (
     RoomID          INT,
     ReferenceNumber INT,
     FromDate        CHAR(8),
     FromTime        CHAR(5),
     ToDate          CHAR(8),
     ToTime          CHAR(5)
    ) 
    
   CREATE TABLE #ProcessedRoomlines
	 (
	 LineNumber		 INT,
	 ReferenceNumber INT,
     DateStamp       CHAR(8)
     PRIMARY KEY CLUSTERED 
(
     LineNumber      DESC,
     ReferenceNumber DESC,
     DateStamp       DESC
)
)

  CREATE TABLE #NoRoomNumber
    (
	  RoomTypeNumber	INT,
	  DateStamp			CHAR(8),
	  HotelNumber	    INT,
	  SoldRoomType      INT,
	  ParentTypeNumber	INT,
	  NumberOfRooms		INT,
	  NumberOfAllocatedRooms INT,
	  PRIMARY KEY CLUSTERED 
	  (
	    RoomTypeNumber	ASC,
	    DateStamp		DESC,
	    HotelNumber	    ASC,
	    SoldRoomType	ASC
	  )
    )

  


  IF @SpecifyOnRoomTypeSegPos = 1
  BEGIN
	CREATE TABLE #ROOMBOOKINGSTATUS (
		[DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
		[RoomTypeNumber] [int] NOT NULL ,
		[SegmentNumber] [int] NOT NULL ,
		[PointOfSaleNumber] [int] NOT NULL ,
		[HotelNumber] [int] NOT NULL ,
		[SoldRoomType] [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 ,
		[NumberOfOccupiedGroup] [smallint] NULL,
		[SoldPrice] [MONEY] NULL
--  		ON [PRIMARY]
		)
  END
  ELSE -- only used by onlinebooking
  BEGIN
	CREATE TABLE #ROOMBOOKINGSTATUS2 (
		[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 ,
		[NumberOfOccupiedGroup] [smallint] NULL,
		[SoldPrice] [MONEY] NULL,
--  		ON [PRIMARY]
		)
  END

  DECLARE
  @AllotmentNumber	Int,
  @StoreAllotmentNumber Int,
  @FreeRoomsAllotment	SmallInt,
  @AllotNumberOfRooms	SmallInt,
  @AllotAdults		SmallInt,
  @AllotChildren1	SmallInt,

  @AllotChildren2	SmallInt,
  @TotalRoomsBooked	Smallint,
  @PaxAllotment		Smallint,

  @InitialCount		Smallint,
  @CurrentCount		Smallint,
  @TotalChange 		Smallint,

  @AvailDate 		datetime,
  @RoomTypeNumber	INT,
  @DateStamp 		char (8),
  @SoldRooms 		Smallint,
  @SoldPrice		MONEY,
  @AllotmentTotal	INT,
  @Adults		SmallInt,
  @Children1		SmallInt,
  @Children2		SmallInt,
  @SpecAdults		SmallInt,
  @SpecChildren1	SmallInt,
  @SpecChildren2	SmallInt,
  @RoomCustomerNumber int,
  @AddressLink	Int,
  @NumberOfConfirmed	Smallint,
  @NumberOfProvisionals	Smallint,
  @NumberOfGuaranteed	Smallint,
  @NumberOfTentative	Smallint,
  @NumberOfWaitinglists	Smallint,
  @NumberOfDayrooms	Smallint,

  @NumberOfOutOfOrder	Smallint,
  @NumberOfOutForService smallint,
  @NumberOfNotSaleable smallint,
  @NumberOfOccupiedGroup smallint,

  @SegmentNumber	INT,
  @PointOfSaleNumber	INT,
  @SoldRoomType		INT,

  @CurrentDate DateTime,
  @CurrentDateStr Char (8),

  @AllocatedTypeNumber INT,
  @ParentTypeNumber INT,

  @ReservationType smallint,

  -- Declare Reservation Roomline Fields
  @HotelNumber Int,
  @LINENUMBER Int,
  @BookedTypeNumber Int,
  @RoomNumberID Int,
  @NumberOfRooms SmallInt,
  @TempNumberOfRooms SMALLINT, -- BA/20100518: DoubleBooking
  @ArrivalDate Char (8),
  @SpecArrivalDate Char (8),
  @DepartureDate Char (8),
  @SpecDepartureDate Char (8),

  @ArrivalTime Char (5),
  @SpecArrivalTime Char (5),
  @DepartureTime Char (5),
  @SpecDepartureTime Char (5),

  @OriginalArrDate Char (8),
  @OriginalDepDate Char (8),
  @Status SmallInt,
  @LinkedRes SmallInt,

  @ReferenceNumber Int,
  @GuestNumber Int,

  @NumberOfRoomsSoldToday smallINT,

  @OldLineNumber Int,
  @RoomArrived	Bit,
  @PaxMulitiplyRooms SmallInt,
  @StoreNumberOfRooms smallInt,
  @StoreNumberOfDayRooms smallint,

  @IsDayroom BIT,

  @IncludeAllotmentInInsale BIT,
  @IncludeTentative BIT,
  @VacantRoomsInclAllotment BIT,
  @IncludeDayroomsInSoldRooms BIT,
  @DayroomsLaterThanTime CHAR (5),
  @SingleSegmentNumber INT,
  @SinglePointOfSaleNumber INT,
  @CentralOfficeType SMALLINT,
  @ContainerSubType BIT,
  @SpecReservationGroup INT,
  @ReservationGroup INT

  SELECT @CentralOfficeType=CONVERT(SMALLINT,KeyValue) FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='CentralBookingType'
  IF @CentralOfficeType IS null
    SET @CentralOfficeType = 0
  SELECT @IncludeAllotmentInInsale=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='IncludeAllotmentInInsale'
  if @IncludeAllotmentInInsale IS null
    Set @IncludeAllotmentInInsale = 0
  SELECT @IncludeTentative=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='IncludeTentative'
  if @IncludeTentative IS null
    Set @IncludeTentative = 0
  SELECT @IncludeDayroomsInSoldRooms=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='IncludeDayroomsInSoldRooms'
  if @IncludeDayroomsInSoldRooms IS null
    Set @IncludeDayroomsInSoldRooms = 0
  SELECT @VacantRoomsInclAllotment=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='VacantRoomsInclAllotment'
  if @VacantRoomsInclAllotment IS null
    Set @VacantRoomsInclAllotment = 0

  if @TheHotelNumber IS null
    Set @TheHotelNumber = ''

  SELECT @DayroomsLaterThanTime=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='DayroomsLaterThanTime'
  IF @DayroomsLaterThanTime IS null
    Set @DayroomsLaterThanTime = '23:59' -- default

  SELECT TOP 1 @SingleSegmentNumber = Number FROM dbo.Segments (NOLOCK) WHERE Number>0 and (MarkAsDeleted='0') OR (MarkAsDeleted is null)  -- Get any SEG to put it on. Only used when checking for overbook
  IF ISNULL(@SingleSegmentNumber, 0) = 0
	SET @SingleSegmentNumber = 0
  SELECT TOP 1 @SinglePointOfSaleNumber = Number FROM dbo.PointOfSales (NOLOCK) WHERE Number>0 and (MarkAsDeleted='0') OR (MarkAsDeleted is null) -- Get any POS to put it on. Only used when checking for overbook
  IF ISNULL(@SinglePointOfSaleNumber, 0) = 0
	SET @SinglePointOfSaleNumber = 0

  -- Check if StartDate or EndDate before Systemdate -> change!
  IF @RealSystemDateStr = ''
    SET @RealSystemDateStr = @SystemDateStr -- for allotment release and dayrooms

  -- Delete generated lines from GenerateAvailability which are only used for checking for overbooking
  SET @SoldRoomType = 0

  IF @NightAudit > 0
  BEGIN
    DECLARE
    @ReservationsDeletedBefore DATETIME, -- The stored date that we before have deleted before. This can never be set to a value lower than it currently is!
    @tmpString CHAR(8)

    SELECT @tmpString=IsNull(KeyValue,0) FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='ReservationsDeletedBefore' -- SAME CODE IS FOUND IN DELETEOLDRESERVATIONS SP
    IF IsNull(@tmpString, 0) = 0
      SET @ReservationsDeletedBefore = CONVERT(DATETIME, '19800101', 112) -- BEFORE: first picasso installation and before data in first converted installation
    ELSE
      SET @ReservationsDeletedBefore = CONVERT(DATETIME, @tmpString, 112)

    SELECT @tmpString=IsNull(KeyValue,0) FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='AllotmentsDeletedBefore' -- SAME CODE IS FOUND IN DELETEOLDALLOTMENTS SP
    IF (IsNull(@tmpString, 0) > 0) AND (CONVERT(DATETIME, @tmpString, 112) > @ReservationsDeletedBefore)
      SET @ReservationsDeletedBefore = CONVERT(DATETIME, @tmpString, 112)

    DELETE FROM dbo.AllotmentLines WHERE (TotalChange = 0) and (InitialCount = 0) and (dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp) = 0) -- X - 0 = 0 then X is 0 too!
  END -- IF @NightAudit > 0
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select @DADATE=convert(varchar, getdate (),121)
print '1.02: '+@DADATE
PRINT '@FormatedEndDate'
PRINT @FormatedEndDate
PRINT '@FormatedStartDate'
PRINT @FormatedStartDate
PRINT '@SystemDateStr  '
PRINT @SystemDateStr

  DECLARE #RoomLineCur CURSOR LOCAL FAST_FORWARD FOR
/*

  SELECT HotelNumber, LINENUMBER, SpecBookedTypeNumber, SpecAllocatedTypeNumber,ParentRoomTypeNumber, SpecRoomNumberID, SpecNumberOfRooms, 
  SpecArrivalDate, SpecDepartureDate, ArrivalDate, DepartureDate, Adults, Children1, Children2, 
  SpecAdults, SpecChildren1, SpecChildren2, ReferenceNumber, GuestNumber, ISNULL(AllotmentNumber,0), Status, LinkedRes,
        ArrivalTime, DepartureTime, SpecArrivalTime, SpecDepartureTime, IsNull(MarketSegment, 0), IsNull(PointOfSale, 0), Price, RoomCustomerNumber, AddressLink, 
		ISNULL(ReservationGroup, 0), ISNULL(SpecReservationGroup, 0)
  FROM dbo.TGenerateAvailabilityView (NOLOCK)
  WHERE (SpecArrivalDate <= @FormatedEndDate AND SpecDepartureDate >= @FormatedStartDate AND SpecDepartureDate >= @SystemDateStr 
		AND ((ReferenceNumber>0 AND (Status < 7 or Status is null)) OR ReferenceNumber <= 0)
        AND (@TheHotelNumber = '' OR ISNULL(HotelNumber, 0) IN (SELECT DISTINCT Number FROM #HotelNumbers)))
  ORDER BY LineNumber, DepartureDate DESC, DepartureTime DESC
*/  
/*
SELECT      
  CASE WHEN RL.RoomNumber > 0 THEN dbo.Rooms.HotelNumber ELSE dbo.RoomTypes.HotelNumber END,
  RL.LINENUMBER, RL.BookedTypeNumber, 
  CASE WHEN RL.RoomNumber > 0 THEN dbo.Rooms.RoomTypeNumber ELSE dbo.RoomTypes.RoomTypeNumber END,
  CASE WHEN RL.RoomNumber > 0 THEN RT2.ParentRoomTypeNumber ELSE dbo.ROOMTYPES.ParentRoomTypeNumber END, 
  RL.RoomNumber, RL.NumberOfRooms,
  RL.ArrivalDate, RL.DepartureDate, dbo.RESERVATIONS.ArrivalDate, dbo.RESERVATIONS.DepartureDate,
  dbo.RESERVATIONS.Adults, dbo.RESERVATIONS.Children1, dbo.RESERVATIONS.Children2, 
  RL.Adults, RL.Children1, RL.Children2, dbo.RESERVATIONS.ReferenceNumber, dbo.RESERVATIONS.GuestNumber,
  ISNULL(dbo.RESERVATIONMAIN.AllotmentNumber, 0),
 CASE RESERVATIONS.GuestNumber WHEN 0 THEN CASE ISNULL(RL.LineNumber, 0) WHEN 0 THEN ISNULL(RESERVATIONS.Status, 0) 
    ELSE CASE ISNULL(RL.StatusRL, - 1) WHEN - 1 THEN ISNULL(RESERVATIONS.Status, 0) ELSE CASE WHEN ISNULL(RESERVATIONS.Status, 0) 
    < 5 THEN ISNULL(RL.StatusRL, 0) ELSE ISNULL(RESERVATIONS.Status, 0) END END END ELSE ISNULL(RESERVATIONS.Status, 0) END, 
 (SELECT COUNT(*) AS Expr1
    FROM dbo.RESERVATIONROOMLINEXTAB WITH (NOLOCK)
    WHERE (LINENUMBER = RL.LINENUMBER)),

  dbo.RESERVATIONS.ArrivalTime, dbo.RESERVATIONS.DepartureTime, RL.ArrivalTime, RL.DepartureTime,
  ISNULL(dbo.RESERVATIONS.MARKETSEGMENT, 0), ISNULL(dbo.RESERVATIONS.POINTOFSALE, 0), RL.Price, dbo.ROOMS.CustomerNumber, dbo.RESERVATIONADDRESS.LINK,
  ISNULL(dbo.RESERVATIONS.FK_ReservationGroup, 0), ISNULL(RL.FK_ReservationGroup, 0)
FROM dbo.RESERVATIONROOMLINE RL WITH (NOLOCK) 
  INNER JOIN  dbo.RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RL.LINENUMBER = dbo.RESERVATIONROOMLINEXTAB.LINENUMBER 
    AND RL.ArrivalDate <= @FormatedEndDate 
    AND RL.DepartureDate >= @FormatedStartDate 
    AND RL.DepartureDate >= @SystemDateStr  
  INNER JOIN dbo.RESERVATIONS WITH (NOLOCK) ON dbo.RESERVATIONROOMLINEXTAB.ReferenceNumber = dbo.RESERVATIONS.ReferenceNumber AND 
	dbo.RESERVATIONROOMLINEXTAB.GuestNumber = dbo.RESERVATIONS.GuestNumber 
		ANd CASE RESERVATIONS.GuestNumber WHEN 0 THEN CASE ISNULL(RL.LineNumber, 0) WHEN 0 THEN ISNULL(RESERVATIONS.Status, 0) 
    ELSE CASE ISNULL(RL.StatusRL, - 1) WHEN - 1 THEN ISNULL(RESERVATIONS.Status, 0) ELSE CASE WHEN ISNULL(RESERVATIONS.Status, 0) 
    < 5 THEN ISNULL(RL.StatusRL, 0) ELSE ISNULL(RESERVATIONS.Status, 0) END END END ELSE ISNULL(RESERVATIONS.Status, 0) END < 7 
INNER JOIN dbo.RESERVATIONMAIN WITH (NOLOCK) ON dbo.RESERVATIONS.ReferenceNumber = dbo.RESERVATIONMAIN.REFERENCENUMBER

LEFT OUTER JOIN dbo.RESERVATIONADDRESSXTAB WITH (NOLOCK) ON dbo.RESERVATIONS.ReferenceNumber = dbo.RESERVATIONADDRESSXTAB.REFERENCENUMBER AND 
           dbo.RESERVATIONS.GuestNumber = dbo.RESERVATIONADDRESSXTAB.GUESTNUMBER AND 
		   dbo.RESERVATIONADDRESSXTAB.AddressTypeNumber = 1
LEFT OUTER JOIN dbo.RESERVATIONADDRESS WITH (NOLOCK) ON dbo.RESERVATIONADDRESSXTAB.ADDRESSNUMBER = dbo.RESERVATIONADDRESS.AddressNumber
LEFT OUTER JOIN dbo.ROOMS WITH (NOLOCK) ON dbo.ROOMS.RoomNumber = RL.RoomNumber 
LEFT OUTER JOIN dbo.ROOMTYPES WITH (NOLOCK) ON dbo.ROOMTYPES.RoomTypeNumber = RL.BookedTypeNumber 
LEFT OUTER JOIN dbo.RoomTypes rt2 (Nolock) ON dbo.ROOMS.RoomTypeNumber = RT2.RoomTypeNUmber
 where (@TheHotelNumber = '' OR ISNULL(CASE WHEN RL.RoomNumber > 0 THEN dbo.Rooms.HotelNumber ELSE dbo.RoomTypes.HotelNumber END, 0) IN (SELECT DISTINCT Number FROM #HotelNumbers))
ORDER BY RL.LINENUMBER, RL.DepartureDate DESC, RESERVATIONS.DepartureTime DESC
*/--nb20161028 Optimization 1.Case simplified 2.Use Aliases 3.Ordering fields by table joins
SELECT      
  RL.LINENUMBER, RL.BookedTypeNumber, RL.RoomNumber, RL.NumberOfRooms, RL.ArrivalDate, RL.DepartureDate, RL.Adults, RL.Children1, RL.Children2,
  RL.ArrivalTime, RL.DepartureTime, RL.Price, ISNULL(RL.FK_ReservationGroup, 0),  
  RR.ArrivalDate, RR.DepartureDate, RR.Adults, RR.Children1, RR.Children2, RR.ReferenceNumber, RR.GuestNumber,
  RR.ArrivalTime, RR.DepartureTime, ISNULL(RR.MARKETSEGMENT, 0), ISNULL(RR.POINTOFSALE, 0), ISNULL(RR.FK_ReservationGroup, 0), 
  ISNULL(RRM.AllotmentNumber, 0), dbo.RESERVATIONADDRESS.LINK, dbo.ROOMS.CustomerNumber, 
  CASE WHEN RL.RoomNumber > 0 THEN dbo.Rooms.HotelNumber ELSE dbo.RoomTypes.HotelNumber END,
  CASE WHEN RL.RoomNumber > 0 THEN dbo.Rooms.RoomTypeNumber ELSE dbo.RoomTypes.RoomTypeNumber END,
  CASE WHEN RL.RoomNumber > 0 THEN RT2.ParentRoomTypeNumber ELSE dbo.ROOMTYPES.ParentRoomTypeNumber END, 
  CASE WHEN (RRX.GuestNumber > 0 OR ISNULL(RL.LineNumber, 0) = 0 OR ISNULL(RL.StatusRL, - 1) = -1 OR ISNULL(RR.Status, 0) >= 5) THEN 
    ISNULL(RR.Status, 0) ELSE ISNULL(RL.StatusRL, 0) END, 
 (SELECT COUNT(*) FROM dbo.RESERVATIONROOMLINEXTAB WITH (NOLOCK) WHERE (LINENUMBER = RL.LINENUMBER))
FROM dbo.RESERVATIONROOMLINE RL WITH (NOLOCK) 
  INNER JOIN dbo.RESERVATIONROOMLINEXTAB RRX WITH (NOLOCK) ON RRX.LINENUMBER = RL.LINENUMBER 
  INNER JOIN dbo.RESERVATIONS RR WITH (NOLOCK) ON RR.ReferenceNumber = RRX.ReferenceNumber AND RR.GuestNumber = RRX.GuestNumber
	AND CASE WHEN (RR.GuestNumber > 0 OR ISNULL(RL.LineNumber, 0) = 0 OR ISNULL(RL.StatusRL, - 1) = -1 OR ISNULL(RR.Status, 0) >= 5) THEN 
      ISNULL(RR.Status, 0) ELSE ISNULL(RL.StatusRL, 0) END < 7 
  INNER JOIN dbo.RESERVATIONMAIN RRM WITH (NOLOCK) ON RR.ReferenceNumber = RRM.REFERENCENUMBER
  LEFT JOIN dbo.RESERVATIONADDRESSXTAB RAX WITH (NOLOCK) ON RR.ReferenceNumber = RAX.REFERENCENUMBER 
    AND RR.GuestNumber = RAX.GUESTNUMBER AND RAX.AddressTypeNumber = 1
  LEFT JOIN dbo.RESERVATIONADDRESS WITH (NOLOCK) ON RAX.ADDRESSNUMBER = dbo.RESERVATIONADDRESS.AddressNumber
  LEFT JOIN dbo.ROOMS WITH (NOLOCK) ON dbo.ROOMS.RoomNumber = RL.RoomNumber 
  LEFT JOIN dbo.ROOMTYPES WITH (NOLOCK) ON dbo.ROOMTYPES.RoomTypeNumber = RL.BookedTypeNumber 
  LEFT JOIN dbo.RoomTypes rt2 (NOLOCK) ON dbo.ROOMS.RoomTypeNumber = RT2.RoomTypeNUmber
WHERE RL.ArrivalDate <= @FormatedEndDate AND RL.DepartureDate >= @FormatedStartDate AND RL.DepartureDate >= @SystemDateStr AND 
 (@TheHotelNumber = '' OR ISNULL(CASE WHEN RL.RoomNumber > 0 THEN dbo.Rooms.HotelNumber ELSE dbo.RoomTypes.HotelNumber END, 0) IN 
  (SELECT DISTINCT Number FROM #HotelNumbers))
ORDER BY RL.LINENUMBER, RL.DepartureDate DESC, RR.DepartureTime DESC

  OPEN #RoomLineCur
select @DADATE=convert(varchar, getdate (),121)
print '1.3: '+@DADATE

  FETCH NEXT FROM #RoomLineCur
/*
  INTO @HotelNumber, @LINENUMBER, @BookedTypeNumber, @AllocatedTypeNumber, @ParentTypeNumber, @RoomNumberID, @NumberOfRooms, @SpecArrivalDate, @SpecDepartureDate, @ArrivalDate, @DepartureDate, @Adults, @Children1, @Children2, @SpecAdults, @SpecChildren1, @SpecChildren2, @ReferenceNumber, @GuestNumber, @AllotmentNumber, @Status, @LinkedRes,
       @ArrivalTime, @DepartureTime, @SpecArrivalTime, @SpecDepartureTime, @SegmentNumber, @PointOfSaleNumber, @SoldPrice, @RoomCustomerNumber, @AddressLink, @ReservationGroup, @SpecReservationGroup
*/--nb20161028 Optimization
  INTO @LINENUMBER, @BookedTypeNumber, @RoomNumberID, @NumberOfRooms, @SpecArrivalDate, @SpecDepartureDate, @SpecAdults, @SpecChildren1, @SpecChildren2, 
    @SpecArrivalTime, @SpecDepartureTime, @SoldPrice, @SpecReservationGroup,
    @ArrivalDate, @DepartureDate, @Adults, @Children1, @Children2, @ReferenceNumber, @GuestNumber,
    @ArrivalTime, @DepartureTime, @SegmentNumber, @PointOfSaleNumber, @ReservationGroup, 
    @AllotmentNumber, @AddressLink, @RoomCustomerNumber, @HotelNumber, @AllocatedTypeNumber, @ParentTypeNumber, @Status, @LinkedRes

  SET @OldLineNumber = 0
  SET @RoomArrived = 0
select @DADATE=convert(varchar, getdate (),121)
print '1.04: '+@DADATE

  WHILE (@@FETCH_STATUS <> -1) --AND @HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers)
  BEGIN

    IF @@FETCH_STATUS <> -2
    BEGIN
      IF @CentralOfficeType = 0
        SET @HotelNumber = 0 -- not MPMS, so disregard KA's hotelnumbers
      ELSE IF @HotelNumber IS NULL
        SET @HotelNumber = 0

      if @Status <= 6  --  rsOutOfHouse
      begin
        Set @OriginalArrDate = ''
        Set @OriginalDepDate = ''
        Set @SoldRoomType = 0

        if @GuestNumber = 0
        begin
          Set @ArrivalDate = @SpecArrivalDate
          Set @DepartureDate = @SpecDepartureDate
          Set @ArrivalTime = @SpecArrivalTime         
          Set @DepartureTime = @SpecDepartureTime --

      /*    IF @ReferenceNumber = 0 and @ArrivalDate=@DepartureDate  -- compare dates for backward compatibility. it would be better to test for 23:59 because of future compatibilty but nothing to do about that now :(
          BEGIN
            SET @DepartureTime = '00:00'
            SET @DepartureDate = CONVERT (CHAR (8), CONVERT (DATETIME, @DepartureDate)+1, 112)
          END
      */

          Set @Adults = @SpecAdults
          Set @Children1 = @SpecChildren1
          Set @Children2 = @SpecChildren2
		  
		  SET @ReservationGroup = @SpecReservationGroup
        end
        else if @LinkedRes > 1  -- guestnumber > 0
        begin
          --if @OldLineNumber = @LineNumber
           -- Set @NumberOfRooms = 0
         -- else
          if @OldLineNumber <>  @LineNumber
          begin
            Set @OriginalArrDate = @ArrivalDate
            Set @OriginalDepDate = @DepartureDate

            Set @ArrivalDate = @SpecArrivalDate              -- JP 210502 Count once in whole period!
            Set @DepartureDate = @SpecDepartureDate  -- THERE IS ONE PROBLEM WITH THIS! If there is a hole in the stay we cannot compare against inhouse lists. Example guest 1 from 1st to 2nd in the month and guest 2 from 3rd to 4th in the month. There is no one in the room the 2nd day but it is still counted! KA has said OK for this
          end
        end

  /*      if @RoomNumberID > 0  
          SELECT @AllocatedTypeNumber = RoomTypeNumber FROM dbo.Rooms (NOLOCK) WHERE RoomNumber = @RoomNumberID
        else
          Set @AllocatedTypeNumber = @BookedTypeNumber
  */        
        Set @NumberOfConfirmed = 0
        Set @NumberOfProvisionals = 0
        Set @NumberOfGuaranteed = 0
        Set @NumberOfWaitinglists = 0
        Set @NumberOfTentative = 0

        IF (@ReferenceNumber > 0)
        BEGIN
     --     SELECT @AllotmentNumber=AllotmentNumber, @ReservationType=ReservationType FROM dbo.ReservationMain (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber
     --     if IsNull (@AllotmentNumber, 0) = 0
     --       Set @AllotmentNumber = 0
		  if @OldLineNumber <>  @LineNumber
          begin	
			  if (@Status = 0)  -- rsConfirmed
				Set @NumberOfConfirmed = @NumberOfRooms
			  else if (@Status = 1)  -- rsProvisional
				Set @NumberOfProvisionals = @NumberOfRooms
			  else if (@Status = 2)  -- rsGuaranteed
				Set @NumberOfGuaranteed = @NumberOfRooms
			  else if (@Status = 3)  -- rsWaitinglist
				Set @NumberOfWaitinglists = @NumberOfRooms
			  else if (@Status = 4)  -- rsTentative
				Set @NumberOfTentative = @NumberOfRooms
		  end

          if @Status = 3 or (@Status = 4 AND @IncludeTentative = 0) -- rsWaitinglist or rsTentative <------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          begin
            set @NumberOfRooms = 0  -- Reservation wont be counted
            set @Adults = 0
            set @Children1 = 0
            set @Children2 = 0
          end
          
          
			
        END  -- IF @ReferenceNumber > 0
        ELSE
          SET @AllotmentNumber=0  -- JP 20041020 fix for allotment at skallerup
          
        if @RoomCustomerNumber = @AddressLink 
		  set @SoldRoomType = 1

        Set @IsDayroom = 0

        if @ArrivalDate < @FormatedStartDate
          Set @CurrentDateStr = @FormatedStartDate
        else
          SET @CurrentDateStr = @ArrivalDate
        SET @CurrentDate = Convert (DATETIME, @CurrentDateStr)

        SET @StoreAllotmentNumber = @AllotmentNumber  -- Makes sure that reservations more than one day is divided correctly!
        SET @StoreNumberOfRooms = @NumberOfRooms

        if IsNull (@Adults, 0) = 0
          SET @Adults = 0
        if IsNull (@Children1, 0) = 0
          SET @Children1 = 0
        if IsNull (@Children2, 0) = 0
          SET @Children2= 0

        -- BA/20100518: DoubleBooking/Do not include ReferenceNumber's without a room. DoubleBooking can only occure
        --              when two different ReferenceNumber stay's at the same room in corssing periodes.
        IF @RoomNumberID IS NOT NULL
        BEGIN
          IF ISNULL(@SpecDepartureTime,'') = ''
            SET @SpecDepartureTime = '23:59'

          INSERT INTO #ProcessedRooms (RoomID, ReferenceNumber, FromDate, FromTime, ToDate, ToTime) 
            VALUES (@RoomNumberID, @ReferenceNumber, @SpecArrivalDate, @SpecArrivalTime, @SpecDepartureDate, @SpecDepartureTime)
  
           --PRINT 'INSERT INTO ProcessedRooms'
           --PRINT @RoomNumberID
           --PRINT @ReferenceNumber
           --PRINT @SpecArrivalDate
           --PRINT @SpecDepartureDate
        END

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        WHILE (@AllocatedTypeNumber > 0 and @CurrentDateStr <= @FormatedEndDate) and ((@ArrivalDate = @DepartureDate and @CurrentDateStr = @DepartureDate) or (@ArrivalDate <> @DepartureDate and @CurrentDateStr = @DepartureDate and (@DepartureTime > @DayroomsLaterThanTime and @DayroomsLaterThanTime<'23:59')) or (@CurrentDateStr < @DepartureDate)) -- fix for dayroom problem regarding departuredate
        BEGIN
          IF @CurrentDateStr >= @SystemDateStr -- NO NEED TO READ DATA FOR PAST!
          BEGIN
			IF EXISTS(SELECT * FROM #ProcessedRoomlines WHERE ReferenceNumber = @ReferenceNumber AND LineNumber = @LINENUMBER AND DateStamp = @CurrentDateStr)
			BEGIN
			  SET @NumberOfRooms = 0
			  SET @StoreNumberOfRooms = 0
			END
			ELSE
			BEGIN
			  INSERT INTO #ProcessedRoomlines (LineNumber, ReferenceNumber, DateStamp )
			  VALUES (@LINENUMBER, @ReferenceNumber, @CurrentDateStr)
			END
			
            IF @AllotmentNumber = 0
              SET @PaxMulitiplyRooms = @NumberOfRooms  -- section moved up from below. numberofrooms made multiplypax wrong since it was 0
            ELSE
             SET @PaxMulitiplyRooms = @StoreNumberOfRooms  -- section moved up from below. numberofrooms made multiplypax wrong since it was 0

            IF @PaxMulitiplyRooms = 0
              SET @PaxMulitiplyRooms = 1

            IF @OriginalDepDate <> ''
              IF (@CurrentDateStr >= @OriginalDepDate or @CurrentDateStr < @OriginalArrDate) and @IsDayRoom = 0
                SET @PaxMulitiplyRooms = 0

            IF @CurrentDateStr >= @FormatedStartDate 
            BEGIN
              -- BA/20110902: CR033070/MixedResourced: Check if the allotment does exists on the hotel of the RoomLine.
              --              If not, then don't activate any Allotment calculations. Allotment only exists for
              --              one hotel. Ignore the Allotment
              SELECT @BookMixedResourcesEnabled = KeyValue FROM HotelSetup  WITH (NOLOCK) WHERE SearchKey = 'BookMixedResourcesEnabled'
              IF @BookMixedResourcesEnabled = 1
              BEGIN
                SELECT @TempHotelNumber = HotelNumber FROM Allotments  WITH (NOLOCK) WHERE AllotmentNumber = @AllotmentNumber
                IF @TempHotelNumber <> @HotelNumber
                  SET @AllotmentNumber = 0
              END
              
              IF @AllotmentNumber = 0  -- No allotmentnumber, have to insert the number of rooms on any given allotment. This doesnt affect the number
              BEGIN
                SET @AllotmentNumber = -1
                SET @AllotNumberOfRooms = 0
                SET @AllotAdults = 0
                SET @AllotChildren1 = 0
                SET @AllotChildren2 = 0
                SET @NumberOfRooms = @StoreNumberOfRooms
                -- Only update TotalRoomsBooked in AllotmentLines
              END
              ELSE -- Allotment
              BEGIN
                SET @AllotNumberOfRooms = @StoreNumberOfRooms  -- We have a res against allotment. Only subtract from allotment
--				SET @AllotNumberOfRooms = @NumberOfRooms  -- We have a res against allotment. Only subtract from allotment
                IF @VacantRoomsInclAllotment = 1
                  SET @NumberOfRooms = 0  -- and not from availability

                IF @AllotNumberOfRooms = 0
                BEGIN
                  SET @AllotAdults = @Adults
                  SET @AllotChildren1 = @Children1
                  SET @AllotChildren2 = @Children2
                END
                ELSE
                BEGIN
                  SET @AllotAdults = @Adults*@AllotNumberOfRooms			
                  SET @AllotChildren1 = @Children1*@AllotNumberOfRooms			
                  SET @AllotChildren2 = @Children2*@AllotNumberOfRooms			
                END
              END

              SET @NumberOfOutOfOrder = 0
              IF @ReferenceNumber=0 
              BEGIN
                IF @CurrentDateStr<@DepartureDate
                  SET @NumberOfOutOfOrder = @NumberOfRooms
                SET @NumberOfRooms = 0
                SET @NumberOfDayRooms = 0
              END
              
              SET @NumberOfOUtForService = 0
              IF @ReferenceNumber=-2 
              BEGIN
                IF @CurrentDateStr<@DepartureDate
                  SET @NumberOfOUtForService = @NumberOfRooms
                SET @NumberOfRooms = 0
                SET @NumberOfDayRooms = 0
              END
              
              SET @NumberOfNotSaleable = 0
              IF @ReferenceNumber=-3 
              BEGIN
				
                IF @CurrentDateStr<@DepartureDate
                  SET @NumberOfNotSaleable = @NumberOfRooms
                SET @NumberOfRooms = 0
                SET @NumberOfDayRooms = 0
              END

              SET @StoreNumberOfDayRooms = @NumberOfDayRooms
              IF @NumberOfDayrooms > 0 AND @ArrivalDate <> @DepartureDate AND @CurrentDateStr < @DepartureDate
                SET @NumberOfDayRooms = 0

              IF @NumberOfDayrooms > 0 AND @AllotNumberOfRooms > 0 AND @ArrivalDate <> @DepartureDate AND @CurrentDateStr = @DepartureDate  -- JP 20040331 fix for allotment on depdate
              BEGIN
                IF @NumberOfRooms = 0
                  SET @NumberOfRooms = @AllotNumberOfRooms
                SET @AllotNumberOfRooms = 0
              END

--              if (((@SpecArrivalDate = @SpecDepartureDate) or (@SpecArrivalDate <> @SpecDepartureDate AND @SpecDepartureDate = @CurrentDateStr))) and (@ReferenceNumber>0)
              if (((@SpecArrivalDate = @SpecDepartureDate) or (@SpecArrivalDate <> @SpecDepartureDate AND @SpecDepartureDate = @CurrentDateStr AND @DepartureTime > @DayroomsLaterThanTime))) and (@ReferenceNumber>0)  -- JP 20060901 look at dayroomslaterthantime
              begin
                Set @IsDayroom = 1
                Set @NumberOfDayrooms = @NumberOfRooms + @AllotNumberOfRooms

                if (@IncludeDayroomsInSoldRooms = 0) or (@CurrentDateStr >= @RealSystemDateStr) -- DO NOT COUNT DayRooms
--                if @IncludeDayroomsInSoldRooms = 0 -- JP 20050901 why this date stuff? NB! in nightaudit realsystemdate is +1
                BEGIN
                  SET @PaxMulitiplyRooms = 0
                  SET @NumberOfRooms = 0
                  SET @AllotNumberOfRooms = 0
                END
              end
              else
			   Set @NumberOfDayrooms = 0 -- JP 20060901 or else null values exist and we cannot add to null value and therefore we get a lower number of dayrooms than acutally should be shown

              IF @ReferenceNumber > 0 -- JP 20041020 allotment bug. no allotment on outoforder res!
                IF (@AllotNumberOfRooms > 0) AND (@CurrentDateStr >=  CONVERT (CHAR (8), @ArrivalDate)) and (@CurrentDateStr < CONVERT (CHAR (8), @DepartureDate)) -- JP 20040617 fix for recalc bookstat for past
                BEGIN
                  UPDATE #AllotmentLines SET
                    CurrentCount = CurrentCount + @AllotNumberOfRooms  -- Number of reservations against this allotment
                  WHERE RoomTypeNumber = @AllocatedTypeNumber AND DateStamp=@CurrentDateStr and AllotmentNumber = @AllotmentNumber OPTION (KEEP PLAN)
                  IF @@rowcount = 0
                    INSERT INTO #AllotmentLines (AllotmentNumber,RoomTypeNumber,DateStamp,CurrentCount)
                    VALUES (@AllotmentNumber, @AllocatedTypeNumber, @CurrentDateStr, @AllotNumberOfRooms)
                END

              IF @SoldPrice IS null
                SET @SoldPrice = 0

              -- BA/20100518: DoubleBooking/Count as one
              --PRINT 'CurrentDateStr'
              --PRINT @ReferenceNumber
              --PRINT @CurrentDateStr + ', ' + @SpecArrivalDate + ', ' + @SpecArrivalTime + ', ' + @SpecDepartureDate + ', ' + @SpecDepartureTime
              --PRINT @RoomNumberID
              SELECT @DoubleBookingCount = COUNT(*)
                 FROM #ProcessedRooms 
                 WHERE 
                   -- OTHER CRITERIA
                   (RoomID = @RoomNumberID) AND (ReferenceNumber <> @ReferenceNumber) AND
                   -- ARRIVAL CRITERIA
                   ((@CurrentDateStr = @SpecArrivalDate AND @CurrentDateStr = FromDate AND @SpecArrivalTime >= FromTime) OR
                     (@CurrentDateStr > FromDate)) AND
                   -- DEPARTURE CRITERIA
                   ((@CurrentDateStr = @SpecDepartureDate AND @CurrentDateStr = ToDate AND @SpecDepartureTime <= ToTime) OR
                     (@CurrentDateStr < ToDate))
--              PRINT @DoubleBookingCount

              -- BA/20100928: CR016840
              IF (@IsDoubleBookingEnabled = 1) and (ISNULL(@DoubleBookingCount, 0) > 0)
              BEGIN
                SET @IsDoubleBooking = 1
                SET @TempNumberOfRooms = 0
              END ELSE
              BEGIN
                SET @IsDoubleBooking = 0
                SET @TempNumberOfRooms = @NumberOfRooms
              END
            
              IF ISNULL(@RoomNumberID,0) = 0
			  BEGIN
 				UPDATE #NoRoomNumber
				SET	NumberOfRooms = NumberOfRooms + @TempNumberOfRooms				
				WHERE RoomTypeNumber = @AllocatedTypeNumber
				AND DateStamp = @CurrentDateStr
				AND HotelNumber = @HotelNumber
				AND SoldRoomType = @SoldRoomType
				
				IF @@rowcount = 0
				  INSERT INTO #NoRoomNumber (RoomTypeNumber, DateStamp, HotelNumber, SoldRoomType, ParentTypeNumber, NumberOfRooms, NumberOfAllocatedRooms)
				  VALUES (@AllocatedTypeNumber, @CurrentDateStr, @HotelNumber, @SoldRoomType, @ParentTypeNumber, @TempNumberOfRooms, 0)
			    
			  END ELSE
			  BEGIN
			    UPDATE #NoRoomNumber
				SET	NumberOfAllocatedRooms = NumberOfAllocatedRooms + @TempNumberOfRooms + @NumberOfNotSaleable				
				WHERE RoomTypeNumber = @AllocatedTypeNumber
				AND DateStamp = @CurrentDateStr
				AND HotelNumber = @HotelNumber
				AND SoldRoomType = @SoldRoomType
				
				IF @@rowcount = 0
				  INSERT INTO #NoRoomNumber (RoomTypeNumber, DateStamp, HotelNumber, SoldRoomType, ParentTypeNumber, NumberOfRooms, NumberOfAllocatedRooms)
				  VALUES (@AllocatedTypeNumber, @CurrentDateStr, @HotelNumber, @SoldRoomType, @ParentTypeNumber, 0, @TempNumberOfRooms + @NumberOfNotSaleable)
			  END         
              
              IF @SpecifyOnRoomTypeSegPos = 1
              BEGIN
				     
				
             
if @tint = 0
  print  'RS_START: '+convert(varchar, getdate (),121)

                UPDATE #RoomBookingStatus SET
-- BA/20100518: DoubleBooking              
--                  SoldRooms            = SoldRooms + @NumberOfRooms,  -- sold not on allotment
                  SoldRooms            = SoldRooms + @TempNumberOfRooms,  -- sold not on allotment
-- BA/090317: CR006600/SoldPrice is not timed the number of rooms
--                  SoldPrice = SoldPrice + @SoldPrice,
                  SoldPrice            = SoldPrice + (@SoldPrice * @NumberOfRooms), 
                  Adults               = Adults + @Adults * @PaxMulitiplyRooms,
                  Children1            = Children1 + @Children1 * @PaxMulitiplyRooms,
                  Children2            = Children2 + @Children2 * @PaxMulitiplyRooms, 
                  NumberOfConfirmed    = NumberOfConfirmed + @NumberOfConfirmed,
                  NumberOfProvisionals = NumberOfProvisionals + @NumberOfProvisionals,
                  NumberOfGuaranteed   = NumberOfGuaranteed + @NumberOfGuaranteed,
                  NumberOfTentative    = NumberOfTentative + @NumberOfTentative, 
                  NumberOfWaitinglists = NumberOfWaitinglists + @NumberOfWaitinglists,
                  NumberOfDayrooms     = NumberOfDayrooms + @NumberOfDayrooms,
                  NumberOfOutOfOrder   = NumberOfOutOfOrder + @NumberOfOutOfOrder,
                  NumberOfOutForService= NumberOfOutForService + @NumberOfOutForService,
                  NumberOfNotSaleable  = NumberOfNotSaleable + @NumberOfNotSaleable,
                  PaxAllotment         = PaxAllotment + (@AllotAdults + @AllotChildren1 + @AllotChildren2)
                WHERE 
                  RoomTypeNumber    = @AllocatedTypeNumber AND 
                  DateStamp         = @CurrentDateStr AND 
                  SegmentNumber     = @SegmentNumber AND 
                  PointOfSaleNumber = @PointOfSaleNumber AND 
                  SoldRoomType		= @SoldRoomType AND
                  HotelNumber       = @HotelNumber AND 
				  ReservationGroup	= @ReservationGroup OPTION (KEEP PLAN)
                  
                 
                IF @@rowcount = 0 -- Nothing was updated -> insert row!
                  INSERT INTO #RoomBookingStatus 
                    (DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, NumberOfOutOfOrder, NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, SoldPrice)
-- BA/090317: CR006600/Sold price is not timed with the number of rooms
--                  VALUES (@CurrentDateStr, @AllocatedTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @NumberOfRooms, @Adults*@PaxMulitiplyRooms, @Children1*@PaxMulitiplyRooms, @Children2*@PaxMulitiplyRooms, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, 0, 0, (@AllotAdults+@AllotChildren1+@AllotChildren2) , 0, @NumberOfOutOfOrder, @SoldPrice * @NumberOfRooms)

-- BA/20100518: DoubleBooking
--                    VALUES (@CurrentDateStr, @AllocatedTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @NumberOfRooms, @Adults*@PaxMulitiplyRooms, @Children1*@PaxMulitiplyRooms, @Children2*@PaxMulitiplyRooms, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, 0, 0, (@AllotAdults+@AllotChildren1+@AllotChildren2) , 0, @NumberOfOutOfOrder, @SoldPrice * @NumberOfRooms)
                    VALUES (@CurrentDateStr, @AllocatedTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRoomType, @ReservationGroup, @TempNumberOfRooms, @Adults*@PaxMulitiplyRooms, @Children1*@PaxMulitiplyRooms, @Children2*@PaxMulitiplyRooms, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, 0, 0, (@AllotAdults+@AllotChildren1+@AllotChildren2) , 0, @NumberOfOutOfOrder, @NumberOfOutForService, @NumberOfNotSaleable, 0, @SoldPrice * @NumberOfRooms)

if @tint = 0
  print  'RS_END  : '+convert(varchar, getdate (),121)
              END
              ELSE -- NOT @SpecifyOnRoomTypeSegPos = 1
              BEGIN
                UPDATE #RoomBookingStatus2 SET
-- BA/20100518: DoubleBooking                
--                  SoldRooms            = SoldRooms + @NumberOfRooms,  -- sold not on allotment
                  SoldRooms            = SoldRooms + @TempNumberOfRooms,  -- sold not on allotment
-- BA/090417: CR006600/SoldPrice is not timed number of rooms
--                  SoldPrice = SoldPrice+@SoldPrice,
                  SoldPrice            = SoldPrice + (@SoldPrice * @NumberOfRooms),
                  Adults               = Adults+@Adults * @PaxMulitiplyRooms,
                  Children1            = Children1 + @Children1 * @PaxMulitiplyRooms,
                  Children2            = Children2 + @Children2 * @PaxMulitiplyRooms, 
                  NumberOfConfirmed    = NumberOfConfirmed + @NumberOfConfirmed,
                  NumberOfProvisionals = NumberOfProvisionals + @NumberOfProvisionals,
                  NumberOfGuaranteed   = NumberOfGuaranteed + @NumberOfGuaranteed,
                  NumberOfTentative    = NumberOfTentative + @NumberOfTentative, 
                  NumberOfWaitinglists = NumberOfWaitinglists + @NumberOfWaitinglists,
                  NumberOfDayrooms     = NumberOfDayrooms + @NumberOfDayrooms,
                  NumberOfOutOfOrder   = NumberOfOutOfOrder + @NumberOfOutOfOrder,
                  NumberOfOutForService= NumberOfOutForService + @NumberOfOutForService,
                  NumberOfNotSaleable  = NumberOfNotSaleable + @NumberOfNotSaleable,
                  PaxAllotment         = PaxAllotment + (@AllotAdults + @AllotChildren1 + @AllotChildren2)
                WHERE 
                  RoomTypeNumber = @AllocatedTypeNumber AND 
                  DateStamp      = @CurrentDateStr AND 
                  HotelNumber    = @HotelNumber AND
				  ReservationGroup = @ReservationGroup

                IF @@rowcount = 0 -- Nothing was updated -> insert row!
                  INSERT INTO #RoomBookingStatus2 (DateStamp,RoomTypeNumber,HotelNumber, ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,NumberOfRoomsSoldToday, NumberOfOutOfOrder, NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, PaxAllotment, SoldPrice)
-- BA/090317: CR006600/Sold price is not timed with the number of rooms
--                  VALUES (@CurrentDateStr, @AllocatedTypeNumber, @HotelNumber, @NumberOfRooms, @Adults*@PaxMulitiplyRooms, @Children1*@PaxMulitiplyRooms, @Children2*@PaxMulitiplyRooms, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, 0, 0, 0, @NumberOfOutOfOrder , (@AllotAdults+@AllotChildren1+@AllotChildren2), @SoldPrice)

-- BA/20100518: DoubleBooking
--                  VALUES (@CurrentDateStr, @AllocatedTypeNumber, @HotelNumber, @NumberOfRooms, @Adults*@PaxMulitiplyRooms, @Children1*@PaxMulitiplyRooms, @Children2*@PaxMulitiplyRooms, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, 0, 0, 0, @NumberOfOutOfOrder , (@AllotAdults+@AllotChildren1+@AllotChildren2), @SoldPrice * @NumberOfRooms)
                  VALUES (@CurrentDateStr, @AllocatedTypeNumber, @HotelNumber, @ReservationGroup, @TempNumberOfRooms, @Adults*@PaxMulitiplyRooms, @Children1*@PaxMulitiplyRooms, @Children2*@PaxMulitiplyRooms, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, 0, 0, 0, @NumberOfOutOfOrder, @NumberOfOutForService, @NumberOfNotSaleable, 0, (@AllotAdults+@AllotChildren1+@AllotChildren2), @SoldPrice * @NumberOfRooms)
              END
              
              SET @NumberOfDayRooms = @StoreNumberOfDayRooms  -- needed because we read from arrival to departure and not the other way
--              END
            END -- end if
            SET @AllotmentNumber = @StoreAllotmentNumber -- Makes sure that reservations with more than one day is divided correctly!
          END -- IF @CurrentDateStr >= @SystemDateStr -- NO NEED TO READ DATA FOR PAST!
          SET @CurrentDate = @CurrentDate +1
          SET @CurrentDateStr = CONVERT (CHAR(8), @CurrentDate, 112)
        END  -- End While
      END -- if @Status <= 6  --  rsOutOfHouse
      SET @OldLineNumber = @LineNumber 
    END -- IF @@FETCH_STATUS <> -2

    FETCH NEXT FROM #RoomLineCur
/*
    INTO @HotelNumber, @LINENUMBER, @BookedTypeNumber, @AllocatedTypeNumber, @ParentTypeNumber, @RoomNumberID, @NumberOfRooms, @SpecArrivalDate, @SpecDepartureDate, @ArrivalDate, @DepartureDate, @Adults, @Children1, @Children2, @SpecAdults, @SpecChildren1, @SpecChildren2, @ReferenceNumber, @GuestNumber, @AllotmentNumber, @Status, @LinkedRes,
         @ArrivalTime, @DepartureTime, @SpecArrivalTime, @SpecDepartureTime, @SegmentNumber, @PointOfSaleNumber, @SoldPrice, @RoomCustomerNumber, @AddressLink, @ReservationGroup, @SpecReservationGroup
*/--nb20161028 Optimization
    INTO @LINENUMBER, @BookedTypeNumber, @RoomNumberID, @NumberOfRooms, @SpecArrivalDate, @SpecDepartureDate, @SpecAdults, @SpecChildren1, @SpecChildren2, 
      @SpecArrivalTime, @SpecDepartureTime, @SoldPrice, @SpecReservationGroup,
      @ArrivalDate, @DepartureDate, @Adults, @Children1, @Children2, @ReferenceNumber, @GuestNumber,
      @ArrivalTime, @DepartureTime, @SegmentNumber, @PointOfSaleNumber, @ReservationGroup, 
      @AllotmentNumber, @AddressLink, @RoomCustomerNumber, @HotelNumber, @AllocatedTypeNumber, @ParentTypeNumber, @Status, @LinkedRes

    SET @tint =  @tint + 1
  END -- END WHILE

  CLOSE #RoomLineCur
  DEALLOCATE #RoomLineCur

  ------------------------------------------------------------------------------------------------------------------------------------
  --- Calculating Occupied for reservations without room number
  ------------------------------------------------------------------------------------------------------------------------------------
  
  DECLARE @MaxRooms INT
  DECLARE @NumberOfChilds INT
  DECLARE @ChildRoomType INT
  DECLARE @Occupied INT
  DECLARE @Key INT
  DECLARE @NumberOfVacantBeds INT
  DECLARE @tmp INT
  DECLARE @NumberOfAllocatedRooms INT
  
  IF @CalculateOccupied = 1
  BEGIN

	  DECLARE #NoRoomCur CURSOR FOR
		SELECT RoomTypeNumber, DateStamp, HotelNumber,  SoldRoomType, ParentTypeNumber, NumberOfRooms, NumberOfAllocatedRooms FROM #NoRoomNumber
	
	  OPEN #NoRoomCur
  
	  FETCH NEXT FROM #NoRoomCur
	  INTO @RoomTypeNumber, @DateStamp, @HotelNumber, @SoldRoomType, @ParentTypeNumber, @NumberOFRooms, @NumberOfAllocatedRooms
  
	  WHILE @@FETCH_STATUS = 0 
	  BEGIN
		SET @Occupied = 0
		SET @Key = 0
	
		IF @ParentTypeNumber IS NOT NULL
		BEGIN
		  SELECT @MaxRooms = NumberOfChildRooms, @ContainerSubType = ContainerSubType  FROM ROOMTYPES WITH (NOLOCK) 
		  WHERE RoomTypeNumber = @ParentTypeNumber 

		  SET @NumberOfVacantBeds = ((SELECT NumberOfAllocatedRooms 
									 FROM #NoRoomNumber 
									 WHERE RoomTypeNumber = @ParentTypeNumber 
									 AND DateStamp = @DateStamp 
									 AND HotelNumber = @HotelNumber and SoldRoomType = @SoldRoomType) * @MaxRooms) - @NumberOfAllocatedRooms

		 
		  IF @NumberOfVacantBeds > 0
			SET @NumberOfRooms = @NumberOfRooms - @NumberOfVacantBeds

		  IF @MaxRooms > 0
			  SET @Occupied = CEILING(CAST(@NumberOfRooms AS FLOAT) / CAST(@MaxRooms AS FLOAT))	

	
		  SET @Key = @ParentTypeNumber	
		END ELSE
		BEGIN
 
		  SELECT @NumberOfChilds = COUNT(*) FROM ROOMTYPES WITH (NOLOCK)	
		  WHERE ParentRoomTypeNumber = @RoomTypeNumber
	  
		  IF  @NumberOfChilds = 1
		  BEGIN
			SELECT @ChildRoomType = RoomTypeNumber FROM ROOMTYPES WITH (NOLOCK)	
			WHERE ParentRoomTypeNumber = @RoomTypeNumber
			SELECT @MaxRooms = NumberOfChildRooms  FROM ROOMTYPES WITH (NOLOCK) 
			WHERE RoomTypeNumber = @RoomTypeNumber
			SET @Occupied = (@NumberOfRooms * @MaxRooms)
			SET @Key = @ChildRoomType
		  END
	
		END
	Update_BookingStatus:
		IF @Key > 0 AND @Occupied > 0
		BEGIN
			IF @SpecifyOnRoomTypeSegPos = 1 
			BEGIN
				UPDATE #ROOMBOOKINGSTATUS
				SET NumberOfNotSaleable = NumberOfNotSaleable + @Occupied
				WHERE
				RoomTypeNumber    = @Key AND 
				DateStamp         = @DateStamp AND 
				SegmentNumber     = 0 AND 
				PointOfSaleNumber = 0 AND 
				SoldRoomType	  = @SoldRoomType AND
				HotelNumber       = @HotelNumber AND
				ReservationGroup  = 0
	        
				IF @@ROWCOUNT = 0
					INSERT INTO #ROOMBOOKINGSTATUS (RoomTypeNumber, DateStamp, SegmentNumber, PointOfSaleNumber, SoldRoomType, HotelNumber, ReservationGroup, NumberOfNotSaleable, SoldRooms)
					VALUES (@Key, @DateStamp, 0, 0, @SoldRoomType, @HotelNumber, 0, @Occupied, 0)
			  END ELSE
			  BEGIN
				UPDATE #ROOMBOOKINGSTATUS2
				SET NumberOfNotSaleable = NumberOfNotSaleable + @Occupied
				WHERE 
						  RoomTypeNumber	= @Key AND 
						  DateStamp			= @DateStamp AND 
						  HotelNumber		= @HotelNumber AND
						  ReservationGroup	= 0
				IF @@ROWCOUNT = 0
					INSERT INTO #ROOMBOOKINGSTATUS2 (RoomTypeNumber,DateStamp, HotelNumber, ReservationGroup, NumberOfNotSaleable)
					VALUES (@Key, @DateStamp, @HotelNumber, 0, @Occupied)
			  END
			  IF @ContainerSubType = 1
			  BEGIN
				SELECT @key=ISNULL(ParentRoomTypeNumber, 0) FROM ROOMTYPES  WITH (NOLOCK) WHERE RoomTypeNumber = @Key
				GOTO Update_BookingStatus 
			  END	
		  END	
		FETCH NEXT FROM #NoRoomCur
		INTO @RoomTypeNumber, @DateStamp,@HotelNumber, @SoldRoomType, @ParentTypeNumber, @NumberOFRooms, @NumberOfAllocatedRooms
	  END
  
	  CLOSE #NoRoomCur
	  DEALLOCATE #NoRoomCur
 END 
  ------------------------------------------------------------------------------------------------------------------------------------
  --- Calculating Sold rooms for reservation groups
  ------------------------------------------------------------------------------------------------------------------------------------
  IF @SpecifyOnRoomTypeSegPos = 1 
  BEGIN
	  DECLARE #ResGroupCur CURSOR FOR
		SELECT DateStamp, RBS.RoomTypeNumber, RBS.HotelNumber, ReservationGroup, SUM(SoldRooms)
		FROM #ROOMBOOKINGSTATUS RBS (NOLOCK)
		INNER JOIN ROOMTYPES RT (NOLOCK) ON RBS.RoomTypeNumber = RT.RoomTypeNumber AND ISNULL(RT.ParentRoomTypeNumber, 0) > 0
		WHERE RBS.ReservationGroup > 0
		GROUP BY  DateStamp, RBS.RoomTypeNumber, RBS.HotelNumber, ReservationGroup
  END ELSE
  BEGIN
    DECLARE #ResGroupCur CURSOR FOR
		SELECT DISTINCT DateStamp, RBS.RoomTypeNumber, RBS.HotelNumber, ReservationGroup, SoldRooms
		FROM #ROOMBOOKINGSTATUS2 RBS (NOLOCK)
		INNER JOIN ROOMTYPES RT (NOLOCK) ON RBS.RoomTypeNumber = RT.RoomTypeNumber AND ISNULL(RT.ParentRoomTypeNumber, 0) > 0
		WHERE RBS.ReservationGroup > 0
  END

  OPEN #ResGroupCur

  
	FETCH NEXT FROM #ResGroupCur
	INTO @DateStamp, @RoomTypeNumber, @HotelNumber, @ReservationGroup,  @NumberOfRooms
  

  WHILE @@FETCH_STATUS = 0 
  BEGIN	
	WITH RoomtypeTree(ParentRoomTypeNumber, RoomTypeNumber, Type, NumberOfChildRooms) AS 
	  (
		  SELECT ParentRoomTypeNumber, RoomTypeNumber, Type, NumberOfChildRooms
		  FROM ROOMTYPES (NOLOCK) 
		  WHERE RoomTypeNumber = @RoomTypeNumber
		  UNION ALL
		  SELECT RT.ParentRoomTypeNumber, RT.RoomTypeNumber, RT.Type, RT.NumberOfChildRooms
		  FROM ROOMTYPES AS RT (NOLOCK)
			  INNER JOIN RoomtypeTree AS RTT
			  ON RT.RoomTypeNumber = RTT.ParentRoomTypeNumber 
	  )
	  SELECT @MaxRooms = NumberOfChildRooms 
	  FROM RoomtypeTree
	  WHERE ParentRoomTypeNumber IS NULL
	  IF ISNULL(@MaxRooms,0) > 0
		SET @MaxRooms = @MaxRooms * CEILING(CAST(@NumberOfRooms AS FLOAT) / CAST(@MaxRooms AS FLOAT))	


		DECLARE #UpdateCur CURSOR FOR
		  SELECT ID FROM ReservationGroup (NOLOCK)
		  WHERE ID <> @ReservationGroup

		OPEN #UpdateCur

		FETCH NEXT FROM #UpdateCur INTO @Key

		WHILE @@FETCH_STATUS = 0 
		BEGIN
		  IF @SpecifyOnRoomTypeSegPos = 1 
		  BEGIN
			UPDATE #ROOMBOOKINGSTATUS
			SET NumberOfOccupiedGroup = NumberOfOccupiedGroup + @MaxRooms
			WHERE
			RoomTypeNumber    = @RoomTypeNumber AND 
			DateStamp         = @DateStamp AND 
			SegmentNumber     = 0 AND 
			PointOfSaleNumber = 0 AND 
			SoldRoomType	  = 0 AND
			HotelNumber       = @HotelNumber AND
			ReservationGroup  = @Key
	        
			IF @@ROWCOUNT = 0
				INSERT INTO #ROOMBOOKINGSTATUS (RoomTypeNumber, DateStamp, SegmentNumber, PointOfSaleNumber, SoldRoomType, HotelNumber, ReservationGroup, NumberOfOccupiedGroup)
				VALUES (@RoomTypeNumber, @DateStamp, 0, 0, 0, @HotelNumber, @Key, @MaxRooms)
		  END ELSE
		  BEGIN
			UPDATE #ROOMBOOKINGSTATUS2
			SET NumberOfOccupiedGroup = NumberOfOccupiedGroup + @MaxRooms
			WHERE 
				RoomTypeNumber    = @RoomTypeNumber AND 
				DateStamp         = @DateStamp AND
				HotelNumber		  = @HotelNumber AND
				ReservationGroup  = @Key
			IF @@ROWCOUNT = 0
				INSERT INTO #ROOMBOOKINGSTATUS2 (RoomTypeNumber,DateStamp, HotelNumber, ReservationGroup, NumberOfOccupiedGroup)
				VALUES  (@RoomTypeNumber, @DateStamp, @HotelNumber, @Key,  @MaxRooms)
		  END
		  
		  FETCH NEXT FROM #UpdateCur INTO @Key
		END

		CLOSE #UpdateCur

		DEALLOCATE #UpdateCur
	
	  FETCH NEXT FROM #ResGroupCur
	  INTO @DateStamp, @RoomTypeNumber, @HotelNumber, @ReservationGroup,  @NumberOfRooms
    
  END
  
  CLOSE #ResGroupCur
  DEALLOCATE #ResGroupCur	
	


select @DADATE=convert(varchar, getdate (),121)
print '1.05: '+@DADATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- R E L E A S E   A L L O T M E N T  A F T E R ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  -- go through #allotmentlines and see if any rows are missing in real DB. if they are we need to add our info - a better fix would be to actually have the rows, but no idea where and when they disappear
  DECLARE @RowCount INT

  DECLARE #AllotCur CURSOR FAST_FORWARD FOR

    SELECT AllotmentNumber, RoomTypeNumber, DateStamp/*, CurrentCount*/ FROM #AllotmentLines (NOLOCK)

  OPEN #AllotCur
  FETCH NEXT FROM #AllotCur
  INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp--, @CurrentCount

  WHILE @@FETCH_STATUS <> -1
  BEGIN
    IF @@FETCH_STATUS <> -2
    BEGIN
      SELECT @RowCount=COUNT(*) FROM AllotmentLines (NOLOCK) WHERE AllotmentNumber=@AllotmentNumber AND RoomTypeNumber=@RoomTypeNumber AND DateStamp=@DateStamp
      IF @RowCount = 0
      BEGIN
        PRINT 'Inserting missing AllotmentLine(1)...'
--        INSERT INTO AllotmentLines (AllotmentNumber, RoomTypeNumber, DateStamp, TotalChange) VALUES (@AllotmentNumber, @RoomTypeNumber, @DateStamp, @CurrentCount)  -- JP 20060523 runofhouse not performed because currentcount was used here. using 0 will make allotmentmodify be called
        INSERT INTO AllotmentLines (AllotmentNumber, RoomTypeNumber, DateStamp, InitialCount, TotalChange) VALUES (@AllotmentNumber, @RoomTypeNumber, @DateStamp, 0, 0)
      END

    END -- IF @@FETCH_STATUS <> -2

    FETCH NEXT FROM #AllotCur
    INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp--, @CurrentCount
  END -- WHILE

  CLOSE #AllotCur
  DEALLOCATE #AllotCur

select @DADATE=convert(varchar, getdate (),121)
print '1.06: '+@DADATE

  IF @NightAudit > 0 -- JP 20050613 we won't be needing released data until nightaudit has been run. currentcount is only written to database at night to avoid locks
    IF @OnlyAllotment = 0 -- and (@TheHotelNumber = 0)  -- allotment is only stored for local hotel
      EXEC dbo.ReleaseAllotment @RealSystemDateStr  -- now it's always filled!

select @DADATE=convert(varchar, getdate (),121)
print '1.07: '+@DADATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- U P D A T E  R O O M B O O K I N G S T A T  W I T H  A L L O T M E N T ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  DECLARE #AllotCur CURSOR FAST_FORWARD FOR

    SELECT AllotmentLines.AllotmentNumber,RoomTypeNumber,DateStamp, IsNull(InitialCount, 0),IsNull(TotalChange, 0), IsNull(HotelNumber, 0) FROM dbo.AllotmentLines (NOLOCK)  -- NOT #AllotmentLines because we need the released info!
    INNER JOIN dbo.Allotments (NOLOCK) ON Allotments.AllotmentNumber = AllotmentLines.AllotmentNumber AND
    DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate AND ((IsNull(HotelNumber,0) IN (SELECT DISTINCT Number FROM #HotelNumbers) AND @TheHotelNumber <> '') OR (@TheHotelNumber = '') OR (HotelNumber IS NULL))  -- Empty string is all
    AND (MarkAsDeleted IS NULL OR CONVERT(DATETIME, DateStamp, 112) < MarkAsDeleted)
-- remarked because it hides overbookings on allotment which should never happen    AND (IsNull(InitialCount,0) + IsNull(TotalChange,0) <> 0)

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

  WHILE @@FETCH_STATUS <> -1
  BEGIN
    IF @@FETCH_STATUS <> -2
    BEGIN
      -- This problem was fixed on the ReservationFormUnit.pas form.
      -- BA/20110902: CR033070/MixedResources: We need to get the correct HotelNumber for the RoomType.
--      SELECT @BookMixedResourcesEnabled = KeyValue FROM HotelSetup WHERE SearchKey = 'BookMixedResourcesEnabled'
      --IF @BookMixedResourcesEnabled = 1
--        SELECT @HotelNumber = HotelNumber FROM RoomTypes WHERE RoomTypeNumber = @RoomTypeNumber
      

--      SELECT @BookMixedResourcesEnabled = KeyValue FROM HotelSetup WHERE SearchKey = 'BookMixedResourcesEnabled'
--      IF @BookMixedResourcesEnabled = 1
  --    BEGIN
    --    SELECT @TempHotelNumber = HotelNumber FROM Allotments WHERE AllotmentNumber = @AllotmentNumber
      --  IF @TempHotelNumber <> @HotelNumber
--        BEGIN
  --        FETCH NEXT FROM #AllotCur
    --      INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp, @InitialCount, @TotalChange, @HotelNumber
      --    CONTINUE
--        END
  --    END
                     
      IF @CentralOfficeType = 0
        SET @HotelNumber = 0 -- not MPMS, so disregard KA's hotelnumbers

      IF IsNull(@HotelNumber, 0) = 0
        SET @HotelNumber = 0

      SELECT @CurrentCount=CurrentCount FROM #AllotmentLines WHERE AllotmentNumber=@AllotmentNumber AND RoomTypeNumber=@RoomTypeNumber AND DateStamp=@DateStamp  -- JP 20050613 read CurrentCount that we just counted ourselves. avoid updating table until nightaudit
      IF @@rowcount=0
        SET @CurrentCount=0

      IF @SpecifyOnRoomTypeSegPos = 1
      BEGIN
        SELECT TOP 1 @SegmentNumber=SegmentNumber, @PointOfSaleNumber=PointOfSaleNumber FROM #RoomBookingStatus (NOLOCK)
        WHERE RoomTypeNumber=@RoomTypeNumber AND DateStamp=@DateStamp AND HotelNumber=@HotelNumber OPTION (KEEP PLAN)

        IF IsNull(@PointOfSaleNumber,0) = 0
          SET @PointOfSaleNumber = @SinglePointOfSaleNumber
        IF IsNull(@SegmentNumber,0) = 0
          SET @SegmentNumber = @SingleSegmentNumber

if (@InitialCount + @TotalChange - @CurrentCount) < 0
begin
-- BA/20090609: CR008736/Reenabled due to a lot of negativ allotment all over the system

  PRINT 'Fixing negative allotment (calling AllotmentModify)...  '+CONVERT(VARCHAR, @CurrentCount-@InitialCount)

  DECLARE
  @ModifyStart DATETIME,
  @ModifyEnd DATETIME
  SET @ModifyStart = CONVERT(DATETIME, @DateStamp, 112)
  SET @ModifyEnd = @ModifyStart + 1
  EXEC dbo.AllotmentModify @AllotmentNumber, @ModifyStart, @ModifyEnd, @RoomTypeNumber -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - LOCKING PROBLEM - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
-- JP 20070807 BUG: When CopySave to a new reservation allotment gets modified eventhough you tell it not to because of this code. Let's find the problems in the source instead of patching our code in this way. Unclear if the following line is a good idea (SET @TotalChange = @InitialCount - @CurrentCount)
  SET @TotalChange = @InitialCount - @CurrentCount
end

        UPDATE #RoomBookingStatus SET
          FreeRoomsAllotment = FreeRoomsAllotment + (@InitialCount + @TotalChange - @CurrentCount),
          TotalRoomsAllotment = TotalRoomsAllotment + (@InitialCount + @TotalChange)
        WHERE RoomTypeNumber=@RoomTypeNumber AND 
			  DateStamp=@DateStamp AND 
			  SegmentNumber=@SegmentNumber AND 
			  PointOfSaleNumber=@PointOfSaleNumber AND 
			  HotelNumber=@HotelNumber AND 
			  SoldRoomType = @ASoldRoomType AND
			  ReservationGroup = 0 
			  OPTION (KEEP PLAN)

        IF @@rowcount = 0 -- Nothing was updated -> insert row!
          INSERT INTO #RoomBookingStatus (DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ReservationGroup, SoldRooms ,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment, SoldPrice)
          VALUES (@DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @ASoldRoomType, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @InitialCount + @TotalChange - @CurrentCount, @InitialCount + @TotalChange, 0)
      END
      ELSE -- IF @SpecifyOnRoomTypeSegPos = 0
      BEGIN
        SET @SegmentNumber = @SingleSegmentNumber
        SET @PointOfSaleNumber = @SinglePointOfSaleNumber

        UPDATE #RoomBookingStatus2 SET
          FreeRoomsAllotment = FreeRoomsAllotment + (@InitialCount + @TotalChange - @CurrentCount),
          TotalRoomsAllotment = TotalRoomsAllotment + (@InitialCount + @TotalChange)
        WHERE DateStamp=@DateStamp AND 
			  HotelNumber=@HotelNumber AND 
			  RoomTypeNumber=@RoomTypeNumber AND 
			  ReservationGroup = 0 OPTION (KEEP PLAN)
        IF @@rowcount = 0 -- Nothing was updated -> insert row!
          INSERT INTO #RoomBookingStatus2 (DateStamp,RoomTypeNumber,HotelNumber, ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment)
          VALUES (@DateStamp,@RoomTypeNumber,@HotelNumber, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @InitialCount + @TotalChange - @CurrentCount, @InitialCount + @TotalChange)
      END
    END -- IF @@FETCH_STATUS <> -2

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

  CLOSE #AllotCur
  DEALLOCATE #AllotCur

select @DADATE=convert(varchar, getdate (),121)
print '1.08: '+@DADATE

IF (@IncludeOccupiedInOFS = 1) AND (@AIncludeOccupiedInOFS = 1)
BEGIN
  IF @SpecifyOnRoomTypeSegPos = 1
	UPDATE #ROOMBOOKINGSTATUS
	SET NumberOfOutForService = NumberOfOutForService + NumberOfNotSaleable
  ELSE
	UPDATE #ROOMBOOKINGSTATUS2
	SET NumberOfOutForService = NumberOfOutForService + NumberOfNotSaleable
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- P U T  D A T A  I N T O  R O O M B O O K I N G S T A T  F O R  P A S T ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- if nightaudit load data from #roombookingstatus into roombookingstatus-table - The roombookingstatus will be locked until transaction has completed!
  IF @NightAudit > 0
  BEGIN
    DECLARE @TotalRoomsAllotment SMALLINT

    IF @TheHotelNumber <> ''
    BEGIN
      DELETE FROM dbo.RoomBookingStatus
      WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate AND CONVERT(DATETIME,DateStamp,112)>=@ReservationsDeletedBefore AND HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers)  -- DO NOT DELETE BOOKINGSTATUS WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS

      DECLARE #RoomBookingStatusCur CURSOR FAST_FORWARD FOR

      SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
      NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms, FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment, NumberOfRoomsSoldToday,
      NumberOfOutOfOrder, NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, SoldPrice --, NumberOfOutOfHouseDayRooms
      FROM #RoomBookingStatus
--      WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND HotelNumber = @TheHotelNumber OPTION (KEEP PLAN)
      WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers) AND DateStamp >= @SystemDateStr OPTION (KEEP PLAN)
    END
    ELSE -- all hotels
    BEGIN
      DELETE FROM dbo.RoomBookingStatus
      WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate  AND CONVERT(DATETIME,DateStamp,112)>=@ReservationsDeletedBefore   -- DO NOT DELETE BOOKINGSTATUS WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS

      DECLARE #RoomBookingStatusCur CURSOR FAST_FORWARD FOR

      SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
      NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment, NumberOfRoomsSoldToday, 
      NumberOfOutOfOrder, NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, SoldPrice--, NumberOfOutOfHouseDayRooms
      FROM #RoomBookingStatus
--      WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate OPTION (KEEP PLAN)
      WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND DateStamp >= @SystemDateStr OPTION (KEEP PLAN)
    END

    Open #RoomBookingStatusCur
    Fetch Next From #RoomBookingStatusCur
    Into @DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRoomType, @ReservationGroup, @SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed,
         @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment, 
         @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder, @NumberOfOutForService, @NumberOfNotSaleable, @NumberOfOccupiedGroup, @SoldPrice--, @NumberOfOutOfHouseDayRooms

    While  (@@FETCH_STATUS <> -1)
    BEGIN
      IF @@FETCH_STATUS <> -2
      BEGIN
        UPDATE dbo.RoomBookingStatus SET
          SoldRooms=@SoldRooms, 
          Adults=@Adults, 
          Children1=@Children1, 
          Children2=@Children2, 
          NumberOfConfirmed=@NumberOfConfirmed, 
          NumberOfProvisionals=@NumberOfProvisionals,
          NumberOfGuaranteed=@NumberOfGuaranteed, 
          NumberOfTentative=@NumberOfTentative, 
          SoldPrice=@SoldPrice,
          NumberOfWaitinglists=@NumberOfWaitinglists, 
          NumberOfDayrooms=@NumberOfDayrooms,
          FreeRoomsAllotment=@FreeRoomsAllotment,
          TotalRoomsAllotment=@TotalRoomsAllotment,
          PaxAllotment=@PaxAllotment,
          NumberOfRoomsSoldToday=@NumberOfRoomsSoldToday,
          NumberOfOutOfOrder=@NumberOfOutOfOrder,
          NumberOfOutForService=@NumberOfOutForService,
          NumberOfNotSaleable=@NumberOfNotSaleable,
		  NumberOfOccupiedGroup= @NumberOfOccupiedGroup
          --, NumberOfOutOfHouseDayRooms=@NumberOfOutOfHouseDayRooms
        WHERE DateStamp=@DateStamp AND 
			  RoomTypeNumber=@RoomTypeNumber AND 
			  SegmentNumber=@SegmentNumber AND 
			  PointOfSaleNumber=@PointOfSaleNumber AND 
			  HotelNumber=@HotelNumber AND 
			  SoldRoomType = @SoldRoomType AND
			  ReservationGroup = @ReservationGroup

        IF @@rowcount = 0
          INSERT INTO dbo.RoomBookingStatus (DateStamp, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, HotelNumber, SoldRoomType, ReservationGroup, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder,NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, SoldPrice)
          VALUES (@DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRoomType, @ReservationGroup, @SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment, @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder,@NumberOfOutForService, @NumberOfNotSaleable, @NumberOfOccupiedGroup, @SoldPrice)
      END

      Fetch Next From #RoomBookingStatusCur
      Into @DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRoomType, @ReservationGroup, @SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed, 
      @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment,
      @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder,@NumberOfOutForService, @NumberOfNotSaleable, @NumberOfOccupiedGroup, @SoldPrice --, @NumberOfOutOfHouseDayRooms
    END

    CLOSE #RoomBookingStatusCur
    DEALLOCATE #RoomBookingStatusCur
  END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- U P D A T E   R O O M B O O K I N G S T A T  D U R I N G  T H E  D A Y ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF @AUpdateRoomBookingStatus = 1
  BEGIN
	DELETE FROM dbo.RoomBookingStatus
	WHERE DateStamp >= @SystemDateStr 
	AND DateStamp IN 
	(SELECT rbs.DateStamp FROM dbo.RoomBookingStatus rbs (NOLOCK)
	 LEFT OUTER JOIN #RoomBookingStatus rbst (NOLOCK) ON rbs.DateStamp = rbst.DateStamp AND 
														 rbs.RoomTypeNumber = rbst.ROomTypeNumber AND
														 rbs.SegmentNumber = rbst.SegmentNumber AND
														 rbs.PointOfSaleNumber = rbst.PointOfSaleNumber AND
														 rbs.HotelNumber = rbst.HotelNumber AND
														 rbs.SoldRoomType = rbst.SoldRoomType AND
														 rbs.ReservationGroup = rbst.ReservationGroup
	 WHERE rbst.DateStamp IS NULL)

	 INSERT INTO dbo.RoomBookingStatus (DateStamp, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, HotelNumber, SoldRoomType, ReservationGroup, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder,NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, SoldPrice)
	 SELECT rbst.DateStamp, rbst.RoomTypeNumber, rbst.SegmentNumber, rbst.PointOfSaleNumber, rbst.HotelNumber, rbst.SoldRoomType, rbst.ReservationGroup, rbst.SoldRooms, rbst.Adults, rbst.Children1, rbst.Children2, rbst.NumberOfConfirmed, rbst.NumberOfProvisionals, rbst.NumberOfGuaranteed, rbst.NumberOfTentative, rbst.NumberOfWaitinglists, rbst.NumberOfDayrooms, rbst.FreeRoomsAllotment, rbst.TotalRoomsAllotment, rbst.PaxAllotment, rbst.NumberOfRoomsSoldToday, rbst.NumberOfOutOfOrder,rbst.NumberOfOutForService, rbst.NumberOfNotSaleable, rbst.NumberOfOccupiedGroup, rbst.SoldPrice
	 FROM #RoomBookingStatus rbst (NOLOCK)
	 LEFT OUTER JOIN dbo.RoomBookingStatus rbs (NOLOCK) ON rbs.DateStamp = rbst.DateStamp AND 
														 rbs.RoomTypeNumber = rbst.ROomTypeNumber AND
														 rbs.SegmentNumber = rbst.SegmentNumber AND
														 rbs.PointOfSaleNumber = rbst.PointOfSaleNumber AND
														 rbs.HotelNumber = rbst.HotelNumber AND
														 rbs.SoldRoomType = rbst.SoldRoomType AND
														 rbs.ReservationGroup = rbst.ReservationGroup
	WHERE rbs.Datestamp IS NULL


	UPDATE dbo.RoomBookingStatus
	SET SoldRooms=rbst.SoldRooms, 
          Adults=rbst.Adults, 
          Children1=rbst.Children1, 
          Children2=rbst.Children2, 
          NumberOfConfirmed=rbst.NumberOfConfirmed, 
          NumberOfProvisionals=rbst.NumberOfProvisionals,
          NumberOfGuaranteed=rbst.NumberOfGuaranteed, 
          NumberOfTentative=rbst.NumberOfTentative, 
          SoldPrice=rbst.SoldPrice,
          NumberOfWaitinglists=rbst.NumberOfWaitinglists, 
          NumberOfDayrooms=rbst.NumberOfDayrooms,
          FreeRoomsAllotment=rbst.FreeRoomsAllotment,
          TotalRoomsAllotment=rbst.TotalRoomsAllotment,
          PaxAllotment=rbst.PaxAllotment,
          NumberOfRoomsSoldToday=rbst.NumberOfRoomsSoldToday,
          NumberOfOutOfOrder=rbst.NumberOfOutOfOrder,
          NumberOfOutForService=rbst.NumberOfOutForService,
          NumberOfNotSaleable=rbst.NumberOfNotSaleable,
		  NumberOfOccupiedGroup= rbst.NumberOfOccupiedGroup
	FROM #RoomBookingStatus rbst
	INNER JOIN dbo.RoomBookingStatus rbs (NOLOCK) ON rbs.DateStamp = rbst.DateStamp AND 
														 rbs.RoomTypeNumber = rbst.ROomTypeNumber AND
														 rbs.SegmentNumber = rbst.SegmentNumber AND
														 rbs.PointOfSaleNumber = rbst.PointOfSaleNumber AND
														 rbs.HotelNumber = rbst.HotelNumber AND
														 rbs.SoldRoomType = rbst.SoldRoomType AND
														 rbs.ReservationGroup = rbst.ReservationGroup
  END

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- P U T  Y E S T E R D A Y S  B O O K I N G S T A T U S   I N T O  O T H E R   T A B L E ------     I N S A L E     ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select @DADATE=convert(varchar, getdate (),121)
print '1.09: '+@DADATE

  IF (@NightAudit > 0) AND (@FormatedEndDate > @RealSystemDateStr) -- not from regeneratebookingstatus sp
  BEGIN
    IF @TheHotelNumber = '' OR NOT EXISTS (SELECT * FROM #HotelNumbers WHERE number <> 0) -- only drop table when localhotel or all hotels
      DELETE FROM dbo.RoomBookingStatusInsale WHERE CONVERT(DATETIME,DateStamp,112)>=@ReservationsDeletedBefore   -- DO NOT DELETE BOOKINGSTATUS WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS
    ELSE
      DELETE FROM dbo.RoomBookingStatusInsale WHERE HotelNumber IN (select distinct number FROM #HotelNumbers) AND CONVERT(DATETIME,DateStamp,112)>=@ReservationsDeletedBefore   -- DO NOT DELETE BOOKINGSTATUS WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS

    IF @TheHotelNumber <> ''
    BEGIN
      DECLARE #StatusCopyCur CURSOR FAST_FORWARD FOR

        SELECT DateStamp, IsNull(HotelNumber, 0), SoldRoomType, SoldRooms,Adults,Children1,Children2,SoldPrice,TotalRoomsAllotment FROM #RoomBookingStatus (NOLOCK)
        WHERE HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers) OPTION (KEEP PLAN)
    END
    ELSE -- all hotels
    BEGIN
      DECLARE #StatusCopyCur CURSOR FAST_FORWARD FOR

        SELECT DateStamp, IsNull(HotelNumber, 0),SoldRoomType, SoldRooms,Adults,Children1,Children2,SoldPrice,TotalRoomsAllotment FROM #RoomBookingStatus (NOLOCK) OPTION (KEEP PLAN)
    END

    OPEN #StatusCopyCur
    FETCH NEXT FROM #StatusCopyCur
    INTO @DateStamp, @HotelNumber, @SoldRoomType, @SoldRooms, @Adults, @Children1, @Children2, @SoldPrice, @TotalRoomsAllotment

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF @@FETCH_STATUS <> -2
      BEGIN
        IF @IncludeAllotmentInInsale = 0
          SET @TotalRoomsAllotment = 0

        IF (@SoldRooms+@TotalRoomsAllotment > 0) OR (@SoldPrice > 0)  -- no need to do anything if no number
        BEGIN
          UPDATE dbo.RoomBookingStatusInsale SET SoldRooms=SoldRooms+@SoldRooms+@TotalRoomsAllotment, SoldPrice=SoldPrice+@SoldPrice 
          WHERE DateStamp=@DateStamp AND HotelNumber=@HotelNumber AND SoldRoomType = @SoldRoomType
          IF @@rowcount = 0
            INSERT INTO dbo.RoomBookingStatusInsale (DateStamp,HotelNumber, SoldRoomType, SoldRooms,SoldPrice)
            VALUES (@DateStamp, @HotelNumber, @SoldRoomType, @SoldRooms+@TotalRoomsAllotment,@SoldPrice)
        END
      END	

      FETCH NEXT FROM #StatusCopyCur
      INTO @DateStamp, @HotelNumber, @SoldRoomType, @SoldRooms, @Adults, @Children1, @Children2, @SoldPrice, @TotalRoomsAllotment
    END

    CLOSE #StatusCopyCur
    DEALLOCATE #StatusCopyCur

    -- Fixes problem in product search
    UPDATE Products SET MinimumDays=0 WHERE MinimumDays IS NULL
    UPDATE Products SET MaximumDays=0 WHERE MaximumDays IS NULL
  END
  DROP TABLE #ProcessedRoomlines		
--  DROP TABLE #AllotmentLines -- not actually needed
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
END -- IF @FormatedEndDate >= @SystemDateStr

select @DADATE=convert(varchar, getdate (),121)
print '1.10: '+@DADATE

if @NightAudit = 0 AND @AUpdateRoomBookingStatus = 0
BEGIN
  IF @FormatedStartDate<@SystemDateStr
  BEGIN
    IF @FormatedEndDate<@SystemDateStr
    BEGIN
      SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRoomType,ReservationGroup,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
      NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
      NumberOfOutOfOrder,NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, SoldPrice 
      FROM dbo.RoomBookingStatus (NOLOCK)
      WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate  AND (@TheHotelNumber='' OR ISNULL(HotelNumber,0) IN (SELECT DISTINCT Number FROM #HotelNumbers))
      AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
      AND (@ASoldRoomType = 2 OR SoldRoomType = @ASoldRoomType)
      ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
    END
    ELSE
    BEGIN
      SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
      NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
      NumberOfOutOfOrder,NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, SoldPrice 
      FROM #RoomBookingStatus (NOLOCK)   -- So that the SP can used as a query                                                                                                                    -- same as below
      WHERE DateStamp BETWEEN @SystemDateStr AND @FormatedEndDate AND (@TheHotelNumber='' OR ISNULL(HotelNumber, 0) IN (SELECT DISTINCT Number FROM #HotelNumbers))
      AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
      AND (@ASoldRoomType = 2 OR SoldRoomType = @ASoldRoomType)
      UNION
      SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRoomType,ReservationGroup,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
      NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
      NumberOfOutOfOrder,NumberOfOutForService, NumberOfNotSaleable, NumberOfOccupiedGroup, SoldPrice 
      FROM dbo.RoomBookingStatus (NOLOCK)
      WHERE DateStamp < @SystemDateStr AND DateStamp >= @FormatedStartDate AND (@TheHotelNumber='' OR  ISNULL(HotelNumber, 0) IN (SELECT DISTINCT Number FROM #HotelNumbers))
      AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
      AND (@ASoldRoomType = 2 OR SoldRoomType = @ASoldRoomType)
      ORDER BY HotelNumber,DateStamp -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
--      DROP TABLE #ROOMBOOKINGSTATUS  -- not actually needed
    END
  END
  ELSE IF @SpecifyOnRoomTypeSegPos = 1
  BEGIN
    SELECT * FROM #RoomBookingStatus (NOLOCK)   -- So that the SP can used as a query                                                                                                                    -- same as above
    WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate AND (@TheHotelNumber='' OR  ISNULL(HotelNumber, 0) IN (SELECT DISTINCT Number FROM #HotelNumbers))
    AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
    AND (@ASoldRoomType = 2 OR SoldRoomType = @ASoldRoomType)
    ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
--    DROP TABLE #ROOMBOOKINGSTATUS  -- not actually needed
  END
  ELSE -- NOT @SpecifyOnRoomTypeSegPos
  BEGIN
    SELECT * FROM #RoomBookingStatus2 (NOLOCK)
    WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate AND (@TheHotelNumber='' OR ISNULL(HotelNumber, 0) IN (SELECT DISTINCT Number FROM #HotelNumbers))
    AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
    ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
--    DROP TABLE #ROOMBOOKINGSTATUS2  -- not actually needed
  END
END

/*
IF ISNULL(@NightAudit, 0) = 1
BEGIN
  DELETE Availability WHERE Date = @StartDate
  INSERT INTO Availability
  SELECT @StartDate AS Date, DateStamp AS DateSales, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, HotelNumber, SUM(SoldRooms) AS SoldRoom, 
    SUM(SoldPrice) AS SoldPrice, SUM(Adults) AS Adult, SUM(Children1) AS Children1, SUM(Children2) AS Children2
  FROM #RoomBookingStatus (NOLOCK)
  GROUP BY DateStamp, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, HotelNumber
END
*/



select @DADATE=convert(varchar, getdate (),121)
print '2.00: '+@DADATE

-- BA/090401: CR007579: Try to aviod Lock time out when opening the Roomtypes plan
DECLARE @SqlStr VARCHAR(25)
SET @SqlStr = 'SET LOCK_TIMEOUT '+ CONVERT(VARCHAR, @LockTimeOut)
EXEC (@SqlStr)