CREATE PROCEDURE dbo.RecurringPatternWareAvailable @AWareNumber INT, @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AAvailable INT OUTPUT AS
--AV 2008 0701

/*Last Changed:
   AV 2008 0722 Return number of available units insted of true false
*/
 
 DECLARE @NumberForSale INT
 DECLARE @Pieces INT
 DECLARE @CurrentPieces INT
 DECLARE  @ArrNumber INT 
 SET @Pieces = 0
 SET @NumberForSale = 0
 SET @AAvailable = 0
 DECLARE ArrCur CURSOR LOCAL FAST_FORWARD FOR
 SELECT DISTINCT ArrangementLines.ArrangementLineNumber,  ISNULL(ArrangementLines.Pieces, 0)
 FROM  ArrangementReservationsXtab WITH (nolock) LEFT OUTER JOIN
 RESERVATIONS WITH (nolock) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber
 AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
 LEFT OUTER JOIN ArrangementLines WITH (nolock) ON
 ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
 WHERE LEN(ArrangementLines.DepartureTime) = 5 AND LEN(ArrangementLines.Time) = 5
 AND DBO.GetPicassoDateTimeAsRealDateTime(ArrangementLines.Date,ArrangementLines.Time ) <= @AEndDateTime
 AND DBO.GetPicassoDateTimeAsRealDateTime(ArrangementLines.Date,ArrangementLines.DepartureTime) >= @AStartDateTime
 AND Status <>7 AND Status <>8

 OPEN ArrCur
 FETCH NEXT FROM ArrCur INTO @ArrNumber, @CurrentPieces
 WHILE(@@Fetch_status=0)
 BEGIN
   SET @Pieces = @Pieces + @CurrentPieces
   FETCH NEXT FROM ArrCur INTO @ArrNumber, @CurrentPieces
 END 

 CLOSE  ArrCur
 DEALLOCATE ArrCur

 SELECT DISTINCT @NumberForSale = ISNULL(CONVERT(INT, RecurringRule.RuleValue), 0)  FROM RecurringPatternSpecified WITH (NOLOCK) 
 INNER JOIN RecurringPatternWareXTab WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternID = RecurringPatternSpecified.FK_RecurringPatternID
 INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
 WHERE RecurringPatternWareXTab.FK_WareNumber = @AWareNumber
 AND RecurringPatternSpecified.StartDateTime <= @AStartDateTime AND RecurringPatternSpecified.EndDateTime >= @AStartDateTime AND RecurringRule.RuleType = 4 

 SET @AAvailable = @NumberForSale - @Pieces

