DROP PROCEDURE IF EXISTS sp_o001oksmload;
DROP FUNCTION IF EXISTS sp_o001oksmload; 

CREATE PROCEDURE public.sp_o001oksmload ("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_o001;
	CREATE TEMP TABLE temp_o001 as
	SELECT 
			KOD, 
			NAME11, 
			ALFA2, 
			ALFA3, 
			NOMAKT, 
			STATUS, 
			CASE WHEN  DATEUTV  = '' THEN NULL ELSE DATEUTV::TIMESTAMP END,
			CASE WHEN  DATEVVED  = '' THEN NULL ELSE DATEVVED::TIMESTAMP END, 
			NOMDESCR, 
			NAME12 
	FROM
	XMLTABLE ('/packet/zap' PASSING T 
	COLUMNS 
			KOD text path './KOD' null, 
			NAME11 text path './NAME11' null, 
			ALFA2 text path './ALFA2' null, 
			ALFA3 text path './ALFA3' null, 
			NOMAKT text path './NOMAKT' null, 
			STATUS text path './STATUS' null, 
			DATEUTV text path './DATEUTV' null, 
			DATEVVED text path './DATEVVED' null, 
			NOMDESCR text path './NOMDESCR' null, 
			NAME12 text path './NAME12' null );

	update ies.T_O001_OKSM t1  
	set 
		NAME11 = t2.NAME11, 
		NAME12 = t2.NAME12,
		ALFA2  = t2.ALFA2,
		ALFA3  = t2.ALFA3,
		NOMDESCR=t2.NOMDESCR,
		NOMAKT = t2.NOMAKT,
		STATUS_Edit  = t2.STATUS,
		DATEUTV  = t2.DATEUTV,
		DATEVVED = t2.DATEVVED
	from temp_o001 t2 
	where t1.KOD = t2.KOD;

	insert into ies.T_O001_OKSM(KOD, NAME11, NAME12, ALFA2, ALFA3, NOMDESCR, NOMAKT, DATA_UPD, STATUS_Edit, DATEUTV, DATEVVED)
	select t2.KOD, t2.NAME11, t2.NAME12, t2.ALFA2, t2.ALFA3, t2.NOMDESCR, t2.NOMAKT,null,t2.STATUS,t2.DATEUTV,t2.DATEVVED
	from temp_o001 t2
	left join ies.T_O001_OKSM t1  on t1.KOD = t2.KOD 
	where t1.KOD is null;

	drop table if exists temp_o001;

	return;
END
$BODY$;

