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

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

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


  SELECT 
   T.c.value('./IDADDRESS[1]', 'VARCHAR(19)') AS IDADDRESS
  ,T.c.value('./UIDMO[1]', 'VARCHAR(17)') AS UIDMO
  ,T.c.value('./UIDSPMO[1]', 'VARCHAR(17)') AS UIDSPMO
  ,T.c.value('./N_DOC[1]', 'VARCHAR(32)') AS N_DOC
  ,T.c.value('./ADDR[1]', 'VARCHAR(2000)') AS ADDR
  ,T.c.value('./ADDR_GAR[1]', 'VARCHAR(36)') AS ADDR_GAR
  ,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_f038
  FROM   @Xml.nodes('/packet/zap') T(c)


	-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update t1 
		set Actual = 0 
	from [IES].[T_F038_ADDRMP] t1
	where not exists(select 1 from #temp_f038 t2 
						cross apply(select top 1 F032TrmoID from ies.T_F032_TRMO f032 where t2.UIDMO = left(f032.UIDMO,11)    and f032.Actual = 1 ) f032
						where t1.IDADDRESS = t2.IDADDRESS and f032.F032TrmoID = t1.UIDMO and t2.UIDSPMO = t1.UIDSPMO and t1.DATEBEG = t2.DATEBEG )
	and t1.Actual = 1

  
	UPDATE t1 
	SET 
		 [ADDR] = t2.[ADDR]
		,[ADDR_GAR] = t2.[ADDR_GAR]
		,[N_DOC] = t2.[N_DOC]
		,[DATEEND] = t2.DATEEND
	FROM [IES].[T_F038_ADDRMP] t1
	join #temp_f038 t2 on t1.IDADDRESS = t2.IDADDRESS and  t2.UIDSPMO = t1.UIDSPMO
	cross apply(select top 1 * from ies.T_F032_TRMO f032 where t2.UIDMO = left(f032.UIDMO,11)   and f032.Actual = 1) f032
	where t1.UIDMO = f032.F032TrmoID and t1.DATEBEG = t2.DATEBEG and t1.Actual = 1 

	INSERT INTO [IES].[T_F038_ADDRMP]
				(F038AddrmpID, IDADDRESS, UIDMO, UIDSPMO, N_DOC, ADDR, ADDR_GAR, DATEBEG, DATEEND,Actual)
		select
				NEWID(), t1.IDADDRESS, f032.F032TrmoID, t1.UIDSPMO, t1.N_DOC, t1.ADDR, t1.ADDR_GAR, t1.DATEBEG, t1.DATEEND,1
	FROM #temp_f038 t1
	cross apply(select top 1 * from ies.T_F032_TRMO f032 where t1.UIDMO = left(f032.UIDMO,11) and f032.Actual = 1 ) f032
	left join [IES].[T_F038_ADDRMP] t2 on t1.IDADDRESS = t2.IDADDRESS and t2.UIDSPMO = t1.UIDSPMO and t2.UIDMO = f032.F032TrmoID  and t1.DATEBEG = t2.DATEBEG and t2.Actual =1 
	where t2.F038AddrmpID is null

drop table #temp_f038
--select * from [IES].[T_F038_ADDRMP]
END
