DROP PROCEDURE IF EXISTS sp_f019persaccorgload;
DROP FUNCTION IF EXISTS sp_f019persaccorgload;

CREATE PROCEDURE public.sp_f019persaccorgload("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$

	declare T xml;
	declare tempStrXml text;
	
	BEGIN
		
		select convert_from("Xml", 'WIN1251') into tempStrXml;
		
		select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T; 
		
		DROP TABLE IF EXISTS temp_f019;
		
		CREATE TEMP TABLE temp_f019 as 
		SELECT 
			ORG_TYPE::int,
			ORG_COD::int, 
			NAM_ORGP,
			NAM_ORGK,
			TF_COD,
			SMO_COD,
			CASE WHEN  D_BEGIN  = '' THEN NULL ELSE D_BEGIN::TIMESTAMP END, 
			CASE WHEN  D_END  = '' THEN NULL ELSE D_END::TIMESTAMP END,
			TF_OKATO
		FROM
		XMLTABLE ('/packet/zap' PASSING T 
		COLUMNS 
			ORG_TYPE text path './orgtype' null, 
			ORG_COD text path './orgcod' null, 
			NAM_ORGP text path './nam_orgp' null,
			NAM_ORGK text path './nam_orgk' null,
			TF_COD text path './tf_kod' null,
			SMO_COD text path './smocod' null,
			D_BEGIN text path './datebeg' null, 
			D_END text path './dateend' null, 
			TF_OKATO text path './tf_okato' null);
		
		update IES.T_F019_PERS_ACC_ORG t2
			set  ORG_TYPE=t1.ORG_TYPE
				,NAM_ORGP=t1.NAM_ORGP
				,NAM_ORGK=t1.NAM_ORGK
				,TF_COD=t1.TF_COD
				,SMO_COD=t1.SMO_COD
				,D_BEGIN=t1.D_BEGIN
				,D_END=t1.D_END
		from temp_f019 t1
		where t1.TF_OKATO=t2.TF_OKATO and t1.ORG_COD = t2.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 
			gen_random_uuid(),
			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 (t1.TF_OKATO=t2.TF_OKATO and t2.ORG_COD = t1.ORG_COD)
		where t2.F019PersAccOrgID is null;
		
		DROP TABLE IF EXISTS temp_f019;
		
	End
$BODY$;