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

  SELECT 
  T.c.value('./idmo[1]', 'varchar(17)') AS IDMO
  ,T.c.value('./oid_mo[1]', 'varchar(35)') AS OID_MO  
  ,T.c.value('./fam_ruk[1]', 'varchar(40)') AS [FAM_RUK]
  ,T.c.value('./im_ruk[1]', 'varchar(40)') AS [IM_RUK]
  ,T.c.value('./ot_ruk[1]', 'varchar(40)') AS [OT_RUK]
  ,T.c.value('./type_ruk[1]', 'varchar(1)') AS [TYPE_RUK]
  ,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_f036
  FROM   @Xml.nodes('/packet/zap') T(c)

UPDATE t1 
SET 
      [FAM_RUK] = t2.[FAM_RUK]
      ,[IM_RUK] = t2.[IM_RUK]
      ,[OT_RUK] = t2.[OT_RUK]
      ,[TYPE_RUK] = t2.[TYPE_RUK]
      ,[DATEEND] = t2.DATEEND
FROM [IES].[T_F036_RukMO] t1
cross apply(select top 1 * from ies.T_F031_ERMO f031 where t1.IDMO = f031.F031ErmoID) f031
join #temp_f036 t2 on t2.IDMO = f031.IDMO and t2.DATEBEG = t1.DATEBEG

INSERT INTO [IES].[T_F036_RukMO]
			(F036RukMOID, IDMO, OID_MO, FAM_RUK, IM_RUK, OT_RUK, TYPE_RUK, DATEBEG, DATEEND)
	select
			NEWID(), f031.F031ErmoID, t1.OID_MO, t1.FAM_RUK, t1.IM_RUK, t1.OT_RUK, t1.TYPE_RUK, t1.DATEBEG, t1.DATEEND
FROM #temp_f036 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
left join [IES].[T_F036_RukMO] t2 on t2.IDMO = f031.F031ErmoID and t2.DATEBEG = t1.DATEBEG
where t2.F036RukMOID is null

drop table #temp_f036

END