CREATE PROCEDURE DBO.CopyPacket @OldPacketNumber int, @NewPacketNumber int, @NewPacketName VarChar (50), @NewDescription VarChar (50) , @APacketItemId INT AS
SET NOCOUNT ON

-- AV 2008 1029  transfer identity number to avoid locking of identityfields table
-- AV 2007 0320 added the two crosstables: PacketItemNewRoomTypeXTab and PacketItemNewDepartmentXTAb
-- 20050526 JP changed to use GetNextNumber

INSERT INTO Packet (PacketNumber,Text,Description) VALUES (@NewPacketNumber,@NewPacketName,@NewDescription)

-- Declare all fields in PacketItem table
DECLARE @PacketItemNumber Int
DECLARE @SortNumber int
DECLARE @Time char(5)
DECLARE @DepTime char(5)
DECLARE @TextOverWrite Varchar(50)
DECLARE @PriceOverWrite real
DECLARE @DebType int
DECLARE @WareNumber int
DECLARE @Arrangement int
DECLARE @CreditAccountNumber int
DECLARE @RoomTypeNumber INT
DECLARE @FK_Department INT
DECLARE @Type INT

Declare CopyCur Cursor Forward_only FOR

SELECT PacketItemNumber, SortNumber, Time, DepartureTime, TextOverWrite,PriceOverWrite,DebType,WareNumber, Arrangement FROM PacketItem_New WITH (NOLOCK)
WHERE PacketNumber = @OldPacketNumber

OPEN CopyCur

FETCH NEXT FROM CopyCur
Into @PacketItemNumber, @SortNumber, @Time, @DepTime, @TextOverWrite,@PriceOverWrite,@DebType,@WareNumber,@Arrangement

While (@@Fetch_status=0)
Begin

  INSERT INTO PacketItem_New (PacketItemNumber,PacketNumber,sortnumber,time, DepartureTime, textoverwrite,priceoverwrite,debtype,warenumber,arrangement)
  VALUES (@APacketItemId,@NewPacketNumber, @SortNumber, @Time,@DepTime, @TextOverWrite,@PriceOverWrite,@DebType,@WareNumber,@Arrangement)

  --Copy the room types  
  DECLARE CopyRoomTypesCur CURSOR LOCAL FAST_FORWARD FOR
  SELECT RoomTypeNumber FROM PacketItem_NEWRoomTypeXTab WITH (NOLOCK) WHERE PacketItemNumber = @PacketItemNumber
  OPEN CopyRoomTypesCur
  FETCH NEXT FROM CopyRoomTypesCur  Into @RoomTypeNumber
  WHILE (@@Fetch_status=0)
  BEGIN
    INSERT INTO PacketItem_NEWRoomTypeXTab(PacketItemNumber, RoomTypeNumber) VALUES (@APacketItemId, @RoomTypeNumber)
    FETCH NEXT FROM CopyRoomTypesCur  Into @RoomTypeNumber  
  END
  CLOSE CopyRoomTypesCur
  DEALLOCATE CopyRoomTypesCur

  --Copy the packet charge departments and service departments
  DECLARE CopyPDCur CURSOR LOCAL FAST_FORWARD FOR
  SELECT FK_Department, TYPE FROM PacketItem_NewDepartmentXTab WITH (NOLOCK) WHERE PacketItemNumber = @PacketItemNumber
  OPEN CopyPDCur
  FETCH NEXT FROM CopyPDCur  INTO @FK_Department, @Type
  WHILE (@@Fetch_status=0)
  BEGIN
    INSERT INTO PacketItem_NewDepartmentXTab(PacketItemNumber, FK_Department, Type) VALUES (@APacketItemId, @FK_Department, @Type)
    FETCH NEXT FROM CopyPDCur  INTO @FK_Department, @Type
  END
  CLOSE CopyPDCur
  DEALLOCATE CopyPDCur

  SET @APacketItemId = @APacketItemId + 1
  FETCH NEXT FROM CopyCur
  INTO @PacketItemNumber, @SortNumber, @Time, @DepTime, @TextOverWrite,@PriceOverWrite,@DebType,@WareNumber,@Arrangement

END

CLOSE CopyCur

DEALLOCATE CopyCur

