CREATE PROCEDURE [dbo].[GetTransactions] @FolioNumber int, @BillTypeNumber SmallInt, @SelectTypeNumber SmallInt, @BillNumber int, @GetDataset Bit, @Saldo Money OUTPUT AS
SET NOCOUNT ON
--nb20160906 ext Exchange Use now reservation currency also when manual rooms/halls present
--nb20160628 fix Tax performance fixed (hold lige tungen i munden)
--nb20160608 opt Tax performance improved
--nb20151210 bug Group field renamed to GroupIndex (dbx.updateSQL vs SQL2012 compatibility issue)
--nb20151126 bug @PriceExclVAT and @VAT var type changed from float to decimal(19,4) as mssql server truncates large numbers (103214.28 is truncated)
--nb20150716 new When Course Production, Day product on price summary for entire stay changed from Pcs > 1 to 1 
--nb20130604 bug @PriceExclVAT type changed from MONEY to FLOAT. needs to be float, money is not accurate enough 
--nb20121220 opt Slow bills on Norlandia Mercur: optimized performance with more then 60 times gain
--nb20120206 bug Bill Layout group value limited to only look in the actual billtype setup (not all billtypes)
--nb20111223 bug Bill Layout grouping waregroups split the group by productnumber (NULL, 0  or >0) fixed not to split (Fredensborg)
--nb20111103 new @BillLayoutOrderByDate as top important option
--nb20111102 On summerized overnight wares (Charge, charge product, null product and post days methods)insure EU spec and overnight vat split are correct 
--nb20111028 hsSplitVatEnviromentFeeInOvernight added as a option
--nb20110418 Split Vat enviroment fee in overnight split
--nb20110411 Split Bill Null product handling fixed
--nb20110328 Split Vat have to show rooms ware transactions from system and manual waregroups 
--nb20110224 Split Vat have to show manual rooms turnover
--nb20101020 VAT Percent reset for product: Rounding needed to make the comparison work and avoid decimal diffs
--nb20100601 bug Billtypes, group setup was not filtered properly ending using settings from another billtype then chosen one
--nb20091221 ext Product transactions specification
--nb20091001 bug CR10043 Show Menuwares on invoices (ProduktWarelinkNumber = -1)
--nb20090923 bug CR10070 Wrong ExclVat value - (dependancy on EInvoice )
--KJ20090803 bug CR9407 GuestNumber sometimes -1 -> "Can not corvert the value 0-1 to datatype smallint" error
--nb20090724 bug CR8812 Currency payment need to be specific 
--KJ 20090320 Ups - the change of @GuestNumber to SMALLINT results in a "can not convert the value 'sometext' to datatype SMALLINT" error.
--nb20090315 GuestNumber can be 4 sign long, changed to SMALLINT
--nb20090310 need to define all sql params as local to ensure sql is fast executed
--nb20090204 Currency payment - show value in pcs fixed
--nb20090116 use last currency rate from reservation on all transactions (KA email 20090107)
--nb20090115 prepare hotelnumber for join with rooms (shared roomnumbers in domino) in select
--KJ 20090107 EXT/BUG: Sometimos Note contains other values than INT
--nb20081219 Revided CurrencyExchange
--KJ 20081205 Fix for "Use create date from reservation to get Exchange rate"
--nb20081009 #CR004888
--mli20080918 TextOverwrite is used to fill BillText with the new text
--nb20080805 Take care of overlapping bill series
--nb20080407 Safety NULL value as DefaultCurrencyCode is not accepted
--nb20080225 Safety NULL value as CurrencyCode is not accepted
-- TL 20080115 BUG Fix for Sometimes HotelCurrency is NULL - Use first currency in Countries instead
-- KJ 20080110 Bug Fix for Sometimes HotelCurrency is NULL - use old CurrencyCode
--nb20080110 Price sortering i lokaler fixed
-- TL 20080102 BUG Fix for Cannot insert NULL into #Transactions.WareGroupSortNumberOnBill
-- TL 20071210 BUG Fix for problems with Exchange when hotel uses different default currency than the hotel country currency
--nb20071113 Summerize helper field for Price type preventing to make updates on specified items 
-- TL 20071009 Return correct CurrencyCode and Exchange Rate
-- TL 20071008 Currency shouldn't be used when there are no accomodation transactions
-- TL 20070830 Fix for currency not used when there are no accomodation transactions
-- KJ 20070724 Fix for "Cursor problem"
--tl20070713 BUG Currency exchange wasn't performed on payments and certain transactions
--nb20070625 FiscalPrinter added department field in the transaction_sort temp table
--nb20070621 Fiscal Printer added department field
--nb20070320 CurrencyExchange Waregroups.WaretYpe -> Waregroups.Payment
--nb20070314 Currencyexchange Ignore Folios without board transactions 
--nb20070313 CurrencyExchange on payments now moved out in Cashier (Rate calculated using averege value)
--nb20070301 CurrencyExchange  Round for Billtypes impl.
--nb20070226 CurrencyExchange based on ReservationCurrency compared with local currency if diff. product transactions by trans date, manual transactions by bill printout date
--nb20070125 All NULL values in sorting sql must be set to a default value to insure the sorting sql builded code does work
--nb20070117 Transfer as ware now treated almost as Transfer as payment on bills
--nb20070105 Transfer payment fixed to be a payment as it should be, use Transfer ware if customer wants it to be shown as ware
--nb20061219 Changed the behavior of SelectTypeNumber matching cashier module needed
--nb20061109 Union whare clauses changed to select only the proper records (product, ware and payments)
--nb20061006 PriceExclVat inner select removed. Did not work on products. GetTransactionVat extended to get ExclVatAmount also
--nb20060918 PriceExclVat Inner select added to the main select
--nb20060822 Do not overwrite the grouped transaction text unless overwrite specified
--nb20060821 Bug More empty fields causing "No Bill text" fixed
--nb20060811 Ext Minimer antal af selects
--nb20060614 Fix for Transfer as Ware and Vat round problems.Now calculated separate
--nb20060607 Domino with separate bookkeeping, hotel spec balance safety check
-- KJ 20060421 Fix for Table overførsel vises ikke på reprint af regninger
-- nb20060209 Total in bill type shows double pcs up cause this dataset return 2 records for each transaction
--nb20060102 GuestNumber Added for Billtypes
--nb20050923 The previous bugfix rolled back cause package Vat wrong calculation (missing)
-- nb20050601 Bug redesigned cause error on Sømandshjem bill  9371
-- nb20050523 New Group by on waregroups (billtypes)
-- nb20050511 Bug Currency = 0 on payments allowed (problem on transfers fix)
-- ks20050415 Ext imp ShowTipsAsWare
-- nb/kj20050229 Bug: price (not Logi) split on TotalPrice fixed to single price
-- ks20050224 Ext select distinct from table
-- ks20050124 Ext imp ShowExpensesAsWare
-- nb20041124 Ext Price (not Logi) split if different added
-- ks20041025  Bug  Added SortTransByOriginalDate
-- ks20040908  Bug  Not select guestnumber from folios when find move information
-- ks20040719 Ext  Imp. ShowDepositAsWare
-- ks20040716 Ext  added DISTINCT to selects
-- ks20040624 BUG (ks20040506) Changed text to text (CharIndex('to r', @TextOverwrite)=0)) AND @Payment=5) (ref + room)
-- ks20040617 BUG (ks20040506) Changed debit to text (CharIndex('to room', @TextOverwrite)=0)) AND @Payment=5)
-- ks20040610 BUG null into .....
-- ks20040511 BUG   Transfer : added to ware on dest. folio. (debit=1 (mayby check on text insted))
-- ks20040506 Transfer : added to ware on dest. folio. (debit=0 (mayby check on text insted))
-- ks20040504 Ware Description field in the returned result (missing in #Transactions_Sort)
-- nb20040427 Ware Description field in the returned result
-- 04/02-2004 : Imp. show transfer as payment
-- 15/11-2002 : find folionumber if only billnumber assigned + select trans by billnumber

--SelectTypeNumber 0=Statement, 1=checkout, 2=checkout(ledger)
--@ShowTransactionBasedOnTypeNumber 1= OnBill, 2 = OnFolio
--@ShowAll 0= AllNoPrintedBeofre 1 = AllShowOnBill 2 = AllInvoicePaymentShowOnBill

DECLARE @ShowPrint BIT
SET @ShowPrint = 0
DECLARE @CursorCreate BIT
SET @CursorCreate = 0
DECLARE @CursorOpen BIT
SET @CursorOpen = 0
--need to be defined as local variables to ensure sql is fast executed
DECLARE @LFolioNumber INT, @LBillNumber INT, @LFkCustomerHotel INT
SET @LFolioNumber = @FolioNumber
SET @LBillNumber = @BillNumber
SELECT @LFkCustomerHotel = HotelCustomerNumber FROM Folios WITH (NOLOCK) WHERE FolioNumber = @LFolioNumber
if @GetDataset = 1
begin
  Declare @DefaultCurrencyCode char(3)
  Declare @SortTransByOriginalDateOnBill Bit
  Declare @HotelCurrencyNumber int

  SELECT @HotelCurrencyNumber = CurrencyNumber FROM Customer WITH (NOLOCK) WHERE CustomerNumber = @LFkCustomerHotel
  IF ISNULL(@HotelCurrencyNumber, 0) = 0
    SELECT @HotelCurrencyNumber = ISNULL(dbo.GetHotelSetupKeyValue ('HotelCurrency', @LFkCustomerHotel), 0) 
  SELECT @DefaultCurrencyCode = CurrencyCode from Countries WITH (nolock) where Countrynumber = @HotelCurrencyNumber
  IF ISNULL(@DefaultCurrencyCode, '') = ''
    SELECT TOP 1 @DefaultCurrencyCode = CurrencyCode FROM Countries WITH (NOLOCK) WHERE NOT CurrencyCode IS NULL ORDER BY SortNumber    

  SELECT @SortTransByOriginalDateOnBill = ISNULL(dbo.GetHotelSetupKeyValue ('SortTransByOriginalDateOnBill', @LFkCustomerHotel), 0)
  CREATE TABLE #Transaction(
    TransactionNumber int not null,
    DateStamp Datetime null,
    Pieces Integer not null,
    Price Money null,
    TotalPrice Money not null,
    VatPercent Money not null,
    Vat money not null,
    PriceExclVAT money not null,
    BillText Varchar(250),
    Description VARCHAR(250) null,
    Payment Smallint not null,
    Note Varchar(50) null,
    WareGroupSortNumberOnBill int not null,
    WareOrderNumber int null,
    OriginalDate DateTime not null,
    CurrencyCode Char(3) not null,
    ExchangeRate money not null,
    OrderNumber int not null,
    WareNumber int not null,
    GuestNumber int null,
    Department char(1) not null,
    Summerize bit null,
    ProductNumber int null,
    ProductWareLinkNumber int null)
end
Declare @NumberOfElements real
Declare @DivResult real
Declare @DivResultEven bit-- lige
Declare @AddToTab Bit
Declare @MovedStatus SmallInt
Declare @PricSign SmallInt
Declare @Repeat bit
Declare @SinglePrice Money
Declare @NumberOfRepeats Int
Declare @I int
Declare @RelativeNumberOfFetchs int
Declare @OldTrans bit
Declare @NumberOfOldTrans int
Declare @PriceSignTranWithOutVAT Smallint
Declare @MovedFolioNumber int
Declare @ExtMovedInfo Bit
Declare @ExtOriginalDateInfo Bit
Declare @AddressFirstName varchar (50)
Declare @AddressLastName varchar (100)
Declare @ReferenceNumber char (12)
Declare @MovedText varchar (150)
Declare @StrLength int
Declare @SortTypeNumber int
Declare @SortOrderNumber int
Declare @SortPeriodNumber int
Declare @SortText VarChar(50)
Declare @OrderNumber int
Declare @SortTextOverwrite bit
Declare @SortSQL VarChar(700)
Declare @DoSort bit
Declare @Trans1TableCreated bit SET @Trans1TableCreated = 0
Declare @ShowDepositAsWare Bit
Declare @ShowExpensesAsWare Bit
Declare @ShowTipsAsWare Bit
DECLARE @GroupIndex INT
DECLARE @WareGroupSortNumberOnBill INT
, @TotalPrice MONEY
, @DoInsert TINYINT
--nb20081219: Revided CurrencyExchange
, @ResCurrencyRate FLOAT
, @ProductNumber INT
, @ProductWareLinkNumber INT
, @InternalCourseProduction BIT

SELECT @ShowDepositAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowDepositAsWare', @LFkCustomerHotel), 0),
  @ShowExpensesAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowExpensesAsWare', @LFkCustomerHotel), 1),
  @ShowTipsAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowTipsAsWare', @LFkCustomerHotel), 0),
  @ExtMovedInfo = ISNULL(dbo.GetHotelSetupKeyValue ('ShowMovedFromOnBill', @LFkCustomerHotel), 0),
  @ExtOriginalDateInfo = ISNULL(dbo.GetHotelSetupKeyValue ('ShowOriginalDateOnBill', @LFkCustomerHotel), 0),
  @InternalCourseProduction = ISNULL(dbo.GetHotelSetupKeyValue ('InternalCourseProduction', @LFkCustomerHotel), 0)
IF ISNULL(@LBillNumber,0)=0 SET @LBillNumber = 0
SET @Saldo = 0
IF @LBillNumber>0 OR @SelectTypeNumber=0 OR @SelectTypeNumber=1
BEGIN
  IF ISNULL(@LBillNumber, 0)=0 SET @LBillNumber = 0
  IF @LBillNumber > 0 AND @LFolioNumber <= 0 
    SELECT DISTINCT @LFolioNumber=Folionumber FROM Transactions WITH (NOLOCK) WHERE InvoiceBillNumber=@LBillNumber
  ELSE
    IF @SelectTypeNumber=1 SET @LBillNumber= -1
  --nb20090115 prepare hotelnumber for join with rooms (shared roomnumbers in domino) in select
  DECLARE @HotelNumber INT
  SELECT @HotelNumber = C.HotelNumber FROM dbo.Customer C WITH (NOLOCK) WHERE C.CustomerNumber = @LFkCustomerHotel
  SET @HotelNumber = ISNULL(@HotelNumber, -1)
  SELECT @NumberOfOldTrans =  COUNT(*) FROM Transactions WITH (NOLOCK) 
  WHERE (MovedDeleted=0) 
  AND (@LBillNumber=-1 OR (@LBillNumber = 0 AND ISNULL(InvoiceBillNumber, 0)=0) OR InvoiceBillNumber=@LBillNumber)
  AND (FolioNumber=@LFolioNumber) AND (ProductWareLinkNumber IS NULL) AND Warenumber IN (
    SELECT WareNumber FROM Ware WITH (NOLOCK) WHERE waregroupnumber IN (
      SELECT WareGroupNumber FROM WareGroups WITH (NOLOCK) WHERE payment IN (2,3)
  ))
  IF @NumberOfOldTrans >0
  BEGIN
    SET @OldTrans = 1

    SET @CursorCreate = 1

    DECLARE #TransCur CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT DISTINCT ShowOnBillFlag, TransactionNumber, GlobalTransactionNumber, DateStamp, FolioNumber, Pieces, PriceOverwrite, TextOverwrite, 
      CAST(Ware.Note AS VARCHAR(250)), Debit, MovedFolioNr, Transactions.WareNumber, Transactions.Payment, VATValue, MovedDeleted, Transactions.OriginalDate,
      ISNULL(Transactions.Note, ''), WareGroups.Payment, WareGroups.SortNumber, /*Transactions.ExchangeRateOverwrite, Countries.CurrencyCode, */Ware.PrintZeroPrice, 
     ISNULL(Transactions.GuestNumber, 0),  Departments.DepartmentNumber
    FROM Transactions WITH (NOLOCK) 
	  INNER JOIN Departments WITH (nolock) on Transactions.FK_DepartmentNumber = Departments.PK_Department
      INNER JOIN Ware WITH (NOLOCK) ON Transactions.WareNumber = Ware.WareNumber 
      INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
    WHERE (@LBillNumber=-1 OR (@LBillNumber = 0 AND ISNULL(InvoiceBillNumber, 0)=0) OR InvoiceBillNumber=@LBillNumber) 
      AND FolioNumber=@LFolioNumber AND MovedDeleted=0 AND ShowOnBillFlag=1
    ORDER BY Transactions.ShowOnBillFlag desc, Transactions.GlobalTransactionNumber, Transactions.TransactionNumber
  END
  ELSE
  BEGIN
    SET @OldTrans = 0
    SET @CursorCreate = 1

    DECLARE #TransCur CURSOR FAST_FORWARD READ_ONLY FOR
    --Product
    SELECT ShowOnBillFlag, TransactionNumber, GlobalTransactionNumber, TR.DateStamp, TR.FolioNumber, TR.Pieces, TR.PriceOverwrite
    , TextOverwrite, OriginalDate, ISNULL(TR.Note, ''), TR.UserIdNumber, TR.Debit, TR.MovedFolioNr, TR.WareNumber, TR.VATValue
    , MovedDeleted, RealDateTimeForTransaction, TR.Payment, ISNULL(TR.GuestNumber, 0)
    , D.DepartmentNumber, CAST(W.Note AS VARCHAR(250)), W.PrintZeroPrice, WG.Payment, WG.SortNumber
    , RT.RoomtypeNumber, RT.Type, RT.SortNumber
	/*
    , ISNULL((SELECT SUM(T1.PriceOverwrite * (CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END))
      FROM dbo.Transactions T1 WITH (NOLOCK)
        INNER JOIN dbo.Accounts A WITH (NOLOCK) ON A.AccountNumber = T1.AccountNumber AND A.Balance = 2 AND A.VAT > 1
      WHERE T1.FolioNumber = @LFolioNumber AND T1.ProductWareLinkNumber= TR.ProductWareLinkNumber
     ),0) AS Vat
    , ISNULL((SELECT SUM(T1.PriceOverwrite * (CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END))
      FROM dbo.Transactions T1 WITH (NOLOCK)
        INNER JOIN dbo.Accounts A WITH (NOLOCK) ON A.AccountNumber = T1.AccountNumber AND A.Balance = 1
      WHERE T1.FolioNumber = @LFolioNumber AND T1.ProductWareLinkNumber= TR.ProductWareLinkNumber
     ),0) AS PriceExclVat
    */
	, ISNULL(PTPV.PriceTax, 0) AS Vat, ISNULL(PXPTV.PriceXTax, 0) AS PriceExclVat
    , TR.RealDateTimeForTransaction, TR.ProductNumber, TR.ProductWareLinkNumber
    FROM dbo.Transactions TR WITH (NOLOCK)
      LEFT JOIN dbo.TPriceXTaxProductView PXPTV WITH (NOLOCK) ON PXPTV.ProductWareLinkNumber = TR.ProductWareLinkNumber and PXPTV.FolioNumber = TR.FolioNumber
      LEFT JOIN dbo.TPriceTaxProductView PTPV WITH (NOLOCK) ON PTPV.ProductWareLinkNumber = TR.ProductWareLinkNumber and PTPV.FolioNumber = TR.FolioNumber
      INNER JOIN dbo.Departments D WITH (NOLOCK) ON TR.FK_DepartmentNumber = D.PK_Department
      INNER JOIN dbo.Ware W WITH (NOLOCK)  ON TR.WareNumber = W.WareNumber 
      INNER JOIN dbo.WareGroups WG WITH (NOLOCK) ON W.WareGroupNumber = WG.WareGroupNumber
      --nb20090115 prepare hotelnumber for join with rooms (shared roomnumbers in domino) in select
      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
    WHERE (@LBillNumber=-1 OR (@LBillNumber = 0 AND ISNULL(InvoiceBillNumber, 0)=0) OR InvoiceBillNumber=@LBillNumber) 
      AND TR.FolioNumber=@LFolioNumber AND MovedDeleted=0 AND TR.ShowOnBillFlag = 1 AND ISNULL(TR.ProductWareLinkNumber, 0)>0
    UNION
    --ware
    SELECT ShowOnBillFlag, TransactionNumber, TR.GlobalTransactionNumber, TR.DateStamp, TR.FolioNumber, TR.Pieces, TR.PriceOverwrite
    , TextOverwrite, OriginalDate, ISNULL(TR.Note, ''), TR.UserIdNumber, TR.Debit, TR.MovedFolioNr, TR.WareNumber, TR.VATValue
    , MovedDeleted, RealDateTimeForTransaction, CASE WHEN W.Type=4 THEN 5 ELSE TR.Payment END AS Payment, ISNULL(TR.GuestNumber, 0)
    , D.DepartmentNumber, CAST(W.Note AS VARCHAR(250)), W.PrintZeroPrice, WG.Payment, WG.SortNumber
    , RT.RoomtypeNumber, RT.Type, RT.SortNumber
	/*
    , ISNULL((SELECT SUM(T1.PriceOverwrite * (CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END))
      FROM dbo.Transactions T1 WITH (NOLOCK)
        INNER JOIN dbo.Accounts A WITH (NOLOCK) ON A.AccountNumber = T1.AccountNumber AND A.Balance = 2 AND A.VAT > 1
      WHERE T1.FolioNumber = @LFolioNumber AND T1.GlobalTransactionNumber = TR.GlobalTransactionNumber
     ),0) AS Vat
    , ISNULL((SELECT SUM(T1.PriceOverwrite * (CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END))
      FROM dbo.Transactions T1 WITH (NOLOCK)
        INNER JOIN dbo.Accounts A WITH (NOLOCK) ON A.AccountNumber = T1.AccountNumber AND A.Balance = 1
      WHERE T1.FolioNumber = @LFolioNumber AND T1.GlobalTransactionNumber = TR.GlobalTransactionNumber
     ),0) AS PriceExclVat
    */
	, ISNULL(PTV.PriceTax, 0) AS Vat, ISNULL(PXTV.PriceXTax, 0) AS PriceExclVat
    , TR.RealDateTimeForTransaction, NULL AS ProductNumber, NULL AS ProductWareLinkNumber
    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
      INNER JOIN dbo.Departments D WITH (NOLOCK) ON TR.FK_DepartmentNumber = D.PK_Department
      INNER JOIN dbo.Ware W WITH (NOLOCK)  ON TR.WareNumber = W.WareNumber 
      INNER JOIN dbo.WareGroups WG WITH (NOLOCK) ON W.WareGroupNumber = WG.WareGroupNumber
      --nb20090115 prepare hotelnumber for join with rooms (shared roomnumbers in domino) in select
      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
    WHERE (@LBillNumber=-1 OR (@LBillNumber = 0 AND ISNULL(InvoiceBillNumber, 0)=0) OR InvoiceBillNumber=@LBillNumber) 
      AND TR.FolioNumber=@LFolioNumber AND MovedDeleted=0 AND TR.ShowOnBillFlag = 1 AND (TR.Payment = 0 AND ISNULL(TR.ProductWareLinkNumber, 0)<=0)
    --payment (not transfer)
    UNION
    SELECT ShowOnBillFlag, TransactionNumber, GlobalTransactionNumber, TR.DateStamp, FolioNumber, TR.Pieces, TR.PriceOverwrite
    , TextOverwrite, OriginalDate, ISNULL(TR.Note, ''), TR.UserIdNumber, TR.Debit, TR.MovedFolioNr, TR.WareNumber, TR.VATValue
    , MovedDeleted, RealDateTimeForTransaction, CASE WHEN W.Type=4 THEN 5 ELSE TR.Payment END AS Payment, ISNULL(TR.GuestNumber, 0)
    , D.DepartmentNumber, CAST(W.Note AS VARCHAR(250)), W.PrintZeroPrice, WG.Payment, WG.SortNumber
    , RT.RoomtypeNumber, RT.Type, RT.SortNumber
    , 0 AS Vat
    , 0 AS PriceExclVat
    , TR.RealDateTimeForTransaction, NULL AS ProductNumber, NULL as ProductWareLinkNumber
    FROM dbo.Transactions TR WITH (NOLOCK)
      INNER JOIN dbo.Departments D WITH (NOLOCK) ON TR.FK_DepartmentNumber = D.PK_Department
      INNER JOIN dbo.Ware W WITH (NOLOCK)  ON TR.WareNumber = W.WareNumber 
      INNER JOIN dbo.WareGroups WG WITH (NOLOCK) ON W.WareGroupNumber = WG.WareGroupNumber
      --nb20090115 prepare hotelnumber for join with rooms (shared roomnumbers in domino) in select
      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
    WHERE (@LBillNumber=-1 OR (@LBillNumber = 0 AND ISNULL(InvoiceBillNumber, 0)=0) OR InvoiceBillNumber=@LBillNumber) 
      AND TR.FolioNumber=@LFolioNumber AND MovedDeleted=0 AND TR.ShowOnBillFlag = 1 AND TR.Payment <> 0
--    ORDER BY Transactions.ProductWareLinkNumber DESC, Transactions.GlobalTransactionNumber DESC
  END
END


IF @GetDataset = 1
BEGIN
  DECLARE @ShowOnBill BIT
  , @TransactionNumber INT
  , @GlobalTransactionNumber INT
  , @DateStamp DATETIME
  , @TransFolioNumber INT
  , @Pieces INT
  , @PriceOverwrite MONEY
  , @TextOverwrite VARCHAR(250)
  , @Description VARCHAR(250)
  , @Debit BIT
  , @MovedFolioNr INT
  , @WareNumber INT
  , @Payment SMALLINT
  , @VatPercent FLOAT
  , @MovedDeleted BIT
  , @OriginalDate DATETIME
  , @Note VARCHAR(50)
  , @UserIdNumber INT
  , @WaregroupPayment TINYINT
  , @WareGroupSortNumber INT
  , @AccountType INT
  , @ExchangeRate MONEY
  , @CurrencyCode CHAR(3)
  , @RoomTypeNumber INT
  , @RoomType VARCHAR(50)
  , @GuestNumber SMALLINT
  , @RoomTypeSortNumber INT
  , @PrintZeroPrice BIT
  , @Vat decimal(19,4) --nb20151126 FLOAT
  , @PriceExclVAT decimal(19,4) --nb20151126 sql2012 do not keep decimals in floats of large numbers (103214.28 is truncated) FLOAT --MONEY --nb20130604 needs to be float, money is not accurate enough 
  , @TransCurFetchStatus SMALLINT
  , @MaxWareGroupSortNumber INT
  , @CurrencyNumber INT
  , @BillDateStamp DATETIME
  , @RealDateStamp DATETIME
  , @Department CHAR(1)
  , @BillLayoutOrderByDate BIT
  OPEN #TransCur

  SET @CursorOpen = 1
  IF @OldTrans=1
  BEGIN
    FETCH NEXT FROM #TransCur
    INTO @ShowOnBill, @TransactionNumber, @GlobalTransactionNumber, @DateStamp, @TransFolioNumber, @Pieces, @PriceOverwrite, @TextOverwrite, @Description, @Debit, @MovedFolioNr, 
      @WareNumber, @Payment, @VatPercent, @MovedDeleted, @OriginalDate, @Note,@WaregroupPayment, @WareGroupSortNumber, /*@ExchangeRate, @CurrencyCode, */@PrintZeroPrice, @GuestNumber, @department
  END
  ELSE
  BEGIN
    FETCH NEXT FROM #TransCur
    INTO @ShowOnBill, @TransactionNumber, @GlobalTransactionNumber, @DateStamp, @TransFolioNumber, @Pieces, @PriceOverwrite
    , @TextOverwrite, @OriginalDate, @Note, @UserIdNumber, @Debit, @MovedFolioNr, @WareNumber, @VatPercent 
    , @MovedDeleted, @RealDateStamp, @Payment, @GuestNumber , @Department, @Description, @PrintZeroPrice, @WaregroupPayment
    , @WareGroupSortNumber, @RoomTypeNumber, @RoomType, @RoomTypeSortNumber, @Vat, @PriceExclVat
      , @RealDateStamp, @ProductNumber, @ProductWareLinkNumber

  END
  SELECT @MaxWareGroupSortNumber = MAX(WareGroupNumber) FROM Waregroups WITH (NOLOCK)
  --Exchange 
  IF (SELECT COUNT(*) FROM Transactions WITH (NOLOCK) 
      INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
      INNER JOIN Waregroups WITH (NOLOCK) ON Waregroups.WaregroupNumber = Ware.WaregroupNumber AND Waregroups.Payment IN (2,3,7,8) -- Rooms, Halls, Manuel Rooms and Manuel Halls
    WHERE FolioNumber = @LFolioNumber AND MovedDeleted = 0) > 0
  BEGIN
    SELECT @ResCurrencyRate= LastCurrencyRate, @CurrencyNumber= Reservations.Currency, @CurrencyCode = Countries.CurrencyCode
    FROM Folios WITH (NOLOCK) 
      INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = Folios.ReferenceNumber AND Reservations.GuestNumber = Folios.GuestNumber
      INNER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = Reservations.Currency
    WHERE FolioNumber = @LFolioNumber AND Folios.ReferenceNumber > 0
  END
  ELSE
  BEGIN
    SET @CurrencyNumber = @HotelCurrencyNumber
    SET @CurrencyCode = @DefaultCurrencyCode
    SET @ResCurrencyRate = NULL
  END
  IF ISNULL(@ResCurrencyRate, 0) > 0 AND @HotelCurrencyNumber <> @CurrencyNumber
  BEGIN
    SET @ResCurrencyRate = 100/@ResCurrencyRate
    SET @BillDateStamp = NULL
    IF ISNULL(@LBillNumber, 0) > 0 
      SELECT TOP 1 @BillDateStamp = DateTimeStamp FROM Invoices WITH (NOLOCK) WHERE BillNumber = @LBillNumber
    IF ISNULL(@BillDateStamp, 0) = 0
      SELECT TOP 1 @BillDateStamp = CONVERT(DATETIME, SystemDate +' '+Convert(Char, CURRENT_TIMESTAMP, 108)) FROM System WITH (NOLOCK)
  END
  ELSE
    SET @ResCurrencyRate = NULL

  WHILE @@FETCH_STATUS = 0 -- safe
  BEGIN
    SET @TransCurFetchStatus = @@FETCH_STATUS
    SET @DoInsert = 0
    --nb20070125 All possible NULL values in sorting sql must be set to a default value to insure the sorting sql builded code does work
    IF ISNULL(@PrintZeroPrice,0)=0 SET @PrintZeroPrice = 0
    IF ISNULL(@Pieces, 0) = 0 SET @Pieces = 1 --nb20070125 not a typing error, has always been set to 1 if NULL or 0
    IF ISNULL(@PriceOverwrite,0)=0 SET @PriceOverwrite = 0
    IF ISNULL(@VatPercent,0)=0 SET @VatPercent = 0
    IF ISNULL(@Vat,0)=0 SET @Vat=0
    IF ISNULL(@ExchangeRate, 1)=1 SET @ExchangeRate = 1
    IF ISNULL(@CurrencyCode,@DefaultCurrencyCode)=@DefaultCurrencyCode SET @CurrencyCode = @DefaultCurrencyCode
    IF ISNULL(@GuestNumber, 0) = 0 SET @GuestNumber=0
    IF ISNULL(@Note,'')='' SET @Note='' 

    IF @TransFolioNumber=@LFolioNumber AND @MovedDeleted=0
      SET @MovedStatus = 0
    ELSE IF @TransFolioNumber=@LFolioNumber AND @MovedDeleted=1
      SET @MovedStatus = 1
    ELSE IF @MovedFolioNr=@LFolioNumber AND @MovedDeleted=1
      SET @MovedStatus = 2

    IF @MovedStatus<>0
    BEGIN
      IF @Payment = 0
      BEGIN
        IF @MovedStatus = 1
          SET @PricSign = -1
        ELSE
          SET @PricSign = 1
      END
      ELSE
      BEGIN
        IF @MovedStatus = 1
          SET @PricSign = 1
        ELSE
          SET @PricSign = -1
      END
      IF @Pieces<0
        SET @PricSign = -1* @PricSign
    END
    ELSE
    BEGIN
      IF @Payment=0 AND @WaregroupPayment<>5
      BEGIN
        IF @Debit=1
          SET @PricSign = 1
        ELSE
          SET @PricSign = -1
      END
      ELSE
      BEGIN
        IF @Debit=1
          SET @PricSign = -1
        ELSE
          SET @PricSign = 1
        IF @Payment=5 OR @WaregroupPayment=5   -- Transfer/rabat
          SET @PricSign = -1*@PricSign
      END
    END
    IF @MovedFolioNr>0  AND @MovedDeleted=0
    BEGIN
      IF @ExtOriginalDateInfo=1
        SET @TextOverwrite = @TextOverwrite + ' - ' + RTrim(Convert(char,@OriginalDate,104)) 
      IF @ExtMovedInfo=1
      BEGIN
        SELECT DISTINCT @AddressFirstName = ISNULL(RESERVATIONADDRESS.FIRSTNAME, ''), @AddressLastName = ISNULL(RESERVATIONADDRESS.LASTNAME, ''), @ReferenceNumber = ISNULL(FOLIOS.ReferenceNumber, 0)
        FROM   FOLIOS WITH (Nolock) LEFT OUTER JOIN RESERVATIONADDRESS WITH (nolock) ON FOLIOS.ReservationAddressNumber = RESERVATIONADDRESS.AddressNumber AND FOLIOS.ReservationAddressNumber = RESERVATIONADDRESS.AddressNumber
        WHERE (FOLIOS.FolioNumber = @MovedFolioNr)
        SET @MovedText=''
        IF @ReferenceNumber>0
        BEGIN
          SELECT @StrLength = LEN(@GuestNumber)
          IF @GuestNumber >= 0
          BEGIN
            IF @StrLength=1
              SET @GuestNumber = '00' + CAST(@GuestNumber AS VARCHAR)
            ELSE IF @StrLength=2
              SET @GuestNumber = '0' + CAST(@GuestNumber AS VARCHAR)
          END
          IF @AddressFirstName<>''
            SET @MovedText= @AddressFirstName + ' ' + @AddressLastName
          ELSE 
            SET @MovedText= @AddressLastName
          IF @MovedText=''
            SET @MovedText = RTrim(@ReferenceNumber) + '/' + CAST(@GuestNumber AS VARCHAR)  
          ELSE
            SET @MovedText = @MovedText + '-' + RTrim(@ReferenceNumber) + '/' + CAST(@GuestNumber AS VARCHAR)  
        END
        ELSE IF @AddressLastName IS NOT NULL
        BEGIN
          IF @AddressFirstName<>''
            SET @MovedText= @AddressFirstName + ' ' + @AddressLastName
          ELSE 
            SET @MovedText= @AddressLastName
          SET @MovedText = @MovedText + 'F' + RTrim(Convert(char, @MovedFolioNr)) 
        END
        ELSE  
          SET @MovedText = 'Folio ' + RTrim(Convert(char, @MovedFolioNr))
        SET @TextOverwrite = @TextOverwrite + ' - ' + @MovedText
      END
    END
    IF @OriginalDate IS NULL
      SET @OriginalDate = @DateStamp
    IF @TextOverwrite='' OR @TextOverwrite IS NULL 
      SET @TextOverwrite = 'No bill text'  -- JP NOTE: this don't make sense when thinking about multi language bills!
    SET @PriceOverwrite = CONVERT(MONEY,@PriceOverwrite * @PricSign)
    SET @SinglePrice = ABS(CONVERT(MONEY, @PriceOverwrite/@Pieces))* @PricSign
    SET @TotalPrice = @PriceOverwrite
    SET @Saldo = @Saldo + @TotalPrice
    IF @OldTrans=1
    BEGIN
      IF @VatPercent>CONVERT(MONEY,0)
        SET @Vat =  @PriceOverwrite*(1-(1/CONVERT(FLOAT,((@VatPercent/100)+1))))
      ELSE
        SET @Vat = CONVERT(MONEY,0)
      SET @PriceExclVAT = @PriceOverwrite - @Vat
    END
    ELSE
    BEGIN
      /*
      SET @PriceSignTranWithOutVAT = @PricSign
      IF @Payment<>0
        SET @Vat  = 0
      IF @VatPercent*@PriceExclVat <> @Vat*100
        SET @VatPercent = 0
      */--nb20090923 Wrong ExclVat value - (dependancy on EInvoice )
      SET @PriceSignTranWithOutVAT = @PricSign
      IF @Payment<>0 and @WaregroupPayment > 0 
      BEGIN 
        SET @Vat = 0
        SET @PriceExclVat = @PriceOverwrite 
      END
      --IF @VatPercent*@PriceExclVat <> @Vat*100
      --nb20101020 VAT Percent reset for product: Rounding needed to make the comparison work and avoid decimal diffs
      IF ROUND(@VatPercent*@PriceExclVat, 1) <> ROUND(@Vat*100, 1)
       
        SET @VatPercent = 0    

    END

    IF ((@PriceExclVAT<>0 OR @Vat<>0 OR @TotalPrice<>0) AND @PrintZeroPrice=0) OR @PrintZeroPrice=1
    BEGIN
      IF @GetDataset=1
      BEGIN
        IF @CurrencyNumber IS NOT NULL AND (@CurrencyNumber <> @HotelCurrencyNumber)
        BEGIN
          DECLARE @TempCurrencyRate FLOAT
          /* 
          --On accomodation transactions use the last saved currency rate
          IF ISNULL(@ResCurrencyRate, 0) > 0 and @UserIdNumber IN (SELECT UserIdNumber FROM Personel (NOLOCK) WHERE UserTypeNumber IN (3,4))
            SET @TempCurrencyRate = @ResCurrencyRate
          ELSE IF @Payment = 0 
            SELECT @TempCurrencyRate= DBO.Exchange( @UserIDNumber, @RealDateStamp,  @BillDateStamp, @CurrencyNumber)
          ELSE
            SET @TempCurrencyRate = 1  
          */--nb20090116 use last currency rate from reservation on all transactions (KA email 20090107)
          --On ressservations with locked currency rate use the last saved currency rate
          IF ISNULL(@ResCurrencyRate, 0) > 0
            SET @TempCurrencyRate = @ResCurrencyRate
          ELSE
            SET @TempCurrencyRate = 1  

IF @ShowPrint = 1
BEGIN
  PRINT 'ExchangeCalc'
  PRINT '@UserIdNumber: ' + CONVERT(VARCHAR, @UserIdNumber)
  PRINT '@DateStamp: ' + CONVERT(VARCHAR, @DateStamp)
  PRINT '@billdatestamp:' + CONVERT(VARCHAR, @billdatestamp)
  PRINT '@CurrencyNumber: ' + CONVERT(VARCHAR, @CurrencyNumber)
  PRINT '@PriceOverwrite: ' + CONVERT(VARCHAR, @PriceOverwrite)
  PRINT '@ResCurrencyRate: ' + CONVERT(VARCHAR, @ResCurrencyRate)
  PRINT '@@TempCurrencyRate: ' + CONVERT(VARCHAR, @TempCurrencyRate)
END
          IF ISNULL(@TempCurrencyRate, 0) <> 0 
          BEGIN
            SET @ExchangeRate = @TempCurrencyRate
            SET @PriceOverwrite = ROUND(@PriceOverwrite * @TempCurrencyRate, 3)
            SET @SinglePrice = ROUND(@SinglePrice * @TempCurrencyRate, 3)
            SET @TotalPrice = ROUND(@TotalPrice * @TempCurrencyRate, 3)
            SET @Vat = ROUND(@Vat * @TempCurrencyRate, 3)
            SET @PriceExclVAT = ROUND(@PriceExclVAT * @TempCurrencyRate, 3)
          END
        END
        --nb20090204 currency payment
        --ELSE IF @Payment = 6
        --nb20090724 bug CR8812 Currency payment need to be specific 
        IF @Payment = 6
        BEGIN
          SELECT @ExchangeRate = T.ExchangeRateOverwrite, @CurrencyNumber = T.CurrencyNumber, @CurrencyCode = C.CurrencyCode FROM Transactions T WITH (NOLOCK) 
            INNER JOIN Countries C WITH (NOLOCK) ON T.CurrencyNumber = C.CountryNumber
          WHERE T.TransactionNumber = @TransactionNumber
        END
          
        --nb20070221 END CurrencyExchange

        SET @WareGroupSortNumberOnBill = 
        CASE WHEN @WaregroupPayment in (0,1) -- ware and payment
          THEN   @WareGroupSortNumber + 4 -- offset
            WHEN   @WaregroupPayment in (2,7) -- rooms
          THEN   1
            WHEN  @WaregroupPayment in (3,8) -- Halls
          THEN   2
            WHEN   @WaregroupPayment = 4 -- Fees
          THEN   @MaxWareGroupSortNumber + 1
            WHEN   @WaregroupPayment = 5 -- Provision
          THEN  3
        END
        SET @WareGroupSortNumberOnBill = IsNull(@WareGroupSortNumberOnBill, 0) -- TL 20080102 Fix for Cannot insert NULL into #Transactions.WareGroupSortNumberOnBill

        IF (@ShowTipsAsWare = 1 AND @Payment = 9 /* Tips/Cash+*/) OR (@ShowDepositAsWare = 1 AND @Payment=12) OR (@ShowExpensesAsWare = 1 AND @Payment = 13) 
          SET @Payment = 0
        --nb20110224 Split Vat have to show manual rooms turnover
        IF @WaregroupPayment in (2,7) and ISNULL(@ProductNumber, 0) <= 0 --Rooms wares can be posted using system and manual room wares      
          SET @ProductNumber = -1
          
        IF @BillTypeNumber=0
        BEGIN
          SET @SortOrderNumber = 1  
          SET @DoInsert = 1
        END
        --BillType sorted bills
        ELSE
        BEGIN
          SET @DoSort= 0
          /*
            --nb20111102 On summerized overnight wares (Charge, charge product, null product and post days methods)insure EU spec and overnight vat split are correct
            IF ISNULL(@ProductNumber, 0) > 0 
              SET @SortSQL = ' WHERE ProductNumber > 0 '
            ELSE IF @ProductNumber IS NULL
              SET @SortSQL = ' WHERE ProductNumber IS NULL '
            ELSE
              SET @SortSQL = ' WHERE ProductNumber='+Rtrim(Convert(char,@ProductNumber))+' '
          */--nb20111223 bug: Bill Layout grouping waregroups split the group by productnumber (NULL, 0  or >0) fixed not to split (Fredensborg)
          SELECT @GroupIndex=ISNULL([GroupIndex], 0)
          FROM WareGroupBillSortTypesXTab (NOLOCK) INNER JOIN
            Ware (NOLOCK) ON WareGroupBillSortTypesXTab.WaregroupNumber = Ware.WareGroupNumber
          WHERE (WareGroupBillSortTypesXTab.BillTypeNumber = @BillTypeNumber) 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
            IF ISNULL(@ProductNumber, 0) > 0 
              SET @SortSQL = ' WHERE ProductNumber > 0 '
            ELSE IF @ProductNumber IS NULL
              SET @SortSQL = ' WHERE ProductNumber IS NULL '
            ELSE
              SET @SortSQL = ' WHERE ProductNumber='+Rtrim(Convert(char,@ProductNumber))+' '
          ELSE
            SET @SortSQL = ' WHERE 1=1'

          --nb20111103 @BillLayoutOrderByDate as top important option
          SELECT @BillLayoutOrderByDate = OrderByDate FROM Billtypes WITH (NOLOCK) WHERE BillTypeNumber = @BillTypeNumber 
          IF @BillLayoutOrderByDate = 1
            IF @SortTransByOriginalDateOnBill = 0
              SET @SortSQL = @SortSQL + ' AND Convert(char,DateStamp,112)='''+Convert(varchar,@DateStamp,112)+''' ' 
            ELSE
              SET @SortSQL = @SortSQL + ' AND Convert(char,OriginalDate,112)='''+Convert(varchar,@OriginalDate,112)+''' ' 
              
          --nb20050523
          SELECT @SortTypeNumber=SortTypeNumber, @SortOrderNumber=FKBillSortOrder, @SortPeriodNumber=FKBillSortPeriod, @SortText=Text, @OrderNumber=OrderNumber, @SortTextOverwrite=TextOverwrite, @GroupIndex=ISNULL([GroupIndex], 0)
          FROM WareGroupBillSortTypesXTab WITH (NOLOCK) INNER JOIN
            Ware WITH (NOLOCK) ON WareGroupBillSortTypesXTab.WaregroupNumber = Ware.WareGroupNumber
          WHERE (WareGroupBillSortTypesXTab.BillTypeNumber = @BillTypeNumber) AND (Ware.WareNumber = @WareNumber)
          IF @@RowCount > 0
          BEGIN
IF @ShowPrint = 1
BEGIN
  PRINT 'DEBUG Bill Layouts start ----------------------------'
  PRINT '@WareNumber = ' + CONVERT(VARCHAR, @WareNumber)
  PRINT '@BillTypeNumber = ' + CONVERT(VARCHAR, @BillTypeNumber)
  PRINT '@DoSort=' + CONVERT(VARCHAR, @DoSort)
  PRINT 'WaregroupDef exists start ----'
  PRINT '@SortTypeNumber=' + CONVERT(VARCHAR, @SortTypeNumber)
  PRINT '@ProductNumber = ' + CONVERT(VARCHAR, ISNULL(@ProductNumber, 0))
  PRINT '@BillLayoutOrderByDate = ' + CONVERT(VARCHAR, @BillLayoutOrderByDate)
  --PRINT '@SortSQL=' + CONVERT(VARCHAR(500), @SortSQL)
  PRINT 'WaregroupDef exists end ----'
END
            SET @WareGroupSortNumberOnBill=@OrderNumber
              
            --nb20050523
            IF @SortTypeNumber = 1 AND @GroupIndex <= 0 --if Specified try to get the ware sort setup              begin
            BEGIN
                SELECT  @SortTypeNumber=SortTypeNumber, @SortOrderNumber=FKBillSortOrder, @SortPeriodNumber=FKBillSortPeriod, @SortText=Text, @WareGroupSortNumber=OrderNumber, @SortTextOverwrite=TextOverwrite --mli TextOverwrite is used to fill BillText with the new text
                FROM       WareBillSortTypesXTab WITH (NOLOCK) INNER JOIN
                Ware WITH (NOLOCK) ON WareBillSortTypesXTab.WareNumber = Ware.WareNumber
                WHERE     (WareBillSortTypesXTab.BillTypeNumber = @BillTypeNumber) AND (Ware.WareNumber = @WareNumber)
              IF @@RowCount = 0 --Waregroup specified sort setup used
              BEGIN
                SELECT  @SortTypeNumber=SortTypeNumber, @SortOrderNumber=FKBillSortOrder, @SortPeriodNumber=FKBillSortPeriod, @SortText=Text, @WareGroupSortNumberOnBill=OrderNumber, @SortTextOverwrite=TextOverwrite
                  FROM       WareGroupBillSortTypesXTab WITH (NOLOCK) INNER JOIN
                  Ware WITH (NOLOCK) ON WareGroupBillSortTypesXTab.WaregroupNumber = Ware.WareGroupNumber
                  WHERE     (WareGroupBillSortTypesXTab.BillTypeNumber = @BillTypeNumber) AND (Ware.WareNumber = @WareNumber)
                -- Waregroup sort type= Specified and undefined ware sorttype = Specified => Transaction Specified
                SET @DoSort= 0
              END
              ELSE
              BEGIN
                SET @SortSQL = @SortSQL +' AND WareNumber='+Rtrim(Convert(char,@WareNumber))+' '
                IF @SortTypeNumber = 1
                  SET @DoSort= 0
                ELSE IF @SortTypeNumber > 0
                  SET @DoSort= 1
                ELSE
                  SET @DoSort= 0
              END
            END
            ELSE IF @GroupIndex > 0 --nb20050523
            BEGIN
              SET @DoSort= 1
              SET @SortSQL = @SortSQL +' AND WareNumber IN (SELECT DISTINCT WareNumber FROM Ware WITH (NOLOCK) INNER JOIN'
               +' WAREGROUPS WITH (NOLOCK) ON Ware.WareGroupNumber = Waregroups.WaregroupNumber INNER JOIN'
               +' WaregroupBillSortTypesXtab WBT WITH (NOLOCK) ON WBT.WaregroupNumber = Waregroups.WaregroupNumber AND WBT.[GroupIndex] = ' + RTRIM(Convert(char, @GroupIndex)) + ' AND WBT.BillTypeNumber= '+CONVERT(VARCHAR, ISNULL(@BillTypeNumber, 0))+') '
            END
            ELSE
            BEGIN
              SET @DoSort= 1              
              SET @SortSQL = @SortSQL +' AND WareNumber In (SELECT DISTINCT WareNumber FROM Ware WITH (NOLOCK) INNER JOIN WAREGROUPS WITH (NOLOCK) ON Ware.WareGroupNumber = (SELECT WaregroupNumber FROM Ware WITH (NOLOCK) where WareNumber = ' + Rtrim(Convert(char, @WareNumber)) + ')) '
            END
            IF @GroupIndex > 0  --nb20050523 find the first waregroup in group
              SELECT TOP 1 @SortPeriodNumber=FKBillSortPeriod, @SortText=WareGroupBillSortTypesXTab.Text, @SortTextOverwrite=TextOverwrite, @OrderNumber=OrderNumber, @TextOverwrite=Waregroups.Text
              FROM WareGroupBillSortTypesXTab WITH (NOLOCK) INNER JOIN Waregroups WITH (NOLOCK) ON Waregroups.WaregroupNumber= WareGroupBillSortTypesXTab.WaregroupNumber
              WHERE WareGroupBillSortTypesXTab.BillTypeNumber = @BillTypeNumber and WareGroupBillSortTypesXTab.[GroupIndex] = @GroupIndex 
              ORDER BY WareGroupBillSortTypesXTab.OrderNumber   
            IF @SortTextOverwrite = 1 SET @TextOverwrite=@SortText
            IF @BillLayoutOrderByDate = 0 and @SortPeriodNumber = 2 
              SET @SortSQL = @SortSQL + ' AND Convert(char,DateStamp,112)='''+Convert(varchar,@DateStamp,112)+''' '
             
          END
IF @ShowPrint = 1
BEGIN
  PRINT '@SortTypeNumber=' + CONVERT(VARCHAR, @SortTypeNumber)
  PRINT '@SortPeriodNumber=' + CONVERT(VARCHAR, @SortPeriodNumber)
  PRINT '@SortOrderNumber = ' + CONVERT(VARCHAR, @SortOrderNumber)
  PRINT '@WareGroupSortNumberOnBill=' + CONVERT(VARCHAR, @WareGroupSortNumberOnBill)
  PRINT '@SortSQL=' + CONVERT(VARCHAR(400), @SortSQL)
  --PRINT '@SortTextOverwrite=' + CONVERT(VARCHAR, @SortTextOverwrite)
  --PRINT '@DoSort = ' + CONVERT(VARCHAR, @DoSort)
  --PRINT '@DoSort = ' + CONVERT(VARCHAR, @DoSort)
  --PRINT '@DoSort = ' + CONVERT(VARCHAR, @DoSort)
  --PRINT '@DoSort = ' + CONVERT(VARCHAR, @DoSort)
  PRINT 'DEBUG Bill Layouts end ----------------------------'
END  
          IF @GroupIndex > 0 
            SET @WareGroupSortNumberOnBill= @OrderNumber
          ELSE IF ISNULL(@SortOrderNumber,0) > 0 
          BEGIN
            IF (@SortOrderNumber=1) 
              SET @SortOrderNumber= Convert(int, @GuestNumber)
            ELSE IF (@SortOrderNumber=2) AND @Note<>''
              SET @SortOrderNumber=Convert(int, @Note)
            ELSE IF (@SortOrderNumber=3)
              SET @SortOrderNumber=@RoomTypeSortNumber
          END
          ELSE
            SET @SortOrderNumber=0
          IF ISNULL(@SortOrderNumber,0) = 0 
            SET @SortOrderNumber=0
          IF @DoSort=0
            SET @DoInsert = 1
          ELSE IF @GroupIndex > 0
          BEGIN
            SET @SortSQL= 'update #Transaction SET Pieces=1'
             --+', Price=Price+'+Ltrim(Convert(char,@TotalPrice))
             --nb20081009 #CR004888
             +', Price= CASE WHEN Price ='+Ltrim(Convert(char,@TotalPrice))+' THEN '+Ltrim(Convert(char,@TotalPrice))+' ELSE NULL END'

             +', Note=CASE WHEN RTRIM(Note)='''+RTRIM(@Note)+''' THEN Note ELSE NULL END'
             +', TotalPrice=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))
             +', Vat=Vat+'+Ltrim(Convert(char,@Vat))
             +', PriceExclVAT=PriceExclVAT+'+Ltrim(Convert(char,@PriceExclVAT))
             +@SortSQL
            EXEC (@SortSQL)
            IF @@ROWCOUNT = 0
              SET @DoInsert=1
          END
          ELSE
          BEGIN
            IF @SortTypeNumber = 2 --joined
            BEGIN
              SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Rtrim(Convert(char,@Pieces))
               +', Price= CASE WHEN Price ='+Ltrim(Convert(char,@SinglePrice))+' THEN '+Ltrim(Convert(char,@SinglePrice))+' ELSE NULL END'                 
			   +', Note=CASE WHEN RTRIM(Note)='''+RTRIM(@Note)+''' THEN Note ELSE NULL END'
               +', TotalPrice=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))                 +', Vat=Vat+'+Ltrim(Convert(char,@Vat))
               +', PriceExclVAT=PriceExclVAT+'+Ltrim(Convert(char,@PriceExclVAT))+@SortSQL--+' AND Price='+Ltrim(Convert(char,@SinglePrice))
              EXEC (@SortSQL)
              IF @@ROWCOUNT = 0
                SET @DoInsert=1
            END
            ELSE IF @SortTypeNumber = 3 --person / guest
            BEGIN
              IF (@Payment>0)
                SET @SortSQL= 'update #Transaction SET Pieces=1'--Pieces+'+Rtrim(Convert(char,@Pieces))
                  +', Price= CASE WHEN Price ='+Ltrim(Convert(char,@TotalPrice))+' THEN '+Ltrim(Convert(char,@TotalPrice))+' ELSE NULL END' --Ltrim(Convert(char,@SinglePrice))+' THEN '+Ltrim(Convert(char,@SinglePrice))+' ELSE NULL END'
                  +', TotalPrice=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))
                  +', Vat=Vat+'+Ltrim(Convert(char,@Vat))
                  +', PriceExclVAT=PriceExclVAT+'+Ltrim(Convert(char,@PriceExclVAT))+@SortSQL+
                  ' AND GuestNumber=''' + Rtrim(Convert(char, @GuestNumber))+'''' 
              ELSE
                SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Rtrim(Convert(char,@Pieces))
                  +', Price= CASE WHEN Price ='+Ltrim(Convert(char,@SinglePrice))+' THEN '+Ltrim(Convert(char,@SinglePrice))+' ELSE NULL END'
                  +', TotalPrice=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))
                  +', Vat=Vat+'+Ltrim(Convert(char,@Vat))
                  +',PriceExclVAT=PriceExclVAT+'+Ltrim(Convert(char,@PriceExclVAT))+@SortSQL+
                  ' AND GuestNumber=''' + Rtrim(Convert(char, @GuestNumber))+'''' 
                EXEC (@SortSQL)
              IF @@ROWCOUNT = 0
                SET @DoInsert=2
            END
            ELSE IF @SortTypeNumber = 4 --roomnumber
            BEGIN
              SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Rtrim(Convert(char,@Pieces))
                +', Price= CASE WHEN Price ='+Ltrim(Convert(char,@SinglePrice))+' THEN '+Ltrim(Convert(char,@SinglePrice))+' ELSE NULL END'
                +', TotalPrice=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))
                +', Vat=Vat+'+Ltrim(Convert(char,@Vat))
                +',PriceExclVAT=PriceExclVAT+'+Ltrim(Convert(char,@PriceExclVAT))+@SortSQL+' AND Note='''+RTrim(@Note)+'''' --+ Convert(char, @Note)
              --print @SortSQL
              EXEC (@SortSQL)
              IF @@ROWCOUNT = 0
                SET @DoInsert=1
            END
            ELSE IF @SortTypeNumber = 5 --roomtype
            BEGIN
              SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Rtrim(Convert(char,@Pieces))
                +', Price= CASE WHEN Price ='+Ltrim(Convert(char,@SinglePrice))+' THEN '+Ltrim(Convert(char,@SinglePrice))+' ELSE NULL END'
                +', TotalPrice=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))
                +', Vat=Vat+'+Ltrim(Convert(char,@Vat))
                +',PriceExclVAT=PriceExclVAT+'+Ltrim(Convert(char,@PriceExclVAT))+@SortSQL+
                ' AND #Transaction.note IN (SELECT Rooms.Number FROM RoomTypes WITH (NOLOCK) INNER JOIN ROOMS WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ROOMS.RoomTypeNumber WHERE RoomTypes.RoomTypeNumber=''' + Rtrim(Convert(char, @RoomTypeNumber)) + ''')'
              EXEC (@SortSQL)
              IF @@ROWCOUNT = 0
                SET @DoInsert=1
            END
            ELSE IF @SortTypeNumber = 6 --price
            BEGIN
              IF (@Trans1TableCreated = 0)
              BEGIN
                CREATE TABLE #Transaction_Sort(
                  DateStamp Datetime null,
                  Pieces Integer not null,
                  Price Money not null,
                  TotalPrice Money not null,
                  BillText Varchar(255) null,
                  Description VARCHAR(250) null,
                  Payment Smallint not null,
                  VatPercent Money not null,
                  Vat money not null,
                  PriceExclVAT money not null,
                  Note Varchar(50) null,
                  WareGroupSortNumberOnBill int not null,
                  WareOrderNumber int null,
                  OriginalDate DateTime null,
                  TransactionNumber int not null,
                  CurrencyCode Char(3) not null,
                  ExchangeRate money not null,
                  OrderNumber int not null,
                  WareNumber int not null,
                  GuestNumber int null,
                  SortPeriodNumber tinyint null,
                  Department char(1) not null,
                  ProductNumber int null,
                  ProductWareLinkNumber int null)
                SET @Trans1TableCreated=1
              END
		      IF @InternalCourseProduction = 0 AND NOT @WaregroupPayment = 3 OR 
			    @InternalCourseProduction = 1 AND (NOT @WaregroupPayment in (3, 8) OR @SortPeriodNumber = 1)
              BEGIN
  		        IF @InternalCourseProduction = 0 AND @WaregroupPayment = 2 OR 
			      @InternalCourseProduction = 1 AND @WaregroupPayment in (2, 3, 8)
                  SET @SortSQL= 'update #Transaction_Sort SET Pieces=1'
                    +', Price=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))
                    +', TotalPrice=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))+
                    ', Note=CASE WHEN RTRIM(Note)='''+RTRIM(@Note)+''' THEN Note ELSE NULL END'+
                    ', Vat=Vat+'+Ltrim(Convert(char,@Vat))+
                    ', PriceExclVAT=PriceExclVAT+'+Ltrim(Convert(char,@PriceExclVAT))+
                    @SortSQL+' AND GuestNumber=''' + Rtrim(Convert(char, @GuestNumber))+''''
                ELSE
                  SET @SortSQL= 'update #Transaction_Sort SET Pieces=Pieces+'+Rtrim(Convert(char,@Pieces))
                    +', TotalPrice=TotalPrice+'+Ltrim(Convert(char,@TotalPrice))+
                    ', Note=CASE WHEN RTRIM(Note)='''+RTRIM(@Note)+''' THEN Note ELSE NULL END'+
                    ', Vat=Vat+'+Ltrim(Convert(char,@Vat))+
                    ', PriceExclVAT=PriceExclVAT+'+Ltrim(Convert(char,@PriceExclVAT))+
                    @SortSQL+' AND GuestNumber=''' + Rtrim(Convert(char, @GuestNumber))+''' AND ABS(Price)='+Ltrim(Convert(char,ABS(@SinglePrice)))
              END
              ELSE
                SET @SortSQL=''
              EXEC (@SortSQL)
              IF @@ROWCOUNT = 0 or @SortSQL=''
                SET @DoInsert=3
            END
          END
        END
--PRINT '@SortSQL=' + CONVERT(VARCHAR(400), @SortSQL)
        --BillType sorted bills end
        IF @DoInsert=1 
          INSERT INTO #Transaction (TransactionNumber, DateStamp, Pieces, Price, TotalPrice, BillText, Department, Description, Payment, VatPercent, Vat, Note, WareGroupSortNumberOnBill, WareOrderNumber, PriceExclVAT,OriginalDate, ExchangeRate, CurrencyCode, OrderNumber, WareNumber, ProductNumber, ProductWareLinkNumber) VALUES
            (@TransactionNumber, @DateStamp, @Pieces, @SinglePrice, @TotalPrice, @TextOverwrite, @Department, @Description, @Payment, @VatPercent, @Vat, RTrim(@Note), @WareGroupSortNumberOnBill, @WareGroupSortNumber, @PriceExclVAT, @OriginalDate, @ExchangeRate, @CurrencyCode, @SortOrderNumber, @WareNumber, @ProductNumber, @ProductWareLinkNumber)
        ELSE IF @DoInsert=2
          INSERT INTO #Transaction (TransactionNumber, DateStamp, Pieces, Price, TotalPrice, BillText, Department, Description, Payment, VatPercent, Vat, Note, WareGroupSortNumberOnBill, WareOrderNumber, PriceExclVAT,OriginalDate, ExchangeRate, CurrencyCode, OrderNumber, WareNumber, GuestNumber, ProductNumber, ProductWareLinkNumber) VALUES
            (@TransactionNumber, @DateStamp, @Pieces, @SinglePrice, @TotalPrice, @TextOverwrite, @Department, @Description, @Payment, @VatPercent, @Vat, RTrim(@Note), @WareGroupSortNumberOnBill, @WareGroupSortNumber, @PriceExclVAT, @OriginalDate, @ExchangeRate, @CurrencyCode, @SortOrderNumber, @WareNumber, @GuestNumber, @ProductNumber, @ProductWareLinkNumber)
        ELSE IF @DoInsert=3
          INSERT INTO #Transaction_Sort (TransactionNumber, DateStamp, Pieces, Price, TotalPrice, BillText, Department, Description, Payment, VatPercent, Vat, Note, WareGroupSortNumberOnBill, WareOrderNumber, PriceExclVAT,OriginalDate, ExchangeRate, CurrencyCode, OrderNumber, WareNumber, GuestNumber, SortPeriodNumber, ProductNumber, ProductWareLinkNumber) VALUES
            (@TransactionNumber, @DateStamp, @Pieces, @SinglePrice, @TotalPrice, @TextOverwrite, @Department, @Description, @Payment, @VatPercent, @Vat, RTrim(@Note), @WareGroupSortNumberOnBill, @WareGroupSortNumber, @PriceExclVAT, @OriginalDate, @ExchangeRate, @CurrencyCode, @SortOrderNumber, @WareNumber, @GuestNumber, @SortPeriodNumber, @ProductNumber, @ProductWareLinkNumber)
      END
    END
    IF @TransCurFetchStatus = 0
    BEGIN 
/*print '@OldTrans='+ convert(varchar,@OldTrans)*/
      IF @OldTrans=1
      BEGIN
        FETCH NEXT FROM #TransCur
        INTO @ShowOnBill, @TransactionNumber, @GlobalTransactionNumber, @DateStamp, @TransFolioNumber, @Pieces, @PriceOverwrite, @TextOverwrite, @Description, @Debit, @MovedFolioNr, 
          @WareNumber, @Payment, @VatPercent, @MovedDeleted, @OriginalDate, @Note,@WaregroupPayment, @WareGroupSortNumber, /*@ExchangeRate, @CurrencyCode, */@PrintZeroPrice, @GuestNumber, @department
      END
      ELSE
      BEGIN
        FETCH NEXT FROM #TransCur
        INTO @ShowOnBill, @TransactionNumber, @GlobalTransactionNumber, @DateStamp, @TransFolioNumber, @Pieces, @PriceOverwrite
        , @TextOverwrite, @OriginalDate, @Note, @UserIdNumber, @Debit, @MovedFolioNr, @WareNumber, @VatPercent 
        , @MovedDeleted, @RealDateStamp, @Payment, @GuestNumber , @Department, @Description, @PrintZeroPrice, @WaregroupPayment
        , @WareGroupSortNumber, @RoomTypeNumber, @RoomType, @RoomTypeSortNumber, @Vat, @PriceExclVat, @RealDateStamp, @ProductNumber, @ProductWareLinkNumber
      END
    END
  END
END

IF @CursorCreate = 1
BEGIN
  IF @CursorOpen = 1
    CLOSE #TransCur
  DEALLOCATE  #TransCur
END

IF @Trans1TableCreated=1 --summerrize additionaly the person sum on price
BEGIN
  DECLARE #TransCur Cursor FAST_FORWARD Read_Only For
    SELECT DateStamp, Pieces, Price, TotalPrice, BillText, Department, Description, Payment, VatPercent, Vat, Note, WareGroupSortNumberOnBill, WareOrderNumber, PriceExclVAT, OriginalDate, TransactionNumber, CurrencyCode, ExchangeRate, OrderNumber, WareNumber, GuestNumber, SortPeriodNumber, ProductNumber, ProductWareLinkNumber FROM #Transaction_Sort WITH (nolock)
  OPEN #TransCur
  FETCH NEXT FROM #TransCur
    INTO @DateStamp, @Pieces, @PriceOverwrite, @TotalPrice, @TextOverwrite, @Department, @Description, @Payment, @VatPercent, @Vat, @Note, @WareGroupSortNumberOnBill, @WareGroupSortNumber, @PriceExclVAT, @OriginalDate, @TransactionNumber, @CurrencyCode, @ExchangeRate, @OrderNumber, @WareNumber,@GuestNumber, @SortPeriodNumber, @ProductNumber, @ProductWareLinkNumber
  WHILE (@@FETCH_STATUS = 0) -- safe
  BEGIN
IF @ShowPrint = 1
BEGIN
  print '------------------------------------------------------------------'
  print '@SortPeriodNumber= '+Convert(varchar, @SortPeriodNumber)
  print '@WareNumber= '+Convert(varchar, @WareNumber)
  print '@WareGroupSortNumberOnBill= '+Convert(varchar, @WareGroupSortNumberOnBill)
  print '@WareGroupSortNumber= '+Convert(varchar, @WareGroupSortNumber)
  print '@TextOverwrite= '+Convert(varchar, @TextOverwrite)
  print '@PriceOverwrite= '+Convert(varchar, @PriceOverwrite)
  print '@DateStamp= '+Convert(varchar,@DateStamp,112)
END
    IF @BillLayoutOrderByDate = 1 or @SortPeriodNumber = 2 
      UPDATE #Transaction SET Pieces= Pieces + @Pieces, TotalPrice= TotalPrice+@TotalPrice, Vat=Vat+@Vat, PriceExclVAT=PriceExclVAT+@PriceExclVAT, Note= CASE WHEN RTRIM(Note)<>RTRIM(@Note) THEN ' ' END
      WHERE Summerize=1 AND WareGroupSortNumberOnBill=@WareGroupSortNumberOnBill AND WareOrderNumber=@WareGroupSortNumber AND Price=@PriceOverwrite AND Convert(Char(8),DateStamp,112)=convert(char(8),@DateStamp,112)
    ELSE
      UPDATE #Transaction SET Pieces= Pieces + @Pieces, TotalPrice= TotalPrice+@TotalPrice, Vat=Vat+@Vat, PriceExclVAT=PriceExclVAT+@PriceExclVAT, Note= CASE WHEN RTRIM(Note)<>RTRIM(@Note) THEN ' ' END
      WHERE Summerize=1 AND WareGroupSortNumberOnBill=@WareGroupSortNumberOnBill AND WareOrderNumber=@WareGroupSortNumber AND Price=@PriceOverwrite
    IF @@RowCount = 0
      INSERT INTO #Transaction (DateStamp, Pieces, Price, TotalPrice, BillText, Department, Description, Payment, VatPercent, Vat, Note, WareGroupSortNumberOnBill, WareOrderNumber, PriceExclVAT, OriginalDate, TransactionNumber, CurrencyCode, ExchangeRate, OrderNumber, WareNumber, GuestNumber, Summerize, ProductNumber, ProductWareLinkNumber) VALUES
        (@DateStamp, @Pieces, @PriceOverwrite, @TotalPrice, @TextOverwrite, @Department, @Description, @Payment, @VatPercent, @Vat, @Note, @WareGroupSortNumberOnBill, @WareGroupSortNumber, @PriceExclVAT, @OriginalDate, @TransactionNumber, @CurrencyCode, @ExchangeRate, @OrderNumber, @WareNumber, @GuestNumber, 1, @ProductNumber, @ProductWareLinkNumber) 
    Fetch Next From #TransCur
    Into @DateStamp, @Pieces, @PriceOverwrite, @TotalPrice, @TextOverwrite, @Department, @Description, @Payment, @VatPercent, @Vat, @Note, @WareGroupSortNumberOnBill, @WareGroupSortNumber, @PriceExclVAT, @OriginalDate, @TransactionNumber, @CurrencyCode, @ExchangeRate, @OrderNumber, @WareNumber,@GuestNumber, @SortPeriodNumber, @ProductNumber, @ProductWareLinkNumber
  END
  CLOSE #TransCur
  DEALLOCATE #TransCur
END

IF @GetDataset = 1
BEGIN
/*
  IF @SortTransByOriginalDateOnBill = 0
  BEGIN
    IF @BillTypeNumber=0
      SELECT DISTINCT * FROM #Transaction WITH (nolock) ORDER BY WareGroupSortNumberOnBill, DateStamp, OriginalDate
    ELSE
      SELECT DISTINCT * FROM #Transaction WITH (nolock) ORDER BY WareGroupSortNumberOnBill, WareOrderNumber, OrderNumber, DateStamp, OriginalDate
  END
  ELSE
  BEGIN
    IF @BillTypeNumber=0
      SELECT DISTINCT * FROM #Transaction WITH (nolock) ORDER BY WareGroupSortNumberOnBill,OriginalDate, DateStamp
    ELSE
      SELECT DISTINCT * FROM #Transaction WITH (nolock) ORDER BY WareGroupSortNumberOnBill, WareOrderNumber, OrderNumber, OriginalDate, DateStamp
  END
*/--nb20111103 new @BillLayoutOrderByDate as top important option
  IF @BillTypeNumber=0
    IF @SortTransByOriginalDateOnBill = 0
      SELECT DISTINCT * FROM #Transaction WITH (nolock) ORDER BY WareGroupSortNumberOnBill, DateStamp, OriginalDate
    ELSE
      SELECT DISTINCT * FROM #Transaction WITH (nolock) ORDER BY WareGroupSortNumberOnBill, OriginalDate, DateStamp
  ELSE IF @SortTransByOriginalDateOnBill = 0
    IF @BillLayoutOrderByDate = 0
      SELECT DISTINCT * FROM #Transaction WITH (nolock) ORDER BY WareGroupSortNumberOnBill, WareOrderNumber, OrderNumber, DateStamp, OriginalDate
    ELSE
      SELECT * FROM #Transaction WITH (nolock) ORDER BY CONVERT(VARCHAR,DateStamp,112), CONVERT(VARCHAR,OriginalDate,112), WareGroupSortNumberOnBill, WareOrderNumber, OrderNumber
  ELSE IF @BillLayoutOrderByDate = 0
    SELECT DISTINCT * FROM #Transaction WITH (nolock) ORDER BY WareGroupSortNumberOnBill, WareOrderNumber, OrderNumber, OriginalDate, DateStamp
  ELSE
    SELECT * FROM #Transaction WITH (nolock) ORDER BY CONVERT(VARCHAR,OriginalDate,112), CONVERT(VARCHAR,DateStamp,112), WareGroupSortNumberOnBill, WareOrderNumber, OrderNumber

  DROP TABLE #Transaction
END