declare @EhrEnt int, @Vers uniqueidentifier
select @EhrEnt = ee.EntityID from VCLib.T_ENTITIES ee where ee.EntName = 'EHR_RUS_M00002' 
select top 1 @Vers = reff.EhrRusRefBooksID from EHR.T_EHR_RUS_REF_BOOKS reff
where reff.Entities = @EhrEnt
order by Cast(SUBSTRING(reff.CurrVersion, 0 , CharIndex('.', reff.CurrVersion, 0)) as int) desc, cast(SUBSTRING(reff.CurrVersion, CharIndex('.', reff.CurrVersion, 0)+1 , len(reff.CurrVersion) - CharIndex('.', reff.CurrVersion, 0)) as int) desc

select atc.Code as atc, (select top 1 ehr.EhrRusID
						from EHR.T_EHR_RUS ehr 
						join EHR.T_EHR_RUS_M00002 a on a.EhrRusID = ehr.EhrRusID
						where ehr.CodeEHR = atc.Code and ehr.EhrRusRefBooks = @Vers
									) as newAtc
INTO #ATC
from PHARM.T_ATC atc

--select * from #ATC
--where newAtc is null ???????????

--T_C_TRN
ALTER TABLE [PHARM].[T_C_TRN] DROP CONSTRAINT [FK_T_ATC_T_C_TRN_Atc]
GO
EXEC sp_rename 'PHARM.T_C_TRN.Atc', 'AtcOld', 'COLUMN';
GO

ALTER TABLE [PHARM].[T_C_TRN] ADD [Atc] uniqueidentifier NULL
GO

update trn
set trn.Atc = tt.newAtc
from PHARM.T_C_TRN trn
join #ATC tt on tt.atc = trn.AtcOld
where trn.Atc is not null

ALTER TABLE [PHARM].[T_C_TRN] ADD CONSTRAINT [FK_T_EHR_RUS_M00002_T_C_TRN_Atc]
FOREIGN KEY([Atc]) REFERENCES [EHR].[T_EHR_RUS_M00002] ([EhrRusID])
GO

ALTER TABLE [PHARM].[T_C_TRN] DROP COLUMN [AtcOld]
GO

--T_ATC_LINK_TRN
ALTER TABLE [PHARM].[T_ATC_LINK_TRN] DROP CONSTRAINT [FK_T_ATC_T_ATC_LINK_TRN_Atc]
GO

EXEC sp_rename 'PHARM.T_ATC_LINK_TRN.Atc', 'AtcOld', 'COLUMN';
GO

ALTER TABLE [PHARM].[T_ATC_LINK_TRN] ADD [Atc] uniqueidentifier NULL
GO

update trn
set trn.Atc = tt.newAtc
from PHARM.T_ATC_LINK_TRN trn
join #ATC tt on tt.atc = trn.AtcOld
where trn.Atc is not null

ALTER TABLE [PHARM].[T_ATC_LINK_TRN] ALTER COLUMN [Atc] uniqueidentifier not null 
GO

ALTER TABLE [PHARM].[T_ATC_LINK_TRN] ADD CONSTRAINT [FK_T_EHR_RUS_M00002_T_ATC_LINK_TRN_Atc]
FOREIGN KEY([Atc]) REFERENCES [EHR].[T_EHR_RUS_M00002] ([EhrRusID])
GO

ALTER TABLE [PHARM].[T_ATC_LINK_TRN] DROP COLUMN [AtcOld]
GO


--T_CONTRAINDICATIONS
ALTER TABLE [PHARM].[T_CONTRAINDICATIONS] DROP CONSTRAINT [FK_T_ATC_T_CONTRAINDICATIONS_Atc]
GO

EXEC sp_rename 'PHARM.T_CONTRAINDICATIONS.Atc', 'AtcOld', 'COLUMN';
GO

ALTER TABLE [PHARM].[T_CONTRAINDICATIONS] ADD [Atc] uniqueidentifier NULL
GO

update trn
set trn.Atc = tt.newAtc
from PHARM.T_CONTRAINDICATIONS trn
join #ATC tt on tt.atc = trn.AtcOld
where trn.Atc is not null

ALTER TABLE [PHARM].[T_CONTRAINDICATIONS] ADD CONSTRAINT [FK_T_EHR_RUS_M00002_T_CONTRAINDICATIONS_Atc]
FOREIGN KEY([Atc]) REFERENCES [EHR].[T_EHR_RUS_M00002] ([EhrRusID])
GO

ALTER TABLE [PHARM].[T_CONTRAINDICATIONS] DROP COLUMN [AtcOld]
GO


--T_INDICATIONS
ALTER TABLE [PHARM].[T_INDICATIONS] DROP CONSTRAINT [FK_T_ATC_T_INDICATIONS_Atc]
GO

EXEC sp_rename 'PHARM.T_INDICATIONS.Atc', 'AtcOld', 'COLUMN';
GO

ALTER TABLE [PHARM].[T_INDICATIONS] ADD [Atc] uniqueidentifier NULL
GO

update trn
set trn.Atc = tt.newAtc
from PHARM.T_INDICATIONS trn
join #ATC tt on tt.atc = trn.AtcOld
where trn.Atc is not null

ALTER TABLE [PHARM].[T_INDICATIONS] ADD CONSTRAINT [FK_T_EHR_RUS_M00002_T_INDICATIONS_Atc]
FOREIGN KEY([Atc]) REFERENCES [EHR].[T_EHR_RUS_M00002] ([EhrRusID])
GO

ALTER TABLE [PHARM].[T_INDICATIONS] DROP COLUMN [AtcOld]
GO

drop table #ATC

drop table PHARM.T_ATC


select EntityID 
INTO #ENTITY
from VCLib.T_ENTITIES e
where e.EntName in ('ATC') and e.[Schema] = 'PHARM'

Delete col
From VCLib.T_USER_COLUMNS col
	Join VCLib.T_USER_CONTEXTS ctx On col.UserContextID = ctx.UserContextID 
	join #ENTITY e on  ctx.EntityID = e.EntityID

Delete ctx
From VCLib.T_USER_CONTEXTS ctx
	join #ENTITY e on  ctx.EntityID = e.EntityID

delete del 
from VCLib.T_DELETED_OBJECT del
	join #ENTITY e on  del.Entities = e.EntityID

delete fv
From VCLib.T_TEMPLATE_FIELD_VALUE fv
Join VCLib.T_TEMPLATE_FIELD fld On fv.TemplateField = fld.TemplateFieldID
	join #ENTITY e on  fld.Entity = e.EntityID

delete fld
From VCLib.T_TEMPLATE_FIELD fld
	join #ENTITY e on  fld.Entity = e.EntityID

delete rep
From VCLib.T_Report rep
	join #ENTITY e on  rep.EntityID = e.EntityID

DELETE us 
FROM VCLib.T_USER_SEARCHES  us
	join #ENTITY e on  us.EntityID = e.EntityID

delete e 
from VCLib.T_ENTITIES_ATTRIBUTES e
	join #ENTITY ee on  e.EntityID = ee.EntityID

delete e 
from VCLib.T_ENTITIES e
	join #ENTITY ee on  e.EntityID = ee.EntityID

drop table #ENTITY