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

  SELECT 
  T.c.value('./idaddress[1]', 'varchar(19)') AS IDADDRESS
  ,T.c.value('./uidmo[1]', 'varchar(17)') AS UIDMO
  ,T.c.value('./idspmo[1]', 'varchar(17)') AS IDSPMO
  ,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 
	[ADDR] = t2.[ADDR]
	,[ADDR_GAR] = t2.[ADDR_GAR]
	,[N_DOC] = t2.[N_DOC]
	,[UIDMO] = f032.F032TrmoID
	,[IDSPMO] = f033.F033SpmoID
    ,[DATEBEG] = t2.DATEBEG
    ,[DATEEND] = t2.DATEEND
FROM [IES].[T_F038_ADDRMP] t1
join #temp_f038 t2 on t1.IDADDRESS = t2.IDADDRESS
outer apply(select top 1 * from ies.T_F032_TRMO f032 where t1.UIDMO = f032.UIDMO and t1.DATEBEG >= f032.DATEBEG and (f032.DATEEND is null or t1.DATEBEG <= f032.DATEEND)) f032
outer apply(select top 1 * from ies.T_F033_SPMO f033 where t1.IDSPMO = f033.IDSPMO and t1.DATEBEG >= f033.DATEBEG and (f033.DATEEND is null or t1.DATEBEG <= f033.DATEEND)) f033

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

drop table #temp_f038

END