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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

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

  SELECT 
   T.c.value('./IDMO[1]', 'VARCHAR(17)') AS IDMO
  ,T.c.value('./MCOD[1]', 'VARCHAR(6)') AS MCOD
  ,T.c.value('./UIDMO[1]', 'VARCHAR(17)') AS UIDMO
  ,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 Actual = 0 
	from IES.T_F035_UMO t1
	where not exists(select 1 from #temp_f035 t2 
					cross apply(select top 1 * from ies.T_F031_ERMO f031 where t2.IDMO = f031.IDMO and f031.Actual = 1) f031
					cross apply(select top 1 * from ies.T_F003_MO f003 where t2.MCOD = f003.MCOD) f003
					where f031.F031ErmoID = t1.IDMO and f003.MCOD = t1.MCOD and t1.INN_U = t2.INN_U and t1.KPP_U = t2.KPP_U and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = 1

			-- обновляем актуальность у записей которые есть в Фед. справочнике
	update t1 
		set Actual = 1 
	from IES.T_F035_UMO t1
	where exists(select 1 from #temp_f035 t2 
					cross apply(select top 1 * from ies.T_F031_ERMO f031 where t2.IDMO = f031.IDMO and f031.Actual = 1) f031
					cross apply(select top 1 * from ies.T_F003_MO f003 where t2.MCOD = f003.MCOD) f003
					where f031.F031ErmoID = t1.IDMO and f003.MCOD = t1.MCOD and t1.INN_U = t2.INN_U and t1.KPP_U = t2.KPP_U and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = 0


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

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,Actual, UIDMO)
	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, 1, t1.UIDMO
FROM #temp_f035 t1
cross apply(select top 1 * from ies.T_F031_ERMO f031 where t1.IDMO = f031.IDMO and f031.Actual = 1) 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.mcod = f003.MCOD and t1.INN_U = t2.INN_U and t1.KPP_U = t2.KPP_U and t2.DATEBEG = t1.DATEBEG and t2.Actual = 1
where t2.F035UmoID is null

drop table #temp_f035

 --select * from [IES].[T_F035_UMO]
END
