CREATE PROCEDURE DBO.CancelDepositOnReservation @referencenumber int, @GuestNumber int = 0, @InclDoneDeposit bit = 0, @UserIDNumber INT = 0 AS
SET NOCOUNT ON
/*
Changes:
 KJ 20191209: Ext: Textoverwrite made a bit longer
 KJ 20160518: Bug: Fix for "Wrong amount on folio in cashier". Case #CR105244
 KJ 20160425: Added post checkin deposit when cancel reservation. Req #2999
 KJ 20140612: Bug: Fix for "Can not insert the value NULL into ProceOverwritten". Case #CR073380
 KJ 20120703: Bug: Fix for "Error when cancel reservation with paied deposit". Case #CR044856.
 KJ 20120514: Bug: Fix for "Can not insert the value NULL into ProceOverwritten". 
 KJ 20101229: Bug: Fix for "Return Deposit wrong pieces"
 KJ 20101228: Ext: Added "post return Deposit" req# 699
 KJ 20100519: Bug: Fix for "sometimes amount is not correct when cancel reservation"
 KJ 20090323: Bug: Fix for "Lock of IdentityFields table"
 KJ 20090320: Fix for "Lock of IdentityFields table"
 KJ 20090319: Optimized the sp.
 KJ 20070410: Department rewrite
 KJ 20061110: Yet another fix for FolioNumber is null
 KJ 20060621: Fix for Sometimes FolioNumber is null
 KJ 20060511: NB changes at 20060207 is bad. @@RowCount is always 0. Now using the CountryNumber 
 KJ 20060412: Cleanup
 20050511 JP: @UserIDNumber as a parameter to use logged in user's id
 17/08 2004: Kun cancel "almindelige" tasks hvis
 24/06-2004 af KS: Added hotelnumber>=0 and only saleschannel in join for hotelnumber
 07/06 2004 by KS: Bug if not use for multihotels
 14/05 2004 by KS: Imp. CustomerHotelNumber for multihotels
 10/05 2004: Hvis det ikke er en futDeposit skal state sættes til Chanceled ikke DepositCancelled
 28/11-2003: Changed added guestnumber
 24/09-2003 : changed    if @type = 15  TO  if @type = 15 and @State=3
 24/09-2003 : changed   UPDATE followup SET state = 5 WHERE followupnumber = @followupnumber TO  UPDATE followup SET state = 4 WHERE followupnumber = @followupnumber
 6/08-2003 : Added 'folionumber if referencenumber=@referencenumber' if new deposit
 30/6-2003 : New Deposit
 28/3-2003 : Added Globaltransactionnumber (lock)
 27/2-2003 : deleted "folionumber in .... if state=3" 
 03/2-2003 : Changed (@InclDoneDeposit=1 AND state < 3) to (@InclDoneDeposit=1 AND state < 4)
 30/1-2003: Imp af cancel af alle tasks
 13/1-2003 : Imp "if @@ROWCOUNT>0 -- safe" when charge correctiondeposit
 09/1-2003 : Imp DepositFollowupNumber
 02/1-2003 : Imp modpostering.
*/

DECLARE
  @FollowUpNumber INT,
  @DepositAmount MONEY,
  @State INT,
  @Type INT,
  @OverLoadUserIDNumber INT,
  @GlobalTransactionNumber INT,
  @Payment SMALLINT,
  @Pieces SMALLINT,
  @WarePrice MONEY,
  @CountryNumber INT,
  @FK_Department INT,
  @WareNumber INT,
  @WareText VARCHAR(150),
  @FolioNumber INT,
  @TransNrShowOnBillOnAdd INT,
  @Correction BIT,
  @HotelCustomerNumber INT,
  @SystemDate CHAR(8),
  @RealDateTimeForTransaction DATETIME,
  @FirstGlobalTransactionnumber INT

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

SET @GlobalTransactionNumber = 0

Select @HotelCustomerNumber =  CustomerNumber from Customer WITH (NOLOCK) where HotelNumber in (Select HotelNumber from ReservationMain WITH (nolock) where ReferenceNumber = @ReferenceNumber) and HotelNumber>=0 and CustomerTypeNumber=7
if isnull(@HotelCustomerNumber,-1) = -1 SET @HotelCustomerNumber=-1

DECLARE @PostDepositReturnWare INT
DECLARE @PostDepositReturnWareName VARCHAR(50)
DECLARE @PostDepositReturnWarePayment INT

DECLARE @PostCheckInDepositReturnWare INT
DECLARE @PostCheckInDepositReturnWareName VARCHAR(150)
DECLARE @PostCheckInDepositReturnWarePayment INT

DECLARE @InhouseAccountNumber INT
DECLARE @PaymentAccountNumber INT

SELECT @PostDepositReturnWare = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ReturnDepositPayment'
IF ISNULL(@PostDepositReturnWare, -1) = -1
  SET @PostDepositReturnWare = -1

SELECT @PostCheckInDepositReturnWare = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ReturnCheckinDepositPayment'
IF ISNULL(@PostCheckInDepositReturnWare, -1) = -1
  SET @PostCheckInDepositReturnWare = -1

if @InclDoneDeposit = 0
begin
  DECLARE #FollowUpCur CURSOR FORWARD_ONLY FOR
    SELECT f.FollowUpNumber, f.DepositAmount, f.State, f.Type 
    FROM Followup f WITH (NOLOCK) 
    INNER JOIN ReservationFollowupXTab rfx WITH (NOLOCK) ON rfx.FollowupNumber = f.FollowupNumber
      AND rfx.ReferenceNumber = @referencenumber AND rfx.GuestNumber = @GuestNumber
    WHERE F.State < 3 --futDeposit & > cancelled
  OPEN #FollowUpCur
end
else
begin
  DECLARE #FollowUpCur CURSOR FORWARD_ONLY FOR
    SELECT f.FollowUpNumber, f.DepositAmount, f.State, f.Type 
    FROM Followup f WITH (NOLOCK) 
    INNER JOIN ReservationFollowupXTab rfx WITH (NOLOCK) ON rfx.FollowupNumber = f.FollowupNumber
      AND rfx.ReferenceNumber = @referencenumber AND rfx.GuestNumber = @GuestNumber
    WHERE F.State < 4 --futDeposit & > cancelled

    UNION ALL

    SELECT 0, t.PriceOverwrite, 3, 15 
    FROM Transactions t WITH (NOLOCK)
    INNER JOIN Folios f WITH (NOLOCK) ON f.FolioNumber = t.Folionumber
      AND f.ReferenceNumber = @ReferenceNumber 
      AND F.GuestNumber = @GuestNumber
    WHERE Payment=12 
    AND MovedDeleted=0 
    AND ISNULL(DepositFollowupNumber, 0) = 0
    AND ShowOnBillFlag=1 
  OPEN #FollowUpCur
end

FETCH NEXT FROM #FollowUpCur INTO @FollowUpNumber, @DepositAmount, @State, @type

IF (@@FETCH_STATUS <> -1)
  EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @FirstGlobalTransactionnumber OUTPUT

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF ISNULL(@CountryNumber, 0) <= 0
  BEGIN
    SELECT @CountryNumber = CONVERT(INT,KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='CountryCode'
    IF ISNULL(@CountryNumber, 0) <= 0
      SELECT TOP 1 @CountryNumber = CountryNumber FROM Countries WITH (NOLOCK)
  END

  if (@State<3) and (@type = 15) -- fusNone, fusNew, fusChanged Deposit 
  begin
    UPDATE followup SET state = 4 WHERE followupnumber = @followupnumber

    SET @Payment = 11 
    SET @Correction = 1  
    Exec Postering 0, 0, 0, 0, 0, 0, @UserIDNumber, @FolioNumber, 0, 0, 0, 0, @wareText, @Pieces, @WarePrice, @CountryNumber, 0, 0 , 0, 0, @FK_Department, @WareNumber, @Payment, 0, 0, @Correction, '', @TransNrShowOnBillOnAdd OUTPUT, @HotelCustomerNumber, @DepositFollowupNumber = @FollowupNumber
  end
  else
  BEGIN
    IF @type = 15 and @State=3--deposit DONE
    BEGIN
      SET @GlobalTransactionNumber = 0
      IF @FollowupNumber > 0
      BEGIN
        SELECT TOP 1
          @GlobalTransactionNumber = GlobalTransactionNumber,
          @OverLoadUserIDNumber = UserIDNumber,
          @Payment = Payment,
          @Pieces = Pieces,
          @WarePrice = PriceOverwrite,
          @CountryNumber = CurrencyNumber,
          @FK_Department = FK_DepartmentNumber, 
          @WareNumber = WareNumber,
          @WareText = TextOverwrite,
          @FolioNumber = FolioNumber,
          @PaymentAccountNumber = AccountNumber
        FROM Transactions WITH (NOLOCK)
        WHERE DepositFollowupNumber = @FollowupNumber
        AND Payment=12 
        AND ShowOnBillFlag = 1 
        AND MovedDeleted=0
      END
      ELSE
      BEGIN
        SELECT TOP 1
          @GlobalTransactionNumber = t.GlobalTransactionNumber,
          @OverLoadUserIDNumber = t.UserIDNumber,
          @Payment = t.Payment,
          @Pieces = t.Pieces,
          @WarePrice = t.PriceOverwrite,
          @CountryNumber = t.CurrencyNumber,
          @FK_Department = t.FK_DepartmentNumber, 
          @WareNumber = t.WareNumber,
          @WareText = t.TextOverwrite,
          @FolioNumber = t.FolioNumber,
          @PaymentAccountNumber = AccountNumber
        FROM Transactions t WITH (NOLOCK)
        INNER JOIN Folios f WITH (NOLOCK) ON f.FolioNumber = t.FolioNumber
          AND f.ReferenceNumber = @Referencenumber 
          AND f.GuestNumber = 0 
          AND f.OpenFolio = 1
        WHERE ISNULL(t.DepositFollowupNumber, 0) = 0
        AND t.Payment=12 
        AND t.ShowOnBillFlag = 1 
        AND t.MovedDeleted=0
        AND t.Priceoverwrite=@DepositAmount
      END
     
      IF ISNULL(@GlobalTransactionNumber, 0) > 0
      BEGIN 
        IF ISNULL(@FolioNumber, 0) <= 0
        BEGIN
          SELECT TOP 1
            @FolioNumber = Transactions.FolioNumber
          FROM Transactions WITH (NOLOCK)
          INNER JOIN Folios WITH (NOLOCK) ON Folios.Folionumber = Transactions.FolioNumber 
          AND ReferenceNumber = @ReferenceNumber AND Folios.GuestNumber = @GuestNumber
        END

        IF ISNULL(@CountryNumber, 0) <= 0
        BEGIN
          SELECT @CountryNumber = CONVERT(INT,KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='CountryCode'
          IF ISNULL(@CountryNumber, 0) <= 0
            SELECT TOP 1 @CountryNumber = CountryNumber FROM Countries WITH (NOLOCK)
        END

        if (ISNULL(@CountryNumber, 0) > 0) and (ISNULL(@FolioNumber, 0) > 0)
        begin 
          IF @UserIDNumber = 0
            SET @UserIDNumber = @OverLoadUserIDNumber  -- JP 20050511 for backward compatibility

          --Update Followup SET Note = note + char('')/*Convert(Char,Note + '  ' + 'Payback deposit ')*/ where followupnumber = @followupnumber
          SET @Pieces = -@Pieces
          SET @Correction = 0

          EXEC Postering 0, 0, 0, 0, 0, 0, @UserIDNumber, @FolioNumber, 0, 0, 0, 0, @wareText, @Pieces, @WarePrice, @CountryNumber, 0, 0 , 0, 0, @FK_Department, @WareNumber, @Payment, @FirstGlobalTransactionnumber, 0, @Correction, '', @TransNrShowOnBillOnAdd OUTPUT,@HotelCustomerNumber, @DepositFollowupNumber = @FollowupNumber
    
          EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @FirstGlobalTransactionnumber OUTPUT

          IF @PostCheckinDepositReturnWare > 0
          BEGIN
            SELECT @PostCheckinDepositReturnWareName = WareName, @PostCheckinDepositReturnWarePayment = @Payment FROM Ware WITH(NOLOCK) WHERE WareNumber = @PostCheckinDepositReturnWare
            SELECT @InhouseAccountNumber = AccountNumber FROM Accounts(NOLOCK) WHERE AccountType = 5 AND MarkAsDeleted = '0'

            -- Credit
            INSERT INTO Transactions (OriginalGlobalTransactionNumber, GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, OriginalDate, ProductWareLinkNumber, GuestNumber, ExchangeRateOverwrite, HotelCustomerNumber, DepositFollowupNumber)
            VALUES(@GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, 0, 0, 0, 0, 0, @RealDateTimeForTransaction, @UserIDNumber, @FolioNumber, 1, @WarePrice, @CountryNumber, 0, 0, 0, @PostCheckinDepositReturnWare, @PostCheckinDepositReturnWareName, @PostCheckinDepositReturnWarePayment, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @FollowupNumber)

            -- Debit
            INSERT INTO Transactions (OriginalGlobalTransactionNumber, GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, OriginalDate, ProductWareLinkNumber, GuestNumber, ExchangeRateOverwrite, HotelCustomerNumber, DepositFollowupNumber)
            VALUES(@GlobalTransactionNumber, @GlobalTransactionNumber, @RealDateTimeForTransaction, 0, 0, 0, 0, 0, @RealDateTimeForTransaction, @UserIDNumber, @FolioNumber, 1, @WarePrice, @CountryNumber, 1, 1, 0, @PostCheckinDepositReturnWare, @PostCheckinDepositReturnWareName, @PostCheckinDepositReturnWarePayment, @PaymentAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @FollowupNumber)

            EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @FirstGlobalTransactionnumber OUTPUT
          END

          IF @PostDepositReturnWare > 0
          BEGIN
            SET @Pieces = (@Pieces*-1)
            SELECT @PostDepositReturnWareName = WareName, @PostDepositReturnWarePayment = @Payment FROM Ware WITH(NOLOCK) WHERE WareNumber = @PostDepositReturnWare
            EXEC Postering 0, 0, 0, 0, 0, 0, @UserIDNumber, @FolioNumber, 0, 0, 0, 0, @PostDepositReturnWareName, @Pieces, @WarePrice, @CountryNumber, 0, 0 , 0, 0, @FK_Department, @PostDepositReturnWare, @PostDepositReturnWarePayment, @FirstGlobalTransactionnumber, 0, @Correction, '', @TransNrShowOnBillOnAdd OUTPUT, @HotelCustomerNumber, @DepositFollowupNumber = @FollowupNumber

            EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @FirstGlobalTransactionnumber OUTPUT
          END   
        END
      END
    END --type = 15
    ELSE IF @InclDoneDeposit = 1 
      UPDATE followup SET state = 5 WHERE followupnumber = @followupnumber
  END
    
  FETCH NEXT FROM #FollowUpCur INTO @FollowUpNumber, @DepositAmount, @State, @type
END

CLOSE #FollowUpCur
DEALLOCATE #FollowUpCur