CREATE PROCEDURE DBO.CopyProduct @OldProductNumber int, @NewProductNumber int, @NewProductName VarChar (50), @NewDescription VarChar (50) AS
SET NOCOUNT ON
/*
  Last Changed:
  RN 20171205: Optimized performance for SQL 2016
  KJ 20141219: Ext: Changed datatype for prices. Handle amount > 214.000.
  RN 20140910: Bug: Added missing ProductHotelXTAB copying
  KJ 20140828: Bug: Fix for "Var @DateStamp already declared"
  KJ 20140828: Bug: Fix for "Sp missing in db after update".
  KJ 20131014: ProductPrice Rewrite.
  2008 1208  AV BUG: product start date must be systemdate if it was smaller than this one
  20060307 JP BUG: fixed productprice not using an index and copied too much data. fixed indexes on xtabs
  20060307 JP Added missing fields to Products, copy only future prices and not past.
  20050502 JP money to smallmoney and deleted productpricenumber
--  Sidst rettet 231002 AV - varchar til moeny
-- Sidst rettet af JP 26-11-2002

*/
--- Product ---
DECLARE
@ProductNumber INT,
@MarkAsDeleted BIT,
@ProductName VarChar (50),
@Description VarChar (50),
@ProductDate Char (8),
@ProductEndDate Char (8),
@DayProduct Bit,
@FixedPrice Bit,
@PriceOverwrite Bit,
@PensionTypeNumber Int,
@BillTypeNumber Int,
@ChangeOfBedSheets Int,
@ProductTypeNumber Int,
@ProductSubtypeNumber Int,
@CancellationFee Real,
@EnvironmentFee Real,
@EnvironmentFeeType SmallInt,
@CurrencyNumber Int,
@MinimumDays Int,
@MaximumDays Int,
@ArrivalMonday Bit,
@ArrivalTuesday Bit,
@ArrivalWednesday Bit,
@ArrivalThursday Bit,
@ArrivalFriday Bit,
@ArrivalSaturday Bit,
@ArrivalSunday Bit,
@NameListSetting INT,
@AllowFilterAvailability BIT,
@AllowFilterPicassoOnline BIT,
@AllowFilterCustomerNumber BIT,
@AllowFilterCustomerGroup BIT,
@AllowFilterOtherBookSystem BIT,
@UseMinMaxDaysOnlyOnline BIT,
@MatchDayColor INT,
@OwnerSettlement BIT,

@BillTextProduct VarChar (50),
@BillTextArrival VarChar (50),
@BillTextStay VarChar (50),
@BillTextDeparture VarChar (50),
@ExcludeVAT bit,
@AddonType smallint,
@SystemDateStr CHAR(8),
@oldProductEndDate CHAR(8)

SELECT @SystemDateStr = SystemDate FROM System WITH (NOLOCK) WHERE Number=1
SELECT @ProductEndDate = ProductEndDate FROM Products WITH (NOLOCK) WHERE ProductNumber = @OldProductNumber
SELECT @ProductDate = ProductDate FROM Products WITH (NOLOCK) WHERE ProductNumber = @OldProductNumber

IF @ProductDate < @SystemDateStr
  SET @ProductDate = @SystemDateStr

DECLARE @CurrentPacketType SMALLINT
DECLARE @CurrentPacketNumber INT
DECLARE @CurrentDateStamp DATETIME
DECLARE @PacketStartDate DATETIME
DECLARE @PacketType SMALLINT
DECLARE @PackageDateStamp DATETIME
DECLARE @FK_PacketNumber INT
DECLARE @InsertExtra BIT
DECLARE @FirstRecord BIT

SELECT @MarkAsDeleted=MarkAsDeleted, @ProductName=ProductName, @Description=Description, @ProductDate=ProductDate, @ProductEndDate=ProductEndDate, @DayProduct=DayProduct, @FixedPrice=FixedPrice,
@PriceOverwrite=PriceOverwrite, @PensionTypeNumber=PensionTypeNumber, @BillTypeNumber=BillTypeNumber, @ChangeOfBedSheets=ChangeOfBedSheets, @ProductTypeNumber=ProductTypeNumber, @ProductSubTypeNumber=ProductSubtypeNumber,
@CancellationFee=CancellationFee, @EnvironmentFee=EnvironmentFee, @EnvironmentFeeType=EnvironmentFeeType, /*@CurrencyNumber=CurrencyNumber,*/ @MinimumDays=MinimumDays, @MaximumDays=MaximumDays, @ArrivalMonday=ArrivalMonday, @ArrivalTuesDay=ArrivalTuesday, @ArrivalWednesDay=ArrivalWednesDay,
@ArrivalThursday=ArrivalThursday, @ArrivalFriday=ArrivalFriday, @ArrivalSaturday=ArrivalSaturday, @ArrivalSunday=ArrivalSunday, 
@BillTextProduct=BillTextProduct, @BillTextArrival=BillTextArrival, @BillTextStay=BillTextStay, @BillTextDeparture=BillTextDeparture, @ExcludeVAT=ExcludeVAT,@AddonType=AddonType, @NameListSetting=NameListSetting,
@AllowFilterAvailability=AllowFilterAvailability, @AllowFilterPicassoOnline=AllowFilterPicassoOnline, @AllowFilterCustomerNumber=AllowFilterCustomerNumber, @AllowFilterCustomerGroup=AllowFilterCustomerGroup, @AllowFilterOtherBookSystem=AllowFilterOtherBookSystem, 
@UseMinMaxDaysOnlyOnline=UseMinMaxDaysOnlyOnline, @MatchDayColor=MatchDayColor, @OwnerSettlement=OwnerSettlement
FROM Products WITH (NOLOCK)
WHERE ProductNumber = @OldProductNumber
IF @@rowcount = 0
  RETURN

IF @ProductDate < @SystemDateStr
  SET @ProductDate = @SystemDateStr

INSERT INTO Products (ProductNumber, MarkAsDeleted, ProductName, Description, ProductDate, ProductEndDate, DayProduct, FixedPrice,
  PriceOverwrite, PensionTypeNumber, BillTypeNumber, ChangeOfBedSheets, ProductTypeNumber, ProductSubtypeNumber,
  CancellationFee, EnvironmentFee, EnvironmentFeeType, /*CurrencyNumber,*/ MinimumDays, MaximumDays, ArrivalMonday, ArrivalTuesday, ArrivalWednesday,
  ArrivalThursday, ArrivalFriday, ArrivalSaturday, ArrivalSunday, 
  BillTextProduct, BillTextArrival, BillTextStay, BillTextDeparture, ExcludeVAT,AddonType, NameListSetting,
  AllowFilterAvailability, AllowFilterPicassoOnline, AllowFilterCustomerNumber, AllowFilterCustomerGroup, AllowFilterOtherBookSystem, UseMinMaxDaysOnlyOnline, MatchDayColor, OwnerSettlement)
VALUES (@NewProductNumber, @MarkAsDeleted, @NewProductName, @NewDescription, @ProductDate, @ProductEndDate, @DayProduct, @FixedPrice,
  @PriceOverwrite, @PensionTypeNumber, @BillTypeNumber, @ChangeOfBedSheets, @ProductTypeNumber, @ProductSubtypeNumber,
  @CancellationFee, @EnvironmentFee, @EnvironmentFeeType, /*@CurrencyNumber,*/ @MinimumDays, @MaximumDays, @ArrivalMonday, @ArrivalTuesday, @ArrivalWednesday,
  @ArrivalThursday, @ArrivalFriday, @ArrivalSaturday, @ArrivalSunday, 
  @BillTextProduct, @BillTextArrival, @BillTextStay, @BillTextDeparture, @ExcludeVAT,@AddonType, @NameListSetting,
  @AllowFilterAvailability, @AllowFilterPicassoOnline, @AllowFilterCustomerNumber, @AllowFilterCustomerGroup, @AllowFilterOtherBookSystem, @UseMinMaxDaysOnlyOnline, @MatchDayColor, @OwnerSettlement)

--- ProductPrice ---
SELECT * INTO #TempProductPrice
FROM ProductPrice WITH (NOLOCK)
WHERE ProductNumber = @OldProductNumber AND DateStamp >= @SystemDateStr AND RoomTypeNumber >= 0 -- make sure we use our primary index

UPDATE #TempProductPrice
SET ProductNumber = @NewProductNumber
WHERE ProductNumber = @OldProductNumber

INSERT INTO ProductPrice SELECT * FROM #TempProductPrice
DROP TABLE #TempProductPrice

-- ProductPOSXtab --
INSERT INTO ProductPOSXtab (ProductNumber, PointOfSaleNumber)
SELECT @NewProductNumber, PointOfSaleNumber 
FROM ProductPOSXtab WITH (NOLOCK)
WHERE ProductNumber = @OldProductNumber

-- ProductSegmentsXTab --
INSERT INTO ProductSegmentsXTab (ProductNumber, SegmentNumber)
SELECT @NewProductNumber, SegmentNumber
FROM ProductSegmentsXTab WITH (NOLOCK)
WHERE ProductNumber = @OldProductNumber

-- ProductMinimumAvailableRoomsXTab --
INSERT INTO ProductMinimumAvailableRoomsXTab (ProductNumber, ProductMinimumDaysNumber, MinimumNumberOfRooms)
SELECT @NewProductNumber, ProductMinimumDaysNumber, MinimumNumberOfRooms 
FROM ProductMinimumAvailableRoomsXTab WITH (NOLOCK)
WHERE ProductNumber = @OldProductNumber

-- ProductPricePacket --
INSERT INTO ProductPricePacket (FK_ProductNumber, PacketType, StartDate, EndDate, FK_PacketNumber)
SELECT @NewProductNumber, PacketType, StartDate, EndDate, FK_PacketNumber FROM ProductPricePacket WITH (NOLOCK) 
WHERE FK_ProductNumber = @OldProductNumber 

-- ProductHotelXTAB
INSERT INTO ProductHotelXTAB (ProductNumber, HotelCustomerNumber) 
SELECT @NewProductNumber, HotelCustomerNumber 
FROM ProductHotelXTAB WITH (NOLOCK)
WHERE ProductNumber = @OldProductNumber

-- ProductExtended
SELECT * INTO #TempProductExtended
FROM ProductExtended WITH (NOLOCK)
WHERE FK_ProductNumber = @OldProductNumber

UPDATE #TempProductExtended
SET FK_ProductNumber = @NewProductNumber
WHERE FK_ProductNumber = @OldProductNumber

INSERT INTO ProductExtended SELECT * FROM #TempProductExtended
DROP TABLE #TempProductExtended

