CREATE VIEW [dbo].[TTransactionProductContentView]
AS
--nb20131107 1.Cast all Prices to Money  2. Added Price and Total versions of XVat and Vat Amounts 
--nb20120827 Created
SELECT 
  CONVERT(DateTime, CONVERT(VARCHAR, TR.DateStamp, 112)) AS Date
, WG.SortNumber AS Grp 
, W.SortNumber AS No
, (CASE WG.Payment WHEN 2 THEN W.WareName + ' - overnight' WHEN 3 THEN W.WareName + ' - dayproduct' ELSE TR.TextOverwrite END) AS Text
, SUM(TR.Pieces) AS Pcs

--, CAST(SUM(TR.PriceOverwrite*(CASE TR.Debit WHEN 0 THEN -1 ELSE 1 END)/ABS(TR.Pieces)) AS MONEY) AS Price
--, CAST(SUM(TR.PriceOverwrite*(CASE TR.Debit WHEN 0 THEN -1 ELSE 1 END)) AS MONEY) AS Total
--, SUM(T2.PriceOverwrite * (CASE T2.Debit WHEN 1 THEN -1 ELSE 1 END)) AS PriceXVAT
--, SUM(T1.PriceOverwrite * (CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END)) AS PriceVat
--nb20131107 Cast to Money all fields and added Price and total versions of XVat and Vat Amounts 
, CAST(SUM(TR.PriceOverwrite*(CASE TR.Debit WHEN 0 THEN -1 ELSE 1 END/ABS(TR.Pieces))) AS MONEY) AS Price
, CAST(SUM(T2.PriceOverwrite*(CASE T2.Debit WHEN 1 THEN -1 ELSE 1 END/ABS(TR.Pieces))) AS MONEY) AS PriceXVAT
, CAST(SUM(T1.PriceOverwrite*(CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END/ABS(TR.Pieces))) AS MONEY) AS PriceVat
, CAST(SUM(TR.PriceOverwrite*(CASE TR.Debit WHEN 0 THEN -1 ELSE 1 END)) AS MONEY) AS Total
, CAST(SUM(T2.PriceOverwrite*(CASE T2.Debit WHEN 1 THEN -1 ELSE 1 END)) AS MONEY) AS TotalXVAT
, CAST(SUM(T1.PriceOverwrite*(CASE T1.Debit WHEN 1 THEN -1 ELSE 1 END)) AS MONEY) AS TotalVat

  , TR.VatValue, TR.ProductNumber, TR.PackageNumber
  , WG.Payment
  , TR.ProductWareLinkNumber AS PWLN
  , (CASE WHEN (WG.Payment IN (7,8) AND (SELECT COUNT(*) FROM Transactions TR1 WITH (NOLOCK)
    where TR1.GlobalTransactionNumber = TR.GlobalTransactionNumber and TR1.ShowOnBillFlag=1) = 0
    ) THEN 1 ELSE WG.Payment END) AS LogicSortNumber
  , TR.FolioNumber
  , I.BillNumber
FROM dbo.Transactions TR WITH (NOLOCK)
  INNER JOIN dbo.Accounts A WITH (NOLOCK) ON A.AccountNumber = TR.AccountNumber and A.AccountType in (5,6)
  INNER JOIN dbo.Ware W WITH (NOLOCK) ON W.WareNumber = TR.WareNumber
  INNER JOIN dbo.WareGroups WG WITH (NOLOCK) ON WG.WaregroupNumber = W.WareGroupNumber
  LEFT JOIN dbo.Transactions T1 WITH (NOLOCK) ON TR.GlobalTransactionNumber = T1.GlobalTransactionNumber
    AND T1.folionumber = TR.FolioNumber
  INNER JOIN dbo.Accounts A1 WITH (NOLOCK) ON T1.AccountNumber = A1.AccountNumber AND NOT A1.AccountType in (5,6) and A1.Balance = 2
  LEFT JOIN dbo.Transactions T2 WITH (NOLOCK) ON TR.GlobalTransactionNumber = T2.GlobalTransactionNumber
    AND T2.folionumber = TR.FolioNumber
  INNER JOIN dbo.Accounts A2 WITH (NOLOCK) ON T2.AccountNumber = A2.AccountNumber AND NOT A2.AccountType in (5,6) and A2.Balance = 1
  LEFT JOIN dbo.InvoiceTransactionXtab ITX WITH (NOLOCK) ON ITX.FkTransaction = TR.TransactionNumber
  LEFT JOIN dbo.Invoice I WITH (NOLOCK) ON I.PkInvoice = ITX.FkInvoice AND I.CancelDate IS NULL
WHERE ISNULL(TR.ProductWareLinkNumber, 0) > 0 AND TR.moveddeleted = 0
GROUP BY CONVERT(DateTime, CONVERT(VARCHAR, TR.DateStamp, 112)), TR.VatValue, TR.PackageNumber
, (CASE WG.Payment WHEN 2 THEN W.WareName + ' - overnight' WHEN 3 THEN W.WareName + ' - dayproduct' ELSE TR.TextOverwrite END)
, WG.Payment, TR.ProductNumber, TR.WareNumber, TR.ProductWareLinkNumber, TR.GlobalTransactionNumber
, WG.SortNumber
, W.SortNumber
, TR.FolioNumber, I.BillNumber
GO


