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

CREATE PROCEDURE public.sp_v042abortslpayreasonload("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_abortslpayreason_load;

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