CREATE OR REPLACE FUNCTION SP_EHR_REPLACEMENT_FILL
(
	"oldVersion" varchar(8)
,	"newVersion" varchar(8)
,	"code" varchar(64)
,	"parent" uuid
)
RETURNS void
LANGUAGE 'plpgsql'
VOLATILE 
AS $BODY$

DECLARE 
	"OldEhrRusRefBooks" VARCHAR(40) := (SELECT rb.EhrRusRefBooksID FROM EHR.T_EHR_RUS_REF_BOOKS rb WHERE rb.Code = "code" AND rb.CurrVersion = "oldVersion" limit 1);
	"NewEhrRusRefBooks" VARCHAR(40) := (SELECT rb1.EhrRusRefBooksID FROM EHR.T_EHR_RUS_REF_BOOKS rb1 WHERE rb1.Code = "code" AND rb1.CurrVersion = "newVersion" limit 1);
	"EntityID" integer := (SELECT erfb.Entities FROM EHR.T_EHR_RUS_REF_BOOKS erfb WHERE erfb.EhrRusRefBooksID = "OldEhrRusRefBooks");
	"NameEHR" VARCHAR(132) := (SELECT e.[Schema] + '.T_' + e.EntName FROM VCLib.T_ENTITIES e WHERE e.EntityID = "EntityID");
	"conditionCode" VARCHAR(200);
IF EXISTS (SELECT MappingName FROM VCLib.T_ENTITIES_ATTRIBUTES ea WHERE EntityID = "EntityID" And MappingName = 'IdEHR' limit 1) then
	"conditionCode" := 'CAST(e1.IdEHR as nvarchar(40)) = CAST(e2.IdEHR as nvarchar(40))';
ELSIF EXISTS (SELECT MappingName FROM VCLib.T_ENTITIES_ATTRIBUTES ea WHERE EntityID = "EntityID" And MappingName = 'CodeEHR' limit 1) then
	"conditionCode" := 'e1.CodeEHR = e2.CodeEHR';
end if;


declare _tsql text;
Begin
	_tsql := 'INSERT INTO  EHR.T_ELEMENT_EHR_REPLACEMENT (ElementEhrReplacementID, EhrReplacement, OldEhr, NewEhr)
	SELECT uuid_generate_v4(),  ''' || "parent"::varchar(40) || ''', e1.EhrRusID, e2.EhrRusID FROM ' || "NameEHR" || ' e1
	LEFT JOIN ' || "NameEHR" || ' e2 
	ON ' || "conditionCode" || ' 
		AND e2.EhrRusRefBooks = ''' || "NewEhrRusRefBooks" || '''
	WHERE e1.EhrRusRefBooks = ''' || "OldEhrRusRefBooks" || ''';
	UPDATE EHR.T_ELEMENT_EHR_REPLACEMENT
	SET AutomaticallyFilled = true
	WHERE NewEhr IS NOT NULL And EhrReplacement = ''' || "parent"::varchar(40) || ''' ;';

	--RAISE NOTICE '%', _tsql;
	EXECUTE _tsql;		
End;
$BODY$;
ALTER FUNCTION SP_EHR_REPLACEMENT_FILL(varchar(8), varchar(8), varchar(64), uuid) OWNER TO postgres;

