if exists ( select * from sys.procedures p where p.name = 'sp_F031ErmoLoad' ) DROP PROCEDURE [dbo].[sp_F031ErmoLoad]
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 [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

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)
	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
FROM #temp_f031 t1
left join [IES].T_F031_ERMO t2 on t2.IDMO = t1.IDMO and t2.DATEBEG = t1.DATEBEG
where t2.F031ErmoID is null

drop table #temp_f031

END