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

select
	T.c.value('./ID_PR[1]','varchar(2)') as ID_PR
	,case when T.c.value('./N_PR[1]','varchar(2000)')='' then NULL else T.c.value('./N_PR[1]','varchar(2000)') end as N_PR
	,convert(datetime,T.c.value('./DATEBEG[1]','varchar(20)'),104) as DATEBEG
	,case when T.c.value('./DATEEND[1]','varchar(20)')='' then NULL else convert(datetime,T.c.value('./DATEEND[1]','varchar(20)'),104) end as DATEEND
into #V042
from @Xml.nodes('/packet/zap') T(c)

update t2
set t2.N_PR=t1.N_PR
	,t2.DATEEND=t1.DATEEND
from #V042 t1
join [IES].[T_V042_ABORT_SL_PAY_REASON] t2 on (t1.ID_PR = t2.ID_PR and t1.DATEBEG = t2.DATEBEG)

insert into [IES].[T_V042_ABORT_SL_PAY_REASON](V042AbortSlPayReasonID, ID_PR, N_PR, DATEBEG, DATEEND)
select NEWID(), t1.ID_PR, t1.N_PR, t1.DATEBEG, t1.DATEEND
from #V042 t1
left join [IES].[T_V042_ABORT_SL_PAY_REASON] t2 on (t1.ID_PR = t2.ID_PR and t1.DATEBEG = t2.DATEBEG)
where t2.V042AbortSlPayReasonID is null

drop table #V042

END