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

  SELECT 
  T.c.value('./idspmo[1]', 'varchar(17)') AS IDSPMO
  ,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)

INSERT INTO [IES].[T_F034_VUP]
			(F034VupID, IDSPMO, IDADDRESS, MPVID, MPUSL, MPROF, DATEBEG, DATEEND)
	select
			NEWID(), f033.F033SpmoID, f038.F038AddrmpID, v008.IDVMP, v006.IDUMP, v002.IDPR, t1.DATEBEG, t1.DATEEND
FROM #temp_f034 t1
cross apply(select top 1 * from ies.[T_F033_SPMO] f033 where t1.IDSPMO = f033.IDSPMO and t1.DATEBEG >= f033.DATEBEG and (f033.DATEEND is null or t1.DATEBEG <= f033.DATEEND)) f033
cross apply(select top 1 * from ies.T_F038_ADDRMP f038 where t1.IDADDRESS = f038.IDADDRESS and t1.DATEBEG >= f038.DATEBEG and (f038.DATEEND is null or t1.DATEBEG <= f038.DATEEND)) f038
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.IDSPMO = f033.F033SpmoID and t2.IDADDRESS = f038.F038AddrmpID and t2.MPVID = v008.IDVMP and t2.MPVID = v006.IDUMP and t2.MPVID = v002.IDPR and t2.DATEBEG = t1.DATEBEG 
where t2.F034VupID is null

drop table #temp_f034

END