CREATE VIEW [dbo].[TBalanceTax]
AS
--nb20230620: Created: Turnover (not balance) amounts xtax & tax
SELECT T.TransactionNumber AS fkTransaction, T.FolioNumber AS fkFolio, T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END AS Amount, T.VatValue AS TaxPercent, 0 AS IsTax
FROM dbo.Transactions T  WITH (NOLOCK)
  JOIN Accounts A  WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber AND A.Balance = 1
  LEFT JOIN dbo.Transactions  TT (NOLOCK) ON TT.GlobalTransactionNumber = T.GlobalTransactionNumber
  LEFT JOIN Ware W WITH (NOLOCK) ON W.WareNumber = TT.WareNumber 
WHERE T.MovedDeleted=0 
UNION
SELECT T.TransactionNumber AS fkTransaction, T.FolioNumber AS fkFolio, T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END AS Amount, T.VatValue AS TaxPercent, 0 AS IsTax
FROM dbo.Transactions T  WITH (NOLOCK)
  JOIN dbo.Transactions  TT (NOLOCK) ON TT.GlobalTransactionNumber = T.GlobalTransactionNumber
  LEFT JOIN Ware W WITH (NOLOCK) ON W.WareNumber = TT.WareNumber 
  JOIN Accounts A  WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber
	AND (A.Balance = 2 AND TT.ShowOnBillFlag=1 AND (TT.Payment = 5 OR TT.Payment = 0 AND W.Type = 4))
WHERE T.MovedDeleted=0 AND T.Payment > 0 --Payment in Transfer need to be treated as turnover to match front leg
  AND A.AccountType <> 5 -- Filter away balance account appearing on Transfer as Payment
UNION--Tax (Non Provision)
SELECT T.TransactionNumber AS fkTransaction, T.FolioNumber AS fkFolio, T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END AS Amount, T.VatValue AS TaxPercent, 1 AS IsTax
FROM Transactions T  WITH (NOLOCK)
  JOIN Accounts A  WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber AND A.Balance = 2 AND A.VAT > 1
  JOIN Ware W WITH (NOLOCK) ON W.WareNumber = T.WareNumber
  JOIN Waregroups WG WITH (NOLOCK) ON WG.WaregroupNumber = W.WaregroupNumber and (WG.Payment <> 5 or T.ShowOnBillFlag = 0) -- excl show on bill provision (incl package)
WHERE T.MovedDeleted=0 
UNION--Tax (Provision only)
SELECT T.TransactionNumber AS fkTransaction, T.FolioNumber AS fkFolio, T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END AS Amount, T.VatValue AS TaxPercent, 1 AS IsTax
FROM Transactions T  WITH (NOLOCK)
  JOIN Accounts A  WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber AND A.Balance = 2 AND A.VAT > 1
  JOIN Ware W WITH (NOLOCK) ON W.WareNumber = T.WareNumber
  JOIN Waregroups WG WITH (NOLOCK) ON WG.WaregroupNumber = W.WaregroupNumber and WG.Payment = 5 --provision
  JOIN transactions TR WITH (NOLOCK) ON TR.GlobalTransactionNumber = T.GlobalTransactionNumber AND
    T.WareNumber = TR.WareNumber AND TR.TransactionNumber <> T.TransactionNumber AND
    TR.ShowOnBillFlag = 0 AND T.ShowOnBillFlag = 0 AND T.MovedDeleted=0 AND TR.MovedDeleted=0
WHERE T.MovedDeleted=0 AND T.ShowOnBillFlag = 1 
GO