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

  SELECT 
  T.c.value('./orgtype[1]', 'int') AS ORG_TYPE
  ,T.c.value('./orgcod[1]','int') as ORG_COD
  ,T.c.value('./nam_orgp[1]', 'varchar(250)') AS NAM_ORGP
  ,T.c.value('./nam_orgk[1]', 'varchar(250)') AS NAM_ORGK
  ,T.c.value('./tf_kod[1]', 'varchar(2)') AS TF_COD
  ,T.c.value('./smocod[1]', 'varchar(5)') AS SMO_COD  
  ,case when T.c.value('./datebeg[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./datebeg[1]', 'varchar(20)'),103) end AS D_BEGIN
  ,case when T.c.value('./dateend[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./dateend[1]', 'varchar(20)'),103) end AS D_END
  ,T.c.value('./tf_okato[1]', 'varchar(5)') AS TF_OKATO
  into #temp_f019
  FROM   @Xml.nodes('/packet/zap') T(c)

UPDATE t1
	SET [ORG_TYPE] = t2.[ORG_TYPE]
		,[NAM_ORGP] = t2.[NAM_ORGP]
		,[NAM_ORGK] = t2.[NAM_ORGK]
		,[TF_COD] = t2.[TF_COD]
		,[SMO_COD] = t2.[SMO_COD]
		,[D_BEGIN] = t2.[D_BEGIN]
		,[D_END] = t2.[D_END]
FROM [IES].T_F019_PERS_ACC_ORG t1
join #temp_f019 t2 on t2.TF_OKATO = t1.TF_OKATO and t2.ORG_COD = t1.ORG_COD

INSERT INTO [IES].[T_F019_PERS_ACC_ORG]
			([F019PersAccOrgID]
			,[ORG_TYPE]
			,[ORG_COD] 
			,[NAM_ORGP]
			,[NAM_ORGK]
			,[TF_COD] 
			,[SMO_COD] 
			,[D_BEGIN]
			,[D_END] 
			,[TF_OKATO])
	select
			NEWID()
			,t1.[ORG_TYPE]
			,t1.[ORG_COD] 
			,t1.[NAM_ORGP]
			,t1.[NAM_ORGK]
			,t1.[TF_COD] 
			,t1.[SMO_COD] 
			,t1.[D_BEGIN]
			,t1.[D_END] 
			,t1.[TF_OKATO]
FROM #temp_f019 t1
left join [IES].T_F019_PERS_ACC_ORG t2 on t2.TF_OKATO = t1.TF_OKATO and t2.ORG_COD = t1.ORG_COD
where t2.F019PersAccOrgID is null

drop table #temp_f019

END