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
IF @DebugLevel >= 1 
BEGIN
  PRINT '---- GetTransactionNew Pre ---'
END

DECLARE @MaxWareGroupSortNumber INT, @LGetDatasetKind SmallInt, @LPkFolio int, @LPkInvoice int, @LPkReceiptLayout SmallInt, @LLocaleId Int
SET @LGetDatasetKind = @GetDatasetKind
SET @LPkFolio = @PkFolio
SET @LPkInvoice = @PkInvoice 
SET @LPkReceiptLayout = @PkReceiptLayout 
SET @LLocaleId = @LocaleId
SELECT @MaxWareGroupSortNumber = MAX(WareGroupNumber) FROM Waregroups WITH (NOLOCK)
--IF @LPkInvoice < 0 SET @LPkInvoice = NULL

;WITH RelevantCTE (FkTransaction, GTN, FolioNumber, WareNumber, PWLN, WaregroupNumber, WareSortNumber, WareType) AS (
SELECT DISTINCT T.Transactionnumber, T.GlobalTransactionNumber, T.FolioNumber, T.WareNumber, T.ProductWareLinkNumber, W.WaregroupNumber, W.SortNumber, W.Type
FROM dbo.Transactions T WITH (NOLOCK) 
  JOIN dbo.Ware W (NOLOCK) ON W.WareNumber = T.WareNumber
  LEFT JOIN dbo.InvoiceTransactionXtab ITX WITH (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
WHERE T.FolioNumber=@LPkFolio AND (ISNULL(@LPkInvoice, 1) <= 0 OR @LPkInvoice > 0 AND ITX.FkInvoice = @LPkInvoice)
AND T.MovedDeleted = 0  AND (W.PrintZeroPrice = 1 OR T.PriceOverwrite <> 0 OR T.ShowOnBillFlag = 0)
EXCEPT --Settled when Prebill
SELECT T.Transactionnumber, T.GlobalTransactionNumber, T.FolioNumber, T.WareNumber, T.ProductWareLinkNumber, W.WaregroupNumber, W.SortNumber, W.Type
FROM dbo.Transactions T WITH (NOLOCK) 
  JOIN dbo.Ware W (NOLOCK) 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 = @LPkFolio AND @LPkInvoice = 0 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 (NOLOCK)
    JOIN dbo.Ware W (NOLOCK) 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 = @LPkFolio 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
  )
, ProductPriceXTaxCTE (PWLN, Price) AS (
  SELECT RT.PWLN, SUM(T.PriceOverwrite * (CASE T.Debit WHEN 1 THEN -1 ELSE 1 END)) 
  FROM RelevantCTE RT (NOLOCK) 
    JOIN dbo.Transactions T WITH (NOLOCK) ON T.ProductWareLinkNumber = RT.PWLN AND RT.FkTransaction = T.TransactionNumber
    JOIN dbo.Accounts A WITH (NOLOCK) ON T.AccountNumber = A.AccountNumber AND NOT A.AccountType in (5,6) and A.Balance = 1
  WHERE RT.PWLN > 0 AND T.folionumber = RT.FolioNumber AND T.MovedDeleted = 0 AND T.ShowOnBillFlag = 0    
  GROUP BY RT.PWLN
)
, ProductPriceTaxCTE (PWLN, Price) AS (
  SELECT RT.PWLN, SUM(T.PriceOverwrite * (CASE T.Debit WHEN 1 THEN -1 ELSE 1 END)) 
  FROM RelevantCTE RT (NOLOCK) 
    JOIN dbo.Transactions T WITH (NOLOCK) ON T.ProductWareLinkNumber = RT.PWLN AND RT.FkTransaction = T.TransactionNumber
    JOIN dbo.Accounts A WITH (NOLOCK) ON T.AccountNumber = A.AccountNumber AND NOT A.AccountType in (5,6) and A.Balance = 2
  WHERE RT.PWLN > 0 AND T.folionumber = RT.FolioNumber AND T.MovedDeleted = 0 AND T.ShowOnBillFlag = 0
  GROUP BY RT.PWLN
)
--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, CASE WHEN TR.Payment = 17 THEN -1 ELSE 1 END * TR.PriceOverwrite*(CASE TR.Debit WHEN 0 THEN -1 ELSE 1 END) AS Price
, TR.VatValue AS TaxPercent, CASE WHEN TR.Payment = 17 THEN -1 ELSE 1 END * TR.Pieces AS 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 @LGetDatasetKind > 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 (NOLOCK) 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 (NOLOCK)
  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  @LGetDatasetKind > 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 * IIF(TR.Debit=1, 1, -1) * VS.SignedValue) AS Price, NULL AS TaxPercent
, TR.Pieces AS Pieces, ISNULL(LTRIM(RTRIM(TR.TextOverwrite)), '')
, CASE WHEN @LGetDatasetKind = 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 (NOLOCK)
  JOIN dbo.Accounts A (NOLOCK) 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 (NOLOCK) 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 MIN(XTax.Price) AS PriceXTax
  , MIN(Tax.Price) 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 RelevantCTE RT
    JOIN dbo.Transactions TR WITH (NOLOCK) ON RT.FkTransaction = TR.TransactionNumber
    JOIN ProductPriceXTaxCTE XTax ON XTax.PWLN = RT.PWLN
    JOIN ProductPriceTaxCTE Tax ON Tax.PWLN = RT.PWLN
    JOIN dbo.Accounts A WITH (NOLOCK) ON A.AccountNumber = TR.AccountNumber and A.AccountType in (5,6)
    JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = RT.WareGroupNumber
  WHERE ISNULL(TR.ProductWareLinkNumber, 0) > 0 AND TR.ShowOnBillFlag = 1
--  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 RelevantCTE RT 
    JOIN dbo.Transactions TR WITH (NOLOCK) ON RT.FkTransaction = TR.TransactionNumber 
    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 dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = RT.WareGroupNumber
  WHERE  @LGetDatasetKind > 0 AND ISNULL(TR.ProductWareLinkNumber, 0) > 0 
    --    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
, T.VATValue AS TaxPercent
, SUM(T.Pieces) AS Pieces, ISNULL(LTRIM(RTRIM(MAX(T.TextOverwrite))), ''), MAX(T.Note), Max(T.TransactionNumber) AS TransactionNumber, T.WareNumber, T.GuestNumber
, CONVERT(Date, MIN(T.DateStamp), 112) AS Date, CONVERT(Date, MIN(T.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 RelevantCTE RT (NOLOCK)
  JOIN dbo.Transactions TM (NOLOCK) ON TM.GlobalTransactionNumber = RT.GTN AND TM.ShowOnBillFlag = 1 AND TM.moveddeleted = 0
  JOIN dbo.Transactions T (NOLOCK) ON T.TransactionNumber = RT.FkTransaction AND T.MovedDeleted = 0 AND T.ShowOnBillFlag=0  
  JOIN dbo.Accounts A WITH (NOLOCK) ON T.AccountNumber = A.AccountNumber AND NOT A.AccountType in (5,6) and A.Balance = 2
  JOIN dbo.Transactions T1 WITH (NOLOCK) ON RT.GTN = 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 dbo.Ware WM (NOLOCK) ON TM.WareNumber = WM.WareNumber AND WM.IsMenuWare = 1
  JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = RT.WareGroupNumber
WHERE  @LGetDatasetKind > 0 
GROUP BY T.GlobalTransactionNumber, T.WareNumber, T.GuestNumber, T.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(@LPkInvoice, 0) > 0 
  SELECT @FkCustomerHotel = FkCustomerHotel FROM Invoice WITH (NOLOCK) WHERE PkInvoice = @LPkInvoice
ELSE
  SELECT @FkCustomerHotel = HotelCustomerNumber FROM Folios WITH (NOLOCK) WHERE FolioNumber = @LPkFolio
--SELECT @SortByOriginalDate = ISNULL(dbo.GetHotelSetupKeyValue ('SortTransByOriginalDateOnBill', @FkCustomerHotel), 0)
--nb20240827 CR247841 Original setup does not exist in TL since forever. Perhaps it existed in db and some cleanup have ereased it, transit to reuse existing setup, questionable it will prevail 
SELECT @SortByOriginalDate = ISNULL(dbo.GetHotelSetupKeyValue ('ShowOriginalDateOnBill', @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 = @LPkReceiptLayout 
  SET @SortByDateRL = ISNULL(@SortByDateRL, 0)
  EXEC dbo.TransactionDecorateNew @LPkReceiptLayout, @LGetDatasetKind, @FkCustomerHotel, @LLocaleId, @SortByOriginalDate, @SortByDateRL
  DECLARE @LOrderBySQL VARCHAR(150), @LSelectSQL VARCHAR(600), @LDistinctSQL VARCHAR(10)
  SET @LSelectSQL = ''
  SET @LDistinctSQL = ''
  SET @LOrderBySQL = 'ORDER BY '
  IF @LPkReceiptLayout<=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 (NOLOCK) WHERE NOT EXISTS (
    SELECT * FROM #TransactionLayout TL2 (NOLOCK) 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 (NOLOCK) WHERE LogicSortNumber=99) AS TS WHERE TS.Link = #TransactionLayout.Link AND TS.LinkX = #TransactionLayout.LinkX'
  EXEC (@LSelectSQL)  
--  PRINT @LSelectSQL
--*/
  SELECT * FROM #TransactionLayout (NOLOCK) ORDER BY Link, LogicSortNumber DESC
  DROP TABLE #TransactionLayout

