
CREATE PROCEDURE DBO.GetTransactionVat @VatPercent MONEY OUTPUT, @Amount MONEY OUTPUT,  @PWLN INT, @GTN INT,  @XVat BIT = 0 AS
SET NOCOUNT ON
/*
NB20061006: PriceXVat calculation added
NB20060611: Created

Get the Vat and PriceXVat values of the transactions showed on the bill
Parameters- 
  @PWLN- By ProductWareLinkNumber for product transactions
  OR
  @GTN- By GlobalTransactionNumber for ordinary transactions
  @XVat
    0- Vat 
    1- PriceExclVat
Notes:
*/
--PRINT @PWLN
--PRINT @GTN
IF ISNULL(@PWLN, 0)>0 OR ISNULL(@GTN, 0)>0
BEGIN
  DECLARE #AmountCur CURSOR FAST_FORWARD READ_ONLY FOR  
  SELECT SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN -1 ELSE 1 END)) AS Price
  , Transactions.VatValue AS VatPercent
  FROM Transactions WITH (NOLOCK)
    INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber AND ((@XVat= 0 AND Accounts.Balance = 2 AND Accounts.VAT > 1) OR (@Xvat=1 AND Accounts.Balance = 1))
  WHERE (ISNULL(@PWLN, 0)>0 AND ProductWareLinkNumber= @PWLN AND MovedDeleted=0) OR (ISNULL(@GTN, 0)>0 AND GlobalTransactionNumber = @GTN AND MovedDeleted=0 )
  GROUP BY Transactions.VatValue
  OPEN #AmountCur
  DECLARE @AmountCurrent MONEY, @VatPercentCurrent MONEY
  SET @Amount = 0
  FETCH NEXT FROM #AmountCur
  INTO @AmountCurrent, @VatPercentCurrent
  SET @VatPercent = @VatPercentCurrent
  WHILE @@FETCH_STATUS = 0 -- safe
  BEGIN
    SET @Amount = @Amount + @AmountCurrent
--  PRINT CONVERT(VARCHAR, @VatPercent)
    if NOT @VatPercent IS NULL AND @VatPercent<>@VatPercentCurrent
      SET @VatPercent = NULL
    FETCH NEXT FROM #AmountCur
    INTO @AmountCurrent, @VatPercentCurrent
  END
  CLOSE #AmountCur
  DEALLOCATE #AmountCur
END

