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_begin_oms[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./d_begin_oms[1]', 'varchar(20)'),104) end AS D_BEGIN_OMS
		,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
		,T.c.value('./osp[1]', 'varchar(6)') AS osp
		,T.c.value('./vid_mo[1]', 'varchar(500)') AS vid_mo
		,T.c.value('./oid_mo[1]', 'varchar(255)') AS oid_mo
		,T.c.value('./oid_spmo[1]', 'varchar(255)') AS oid_spmo
		,T.c.value('./nam_mop[1]', 'varchar(500)') AS nam_mop
		,T.c.value('./nam_mok[1]', 'varchar(255)') AS nam_mok
		,T.c.value('./inn[1]', 'varchar(50)') AS inn
		,T.c.value('./kpp[1]', 'varchar(50)') AS kpp
		,T.c.value('./ogrn[1]', 'varchar(50)') AS ogrn
		,T.c.value('./juraddress_index[1]', 'varchar(50)') AS juraddress_index
		,T.c.value('./juraddress_address[1]', 'varchar(500)') AS juraddress_address
		,T.c.value('./gar_address[1]', 'uniqueidentifier') AS gar_address
		,T.c.value('./okfs[1]', 'varchar(50)') AS okfs
		,T.c.value('./vedpri[1]', 'varchar(50)') AS vedpri
		,T.c.value('./phone[1]', 'varchar(100)') AS phone
		,T.c.value('./fax[1]', 'varchar(100)') AS fax
		,T.c.value('./email[1]', 'varchar(100)') AS email
		,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)

	;with cte as 
	(
	 select  t1.*, ROW_NUMBER() over (partition by t1.mcod order by t1.D_BEGIN desc) as rn
	 from #F032 t1 
	 left join ies.T_F003_MO t2 on t1.MCOD = t2.MCOD 
	 where t2.MCOD is null
	)

	insert into [IES].[T_F003_MO]
			   ([TF_OKATO]
			   ,[MCOD]
			   ,[NAM_MOP]
			   ,[NAM_MOK]
			   ,[FAM_RUK]
			   ,[IM_RUK]
			   ,[OT_RUK]
			   ,[D_BEGIN]
			   ,[D_END]
			   ,[NAME_E]
			   ,[D_EDIT]
			   ,[IsActive]
			   ,[Holidays]
			   )

	select 
			   left(t1.OKTMO_P,5)  as [TF_OKATO]
			   ,t1.[MCOD]
			   ,t1.UIDMO --[NAM_MOP]
			   ,t1.IDMO--[NAM_MOK]
			   ,'' as [FAM_RUK]
			   ,'' as [IM_RUK]
			   ,'' as [OT_RUK]
			   ,t1.[D_BEGIN]
			   ,t1.[D_END]
			   ,t1.[NAME_E]
			   ,t1.[DATEBEG]
			   ,0 as [IsActive]
			   ,64 as [Holidays]

	from cte t1 
     where rn = 1


	UPDATE t2 
		set  t2.[SUBJ]					= t1.[SUBJ]
			,t2.[NAME_E]				= t1.[NAME_E]
			,t2.[DATEBEG]				= t1.[DATEBEG] -- добавил
			,t2.[DATEEND]				= t1.[DATEEND]
			,t2.[D_END]					= t1.[D_END]
			,t2.[MCOD]					= t1.[MCOD]
			,t2.[OKTMO_P]				= t1.[OKTMO_P]
			,t2.[SUBJ]					= t1.[SUBJ]
			,t2.[NAME_E]				= t1.[NAME_E]			 
			,t2.[osp]					= t1.[osp]
			,t2.[vid_mo]				= t1.[vid_mo]
			,t2.[oid_mo]				= t1.[oid_mo]
			,t2.[oid_spmo]				= t1.[oid_spmo]
			,t2.[nam_mop]				= t1.[nam_mop]
			,t2.[nam_mok]				= t1.[nam_mok]
			,t2.[inn]					= t1.[inn]
			,t2.[kpp]					= t1.[kpp]
			,t2.[ogrn]					= t1.[ogrn]
			,t2.[juraddress_index]		= t1.[juraddress_index]
			,t2.[juraddress_address]	= t1.[juraddress_address]
			,t2.[gar_address]			= t1.[gar_address]
			,t2.[okfs]					= t1.[okfs]
			,t2.[vedpri]				= t1.[vedpri]
			,t2.[phone]					= t1.[phone]
			,t2.[fax]					= t1.[fax]
			,t2.[email]					= t1.[email]
		from #F032 t1
		join [IES].[T_F032_TRMO] t2 on t1.UIDMO = t2.UIDMO and t1.D_BEGIN = t2.D_BEGIN
	

	INSERT INTO [IES].[T_F032_TRMO]
	           (F032TrmoID
			   ,[UIDMO]
	           ,[IDMO]
	           ,[MCOD]
	           ,[OKTMO_P]
	           ,[SUBJ]
	           ,[D_BEGIN]
	           ,[D_BEGIN_OMS]
	           ,[D_END]
	           ,[NAME_E]
	           ,[osp]
	           ,[vid_mo]
	           ,[oid_mo]
	           ,[oid_spmo]
	           ,[nam_mop]
	           ,[nam_mok]
	           ,[inn]
	           ,[kpp]
	           ,[ogrn]
	           ,[juraddress_index]
	           ,[juraddress_address]
	           ,[gar_address]
	           ,[okfs]
	           ,[vedpri]
	           ,[phone]
	           ,[fax]
	           ,[email]
	           ,[DATEBEG]
	           ,[DATEEND])
			  
		select 
				NEWID()
			   ,[UIDMO]
	           ,[IDMO]
	           ,[MCOD]
	           ,[OKTMO_P]
	           ,[SUBJ]
	           ,[D_BEGIN]
	           ,[D_BEGIN_OMS]
	           ,[D_END]
	           ,[NAME_E]
	           ,[osp]
	           ,[vid_mo]
	           ,[oid_mo]
	           ,[oid_spmo]
	           ,[nam_mop]
	           ,[nam_mok]
	           ,[inn]
	           ,[kpp]
	           ,[ogrn]
	           ,[juraddress_index]
	           ,[juraddress_address]
	           ,[gar_address]
	           ,[okfs]
	           ,[vedpri]
	           ,[phone]
	           ,[fax]
	           ,[email]
	           ,[DATEBEG]
	           ,[DATEEND]
	    from #F032 t1
		join ies.T_F003_MO f003 on (t1.MCOD = f003.MCOD)
		where not exists(select 1 from [IES].[T_F032_TRMO] t2 where  t1.UIDMO = t2.UIDMO and t1.D_BEGIN = t2.D_BEGIN)
	
END