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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE dbo.sp_K005AvgCostNormLoad
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

	SELECT 
		T.c.value('./UID_SN[1]','int') AS UID_SN, 
		T.c.value('./TF_OKATO_SN[1]','varchar(500)') AS TF_OKATO_SN, 
		T.c.value('./NUM_SN[1]','int') AS NUM_SN, 
		T.c.value('./NAM_SN[1]','varchar(1000)') AS NAM_SN, 
		T.c.value('./Z_SN[1]','decimal(15,2)') AS Z_SN, 
		T.c.value('./USL_OK_SN[1]','varchar(500)') AS USL_OK_SN, 
		T.c.value('./VIDPOM_SN[1]','varchar(500)') AS VIDPOM_SN, 
		T.c.value('./VID_HMP_SN[1]','varchar(500)') AS VID_HMP_SN,
		T.c.value('./METOD_HMP_SN[1]','varchar(500)') AS METOD_HMP_SN, 
		T.c.value('./PROFIL_SN[1]','varchar(500)') AS PROFIL_SN,
		T.c.value('./DISP_SN[1]','varchar(1)') AS DISP_SN, 
		T.c.value('./DS1_SN[1]','varchar(500)') AS DS1_SN,
		T.c.value('./N_KSG_SN[1]','varchar(500)') AS N_KSG_SN,
		T.c.value('./CODE_USL_SN[1]','varchar(500)') AS CODE_USL_SN, 
		T.c.value('./IDSP_SN[1]','int') AS IDSP_SN, 
		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 #temp_k005
	FROM @xml.nodes('/packet/zap') T(c)


	update t2 set 
			 TF_OKATO_SN	  = t1.TF_OKATO_SN
			,NUM_SN           = t1.NUM_SN         
			,NAM_SN			  = t1.NAM_SN			
			,Z_SN			  = t1.Z_SN			
			,USL_OK_SN		  = t1.USL_OK_SN		
			,VIDPOM_SN		  = t1.VIDPOM_SN		
			,VID_HMP_SN		  = t1.VID_HMP_SN		
			,METOD_HMP_SN	  = t1.METOD_HMP_SN	
			,PROFIL_SN		  = t1.PROFIL_SN		
			,DISP_SN		  = t1.DISP_SN		
			,DS1_SN			  = t1.DS1_SN			
			,N_KSG_SN		  = t1.N_KSG_SN		
			,CODE_USL_SN	  = t1.CODE_USL_SN	
			,IDSP_SN		  = t1.IDSP_SN		
			,DATEBEG		  = t1.DATEBEG		
			,DATEEND		  = t1.DATEEND		
		from #temp_k005 t1
		join IES.T_K005_AVG_COST_NORM t2 on t1.UID_SN=t2.UID_SN

	Insert Into IES.T_K005_AVG_COST_NORM 
	(
		 K005AvgCostNormID
		,UID_SN
		,TF_OKATO_SN	
		,NUM_SN         
		,NAM_SN			
		,Z_SN			
		,USL_OK_SN		
		,VIDPOM_SN		
		,VID_HMP_SN		
		,METOD_HMP_SN	
		,PROFIL_SN		
		,DISP_SN		
		,DS1_SN			
		,N_KSG_SN		
		,CODE_USL_SN	
		,IDSP_SN		
		,DATEBEG		
		,DATEEND		
	)
	select 
		 NEWID()
		,t1.UID_SN
		,t1.TF_OKATO_SN	
		,t1.NUM_SN         
		,t1.NAM_SN			
		,t1.Z_SN			
		,t1.USL_OK_SN		
		,t1.VIDPOM_SN		
		,t1.VID_HMP_SN		
		,t1.METOD_HMP_SN	
		,t1.PROFIL_SN		
		,t1.DISP_SN		
		,t1.DS1_SN			
		,t1.N_KSG_SN		
		,t1.CODE_USL_SN	
		,t1.IDSP_SN		
		,t1.DATEBEG		
		,t1.DATEEND		
	from #temp_k005 t1
	left join  IES.T_K005_AVG_COST_NORM  t2 on t1.UID_SN=t2.UID_SN
	where t2.UID_SN is null

	DROP TABLE #temp_k005

END