CREATE PROCEDURE dbo.CopyDayToPeriod @OldProductNumber int, @SourceDate datetime, @DestStartDate datetime, @DestEndDate datetime, @HotelNumber INT = -1 AS
SET NOCOUNT ON
/*

  Last changed: 
  KJ 20141219: Ext: Changed datatype for prices. Handle amount > 214.000.
  KJ 20141020: Bug: Fix for "Package info not copied correctly" Bug #224
  KJ 20140312: Bug: Fix for "Can not isert the value NULL into PacketNumber".
  KJ 20131014: ProductPrice Rewrite.
  AV 2007-11-29 FIX for empty records when copying day to period dayproducts. 
  JP 2007-06-13 EXT: added HotelNumber parameter to support copying only of data for a specified hotel. Default -1 means all hotels (backward compatible)
  JP 2005-05-02 changed money fields to smallmoney. removed ProductPriceNumber
  JP 2004-12-06 only open copycur dataset once!  00:44 -> 00:37 in test
  Jacob Pedersen 26-11-2002

  OPTIMIZE: investigate wether deleting only the records not found on sourcedate and then try to make an update and if @@rowcount=0 then an insert. might be faster!?    DONE: DID NOT MAKE IT FASTER

  TEST:
exec CopyDayToPeriodOld  6, '20041206', '20041206', '20061206'  -- old tog 00:44
exec CopyDayToPeriod  6, '20041206', '20041206', '20061206'  -- old tog 00:37
*/

--DECLARE @DADATE VARCHAR(255)
--select @DADATE=convert(varchar, getdate (),121)
--print '1.0: '+@DADATE

-- Declare all fields in ProductPrice
DECLARE
@ProductNumber INT,
@DateStamp CHAR(8),
@RoomtypeNumber INT,
@AdultPrice1 money,
@AdultPrice2 money,
@AdultPrice3 money,
@AdultPrice4 money,
@AdultPrice5 money,
@AdultPrice6 money,
@AdultPrice7 money,
@AdultPrice8 money,
@AdultPrice9 money,
@Children1Price1 money,
@Children1Price2 money,
@Children1Price3 money,
@Children1Price4 money,
@Children1Price5 money,
@Children1Price6 money,
@Children1Price7 money,
@Children1Price8 money,
@Children1Price9 money,
@Children2Price1 money,
@Children2Price2 money,
@Children2Price3 money,
@Children2Price4 money,
@Children2Price5 money,
@Children2Price6 money,
@Children2Price7 money,
@Children2Price8 money,
@Children2Price9 money,
@AdultArrivalAddon smallmoney,
@AdultStayAddon smallmoney,
@AdultDepartureAddon smallmoney,
@Children1ArrivalAddon smallmoney,
@Children1StayAddon smallmoney,
@Children1DepartureAddon smallmoney,
@Children2ArrivalAddon smallmoney,
@Children2StayAddon smallmoney,
@Children2DepartureAddon smallmoney,

@AdultArrivalAddon2 smallmoney,
@AdultStayAddon2 smallmoney,
@AdultDepartureAddon2 smallmoney,
@Children1ArrivalAddon2 smallmoney,
@Children1StayAddon2 smallmoney,
@Children1DepartureAddon2 smallmoney,
@Children2ArrivalAddon2 smallmoney,
@Children2StayAddon2 smallmoney,
@Children2DepartureAddon2 smallmoney,
@AdultArrivalAddon3 smallmoney,
@AdultStayAddon3 smallmoney,
@AdultDepartureAddon3 smallmoney,
@Children1ArrivalAddon3 smallmoney,
@Children1StayAddon3 smallmoney,
@Children1DepartureAddon3 smallmoney,
@Children2ArrivalAddon3 smallmoney,
@Children2StayAddon3 smallmoney,
@Children2DepartureAddon3 smallmoney,
@AdultArrivalAddon4 smallmoney,
@AdultStayAddon4 smallmoney,
@AdultDepartureAddon4 smallmoney,
@Children1ArrivalAddon4 smallmoney,
@Children1StayAddon4 smallmoney,
@Children1DepartureAddon4 smallmoney,
@Children2ArrivalAddon4 smallmoney,
@Children2StayAddon4 smallmoney,
@Children2DepartureAddon4 smallmoney,
@AdultArrivalAddon5 smallmoney,
@AdultStayAddon5 smallmoney,
@AdultDepartureAddon5 smallmoney,
@Children1ArrivalAddon5 smallmoney,
@Children1StayAddon5 smallmoney,
@Children1DepartureAddon5 smallmoney,
@Children2ArrivalAddon5 smallmoney,
@Children2StayAddon5 smallmoney,
@Children2DepartureAddon5 smallmoney,
@AdultArrivalAddon6 smallmoney,
@AdultStayAddon6 smallmoney,
@AdultDepartureAddon6 smallmoney,
@Children1ArrivalAddon6 smallmoney,
@Children1StayAddon6 smallmoney,
@Children1DepartureAddon6 smallmoney,
@Children2ArrivalAddon6 smallmoney,
@Children2StayAddon6 smallmoney,
@Children2DepartureAddon6 smallmoney,
@AdultArrivalAddon7 smallmoney,
@AdultStayAddon7 smallmoney,
@AdultDepartureAddon7 smallmoney,
@Children1ArrivalAddon7 smallmoney,
@Children1StayAddon7 smallmoney,
@Children1DepartureAddon7 smallmoney,
@Children2ArrivalAddon7 smallmoney,
@Children2StayAddon7 smallmoney,
@Children2DepartureAddon7 smallmoney,
@AdultArrivalAddon8 smallmoney,
@AdultStayAddon8 smallmoney,

@AdultDepartureAddon8 smallmoney,
@Children1ArrivalAddon8 smallmoney,
@Children1StayAddon8 smallmoney,
@Children1DepartureAddon8 smallmoney,
@Children2ArrivalAddon8 smallmoney,
@Children2StayAddon8 smallmoney,
@Children2DepartureAddon8 smallmoney,
@AdultArrivalAddon9 smallmoney,
@AdultStayAddon9 smallmoney,
@AdultDepartureAddon9 smallmoney,
@Children1ArrivalAddon9 smallmoney,
@Children1StayAddon9 smallmoney,
@Children1DepartureAddon9 smallmoney,
@Children2ArrivalAddon9 smallmoney,
@Children2StayAddon9 smallmoney,
@Children2DepartureAddon9 smallmoney,

@PacketType INT,
@PacketNumber INT,
@PacketStartDate DATETIME,
@PacketEndDate DATETIME,
@FormatedSourceDate CHAR(8),
@CurrentDate CHAR(8),
@EndDate CHAR(8),
@StoreDestStartDate DATETIME,
@StoreDestEndDate DATETIME,
@IsDayProduct BIT,
@PricePerType BIT

SET @StoreDestStartDate = @DestStartDate
SET @StoreDestEndDate = @DestEndDate

SET @CurrentDate = Convert (CHAR(8), @DestStartDate, 112)
SET @FormatedSourceDate = Convert (CHAR(8), @SourceDate, 112)
SET @EndDate = Convert (CHAR(8), @DestEndDate, 112)

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

SELECT @IsDayProduct = ISNULL(DayProduct, 0), @PricePerType = ISNULL(PricePerType, 0) FROM Products WITH (NOLOCK) WHERE ProductNumber = @OldProductNumber

IF @IsDayProduct = 0
BEGIN
  DELETE ProductPrice FROM ProductPrice WITH (ROWLOCK)
  INNER JOIN RoomTypes WITH (NOLOCK) ON ProductNumber=@OldProductNumber AND ProductPrice.RoomTypeNumber=RoomTypes.RoomTypeNumber AND (@HotelNumber=-1 OR ISNULL(Roomtypes.HotelNumber, 0)=@HotelNumber)
  AND DateStamp BETWEEN @CurrentDate AND @EndDate AND DateStamp <> @FormatedSourceDate
END
ELSE
BEGIN
  DELETE ProductPrice FROM ProductPrice WITH (ROWLOCK)
  WHERE  ProductNumber=@OldProductNumber 
  AND DateStamp BETWEEN @CurrentDate AND @EndDate AND DateStamp <> @FormatedSourceDate
END

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

DECLARE CopyCur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT DISTINCT ProductNumber, DateStamp, ProductPrice.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
  FROM ProductPrice WITH (NOLOCK)
  LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON  ProductPrice.RoomTypeNumber=RoomTypes.RoomTypeNumber AND (@HotelNumber=-1 OR ISNULL(Roomtypes.HotelNumber, 0)=@HotelNumber)
  WHERE DateStamp = @FormatedSourceDate 
  AND ProductNumber = @OldProductNumber 
  AND (@IsDayProduct = 1 OR (@IsDayProduct = 0 AND RoomTypes.RoomTypeNumber IS NOT NULL))

OPEN CopyCur
FETCH NEXT FROM CopyCur INTO @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

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @DestStartDate = @StoreDestStartDate 
  SET @CurrentDate = Convert (CHAR(8), @DestStartDate, 112)

  WHILE (@CurrentDate <= @EndDate) 
  BEGIN
    IF (@CurrentDate <> @FormatedSourceDate)
    BEGIN
      -- Insert with NEW date every record from sourcedate
--print @currentdate
      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
      )
      VALUES (@ProductNumber, @CurrentDate, @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
      )
      END
    SET @DestStartDate = @DestStartDate +1
    SET @CurrentDate = Convert (CHAR(8), @DestStartDate, 112)
  END

  FETCH NEXT FROM CopyCur
  INTO @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
END

--Deallocate Cursor
CLOSE CopyCur
DEALLOCATE CopyCur

--select @DADATE=convert(varchar, getdate (),121)
--print '1.3: '+@DADATE
DECLARE @PackageType INT

SET @PackageType = 0 
WHILE @PackageType <= 2
BEGIN
  SET @PacketNumber = -1

  SELECT TOP 1 @PacketNumber = FK_PacketNumber
  FROM ProductPricePacket WITH (NOLOCK)
  WHERE StartDate <= @FormatedSourceDate 
  AND EndDate >= @FormatedSourceDate 
  AND FK_ProductNumber = @OldProductNumber 
  AND PacketType = @PackageType
  ORDER BY Id DESC
 
  --IF ISNULL(@PacketNumber, 0) > 0
    IF ISNULL(@ProductNumber, 0) > 0
      INSERT INTO ProductPricePacket (FK_ProductNumber, PacketType, StartDate, EndDate, FK_PacketNumber) 
      VALUES (@ProductNumber, @PackageType, @StoreDestStartDate, @StoreDestEndDate, ISNULL(@PacketNumber, 0))
 
  SET @PackageType = @PackageType + 1  
END