CREATE PROCEDURE [dbo].[TransactionDecorateNew] @PkReceiptLayout SmallInt, @GetDatasetKind SmallInt, @FkCustomerHotel INT, @LocaleId INT
, @SortByOriginalDate BIT, @SortByDateRL BIT
AS
SET NOCOUNT ON
--nb20191223 Created: ParamSQL Sorting based on 2 level dataset (1. level based on receipt layout todo 2. level based on tax percent and eu spec rq limits)

DECLARE @DebugLevel TINYINT
SET @DebugLevel = 0
DECLARE @Date DATE
, @Pieces INT
, @Price MONEY
, @PriceXTax MONEY
, @PriceTax MONEY
, @Text VARCHAR(250)
, @Note VARCHAR(50)
, @TaxPercent INT
, @TransactionNumber INT
, @GuestNumber INT
, @Link INT
, @WareNumber INT
, @LogicSortNumber INT
, @ProductNumber INT
, @OriginalDate DATE
, @GroupByPeriod TINYINT --0:Stay; 1:Month; 2:Day
, @OrderRL INT
, @DoInsert TINYINT
, @HotelNumber INT
, @RoomTypeSortNumber INT
, @PriceSingle MONEY
, @Payment INT
, @PaymentWG INT
, @RoomTypeNumber INT
, @SumTableCreated BIT
, @InternalCourseProduction BIT
, @ProductLink INT
, @LinkX INT

DECLARE @DoSort BIT
, @GroupIndex INT
, @ExecSQL NVARCHAR(800)
, @UpdateSQL NVARCHAR(300)
, @WhereSQL NVARCHAR(500)
, @WhereSQLPre NVARCHAR(30)
, @WhereGroupByPeriodSQL NVARCHAR(100)
, @SumNo INT
, @SortText NVARCHAR(50)
, @OrderWGRLOrig INT
, @OrderWGRL INT--, @OrderNumber INT
, @OrderWRLOrig INT
, @OrderWRL INT
, @SortTextOverwrite BIT
, @RowCount INT
, @RowCountPackage INT
DECLARE @LinkSum INT, @LinkSumX INT
DECLARE @ParamDefs NVARCHAR(250)

SET @SumTableCreated = 0
SET @DoSort= 0
SELECT @HotelNumber = C.HotelNumber FROM dbo.Customer C WITH (NOLOCK) WHERE C.CustomerNumber = @FkCustomerHotel
SET @HotelNumber = ISNULL(@HotelNumber, -1)
IF @DebugLevel >= 1
BEGIN
  PRINT '---- Receipt Layout start ---'
  PRINT '@PkReceiptLayout = ' + CONVERT(NVARCHAR, ISNULL(@PkReceiptLayout, 0))
  PRINT '@dsKind = ' + CONVERT(NVARCHAR, ISNULL(@GetDatasetKind, 0))
/*
SELECT TC.PriceXTax, TC.PriceTax, TC.Price, CAST((TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1)) AS MONEY) AS PriceSingle
, TC.TaxPercent, TC.Pieces, ISNULL(LW.Name, TC.TextOverwrite) AS TextOverwrite
, TC.Note, TC.TransactionNumber, TC.WareNumber, TC.GuestNumber, TC.Date, TC.OriginalDate, TC.Link, TC.LinkX, TC.LogicSortNumber
, TC.PaymentWG, TC.OrderWGRL, TC.OrderW
, CASE WHEN TR.ProductWareLinkNumber > 0 THEN TR.ProductNumber ELSE NULL END AS ProductNumber, TR.ProductWareLinkNumber
, CASE WHEN W.Type=4 THEN 5 ELSE TR.Payment END AS Payment, RT.RoomtypeNumber, RT.SortNumber
FROM #TransactionCommon TC
  INNER JOIN dbo.Transactions TR ON TC.TransactionNumber = TR.TransactionNumber
  INNER JOIN dbo.Ware W WITH (NOLOCK) ON W.WareNumber = TR.WareNumber
  LEFT JOIN dbo.Rooms RM WITH (NOLOCK) ON CAST(RM.Number AS VARCHAR(50)) = TR.Note and (@HotelNumber = -1 OR RM.HotelNumber = @HotelNumber)
  LEFT JOIN dbo.RoomTypes RT WITH (NOLOCK) ON RT.RoomTypeNumber = RM.RoomTypeNumber
  LEFT JOIN dbo.Translation LW WITH (NOLOCK) ON LW.ObjectNumber = W.WareNumber AND LW.TYPE = 1 AND LW.CountryNumber = @LocaleId
WHERE TC.LogicSortNumber <> 99 --Do not disable, only main lines, package content is summerized in parallel
ORDER By Link, LogicSortNumber desc, TransactionNumber
*/
END
DECLARE #TransCur CURSOR FAST_FORWARD READ_ONLY FOR
SELECT TC.PriceXTax, TC.PriceTax, TC.Price, TC.TaxPercent, TC.Pieces, ISNULL(LW.Name, TC.TextOverwrite) AS TextOverwrite
, TC.Note, TC.TransactionNumber, TC.WareNumber, TC.GuestNumber, TC.Date, TC.OriginalDate, TC.Link, TC.LinkX, TC.LogicSortNumber
, TC.PaymentWG, TC.OrderWGRL, TC.OrderW
, CASE WHEN TR.ProductWareLinkNumber > 0 THEN TR.ProductNumber ELSE NULL END AS ProductNumber, TR.ProductWareLinkNumber
, CASE WHEN W.Type=4 THEN 5 ELSE TR.Payment END AS Payment, RT.RoomtypeNumber, RT.SortNumber
FROM #TransactionCommon TC (NOLOCK) 
  JOIN dbo.Transactions TR (NOLOCK) ON TC.TransactionNumber = TR.TransactionNumber
  JOIN dbo.Ware W WITH (NOLOCK) ON W.WareNumber = TR.WareNumber
  LEFT JOIN dbo.Rooms RM WITH (NOLOCK) ON CAST(RM.Number AS VARCHAR(50)) = TR.Note and (@HotelNumber = -1 OR RM.HotelNumber = @HotelNumber)
  LEFT JOIN dbo.RoomTypes RT WITH (NOLOCK) ON RT.RoomTypeNumber = RM.RoomTypeNumber
  LEFT JOIN dbo.Translation LW WITH (NOLOCK) ON LW.ObjectNumber = W.WareNumber AND LW.TYPE = 1 AND LW.CountryNumber = @LocaleId
WHERE TC.LogicSortNumber = 99 --Do not disable, only main lines, package content is summerized in parallel
ORDER By Link, LogicSortNumber desc, TransactionNumber
OPEN #TransCur
FETCH NEXT FROM #TransCur
INTO @PriceXTax, @PriceTax, @Price, @TaxPercent, @Pieces, @Text, @Note, @TransactionNumber, @WareNumber, @GuestNumber
, @Date, @OriginalDate, @Link, @LinkX, @LogicSortNumber, @PaymentWG, @OrderWGRL, @OrderWRL, @ProductNumber, @ProductLink, @Payment
, @RoomTypeNumber, @RoomTypeSortNumber
WHILE @@FETCH_STATUS = 0 -- safe
BEGIN
  SET @WhereSQL = NULL
  SET @SumNo = NULL
  SET @OrderRL = NULL
  SET @GroupByPeriod = NULL
  SET @OrderWGRL = NULL
  SET @OrderWRL = NULL
  SET @GroupIndex = NULL
  SET @Note = ISNULL(LTRIM(RTrim(@Note)), '')
  SET @Pieces = ISNULL(NULLIF(@Pieces,0), CASE WHEN @Price > 0 THEN 1 ELSE -1 END)
  SET @PriceSingle = @Price/ABS(@Pieces)
  IF @PriceXTax IS NULL AND @Payment = 5 
    SELECT @PriceXTax = SUM(PriceXTax), @PriceTax = SUM(PriceTax) FROM #TransactionCommon (NOLOCK) WHERE Link = @Link AND LogicSortNumber = 0

  SET @PriceXTax = ISNULL(@PriceXTax, @Price)
  SET @PriceTax = ISNULL(@PriceTax, 0)
  SET @Price = ISNULL(@Price, @PriceXTax + @PriceTax)
  SET @TaxPercent = ISNULL(@TaxPercent, 0)
  IF @DebugLevel >= 2
  BEGIN
    --PRINT '@WareNumber = ' + ISNULL(CONVERT(NVARCHAR, @WareNumber), '')
    PRINT '@ProductNumber = ' + ISNULL(CONVERT(NVARCHAR, @ProductNumber), '')
    PRINT '@Price = ' + ISNULL(CONVERT(NVARCHAR, @Price), '')
    PRINT '@Pieces = ' + ISNULL(CONVERT(NVARCHAR, @Pieces), '')
  END
  SET @WhereSQLPre = ' WHERE LogicSortNumber = 99'
  SET @WhereSQL = ''
  SELECT @GroupIndex=ISNULL([GroupIndex], 0)
  FROM WareGroupBillSortTypesXTab (NOLOCK) 
    JOIN Ware (NOLOCK) ON WareGroupBillSortTypesXTab.WaregroupNumber = Ware.WareGroupNumber
  WHERE (WareGroupBillSortTypesXTab.BillTypeNumber = @PkReceiptLayout) AND (Ware.WareNumber = @WareNumber)
  --nb20111102 On summerized overnight wares (Charge, charge product, null product and post days methods)insure EU spec and overnight vat split are correct 
  IF @GroupIndex = 0 and ISNULL(@ProductNumber, 0) <> -2
    IF ISNULL(@ProductNumber, 0) > 0 
      SET @WhereSQL = @WhereSQL +' AND ProductNumber > 0'
    ELSE IF @ProductNumber IS NULL
      SET @WhereSQL = @WhereSQL +' AND ProductNumber IS NULL'
    ELSE
      SET @WhereSQL = @WhereSQL +' AND ProductNumber='+Convert(NVARCHAR,@ProductNumber)
  SET @OrderWGRLOrig = ISNULL(@OrderWGRL, 1)
  SET @OrderWRLOrig = ISNULL(@OrderWRL, 1)
  SELECT @SumNo=SortTypeNumber, @OrderRL=FKBillSortOrder, @GroupByPeriod=GroupByPeriod, @SortText=Text, @OrderWGRL=OrderNumber
  , @SortTextOverwrite=TextOverwrite, @GroupIndex=ISNULL([GroupIndex], 0)
  FROM WareGroupBillSortTypesXTab RLWG WITH (NOLOCK) 
    JOIN Ware WITH (NOLOCK) ON RLWG.WaregroupNumber = Ware.WareGroupNumber
  WHERE (RLWG.BillTypeNumber = @PkReceiptLayout) AND (Ware.WareNumber = @WareNumber)
  SET @RowCount = @@RowCount
  SET @OrderWGRL = ISNULL(@OrderWGRL, @OrderWGRLOrig)
  SET @SumNo = ISNULL(@SumNo, 1)
  IF @DebugLevel >= 1
  BEGIN
    PRINT '--- Receipt Layout record start @TransactionNumber = ' + CONVERT(NVARCHAR, @TransactionNumber)
  END
  IF @DebugLevel >= 2
  BEGIN
    PRINT '@Note = ' + CONVERT(NVARCHAR, @Note)
    PRINT '@ProductNumber = ' + ISNULL(CONVERT(NVARCHAR, @ProductNumber), '')
    PRINT '@SortByDateRL = ' + ISNULL(CONVERT(NVARCHAR, @SortByDateRL), '')
    PRINT '@WhereSQL1= '+ISNULL(@WhereSQL, '')
    PRINT '--WaregroupDef exists end'
  END
  IF @RowCount > 0
  BEGIN
    IF @SumNo = 1 AND @GroupIndex <= 0 --if Specified try to get the ware sort setup              
    BEGIN
      SELECT @SumNo=SortTypeNumber, @OrderRL=FKBillSortOrder, @GroupByPeriod=GroupByPeriod, @SortText=ISNULL(L.Name, Text) 
	    , @OrderWRL=OrderNumber, @SortTextOverwrite=TextOverwrite --mli TextOverwrite is used to fill BillText with the new text
      FROM WareBillSortTypesXTab RLW WITH (NOLOCK) 
	      JOIN Ware WITH (NOLOCK) ON RLW.WareNumber = Ware.WareNumber
        LEFT JOIN Translation L (NOLOCK) ON L.ObjectNumber = RLW.ItemId AND L.CountryNumber = @LocaleId AND L.Type = 16 --ttReceiptLayoutWare
      WHERE (RLW.BillTypeNumber = @PkReceiptLayout) AND (Ware.WareNumber = @WareNumber)
      SET @RowCount = @@RowCount
      SET @OrderWRL = ISNULL(@OrderWRL, @OrderWRLOrig)
      SET @SumNo = ISNULL(@SumNo, 1)
      IF @RowCount = 0 --Waregroup specified sort setup used
      BEGIN
        SELECT @SumNo=SortTypeNumber, @OrderRL=FKBillSortOrder, @GroupByPeriod=GroupByPeriod, @SortText=ISNULL(L.Name, Text), @SortTextOverwrite=TextOverwrite
        FROM WareGroupBillSortTypesXTab RLW WITH (NOLOCK) 
  	      JOIN Ware WITH (NOLOCK) ON RLW.WaregroupNumber = Ware.WareGroupNumber
          LEFT JOIN Translation L (NOLOCK) ON L.ObjectNumber = RLW.ItemId AND L.CountryNumber = @LocaleId AND L.Type = 17 --ttReceiptLayoutWaregroup
        WHERE (RLW.BillTypeNumber = @PkReceiptLayout) AND (Ware.WareNumber = @WareNumber)
        -- Waregroup sort type= Specified and undefined ware sorttype = Specified => Transaction Specified
        SET @DoSort= 0
      END
      ELSE
      BEGIN
        SET @WhereSQL = @WhereSQL +' AND WareNumber='+Convert(NVARCHAR,@WareNumber)
        IF @SumNo = 1
          SET @DoSort= 0
        ELSE IF @SumNo > 0
          SET @DoSort= 1
        ELSE
          SET @DoSort= 0
      END
    END
    ELSE IF @GroupIndex > 0 --nb20050523
    BEGIN
      SET @DoSort= 1
      SET @WhereSQL = @WhereSQL +' AND WareNumber IN (SELECT DISTINCT WareNumber FROM Ware (NOLOCK)'
      +' JOIN WAREGROUPS WITH (NOLOCK) ON Ware.WareGroupNumber = Waregroups.WaregroupNumber'
      +' JOIN WaregroupBillSortTypesXtab WBT WITH (NOLOCK) ON WBT.WaregroupNumber = Waregroups.WaregroupNumber AND WBT.[GroupIndex] = ' 
	    + Convert(NVARCHAR, @GroupIndex) + ' AND WBT.BillTypeNumber= '+CONVERT(NVARCHAR, ISNULL(@PkReceiptLayout, 0))+')'
    END
    ELSE
    BEGIN
      SET @DoSort= 1              
      SET @WhereSQL = @WhereSQL + ' AND WareNumber In (SELECT DISTINCT W2.WareNumber FROM Ware W2 (NOLOCK)'
      +' JOIN Ware W1 (NOLOCK) ON W2.WareGroupNumber = W1.WareGroupNumber AND W1.WareNumber = ' + Convert(NVARCHAR, @WareNumber) + ')'
    END
    IF @GroupIndex > 0  --nb20050523 find the first waregroup in group
	BEGIN
      SELECT TOP 1 @GroupByPeriod=GroupByPeriod, @SortText=WareGroupBillSortTypesXTab.Text, @SortTextOverwrite=TextOverwrite, @OrderWGRL=OrderNumber, @Text=Waregroups.Text
      FROM WareGroupBillSortTypesXTab WITH (NOLOCK) 
	    JOIN Waregroups WITH (NOLOCK) ON Waregroups.WaregroupNumber= WareGroupBillSortTypesXTab.WaregroupNumber
      WHERE WareGroupBillSortTypesXTab.BillTypeNumber = @PkReceiptLayout and WareGroupBillSortTypesXTab.[GroupIndex] = @GroupIndex 
      ORDER BY WareGroupBillSortTypesXTab.OrderNumber   
      SET @OrderWGRL = ISNULL(@OrderWGRL, @OrderWGRLOrig)
	END
    IF @SortTextOverwrite = 1 SET @Text=@SortText
  END
  SET @GroupByPeriod = ISNULL(@GroupByPeriod, 0)
  SET @Text = RTrim(LTrim(ISNULL(@Text, '')))
  SET @SumNo = ISNULL(@SumNo, 1)
  SET @OrderRL= ISNULL(@OrderRL,0)
  SET @OrderWRL= ISNULL(@OrderWRL, @OrderWRLOrig)
  IF ISNULL(@GroupIndex, 0) = 0 AND @OrderRL > 0 
  BEGIN
    IF (@OrderRL=1) 
      SET @OrderRL= Convert(int, @GuestNumber)
    ELSE IF (@OrderRL=2) AND @Note<>''
      SET @OrderRL=Convert(int, @Note)
    ELSE IF (@OrderRL=3)
      SET @OrderRL=@RoomTypeSortNumber
  END
  --nb20211005 Rq3979 GroupByPeriod Month support added
  IF @GroupByPeriod = 1 
    IF @SortByOriginalDate = 0
      SET @WhereGroupByPeriodSQL = ' AND YEAR(Date)='+CONVERT(NVARCHAR, YEAR(@Date))+' AND MONTH(Date)='+CONVERT(NVARCHAR,MONTH(@Date))
    ELSE
      SET @WhereGroupByPeriodSQL = ' AND YEAR(Date)='+CONVERT(NVARCHAR,YEAR(@OriginalDate))+' AND MONTH(Date)='+CONVERT(NVARCHAR,MONTH(@OriginalDate))
  ELSE IF @SortByDateRL = 1 OR @GroupByPeriod = 2 OR @GetDatasetKind = 2 --EU spec rq by date
    IF @SortByOriginalDate = 0
      SET @WhereGroupByPeriodSQL = ' AND Convert(NVARCHAR,Date,112)='''+Convert(NVARCHAR,@Date,112)+'''' 
    ELSE
      SET @WhereGroupByPeriodSQL = ' AND Convert(NVARCHAR,OriginalDate,112)='''+Convert(NVARCHAR,@OriginalDate,112)+'''' 
  ELSE
    SET @WhereGroupByPeriodSQL = ''
  IF @WhereGroupByPeriodSQL <> ''
    SET @WhereSQL = @WhereSQL + @WhereGroupByPeriodSQL

  IF @DebugLevel >= 1
  BEGIN  
    print '@Text= '+ISNULL(Convert(varchar(250), @Text), '')
    PRINT '@WareNumber = ' + ISNULL(CONVERT(NVARCHAR, @WareNumber), '')
    PRINT '@SumNo=' + CONVERT(NVARCHAR, @SumNo)
    PRINT '@OrderRL = ' + ISNULL(CONVERT(NVARCHAR, @OrderRL), '')
    PRINT '@GroupByPeriod = ' + ISNULL(CONVERT(NVARCHAR, @GroupByPeriod), '')
    PRINT '@OrderWRL = ' + ISNULL(CONVERT(NVARCHAR, @OrderWRL), '')
    PRINT '@OrderWGRL = ' + ISNULL(CONVERT(NVARCHAR, @OrderWGRL), '')
    PRINT '@GroupIndex = ' + CONVERT(NVARCHAR, ISNULL(@GroupIndex, 0))
    PRINT '@SortText = ' + ISNULL(CONVERT(NVARCHAR, @SortText), '')
  END
  IF @DebugLevel >= 2
  BEGIN
    PRINT '@WhereGroupByPeriodSQL=' + ISNULL(@WhereGroupByPeriodSQL, '')
    PRINT '@SortTextOverwrite=' + CONVERT(NVARCHAR, @SortTextOverwrite)
    PRINT '@DoSort = ' + CONVERT(NVARCHAR, ISNULL(@DoSort, 0))
    PRINT '@WhereSQL2= '+ISNULL(@WhereSQL, '')
  END
  SET @RowCount=0
  SET @DoInsert=0
  SET @UpdateSQL= ''
  IF @DoSort=0
    SET @DoInsert = 1
  ELSE IF @GroupIndex > 0
  BEGIN
    SET @UpdateSQL= 'update #TransactionLayout SET Pieces=CASE WHEN @Price > 0 THEN 1 ELSE -1 END'
    +', PriceSingle= CASE WHEN PriceSingle = @PriceSingle THEN PriceSingle ELSE NULL END'
    +', Note=CASE WHEN Note=@Note THEN Note ELSE NULL END'
    +', Price=Price+@Price, PriceTax=PriceTax+@PriceTax, PriceXTax=PriceXTax+@PriceXTax'
    IF @ProductNumber = - 2 --(Kitchen Layout)
	  SET @WhereSQL= @WhereSQL+' AND Note=@Note'
    SET @DoInsert=1
  END
  ELSE IF @SumNo = 2 or @ProductNumber = -2 --and @SortTypeNumber = 6 --total price (align 4 Kitchen with price)
  BEGIN
    SET @UpdateSQL= 'update #TransactionLayout SET Pieces=Pieces+@Pieces'
    +', PriceSingle= CASE WHEN PriceSingle = @PriceSingle THEN PriceSingle ELSE NULL END'
    +', Note=CASE WHEN Note=@Note THEN Note ELSE NULL END, Price=Price+@Price, PriceTax=PriceTax+@PriceTax'
    +', PriceXTax=PriceXTax+@PriceXTax'
	IF @ProductNumber = - 2 --(Kitchen Layout)
	  SET @WhereSQL= @WhereSQL+' AND Note=@Note'
    SET @DoInsert=1
  END
  ELSE IF @SumNo = 3 --person / guest
  BEGIN
    IF (@Payment>0)
	  SET @UpdateSQL = 'CASE WHEN @Price > 0 THEN 1 ELSE -1 END'
	ELSE
	  SET @UpdateSQL = 'Pieces+@Pieces'
    SET @UpdateSQL= 'update #TransactionLayout SET Pieces='+@UpdateSQL
    +', PriceSingle= CASE WHEN PriceSingle = @Price THEN @Price ELSE NULL END, Price=Price+@Price'
    +', PriceTax=PriceTax + @PriceTax, PriceXTax=PriceXTax+@PriceXTax'
    SET @WhereSQL= @WhereSQL+' AND GuestNumber=@GuestNumber'
    SET @DoInsert=2
  END
  ELSE IF @SumNo = 4 --roomnumber
  BEGIN
    SET @UpdateSQL= 'update #TransactionLayout SET Pieces=Pieces+@Pieces, PriceSingle= CASE WHEN PriceSingle= @PriceSingle THEN PriceSingle ELSE NULL END'
    +', Price=Price+@Price, PriceTax=PriceTax+@PriceTax,PriceXTax=PriceXTax+@PriceXTax'
	SET @WhereSQL= @WhereSQL+' AND Note=@Note' 
    SET @DoInsert=1
  END
  ELSE IF @SumNo = 5 --roomtype
  BEGIN
    SET @UpdateSQL= 'update #TransactionLayout SET Pieces=Pieces+@Pieces, PriceSingle=CASE WHEN PriceSingle=@PriceSingle THEN PriceSingle ELSE NULL END'
    +', Price=Price+@Price, PriceTax=PriceTax+@PriceTax,PriceXTax=PriceXTax+@PriceXTax'
	SET @WhereSQL= @WhereSQL+' AND #TransactionLayout.Note IN (SELECT R.Number FROM RoomTypes RT (NOLOCK)'
    +' JOIN ROOMS R (NOLOCK) ON RT.RoomTypeNumber = R.RoomTypeNumber WHERE RT.RoomTypeNumber=@RoomTypeNumber)'
    SET @DoInsert=1
  END
  ELSE IF @SumNo = 6 --product price
  BEGIN
    IF @DebugLevel >= 2
    BEGIN
      PRINT '@PaymentWG= '+ISNULL(Convert(varchar, @PaymentWG), '')
      PRINT '@InternalCourseProduction= '+ISNULL(Convert(varchar, @InternalCourseProduction), '')
    END
    IF @SumTableCreated = 1 AND (@InternalCourseProduction = 0 AND NOT @PaymentWG = 3 OR 
      @InternalCourseProduction = 1 AND (NOT @PaymentWG in (3, 8) OR @GroupByPeriod = 1))
    BEGIN
      IF @InternalCourseProduction = 0 AND @PaymentWG = 2 OR @InternalCourseProduction = 1 AND @PaymentWG in (2, 3, 8)
        SET @UpdateSQL= 'CASE WHEN @Price > 0 THEN 1 ELSE -1 END, PriceSingle=Price+@Price'
      ELSE
        SET @UpdateSQL= 'Pieces+@Pieces'
      SET @UpdateSQL= 'update #Transaction_Sort SET Pieces='+@UpdateSQL
      +', Price=Price+@Price, Note= CASE WHEN Note=@Note THEN Note ELSE NULL END'
      +', PriceTax=PriceTax+@PriceTax, PriceXTax=PriceXTax+@PriceXTax'
      IF @InternalCourseProduction = 0 AND @PaymentWG = 2 OR @InternalCourseProduction = 1 AND @PaymentWG in (2, 3, 8)
		  SET @WhereSQL = @WhereSQL +' AND GuestNumber=@GuestNumber'
      ELSE
        SET @WhereSQL = @WhereSQL +' AND GuestNumber=@GuestNumber AND ABS(PriceSingle)=ABS(@PriceSingle)'
    END
    SET @DoInsert=3
--SET @UpdateSQL = ''
  END 
  ELSE IF @SumNo = 7 --Line text
  BEGIN
    SET @UpdateSQL= 'update #TransactionLayout SET Pieces=Pieces+@Pieces, PriceSingle= CASE WHEN PriceSingle=@PriceSingle THEN PriceSingle ELSE NULL END'                 
    +', Note=CASE WHEN Note=@Note THEN Note ELSE NULL END, Price=Price+@Price, PriceTax=PriceTax+@PriceTax'
    +', PriceXTax=PriceXTax+@PriceXTax'
    SET @WhereSQL = @WhereSQL +' AND TextOverwrite= @TextOverwrite'
    SET @DoInsert=1
  END
  ELSE
  BEGIN
    SET @DoInsert = 1
    SET @UpdateSQL= ''
    SET @OrderWGRL = @OrderWGRLOrig
	SET @OrderWRL = @OrderWRLOrig 
  END
  IF @UpdateSQL <> '' AND @GetDatasetKind = 2
    SET @WhereSQL = @WhereSQL +' AND TextOverwrite= @TextOverwrite AND ABS(PriceSingle)=ABS(@PriceSingle)'

  IF @RowCount = 0 AND @UpdateSQL <> ''
  BEGIN
    SET @ExecSQL= @UpdateSQL+@WhereSQLPre+@WhereSQL+' SELECT @rowc = @@rowcount'
    SET @ParamDefs = N'@Pieces INT, @PriceSingle MONEY, @Price MONEY, @PriceTax MONEY, @PriceXTax MONEY, @GuestNumber INT, @Note VARCHAR(50), @RoomTypeNumber INT, @TextOverwrite VARCHAR(255), @rowc int OUTPUT '
    EXEC SP_EXECUTESQL @ExecSQL, @ParamDefs, @Pieces, @PriceSingle, @Price, @PriceTax, @PriceXTax, @GuestNumber, @Note, @RoomTypeNumber, @Text, @RowCount OUTPUT      		
  END

  IF @DebugLevel >= 2
  BEGIN
    PRINT '@ParamDefs= '+ISNULL(@ParamDefs, '')
    PRINT '@ExecSQL = ' + ISNULL(@ExecSQL, '')
    PRINT '@UpdateSQL= '+ISNULL(@UpdateSQL, '')
    PRINT '@WhereSQLPre = ' + ISNULL(@WhereSQLPre, '')
    PRINT '@WhereSQL = ' + ISNULL(@WhereSQL, '')
    PRINT '@RowCountUpdate = ' + CONVERT(NVARCHAR, @RowCount)
    PRINT '@DoInsert = ' + CONVERT(NVARCHAR, @DoInsert)
  END
  IF @DebugLevel >= 2
    PRINT '@Price=' + ISNULL(CONVERT(NVARCHAR, @Price), 0)
  IF (@DoInsert=3 OR @SumNo = 6) AND (@SumTableCreated = 0)
  BEGIN
    CREATE TABLE dbo.#Transaction_Sort(
      PriceXTax money not null
    , PriceTax money not null
    , Price Money not null
    , TaxPercent Money not null
    , Pieces Integer not null
    , TextOverwrite VARCHAR(255) not null
    , Note Varchar(50) null
    , WareNumber int not null
    , GuestNumber int null
    , Date Date null
    , OriginalDate Date null
	  , LogicSortNumber INT not null
    , OrderWGRL int not null
    , OrderWRL int null
	  , OrderRL int null
    , PriceSingle Money null
    , ProductNumber int null
    , ProductLink int null
	, GroupByPeriod tinyint not null
    , Link INT NOT NULL
    , LinkX INT NOT NULL
    , TransactionNumber INT NOT NULL
    )
    SET @SumTableCreated=1
    SELECT @InternalCourseProduction = ISNULL(dbo.GetHotelSetupKeyValue ('InternalCourseProduction', @FkCustomerHotel), 0)
  END
  IF @RowCount = 0
  BEGIN
    IF @DoInsert=3
    BEGIN
      IF @SumNo = 6 AND @SortTextOverwrite = 0 AND @ProductNumber > 0
	  BEGIN
        SELECT @Text = BillTextProduct FROM Products (NOLOCK) WHERE ProductNumber = @ProductNumber 
		IF ISNULL(@Text, '') = ''
          SELECT @Text = WareName FROM Ware (NOLOCK) WHERE WareNumber = @WareNumber 
      END 
      INSERT INTO #Transaction_Sort (Date, Pieces, PriceSingle, Price, TaxPercent, PriceTax, PriceXTax, Note, OriginalDate
	    , WareNumber, GuestNumber, ProductNumber, OrderWGRL, OrderWRL, OrderRL, TextOverwrite, ProductLink, GroupByPeriod
	    , LogicSortNumber, Link, LinkX, TransactionNumber) VALUES
      (@Date, @Pieces, @PriceSingle, @Price, @TaxPercent, @PriceTax, @PriceXTax, @Note, @OriginalDate
	    , @WareNumber, @GuestNumber, @ProductNumber, @OrderWGRL, @OrderWRL, @OrderRL, @Text, @ProductLink, @GroupByPeriod
	    , @LogicSortNumber, @Link, @LinkX, @TransactionNumber)
	    IF @GetDatasetKind = 2
        INSERT INTO #Transaction_Sort 
        SELECT SUM(TC.PriceXTax), SUM(TC.PriceTax), SUM(TC.PriceXTax + TC.PriceTax), MAX(TC.TaxPercent), MAX(TC.Pieces)
        , TC.TextOverwrite, MAX(TC.Note), TC.WareNumber, MAX(TC.GuestNumber), TC.Date, MAX(TC.OriginalDate), MAX(TC.LogicSortNumber)
        , MAX(TC.OrderWGRL), MAX(TC.OrderW), MAX(@OrderRL), (TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1))
        , @ProductNumber, @ProductLink, @GroupByPeriod, @Link, @LinkX, MAX(TC.TransactionNumber)
        FROM #TransactionCommon TC (NOLOCK) 
          JOIN dbo.Transactions TR (NOLOCK) ON TC.TransactionNumber = TR.TransactionNumber
	    WHERE TC.Link = @Link AND TC.LinkX = @LinkX and TC.LogicSortNumber <> 99  
	    GROUP BY TC.WareNumber, TC.DATE, TC.TextOverwrite, (TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1))
	    ELSE --IF @GetDatasetKind = 1
        INSERT INTO #Transaction_Sort 
        SELECT SUM(TC.PriceXTax), SUM(TC.PriceTax), SUM(TC.PriceXTax + TC.PriceTax), TC.TaxPercent, MAX(TC.Pieces)
        , @Text + ' ' + CONVERT(VARCHAR, CAST(TC.TaxPercent AS INT)) + '%', MAX(TC.Note), MAX(TC.WareNumber), MAX(TC.GuestNumber)
        , MAX(TC.Date), MAX(TC.OriginalDate), MAX(TC.LogicSortNumber), MAX(TC.OrderWGRL), MAX(TC.OrderW), MAX(@OrderRL), NULL
	    , @ProductNumber, @ProductLink, @GroupByPeriod, @Link, @LinkX, MAX(TC.TransactionNumber)
	    FROM #TransactionCommon TC (NOLOCK) 
          JOIN dbo.Transactions TR (NOLOCK) ON TC.TransactionNumber = TR.TransactionNumber
	    WHERE TC.Link = @Link AND TC.LinkX = @LinkX and TC.LogicSortNumber <> 99  
	    GROUP BY TC.TaxPercent
    END
    ELSE IF @DoInsert <> 0
    BEGIN
      --IF @DoInsert=1 --outcommented because there is no obvious reason and no comment to explain
	  --  SET @GuestNumber = NULL
      INSERT INTO #TransactionLayout (Date, Pieces, Price, PriceXTax, PriceTax, TextOverwrite, Note, TaxPercent, TransactionNumber
      , WareNumber, OrderWGRL, OrderWRL, OrderRL, PriceSingle, OriginalDate, ProductNumber, LogicSortNumber, GuestNumber
      , Link, LinkX) VALUES
      (@Date, @Pieces, @Price, @PriceXTax, @PriceTax, @Text, @Note, @TaxPercent, @TransactionNumber
      , @WareNumber, @OrderWGRL, @OrderWRL, @OrderRL, @PriceSingle, @OriginalDate, @ProductNumber, @LogicSortNumber, @GuestNumber
      , @Link, @LinkX)
	    IF @GetDatasetKind = 2
	      INSERT INTO #TransactionLayout 
	        SELECT SUM(TC.PriceXTax), SUM(TC.PriceTax), SUM(TC.PriceXTax + TC.PriceTax), MAX(TC.TaxPercent), SUM(TC.Pieces)
          , TC.TextOverwrite, MAX(TC.Note), MAX(TC.TransactionNumber), TC.WareNumber, MAX(TC.GuestNumber), TC.Date
          , MAX(TC.OriginalDate), MAX(TC.LogicSortNumber), MAX(TC.OrderWGRL), MAX(TC.OrderW), @OrderRL
          , (TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1)), 0, @ProductNumber, @Link, @LinkX
	      FROM #TransactionCommon TC (NOLOCK) 
            JOIN dbo.Transactions TR (NOLOCK) ON TC.TransactionNumber = TR.TransactionNumber
	      WHERE TC.Link = @Link AND TC.LinkX = @LinkX AND TC.LogicSortNumber <> 99 --WHERE TC.Link = @Link AND TC.LinkX = @LinkX AND TR.GlobalTransactionNumber = @Link and TC.LogicSortNumber <> 99 --Link = PLWN on produkts
	      GROUP BY TC.WareNumber, TC.DATE, TC.TextOverwrite, (TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1))
      ELSE --IF @GetDatasetKind = 1
      BEGIN
	    SELECT @Pieces = Pieces FROM #TransactionLayout (NOLOCK) WHERE Link= @Link and LinkX = @LinkX and LogicSortNumber = 99
        SET @Pieces = ISNULL(@Pieces, 0)
        INSERT INTO #TransactionLayout 
	        SELECT SUM(TC.PriceXTax), SUM(TC.PriceTax), SUM(TC.PriceXTax + TC.PriceTax), TC.TaxPercent, CASE WHEN @Pieces > 0 THEN @Pieces ELSE SUM(TC.Pieces) END
		    , @Text  + ' ' + CONVERT(VARCHAR, CAST(TC.TaxPercent AS INT)) + '%', MAX(TC.Note), MAX(TC.TransactionNumber)
            , MAX(TC.WareNumber), MAX(TC.GuestNumber), MAX(TC.Date), MAX(TC.OriginalDate), MAX(TC.LogicSortNumber), MAX(TC.OrderWGRL)
	        , MAX(TC.OrderW), @OrderRL, NULL, 0, @ProductNumber, @Link, @LinkX
	        FROM #TransactionCommon TC (NOLOCK) 
              JOIN dbo.Transactions TR (NOLOCK) ON TC.TransactionNumber = TR.TransactionNumber
	        WHERE TC.Link = @Link AND TC.LinkX = @LinkX AND TC.LogicSortNumber <> 99 --WHERE TC.Link = @Link AND TC.LinkX = @LinkX AND TR.GlobalTransactionNumber = @Link and TC.LogicSortNumber <> 99 --Link = PLWN on produkts
	        GROUP BY TC.TaxPercent
      END
	    SET @RowCountPackage = @@RowCount
	  END
  END
  ELSE
  BEGIN
    IF @SumNo = 6
    BEGIN
      SET @ExecSQL= 'SELECT @LinkSum = Link, @LinkSumX = LinkX FROM #Transaction_Sort (NOLOCK) WHERE LogicSortNumber = 99'+@WhereSQL
      +' SELECT @rowc = @@rowcount'
      SET @ParamDefs = @ParamDefs + N', @LinkSum INT OUTPUT, @LinkSumX INT OUTPUT'
      EXEC SP_EXECUTESQL @ExecSQL, @ParamDefs, @Pieces, @PriceSingle, @Price, @PriceTax, @PriceXTax, @GuestNumber, @Note
      , @RoomTypeNumber, @Text, @RowCount OUTPUT, @LinkSum OUTPUT, @LinkSumX OUTPUT     
      IF @DebugLevel >= 2 
      BEGIN
        print '@RowCount= '+ISNULL(Convert(varchar, @RowCount), '')
        print '@LinkSum= '+ISNULL(Convert(varchar, @LinkSum), '')
        print '@LinkSumX= '+ISNULL(Convert(varchar, @LinkSumX), '')
      END
	  IF @RowCount = 1
	  BEGIN
        IF @DebugLevel >= 2
          PRINT 'Update'
	      IF @GetDatasetKind = 2
  	        UPDATE #Transaction_Sort SET #Transaction_Sort.Pieces= #Transaction_Sort.Pieces + TS.Pieces
            , #Transaction_Sort.Price=#Transaction_Sort.Price+(TS.PriceXTax + TS.PriceTax)
	        , #Transaction_Sort.PriceTax=#Transaction_Sort.PriceTax+TS.PriceTax, #Transaction_Sort.PriceXTax=#Transaction_Sort.PriceXTax+TS.PriceXTax
            FROM 
	          (SELECT SUM(Pieces) AS Pieces, SUM(PriceXTax) AS PriceXTax, SUM(PriceTax) AS PriceTax, WareNumber
              , MAX(Link) AS Link, MAX(LinkX) AS LinkX, Date, TextOverwrite, Price/ABS(ISNULL(NULLIF(Pieces,0),1)) AS PriceSingle
	          FROM #TransactionCommon (NOLOCK) 
	          WHERE Link = @Link AND LinkX = @LinkX AND LogicSortNumber <> 99
		      GROUP BY WareNumber, DATE, TextOverwrite, TaxPercent, Price/ABS(ISNULL(NULLIF(Pieces,0),1))) AS TS
	        WHERE #Transaction_Sort.LogicSortNumber <> 99 AND #Transaction_Sort.Link = @LinkSum AND #Transaction_Sort.LinkX = @LinkSumX
              AND TS.WareNumber = #Transaction_Sort.WareNumber AND TS.DATE = #Transaction_Sort.Date 
              AND TS.TextOverwrite COLLATE DATABASE_DEFAULT = #Transaction_Sort.TextOverwrite COLLATE DATABASE_DEFAULT AND TS.PriceSingle = #Transaction_Sort.PriceSingle
		  ELSE
  	        UPDATE #Transaction_Sort SET #Transaction_Sort.Pieces= #Transaction_Sort.Pieces + TS.Pieces
            , #Transaction_Sort.Price=#Transaction_Sort.Price+(TS.PriceXTax + TS.PriceTax)
	        , #Transaction_Sort.PriceTax=#Transaction_Sort.PriceTax+TS.PriceTax
            , #Transaction_Sort.PriceXTax=#Transaction_Sort.PriceXTax+TS.PriceXTax
            FROM 
              (SELECT SUM(Pieces) AS Pieces, SUM(PriceXTax) AS PriceXTax, SUM(PriceTax) AS PriceTax, MAX(Link) AS Link, MAX(LinkX) AS LinkX, TaxPercent		    
              FROM #TransactionCommon (NOLOCK) 
              WHERE Link = @Link AND LinkX = @LinkX AND LogicSortNumber <> 99
              GROUP BY TaxPercent) AS TS 
            WHERE #Transaction_Sort.LogicSortNumber <> 99 AND #Transaction_Sort.Link = @LinkSum AND #Transaction_Sort.LinkX = @LinkSumX
              AND #Transaction_Sort.TaxPercent = TS.TaxPercent
        IF @DebugLevel >= 2
          print '@RowCountUpdate= '+ISNULL(Convert(varchar, @@RowCount), '')
	      IF @GetDatasetKind = 2
          INSERT INTO #Transaction_Sort
            SELECT SUM(TC.PriceXTax), SUM(TC.PriceTax), SUM(TC.PriceXTax + TC.PriceTax), MAX(TC.TaxPercent), SUM(TC.Pieces), TC.TextOverwrite
            , MAX(TC.Note), TC.WareNumber, MAX(TC.GuestNumber), TC.Date, MAX(TC.OriginalDate), MAX(TC.LogicSortNumber), MAX(TC.OrderWGRL)
            , MAX(TC.OrderW), NULL--OrderRL if needed to be layout based then do it later
	          , (TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1)), MAX(TR.ProductNumber), MAX(TR.ProductWareLinkNumber),  2 --Day GroupByPeriod if needed to be layout based then do it later
	          , @LinkSum, @LinkSumX, MAX(TC.TransactionNumber)
	          FROM #TransactionCommon TC (NOLOCK) 
	            JOIN Transactions TR (NOLOCK) ON TC.TransactionNumber = TR.TransactionNumber
		        LEFT JOIN #Transaction_Sort TS (NOLOCK) ON TC.WareNumber = TS.WareNumber AND TC.Date = TS.Date 
                  AND (TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1)) = TS.PriceSingle AND TS.Link = @LinkSum AND TS.LinkX = @LinkSumX
	          WHERE TC.Link = @Link AND TC.LinkX = @LinkX AND TC.LogicSortNumber <> 99 AND TS.WareNumber IS NULL
	          GROUP BY TC.WareNumber, TC.DATE, TC.TextOverwrite, (TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1))
 	      ELSE --IF @GetDatasetKind = 1
          INSERT INTO #Transaction_Sort
            SELECT SUM(TC.PriceXTax), SUM(TC.PriceTax), SUM(TC.PriceXTax + TC.PriceTax), TC.TaxPercent, SUM(TC.Pieces), MAX(TC.TextOverwrite)
            , MAX(TC.Note), MAX(TC.WareNumber), MAX(TC.GuestNumber), MAX(TC.Date), MAX(TC.OriginalDate), MAX(TC.LogicSortNumber)
            , MAX(TC.OrderWGRL), MAX(TC.OrderW), NULL--OrderRL if needed to be layout based then do it later
            , SUM((TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1))), MAX(TR.ProductNumber), MAX(TR.ProductWareLinkNumber),  2 --Day GroupByPeriod if needed to be layout based then do it later
            , @LinkSum, @LinkSumX, MAX(TC.TransactionNumber)
            FROM #TransactionCommon TC (NOLOCK) 
              JOIN Transactions TR (NOLOCK) ON TC.TransactionNumber = TR.TransactionNumber
              LEFT JOIN #Transaction_Sort TS (NOLOCK) ON TC.TaxPercent = TS.TaxPercent 
                AND TS.Link = @LinkSum AND TS.LinkX = @LinkSumX
            WHERE TC.Link = @Link AND TC.LinkX = @LinkX AND TC.LogicSortNumber <> 99 AND TS.WareNumber IS NULL
            GROUP BY TC.TaxPercent
        IF @DebugLevel >= 2
         print '@RowCountInsert= '+ISNULL(Convert(varchar, @@RowCount), '')
	    END	   
    END
    ELSE
    BEGIN
      IF @GroupByPeriod = 0
        SET @Date = NULL
      SET @ParamDefs = N'@OrderWGRL INT, @OrderWRL INT, @Price MONEY, @Date DATE, @GroupByPeriod TINYINT, @LinkSum INT OUTPUT, @LinkSumX INT OUTPUT'
      IF @SumNo <> 3 
      BEGIN
        --SET @ExecSQL= 'SELECT @LinkSum = Link, @LinkSumX = LinkX FROM #TransactionLayout (NOLOCK) WHERE OrderWGRL=@OrderWGRL AND OrderWRL=@OrderWRL AND PriceSingle=@Price AND'+
		--nb20221003 Layout.Sum.Total on menu wares fixed
	    IF @SumNo = 2
	      SET @ExecSQL= ''
		ELSE
	      SET @ExecSQL= ' PriceSingle=@Price AND'
        SET @ExecSQL= 'SELECT @LinkSum = Link, @LinkSumX = LinkX FROM #TransactionLayout (NOLOCK) WHERE OrderWGRL=@OrderWGRL AND OrderWRL=@OrderWRL AND'+@ExecSQL+

	    ' (@GroupByPeriod = 0 OR @GroupByPeriod=2 AND CONVERT(DATE, Date, 112)=@Date OR @GroupByPeriod=1 AND YEAR(Date)=YEAR(@Date) AND MONTH(Date)=MONTH(@Date))'
        EXEC SP_EXECUTESQL @ExecSQL, @ParamDefs, @OrderWGRL, @OrderWRL, @Price, @Date, @GroupByPeriod, @LinkSum OUTPUT, @LinkSumX OUTPUT
      END
      ELSE --Person
      BEGIN
        SET @ExecSQL= 'SELECT @LinkSum = Link, @LinkSumX = LinkX FROM #TransactionLayout (NOLOCK) WHERE GuestNumber=@OrderWGRL AND'+
	    ' (@GroupByPeriod = 0 OR @GroupByPeriod=2 AND CONVERT(DATE, Date, 112)=@Date OR @GroupByPeriod=1 AND YEAR(Date)=YEAR(@Date) AND MONTH(Date)=MONTH(@Date))'
        EXEC SP_EXECUTESQL @ExecSQL, @ParamDefs, @GuestNumber, @OrderWRL, @Price, @Date, @GroupByPeriod, @LinkSum OUTPUT, @LinkSumX OUTPUT
      END
      IF @DebugLevel >= 2
      BEGIN
        PRINT '@LinkSum= '+ISNULL(Convert(varchar, @LinkSum), '')
--        PRINT '@GuestNumber = ' + ISNULL(Convert(varchar, @GuestNumber), '')
--        PRINT '@Date = ' + ISNULL(Convert(varchar, @Date), '')
	    END
      IF ISNULL(@LinkSum, 0) <> 0
	      IF @GetDatasetKind = 2
        BEGIN
  	      UPDATE #TransactionLayout SET #TransactionLayout.Pieces=#TransactionLayout.Pieces + TS.Pieces
          , #TransactionLayout.Price=#TransactionLayout.Price+(TS.PriceXTax + TS.PriceTax)
	        , #TransactionLayout.PriceTax=#TransactionLayout.PriceTax+TS.PriceTax
          , #TransactionLayout.PriceXTax=#TransactionLayout.PriceXTax+TS.PriceXTax
          FROM 
	          (SELECT SUM(Pieces) AS Pieces, SUM(PriceXTax) AS PriceXTax, SUM(PriceTax) AS PriceTax, WareNumber, MAX(Link) AS Link
            , MAX(LinkX) AS LinkX, Date, CAST((TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1)) AS MONEY) AS PriceSingle
            , TC.TaxPercent, TC.TextOverwrite
	          FROM #TransactionCommon TC (NOLOCK) 
	          WHERE Link = @Link AND LinkX = @LinkX AND LogicSortNumber <> 99
		        GROUP BY WareNumber, DATE, TaxPercent, CAST((TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1)) AS MONEY), TextOverWrite 
            ) AS TS 
          WHERE LogicSortNumber <> 99 AND #TransactionLayout.Link = @LinkSum AND #TransactionLayout.LinkX = @LinkSumX 
            AND #TransactionLayout.WareNumber = TS.WareNumber AND #TransactionLayout.Date = TS.Date 
            AND #TransactionLayout.PriceSingle = TS.PriceSingle AND #TransactionLayout.TextOverwrite COLLATE DATABASE_DEFAULT = TS.TextOverwrite COLLATE DATABASE_DEFAULT

          INSERT INTO #TransactionLayout 
            SELECT SUM(TC.PriceXTax), SUM(TC.PriceTax), SUM(TC.PriceXTax + TC.PriceTax), MAX(TC.TaxPercent), SUM(TC.Pieces)
            , TC.TextOverwrite
            , MAX(TC.Note), MAX(TC.TransactionNumber), TC.WareNumber, MAX(TC.GuestNumber)
            , TC.Date
            , MAX(TC.OriginalDate), MAX(TC.LogicSortNumber), MAX(TC.OrderWGRL), MAX(TC.OrderW), NULL--OrderRL if needed to be layout based then do it later
	          , CAST((TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1)) AS MONEY)
            , NULL, MAX(TR.ProductNumber), @LinkSum, @LinkSumX
	          FROM #TransactionCommon TC (NOLOCK) 
	            JOIN Transactions TR (nolock) on TC.TransactionNumber = TR.TransactionNumber
		        LEFT JOIN #TransactionLayout TL (NOLOCK) ON TC.WareNumber = TL.WareNumber AND TC.Date = TL.Date 
                  AND TL.Link = @LinkSum AND TL.LinkX = @LinkSumX AND TC.TextOverwrite  COLLATE DATABASE_DEFAULT = TL.TextOverwrite COLLATE DATABASE_DEFAULT
	          WHERE TC.Link = @Link AND TC.LinkX = @LinkX AND TC.LogicSortNumber <> 99 AND TL.WareNumber IS NULL
	          GROUP BY TC.WareNumber, TC.DATE, TC.TextOverwrite, CAST((TC.PriceXTax + TC.PriceTax)/ABS(ISNULL(NULLIF(TC.Pieces,0),1)) AS MONEY)
        END
		ELSE IF @SumNo <> 7 --CR254814 Pcs+Price is multiplied by record count on one group post only (was unable to figure out why)
        BEGIN
          SELECT @Pieces = Pieces FROM #TransactionLayout (NOLOCK) WHERE Link= @LinkSum and LinkX = @LinkSumX and LogicSortNumber = 99
          SET @Pieces = ISNULL(@Pieces, 0)
  	      UPDATE #TransactionLayout SET #TransactionLayout.Pieces= @Pieces
          , #TransactionLayout.Price=#TransactionLayout.Price+(TS.PriceXTax + TS.PriceTax)
	        , #TransactionLayout.PriceTax=#TransactionLayout.PriceTax+TS.PriceTax
          , #TransactionLayout.PriceXTax=#TransactionLayout.PriceXTax+TS.PriceXTax
          FROM 
	          (SELECT SUM(PriceXTax) AS PriceXTax, SUM(PriceTax) AS PriceTax, MAX(Link) AS Link
              , MAX(LinkX) AS LinkX, TaxPercent
	          FROM #TransactionCommon TC (NOLOCK) 
	          WHERE Link = @Link AND LinkX = @LinkX AND LogicSortNumber <> 99
		      GROUP BY TaxPercent) AS TS 
          WHERE LogicSortNumber <> 99 AND #TransactionLayout.Link = @LinkSum AND #TransactionLayout.LinkX = @LinkSumX AND
            #TransactionLayout.TaxPercent = TS.TaxPercent
	      IF @GetDatasetKind = 2
          INSERT INTO #TransactionLayout
            SELECT SUM(TC.PriceXTax), SUM(TC.PriceTax), SUM(TC.PriceXTax + TC.PriceTax), TC.TaxPercent, CASE WHEN @Pieces > 0 THEN @Pieces ELSE SUM(TC.Pieces) END
		        , @Text + ' '+CONVERT(VARCHAR, CAST(TC.TaxPercent AS INT)) + '%'
		        , MAX(TC.Note), MAX(TC.TransactionNumber), MAX(TC.WareNumber), MAX(TC.GuestNumber), MAX(TC.Date), MAX(TC.OriginalDate)
            , MAX(TC.LogicSortNumber), MAX(TC.OrderWGRL), MAX(TC.OrderW), NULL--OrderRL if needed to be layout based then do it later
	          , NULL, NULL, MAX(TR.ProductNumber), @LinkSum, @LinkSumX
	          FROM #TransactionCommon TC (NOLOCK) 
	            JOIN Transactions TR (nolock) on TC.TransactionNumber = TR.TransactionNumber
		          LEFT JOIN #TransactionLayout TL (NOLOCK) ON TC.TaxPercent = TL.TaxPercent 
              AND TL.Link = @LinkSum AND TL.LinkX = @LinkSumX
	          WHERE TC.Link = @Link AND TC.LinkX = @LinkX AND TC.LogicSortNumber <> 99 AND TL.TaxPercent IS NULL
  	        GROUP BY TC.TaxPercent
        END
    END
  END
  IF @DebugLevel >= 1
  begin
    print '@Link= '+ISNULL(Convert(varchar, @Link), '')
    print '@RowCountPackage= '+ISNULL(Convert(varchar, @RowCountPackage), '')
    print '@Pieces= '+ISNULL(Convert(varchar, @Pieces), '')    
    PRINT '---Receipt Layout record end @SumNo=' + CONVERT(NVARCHAR, @SumNo)
  end
  FETCH NEXT FROM #TransCur
  INTO @PriceXTax, @PriceTax, @Price, @TaxPercent, @Pieces, @Text, @Note, @TransactionNumber, @WareNumber, @GuestNumber
  , @Date, @OriginalDate, @Link, @LinkX, @LogicSortNumber, @PaymentWG, @OrderWGRL, @OrderWRL, @ProductNumber, @ProductLink
  , @Payment, @RoomTypeNumber, @RoomTypeSortNumber
END
CLOSE #TransCur
DEALLOCATE  #TransCur

IF @SumTableCreated=1 --summerrize additionaly the person sum on price
BEGIN
  /*SELECT * FROM #Transaction_Sort 
	WHERE LogicSortNumber = 2
  ORDER BY LogicSortNumber, ProductLink */
  DECLARE #TransCur Cursor FAST_FORWARD Read_Only For
    SELECT Date, Pieces, PriceSingle, Price, TaxPercent, PriceTax, PriceXTax, Note, OriginalDate, WareNumber, GuestNumber
	, ProductNumber, OrderWGRL, OrderWRL, TextOverwrite, ProductLink, GroupByPeriod, LogicSortNumber, Link, LinkX
  , TransactionNumber  
	FROM #Transaction_Sort WITH (nolock)
	WHERE LogicSortNumber = 99
	ORDER BY ProductLink
  OPEN #TransCur
  FETCH NEXT FROM #TransCur
  INTO @Date, @Pieces, @PriceSingle, @Price, @TaxPercent, @PriceTax, @PriceXTax, @Note, @OriginalDate, @WareNumber, @GuestNumber
	, @ProductNumber, @OrderWGRL, @OrderWRL, @Text, @ProductLink, @GroupByPeriod, @LogicSortNumber, @Link, @LinkX, @TransactionNumber
  WHILE (@@FETCH_STATUS = 0) -- safe
  BEGIN
    IF @DebugLevel >= 2 
      PRINT '--- Receipt Layout #Transaction_Sort record BEGIN ---'
    IF @DebugLevel >= 1 
    BEGIN
      print '@SortByDateRL= '+ISNULL(Convert(varchar, @SortByDateRL), '')
      print '@WareNumber= '+Convert(varchar, @WareNumber)
      print '@TextOverwrite= '+Convert(varchar, @Text)
      print '@OrderWGRL= '+Convert(varchar, @OrderWGRL)
      print '@OrderWRL= '+Convert(varchar, @OrderWRL)
      print '@Price= '+Convert(varchar, @Price)
      print '@PriceSingle= '+Convert(varchar, @PriceSingle)
      print '@Date= '+ISNULL(Convert(varchar, @Date, 112), '')
    END
--/*
    IF @DebugLevel >= 2 
      PRINT '-UPDATE Main'     
	IF @GroupByPeriod = 2 --nb20211005 Rq3979 GroupByPeriod Month support added
      UPDATE #TransactionLayout SET Pieces= Pieces + @Pieces, Price= Price+@Price, PriceTax=PriceTax+@PriceTax
      , PriceXTax=PriceXTax+@PriceXTax, Note= CASE WHEN Note<>@Note THEN ' ' END
      WHERE Summerize=1 AND OrderWGRL=@OrderWGRL AND OrderWRL=@OrderWRL AND PriceSingle=@PriceSingle 
        AND YEAR(Date)=YEAR(@Date) AND MONTH(Date)=MONTH(@Date)
	ELSE IF @SortByDateRL = 1 OR @GroupByPeriod = 2
	BEGIN
	  SET @GroupByPeriod = 2
      UPDATE #TransactionLayout SET Pieces= Pieces + @Pieces, Price= Price+@Price, PriceTax=PriceTax+@PriceTax
      , PriceXTax=PriceXTax+@PriceXTax, Note= CASE WHEN Note<>@Note THEN ' ' END
      WHERE Summerize=1 AND OrderWGRL=@OrderWGRL AND OrderWRL=@OrderWRL AND PriceSingle=@PriceSingle 
	    AND CONVERT(DATE, Date, 112)=@Date
	END
	ELSE
      UPDATE #TransactionLayout SET Pieces= Pieces + @Pieces, Price= Price+@Price, PriceTax=PriceTax+@PriceTax
      , PriceXTax=PriceXTax+@PriceXTax, Note= CASE WHEN Note<>@Note THEN ' ' END
      WHERE Summerize=1 AND OrderWGRL=@OrderWGRL AND OrderWRL=@OrderWRL AND PriceSingle=@PriceSingle 
--*/
	IF @@RowCount = 0 --nb20180926 vars not part of cursor can create issues, let us see
	BEGIN
      IF @DebugLevel >= 1 
		PRINT '-INSERT Main' 
      INSERT INTO #TransactionLayout(Date, Pieces, PriceSingle, Price, TextOverwrite, TaxPercent, PriceTax, Note, OrderWGRL, OrderWRL
      , PriceXTax, OriginalDate, WareNumber, Summerize, ProductNumber, GuestNumber, LogicSortNumber, Link, LinkX, TransactionNumber) VALUES
      (@Date, @Pieces, @PriceSingle, @Price, @Text, @TaxPercent, @PriceTax, @Note, @OrderWGRL, @OrderWRL
      , @PriceXTax, @OriginalDate, @WareNumber, 1, @ProductNumber, @GuestNumber, @LogicSortNumber, @Link, @LinkX, @TransactionNumber)
	    INSERT INTO #TransactionLayout 
        SELECT TS.PriceXTax, TS.PriceTax, (TS.PriceXTax + TS.PriceTax), TS.TaxPercent, TS.Pieces, TS.TextOverwrite, TS.Note
        , TS.TransactionNumber, TS.WareNumber, TS.GuestNumber, TS.Date, TS.OriginalDate, TS.LogicSortNumber, TS.OrderWGRL, TS.OrderWRL
        , TS.OrderRL, (TS.PriceXTax + TS.PriceTax)/ABS(ISNULL(NULLIF(TS.Pieces,0),1)), 1, TS.ProductNumber, TS.Link, TS.LinkX
	      FROM #Transaction_Sort TS (NOLOCK) 
        WHERE TS.Link = @Link AND TS.LinkX = @LinkX AND TS.LogicSortNumber <> 99
      IF @DebugLevel >= 2 
      BEGIN
        PRINT '-INSERT Content' 
	    PRINT '@Link= '+ISNULL(Convert(varchar, @Link), '')
	  END
	END
    ELSE
	  BEGIN
      SET @ExecSQL= 'SELECT @LinkSum = Link, @LinkSumX = LinkX FROM #TransactionLayout (NOLOCK) WHERE Summerize=1 AND OrderWGRL=@OrderWGRL AND OrderWRL=@OrderWRL AND PriceSingle=@Price AND'+
	    ' (@GroupByPeriod = 0 OR @GroupByPeriod=2 AND CONVERT(DATE, Date, 112)=@Date OR @GroupByPeriod=1 AND YEAR(Date)=YEAR(@Date) AND MONTH(Date)=MONTH(@Date))'
      SET @ParamDefs = N'@OrderWGRL INT, @OrderWRL INT, @Price MONEY, @Date DATE, @GroupByPeriod TINYINT, @LinkSum INT OUTPUT, @LinkSumX INT OUTPUT'
      EXEC SP_EXECUTESQL @ExecSQL, @ParamDefs, @OrderWGRL, @OrderWRL, @PriceSingle, @Date, @GroupByPeriod, @LinkSum OUTPUT, @LinkSumX OUTPUT     
      IF @DebugLevel >= 1
      BEGIN
        PRINT '--UPDATE Content' 
        PRINT '@LinkSum= '+ISNULL(Convert(varchar, @LinkSum), '')
        PRINT '@Link= '+ISNULL(Convert(varchar, @Link), '')
        PRINT '@LinkX= '+ISNULL(Convert(varchar, @LinkX), '')
--          PRINT '@ExecSQL = ' + ISNULL(@ExecSQL, '')
      END
      IF ISNULL(@LinkSum, 0) <> 0
	    BEGIN
    	  IF @GetDatasetKind = 2
  	      UPDATE #TransactionLayout SET #TransactionLayout.Pieces=#TransactionLayout.Pieces + TS.Pieces
          , #TransactionLayout.Price=#TransactionLayout.Price+(TS.PriceXTax + TS.PriceTax)
	        , #TransactionLayout.PriceTax=#TransactionLayout.PriceTax+TS.PriceTax
          , #TransactionLayout.PriceXTax=#TransactionLayout.PriceXTax+TS.PriceXTax
          FROM 
	          (SELECT SUM(Pieces) AS Pieces, SUM(PriceXTax) AS PriceXTax, SUM(PriceTax) AS PriceTax, WareNumber, MAX(Link) AS Link
            , MAX(LinkX) AS LinkX, Date, TS.PriceSingle, TS.TextOverwrite
	          FROM #Transaction_Sort TS (NOLOCK) 
	          WHERE Link = @Link AND LinkX = @LinkX AND LogicSortNumber <> 99
		        GROUP BY TS.WareNumber, TS.DATE, TS.PriceSingle, TS.TextOverwrite) AS TS 
	        WHERE LogicSortNumber <> 99 AND #TransactionLayout.Link = @LinkSum AND #TransactionLayout.LinkX = @LinkSumX 
          AND #TransactionLayout.PriceSingle = TS.PriceSingle AND #TransactionLayout.WareNumber = TS.WareNumber 
          AND #TransactionLayout.DATE = TS.DATE --AND #TransactionLayout.TextOverwrite COLLATE DATABASE_DEFAULT = TS.TextOverwrite COLLATE DATABASE_DEFAULT --updates pcs on prooductpart on konference layouts
        ELSE
          UPDATE #TransactionLayout SET #TransactionLayout.Pieces=#TransactionLayout.Pieces + TS.Pieces
          , #TransactionLayout.Price=#TransactionLayout.Price+(TS.PriceXTax + TS.PriceTax)
	        , #TransactionLayout.PriceTax=#TransactionLayout.PriceTax+TS.PriceTax
          , #TransactionLayout.PriceXTax=#TransactionLayout.PriceXTax+TS.PriceXTax
          FROM 
	          (SELECT SUM(Pieces) AS Pieces, SUM(PriceXTax) AS PriceXTax, SUM(PriceTax) AS PriceTax, MAX(Link) AS Link
            , MAX(LinkX) AS LinkX, TS.TaxPercent
	          FROM #Transaction_Sort TS (NOLOCK) 
	          WHERE Link = @Link AND LinkX = @LinkX AND LogicSortNumber <> 99
		        GROUP BY TS.TaxPercent) AS TS 
	        WHERE LogicSortNumber <> 99 AND #TransactionLayout.Link = @LinkSum AND #TransactionLayout.LinkX = @LinkSumX 
          AND #TransactionLayout.TaxPercent = TS.TaxPercent
        IF @DebugLevel >= 1
          print '@RowCountUpdate= '+ISNULL(Convert(varchar, @@RowCount), '')
	      IF @GetDatasetKind = 2
          INSERT INTO #TransactionLayout
            SELECT SUM(TS.PriceXTax), SUM(TS.PriceTax), SUM(TS.PriceXTax + TS.PriceTax), MAX(TS.TaxPercent), SUM(TS.Pieces)
            , TS.TextOverwrite, MAX(TS.Note), MAX(TS.TransactionNumber), TS.WareNumber, MAX(TS.GuestNumber), TS.Date
            , MAX(TS.OriginalDate), MAX(TS.LogicSortNumber), MAX(TS.OrderWGRL), MAX(TS.OrderWRL), MAX(TS.OrderRL)
	          , TS.PriceSingle, 1, MAX(TS.ProductNumber), @LinkSum, @LinkSumX
	          FROM #Transaction_Sort TS (NOLOCK) 
		          LEFT JOIN #TransactionLayout TL (NOLOCK) ON TS.WareNumber = TL.WareNumber AND TS.Date = TL.Date 
              AND TS.PriceSingle = TL.PriceSingle AND TL.Link = @LinkSum AND TL.LinkX = @LinkSumX
	          WHERE TS.Link = @Link AND TS.LinkX = @LinkX AND TS.LogicSortNumber <> 99 AND TL.WareNumber IS NULL
            GROUP BY TS.WareNumber, TS.DATE, TS.PriceSingle, TS.TextOverwrite
        IF @DebugLevel >= 1
         print '@RowCountInsert= '+ISNULL(Convert(varchar, @@RowCount), '')
	    END
	  END	   
    IF @DebugLevel >= 2 
      PRINT '--- Receipt Layout #Transaction_Sort record END ---'
    FETCH NEXT FROM #TransCur
    INTO @Date, @Pieces, @PriceSingle, @Price, @TaxPercent, @PriceTax, @PriceXTax, @Note, @OriginalDate, @WareNumber, @GuestNumber
	  , @ProductNumber, @OrderWGRL, @OrderWRL, @Text, @ProductLink, @GroupByPeriod, @LogicSortNumber, @Link, @LinkX, @TransactionNumber
  END
  CLOSE #TransCur
  DEALLOCATE #TransCur
  DROP TABLE #Transaction_Sort
END

IF @DebugLevel >= 1 
BEGIN
  PRINT '---- Receipt Layout END ---'
END
