CREATE PROCEDURE [dbo].[GetTransactionNew] @GetDatasetKind SmallInt, @PkFolio int, @PkInvoice int, @PkReceiptLayout SmallInt, @LocaleId Int AS
SET NOCOUNT ON
--nb20191223 Created: 2 level dataset; partly decorated in delphi ported completly to sp; data reduced to absolutly minimum required 4 layout calc
--@GetDatasetKind: 0=Internal (Picasso receipt: kitchen/prebill/invoice); 1=eInvoice; 2=EU spec;
--@LinkX: 0: Ware; -1: Transfer; -2: Product; -3: Menu; Payment N
DECLARE @DebugLevel TINYINT
SET @DebugLevel = 0
DECLARE @MaxWareGroupSortNumber INT
SELECT @MaxWareGroupSortNumber = MAX(WareGroupNumber) FROM Waregroups WITH (NOLOCK)
IF @PkInvoice < 0 SET @PkInvoice = NULL
;WITH RelevantCTE (FkTransaction, GTN, WareNumber, WaregroupNumber, WareSortNumber, WareType) AS (
SELECT DISTINCT T.Transactionnumber, T.GlobalTransactionNumber, T.WareNumber, W.WaregroupNumber, W.SortNumber, W.Type
FROM dbo.Transactions T WITH (NOLOCK) 
  JOIN dbo.Ware W ON W.WareNumber = T.WareNumber
  LEFT JOIN dbo.InvoiceTransactionXtab ITX WITH (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
WHERE T.FolioNumber=@PkFolio AND (NOT @PkInvoice IS NULL AND ITX.FkInvoice=@PkInvoice OR ISNULL(@PkInvoice, 0) = 0) AND T.MovedDeleted = 0 
  AND (W.PrintZeroPrice = 1 OR T.PriceOverwrite <> 0)
EXCEPT --Settled when Prebill
SELECT T.Transactionnumber, T.GlobalTransactionNumber, T.WareNumber, W.WaregroupNumber, W.SortNumber, W.Type
FROM dbo.Transactions T WITH (NOLOCK) 
  JOIN dbo.Ware W ON W.WareNumber = T.WareNumber
  JOIN dbo.InvoiceTransactionXtab ITX WITH(NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
  JOIN dbo.Invoice I WITH(NOLOCK) ON ITX.FkInvoice = I.PkInvoice AND I.ReceiptType <> 0 AND NOT I.PkInvoice IS NULL AND I.CancelDate IS NULL
WHERE T.FolioNumber = @PkFolio AND @PkInvoice = 0 AND (NOT I.PkInvoice IS NULL AND I.CancelDate IS NULL OR I.ReceiptType = 0)
),
TransferContentCTE (Pieces, Price, TextOverwrite, GTN, WareNumber, TransactionNumber, AccountType, Balance, PaymentWG, OrderWGRL, OrderW) AS (
  SELECT SUM(T.Pieces)
  , SUM(T.PriceOverwrite * CASE T.Debit WHEN 0 THEN 1 ELSE -1 END) 
  , ISNULL(LTRIM(RTRIM(MAX(T.TextOverwrite))), '') AS TextOverwrite, T.GlobalTransactionnumber
  , T.WareNumber, MAX(T.TransactionNumber) AS TransactionNumber
  , A.AccountType, A.Balance
  , WG.Payment, WG.SortNumber, W.SortNumber
  FROM dbo.Transactions T
    JOIN dbo.Ware W ON W.WareNumber = T.WareNumber 
    JOIN dbo.Accounts A WITH (NOLOCK) ON T.AccountNumber = A.AccountNumber
    JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = W.WareGroupNumber
  WHERE T.FolioNumber = @PkFolio AND T.GlobalTransactionNumber IN (
    SELECT GlobalTransactionnumber
    FROM RelevantCTE T WITH (NOLOCK)
    WHERE T.WareType = 4
    )
    AND T.MovedDeleted = 0 AND T.ShowOnBillFlag = 0
  GROUP BY CONVERT(Date, T.DateStamp, 112), T.GlobalTransactionNumber, T.WareNumber--, T.PriceOverwrite, ISNULL(LTRIM(RTRIM(T.TextOverwrite)), '') --NOT Possible because the joins will multiply with multiple records
  , A.AccountType, A.Balance, WG.Payment, WG.SortNumber, W.SortNumber
  )
--Avoid settled transactions using SettledCTE as helper
--Transfer content using TransferContentCTE as helper
SELECT * 
INTO #TransactionCommon
FROM (--Payment and Wares
SELECT PXTV.PriceXTax, ISNULL(PTV.PriceTax, 0) AS PriceTax, TR.PriceOverwrite*(CASE TR.Debit WHEN 0 THEN -1 ELSE 1 END) AS Price
, TR.VatValue AS TaxPercent, TR.Pieces, ISNULL(LTRIM(RTRIM(TR.TextOverwrite)), '') AS TextOverwrite, TR.Note, TR.TransactionNumber, TR.WareNumber, TR.GuestNumber
, CONVERT(Date, TR.DateStamp, 112) AS Date, ISNULL(CONVERT(Date, TR.OriginalDate, 112), CONVERT(Date, TR.DateStamp, 112)) AS OriginalDate
, TR.GlobalTransactionNumber AS Link, CASE WHEN W.IsMenuWare = 1 AND @GetDatasetKind > 0 THEN -3 ELSE TR.Payment END AS LinkX, 99 AS LogicSortNumber
, WG.Payment AS PaymentWG
, WG.SortNumber AS OrderWGRL--Modified later when all data loaded in temp table
, RT.WareSortNumber AS OrderW
FROM dbo.Transactions TR WITH (NOLOCK)
  LEFT JOIN dbo.TPriceXTaxView PXTV WITH (NOLOCK) ON PXTV.GlobalTransactionNumber = TR.GlobalTransactionNumber and PXTV.FolioNumber = TR.FolioNumber
  LEFT JOIN dbo.TPriceTaxView PTV WITH (NOLOCK) ON PTV.GlobalTransactionNumber = TR.GlobalTransactionNumber and PTV.FolioNumber = TR.FolioNumber
  JOIN dbo.Ware W WITH (NOLOCK) ON W.WareNumber = TR.WareNumber
  JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = W.WareGroupNumber
  JOIN RelevantCTE RT ON RT.FkTransaction = TR.TransactionNumber
WHERE (ISNULL(TR.ProductWareLinkNumber, 0) = 0 OR ISNULL(TR.ProductWareLinkNumber, -1) = -1) AND RT.WareType <> 4 AND TR.moveddeleted = 0 AND TR.ShowOnBillFlag = 1
UNION --Transfer as ware
SELECT SUM(ISNULL(T.Price, 0)) AS PriceXTax
, SUM(ISNULL(T1.Price, 0)) AS PriceTax
, SUM(ISNULL(T.Price, 0)) + SUM(ISNULL(T1.Price, 0)) AS Price
, TR.VATValue AS TaxPercent
, SUM(ISNULL(T.Pieces, 0)) AS Pieces, LTRIM(RTRIM(MAX(ISNULL(T.TextOverwrite, '')))) AS TextOverwrite, MAX(ISNULL(TR.Note, '')) AS Note, Max(T.TransactionNumber) AS TransactionNumber, T.WareNumber, TR.GuestNumber
, CONVERT(Date, TR.DateStamp, 112) AS Date, CONVERT(Date, TR.OriginalDate, 112) AS OriginalDate
, T.GTN AS Link, -1 AS LinkX, 0 AS LogicSortNumber, T.PaymentWG, T.OrderWGRL, T.OrderW
FROM TransferContentCTE T
  LEFT JOIN TransferContentCTE T1 WITH (NOLOCK) ON T.GTN = T1.GTN AND T.WareNumber = T1.WareNumber AND NOT T1.AccountType in (4,5,6) and T1.Balance = 2
	JOIN Transactions TR (NOLOCK) ON T.TransactionNumber = TR.TransactionNumber
WHERE  @GetDatasetKind > 0 AND (NOT T.AccountType in (5,6) and T.Balance = 1 OR T.AccountType = 4 and T.Balance = 2)
  AND TR.MovedDeleted = 0 --performance gain with this param
GROUP BY CONVERT(Date, TR.DateStamp, 112), CONVERT(Date, TR.OriginalDate, 112), T.GTN, T.WareNumber, TR.GuestNumber, TR.VATValue, T.PaymentWG, T.OrderWGRL, T.OrderW
UNION --Transfer main post Identified by ShowOnBillFlag = 1 and connected with content using same GTN
SELECT NULL As PriceXTax, NULL AS PriceTax, (TR.PriceOverwrite * VS.SignedValue) AS Price, NULL AS TaxPercent
, TR.Pieces AS Pieces, ISNULL(LTRIM(RTRIM(TR.TextOverwrite)), '')
, CASE WHEN @GetDatasetKind = 0 THEN TR.Note ELSE (SELECT Top 1 CASE WHEN IA.Note IS NULL THEN FA.Note ELSE IA.Note END FROM dbo.TransactionTransactionXtab TTX WITH (NOLOCK)
    JOIN dbo.Transactions TA WITH (NOLOCK) ON TA.TransactionNumber = TTX.FK_TransactionNumber1
    JOIN dbo.Folios FA WITH (NOLOCK) ON FA.FolioNumber = TA.FolioNumber
    LEFT JOIN dbo.InvoiceTransactionXTab ITXA WITH (NOLOCK) ON ITXA.FkTransaction = TTX.FK_TransactionNumber1
    LEFT JOIN dbo.Invoice IA WITH (NOLOCK) ON IA.PkInvoice = ITXA.FkInvoice
  WHERE TTX.FK_TransactionNumber2 = TR.TransactionNumber
  ORDER BY PkInvoice DESC) END AS Note
, TR.TransactionNumber, TR.WareNumber, TR.GuestNumber
, CONVERT(Date, TR.DateStamp, 112) AS Date, CONVERT(Date, TR.OriginalDate, 112) AS OriginalDate
, TR.GlobalTransactionnumber AS Link, -1 AS LinkX, 99 AS LogicSortNumber
, WG.Payment AS PaymentWG, WG.SortNumber AS OrderWGRL, RT.WareSortNumber AS OrderW
FROM dbo.Transactions TR
  JOIN dbo.Accounts A ON TR.AccountNumber = A.AccountNumber
  LEFT JOIN dbo.TransactionTransactionXtab TTX (NOLOCK) ON TTX.FK_TransactionNumber1 = TR.TransactionNumber 
  LEFT JOIN TSignPaymentWGWView VS (NOLOCK) ON VS.TransactionNumber = TR.TransactionNumber
  JOIN RelevantCTE RT ON RT.FkTransaction = TR.TransactionNumber AND RT.Waretype = 4
  JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = RT.WareGroupNumber
WHERE TR.ShowOnBillFlag = 1 AND TR.MovedDeleted = 0 --performance gain with this param
UNION --Product
SELECT (SELECT SUM(T2.PriceOverwrite * (CASE T2.Debit WHEN 1 THEN -1 ELSE 1 END)) FROM dbo.Transactions T2 WITH (NOLOCK) 
    JOIN dbo.Accounts A2 WITH (NOLOCK) ON T2.AccountNumber = A2.AccountNumber AND NOT A2.AccountType in (5,6) and A2.Balance = 1
	WHERE TR.ProductWareLinkNumber = T2.ProductWareLinkNumber
      AND T2.folionumber = TR.FolioNumber
      AND T2.MovedDeleted = 0) AS PriceXTax
  , (SELECT SUM(T1.PriceOverwrite * (CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END)) FROM dbo.Transactions T1 WITH (NOLOCK) 
      JOIN dbo.Accounts A1 WITH (NOLOCK) ON T1.AccountNumber = A1.AccountNumber AND NOT A1.AccountType in (5,6) and A1.Balance = 2
    WHERE T1.ProductWareLinkNumber = TR.ProductWareLinkNumber AND T1.folionumber = TR.FolioNumber AND T1.MovedDeleted = 0) AS PriceTax
  , SUM(TR.PriceOverwrite*(CASE TR.Debit WHEN 0 THEN -1 ELSE 1 END)) AS Price
  , TR.VatValue AS TaxPercent, SUM(TR.Pieces) AS Pieces, LTRIM(RTRIM(MAX(ISNULL(TR.TextOverwrite, '')))), MAX(ISNULL(TR.Note, '')), MAX(TR.TransactionNumber) AS TransactionNumber
  , TR.WareNumber, TR.GuestNumber, CONVERT(Date, TR.DateStamp, 112) AS Date, CONVERT(Date, TR.OriginalDate, 112) AS OriginalDate
  , TR.ProductWareLinkNumber AS Link, -2 AS LinkX, 99 AS LogicSortNumber
  , WG.Payment AS PaymentWG, WG.SortNumber AS OrderWGRL, RT.WareSortNumber AS OrderW
  FROM dbo.Transactions TR WITH (NOLOCK)
    JOIN dbo.Accounts A WITH (NOLOCK) ON A.AccountNumber = TR.AccountNumber and A.AccountType in (5,6)
    JOIN RelevantCTE RT ON RT.FkTransaction = TR.TransactionNumber
    JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = RT.WareGroupNumber
  WHERE ISNULL(TR.ProductWareLinkNumber, 0) > 0 AND TR.ShowOnBillFlag = 1
    AND TR.MovedDeleted = 0 --performance gain with this param
--  AND TR.GuestNumber = 1
  GROUP BY CONVERT(Date, TR.OriginalDate, 112), CONVERT(Date, TR.DateStamp, 112), TR.VatValue
  , TR.WareNumber, TR.GuestNumber, TR.ProductWareLinkNumber, TR.GlobalTransactionNumber, TR.FolioNumber, WG.Payment
  , WG.SortNumber, RT.WareSortNumber
  UNION 
  SELECT SUM(T2.PriceOverwrite * (CASE T2.Debit WHEN 1 THEN -1 ELSE 1 END)) AS PriceXTax
  , SUM(T1.PriceOverwrite * (CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END)) AS PriceTax
  , NULL AS Price, TR.VatValue AS TaxPercent, SUM(TR.Pieces) AS Pieces, LTRIM(RTRIM(MAX(ISNULL(TR.TextOverwrite, '')))), MAX(ISNULL(TR.Note, ''))
  , MAX(TR.TransactionNumber) AS TransactionNumber, TR.WareNumber, TR.GuestNumber
  , CONVERT(Date, TR.DateStamp, 112) AS Date
  , CONVERT(Date, TR.OriginalDate, 112) AS OriginalDate
  , TR.ProductWareLinkNumber AS Link, -2 AS LinkX
  , CASE WHEN (WG.Payment IN (7,8) AND (SELECT COUNT(*) FROM Transactions TR1 WITH (NOLOCK) WHERE TR1.GlobalTransactionNumber = TR.GlobalTransactionNumber and TR1.ShowOnBillFlag=1) = 0) THEN 1 ELSE WG.Payment END AS LogicSortNumber
  , WG.Payment AS PaymentWG, WG.SortNumber AS OrderWGRL, RT.WareSortNumber AS OrderW
  FROM dbo.Transactions TR WITH (NOLOCK)
    JOIN dbo.Accounts A WITH (NOLOCK) ON A.AccountNumber = TR.AccountNumber and A.AccountType in (5,6)
    LEFT JOIN dbo.Transactions T1 WITH (NOLOCK) ON TR.GlobalTransactionNumber = T1.GlobalTransactionNumber
      AND T1.folionumber = TR.FolioNumber
      AND T1.MovedDeleted = 0
    JOIN dbo.Accounts A1 WITH (NOLOCK) ON T1.AccountNumber = A1.AccountNumber AND NOT A1.AccountType in (5,6) and A1.Balance = 2
    LEFT JOIN dbo.Transactions T2 WITH (NOLOCK) ON TR.GlobalTransactionNumber = T2.GlobalTransactionNumber
      AND T2.folionumber = TR.FolioNumber
      AND T2.MovedDeleted = 0
    JOIN dbo.Accounts A2 WITH (NOLOCK) ON T2.AccountNumber = A2.AccountNumber AND NOT A2.AccountType in (5,6) and A2.Balance = 1
    JOIN RelevantCTE RT ON RT.FkTransaction = TR.TransactionNumber 
    JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = RT.WareGroupNumber
  WHERE  @GetDatasetKind > 0 AND ISNULL(TR.ProductWareLinkNumber, 0) > 0 
    AND TR.MovedDeleted = 0 --performance gain with this param
    --    AND TR.GuestNumber = 1
  GROUP BY CONVERT(Date, TR.OriginalDate, 112), CONVERT(Date, TR.DateStamp, 112), TR.VatValue
  , WG.ExternalCashBoxCode, WG.Payment, TR.WareNumber, TR.GuestNumber, TR.ProductWareLinkNumber, TR.GlobalTransactionNumber
  , TR.ShowOnBillFlag, WG.SortNumber, RT.WareSortNumber
UNION --Menu Ware Content
SELECT SUM(T1.PriceOverwrite*(CASE TM.Debit WHEN 0 THEN -1 ELSE 1 END)) AS PriceXTax, SUM(T.PriceOverwrite*(CASE TM.Debit WHEN 0 THEN -1 ELSE 1 END)) AS PriceTax
, SUM(T1.PriceOverwrite*(CASE TM.Debit WHEN 0 THEN -1 ELSE 1 END)) + SUM(T.PriceOverwrite*(CASE TM.Debit WHEN 0 THEN -1 ELSE 1 END)) AS Price
, TR.VATValue AS TaxPercent
, SUM(T.Pieces) AS Pieces, ISNULL(LTRIM(RTRIM(MAX(T.TextOverwrite))), ''), MAX(TR.Note), Max(T.TransactionNumber) AS TransactionNumber, T.WareNumber, TR.GuestNumber
, CONVERT(Date, TR.DateStamp, 112) AS Date, CONVERT(Date, TR.OriginalDate, 112) AS OriginalDate
, T.GlobalTransactionNumber AS Link, -3 AS LinkX, 0 AS LogicSortNumber
, WG.Payment AS PaymentWG
, WG.SortNumber AS OrderWGRL--Modified later when all data loaded in temp table
, RT.WareSortNumber AS OrderW
FROM dbo.Transactions T
  JOIN dbo.Accounts A WITH (NOLOCK) ON T.AccountNumber = A.AccountNumber AND NOT A.AccountType in (5,6) and A.Balance = 2
  LEFT JOIN dbo.Transactions T1 WITH (NOLOCK) ON T.GlobalTransactionNumber = T1.GlobalTransactionNumber AND T.WareNumber = T1.WareNumber AND T1.MovedDeleted = 0 AND T1.ShowOnBillFlag=0 
  JOIN dbo.Accounts A1 WITH (NOLOCK) ON T1.AccountNumber = A1.AccountNumber AND NOT A1.AccountType in (5,6) and A1.Balance = 1
  JOIN Transactions TR (NOLOCK) ON T.TransactionNumber = TR.TransactionNumber AND T.MovedDeleted = 0 
  JOIN dbo.Transactions TM (NOLOCK) ON TM.GlobalTransactionNumber = TR.GlobalTransactionNumber AND TM.ShowOnBillFlag = 1 AND TM.moveddeleted = 0 AND TR.ShowOnBillFlag= 0
  JOIN dbo.Ware WM (NOLOCK) ON TM.WareNumber = WM.WareNumber AND WM.IsMenuWare = 1
  JOIN RelevantCTE RT ON RT.FkTransaction = TR.TransactionNumber AND TR.MovedDeleted = 0 --performance gain with this param
  JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = RT.WareGroupNumber
WHERE  @GetDatasetKind > 0 
GROUP BY CONVERT(Date, TR.DateStamp, 112), CONVERT(Date, TR.OriginalDate, 112), T.GlobalTransactionNumber, T.WareNumber, TR.GuestNumber, TR.VATValue
, WG.Payment, WG.SortNumber, RT.WareSortNumber
) TC
--WHERE TC.Link = 356941
--WHERE TC.GuestNumber = 27
DECLARE @SortByOriginalDate BIT, @SortByDateRL BIT, @FkCustomerHotel INT
IF ISNULL(@PkInvoice, 0) > 0 
  SELECT @FkCustomerHotel = FkCustomerHotel FROM Invoice WITH (NOLOCK) WHERE PkInvoice = @PkInvoice
ELSE
  SELECT @FkCustomerHotel = HotelCustomerNumber FROM Folios WITH (NOLOCK) WHERE FolioNumber = @PkFolio
SELECT @SortByOriginalDate = ISNULL(dbo.GetHotelSetupKeyValue ('SortTransByOriginalDateOnBill', @FkCustomerHotel), 0)
  UPDATE #TransactionCommon SET OrderWGRL = ISNULL(CASE 
    WHEN PaymentWG in (0,1) THEN OrderWGRL + 4-- ware and payment then offset
    WHEN PaymentWG in (2,7) THEN 1 -- rooms          
    WHEN PaymentWG in (3,8) THEN 2 -- Halls
    WHEN PaymentWG = 4    THEN @MaxWareGroupSortNumber + 1-- Fees
    WHEN PaymentWG = 5   THEN 3-- Provision          
  END, 0)

CREATE TABLE dbo.#TransactionLayout(
  PriceXTax MONEY NOT NULL
  , PriceTax MONEY NOT NULL
  , Price MONEY NOT NULL
  , TaxPercent INT NOT NULL
  , Pieces INT NOT NULL
  , TextOverwrite VARCHAR(250) NOT NULL
  , Note VARCHAR(50) NULL
  , TransactionNumber INT NULL
  , WareNumber INT NOT NULL
  , GuestNumber INT NULL --4 grouping by receipt layout not used on client
  , Date DATETIME NOT NULL
  , OriginalDate DATETIME NOT NULL
  , LogicSortNumber SMALLINT NOT NULL
  , OrderWGRL INT NOT NULL --WareGroupSortNumberOnBill int not null,
  , OrderWRL INT NOT NULL  --WareOrderNumber int null,
  , OrderRL INT NULL
  , PriceSingle MONEY NULL
  , Summerize bit NULL
  , ProductNumber INT NULL --4 grouping by receipt layout
  , Link INT NOT NULL
  , LinkX INT NOT NULL
)
  SELECT @SortByDateRL = OrderByDate FROM Billtypes WITH (NOLOCK) WHERE BillTypeNumber = @PkReceiptLayout 
  SET @SortByDateRL = ISNULL(@SortByDateRL, 0)
  EXEC dbo.TransactionDecorateNew @PkReceiptLayout, @GetDatasetKind, @FkCustomerHotel, @LocaleId, @SortByOriginalDate, @SortByDateRL

  DECLARE @LOrderBySQL VARCHAR(150), @LSelectSQL VARCHAR(600), @LDistinctSQL VARCHAR(10)
  SET @LSelectSQL = ''
  SET @LDistinctSQL = ''
  SET @LOrderBySQL = 'ORDER BY '
  IF @PkReceiptLayout<=0
    IF @SortByOriginalDate = 0
      SET @LOrderBySQL = @LOrderBySQL+'OrderWGRL, Date, OriginalDate, TransactionNumber'
    ELSE
      SET @LOrderBySQL = @LOrderBySQL+'OrderWGRL, OriginalDate, Date, OrderWRL, OrderRL, TransactionNumber'
  ELSE  
    IF @SortByOriginalDate = 0
      IF @SortByDateRL = 0
        SET @LOrderBySQL = @LOrderBySQL+'OrderWGRL, OrderWRL, OrderRL, Date, OriginalDate, TransactionNumber'
      ELSE
        SET @LOrderBySQL = @LOrderBySQL+'Date, OriginalDate, OrderWGRL, OrderWRL, OrderRL, TransactionNumber'
    ELSE IF @SortByDateRL = 0
      SET @LOrderBySQL = @LOrderBySQL+'OrderWGRL, OrderWRL, OrderRL, OriginalDate, Date'
    ELSE
      SET @LOrderBySQL = @LOrderBySQL+'OriginalDate, Date, OrderWGRL, OrderWRL, OrderRL'
--/*
  --DELETE records with no show on bill line
  DELETE #TransactionLayout WHERE Link IN (SELECT Link FROM #TransactionLayout TL1 WHERE NOT EXISTS (SELECT * FROM #TransactionLayout TL2 WHERE TL1.Link = TL2.Link AND LogicSortNumber=99))
  SET @LSelectSQL = 'UPDATE #TransactionLayout SET Link = TS.RowNumber FROM (SELECT ROW_NUMBER() OVER('+@LOrderBySQL+') AS RowNumber, Link, LinkX FROM #TransactionLayout WHERE LogicSortNumber=99) AS TS WHERE TS.Link = #TransactionLayout.Link AND TS.LinkX = #TransactionLayout.LinkX'
  EXEC (@LSelectSQL)  
--  PRINT @LSelectSQL
--*/
  SELECT * FROM #TransactionLayout ORDER BY Link, LogicSortNumber DESC
  DROP TABLE #TransactionLayout

