CREATE PROCEDURE dbo.RecurringPatternAvailableWaresByDateTime
(
	@WareNumber int
	,@Datetime datetime
)
/*
	Returns for a given warenumber and datetime the matching period from recurring patterns and number of available wares.
	Resultset: WareNumber, StartDateTime, EndDateTime, Available
	When ware is not related to any recurring pattern, a resultset with one row and StartDateTime=NULL will be returned.
	When ware is related to a recurring pattern but matching period is not found, an empty resultset is returned.

  Last Changed:
  KJ 20221216: Ext: Multiple rules per WareLinkGroup.
	2022-09-23	MLJ	First version

*/
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @RecurringPatterns TABLE (RecurringPatternId int)
	DECLARE @WarePeriods [RecurringPatternAvailableWaresTable]

	INSERT INTO @RecurringPatterns 
	SELECT DISTINCT X.FK_RecurringPatternId RecurringPatternId
	FROM RecurringPatternWareXTab X WITH (NOLOCK) 
	INNER JOIN RecurringRule RR WITH (NOLOCK) ON RR.FK_RecurringPatternID=X.FK_RecurringPatternId AND RR.RuleType = 4 
	WHERE X.FK_WareNumber=@WareNumber
	UNION
	SELECT DISTINCT WareLinkGroupRecurringPatternXTab.FKRecurringPatternId RecurringPatternId
	FROM WareLinkGroup WLG WITH (NOLOCK) 
  INNER JOIN WareLinkGroupRecurringPatternXTab WITH (NOLOCK) ON WareLinkGroupRecurringPatternXTab.FKWareLinkGroupId = WLG.Id
	INNER JOIN RecurringRule RR WITH (NOLOCK) ON RR.FK_RecurringPatternID=WareLinkGroupRecurringPatternXTab.FKRecurringPatternId
    AND RR.RuleType = 4 
	INNER JOIN Ware W ON W.WareLinkNumber=WLG.Id AND ISNULL(W.markasdeleted, 0) = 0 
	WHERE W.WareNumber=@WareNumber

	IF @@ROWCOUNT=0
	BEGIN
		SELECT @WareNumber WareNumber, CAST(NULL AS datetime) StartDateTime, CAST(NULL AS datetime) EndDateTime, CAST(NULL AS int) Available
	END
	ELSE
	BEGIN
		INSERT INTO @WarePeriods VALUES (@WareNumber, CAST(@Datetime AS DATE), DATEADD(day, 1, CAST(@Datetime AS DATE)), 0 )
		EXEC [dbo].[RecurringPatternAvailableWares] @WarePeriods=@WarePeriods, @ReturnSpecified=1, @ReturnSpecifiedDateTime=@Datetime
	END

END
