CREATE PROCEDURE dbo.FixProductAddonAndPacket @AProductNumber INT, @AType INT AS
--Created AV 2008 1118 used for fixing addons if records are empty
-- Type 1= arrival 2=stay = 3 departure
  DECLARE  @DateStamp VARCHAR(8)
  DECLARE @ThePrice SMALLMONEY
  DECLARE @AdultArrivalAddon SMALLMONEY
  DECLARE @AdultStayAddon SMALLMONEY
  DECLARE @AdultDepartureAddon SMALLMONEY
  DECLARE @ArrivalPacketNumber INT
  DECLARE @StayPacketNumber INT
  DECLARE @DeparturePacketNumber INT
  DECLARE @SystemDate CHAR(8)
  DECLARE @RoomTypeCount INT
  DECLARE @CurrentRoomTypeCount INT
  DECLARE @RoomTypeNumber INT
  SELECT @SystemDate = SystemDate FROM System WITH (NOLOCK)
  SELECT @RoomTypeCount = Count(*) FROM RoomTypes WITH (NOLOCK)

  DECLARE RoomTypeCur CURSOR LOCAL SCROLL FOR
  SELECT RoomTypeNumber FROM RoomTypes WITH (NOLOCK)
  WHERE dbo.ProductHotelNumber(@AProductNumber, CONVERT(VARCHAR(2), HotelNumber),1) = 1
  OPEN RoomTypeCur


  DECLARE UpdateCur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DISTINCT DateStamp FROM ProductPrice WITH (NOLOCK) WHERE 
  ProductNumber = @AProductNumber 
  AND
  (
    (@AType = 1 AND ISNULL(AdultArrivalAddon, 0) > 0)
    OR (@AType = 2 AND ISNULL(AdultStayAddon, 0) > 0)
    OR (@AType = 3 AND ISNULL(AdultDepartureAddon, 0) > 0)
  )

  AND datestamp >= @SystemDate
  ORDER BY DateStamp

  OPEN UpdateCur
  
  FETCH NEXT FROM UpdateCur INTO @DateStamp
 
  While(@@Fetch_status=0)
  BEGIN
      SET @AdultArrivalAddon = 0
      SET @AdultStayAddon = 0
      SET @AdultDepartureAddon = 0
      SET @ArrivalPacketNumber = NULL
      SET  @StayPacketNumber = NULL
      SET @DeparturePacketNumber = NULL

    --Below section is used when all the correct roomtypes record exists, but there are NULL values in packet or Addon
    SET @ThePrice = 0
    IF @AType = 1
    BEGIN
      SELECT @ThePrice = ISNULL(AdultArrivalAddon, 0), @ArrivalPacketNumber = ArrivalPacketNumber, @StayPacketNumber = StayPacketNumber, @DeparturePacketNumber = DeparturePacketNumber FROM ProductPrice WITH (nolock) where ProductNumber = @AProductNumber AND DateStamp = @DateStamp and
      ISNULL(AdultArrivalAddon, 0) <> 0
      IF @ThePrice <> 0
      BEGIN
        UPDATE ProductPrice SET AdultArrivalAddon = @ThePrice WHERE ProductNumber = @AProductNumber AND DateStamp = @DateStamp AND ISNULL(AdultArrivalAddon, 0) = 0
        SET @AdultArrivalAddon = @ThePrice
        SET @AdultStayAddon = 0
        SET @AdultDepartureAddon = 0  
      END
    END
    ELSE IF @AType = 2
    BEGIN
      SELECT @ThePrice = ISNULL(AdultStayAddon, 0),@ArrivalPacketNumber = ArrivalPacketNumber, @StayPacketNumber = StayPacketNumber, @DeparturePacketNumber = DeparturePacketNumber  FROM ProductPrice WITH (nolock) where ProductNumber = @AProductNumber AND DateStamp = @DateStamp and
      ISNULL(AdultStayAddon, 0) <> 0
      IF @ThePrice <> 0
      BEGIN
        UPDATE ProductPrice SET AdultStayAddon = @ThePrice WHERE ProductNumber = @AProductNumber AND DateStamp = @DateStamp  AND ISNULL(AdultStayAddon, 0) = 0
        SET @AdultArrivalAddon = 0
        SET @AdultStayAddon = @ThePrice
        SET @AdultDepartureAddon = 0
      END 
    END
    ELSE IF @AType = 3
    BEGIN
      SELECT @ThePrice = ISNULL(AdultdepartureAddon, 0),@ArrivalPacketNumber = ArrivalPacketNumber, @StayPacketNumber = StayPacketNumber, @DeparturePacketNumber = DeparturePacketNumber  FROM ProductPrice WITH (nolock) where ProductNumber = @AProductNumber AND DateStamp = @DateStamp and
      ISNULL(AdultdepartureAddon, 0) <> 0
      IF @ThePrice <> 0
      BEGIN
        UPDATE ProductPrice SET AdultdepartureAddon = @ThePrice WHERE ProductNumber = @AProductNumber AND DateStamp = @DateStamp  AND ISNULL(AdultDepartureAddon, 0) = 0
        SET @AdultArrivalAddon = 0
        SET @AdultStayAddon = 0
        SET @AdultDepartureAddon = @ThePrice
      END 
    END

    --Below section is used when all the correct roomtypes record exists does not exists, so it will create them if the roomtype is placed on the same hotel as the product we are dealing with
    SELECT @CurrentRoomTypeCount = Count(*) FROM ProductPrice WITH (NOLOCK) WHERE ProductNumber = @AProductNumber AND DateStamp = @DateStamp
    IF @CurrentRoomTypeCount < @RoomTypeCount
    BEGIN
      FETCH FIRST FROM RoomTypeCur INTO @RoomTypeNumber
      WHILE(@@Fetch_status=0)
      BEGIN
        IF NOT EXISTS(SELECT RoomTypeNumber FROM ProductPrice WITH (NOLOCK) WHERE ProductNumber = @AProductNumber AND DateStamp = @DateStamp AND RoomTypeNumber = @RoomTypeNumber)
        BEGIN
            Insert Into ProductPrice
           (ProductNumber, DateStamp, RoomtypeNumber, AdultPrice1, AdultPrice2, AdultPrice3, AdultPrice4, AdultPrice5, AdultPrice6, AdultPrice7, AdultPrice8, AdultPrice9,
           Children1Price1, Children1Price2, Children1Price3, Children1Price4, Children1Price5, Children1Price6, Children1Price7, Children1Price8, Children1Price9,
           Children2Price1, Children2Price2, Children2Price3, Children2Price4, Children2Price5, Children2Price6, Children2Price7, Children2Price8, Children2Price9,
           AdultArrivalAddon, AdultStayAddon, AdultDepartureAddon,Children1ArrivalAddon,Children1StayAddon, Children1DepartureAddon,Children2ArrivalAddon,Children2StayAddon, Children2DepartureAddon,
           AdultArrivalAddon2, AdultStayAddon2, AdultDepartureAddon2, Children1ArrivalAddon2, Children1StayAddon2, Children1DepartureAddon2, Children2ArrivalAddon2, Children2StayAddon2, Children2DepartureAddon2,
           AdultArrivalAddon3, AdultStayAddon3, AdultDepartureAddon3, Children1ArrivalAddon3, Children1StayAddon3, Children1DepartureAddon3, Children2ArrivalAddon3, Children2StayAddon3, Children2DepartureAddon3,
           AdultArrivalAddon4, AdultStayAddon4, AdultDepartureAddon4, Children1ArrivalAddon4, Children1StayAddon4, Children1DepartureAddon4, Children2ArrivalAddon4, Children2StayAddon4, Children2DepartureAddon4,
           AdultArrivalAddon5, AdultStayAddon5, AdultDepartureAddon5, Children1ArrivalAddon5, Children1StayAddon5, Children1DepartureAddon5, Children2ArrivalAddon5, Children2StayAddon5, Children2DepartureAddon5,
           AdultArrivalAddon6, AdultStayAddon6, AdultDepartureAddon6, Children1ArrivalAddon6, Children1StayAddon6, Children1DepartureAddon6, Children2ArrivalAddon6, Children2StayAddon6, Children2DepartureAddon6,
           AdultArrivalAddon7, AdultStayAddon7, AdultDepartureAddon7, Children1ArrivalAddon7, Children1StayAddon7, Children1DepartureAddon7, Children2ArrivalAddon7, Children2StayAddon7, Children2DepartureAddon7,
           AdultArrivalAddon8, AdultStayAddon8, AdultDepartureAddon8, Children1ArrivalAddon8, Children1StayAddon8, Children1DepartureAddon8, Children2ArrivalAddon8, Children2StayAddon8, Children2DepartureAddon8,
           AdultArrivalAddon9, AdultStayAddon9, AdultDepartureAddon9, Children1ArrivalAddon9, Children1StayAddon9, Children1DepartureAddon9, Children2ArrivalAddon9, Children2StayAddon9, Children2DepartureAddon9,
           ArrivalPacketNumber,StayPacketNumber, DeparturePacketNumber)
           Values (@AProductNumber, @DateStamp, @RoomtypeNumber, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,@AdultArrivalAddon, @AdultStayAddon, @AdultDepartureAddon,0,0, 0,0,0, 0,
                     0, 0, 0, 0, 0, 0, 0, 0, 0,0, 0, 0, 0, 0, 0, 0, 0, 0,0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,0, 0, 0, 0, 0, 0, 0, 0, 0,0, 0, 0, 0, 0, 0, 0, 0, 0, @ArrivalPacketNumber, @StayPacketNumber, @DeparturePacketNumber)
        END 
        FETCH NEXT FROM RoomTypeCur INTO @RoomTypeNumber
      END

    END 

    FETCH NEXT FROM UpdateCur INTO @datestamp
  END

  CLOSE RoomTypeCur
  DEALLOCATE RoomTypeCur

  CLOSE UpdateCur
  DEALLOCATE UpdateCur