CREATE PROCEDURE [dbo].[PostCancelBill] @FkInvoice INT, @AUserIdNumber INT, @ACashBox INT = -1, @APaymentTypeCSV VARCHAR(50) = '' AS
SET NOCOUNT ON

/*
  Created by Kent Johansen February 2010
  - Creates transactions with the opposite value than the existing transactions
  - Created on the same folio but with new user, date, globaltran ProductWareLinkNumber and cashbox

  Changes:
    KJ 20241018: Ext: Also possible to include transfer.
    KJ 20240214: Added timeZone
    KJ 20180413: Ext: Added Accrued Credit Note
    KJ 20170209: Bug: TransferAsWare transactions is also cancelled. Case #CR119958
    KJ 20161125: Bug: Fix for "Wrong tax when cancel bill (creditnota)". Case #CR115554
    KJ 20161109: Bug: Fix for "Wrong tax when cancel bill (creditnota)". Case #CR115554
    KJ 20140611: Ext: CashDesk per hotel. Case #CR073269
    NB20131219 Invoice
    KJ 20110815: Ext: New ProductWareLinkNumber when move and delete transactions 
    KJ 20100225: Ext: Added APaymentTypeCSV
*/

DECLARE @OrgTransactionNumber INT
DECLARE @OrgDateStamp DATETIME
DECLARE @OrgDate CHAR(8)
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 @OrgFK_Department INT
DECLARE @OrgDebit BIT
DECLARE @OrgVATValue MONEY
DECLARE @OrgMovedDeleted BIT
DECLARE @OrgMovedFolioNr INT
DECLARE @OrgWareNumber INT
DECLARE @OrgPayment INT
DECLARE @OrgAccountNumber INT
DECLARE @OrgProductNumber INT
DECLARE @OrgPackageNumber INT
DECLARE @OrgRealDateTimeforTransaction DATETIME
DECLARE @OrgNote VARCHAR(10)
DECLARE @OrgGuestNumber INT
DECLARE @OrgProductWareLinkNumber INT
DECLARE @NewProductWareLinkNumber INT
DECLARE @CurProductWareLinkNumber INT
DECLARE @OrgDepositFollowupNumber INT
DECLARE @OrgAutoReceivedPayment BIT
DECLARE @OrgHotelCustomerNumber INT
DECLARE @OrgGlobalTransactionNumber INT
DECLARE @NewGlobalTransactionNumber INT
DECLARE @CurGlobalTransactionNumber INT

DECLARE @CurrentDateTime DATETIME
DECLARE @PostDateTime DATETIME

DECLARE @NewDebit BIT
DECLARE @UseCashBox BIT
DECLARE @LastTransactionNumber INT

EXEC SystemDateTime '', @CurrentDateTime OUTPUT

DECLARE @AccruedCreditNote BIT
SELECT @AccruedCreditNote = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AccruedCreditNote'
SET @AccruedCreditNote = ISNULL(@AccruedCreditNote, 0)

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, ProductWareLinkNumber, DepositfollowupNumber, AutoReceivedPayment, HotelCustomerNumber
  FROM Transactions WITH (NOLOCK)
    INNER JOIN InvoiceTransactionXTab ITX WITH (NOLOCK) ON Transactions.TransactionNumber = ITX.FkTransaction
  WHERE ITX.FkInvoice = @FkInvoice
  AND Transactions.Payment IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APaymentTypeCSV))
/*
  AND Transactions.GlobalTransactionNumber NOT IN (
    SELECT GlobalTransactionNumber
    FROM Transactions WITH (NOLOCK)
    INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
      AND Ware.Type = 4
  )
*/
  ORDER BY ProductWareLinkNumber, GlobalTransactionNumber
OPEN db_cur

FETCH NEXT FROM db_cur INTO @OrgTransactionNumber, @OrgDateStamp, @OrgUserIdNumber, @OrgFolioNumber, @OrgPieces, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, 
  @OrgPaxAdult, @OrgPaxChild1, @OrgPaxChild2, @OrgTextOverwrite, @OrgExchangeRateOverwrite, @OrgFK_Department, @OrgDebit, @OrgVATValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber,
  @OrgPayment, @OrgAccountNumber, @OrgGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @OrgRealDateTimeforTransaction,
  @OrgNote, @OrgGuestNumber, @OrgProductWareLinkNumber, @OrgDepositfollowupNumber, @OrgAutoReceivedPayment, @OrgHotelCustomerNumber

SET @CurGlobalTransactionNumber = 0
SET @CurProductWareLinkNumber = 0

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @CurGlobalTransactionNumber <> @OrgGlobalTransactionNumber
    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @NewGlobalTransactionNumber OUTPUT

  IF ISNULL(@OrgProductWareLinkNumber, -1) > 0 
  BEGIN
    IF ISNULL(@CurProductWareLinkNumber, 0) <> ISNULL(@OrgProductWareLinkNumber, 0)
      EXEC GetNextNumber 'PRODUCTWARELINKIDENTITY', 1, @NewProductWareLinkNumber OUTPUT
    ELSE IF ISNULL(@OrgProductWareLinkNumber, 0) = 0
      SET @NewProductWareLinkNumber = @OrgProductWareLinkNumber
  END
  ELSE  
    SET @NewProductWareLinkNumber = NULL

  -- insert new trans with new DateStamp, UserIdNumber, Pieces*-1, Debit, RealDateTimeForTransaction, ProductWareLinkNumber
  IF @OrgDebit = 1
    SET @NewDebit = 0
  ELSE
    SET @NewDebit = 1

--SELECT @UseCashBox = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'FiscalPrinterSweden'
  SELECT @UseCashBox = dbo.GetHotelSetupKeyValue ('FiscalPrinterSweden', @OrgHotelCustomerNumber)
  SET @UseCashBox = ISNULL(@UseCashBox,0)

  IF @AccruedCreditNote = 1 AND @OrgDateStamp > @CurrentDateTime
  BEGIN
    SET @OrgDate = (SELECT CONVERT(CHAR(8), @OrgDateStamp, 112))
    EXEC SystemDateTime @OrgDate, @PostDateTime OUTPUT
  END
  ELSE
    SET @PostDateTime = @CurrentDateTime

  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 
    (@PostDateTime, @AUserIdNumber, @OrgFolioNumber, @OrgPieces*-1, @OrgPriceOverwrite, @OrgCurrencyNumber, @OrgShowOnBillFlag, @OrgPaxAdult, @OrgPaxChild1, @OrgPaxChild2,
    @OrgTextOverwrite, @OrgExchangeRateOverwrite, @OrgFK_Department, @NewDebit, @OrgVatValue, @OrgMovedDeleted, @OrgMovedFolioNr, @OrgWareNumber, @OrgPayment, @OrgAccountNumber,
    @NewGlobalTransactionNumber, @NewGlobalTransactionNumber, @OrgProductNumber, @OrgPackageNumber, @CurrentDateTime, @OrgNote, @OrgGuestNumber, @CurrentDateTime, @NewProductWareLinkNumber, @OrgDepositFollowupNumber,
    @OrgAutoReceivedPayment, @OrgHotelCustomerNumber, 0)

  IF @UseCashBox = 1
  BEGIN
    SELECT @LastTransactionNumber = @@Identity
    EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashBox, @AUserIdNumber
  END

  SET @CurGlobalTransactionNumber = @OrgGlobalTransactionNumber
  SET @CurProductWareLinkNumber = @OrgProductWareLinkNumber

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

CLOSE db_cur
DEALLOCATE db_cur