IF EXISTS
(
	SELECT *
	FROM   sys.objects
	WHERE  object_id = OBJECT_ID(N'[dbo].[fn_GetDemandDrugRequestList]')
		   AND [type] IN(N'FN', N'IF', N'TF', N'FS', N'FT')
)
	BEGIN
		DROP FUNCTION [dbo].[fn_GetDemandDrugRequestList]
	END
--GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Marat Zigangaraev
---- Create date: 14.10.2010
---- Description:	Возвращает заявочный лист
---- =============================================


--CREATE FUNCTION [dbo].[fn_GetDemandDrugRequestList]
--(@DrugDemandID uniqueidentifier
--)
--RETURNS uniqueidentifier
--AS
--	BEGIN
--		RETURN
--		(
--			SELECT
--			(
--				SELECT TOP 1 [drl].[BaseDrugRequestItemID]
--				FROM         [AKUZ].[T_BASE_DRUG_REQUEST_ITEM] AS [drl]
--							 LEFT JOIN [AKUZ].[T_REQUEST_CATALOG] AS [rc] ON [rc].[BaseDrugRequestItemID] = [drl].[BaseDrugRequestItemID] 
--				WHERE        [drl].[RequestCode] = [dd].[RequestCode] 
--							 AND [drl].[AppListEndDate]/*Элемент зс*/ >= bdr.[BeginPeriod]/*Заявка*/
--							 and [drl].[AppListBeginDate]/*Элемент зс*/ <= bdr.[EndPeriod]/*Заявка*/
--							 AND ([rc].[BaseDrugRequestItemID] IS NULL
--								  OR [dr].[RequestType] = [rc].[BenefitType])
--							AND ((rc.SectionCode is null and  dd.SectionCode is null)
--									or rc.SectionCode = dd.SectionCode)
--			)
--			FROM [AKUZ].T_DRUG_DEMAND AS dd
--				 JOIN [AKUZ].T_DRUG_REQUEST AS dr ON dd.DrugRequest = dr.DrugRequestID
--				 join [AKUZ].T_BASE_DRUG_REQUEST AS bdr ON dd.DrugRequest = bdr.DrugRequestID
--			WHERE [dd].[DrugDemandID] = @DrugDemandID
--		)
--	END
--GO