SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sendDrugsResidues]') and xtype = N'P')
drop procedure [dbo].[sp_sendDrugsResidues]
GO
-- =============================================
-- Author:		ЗАО Витакор
-- Create date: 2016.02.11
-- Description:	[dbo].[sp_sendDrugsResidues] 
-- =============================================
--CREATE PROCEDURE [dbo].[sp_sendDrugsResidues] 
--	@doc xml	-- xml
--As
--Begin
--	SET NOCOUNT ON;

--	If @doc Is NULL
--	begin
--		Select 1 ExitCode;
--		Select 'Ошибка' Err;
--		goto ENDING;
--	end

--	;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://www.w3.org/2001/XMLSchema-instance' as ns1, DEFAULT 'http://esc.ru/DOCSCLADREESTROST')
--	INSERT INTO [AKUZ].[T_MEDICINE_PRESENCE]([CODE],[C_PFS],[MedicinePresenceID],[LpuMain],[KOL],[DAT_INF],[DrugPrice],[DrugForm],[TYPE_FIN],[MNN])
--	Select --top 10000
--           T.c.value('./SUBDIVCODE[1]', 'int') CODE --(<CODE, int,>
--           ,NULL C_PFS --<C_PFS, int,>
--           ,newid() MedicinePresenceID --<MedicinePresenceID, uniqueidentifier,>
--           ,NULL LpuMain --<LpuMain, uniqueidentifier,>
--           ,cast(replace(T.c.value('./KOL[1]', 'varchar(max)'),',','.') as decimal(10,2)) KOL --<KOL, decimal(10,2),>
--           ,cast(replace(T.c.value('./DATE_OST[1]', 'varchar(10)'), '-', '') as datetime) DAT_INF --<DAT_INF, datetime,>
--           ,cast(replace(T.c.value('./CENAROZ[1]', 'varchar(max)'),',','.') as decimal(13,2)) DrugPrice --<DrugPrice, decimal(13,2),>
--           ,sp.NOMK_LS DrugForm --<DrugForm, int,>)
--		   ,T.c.value('./TYPE_FIN[1]', 'int') TYPE_FIN
--		   ,T.c.value('./MNNCODE[1]', 'int') AS MNNCODE -- 
--	FROM @doc.nodes('*/MAIN/DATAMAIN/DOCUMENTS/OST_LGOTA/OST') T(c)
--		join [AKUZ].T_DRUG_FORM_SPPOST sp On sp.C_LSFO = T.c.value('./DRUGSCODE[1]', 'int')
--	where 
--		exists (
--			select * 
--			from [AKUZ].T_PHARMACY p 
--			Where p.Code = T.c.value('./SUBDIVCODE[1]', 'int')
--			)
--		AND
--		not exists (
--			select * 
--			from [AKUZ].[T_MEDICINE_PRESENCE] mp 
--			where mp.DAT_INF = cast(replace(T.c.value('./DATE_OST[1]', 'varchar(10)'), '-', '') as datetime)
--			and mp.CODE = T.c.value('./SUBDIVCODE[1]', 'int')
--			and mp.TYPE_FIN = T.c.value('./TYPE_FIN[1]', 'int')
--			and mp.DrugForm = sp.NOMK_LS)


--	-- результат
--	Select 0 ExitCode;
--	Select 'Ok' Msg

--ENDING:
--	return
--End
--GO


