if object_id('SP_EHR_REPLACEMENT_FILL', 'P') is not null
	drop procedure SP_EHR_REPLACEMENT_FILL
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE SP_EHR_REPLACEMENT_FILL
	@oldVersion varchar(8)
,	@newVersion varchar(8)
,	@code varchar(64)
,	@parent uniqueidentifier
AS 
BEGIN
SET XACT_ABORT, NOCOUNT ON

DECLARE @OldEhrRusRefBooks VARCHAR(40) = (SELECT TOP 1 rb.EhrRusRefBooksID FROM EHR.T_EHR_RUS_REF_BOOKS rb WHERE rb.Code = @code AND rb.CurrVersion = @oldVersion)
DECLARE @NewEhrRusRefBooks VARCHAR(40) = (SELECT TOP 1 rb1.EhrRusRefBooksID FROM EHR.T_EHR_RUS_REF_BOOKS rb1 WHERE rb1.Code = @code AND rb1.CurrVersion = @newVersion)
DECLARE @EntityID int = (SELECT erfb.Entities FROM EHR.T_EHR_RUS_REF_BOOKS erfb WHERE erfb.EhrRusRefBooksID = @OldEhrRusRefBooks)
DECLARE @NameEHR VARCHAR(132) = (SELECT e.[Schema] + '.T_' + e.EntName FROM VCLib.T_ENTITIES e WHERE e.EntityID = @EntityID)
DECLARE @conditionCode VARCHAR(200)
IF EXISTS (SELECT TOP 1 MappingName FROM VCLib.T_ENTITIES_ATTRIBUTES ea WHERE EntityID = @EntityID And MappingName = 'IdEHR')
	SET @conditionCode = 'CAST(e1.IdEHR as nvarchar(40)) = CAST(e2.IdEHR as nvarchar(40))'
ELSE IF EXISTS (SELECT TOP 1 MappingName FROM VCLib.T_ENTITIES_ATTRIBUTES ea WHERE EntityID = @EntityID And MappingName = 'CodeEHR')
	SET @conditionCode = 'e1.CodeEHR = e2.CodeEHR'	

declare @tsql nvarchar(max)

set @tsql = N'INSERT INTO  EHR.T_ELEMENT_EHR_REPLACEMENT (ElementEhrReplacementID, EhrReplacement, OldEhr, NewEhr)
(SELECT NEWID(),  ''' + CAST(@parent as nvarchar(40)) + N''', e1.EhrRusID, e2.EhrRusID FROM ' + @NameEHR + N' e1
LEFT JOIN ' + @NameEHR + N' e2 
ON ' + @conditionCode + ' AND e2.EhrRusRefBooks = ''' + @NewEhrRusRefBooks + N'''
WHERE e1.EhrRusRefBooks = ''' + @OldEhrRusRefBooks + N''')
UPDATE EHR.T_ELEMENT_EHR_REPLACEMENT
SET AutomaticallyFilled = 1
WHERE NewEhr IS NOT NULL  And EhrReplacement = ''' + CAST(@parent as nvarchar(40)) + N''' '
--print @tsql;
exec sp_executesql @tsql;

return;

end