CREATE PROCEDURE DBO.GenerateAvailabilityNight @StartDate DATETIME, @EndDate DATETIME, @TheHotelNumber INTEGER, @RealSystemDateStr CHAR (8) = '', @SpecifyOnRoomTypeSegPos BIT = 1 AS
SET NOCOUNT ON
SET lock_timeout 50000  -- give updates and inserts a chance to complete!

/* Setup Variables */
DECLARE
@FormatedStartDate CHAR(8),
@FormatedEndDate CHAR(8),
@SystemDateStr CHAR(8)

/* Fill Variables */
SET @FormatedStartDate =  CONVERT(CHAR (8), @StartDate, 112)
SET @FormatedEndDate =  CONVERT(CHAR (8), @EndDate, 112)

-- Check if StartDate or EndDate before Systemdate -> change!
SELECT @SystemDateStr = SYSTEMDATE FROM System  WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record
IF @RealSystemDateStr = ''
  SET @RealSystemDateStr = @SystemDateStr -- for allotment release and dayrooms

/* Call sub procedures */
--EXEC GA_CreateTempTables @SpecifyOnRoomTypeSegPos  -- TEMP Tables cannot be created in a sub procedure :(
CREATE TABLE #AllotmentLines (
	AllotmentNumber INT,
	RoomTypeNumber INT,
	DateStamp CHAR (8) COLLATE DATABASE_DEFAULT NOT NULL, -- DO NOT REMOVE COLLATION!
	CurrentCount SMALLINT
)
/*
CREATE UNIQUE INDEX #pk_allotmentlines ON #AllotmentLines(
        [AllotmentNumber],
        [RoomTypeNumber],
        [DateStamp]
) WITH FILLFACTOR=66
*/

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 ,
	[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,
	[SoldPrice] [MONEY] NULL
--  		ON [PRIMARY]
  )
/*
	CREATE UNIQUE INDEX [#PK_ROOMBOOKINGSTATUS] ON #ROOMBOOKINGSTATUS(
		[DateStamp],
		[RoomTypeNumber],
		[SegmentNumber],
		[PointOfSaleNumber],
		[HotelNumber]
		) WITH FILLFACTOR=66
*/
END
ELSE
BEGIN
  CREATE TABLE #ROOMBOOKINGSTATUS2 (
	[DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
	[RoomTypeNumber] [int] NOT NULL ,
	[HotelNumber] [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,
	[SoldPrice] [MONEY] NULL
--	ON [PRIMARY]
  )
/*
  CREATE UNIQUE INDEX [#PK_ROOMBOOKINGSTATUS2] ON #ROOMBOOKINGSTATUS2(
	[DateStamp],
	[RoomTypeNumber],
	[HotelNumber]
  ) WITH FILLFACTOR=66
*/
END

EXEC GA_FixAllotment @FormatedStartDate, @FormatedEndDate

EXEC GA_FillTempTables @SpecifyOnRoomTypeSegPos, @FormatedStartDate, @FormatedEndDate, @RealSystemDateStr, @TheHotelNumber

EXEC GA_AllotmentInsert @FormatedStartDate, @FormatedEndDate, @SystemDateStr, @TheHotelNumber, 1  -- 1: nightaudit

EXEC GA_AllotmentRelease @SystemDateStr, 1  -- 1: nightaudit

EXEC GA_AllotmentIntoBookingStatus @SpecifyOnRoomTypeSegPos, @FormatedStartDate, @FormatedEndDate, @TheHotelNumber

EXEC GA_BookStatusIntoPast @FormatedStartDate, @FormatedEndDate, @TheHotelNumber

EXEC GA_Insale @TheHotelNumber

EXEC GA_DropTempTables @SpecifyOnRoomTypeSegPos

SET lock_timeout 0

