IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('CalcSaleTotal')))
THEN Drop procedure CalcSaleTotal
END IF

GO

create procedure DBA.CalcSaleTotal( in @SaleID integer ) 
begin atomic
  declare Price decimal(16,4);
  declare Total decimal(16,4);
  declare VATSuma decimal(16,4);
  declare VAT double;
  set Price = 0;
  set Total = 0;
  set VATSuma = 0;
  set VAT = 0;
  for f as curs scroll cursor for
    select SOL.VatRate as @VATRate,
      Round(SOL.Qty*SOL.SellPrice*(1+SOL.Discount/100),2) as @TotalSuma
      from SaleOrders as SO join SaleOrderLines as SOL on SO.Id = SOL.DocId
      where SO.Id = @SaleID order by SOL.Id asc do
    set VAT = 1+@VATRate;
    set Price = Price+@TotalSuma/VAT;
    set Total = Total+@TotalSuma;
    set VATSuma = VATSuma+Round(@TotalSuma-(@TotalSuma/VAT),3)
  end for;
  update SaleOrders set TotalSumNoVat = Round(Price,2),VatSum = Round(VATSuma,2),TotalSum = Round(Total,2)
    where Id = @SaleID
end