if exists ( select * from sys.all_objects ao where ao.name = 'sp_F006VidExp' ) DROP PROCEDURE [dbo].[sp_F006VidExp]
GO

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

	declare @date datetime

	select top 1 @date = 
		 case when T.c.value('./date[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./date[1]', 'varchar(20)'),104) end 
	from @xml.nodes('/packet/zglv') T(c)

	select 
		 T.c.value('./IDVID[1]', 'int') AS IDVID
		,T.c.value('./VIDNAME[1]', 'varchar(2000)') AS VIDNAME
		,case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then @date else CONVERT(datetime,T.c.value('./DATEBEG[1]', 'varchar(20)'),104) end AS DATEBEG
		,case when T.c.value('./DATEEND[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEEND[1]', 'varchar(20)'),104) end AS DATEEND
	into #F006
	from @xml.nodes('/packet/zap') T(c)
	
	update t2 
	set  t2.VIDNAME=t1.VIDNAME
		,t2.DATEEND=t1.DATEEND
	from #F006 t1
	join [IES].[T_F006_CONTROL_TYPE] t2 on (t1.IDVID=t2.IDVID and t1.DATEBEG = t2.DATEBEG and t2.MigratedEntityName is null) 

	insert into [IES].[T_F006_CONTROL_TYPE](IDVID,VIDNAME,DATEBEG,DATEEND,F006ControlTypeID)
	select t1.IDVID,t1.VIDNAME,t1.DATEBEG,t1.DATEEND,NEWID()
	from #F006 t1
	left join [IES].[T_F006_CONTROL_TYPE] t2 on (t1.IDVID=t2.IDVID and t1.DATEBEG = t2.DATEBEG and t2.MigratedEntityName is null)
	where t2.IDVID is null
		
	drop table #F006
END