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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

--на всякий 
  IF OBJECT_ID('dbo.T_F036_RukMO_BACKUP','U') IS NULL
		begin
			SELECT * INTO dbo.T_F036_RukMO_BACKUP FROM ies.T_F036_RukMO
		end

  SELECT 
   T.c.value('./IDMO[1]', 'VARCHAR(17)') AS IDMO
  ,T.c.value('./OID_MO[1]', 'VARCHAR(35)') AS OID_MO
  ,T.c.value('./MCOD[1]', 'VARCHAR(6)') AS MCOD
  ,T.c.value('./UIDMO[1]', 'VARCHAR(17)') AS UIDMO  
  ,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)

    select * 
  	into #temp_f036_2
  FROM #temp_f036 t1
  cross apply(select top 1 F031ErmoID from ies.T_F031_ERMO f031 where t1.IDMO = f031.IDMO and  f031.Actual = 1 ) f031
  --cross apply(select top 1 F032TrmoID from ies.T_F032_TRMO f032 where t1.UIDMO = f032.UIDMO and f032.Actual = 1 ) f032
  --cross apply(select top 1 MCOD as MCODid from ies.T_F003_MO f003 where t1.MCOD = f003.MCOD) f003

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

	UPDATE t1 
	SET 
		   [OT_RUK]  = t2.[OT_RUK]
		  ,[OID_MO]  = t2.OID_MO
		  ,[MCOD]    = t2.MCOD
		  ,[UIDMO]   = t2.UIDMO
		  ,[DATEEND] = t2.DATEEND
	FROM [IES].[T_F036_RukMO] t1
	join #temp_f036_2 t2 on t1.IDMO = t2.F031ErmoID and t1.FAM_RUK = t2.FAM_RUK  and t1.IM_RUK = t2.IM_RUK and t1.TYPE_RUK = t2.TYPE_RUK and t2.DATEBEG = t1.DATEBEG
	where t1.Actual =1

	INSERT INTO [IES].[T_F036_RukMO]
				(F036RukMOID, IDMO, OID_MO, FAM_RUK, IM_RUK, OT_RUK, TYPE_RUK, DATEBEG, DATEEND,MCOD,UIDMO ,Actual)
		select
				NEWID(), t1.F031ErmoID, t1.OID_MO, t1.FAM_RUK, t1.IM_RUK, t1.OT_RUK, t1.TYPE_RUK, t1.DATEBEG, t1.DATEEND, t1.MCOD, t1.UIDMO ,1
	FROM #temp_f036_2 t1
	left join [IES].[T_F036_RukMO] t2 on t2.IDMO = t1.F031ErmoID  and t1.FAM_RUK = t2.FAM_RUK  and t1.IM_RUK = t2.IM_RUK and t1.TYPE_RUK = t2.TYPE_RUK and t1.DATEBEG = t2.DATEBEG and t2.Actual = 1 -- тут смотря как добавят
	where t2.F036RukMOID is null

drop table #temp_f036
drop table #temp_f036_2
--select  *from [IES].[T_F036_RukMO]
END
