if exists ( select * from sys.procedures p where p.name = 'sp_F035UmoLoad' ) DROP PROCEDURE [dbo].[sp_F035UmoLoad]
GO
CREATE PROCEDURE [dbo].[sp_F035UmoLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

  SELECT 
  T.c.value('./idmo[1]', 'varchar(17)') AS IDMO
  ,T.c.value('./mcod[1]', 'varchar(6)') AS MCOD
  ,T.c.value('./oid_mo[1]', 'varchar(35)') AS OID_MO  
  ,T.c.value('./nam_umop[1]', 'varchar(1000)') AS [NAM_UMOP]
  ,T.c.value('./nam_umok[1]', 'varchar(250)') AS [NAM_UMOK]
  ,T.c.value('./inn_u[1]', 'varchar(12)') AS [INN_U]
  ,T.c.value('./kpp_u[1]', 'varchar(9)') AS [KPP_U]
  ,T.c.value('./nam_ppo_u[1]', 'varchar(250)') AS NAM_PRO_U
  ,T.c.value('./oktmo_u[1]', 'varchar(11)') AS OKTMO_U
  ,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_f035
  FROM   @Xml.nodes('/packet/zap') T(c)

UPDATE t1 
SET 
      [NAM_UMOP] = t2.NAM_UMOP
      ,[NAM_UMOK] = t2.NAM_UMOK
      ,[INN_U] = t2.INN_U
      ,[KPP_U] = t2.KPP_U
      ,[NAM_PRO_U] = t2.NAM_PRO_U
      ,[OKTMO_U] = t2.OKTMO_U
      ,[DATEEND] = t2.DATEEND
FROM [IES].[T_F035_UMO] t1
cross apply(select top 1 * from ies.T_F031_ERMO f031 where t1.IDMO = f031.F031ErmoID) f031
join #temp_f035 t2 on t2.IDMO = f031.IDMO and t2.DATEBEG = t1.DATEBEG

INSERT INTO [IES].[T_F035_UMO]
			(F035UmoID, IDMO, MCOD, OID_MO, NAM_UMOP, NAM_UMOK, INN_U, KPP_U, NAM_PRO_U, OKTMO_U, DATEBEG, DATEEND)
	select
			NEWID(), f031.F031ErmoID, t1.MCOD, t1.OID_MO, t1.NAM_UMOP, t1.NAM_UMOK, t1.INN_U, t1.KPP_U, t1.NAM_PRO_U, t1.OKTMO_U, t1.DATEBEG, t1.DATEEND
FROM #temp_f035 t1
cross apply(select top 1 * from ies.T_F031_ERMO f031 where t1.IDMO = f031.IDMO and t1.DATEBEG >= f031.DATEBEG and (f031.DATEEND is null or t1.DATEBEG <= f031.DATEEND)) f031
cross apply(select top 1 * from ies.T_F003_MO f003 where t1.MCOD = f003.MCOD) f003
left join [IES].[T_F035_UMO] t2 on t2.IDMO = f031.F031ErmoID and t2.DATEBEG = t1.DATEBEG
where t2.F035UmoID is null

drop table #temp_f035

END