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 * IIF(TS.Debit=1, 1, -1) * SPV.SignedValue) AS MONEY) AS Total
, (COUNT(*) - 1) AS SplitCount
/*
SELECT SPV.SignedValue
, FLOOR(TS.Pieces) AS dPcs
, CAST(TS.PriceOverwrite * SPV.SignedValue AS MONEY) AS dTotal
*/
--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
  LEFT JOIN TSignPaymentWGWView SPV (NOLOCK) ON SPV.TransactionNumber = T.TransactionNumber
GROUP BY TS.FolioNumber, TS.WareNumber, TS.HotelCustomerNumber, TS.OriginalGlobalTransactionNumber, T.GlobalTransactionNumber
--WHERE T.FolioNumber = 876769