CREATE PROCEDURE DBO.PostMissingPackageTransaction @FolioNumber INT, @ARecordFixed BIT OUTPUT AS
SET NOCOUNT ON

/*
  Changes:
    KJ 20180302: Bug: MovedDeleted wrong.
    KJ 20170220: Ext: Added @TotalPackageAmount to prevent unvanted transactions when package dep account is in level
    KJ 20161219: Bug Fix for "Can not insert the value NULL into FKInvoice"
    KJ 20160916: Ext: Added output param 
    KJ 20160916: Bug: Fix for "InvoiceBillNumber not set on new transactions"
    KJ 20131017: Bug: Fix for "wrong debit credit on posted transactions"
    KJ 20111123: Bug: Fix for "Also use package amount to determine debit and kredit" Case #CR036690
    KJ 20100602: Bug: Added OriginalGlobalTransactionNumber
    KJ 20090126: Some bugfixes.
    KJ 20081021: Ext: Department rewrite.
*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

IF @ShowPrint = 1
  PRINT 'Post missing Packages script'

DECLARE @TotalPackageAmount MONEY

SELECT
  @TotalPackageAmount = 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 WITH (NOLOCK)
FULL OUTER JOIN Transactions WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
WHERE FolioNumber = @FolioNumber
AND Accounts.AccountType = 6

IF @TotalPackageAmount <> 0
BEGIN
SET @ARecordFixed = 0

DECLARE @PacketAccountNumber INT

DECLARE @Pieces INT
DECLARE @PackageNumber INT
DECLARE @PackageAmount FLOAT
DECLARE @WareNumber INT
DECLARE @ProductNumber INT
DECLARE @Adult INT
DECLARE @Children1 INT
DECLARE @Children2 INT
DECLARE @CurrencyNumber INT
DECLARE @TextOverwrite VARCHAR(150)
DECLARE @ResultAccountNumber INT
DECLARE @BalanceAccountNumber INT
DECLARE @VATAccountNumber INT
DECLARE @VATValueNumber INT
DECLARE @FK_DepartmentNumber INT
DECLARE @GuestNumber INT
DECLARE @ProductWareLinkNumber INT
DECLARE @HotelCustomerNumber INT
DECLARE @InvoiceBillNumber INT
DECLARE @FKInvoice INT

DECLARE @VatValueWare FLOAT
DECLARE @WarePriceWithOutVAT FLOAT
DECLARE @VATOfPacketItem FLOAT

DECLARE @GlobalTransactionNumber INT
DECLARE @ServerDateTime DATETIME
DECLARE @RealDateTimeForTransaction DATETIME

DECLARE @NegPackageAmount BIT

DECLARE @MovedDeleted BIT

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

IF @ShowPrint = 1
BEGIN
  PRINT '@RealDateTimeForTransaction'
  PRINT CONVERT(VARCHAR, @RealDateTimeForTransaction)
END

DECLARE db_cur CURSOR FOR
  SELECT Folios.FolioNumber, Transactions.Pieces, Transactions.PackageNumber, Transactions.WareNumber, Transactions.ProductNumber, PaxAdult, PaxChild1, PaxChild2,
    CurrencyNumber, TextOverwrite, Ware.ResultAccountNumber, Ware.BalanceAccountNumber, AccountsVAT.VATAccountNumber, AccountsVAT.VatValueNumber, FK_DepartmentNumber,
    Transactions.GuestNumber, ProductWareLinkNumber, Transactions.HotelCustomerNumber, Transactions.InvoiceBillNumber,
    (
      SELECT TOP 1 FKInvoice FROM InvoiceTransactionXTab WITH (NOLOCK)
      INNER JOIN Invoice WITH (NOLOCK) ON Invoice.PkInvoice = InvoiceTransactionXTab.FkInvoice
      WHERE InvoiceTransactionXTab.FkTransaction = Transactions.TransactionNumber
        AND Invoice.CancelDate IS NULL
    ),
    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,
    Transactions.MovedDeleted, Transactions.AccountNumber
  FROM Accounts WITH (NOLOCK)
  FULL OUTER JOIN Transactions WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
  INNER JOIN FOLIOS WITH (NOLOCK) ON Transactions.FolioNumber = FOLIOS.FolioNumber AND Folios.FolioNumber = @FolioNumber
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
  INNER JOIN Accounts AccountsVAT WITH (NOLOCK) ON  Ware.ResultAccountNumber = AccountsVAT.AccountNumber
  WHERE Accounts.AccountType = 6
  --AND ISNULL(MovedDeleted, 0) = 0
  GROUP BY Folios.FolioNumber, Transactions.Pieces, Transactions.PackageNumber, Transactions.WareNumber, Transactions.ProductNumber, PaxAdult, PaxChild1, PaxChild2,
    CurrencyNumber, TextOverwrite, Ware.ResultAccountNumber, Ware.BalanceAccountNumber, AccountsVAT.VATAccountNumber, AccountsVAT.VatValueNumber, FK_DepartmentNumber,
    Transactions.GuestNumber, ProductWareLinkNumber, Transactions.HotelCustomerNumber, Transactions.InvoiceBillNumber, Transactions.TransactionNumber, Transactions.MovedDeleted,
    Transactions.AccountNumber
  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
OPEN db_cur

FETCH NEXT FROM db_cur INTO @FolioNumber, @Pieces, @PackageNumber, @WareNumber, @ProductNumber, @Adult, @Children1, @Children2, 
  @CurrencyNumber, @TextOverwrite, @ResultAccountNumber, @BalanceAccountNumber, @VATAccountNumber, @VATValueNumber, @FK_DepartmentNumber,
  @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @FKInvoice,
  @PackageAmount, @MovedDeleted, @PacketAccountNumber

WHILE (@@fetch_status = 0)
BEGIN
  SET @ARecordFixed = 1
  IF @ShowPrint = 1
  BEGIN
    PRINT '------------------------'
    PRINT '@FolioNumber'
    PRINT @FolioNumber
    PRINT '@Pieces'
    PRINT @Pieces
    PRINT '@PackageNumber'
    PRINT @PackageNumber
    PRINT '@PackageAmount'
    PRINT CONVERT(VARCHAR, @PackageAmount)
    PRINT '@WareNumber'
    PRINT @WareNumber
    PRINT '@BalanceAccountNumber'
    PRINT @BalanceAccountNumber
    PRINT '@VATAccountNumber'
    PRINT @VATAccountNumber
    PRINT '@VATValueNumber'
    PRINT @VATValueNumber 
  END

  IF @PackageAmount < 0
    SET @NegPackageAmount = 1 
  ELSE
    SET @NegPackageAmount = 0
  
  SET @PackageAmount = ABS(@PackageAmount)

  EXEC GetVATValueFromVATValueNumber @VATValueNumber, @VatValueWare OUTPUT

  IF (@PackageAmount<>0)
  BEGIN
   IF @Pieces>0
     SET @VATOfPacketItem = @PackageAmount*(1.0-(1.0/CONVERT(FLOAT,((@VatValueWare/100.0)+1.0))))
   ELSE
     SET @VATOfPacketItem = ABS(-@PackageAmount*(1.0-(1.0/CONVERT(FLOAT,((@VatValueWare/100.0)+1.0)))))
  END

  IF @ShowPrint = 1
  BEGIN
    PRINT '@VATOfPacketItem'
    PRINT CONVERT(VARCHAR, @VATOfPacketItem)
    print '@PackageAmount - @VATOfPacketItem'
    print CONVERT(VARCHAR, @PackageAmount - @VATOfPacketItem)
  END

  EXEC GetNextNumber 'GlobalTransactionIdentity', 1, @GlobalTransactionNumber OUTPUT

  IF /*(@Pieces<0) OR*/ @NegPackageAmount = 1
  BEGIN
    -- Krediter vare på omsætning excl moms
    INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, OriginalDate, ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount - @VATOfPacketItem, @CurrencyNumber, 0, 0, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @ResultAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
    IF ISNULL(@FKInvoice, 0) > 0
      INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

    -- Debiter vare incl moms på balancekonto
    INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, OriginalDate,ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount, @CurrencyNumber, 0, 1, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @PacketAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
    IF ISNULL(@FKInvoice, 0) > 0
      INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

    -- Krediter momsen for varen på balancen (moms)
    INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATVAlue, FK_DepartmentNumber, OriginalDate,ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @VATOfPacketItem, @CurrencyNumber, 0, 0, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @VATAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
    IF ISNULL(@FKInvoice, 0) > 0
      INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)
  END
  ELSE
  BEGIN
    -- Debiter vare på omsætning på resultat excl moms
    INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, OriginalDate, ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount - @VATOfPacketItem, @CurrencyNumber, 0, 1, @MovedDeleted, @WareNumber, @TextOverwrite , 0, @ResultAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
    IF ISNULL(@FKInvoice, 0) > 0
      INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

    -- Krediter vare incl moms på balancekonto
    INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, OriginalDate, ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @PackageAmount, @CurrencyNumber, 0, 0, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @PacketAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
    IF ISNULL(@FKInvoice, 0) > 0
      INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

    -- Debiter momsen for varen på balancen (moms)
    INSERT INTO Transactions (GlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATVAlue, FK_DepartmentNumber, OriginalDate, ExchangeRateOverwrite, GuestNumber,ProductWareLinkNumber, HotelCustomerNumber, OriginalGlobalTransactionNumber, InvoiceBillNumber)
    VALUES(@GlobalTransactionNumber, @RealDateTimeForTransaction, @ProductNumber, @PackageNumber, @Adult, @Children1, @Children2, @RealDateTimeForTransaction, 1, @FolioNumber, @Pieces, @VATOfPacketItem, @CurrencyNumber, 0, 1, @MovedDeleted, @WareNumber, @TextOverwrite, 0, @VATAccountNumber, @VatValueWare, @FK_DepartmentNumber, @RealDateTimeForTransaction, 1, @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @GlobalTransactionNumber, @InvoiceBillNumber)
    IF ISNULL(@FKInvoice, 0) > 0
      INSERT INTO InvoiceTransactionXTab (FKInvoice, FKTransaction) VALUES (@FKInvoice, @@IDENTITY)

  END

  FETCH NEXT FROM db_cur INTO @FolioNumber, @Pieces, @PackageNumber, @WareNumber, @ProductNumber, @Adult, @Children1, @Children2, 
    @CurrencyNumber, @TextOverwrite, @ResultAccountNumber, @BalanceAccountNumber, @VATAccountNumber, @VATValueNumber, @FK_DepartmentNumber,
    @GuestNumber, @ProductWareLinkNumber, @HotelCustomerNumber, @InvoiceBillNumber, @FKInvoice,
    @PackageAmount, @MovedDeleted, @PacketAccountNumber
END

CLOSE db_cur
DEALLOCATE db_cur
END