CREATE FUNCTION DBO.GetFolioAmountByWareNumber (@ReferenceNumber INT, @GuestNumber INT, @FolioNumber INT, @WareNumber INT,  @StartDate CHAR(8), @EndDate CHAR(8))
  RETURNS @tbl TABLE (
    Amount MONEY NOT NULL,
    Pieces INT NOT NULL) AS
/*
  Created By Kent Johansen December 2006    

  Changes:
    KJ 20100323: Bug: Fix for "Pieces not correct for deleted transactions"
    KJ 20070502: Ext: Handle folioNumber = -1
    KJ 20060103: Added Pieces to result - now returning a table
*/


BEGIN
  DECLARE @Total FLOAT
  DECLARE @TotalPieces INT

  SET @Total = 0
  SET @TotalPieces = 0
  
  DECLARE @PriceOverwrite FLOAT
  DECLARE @Pieces INT
  DECLARE @Debit BIT

  DECLARE db_cur CURSOR FOR
    SELECT SUM(PriceOverwrite), SUM(Pieces), Debit
    FROM Transactions WITH (NOLOCK)
    INNER JOIN FOLIOS WITH (NOLOCK) ON Transactions.FolioNumber = FOLIOS.FolioNumber 
      AND Folios.ReferenceNumber = @ReferenceNumber
      AND Transactions.GuestNumber = @GuestNumber 
--      AND Transactions.FolioNumber = @FolioNumber
      AND ((@FolioNumber = -1) OR (Transactions.FolioNumber = @FolioNumber))
    WHERE Transactions.WareNumber = @WareNumber
    AND ShowOnBillFlag = 1
    AND CONVERT(CHAR(8), DateStamp, 112) BETWEEN @StartDate AND @EndDate
    GROUP BY Debit
    ORDER BY Debit DESC
  OPEN db_cur

  FETCH NEXT FROM db_cur INTO @PriceOverwrite, @Pieces, @Debit

  WHILE @@FETCH_STATUS <> -1
  BEGIN
    IF @Debit = 1
    BEGIN
      SET @Total = @Total + @PriceOverwrite
      SET @TotalPieces = @TotalPieces + @Pieces
    END
    ELSE
    BEGIN
      SET @Total = @Total - @PriceOverwrite
      SET @TotalPieces = @TotalPieces + @Pieces
    END

    FETCH NEXT FROM db_cur INTO @PriceOverwrite, @Pieces, @Debit
  END

  CLOSE db_cur
  DEALLOCATE db_cur

  INSERT INTO @tbl VALUES (@Total, @TotalPieces)

  RETURN
END