CREATE FUNCTION dbo.GetFolioBalanceByReference (@AReferenceNumber INT, @AGuestNumber INT, @AWareNumberCSV VARCHAR(50))
RETURNS FLOAT
AS
--nb20160525 Extended to accept empty @AWareNumberCSV = all wares
--kj201410 Created
BEGIN
  DECLARE @Total FLOAT

  SELECT @Total = SUM(Transactions.PriceOverwrite * (CASE WHEN (Accounts.AccountType IN (4, 9)) THEN -1 ELSE 1 END)*  (CASE WHEN (Transactions.Debit = 0) THEN -1 ELSE 1 END)) 
  FROM Transactions WITH (NOLOCK) 
  INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
  INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber
    AND Folios.ReferenceNumber = @AReferenceNumber
  WHERE ShowOnBillFlag=1 AND MovedDeleted=0
  AND (@AWareNumberCSV = '' OR Transactions.WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareNumberCSV)))
  AND Transactions.GuestNumber = @AGuestNumber

  IF @Total < 0.01 AND @Total > -0.01 
    SET @Total = 0

  RETURN @Total * -1
END