/****** Object:  UserDefinedFunction [dbo].[GetQuantityOnSlotByTaskNumberAndOfferingID]    Script Date: 27.02.2016 0:39:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FN' AND OBJECT_ID = OBJECT_ID('dbo.GetQuantityOnSlotByTaskNumberAndOfferingID'))
   exec('DROP FUNCTION GetQuantityOnSlotByTaskNumberAndOfferingID')
GO
CREATE FUNCTION [dbo].[GetQuantityOnSlotByTaskNumberAndOfferingID]
(
	@StoreTaskNumber nvarchar(50),
	@OfferingID uniqueidentifier
)
RETURNS decimal(15,4)
AS
BEGIN
	RETURN

	ISNULL((
		SELECT SUM([tbl_OfferingInSlot].[Quantity])		
		FROM [dbo].[tbl_OfferingInSlot] AS [tbl_OfferingInSlot]
		INNER JOIN 
			[dbo].[tbl_ActiveSlot] AS [tbl_ActiveSlot] ON [tbl_ActiveSlot].[SlotID] = [tbl_OfferingInSlot].[SlotID]
		INNER JOIN 
			[dbo].[tbl_StoreTask] AS [tbl_StoreTask] ON [tbl_StoreTask].[ID] = [tbl_ActiveSlot].[StoreTaskID]
		WHERE 				
			[tbl_StoreTask].[Number] = @StoreTaskNumber		
		AND
			[tbl_OfferingInSlot].[OfferingID] = @OfferingID		
		AND	
			(			
			[tbl_StoreTask].[OfferingMovementID] = [tbl_OfferingInSlot].[OfferingMovementID]
			OR
			[tbl_StoreTask].[InvoiceID] = [tbl_OfferingInSlot].[InvoiceID]
			)
		),0)

END