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 Actual = 0 
from [IES].[T_V019_VMP_METHOD] t1
where not exists(
	select 1 from #V019 t2 
	where t1.IDHM = t2.IDHM
	  and coalesce(t1.HVID, '') = coalesce(t2.HVID, '')
	  and coalesce(t1.HGR, -1) = coalesce(t2.HGR, -1)
	  and coalesce(t1.DATEBEG, '1900-01-01') = coalesce(t2.DATEBEG, '1900-01-01')
	)
and t1.Actual = 1

update t1 
	set Actual = 1 
from [IES].[T_V019_VMP_METHOD] t1
where exists(
	select 1 from #V019 t2 
	where t1.IDHM = t2.IDHM
	  and coalesce(t1.HVID, '') = coalesce(t2.HVID, '')
	  and coalesce(t1.HGR, -1) = coalesce(t2.HGR, -1)
	  and coalesce(t1.DATEBEG, '1900-01-01') = coalesce(t2.DATEBEG, '1900-01-01')
	)
and t1.Actual = 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)
	)
	and t1.Actual = 1
)

insert into [IES].T_V019_VMP_METHOD(V019VmpMethodID, IDHM, HMNAME, DIAG, HVID, DATEBEG, DATEEND, HGR, HMODP, [V022Modpac], Actual)
select newid(), t0.IDHM, t0.HMNAME, t0.DIAG, t0.HVID, t0.DATEBEG, t0.DATEEND, t0.HGR, t0.HMODP, aa.[V022ModpacID], 1
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)
	) 
	and t1.Actual = 1
)
where t1.V019VmpMethodID is null

drop table #V019

END