CREATE PROCEDURE [dbo].[PostDepositDiff] @AFolioNumber INT AS
SET NOCOUNT ON

/*
  Created by Kent Johansen August 2008
  used to post deposit that is not in balance on a selected folio

  Changes:
    KJ 20240214: Added timeZone
    KJ 20201116: Added handling for more dirrerences.
    KJ 20130819: Added handling for more dirrerences.
    KJ 20110829: Added handling for more dirrerences.
    KJ 20110117: Update to use in 4.3
    KJ 20100309: Bug: Fix for "amount sometimes negative"
    KJ 20090310: Also look a reservations that is inhouse.
*/

DECLARE @HotelCustomerNumber INT
DECLARE @InhouseSaldo MONEY
DECLARE @WantedSaldo MONEY
DECLARE @DueSaldo MONEY
DECLARE @FolioNumber INT
DECLARE @ResStatus INT
DECLARE @InHouseAccountNumber INT
DECLARE @DueAccountNumber INT
DECLARE @WantedAccountNumber INT
DECLARE @GlobalTransactionNumber INT
DECLARE @RealDateTimeForTransaction DATETIME
DECLARE @HotelCountryNumber INT
DECLARE @CheckinWareNumber INT
DECLARE @CheckinWareText VARCHAR(150)
DECLARE @WantedWareNumber INT
DECLARE @WantedWareText VARCHAR(150)
DECLARE @FK_Department INT
DECLARE @CentralBookingType SMALLINT
DECLARE @DepositFollowupNumber INT

DECLARE @TotalSaldo MONEY

SET @ResStatus = 5
SET @TotalSaldo = 0
SET @DepositFollowupNumber = 0

SELECT @InhouseAccountNumber = AccountNumber FROM Accounts WITH (NOLOCK) WHERE AccountType = 5 AND MarkAsDeleted = '0'
SELECT @DueAccountNumber = AccountNumber FROM Accounts WITH (NOLOCK) WHERE AccountType = 8 AND MarkAsDeleted = '0'
SELECT @WantedAccountNumber = AccountNumber FROM Accounts WITH (NOLOCK) WHERE AccountType = 9 AND MarkAsDeleted = '0'

EXEC SystemDateTime '', @RealDateTimeForTransaction OUTPUT

SELECT @HotelCountryNumber = CONVERT(INT, KeyValue) FROM HotelSetup (NOLOCK) WHERE SearchKey = 'CountryCode'
IF @@ROWCOUNT = 0
  SELECT TOP 1 @HotelCountryNumber = CountryNumber FROM Countries (NOLOCK)

SELECT @CheckinWareNumber = WareNumber, @CheckinWareText = WareName 
FROM Ware(NOLOCK) 
WHERE Payment = 10 
AND ISNULL(MarkAsDeleted, 0) = 0

SELECT @WantedWareNumber = WareNumber, @WantedWareText = WareName 
FROM Ware(NOLOCK) 
WHERE Payment = 11
AND ISNULL(MarkAsDeleted, 0) = 0

SELECT @CentralBookingType = CONVERT(INT, ISNULL(KeyValue, 0)) FROM HotelSetup (NOLOCK) WHERE SearchKey = 'CentralBookingType'

DECLARE db_cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
  SELECT Folios.FolioNumber, Transactions.HotelCustomerNumber, --Transactions.DepositFollowupNumber,
  SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) AS Price
  FROM Accounts (NOLOCK)
  FULL OUTER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
  INNER JOIN Folios (NOLOCK) ON Transactions.FolioNumber = Folios.FolioNumber
  INNER JOIN Reservations (NOLOCK) ON Reservations.ReferenceNumber = Folios.ReferenceNumber AND Reservations.GuestNumber = Folios.GuestNumber
  AND (Accounts.Balance = 1 OR Accounts.Balance = 2)
  AND Accounts.AccountType <> 2
  AND Accounts.AccountType <> 1
  AND Accounts.AccountNumber IN (@InhouseAccountNumber)
  AND Reservations.Status >= @ResStatus
  AND Folios.FolioNumber = @AFolioNumber
  GROUP BY Folios.FolioNumber, Transactions.HotelCustomerNumber--, Transactions.DepositFollowupNumber
  HAVING SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) NOT BETWEEN -1 AND 1
  ORDER BY Folios.FolioNumber DESC
OPEN db_cur

FETCH NEXT FROM db_cur INTO @FolioNumber, @HotelCustomerNumber,  @InhouseSaldo

WHILE @@FETCH_STATUS = 0
BEGIN
  -- Check for missing checkin deposit
  SELECT @DueSaldo = SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)
  FROM Accounts (NOLOCK)
  INNER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    AND Transactions.FolioNumber = @FolioNumber
  WHERE Transactions.AccountNumber = @DueAccountNumber

  -- Check for due <> wanted deposit
  SELECT @WantedSaldo = SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)
  FROM Accounts (NOLOCK)
  INNER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    AND Transactions.FolioNumber = @FolioNumber
  WHERE Transactions.AccountNumber = @WantedAccountNumber

  PRINT '@InhouseSaldo'
  PRINT CONVERT(VARCHAR, @InhouseSaldo)
  PRINT '@DueSaldo'
  PRINT CONVERT(VARCHAR, @DueSaldo)
  PRINT '@WantedSaldo'
  PRINT CONVERT(VARCHAR, @WantedSaldo)

  IF @CentralBookingType=7
  BEGIN
    SELECT @FK_Department = PK_Department
    FROM Departments (NOLOCK)
    INNER JOIN Customer(NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
      AND CustomerNumber = @HotelCustomerNumber
    WHERE Departments.MarkAsDeleted = 0
    AND AutoCharge = 1
    ORDER BY SortNumber

    IF ISNULL(@FK_Department, 0) = 0
      SELECT @FK_Department = PK_Department
      FROM Departments (NOLOCK)
      INNER JOIN Customer(NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
        AND CustomerNumber = @HotelCustomerNumber
      WHERE Departments.MarkAsDeleted = 0
      ORDER BY SortNumber
  END
  ELSE
  BEGIN
    SELECT @FK_Department = PK_Department
    FROM Departments (NOLOCK)
    WHERE Departments.MarkAsDeleted = 0
    AND AutoCharge = 1
    ORDER BY SortNumber

    IF ISNULL(@FK_Department, 0) = 0
      SELECT @FK_Department = PK_Department
      FROM Departments (NOLOCK)
      WHERE Departments.MarkAsDeleted = 0
      ORDER BY SortNumber
  END

  IF @InhouseSaldo < 0 AND ISNULL(@DueSaldo, 0) = 0 AND ISNULL(@WantedSaldo, 0) = 0
  BEGIN
    -- deposit is not checked in
    PRINT '@InhouseSaldo < 0 AND ISNULL(@DueSaldo, 0) = 0 AND ISNULL(@WantedSaldo, 0) = 0'
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    SET @InhouseSaldo = @InhouseSaldo*-1

    -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

    -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
  END
  ELSE IF @InhouseSaldo = ((@DueSaldo+@WantedSaldo) * -1)
  BEGIN
    PRINT 'IF @InhouseSaldo = ((@DueSaldo+@WantedSaldo) * -1)'
  
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT
    
    IF @InhouseSaldo < 0
    BEGIN
      PRINT '@DueSaldo < 0'
      SET @InhouseSaldo = @InhouseSaldo*-1

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
    ELSE
    BEGIN
      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END

    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT
  
    IF @WantedSaldo < 0
    BEGIN
      SET @WantedSaldo = @WantedSaldo*-1

      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
    ELSE
    BEGIN
      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
  END

  ELSE IF @InhouseSaldo = @DueSaldo
  BEGIN
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    IF @InhouseSaldo < 0
    BEGIN
      SET @InhouseSaldo = @InhouseSaldo*-1

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
    ELSE
    BEGIN
      PRINT '@DueSaldo > 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, 1, @FolioNumber, 1, @DueSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, -1)

      -- 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, 1, @FolioNumber, 1, @DueSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, -1)
    END
  END

  ELSE IF @InhouseSaldo = (@DueSaldo*-1)
  BEGIN
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    IF @InhouseSaldo < 0
    BEGIN
      SET @InhouseSaldo = @InhouseSaldo*-1

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
    ELSE
    BEGIN
      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
  END

  IF @InhouseSaldo = (@WantedSaldo*-1)
  BEGIN
    PRINT '@InhouseSaldo = (@WantedSaldo*-1)'
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    IF @WantedSaldo < 0
    BEGIN
      PRINT '@WantedSaldo < 0'
      SET @WantedSaldo = @WantedSaldo*-1
      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 1, 0, @WantedWareNumber, @WantedWareText, 11, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, -1)

      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 0, 0, @WantedWareNumber, @WantedWareText, 11, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, -1)
    END
    ELSE
    BEGIN
      PRINT '@WantedSaldo > 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 0, 0, @WantedWareNumber, @WantedWareText, 11, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, -1)

      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 1, 0, @WantedWareNumber, @WantedWareText, 11, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, -1)
    END
  END

  FETCH NEXT FROM db_cur INTO @FolioNumber, @HotelCustomerNumber, @InhouseSaldo
END

CLOSE db_cur
DEALLOCATE db_cur

DECLARE db_cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
  SELECT Folios.FolioNumber, Transactions.HotelCustomerNumber, --Transactions.DepositFollowupNumber,
  SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) AS Price
  FROM Accounts (NOLOCK)
  FULL OUTER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
  INNER JOIN Folios (NOLOCK) ON Transactions.FolioNumber = Folios.FolioNumber
  INNER JOIN Reservations (NOLOCK) ON Reservations.ReferenceNumber = Folios.ReferenceNumber AND Reservations.GuestNumber = Folios.GuestNumber
  AND (Accounts.Balance = 1 OR Accounts.Balance = 2)
  AND Accounts.AccountType <> 2
  AND Accounts.AccountType <> 1
  AND Accounts.AccountNumber IN (@WantedAccountNumber)
  AND Reservations.Status >= @ResStatus
  AND Folios.FolioNumber = @AFolioNumber
  GROUP BY Folios.FolioNumber, Transactions.HotelCustomerNumber--, Transactions.DepositFollowupNumber
  HAVING SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) NOT BETWEEN -1 AND 1
  ORDER BY Folios.FolioNumber DESC
OPEN db_cur

FETCH NEXT FROM db_cur INTO @FolioNumber, @HotelCustomerNumber,  @WantedSaldo

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @InhouseSaldo = SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)
  FROM Accounts (NOLOCK)
  INNER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    AND Transactions.FolioNumber = @FolioNumber
  WHERE Transactions.AccountNumber = @InhouseAccountNumber

  -- Check for due <> wanted deposit
  SELECT @DueSaldo = SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)
  FROM Accounts (NOLOCK)
  INNER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    AND Transactions.FolioNumber = @FolioNumber
  WHERE Transactions.AccountNumber = @DueAccountNumber

  PRINT 'Check for wanted'
  PRINT '@WantedSaldo'
  PRINT CONVERT(VARCHAR, @WantedSaldo)
  PRINT '@InhouseSaldo'
  PRINT CONVERT(VARCHAR, @InhouseSaldo)
  PRINT '@DueSaldo'
  PRINT CONVERT(VARCHAR, @DueSaldo)

  IF @CentralBookingType=7
  BEGIN
    SELECT @FK_Department = PK_Department
    FROM Departments (NOLOCK)
    INNER JOIN Customer(NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
      AND CustomerNumber = @HotelCustomerNumber
    WHERE Departments.MarkAsDeleted = 0
    AND AutoCharge = 1
    ORDER BY SortNumber

    IF ISNULL(@FK_Department, 0) = 0
      SELECT @FK_Department = PK_Department
      FROM Departments (NOLOCK)
      INNER JOIN Customer(NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
        AND CustomerNumber = @HotelCustomerNumber
      WHERE Departments.MarkAsDeleted = 0
      ORDER BY SortNumber
  END
  ELSE
  BEGIN
    SELECT @FK_Department = PK_Department
    FROM Departments (NOLOCK)
    WHERE Departments.MarkAsDeleted = 0
    AND AutoCharge = 1
    ORDER BY SortNumber

    IF ISNULL(@FK_Department, 0) = 0
      SELECT @FK_Department = PK_Department
      FROM Departments (NOLOCK)
      WHERE Departments.MarkAsDeleted = 0
      ORDER BY SortNumber
  END

  IF @WantedSaldo < 0 AND ISNULL(@InhouseSaldo, 0) = 0 AND ISNULL(@DueSaldo, 0) = 0
  BEGIN
    -- deposit is not checked in
    PRINT 'IF @WantedSaldo < 0 AND ISNULL(@InhouseSaldo, 0) = 0 AND ISNULL(@DueSaldo, 0) = 0'
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    SET @WantedSaldo = @WantedSaldo*-1

    -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

    -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
  END
  ELSE IF @WantedSaldo = (@InhouseSaldo*-1)
  BEGIN
    PRINT '@WantedSaldo = (@InhouseSaldo*-1)'
    
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    IF @InhouseSaldo < 0
    BEGIN
      SET @InhouseSaldo = @InhouseSaldo*-1

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
    ELSE
    BEGIN
      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
  END

  ELSE IF @WantedSaldo = (@DueSaldo * -1)
  BEGIN
    PRINT '@WantedSaldo = (@DueSaldo * -1)'

    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    IF @WantedSaldo < 0
    BEGIN
      SET @WantedSaldo = @WantedSaldo*-1

      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
    ELSE
    BEGIN
      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @WantedSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
  END

  FETCH NEXT FROM db_cur INTO @FolioNumber, @HotelCustomerNumber, @WantedSaldo
END

CLOSE db_cur
DEALLOCATE db_cur

DECLARE db_cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
  SELECT Folios.FolioNumber, Transactions.HotelCustomerNumber, --Transactions.DepositFollowupNumber,
  SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) AS Price
  FROM Accounts (NOLOCK)
  FULL OUTER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
  INNER JOIN Folios (NOLOCK) ON Transactions.FolioNumber = Folios.FolioNumber
  INNER JOIN Reservations (NOLOCK) ON Reservations.ReferenceNumber = Folios.ReferenceNumber AND Reservations.GuestNumber = Folios.GuestNumber
  AND (Accounts.Balance = 1 OR Accounts.Balance = 2)
  AND Accounts.AccountType <> 2
  AND Accounts.AccountType <> 1
  AND Accounts.AccountNumber IN (@DueAccountNumber)
  AND Reservations.Status >= @ResStatus
  AND Folios.FolioNumber = @AFolioNumber
  GROUP BY Folios.FolioNumber, Transactions.HotelCustomerNumber--, Transactions.DepositFollowupNumber
  HAVING SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) NOT BETWEEN -1 AND 1
  ORDER BY Folios.FolioNumber DESC
OPEN db_cur

FETCH NEXT FROM db_cur INTO @FolioNumber, @HotelCustomerNumber,  @DueSaldo

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @InhouseSaldo = SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)
  FROM Accounts (NOLOCK)
  INNER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    AND Transactions.FolioNumber = @FolioNumber
  WHERE Transactions.AccountNumber = @InhouseAccountNumber

  -- Check for due <> wanted deposit
  SELECT @WantedSaldo = SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)*
  CASE WHEN Accounts.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)
  FROM Accounts (NOLOCK)
  INNER JOIN Transactions (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    AND Transactions.FolioNumber = @FolioNumber
  WHERE Transactions.AccountNumber = @WantedAccountNumber

  PRINT 'Check for due'
  PRINT '@WantedSaldo'
  PRINT CONVERT(VARCHAR, @WantedSaldo)
  PRINT '@InhouseSaldo'
  PRINT CONVERT(VARCHAR, @InhouseSaldo)
  PRINT '@DueSaldo'
  PRINT CONVERT(VARCHAR, @DueSaldo)

  IF @CentralBookingType=7
  BEGIN
    SELECT @FK_Department = PK_Department
    FROM Departments (NOLOCK)
    INNER JOIN Customer(NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
      AND CustomerNumber = @HotelCustomerNumber
    WHERE Departments.MarkAsDeleted = 0
    AND AutoCharge = 1
    ORDER BY SortNumber

    IF ISNULL(@FK_Department, 0) = 0
      SELECT @FK_Department = PK_Department
      FROM Departments (NOLOCK)
      INNER JOIN Customer(NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
        AND CustomerNumber = @HotelCustomerNumber
      WHERE Departments.MarkAsDeleted = 0
      ORDER BY SortNumber
  END
  ELSE
  BEGIN
    SELECT @FK_Department = PK_Department
    FROM Departments (NOLOCK)
    WHERE Departments.MarkAsDeleted = 0
    AND AutoCharge = 1
    ORDER BY SortNumber

    IF ISNULL(@FK_Department, 0) = 0
      SELECT @FK_Department = PK_Department
      FROM Departments (NOLOCK)
      WHERE Departments.MarkAsDeleted = 0
      ORDER BY SortNumber
  END

  IF @DueSaldo < 0 AND ISNULL(@InhouseSaldo, 0) = 0 AND ISNULL(@WantedSaldo, 0) = 0
  BEGIN
    -- deposit is not checked in
    PRINT 'IF @@DueSaldo < 0 AND ISNULL(@InhouseSaldo, 0) = 0 AND ISNULL(@@WantedSaldo, 0) = 0'
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    SET @DueSaldo = @DueSaldo*-1

    -- 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, 1, @FolioNumber, 1, @DueSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

    -- 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, 1, @FolioNumber, 1, @DueSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
  END
  ELSE IF @DueSaldo = (@InhouseSaldo*-1)
  BEGIN
    PRINT '@@DueSaldo = (@InhouseSaldo*-1)'
    
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    IF @InhouseSaldo < 0
    BEGIN
      SET @InhouseSaldo = @InhouseSaldo*-1

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
    ELSE
    BEGIN
      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @InhouseAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @InhouseSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
  END

  ELSE IF @DueSaldo = (@WantedSaldo * -1)
  BEGIN
    PRINT '@@DueSaldo = (@@WantedSaldo * -1)'

    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

    IF @DueSaldo < 0
    BEGIN
      SET @DueSaldo = @DueSaldo*-1

      -- 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, 1, @FolioNumber, 1, @DueSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @DueSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
    ELSE
    BEGIN
      -- 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, 1, @FolioNumber, 1, @DueSaldo, @HotelCountryNumber, 0, 0, 0, @CheckinWareNumber, @CheckinWareText, 10, @DueAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)

      -- 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, 1, @FolioNumber, 1, @DueSaldo, @HotelCountryNumber, 0, 1, 0, @CheckinWareNumber, @CheckinWareText, 10, @WantedAccountNumber, 0, @FK_Department, '', @RealDateTimeForTransaction, NULL, 0, 1, @HotelCustomerNumber, @DepositFollowupNumber)
    END
  END

  FETCH NEXT FROM db_cur INTO @FolioNumber, @HotelCustomerNumber, @DueSaldo
END

CLOSE db_cur
DEALLOCATE db_cur