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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

--на всякий 
  IF OBJECT_ID('dbo.T_F037_LicMO_BACKUP','U') IS NULL
		begin
			SELECT * INTO dbo.T_F037_LicMO_BACKUP FROM ies.T_F037_LicMO
		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('./N_DOC[1]', 'VARCHAR(32)')  AS N_DOC
  ,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_f037
  FROM   @Xml.nodes('/packet/zap') T(c)

  
  select * 
  	into #temp_f037_2
  FROM #temp_f037 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_F037_LicMO t1
	where not exists(select 1 from #temp_f037_2 t2 where  t1.IDMO = t2.F031ErmoID and t1.UIDMO = t2.F032TrmoID /*and t1.MCOD = t2.MCOD*/ and t1.N_DOC = t2.N_DOC and t1.DATEBEG = t2.DATEBEG)
	and t1.Actual = 1

			-- обновляем актуальность у записей которые есть в Фед. справочнике
	update t1 
		set Actual = 1 
	from IES.T_F037_LicMO t1
	where  exists(select 1 from #temp_f037_2 t2 where  t1.IDMO = t2.F031ErmoID and t1.UIDMO = t2.F032TrmoID /*and t1.MCOD = t2.MCOD*/ and t1.N_DOC = t2.N_DOC and t1.DATEBEG = t2.DATEBEG)
	and t1.Actual = 0
  
	UPDATE t1 
	SET 
		   [OID_MO]  = t2.OID_MO
		  ,[MCOD]    = t2.MCOD
		  ,[DATEEND] = t2.DATEEND
	FROM [IES].[T_F037_LicMO] t1
	join #temp_f037_2 t2 on t1.IDMO = t2.F031ErmoID and t1.UIDMO = t2.F032TrmoID /*and t1.MCOD = t2.MCOD */and t1.N_DOC = t2.N_DOC and t1.DATEBEG = t2.DATEBEG and t1.Actual=1

	INSERT INTO [IES].[T_F037_LicMO]
				(F037LicMOID, IDMO, OID_MO, MCOD, UIDMO, N_DOC, DATEBEG, DATEEND, Actual)
		select
				NEWID(), t1.F031ErmoID, t1.OID_MO, t1.MCOD, t1.F032TrmoID, t1.N_DOC, t1.DATEBEG, t1.DATEEND,1
	FROM #temp_f037_2 t1
	left join [IES].[T_F037_LicMO] t2 on t2.IDMO = t1.F031ErmoID and t2.UIDMO = t1.F032TrmoID /*and t2.MCOD = t1.MCOD*/ and t1.N_DOC = t2.N_DOC and t1.DATEBEG = t2.DATEBEG and t2.Actual=1
	where t2.F037LicMOID is null

drop table #temp_f037
drop table #temp_f037_2
--select * from [IES].[T_F037_LicMO]
END
