CREATE PROCEDURE DBO.MoveTransactionsToOtherHotel @AGlobalTransactionNumber INT, @AMoveToHotelCustomerNumber INT AS
SET NOCOUNT ON

/*
  Created by Kent Johansen March 2007

  THIS CAN NOT MOVE LOGI/HALL TRANSACTIONS BECAUSE PACKAGE IS NOT SELECTED WHEN MOVED. REMEMBER TO GIVE NEW PRODUCTWARELINKNUMBER
  ON THE TRANSACTIONS THAT IS CREATED IN HERE. SEE CASE #CR032520

  Changes:
    KJ 20170309: Bug: Fix for "Handle for InvoiceBillNumber rewrite". Ontime #CR122561
    KJ 20081222: Added OriginalGlobalTransactionNumber
    KJ 20070410: Department rewrite
*/

DECLARE @OrgTransactionNumber INT
DECLARE @OrgDateStamp DATETIME
DECLARE @OrgUserIdNumber INT
DECLARE @OrgFolioNumber INT
DECLARE @OrgPieces INT
DECLARE @OrgPriceOverwrite FLOAT
DECLARE @OrgCurrencyNumber INT
DECLARE @OrgShowOnBillFlag BIT
DECLARE @OrgPAXadult INT
DECLARE @OrgPAXchild1 INT
DECLARE @OrgPAXchild2 INT
DECLARE @OrgTextOverwrite VARCHAR(150)
DECLARE @OrgExchangeRateOverwrite FLOAT
DECLARE @FK_OrgDepartmentOverwrite INT
DECLARE @OrgDebit BIT
DECLARE @OrgVATValue MONEY
DECLARE @OrgMovedDeleted BIT
DECLARE @OrgMovedFolioNr INT
DECLARE @OrgWareNumber INT
DECLARE @OrgPayment INT
DECLARE @OrgAccountNumber INT
DECLARE @OrgGlobalTransactionNumber INT
DECLARE @OrgProductNumber INT
DECLARE @OrgPackageNumber INT
DECLARE @OrgRealDateTimeforTransaction DATETIME
DECLARE @OrgNote VARCHAR(10)
DECLARE @OrgGuestNumber INT
DECLARE @OrgOriginalDate DATETIME
DECLARE @OrgProductWareLinkNumber INT
DECLARE @OrgDepositfollowupNumber INT
DECLARE @OrgAutoReceivedPayment BIT
DECLARE @OrgHotelCustomerNumber INT
DECLARE @OrgChargedWithCard BIT
DECLARE @OrgFKInvoice INT

DECLARE @tmpTransactionNumber INT

DECLARE @CurrentDateStr VARCHAR(8)
DECLARE @CurrentDateTime DATETIME

SELECT @CurrentDateStr = SYSTEMDATE FROM System WITH (NOLOCK) WHERE Number = 1
SET @CurrentDateTime = CONVERT(DATETIME, @CurrentDateStr + ' ' + CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108))

DECLARE @TechotelUser INT
SELECT @TechotelUser = UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = 2
IF ISNULL(@TechotelUser, 0) = 0
  SET @TechotelUser = 1

DECLARE @NewDebit BIT

CREATE TABLE #tblTransactions (
	[TransactionNumber] [int] NOT NULL ,
	[DateStamp] [datetime] NOT NULL ,
	[UserIDNumber] [int] NOT NULL ,
	[FolioNumber] [int] NOT NULL ,
	[Pieces] [int] NOT NULL ,
	[PriceOverwrite] [float] NULL ,
	[CurrencyNumber] [int] NOT NULL ,
	[ShowOnBillFlag] [bit] NOT NULL ,
	[PAXadult] [smallint] NULL ,
	[PAXchild1] [smallint] NULL ,
	[PAXchild2] [smallint] NULL ,
	[TextOverwrite] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ExchangeRateOverwrite] [float] NULL ,
	[FK_DepartmentOverwrite] [int] NOT NULL ,
	[Debit] [bit] NOT NULL ,
	[VATValue] [money] NULL ,
	[MovedDeleted] [bit] NOT NULL ,
	[MovedFolioNr] [int] NULL ,
	[WareNumber] [int] NOT NULL ,
	[Payment] [tinyint] NOT NULL ,
	[AccountNumber] [int] NOT NULL ,
	[GlobalTransactionNumber] [int] NOT NULL ,
	[ProductNumber] [int] NULL ,
	[PackageNumber] [int] NULL ,
	[RealDateTimeforTransaction] [datetime] NOT NULL ,
	[Note] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[GuestNumber] [smallint] NULL ,
	[OriginalDate] [datetime] NULL ,
	[ProductWareLinkNumber] [int] NULL ,
	[DepositfollowupNumber] [int] NULL ,
	[AutoReceivedPayment] [bit] NULL ,
	[HotelCustomerNumber] [int] NOT NULL ,
	[ChargedWithCard] [bit] NOT NULL,
	FKInvoice INT NULL)

DECLARE db_cur CURSOR FOR
  SELECT TransactionNumber, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, PAXadult, PAXchild1, PAXchild2, TextOverwrite, ExchangeRateOverwrite,
    FK_DepartmentNumber, Debit, VATValue, MovedDeleted, MovedFolioNr, WareNumber, Payment, AccountNumber, GlobalTransactionNumber,
    ProductNumber, PackageNumber, RealDateTimeforTransaction, Note, GuestNumber, OriginalDate, ProductWareLinkNumber, DepositfollowupNumber, AutoReceivedPayment,
    HotelCustomerNumber, ChargedWithCard, FKInvoice
  FROM Transactions WITH (NOLOCK)
  LEFT OUTER JOIN InvoiceTransactionXTab WITH (NOLOCK) ON InvoiceTransactionXTab.FkTransaction = Transactions.TransactionNumber
  WHERE GlobalTransactionNumber = @AGlobalTransactionNumber
OPEN db_cur

FETCH NEXT FROM db_cur INTO @OrgTransactionNumber, @OrgDateStamp, @OrgUserIdNumber, @OrgFolioNumber, @OrgPieces, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPAXadult, @OrgPAXchild1, 
  @OrgPAXchild2, @OrgTextOverwrite, @OrgExchangeRateOverwrite, @FK_OrgDepartmentOverwrite, @OrgDebit, @OrgVATValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber,
  @OrgPayment, @OrgAccountNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @OrgRealDateTimeforTransaction,
  @OrgNote, @OrgGuestNumber, @OrgOriginalDate, @OrgProductWareLinkNumber, @OrgDepositfollowupNumber, @OrgAutoReceivedPayment, @OrgHotelCustomerNumber, @OrgChargedWithCard, @OrgFKInvoice

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #tblTransactions VALUES (@OrgTransactionNumber, @OrgDateStamp, @OrgUserIdNumber, @OrgFolioNumber, @OrgPieces, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPAXadult, @OrgPAXchild1, 
    @OrgPAXchild2, @OrgTextOverwrite, @OrgExchangeRateOverwrite, @FK_OrgDepartmentOverwrite, @OrgDebit, @OrgVATValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber,
    @OrgPayment, @OrgAccountNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @OrgRealDateTimeforTransaction,
    @OrgNote, @OrgGuestNumber, @OrgOriginalDate, @OrgProductWareLinkNumber, @OrgDepositfollowupNumber, @OrgAutoReceivedPayment, @OrgHotelCustomerNumber, @OrgChargedWithCard, @OrgFKInvoice)

  FETCH NEXT FROM db_cur INTO @OrgTransactionNumber, @OrgDateStamp, @OrgUserIdNumber, @OrgFolioNumber, @OrgPieces, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPAXadult, @OrgPAXchild1, 
    @OrgPAXchild2, @OrgTextOverwrite, @OrgExchangeRateOverwrite, @FK_OrgDepartmentOverwrite, @OrgDebit, @OrgVATValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber,
    @OrgPayment, @OrgAccountNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @OrgRealDateTimeforTransaction,
    @OrgNote, @OrgGuestNumber, @OrgOriginalDate, @OrgProductWareLinkNumber, @OrgDepositfollowupNumber, @OrgAutoReceivedPayment, @OrgHotelCustomerNumber, @OrgChargedWithCard, @OrgFKInvoice
END

CLOSE db_cur
DEALLOCATE db_cur

DECLARE db_cur CURSOR FOR
  SELECT TransactionNumber, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, PAXadult, PAXchild1, PAXchild2, TextOverwrite, ExchangeRateOverwrite,
    FK_DepartmentOverwrite, Debit, VATValue, MovedDeleted, MovedFolioNr, WareNumber, Payment, AccountNumber, GlobalTransactionNumber,
    ProductNumber, PackageNumber, RealDateTimeforTransaction, Note, GuestNumber, OriginalDate, ProductWareLinkNumber, DepositfollowupNumber, AutoReceivedPayment,
    HotelCustomerNumber, ChargedWithCard, FKInvoice
  FROM #tblTransactions WITH (NOLOCK)
  WHERE GlobalTransactionNumber = @AGlobalTransactionNumber
OPEN db_cur

FETCH NEXT FROM db_cur INTO @OrgTransactionNumber, @OrgDateStamp, @OrgUserIdNumber, @OrgFolioNumber, @OrgPieces, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPAXadult, @OrgPAXchild1, 
  @OrgPAXchild2, @OrgTextOverwrite, @OrgExchangeRateOverwrite, @FK_OrgDepartmentOverwrite, @OrgDebit, @OrgVATValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber,
  @OrgPayment, @OrgAccountNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @OrgRealDateTimeforTransaction,
  @OrgNote, @OrgGuestNumber, @OrgOriginalDate, @OrgProductWareLinkNumber, @OrgDepositfollowupNumber, @OrgAutoReceivedPayment, @OrgHotelCustomerNumber, @OrgChargedWithCard, @OrgFKInvoice

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @OrgMovedDeleted = 0
  BEGIN
    -- insert new trans with new HotelCustomerNumber
    -- insert New delete trans with new DateStamp, UserIdNumber, Pieces*-1, Debit, RealDateTimeForTransaction
    -- Mark current tran as MovedDeleted 

    IF @OrgDebit = 1
      SET @NewDebit = 0
    ELSE
      SET @NewDebit = 1

    INSERT INTO Transactions (DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, PAXadult, PAXchild1, PAXchild2,
      TextOverwrite, ExchangeRateOverwrite, FK_DepartmentNumber, Debit, VATValue, MovedDeleted, MovedFolioNr, WareNumber, Payment, AccountNumber,
      GlobalTransactionNumber, OriginalGlobalTransactionNumber, ProductNumber, PackageNumber, RealDateTimeforTransaction, Note, GuestNumber, OriginalDate, ProductWareLinkNumber, DepositfollowupNumber, 
      AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard) VALUES 
      (@CurrentDateTime, @TechotelUser, @OrgFolioNumber, @OrgPieces, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPaxAdult, @OrgPaxChild1, @OrgPaxChild2,
      @OrgTextOverwrite, @OrgExchangeRateOverwrite, @FK_OrgDepartmentOverwrite, @OrgDebit, @OrgVatValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber, @OrgPayment, @OrgAccountNumber,
      @OrgGlobalTransactionNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @CurrentDateTime, @OrgNote, @OrgGuestNumber, @OrgOriginalDate, @OrgProductWareLinkNumber, @OrgDepositFollowupNumber,
      @OrgAutoReceivedPayment, @AMoveToHotelCustomerNumber, @OrgChargedWithCard)

	SELECT @tmpTransactionNumber = @@Identity 

    INSERT INTO Transactions (DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, PAXadult, PAXchild1, PAXchild2,
      TextOverwrite, ExchangeRateOverwrite, FK_DepartmentNumber, Debit, VATValue, MovedDeleted, MovedFolioNr, WareNumber, Payment, AccountNumber,
      GlobalTransactionNumber, OriginalGlobalTransactionNumber, ProductNumber, PackageNumber, RealDateTimeforTransaction, Note, GuestNumber, OriginalDate, ProductWareLinkNumber, DepositfollowupNumber, 
      AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard) VALUES 
      (@CurrentDateTime, @TechotelUser, @OrgFolioNumber, @OrgPieces*-1, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPaxAdult, @OrgPaxChild1, @OrgPaxChild2,
      @OrgTextOverwrite, @OrgExchangeRateOverwrite, @FK_OrgDepartmentOverwrite, @NewDebit, @OrgVatValue, 1, @OrgMovedFolioNr, @OrgWareNumber, @OrgPayment, @OrgAccountNumber,
      @OrgGlobalTransactionNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @CurrentDateTime, @OrgNote, @OrgGuestNumber, @OrgOriginalDate, @OrgProductWareLinkNumber, @OrgDepositFollowupNumber,
      @OrgAutoReceivedPayment, @OrgHotelCustomerNumber, @OrgChargedWithCard)

    UPDATE Transactions SET MovedDeleted = 1 WHERE TransactionNumber = @OrgTransactionNumber

    IF ISNULL(@OrgFKInvoice, 0) > 0
      UPDATE InvoiceTransactionXTab SET FkTransaction = @tmpTransactionNumber WHERE FkInvoice = @OrgFKInvoice AND FkTransaction = @OrgTransactionNumber
  END
  ELSE
  BEGIN
    -- insert new trans with new DateStamp, UserIdNumber, Pieces*-1, Debit, RealDateTimeForTransaction
    IF @OrgDebit = 1
      SET @NewDebit = 0
    ELSE
      SET @NewDebit = 1

    INSERT INTO Transactions (DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, PAXadult, PAXchild1, PAXchild2,
      TextOverwrite, ExchangeRateOverwrite, FK_DepartmentNumber, Debit, VATValue, MovedDeleted, MovedFolioNr, WareNumber, Payment, AccountNumber,
      GlobalTransactionNumber, OriginalGlobalTransactionNumber, ProductNumber, PackageNumber, RealDateTimeforTransaction, Note, GuestNumber, OriginalDate, ProductWareLinkNumber, DepositfollowupNumber, 
      AutoReceivedPayment, HotelCustomerNumber, ChargedWithCard) VALUES 
      (@CurrentDateTime, @TechotelUser, @OrgFolioNumber, @OrgPieces*-1, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPaxAdult, @OrgPaxChild1, @OrgPaxChild2,
      @OrgTextOverwrite, @OrgExchangeRateOverwrite, @FK_OrgDepartmentOverwrite, @NewDebit, @OrgVatValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber, @OrgPayment, @OrgAccountNumber,
      @OrgGlobalTransactionNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @CurrentDateTime, @OrgNote, @OrgGuestNumber, @OrgOriginalDate, @OrgProductWareLinkNumber, @OrgDepositFollowupNumber,
      @OrgAutoReceivedPayment, @OrgHotelCustomerNumber, @OrgChargedWithCard)
  END

  FETCH NEXT FROM db_cur INTO @OrgTransactionNumber, @OrgDateStamp, @OrgUserIdNumber, @OrgFolioNumber, @OrgPieces, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPAXadult, @OrgPAXchild1, 
    @OrgPAXchild2, @OrgTextOverwrite, @OrgExchangeRateOverwrite, @FK_OrgDepartmentOverwrite, @OrgDebit, @OrgVATValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber,
    @OrgPayment, @OrgAccountNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @OrgRealDateTimeforTransaction,
    @OrgNote, @OrgGuestNumber, @OrgOriginalDate, @OrgProductWareLinkNumber, @OrgDepositfollowupNumber, @OrgAutoReceivedPayment, @OrgHotelCustomerNumber, @OrgChargedWithCard, @OrgFKInvoice
END

CLOSE db_cur
DEALLOCATE db_cur

DROP TABLE #tblTransactions