if exists ( select * from sys.procedures p where p.name = 'sp_F037LicMoLoad' ) DROP PROCEDURE [dbo].[sp_F037LicMoLoad]
GO
CREATE PROCEDURE [dbo].[sp_F037LicMoLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

  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)
  
UPDATE t1 
SET 
      [DATEEND] = t2.DATEEND
FROM [IES].[T_F037_LicMO] t1
cross apply(select top 1 * from ies.T_F031_ERMO f031 where t1.IDMO = f031.F031ErmoID) f031
cross apply(select top 1 * from ies.T_F032_TRMO f032 where t1.UIDMO = f032.F032TrmoID) f032
cross apply(select top 1 * from ies.T_F003_MO f003 where t1.MCOD = f003.MCOD) f003
join #temp_f037 t2 on t2.IDMO = f031.IDMO and t2.UIDMO = f032.UIDMO and t2.MCOD = f003.MCOD and t1.N_DOC = t2.N_DOC and t2.DATEBEG = t1.DATEBEG 

INSERT INTO [IES].[T_F037_LicMO]
			(F037LicMOID, IDMO, OID_MO, MCOD, UIDMO, N_DOC, DATEBEG, DATEEND)
	select
			NEWID(), f031.F031ErmoID, f032.F032TrmoID, t1.MCOD, f032.F032TrmoID, t1.N_DOC, t1.DATEBEG, t1.DATEEND
FROM #temp_f037 t1
cross apply(select top 1 * from ies.T_F031_ERMO f031 where t1.IDMO = f031.IDMO and t1.DATEBEG >= f031.DATEBEG and (f031.DATEEND is null or t1.DATEBEG <= f031.DATEEND)) f031
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_F003_MO f003 where t1.MCOD = f003.MCOD) f003
left join [IES].[T_F037_LicMO] t2 on t2.IDMO = f031.F031ErmoID and t2.UIDMO = f032.F032TrmoID and t2.MCOD = f003.MCOD and t1.N_DOC = t2.N_DOC and t2.DATEBEG = t1.DATEBEG 
where t2.F037LicMOID is null

drop table #temp_f037

END