CREATE VIEW [dbo].[TTransactionJView]
AS
-- nb20210712: Reduction of reduction back to original folio
-- nb20160205: Show at Provision the price and Pcs signed correctly
-- nb20131203: Signed Total fixed for Transfer as Ware 
-- nb20131107: PWLN Invalid here as it affects the price on splitted product transactions, moved in outer sql
-- nb20130312: Repeated reduction on same original transactions create duplicate records fixed
-- nb20130221: Hidden dest records maked visible and Price = Original price 
-- nb20130115: Created
SELECT SUM(FLOOR(TS.Pieces)) AS Pcs
/*
, CAST(SUM(TS.PriceOverwrite/TS.Pieces*ABS(TS.Pieces) * CASE 
  WHEN WG.Payment = 5 THEN -1
  WHEN TS.Payment = 0 THEN 1 ELSE -1 END) AS MONEY) AS Total
*/--fix for Transfer as ware
, CAST(SUM(TS.PriceOverwrite/TS.Pieces*ABS(TS.Pieces) * 
  CASE WHEN WG.Payment = 5 THEN -1 WHEN 
    CASE
      WHEN W.Payment = 5 AND (PATINDEX('%to ref%', T.TextOverwrite) > 0 OR PATINDEX('%to room%', T.TextOverwrite) > 0 OR PATINDEX('%to folio%', T.TextOverwrite) > 0) THEN 5
      WHEN W.Type = 4 AND (PATINDEX('%to ref%', T.TextOverwrite) > 0 OR PATINDEX('%to room%', T.TextOverwrite) > 0 OR PATINDEX('%to folio%', T.TextOverwrite) > 0) THEN 5 
      WHEN W.Payment = 5 THEN 0
      ELSE T.Payment 
	END = 0 THEN 1 ELSE -1 END
  ) AS MONEY) AS Total

, (COUNT(*) - 1) AS SplitCount
--helper fields
, TS.OriginalGlobalTransactionNumber AS MGTN
, TS.FolioNumber
, T.GlobalTransactionNumber AS GTN
FROM TRANSACTIONS TS WITH (NOLOCK) 
  LEFT JOIN Transactions T WITH (NOLOCK) ON T.GlobalTransactionNumber = TS.OriginalGlobalTransactionNumber AND 
    T.WareNumber = TS.WareNumber AND T.ShowOnBillFlag = 1 AND TS.ShowOnBillFlag=1 AND T.MovedDeleted=0 AND TS.MovedDeleted=0 AND 
    (ISNULL(T.MovedFolioNr, 0) = 0 or T.FolioNumber <> TS.FolioNumber)
  LEFT JOIN Ware W WITH (NOLOCK) ON W.WareNumber = T.WareNumber
  LEFT JOIN WareGroups WG WITH (NOLOCK) ON WG.WareGroupNumber = W.WareGroupNumber
GROUP BY TS.FolioNumber, TS.WareNumber, TS.HotelCustomerNumber, TS.OriginalGlobalTransactionNumber, T.GlobalTransactionNumber