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

select 
	T.c.value('./IDHM[1]','int') as [IDHM]
	,T.c.value('./HMNAME[1]','varchar(500)') as [HMNAME]
	,T.c.value('./DIAG[1]','varchar(5000)') as [DIAG]
	,T.c.value('./HVID[1]','varchar(30)') as [HVID]
	,T.c.value('./HGR[1]','int') as [HGR]
	,T.c.value('./HMODP[1]','varchar(500)') as [HMODP]
	,T.c.value('./IDMODP[1]','int') as [IDMODP]
	,case when T.c.value('./DATEBEG[1]','varchar(40)')='' then null else convert(datetime,T.c.value('./DATEBEG[1]','varchar(30)'),104) end as [DATEBEG]
	,case when T.c.value('./DATEEND[1]','varchar(40)')='' then null else convert(datetime,T.c.value('./DATEEND[1]','varchar(30)'),104) end as [DATEEND]
	into #V019
from @Xml.nodes('/packet/zap') T(c)

update #V019
set IDMODP = null
where IDMODP = 0

update t1
set t1.DATEEND = aa.DATEEND
	,t1.HMNAME = aa.HMNAME
	,t1.DIAG = aa.DIAG
	,t1.HMODP = aa.HMODP 
from #V019 aa
outer apply(select top 1 * from ies.T_V022_MODPAC t2 where aa.IDMODP = t2.IDMPAC and aa.datebeg >= t2.DATEBEG and (aa.DATEBEG <= t2.DATEEND or t2.DATEEND is null)) ab
left join [IES].T_V019_VMP_METHOD t1 on (t1.IDHM = aa.IDHM and t1.datebeg = aa.DateBeg and t1.HGR = aa.HGR and t1.HVID = aa.HVID and (t1.V022Modpac = ab.V022ModpacID or (t1.V022Modpac is null and ab.V022ModpacID is null)))

insert into [IES].T_V019_VMP_METHOD(V019VmpMethodID, IDHM, HMNAME, DIAG, HVID, DATEBEG, DATEEND, HGR, HMODP, [V022Modpac])
select newid(), t0.IDHM, t0.HMNAME, t0.DIAG, t0.HVID, t0.DATEBEG, t0.DATEEND, t0.HGR, t0.HMODP, aa.[V022ModpacID]
from #V019 t0
outer apply(select top 1 * from ies.T_V022_MODPAC t2 where t0.IDMODP = t2.IDMPAC and t0.datebeg >= t2.DATEBEG and (t0.DATEBEG <= t2.DATEEND or t2.DATEEND is null)) aa
left join [IES].T_V019_VMP_METHOD t1 on (t1.IDHM = t0.IDHM and t1.datebeg = t0.DateBeg and t1.HGR = t0.HGR and t1.HVID = t0.HVID and(t1.V022Modpac = aa.V022ModpacID or (t1.V022Modpac is null and aa.V022ModpacID is null)))
where t1.V019VmpMethodID is null

drop table #V019

END