DROP PROCEDURE IF EXISTS public.sp_v040applplaceload;
DROP FUNCTION IF EXISTS public.sp_v040applplaceload;

CREATE PROCEDURE public.sp_v040applplaceload("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
	
DECLARE 
    T xml;
    tempStrXml text;
BEGIN
	
	select convert_from("Xml", 'WIN1251') into tempStrXml;	
	select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T; 
	
	DROP TABLE IF EXISTS temp_applplace_load;

	CREATE TEMP TABLE temp_applplace_load AS
	SELECT 
		ID_MOP,
		NULLIF(N_MOP, '') AS N_MOP,
		DATEBEG :: timestamp,
		NULLIF(DATEEND, '') :: timestamp as DATEEND
	FROM XMLTABLE 
	(
		'/packet/zap' PASSING T 
		COLUMNS 
			ID_MOP text PATH './ID_MOP',
			N_MOP text PATH './N_MOP',
			DATEBEG text PATH './DATEBEG',
			DATEEND text PATH './DATEEND'
	);
	
	update ies.t_v040_appl_place t2
	set N_MOP = t1.N_MOP
		,DATEEND = t1.DATEEND
	from temp_applplace_load t1
	where t1.ID_MOP = t2.ID_MOP and t1.DATEBEG = t2.DATEBEG;
	
	insert into ies.t_v040_appl_place (v040applplaceid, ID_MOP, N_MOP, DATEBEG, DATEEND)
	select gen_random_uuid(), t1.ID_MOP, t1.N_MOP, t1.DATEBEG, t1.DATEEND
	from temp_applplace_load t1
	left join ies.t_v040_appl_place t2 on (t1.ID_MOP = t2.ID_MOP and t1.DATEBEG = t2.DATEBEG)
	where t2.v040applplaceid is null;
	
	DROP TABLE IF EXISTS temp_applplace_load;
	
	RETURN;
END
$BODY$;