IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetDrugRequestList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetDrugRequestList]
GO

--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Dmitry
---- Create date: 05.07.2010
---- Description:	Возвращает DrugRequestList
---- =============================================
--CREATE FUNCTION [dbo].[fn_GetDrugRequestList]
--(
--	@Code int
--	, @OnDate datetime =NULL
--)
--RETURNS uniqueidentifier
--AS
--begin
--	Declare @c int
--	Set @c = case when @Code < 0 Then ~(@Code) Else @Code End

--	Declare 
--		@Mnn int
--		,@Trn int
--		,@MedicationForms uniqueidentifier
--		,@DoseView int
--		,@Dose varchar(255)
--		,@DoseCount int
--		,@Doses decimal(31,10)
--		,@VolumeView int
--		,@Weigth decimal(31,10)
--		,@WeightView int
--		,@Volume decimal(31,10)
	
--		select top 1
--				@Mnn = df.Mnn
--				,@Trn = df.Trn
--				,@MedicationForms = df.MedicationForms
--				,@DoseView = df.DoseView
--				,@Dose = df.Dose
--				,@DoseCount = df.DoseCount
--				,@Doses = df.Doses
--				,@VolumeView = df.VolumeView
--				,@Weigth = df.Weigth
--				,@WeightView = df.WeightView
--				,@Volume = df.Volume
--		from dbo.T_DRUG_FORM df WITH(NOLOCK)
--		where df.Code = @c

--	return(
--		select top 1 dr.BaseDrugRequestItemID
--		from [AKUZ].T_BASE_DRUG_REQUEST_ITEM dr WITH(NOLOCK)
--            join [AKUZ].T_DRUG_REQUEST_LIST drl on dr.BaseDrugRequestItemID = dr.BaseDrugRequestItemID
--		where dr.BeginDate <= IsNull(@OnDate, getdate()) 
--			and drl.Mnn = @Mnn
--			--And ( (dr.Trn Is Null And @Code < 0) OR dr.Trn = @Trn)
--			And ((drl.Trn Is Null AND @Trn IS NULL) OR drl.Trn = @Trn)
--			and dr.MedicationForms = @MedicationForms
--			and drl.DoseView = @DoseView
--			and drl.Dose = @Dose
--			and drl.DoseCount = @DoseCount
--			and drl.Doses = @Doses

--			and ((drl.Volume IS NULL AND @Volume IS NULL) OR drl.Volume = @Volume)
--			and ((drl.VolumeView IS NULL AND @VolumeView IS NULL) OR drl.VolumeView = @VolumeView)
--			and ((drl.Weigth IS NULL AND @Weigth IS NULL) OR drl.Weigth = @Weigth)
--			and ((drl.WeightView IS NULL AND @WeightView IS NULL) OR drl.WeightView = @WeightView)
--		order by dr.BeginDate desc, drl.Trn Desc
--		)
--end