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

/*
  Created by Kent Johansen March 2007

  Changes:
    KJ 20250123: Department wrong on transaction on new hotel. Department should be set to extra charge department on "move to"-hotel.
    KJ 20250122: Support for move of Logi/Hall transaction
    KJ 20240214: Added timeZone
    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 @LNewDepartment INT

SELECT TOP 1 @LNewDepartment = PK_Department 
FROM Departments WITH (NOLOCK)
INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
  AND CustomerTypeNumber = 7
  AND Customer.MarkAsDeleted = 0
  AND Customer.CustomerNumber = @AMoveToHotelCustomerNumber
WHERE ISNULL(Departments.MarkAsDeleted, 0) = 0 
AND Departments.AutoCharge = 1 

PRINT '@LNewDepartment'
PRINT @LNewDepartment

IF ISNULL(@LNewDepartment, 0) <= 0
  SELECT TOP 1 @LNewDepartment = PK_Department 
  FROM Departments WITH (NOLOCK)
  INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
    AND CustomerTypeNumber = 7
    AND Customer.MarkAsDeleted = 0
    AND Customer.CustomerNumber = @AMoveToHotelCustomerNumber
  WHERE ISNULL(Departments.MarkAsDeleted, 0) = 0

PRINT '@LNewDepartment'
PRINT @LNewDepartment

IF ISNULL(@LNewDepartment, 0) <= 0
BEGIN
  DECLARE @s VARCHAR(500)
  SET @s = 'Error in storedprocedure <<MoveTransactionsToOtherHotel>>: No extra charge department on move to hotel. (@AMoveToHotelCustomerNumber = ' + CAST(@AMoveToHotelCustomerNumber AS VARCHAR) + ')'
  RAISERROR(@s, 15, 1)
END

DECLARE @tmpTransactionNumber INT

DECLARE @CurrentDateTime DATETIME

EXEC SystemDateTime '', @CurrentDateTime OUTPUT

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 @pwl INTEGER

SELECT @pwl = ProductWareLinkNumber
FROM Transactions WITH (NOLOCK)
WHERE GlobalTransactionNumber = @AGlobalTransactionNumber

/*
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 ((ISNULL(@pwl, 0) > 0 AND ProductWareLinkNumber = @pwl) OR (GlobalTransactionNumber = @AGlobalTransactionNumber))
*/

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 ((ISNULL(@pwl, 0) > 0 AND ProductWareLinkNumber = @pwl) OR (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 ((ISNULL(@pwl, 0) > 0 AND ProductWareLinkNumber = @pwl) OR (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, @LNewDepartment, @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