CREATE VIEW [dbo].[TSignPaymentWGWView]
AS
--nb20210806: Created View 4 calc signed value of transfer amounts
SELECT DISTINCT T.TransactionNumber, CASE WHEN WG.Payment = 5 THEN -1 
  WHEN CASE
    WHEN (W.Payment = 5 OR W.Type = 4) AND RelationType=2 AND NOT TX.FK_TransactionNumber1 IS NULL /*NB202109 Next line part can be removed in 5 years*/ 
	  OR RelationType IS NULL AND (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 AND T.Pieces > 0 THEN 1 ELSE -1 END AS SignedValue
--, T.Payment, W.Payment AS wPayment, W.Type AS wType
FROM dbo.Transactions T (NOLOCK) 
  JOIN Ware W WITH (NOLOCK) ON T.WareNumber = W.WareNumber
  JOIN WAREGROUPS WG WITH (NOLOCK) ON W.WareGroupNumber = WG.WareGroupNumber
  LEFT JOIN TransactionTransactionXtab TX WITH (NOLOCK) ON TX.FK_TransactionNumber1 = T.TransactionNumber AND RelationType=2
GO
