-- Tables

/***** Object:  Table [AKUZ].[T_CDA_COMMERCIAL_RECIPE] *****/
CREATE TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE]
(
    [CdaCommercialRecipeID] uniqueidentifier NOT NULL,
    [EhrRusS0JLHGTLBJ] uniqueidentifier NULL,
    [EhrRusD4U765T5TH] uniqueidentifier NULL,
    [EhrRus9a9g09e8kl] uniqueidentifier NULL,
    [EhrRusS0JLHGTLBJ_1] uniqueidentifier NULL,
    [EhrRus6kiqts8t99] uniqueidentifier NULL,
    [EhrRusS0JLHGTLBJ_2] uniqueidentifier NOT NULL,
    [ConsumeInstruction] varchar(255) NULL,
    [ConsumingDuration] varchar(255) NULL,
    [NumberOfDoses] int NOT NULL,
    [FrequencyOfReception] decimal(12, 2) NULL,
    [DosePerConsume] decimal(12, 2) NULL,
    [InstitutionSpecified] bit NULL,
    [CdaSectionCommercialRecipe] uniqueidentifier NULL,
    [EhrRusJe1hcqeueh] uniqueidentifier NULL,
    [EhrRusS0JLHGTLBJ_3] uniqueidentifier NULL,
    [FrequencyOfDispensing] int NULL,
    [NumberOfDispence] int NULL,
    [EhrRusS0JLHGTLBJ_4] uniqueidentifier NULL,
    [InstitutionSpectifiedForDispencing] bit NULL,
    [EhrRus575VSF88OG] uniqueidentifier NULL
)
ON [PRIMARY]
GO

-- Alters

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [PK_T_CDA_COMMERCIAL_RECIPE]
PRIMARY KEY CLUSTERED ([CdaCommercialRecipeID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_COMMERCIAL_RECIPE_EhrRusS0JLHGTLBJ]
FOREIGN KEY([EhrRusS0JLHGTLBJ]) REFERENCES [EHR].[T_EHR_RUS_S0JLHGTLBJ] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_D4U765T5TH_T_CDA_COMMERCIAL_RECIPE_EhrRusD4U765T5TH]
FOREIGN KEY([EhrRusD4U765T5TH]) REFERENCES [EHR].[T_EHR_RUS_D4U765T5TH] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_9A9G09E8KL_T_CDA_COMMERCIAL_RECIPE_EhrRus9a9g09e8kl]
FOREIGN KEY([EhrRus9a9g09e8kl]) REFERENCES [EHR].[T_EHR_RUS_9A9G09E8KL] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_COMMERCIAL_RECIPE_EhrRusS0JLHGTLBJ_1]
FOREIGN KEY([EhrRusS0JLHGTLBJ_1]) REFERENCES [EHR].[T_EHR_RUS_S0JLHGTLBJ] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_6KIQTS8T99_T_CDA_COMMERCIAL_RECIPE_EhrRus6kiqts8t99]
FOREIGN KEY([EhrRus6kiqts8t99]) REFERENCES [EHR].[T_EHR_RUS_6KIQTS8T99] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_COMMERCIAL_RECIPE_EhrRusS0JLHGTLBJ_2]
FOREIGN KEY([EhrRusS0JLHGTLBJ_2]) REFERENCES [EHR].[T_EHR_RUS_S0JLHGTLBJ] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_CDA_SECTION_T_CDA_COMMERCIAL_RECIPE_CdaSectionCommercialRecipe]
FOREIGN KEY([CdaSectionCommercialRecipe]) REFERENCES [AKUZ].[T_CDA_SECTION] ([CdaSectionID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_JE1HCQEUEH_T_CDA_COMMERCIAL_RECIPE_EhrRusJe1hcqeueh]
FOREIGN KEY([EhrRusJe1hcqeueh]) REFERENCES [EHR].[T_EHR_RUS_JE1HCQEUEH] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_COMMERCIAL_RECIPE_EhrRusS0JLHGTLBJ_3]
FOREIGN KEY([EhrRusS0JLHGTLBJ_3]) REFERENCES [EHR].[T_EHR_RUS_S0JLHGTLBJ] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_COMMERCIAL_RECIPE_EhrRusS0JLHGTLBJ_4]
FOREIGN KEY([EhrRusS0JLHGTLBJ_4]) REFERENCES [EHR].[T_EHR_RUS_S0JLHGTLBJ] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_CDA_COMMERCIAL_RECIPE] ADD CONSTRAINT [FK_T_EHR_RUS_575VSF88OG_T_CDA_COMMERCIAL_RECIPE_EhrRus575VSF88OG]
FOREIGN KEY([EhrRus575VSF88OG]) REFERENCES [EHR].[T_EHR_RUS_575VSF88OG] ([EhrRusID])
GO

-------перенос данных
INSERT INTO AKUZ.T_CDA_COMMERCIAL_RECIPE (CdaCommercialRecipeID, EhrRusS0JLHGTLBJ, EhrRusD4U765T5TH, EhrRus9a9g09e8kl, EhrRusS0JLHGTLBJ_1, EhrRus6kiqts8t99, EhrRusS0JLHGTLBJ_2, ConsumeInstruction, ConsumingDuration, NumberOfDoses, FrequencyOfReception, DosePerConsume, InstitutionSpecified,
CdaSectionCommercialRecipe, EhrRusJe1hcqeueh, EhrRusS0JLHGTLBJ_3, FrequencyOfDispensing, NumberOfDispence, EhrRusS0JLHGTLBJ_4, InstitutionSpectifiedForDispencing, EhrRus575VSF88OG) 
select newid(), section.EhrRusS0JLHGTLBJ, section.EhrRusD4U765T5TH, section.EhrRus9a9g09e8kl, section.EhrRusS0JLHGTLBJ_1, section.EhrRus6kiqts8t99, section.EhrRusS0JLHGTLBJ_2, section.ConsumeInstruction, section.ConsumingDuration, section.NumberOfDoses, section.FrequencyOfReception,
section.DosePerConsume, section.InstitutionSpecified, section.CdaSectionCommercialRecipe, section.EhrRusJe1hcqeueh, section.EhrRusS0JLHGTLBJ_3, section.FrequencyOfDispensing, section.NumberOfDispence, section.EhrRusS0JLHGTLBJ_4, section.InstitutionSpectifiedForDispencing, section.EhrRus575VSF88OG
     from AKUZ.T_CDA_SECTION_RECIPE section where section.CdaSectionCommercialRecipe is not null
GO
--очищение элементов
delete from akuz.t_cda_section_recipe where CdaSectionCommercialRecipe is not null
GO

-------удаление парента на коммерческий рецепт в T_CDA_SECTION_RECIPE
drop index IX_T_CDA_SECTION_RECIPE_CdaSectionCommercialRecipe on [AKUZ].[T_CDA_SECTION_RECIPE]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_RECIPE] DROP CONSTRAINT [FK_T_CDA_SECTION_T_CDA_SECTION_RECIPE_CdaSectionCommercialRecipe]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_RECIPE] DROP COLUMN [CdaSectionCommercialRecipe]
GO


