if exists ( select * from sys.procedures p where p.name = 'sp_F034VupLoad' ) DROP PROCEDURE dbo.sp_F034VupLoad
GO

CREATE PROCEDURE [dbo].[sp_F034VupLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

--на всякий 
  IF OBJECT_ID('dbo.T_F034_VUP_BACKUP','U') IS NULL
		begin
			SELECT * INTO dbo.T_F034_VUP_BACKUP FROM ies.T_F034_VUP 
		end


   SELECT 
   T.c.value('./UIDSPMO[1]', 'varchar(17)') AS UIDSPMO
  ,T.c.value('./IDADDRESS[1]', 'varchar(19)') AS IDADDRESS
  ,T.c.value('./MPVID[1]', 'varchar(4)') AS MPVID
  ,T.c.value('./MPUSL[1]', 'varchar(2)') AS MPUSL
  ,T.c.value('./MPROF[1]', 'varchar(3)') AS MPROF
  ,case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEBEG[1]', 'varchar(20)'),103) end AS DATEBEG
  ,case when T.c.value('./DATEEND[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEEND[1]', 'varchar(20)'),103) end AS DATEEND
  into #temp_f034
  FROM   @Xml.nodes('/packet/zap') T(c)

  
		-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update t2 
		set Actual = 0 
	from [IES].[T_F034_VUP] t2
	where 
	not exists(
						select 1 
						from #temp_f034 t1 
							cross apply(select top 1 * from ies.T_V008_MEDICAL_TYPE v008 where t1.MPVID = v008.IDVMP) v008
							cross apply(select top 1 * from ies.T_V006_MEDICAL_TERMS v006 where t1.MPUSL = v006.IDUMP) v006
							cross apply(select top 1 * from ies.T_V002_PROFILE v002 where t1.MPROF = v002.IDPR) v002
						where t2.UIDSPMO = t1.UIDSPMO and t2.IDADDRESS = t1.IDADDRESS and t2.MPVID = v008.IDVMP 
								and t2.MPUSL = v006.IDUMP and t2.MPROF = v002.IDPR and t2.DATEBEG = t1.DATEBEG
			  )
	and t2.Actual = 1



	INSERT INTO [IES].[T_F034_VUP]
				(F034VupID, UIDSPMO, IDADDRESS, MPVID, MPUSL, MPROF, DATEBEG, DATEEND, Actual)
		select
				NEWID(), t1.UIDSPMO, t1.IDADDRESS, v008.IDVMP, v006.IDUMP, v002.IDPR, t1.DATEBEG, t1.DATEEND,1
	FROM #temp_f034 t1
	cross apply(select top 1 * from ies.T_V008_MEDICAL_TYPE v008 where t1.MPVID = v008.IDVMP) v008
	cross apply(select top 1 * from ies.T_V006_MEDICAL_TERMS v006 where t1.MPUSL = v006.IDUMP) v006
	cross apply(select top 1 * from ies.T_V002_PROFILE v002 where t1.MPROF = v002.IDPR) v002
	left join [IES].[T_F034_VUP] t2 on t2.UIDSPMO = t1.UIDSPMO and t2.IDADDRESS = t1.IDADDRESS and t2.MPVID = v008.IDVMP and t2.MPUSL = v006.IDUMP and t2.MPROF = v002.IDPR and t2.DATEBEG = t1.DATEBEG and t2.Actual = 1
	where t2.F034VupID is null

	drop table #temp_f034

--select * from [IES].[T_F034_VUP]
END
