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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_F032TRMOLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

	select 
		 T.c.value('./UIDMO[1]', 'varchar(17)') AS UIDMO
		,T.c.value('./IDMO[1]', 'varchar(17)') AS IDMO
		,T.c.value('./MCOD[1]', 'varchar(6)') AS MCOD
		,T.c.value('./OKTMO_P[1]', 'varchar(11)') AS OKTMO_P
		,T.c.value('./SUBJ[1]', 'varchar(150)') AS SUBJ
		,case when T.c.value('./D_BEGIN[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./D_BEGIN[1]', 'varchar(20)'),104) end AS D_BEGIN
		,case when T.c.value('./D_END[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./D_END[1]', 'varchar(20)'),104) end AS D_END
		,T.c.value('./NAME_E[1]', 'varchar(1)') AS NAME_E
		,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 #F032
	from @xml.nodes('/packet/zap') T(c)
	
	update t2 
	set  t2.SUBJ=t1.SUBJ
		,t2.NAME_E=t1.NAME_E
		,t2.DATEEND=t1.DATEEND
		,t2.D_END=t1.D_END
	from #F032 t1
	join [IES].T_F032_TRMO t2 on (t1.UIDMO = t2.UIDMO and t1.DATEBEG = t2.DATEBEG)

	insert into [IES].T_F032_TRMO(F032TrmoID, UIDMO, IDMO, MCOD, OKTMO_P, SUBJ, D_BEGIN, D_END, NAME_E, DATEBEG, DATEEND)
	select newid(), t1.UIDMO, t1.IDMO, t1.MCOD, t1.OKTMO_P, t1.SUBJ, t1.D_BEGIN, t1.D_END, t1.NAME_E, t1.DATEBEG, t1.DATEEND
	from #F032 t1
	join ies.T_F003_MO f003 on (t1.MCOD = f003.MCOD)
	left join [IES].T_F032_TRMO t2 on (t1.UIDMO = t2.UIDMO and t1.DATEBEG = t2.DATEBEG)
	where t2.F032TrmoID is null

	drop table #F032

END

