CREATE VIEW [dbo].[TSplitByRelationView]
AS
--nb20240930 Reduction from reduction and moved original cant be loaded correctly by OGT. Old sql removed. Used only on folios with transactions older then the oldes TTX transaction   
--nb20240925 Reduction on reduction 
-- nb20230919: Created
SELECT DISTINCT
 T.DateStamp AS Date
, T.TextOverwrite AS Text
, FLOOR(T.Pieces) As Pcs
, CAST(T.PriceOverwrite/FLOOR(T.Pieces) AS MONEY) AS Price 
, CAST(T.PriceOverwrite/FLOOR(T.Pieces)*ABS(FLOOR(T.Pieces)) AS MONEY) As Total
, P.UserIdCode AS UserID
, C.LastName AS Hotel--Do not use unless several hotels in domino
, T.FolioNumber
, T.MovedDeleted
, TM.GlobalTransactionNumber AS MGTN
, T.ProductWareLinkNumber AS PWLN
, T.TransactionNumber
, T.GlobalTransactionNumber AS GTN
, T.Payment AS PaymentType
, T.HotelCustomerNumber
, T.WareNumber --4 delete
, T.Debit
, 1 AS Part
FROM dbo.Transactions T WITH (NOLOCK) 
  JOIN dbo.TransactionTransactionXtab TTX (NOLOCK) ON TTX.FK_TransactionNumber2 = T.TransactionNumber AND TTX.RelationType IN (3,4,5) AND T.MovedDeleted = 0
  JOIN dbo.Transactions TM (NOLOCK) ON TM.TransactionNumber = TTX.FK_TransactionNumber1 AND TM.FolioNumber = T.FolioNumber AND TM.MovedDeleted = 0
  LEFT JOIN Personel P WITH (NOLOCK) ON P.UserIdNumber = T.UserIdNumber
  LEFT JOIN Customer C WITH (NOLOCK) ON C.CustomerNumber = T.HotelCustomerNumber
UNION
SELECT
 T.DateStamp AS Date
, T.TextOverwrite AS Text
, FLOOR(T.Pieces) As Pcs
, CAST(T.PriceOverwrite/FLOOR(T.Pieces) AS MONEY) AS Price 
, CAST(T.PriceOverwrite/FLOOR(T.Pieces)*ABS(FLOOR(T.Pieces)) AS MONEY) As Total
, P.UserIdCode AS UserID
, C.LastName AS Hotel--Do not use unless several hotels in domino
, T.FolioNumber
, T.MovedDeleted
, T.GlobalTransactionNumber AS MGTN
, T.ProductWareLinkNumber AS PWLN
, T.TransactionNumber
, T.GlobalTransactionNumber AS GTN
, T.Payment AS PaymentType
, T.HotelCustomerNumber
, T.WareNumber --4 delete
, T.Debit
, 2 AS Part
FROM dbo.Transactions T WITH (NOLOCK) 
  JOIN TransactionTransactionXtab TTX (NOLOCK) ON TTX.FK_TransactionNumber1 = T.TransactionNumber AND TTX.RelationType IN (3,4,5) AND T.MovedDeleted = 0
  JOIN dbo.Transactions TC (NOLOCK) ON TC.TransactionNumber = TTX.FK_TransactionNumber2 AND TC.FolioNumber = T.FolioNumber AND TC.MovedDeleted = 0 
  LEFT JOIN Personel P WITH (NOLOCK) ON P.UserIdNumber = T.UserIdNumber
  LEFT JOIN Customer C WITH (NOLOCK) ON C.CustomerNumber = T.HotelCustomerNumber
