SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MakeRequest]') and xtype = N'P')
drop procedure [dbo].[sp_MakeRequest]
GO

---- =============================================
---- Author:		Marat Zigangarayev
---- Create date: 2013/12/03
---- Description:	Формирование заявочного листа из входного xml
---- =============================================
--CREATE PROCEDURE [dbo].[sp_MakeRequest] 
--	@fileName varchar(255)
--	,@lpuCode varchar(8)
--	,@mode int -- 0 - загрузка с распределением по месяцам, 1 - распределение на первый месяц
--	,@doc xml	-- xml
--As
--Begin
--	SET NOCOUNT ON;

--	declare @lpuID uniqueidentifier, @validState int, @half int
--	Select top 1 @lpuID = LpuID, @validState = 1 From [AKUZ].T_LPU_MAIN Where Code = @lpuCode
--	If @lpuID Is NULL
--	begin
--		Select 1 ExitCode;
--		Select  ('Не найден ЛПУ с кодом: ' + IsNull(@lpuCode,'')) Err;
--		goto ENDING;
--	end

--	declare @xml xml
--	set @xml = @doc

--	-- !!!!!!!!!!!!!!!!!!
--	DECLARE @DEBUG BIT
--	SET @DEBUG = 0

--	-- Заявки ЛПУ
--	;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as ns1, DEFAULT 'http://esc.ru/PLANZAKREESTR')
--	Select
--      T.c.value('./UNIQCODE[1]', 'int') AS UNIQCODE -- Уникальный код заявочной кампании
--      ,T.c.value('./TITLE[1]', 'varchar(255)') AS TITLE -- Наименование ЗК
--      ,cast(replace(T.c.value('./DATE_BEG[1]', 'varchar(10)'), '-', '') as datetime) AS DATE_BEG -- дата начала ЗК
--      ,cast(replace(T.c.value('./DATE_END[1]', 'varchar(10)'), '-', '') as datetime) AS DATE_END -- дата окончания ЗК
--      ,T.c.value('./C_FINL[1]', 'int') AS C_FINL -- Источник финансирования
--      ,T.c.value('./PROG_LLO[1]', 'int') AS PROG_LLO -- Программа ЛЛО
--      ,T.c.value('./STATE[1]', 'int') AS  [STATE] -- Состояние ЗК. 0 - открыта 1-закрыта 2-утверждена
--	  ,GETDATE() AS BeginDate
--	Into #zt
--	FROM @xml.nodes('/MAIN/DATAMAIN/DOCUMENTS/LPU_PLANZAKTITLES_DOC/LPU_PLANZAKTITLES') T(c)

--    -- Строки заявок ЛПУ
--    ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as ns1, DEFAULT 'http://esc.ru/PLANZAKREESTR')
--    Select
--      T.c.value('./TITLESID[1]', 'int') AS TITLESID -- ID заявочной кампании
--      ,T.c.value('./STRNPP[1]', 'int') AS STRNPP -- Порядковый номер строки
--      ,T.c.value('./MCOD[1]', 'varchar(128)') AS MCOD -- Код ЛПУ
--      ,T.c.value('./C_MNN[1]', 'int') AS C_MNN -- Код МНН
--      ,T.c.value('./C_LSFO[1]', 'int') AS C_LSFO -- Код ЛС поставщика
--      ,T.c.value('./QUANTITY[1]', 'decimal(10,2)') AS QUANTITY -- Количество упаковок
--      ,T.c.value('./PRICE[1]', 'varchar(128)') AS PRICE -- Предельная цена за единицу товара
--      ,T.c.value('./C_LF[1]', 'int') AS C_LF -- Код лекарственной формы
--      ,T.c.value('./D_LS[1]', 'varchar(128)') AS D_LS -- Дозировка действующего вещества
--      ,T.c.value('./C_DLS[1]', 'int') AS C_DLS -- Код единицы измерения дозировки
--      ,T.c.value('./V_LF[1]', 'decimal(31,10)') AS V_LF -- Объем лекарственной формы (заполняется для растворов, сиропов, суспензий, аэрозолей, мазей)
--      ,T.c.value('./M_LF[1]', 'decimal(31,10)') AS M_LF -- Вес лекарственной формы
--      ,T.c.value('./N_FV[1]', 'int') AS N_FV -- Фасовка лекарственного средства
--      ,T.c.value('./C_FINL[1]', 'int') AS C_FINL -- Источник финансирования (1 – Федеральный; 2 – Субъект РФ; 3 – муниципальный)
--      ,T.c.value('./PROG_LLO[1]', 'varchar(8)') AS PROG_LLO -- Программа ЛЛО
--      ,T.c.value('./DESTROYED[1]', 'varchar(8)') AS DESTROYED  -- Признак удаленной записи
--	  ,cast(NULL As int) RequestCode
----	  ,cast(0 as int) AS DRUG_FORM
----	  ,cast(0 as int) AS Mnn
----	  ,cast(0 as int) AS TRN
--	Into #zd
--	FROM @xml.nodes('/MAIN/DATAMAIN/DOCUMENTS/LPU_PLANZAKDATA_DOC/LPU_PLANZAKDATA') T(c)
--	Where T.c.value('./MCOD[1]', 'varchar(128)') = @lpuCode

--	If (Select Count(*) From #zd) = 0
--	begin
--		Select 2 ExitCode;
--		Select  ('Для ЛПУ с кодом ' + IsNull(@lpuCode,'') + ' заявки не найдены.') Err
--		goto ENDING;
--	end

--	Update t
--	Set t.RequestCode = df.Code
--		,t.C_LF = df.MedicationForms
--		,t.C_DLS = df.DoseView 
--		,t.D_LS = df.Dose
--		,t.N_FV = df.DoseCount 
--		,t.M_LF = df.Weigth 
--		,t.V_LF = df.Volume
--	From #zd t
--		Join T_DRUG_FORM df On df.LSFO = t.C_LSFO
--	Where t.C_LSFO IS NOT NULL

--	Update t
--	Set t.RequestCode = (
--			Select top 1 df.Code 
--			From T_DRUG_FORM df 
--			Where t.C_MNN = df.Mnn
--				and (t.C_LF = df.MedicationForms OR (t.C_LF IS NULL AND df.MedicationForms IS NULL))
--				and (t.C_DLS = df.DoseView OR ((t.C_DLS IS NULL OR t.C_DLS = '0') AND (df.DoseView IS NULL OR df.DoseView = '0')))
--				and (t.D_LS = df.Dose OR ((t.D_LS IS NULL OR t.D_LS = '0') AND (df.Dose IS NULL OR df.Dose = '0')))
--				and (t.N_FV = df.DoseCount OR (t.N_FV IS NULL AND df.DoseCount IS NULL))
--				and (t.M_LF = df.Weigth OR (t.M_LF IS NULL AND df.Weigth IS NULL))
--				and (t.V_LF = df.Volume OR (t.V_LF IS NULL AND df.Volume IS NULL))
--			Order by df.Code ASC
--		) 
--	From #zd t
--	Where t.C_LSFO IS NULL

--	IF @DEBUG = 0
--	BEGIN
--		if exists(select * From #zd t where t.RequestCode IS NULL)
--		begin
--			Select 3 ExitCode;
--			Select 'Не найден код формы выпуска лекарственных средств' Err, TITLESID, STRNPP, MCOD, C_MNN, C_LSFO, QUANTITY, PRICE, C_LF, D_LS, C_DLS, V_LF, M_LF, N_FV, C_FINL, PROG_LLO, DESTROYED
--			From #zd t where t.RequestCode IS NULL;
--			goto ENDING;
--		end
--	END

--	--goto ENDING;

--	IF @DEBUG > 0
--	BEGIN
--		Delete From #zd	Where RequestCode IS NULL-- OR C_LF IS NULL
--	END

--	---- 1. заносим недостающие записи в справочник
--	--INSERT INTO [AKUZ].[T_DRUG_REQUEST_LOT]([DrugRequestLotID],[Number],[RequestYear],[HalfYear],[Name])
--	--Select NEWID() DrugRequstLogID, 1 Number, t1.RequestYear, t1.HalfYear, Convert(varchar(10), t1.BeginDate, 104) Name
--	--From #zt t1 
--	--Group By t1.RequestYear, t1.HalfYear, t1.BeginDate
--	--Having Not Exists(Select * From [AKUZ].T_DRUG_REQUEST_LOT l Where t1.RequestYear = l.RequestYear and t1.HalfYear = l.HalfYear)

--	-- 2. 
--	ALTER TABLE #zd ADD id uniqueidentifier
--	update #zd set id = newID()
--	INSERT INTO [AKUZ].[T_BASE_DRUG_REQUEST_ITEM]
--           ([BaseDrugRequestItemID]
--           ,[BeginDate]
--           ,[Price]
--           ,[MedicationForms]
--           ,[RequestCode]
--           ,[AppListBeginDate]
--           ,[AppListEndDate]
--           ,[type_])
--		 Select id [BaseDrugRequestItemID], 
--		t2.DATE_BEG BeginDate
--		,t1.PRICE Price
--		,t1.C_LF MedicationForms
--		,t1.RequestCode RequestCode
--		,t2.DATE_BEG [AppListBeginDate]
--		,t2.DATE_END [AppListEndDate]
--		,709 [type_]
--	From #zd t1
--		Inner Join #zt t2 On t1.TITLESID = t2.UNIQCODE
--	Where not exists
--	(
--		Select *
--		from [AKUZ].T_DRUG_REQUEST_LIST drl
--		join [AKUZ].[T_BASE_DRUG_REQUEST_ITEM] ri on ri.[BaseDrugRequestItemID] = drl.[BaseDrugRequestItemID]
--		where drl.PartNo = 1
--		and ri.RequestCode = t1.RequestCode
--		and ri.AppListBeginDate <= t2.DATE_BEG
--		and ri.AppListEndDate >= t2.DATE_END
--	)
	
--	INSERT INTO [AKUZ].[T_DRUG_REQUEST_LIST]
--		([BaseDrugRequestItemID],
--		[PartNo],
--		[N_LS],
--		[Mnn],
--		[Trn],
--		[Dose],
--		[VolumeView],
--		[Weigth],
--		[WeightView],
--		[Volume]
--        ,[DoseCount]
--        ,[Doses]
--		,DoseView)
--	Select id [BaseDrugRequestItemID]
--		, 1 PartNo
--		, t1.STRNPP N_LS
--		, ISNULL(t1.C_MNN, (Select top 1 df.Mnn From T_DRUG_FORM df Where df.LSFO = t1.C_LSFO)) Mnn
--		, (Select top 1 df.Trn From T_DRUG_FORM df Where df.LSFO = t1.C_LSFO) Trn
--		, t1.D_LS Dose
--		, NULL VolumeView -- 't1.C_VLF' 
--		, t1.M_LF Weigth
--		, NULL WeightViewID -- 't1.C_MLF' 
--		, t1.V_LF Volume
--		,t1.N_FV DoseCount
--		,NULL Doses -- 't1.N_DOZA'
--		,t1.C_DLS
--	From #zd t1
--		Inner Join #zt t2 On t1.TITLESID = t2.UNIQCODE
--	Where not exists
--	(
--		Select *
--		from [AKUZ].T_DRUG_REQUEST_LIST drl
--		join [AKUZ].[T_BASE_DRUG_REQUEST_ITEM] ri on ri.[BaseDrugRequestItemID] = drl.[BaseDrugRequestItemID]
--		where drl.PartNo = 1
--		and ri.RequestCode = t1.RequestCode
--		and ri.AppListBeginDate <= t2.DATE_BEG
--		and ri.AppListEndDate >= t2.DATE_END
--	)

--	-- 3. удаляем полностью заявку, если есть
--	declare @dr table (DrugRequestID uniqueidentifier)
--	Insert Into @dr
--	Select dr.DrugRequestID 
--	From [AKUZ].T_DRUG_REQUEST dr
--	join [AKUZ].T_BASE_DRUG_REQUEST bdr on bdr.DrugRequestID = dr.DrugRequestID
--	Where exists (
--		Select * 
--		From #zt t
--		Where exists (Select * from #zd d Where d.TITLESID = t.UNIQCODE AND d.C_FINL = t.C_FINL AND d.MCOD = @lpuCode)
--			AND t.[STATE] = @validState
--			AND bdr.Active = 1 AND t.DATE_BEG = bdr.BeginPeriod
--			AND t.DATE_END = bdr.EndPeriod AND t.DATE_BEG = bdr.[RequestDate] AND t.C_FINL = dr.RequestType AND t.UNIQCODE = dr.Number
--			AND bdr.LpuMain = (select top 1 LpuID from [AKUZ].T_LPU_MAIN where Code  = @lpuCode) )
	
--	Delete ph
--	From [AKUZ].T_PHARMACY_DRUG_DEMAND ph
--		Join [AKUZ].T_DRUG_DEMAND dd On ph.DrugDemand = dd.DrugDemandID
--	Where dd.DrugRequest in (Select DrugRequestID From @dr)

--	Delete dd
--	From [AKUZ].T_DRUG_DEMAND dd
--	Where dd.DrugRequest in (Select DrugRequestID From @dr)
	
--	delete dr
--	from [AKUZ].T_DRUG_REQUEST dr
--	where dr.DrugRequestID in (Select DrugRequestID From @dr)

--	-- 4. заявка
--	Select 
--	NEWID() DrugRequestID, 
--	t.DATE_BEG RequestDate, 
--	@lpuID LpuMain
--	, cast(1 as bit) Active, 
--	 cast(NULL as uniqueidentifier) DrugRequestMain
--	--, ISNULL((1+(Select top 1 dr.Number From [AKUZ].T_DRUG_REQUEST dr Where dr.Active = 1 AND t.RequestYear = dr.RequestYear AND t.HalfYear = dr.HalfYear AND t.DATE_BEG = dr.[RequestDate] AND t.C_FINL = dr.RequestType)),0) Number
--	, cast(t.UNIQCODE as int) Number
--	, cast(t.C_FINL as int) RequestType
--	, cast(@fileName as varchar(255)) ImportFileName
--	, cast(t.TITLE as varchar(255)) Name
--	,t.DATE_BEG [BeginPeriod]
--	,t.DATE_END [EndPeriod]
--	Into #DRUG_REQUEST
--	From #zt t
--	Where exists (Select * from #zd d Where d.TITLESID = t.UNIQCODE AND d.C_FINL = t.C_FINL AND d.MCOD = @lpuCode)
--		AND t.[STATE] = @validState
	
--INSERT INTO [AKUZ].[T_BASE_DRUG_REQUEST]
--           ([DrugRequestID]
--           ,[RequestDate]
--           ,[LpuMain]
--           ,[Active]
--           ,[DrugRequestMain]
--           ,[Name]
--           ,[BeginPeriod]
--           ,[EndPeriod]
--           ,[Status]
--           ,[type_])
--	Select	
--	[DrugRequestID],
--	[RequestDate],
--	[LpuMain],
--	[Active],
--	[DrugRequestMain],
--	[Name],
--	[BeginPeriod],
--    [EndPeriod],
--	3
--	,724
--	From #DRUG_REQUEST

--INSERT INTO [AKUZ].[T_DRUG_REQUEST]
--           ([DrugRequestID]
--           ,[Number]
--           ,[RequestType]
--           ,[ImportFileName])
--	Select	
--	[DrugRequestID],
--	[Number],
--	[RequestType],
--	[ImportFileName]
--	From #DRUG_REQUEST

--	-- 5. потребность
--	select NEWID() DrugDemandID,dr.DrugRequestID DrugRequest, cast(0 as int) DemandOnPeriod, d.QUANTITY RequestQuantity, d.RequestCode RequestCode, d.C_LSFO LSFO, dr.BeginPeriod, dr.EndPeriod
--	Into #DRUG_DEMAND
--	From #zd d 
--		Join #DRUG_REQUEST dr On d.TITLESID = dr.Number AND d.C_FINL = dr.RequestType
--	Where d.MCOD = @lpuCode

--	INSERT INTO [AKUZ].[T_DRUG_DEMAND]([DrugDemandID],[DrugRequest],[DemandOnPeriod],[RequestQuantity],[RequestCode],[LSFO])
--	Select [DrugDemandID],[DrugRequest],[DemandOnPeriod],[RequestQuantity],[RequestCode],[LSFO] From #DRUG_DEMAND

--	-- 6. распределение потребности по аптекам

--	-- таблица с аптеками для распределения
--	declare @phs table (pharmacy int)
--	Insert into @phs(pharmacy)
--	Select distinct ( Select top 1 sp.Pharmacy
--		From [AKUZ].T_STATE_PHARMACY sp
--			Join [AKUZ].T_STATE s On sp.[State] = s.StateID
--			Join [AKUZ].T_DIVISION d On s.Division = d.DivisionID
--		Where d.Branch = b.LpuID
--		) Pharmacy
--	From [AKUZ].T_BRANCH b
--	Where b.LpuMain = @lpuID
--	and exists( Select * 
--		From [AKUZ].T_STATE_PHARMACY sp
--			Join [AKUZ].T_STATE s On sp.[State] = s.StateID
--			Join [AKUZ].T_DIVISION d On s.Division = d.DivisionID
--		Where d.Branch = b.LpuID)

--	-- кол-во аптек
--	Declare @m int
--	Set @m = (Select COUNT(*) From @phs)

--	if @m = 0 goto ENDING;

--	-- курсор на потребность в заявке
--	declare dd_curs cursor local for
--	select DrugDemandID, RequestQuantity, BeginPeriod, EndPeriod
--	from #DRUG_DEMAND

--	declare @DrugDemandID uniqueidentifier, @n int, @BeginPeriod datetime, @EndPeriod datetime

--	open dd_curs
--	fetch next from dd_curs into @DrugDemandID, @n, @BeginPeriod, @EndPeriod 
--	while @@fetch_status = 0
--	begin
--		-- предв.вычисления
--		declare @val int, @remain int, @start int, @end int
--		set @val = @n/@m;
--		set @remain = @n - @m * @val
--		set @start = DATEPART(MONTH, @BeginPeriod)
--		set @end = DATEPART(MONTH, @EndPeriod)


--		declare @Pharmacy int, @RequestQuantity int, @Mont1 int, @Mont2 int, @Mont3 int, @Mont4 int, @Mont5 int, @Mont6 int, @Mont7 int, @Mont8 int, @Mont9 int, @Mont10 int, @Mont11 int, @Mont12 int

--		select @Mont1 = 0, @Mont2 = 0, @Mont3 = 0, @Mont4 = 0, @Mont5 = 0, @Mont6 = 0, @Mont7 = 0, @Mont8 = 0, @Mont9 = 0, @Mont10 = 0, @Mont11 = 0, @Mont12 = 0

--		-- курсор по аптекам
--		declare ph_curs cursor local for
--		select pharmacy from @phs
--		open ph_curs
--		fetch next from ph_curs into @Pharmacy
--		while @@fetch_status = 0
--		begin

--			-- находим кол-во для текущей аптеки
--			if @remain > 0 select @RequestQuantity = @val + 1, @remain = @remain - 1
--			else set @RequestQuantity = @val

--			-- вычисление месяцев
--			declare @valM int, @remainM int, @Monts int
--			if(@start > @end)
--			begin
--			set @Monts = DateDiff(MONTH, @BeginPeriod, @EndPeriod) + 1
--			end
--			else
--			set @Monts = @end - @start + 1

--			set @valM = @RequestQuantity/@Monts;
--			set @remainM = @RequestQuantity - @Monts * @valM
			
--				if @mode = 0
--				    begin
--				     if (@remainM > 0 and @start <= 1 and @end >= 1) select @Mont1 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 1 and @end >= 1) select @Mont1 = @valM
--				       else set @Mont1 = 0  
				           
--				     if (@remainM > 0 and @start <= 2 and @end >= 2) select @Mont2 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 2 and @end >= 2) select @Mont2 = @valM
--				       else set @Mont2 = 0
				     
--				     if (@remainM > 0 and @start <= 3 and @end >= 3) select @Mont3 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 3 and @end >= 3) select @Mont3 = @valM
--				       else set @Mont3 = 0
				     
--				     if (@remainM > 0 and @start <= 4 and @end >= 4) select @Mont4 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 4 and @end >= 4) select @Mont4 = @valM
--				       else set @Mont4 = 0
				     
--				     if (@remainM > 0 and @start <= 5 and @end >= 5) select @Mont5 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 5 and @end >= 5) select @Mont5 = @valM
--				       else set @Mont5 = 0
				     
--				     if (@remainM > 0 and @start <= 6 and @end >= 6) select @Mont6 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 6 and @end >= 6) select @Mont6 = @valM
--				       else set @Mont6 = 0
				     
--				     if (@remainM > 0 and @start <= 7 and @end >= 7) select @Mont7 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 7 and @end >= 7) select @Mont7 = @valM
--				       else set @Mont7 = 0
				     
--				     if (@remainM > 0 and @start <= 8 and @end >= 8) select @Mont8 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 8 and @end >= 8) select @Mont8 = @valM
--				       else set @Mont8 = 0
				     
--				     if (@remainM > 0 and @start <= 9 and @end >= 9) select @Mont9 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 9 and @end >= 9) select @Mont9 = @valM
--				       else set @Mont9 = 0
				     
--				     if (@remainM > 0 and @start <= 10 and @end >= 10) select @Mont10 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 10 and @end >= 10) select @Mont10 = @valM
--				       else set @Mont10 = 0
				     
--				     if (@remainM > 0 and @start <= 11 and @end >= 11) select @Mont11 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 11 and @end >= 11) select @Mont11 = @valM
--				       else set @Mont11 = 0
				     
--				     if (@remainM > 0 and @start <= 12 and @end >= 12) select @Mont12 = @valM + 1, @remainM = @remainM - 1
--				     else if (@remainM = 0 and @start <= 12 and @end >= 12) select @Mont12 = @valM
--				       else set @Mont12 = 0
--				    end
--				    else 
--				     select @Mont1 = case when @start = 1 then @RequestQuantity else 0 end
--				     ,@Mont2 = case when @start = 2 then @RequestQuantity else 0 end
--				     ,@Mont3 = case when @start = 3 then @RequestQuantity else 0 end
--				     ,@Mont4 = case when @start = 4 then @RequestQuantity else 0 end
--				     ,@Mont5 = case when @start = 5 then @RequestQuantity else 0 end
--				     ,@Mont6 = case when @start = 6 then @RequestQuantity else 0 end
--				     ,@Mont7 = case when @start = 7 then @RequestQuantity else 0 end
--				     ,@Mont8 = case when @start = 8 then @RequestQuantity else 0 end
--				     ,@Mont9 = case when @start = 9 then @RequestQuantity else 0 end
--				     ,@Mont10 = case when @start = 10 then @RequestQuantity else 0 end
--				     ,@Mont11 = case when @start = 11 then @RequestQuantity else 0 end
--				     ,@Mont12 = case when @start = 12 then @RequestQuantity else 0 end


--			INSERT INTO [AKUZ].[T_PHARMACY_DRUG_DEMAND]([PharmacyDrugDemandID],[DrugDemand],[DemandOnPeriod],[Pharmacy],[RequestQuantity],[Mont1],[Mont2],[Mont3],[Mont4],[Mont5],[Mont6],[Mont7],[Mont8],[Mont9],[Mont10],[Mont11],[Mont12])
--			VALUES(NEWID(),@DrugDemandID,0,@Pharmacy,@RequestQuantity, @Mont1, @Mont2, @Mont3, @Mont4, @Mont5, @Mont6, @Mont7, @Mont8, @Mont9, @Mont10, @Mont11, @Mont12)

--			fetch next from ph_curs into @Pharmacy
--		end

--		close ph_curs
--		deallocate ph_curs

--		fetch next from dd_curs into @DrugDemandID, @n, @BeginPeriod, @EndPeriod

--	end

--	close dd_curs
--	deallocate dd_curs

--	-- результат
--	Select 0 ExitCode;
--	Select 'Загружена заявка', [DrugRequestID],[RequestDate],[LpuMain],[Active],[DrugRequestMain],[Number],[RequestType],[ImportFileName],[Name]
--	From #DRUG_REQUEST

--ENDING:
--	return
--	--drop table #zt
--	--drop table #zd
--	--drop table #DRUG_REQUEST
--	--drop table #DRUG_DEMAND

--End
