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

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

	SELECT 
		T.c.value('./IDEKMP[1]','varchar(8)') AS IDEKMP, 
		case when T.c.value('./D_BEGIN[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./D_BEGIN[1]', 'varchar(20)'),104) end AS D_BEGIN,
		T.c.value('./FAM[1]','varchar(40)') AS FAM, 
		T.c.value('./IM[1]','varchar(40)') AS IM, 
		T.c.value('./OT[1]','varchar(40)') AS OT, 
		T.c.value('./CER_NOM[1]','varchar(1000)') AS CER_NOM, 
		case when T.c.value('./CER_DATEBEG[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./CER_DATEBEG[1]', 'varchar(20)'),104) end AS CER_DATEBEG,
		case when T.c.value('./CER_DATEEND[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./CER_DATEEND[1]', 'varchar(20)'),104) end AS CER_DATEEND, 
		T.c.value('./CER_ORG[1]',' varchar(1000)') AS CER_ORG, 
		T.c.value('./WORKPHONE[1]','varchar(250)') AS WORKPHONE, 
		T.c.value('./MOBILEPHONE[1]','varchar(250)') AS MOBILEPHONE, 
		T.c.value('./EMAIL[1]','varchar(250)') AS EMAIL, 
		T.c.value('./MEE[1]','varchar(10)') AS MEE, --false/true
		T.c.value('./KMP[1]','varchar(10)') AS KMP, --false/true
		case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then null 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(date,T.c.value('./DATEEND[1]', 'varchar(20)'),104) end AS DATEEND
	INTO #F041
	FROM @xml.nodes('/packet/zap') T(c)

	UPDATE t2 
	SET 
		 [D_BEGIN]		  =  t1.[D_BEGIN]
		,[FAM]            =  t1.[FAM]
		,[IM]			  =  t1.[IM]
		,[OT]			  =  t1.[OT]
		,[CER_NOM]		  =  t1.[CER_NOM]
		,[CER_DATEBEG]	  =  t1.[CER_DATEBEG]
		,[CER_DATEEND]	  =  t1.[CER_DATEEND]
		,[CER_ORG]		  =  t1.[CER_ORG]
		,[WORKPHONE]	  =  t1.[WORKPHONE]
		,[MOBILEPHONE]	  =  t1.[MOBILEPHONE]
		,[EMAIL]		  =  t1.[EMAIL]
		,[MEE]			  =  t1.[MEE]
		,[KMP]			  =  t1.[KMP]
		,[DATEBEG]		  =  t1.[DATEBEG]
		,[DATEEND]		  =  t1.[DATEEND]
	 FROM #F041 t1 
	 JOIN [IES].[T_F041_EREKMP] t2 on t1.[IDEKMP] = t2.[IDEKMP]
	 
	
	INSERT INTO [IES].[T_F041_EREKMP]
			   ([F041ErekmpID]
			   ,[IDEKMP]
			   ,[D_BEGIN]
			   ,[FAM]
			   ,[IM]
			   ,[OT]
			   ,[CER_NOM]
			   ,[CER_DATEBEG]
			   ,[CER_DATEEND]
			   ,[CER_ORG]
			   ,[WORKPHONE]
			   ,[MOBILEPHONE]
			   ,[EMAIL]
			   ,[MEE]
			   ,[KMP]
			   ,[DATEBEG]
			   ,[DATEEND])
	 SELECT		newid()
			   ,t1.[IDEKMP]
			   ,t1.[D_BEGIN]
			   ,t1.[FAM]
			   ,t1.[IM]
			   ,t1.[OT]
			   ,t1.[CER_NOM]
			   ,t1.[CER_DATEBEG]
			   ,t1.[CER_DATEEND]
			   ,t1.[CER_ORG]
			   ,t1.[WORKPHONE]
			   ,t1.[MOBILEPHONE]
			   ,t1.[EMAIL]
			   ,t1.[MEE]
			   ,t1.[KMP]
			   ,t1.[DATEBEG]
			   ,t1.[DATEEND] 
	 FROM #F041 t1 
	 LEFT JOIN [IES].[T_F041_EREKMP] t2 ON t1.[IDEKMP] = t2.[IDEKMP]
	 WHERE t2.F041ErekmpID is null

	 DROP TABLE #F041

END
