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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

  SELECT 
   T.c.value('./IDMO[1]', 'VARCHAR(17)') AS IDMO
  ,T.c.value('./INN[1]','VARCHAR(12)') AS INN
  ,T.c.value('./NAM_MOP[1]', 'VARCHAR(2500)') AS NAM_MOP
  ,T.c.value('./NAM_MOK[1]', 'VARCHAR(2500)') AS NAM_MOK
  ,T.c.value('./KPP[1]', 'VARCHAR(9)') AS KPP
  ,T.c.value('./OGRN[1]', 'VARCHAR(15)') AS OGRN  
  ,T.c.value('./OID_MO[1]', 'VARCHAR(32)') AS OID_MO  
  ,T.c.value('./OKOPF[1]', 'VARCHAR(5)') AS OKOPF  
  ,T.c.value('./OKFS[1]', 'VARCHAR(2)') AS OKFS  
  ,T.c.value('./ADDR_J[1]', 'VARCHAR(300)') AS ADDR_J  
  ,T.c.value('./ADDR_J_GAR[1]', 'VARCHAR(36)') AS ADDR_J_GAR  
  ,T.c.value('./OKTMO[1]', 'VARCHAR(11)') AS OKTMO  
  ,T.c.value('./EMAIL[1]', 'VARCHAR(255)') AS EMAIL
  ,T.c.value('./PHONE[1]', 'VARCHAR(255)') AS PHONE
  ,T.c.value('./FAX[1]', 'VARCHAR(250)') AS FAX
  ,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_f031
  FROM   @Xml.nodes('/packet/zap') T(c)

			-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update t1 
		set Actual = 0 
	from IES.T_F031_ERMO t1
	where not exists(select 1 from #temp_f031 t2 where t2.IDMO = t1.IDMO and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = 1

			-- обновляем актуальность у записей которые есть в Фед. справочнике
	update t1 
		set Actual = 1 
	from IES.T_F031_ERMO t1
	where  exists(select 1 from #temp_f031 t2 where t2.IDMO = t1.IDMO and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = 0

	UPDATE t1 
	SET [NAM_MOP] = t2.[NAM_MOP]
		  ,[NAM_MOK] = t2.NAM_MOK
		  ,[INN] = t2.INN
		  ,[KPP] = t2.KPP
		  ,[OGRN] = t2.OGRN
		  ,[OID_MO] = t2.OID_MO
		  ,[OKOPF] = t2.OKOPF
		  ,[OKFS] = t2.OKFS
		  ,[ADDR_J] = t2.ADDR_J
		  ,[ADDR_J_GAR] = t2.ADDR_J_GAR
		  ,[OKTMO] = t2.OKTMO
		  ,[EMAIL] = t2.EMAIL
		  ,[PHONE] = t2.PHONE
		  ,[FAX] = t2.FAX
		  ,[DATEEND] = t2.DATEEND
	FROM [IES].T_F031_ERMO t1
	join #temp_f031 t2 on t2.IDMO = t1.IDMO and t2.DATEBEG = t1.DATEBEG and t1.Actual = 1

	INSERT INTO [IES].[T_F031_ERMO]
				(F031ErmoID, IDMO, NAM_MOP, NAM_MOK, INN, KPP, OGRN, OID_MO, OKOPF, OKFS, ADDR_J, ADDR_J_GAR, OKTMO, EMAIL, PHONE, FAX, DATEBEG, DATEEND,Actual)
		select
				NEWID(), t1.IDMO, t1.NAM_MOP, t1.NAM_MOK, t1.INN, t1.KPP, t1.OGRN, t1.OID_MO, t1.OKOPF, t1.OKFS, t1.ADDR_J, t1.ADDR_J_GAR, t1.OKTMO, t1.EMAIL, t1.PHONE, t1.FAX, t1.DATEBEG, t1.DATEEND,1
	FROM #temp_f031 t1
	left join [IES].T_F031_ERMO t2 on t2.IDMO = t1.IDMO and t2.DATEBEG = t1.DATEBEG and t2.Actual = 1
	where t2.F031ErmoID is null

	drop table #temp_f031
	--select * from [IES].[T_F031_ERMO]

END
