if exists ( select * from sys.procedures p where p.name = 'sp_N021OnkLpshLoad' ) DROP PROCEDURE [dbo].[sp_N021OnkLpshLoad]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_N021OnkLpshLoad]
  @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

	IF OBJECT_ID('dbo.T_N021_ONK_LPSH_BACKUP','U') IS NULL
		begin
			SELECT * INTO dbo.T_N021_ONK_LPSH_BACKUP FROM ies.T_N021_ONK_LPSH
		end	

	select 
		 T.c.value('./ID_ZAP[1]', 'varchar(15)') AS ID_ZAP
		,T.c.value('./CODE_SH[1]', 'varchar(10)') AS CODE_SH
		,T.c.value('./ID_LEKP[1]', 'varchar(6)') AS ID_LEKP
		,T.c.value('./LEKP_EXT[1]', 'varchar(250)') AS LEKP_EXT
		,case when T.c.value('./ID_LEKP_EXT[1]', 'varchar(25)')='' then null else T.c.value('./ID_LEKP_EXT[1]', 'varchar(25)') end AS ID_LEKP_EXT
		,case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEBEG[1]', 'varchar(20)'),104) end 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 #N021
	from @Xml.nodes('/packet/zap') T(c)
	
	update t2 
	set  t2.CODE_SH=t1.CODE_SH
		,t2.ID_LEKP=aa.N020OnkLekpID
		,t2.DATEEND=t1.DATEEND
		,t2.LEKP_EXT=t1.LEKP_EXT
		,t2.ID_LEKP_EXT=t1.ID_LEKP_EXT
	from #N021 t1
	join [IES].[T_N021_ONK_LPSH] t2 on (t1.ID_ZAP=t2.ID_ZAP and t1.DATEBEG = t2.DATEBEG)
	cross apply(select top 1 * from ies.T_N020_ONK_LEKP n020 where n020.ID_LEKP = t1.ID_LEKP and t1.DATEBEG >= n020.DATEBEG and (t1.DATEEND <= n020.DATEEND or n020.DATEEND is null or t1.DATEEND is null) order by n020.DATEBEG desc) aa

	insert into [IES].[T_N021_ONK_LPSH](N021OnkLpshID, ID_ZAP, CODE_SH, ID_LEKP, LEKP_EXT, ID_LEKP_EXT, DATEBEG, DATEEND)
	select newid(),t1.ID_ZAP, t1.CODE_SH, aa.N020OnkLekpID, t1.LEKP_EXT, t1.ID_LEKP_EXT, t1.DATEBEG, t1.DATEEND
	from #N021 t1
	left join [IES].[T_N021_ONK_LPSH] t2 on (t1.ID_ZAP=t2.ID_ZAP and t1.DATEBEG = t2.DATEBEG)
	cross apply(select top 1 * from ies.T_N020_ONK_LEKP n020 where n020.ID_LEKP = t1.ID_LEKP and t1.DATEBEG >= n020.DATEBEG and (t1.DATEEND <= n020.DATEEND or n020.DATEEND is null or t1.DATEEND is null) order by n020.DATEBEG desc) aa
	where t2.N021OnkLpshID is null
		
	drop table #N021
END