 --newly generated concepts 
 --Brand Names
 insert into  drug_concept_stage (CONCEPT_NAME,DOMAIN_ID,VOCABULARY_ID,CONCEPT_CLASS_ID,STANDARD_CONCEPT,CONCEPT_CODE,VALID_START_DATE,VALID_END_DATE,INVALID_REASON, source_concept_class_id )
-select                           CONCEPT_NAME,'Drug', 'dm+d', 'Brand Name', '', concept_code, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'Brand Name' from br_name_list
+select  distinct  CONCEPT_NAME,'Drug', 'dm+d', 'Brand Name', '', concept_code, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'Brand Name' from br_name_list
 ;
 --NEW Ingredients
 insert into  drug_concept_stage (CONCEPT_NAME,DOMAIN_ID,VOCABULARY_ID,CONCEPT_CLASS_ID,STANDARD_CONCEPT,CONCEPT_CODE,VALID_START_DATE,VALID_END_DATE,INVALID_REASON, source_concept_class_id)
-select                           CONCEPT_NAME_1,'Drug', 'dm+d', 'Ingredient', 'S', concept_code_1, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'Ingredient'  from ingred_to_ingred_FINAL_BY_Lena where concept_code_1 like 'OMOP%'
+select distinct  CONCEPT_NAME_1,'Drug', 'dm+d', 'Ingredient', 'S', concept_code_1, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'Ingredient'  from ingr_to_ingr where concept_code_1 like 'OMOP%'
 ;
 --NEW Forms
 insert into  drug_concept_stage (CONCEPT_NAME,DOMAIN_ID,VOCABULARY_ID,CONCEPT_CLASS_ID,STANDARD_CONCEPT,CONCEPT_CODE,VALID_START_DATE,VALID_END_DATE,INVALID_REASON, source_concept_class_id)
- select distinct                          CONCEPT_NAME_2,'Drug', 'dm+d', 'Dose Form', '', concept_code_2, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'Form' from DR_TO_DOSE_form_full where concept_code_2 like 'OMOP%'
+ select distinct CONCEPT_NAME_2,'Drug', 'dm+d', 'Dose Form', '', concept_code_2, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'Form' from DR_TO_DOSE_form_full where concept_code_2 like 'OMOP%'
 ;
 --NEW Pack components 
 insert into  drug_concept_stage (CONCEPT_NAME,DOMAIN_ID,VOCABULARY_ID,CONCEPT_CLASS_ID,STANDARD_CONCEPT,CONCEPT_CODE,VALID_START_DATE,VALID_END_DATE,INVALID_REASON, source_concept_class_id)
- select distinct                          DRUG_NEW_NAME,'Drug', 'dm+d', 'Drug Product', 'S', DRUG_CODE, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'VMP' from PACK_DRUG_TO_CODE_2_2  where DRUG_CODE like 'OMOP%'
-;
---modify classes for Packs
-update drug_concept_stage set concept_class_id = 'Drug Pack' where concept_code  in (select PACK_CONCEPT_CODE from pc_stage )
+ select distinct  DRUG_NEW_NAME,'Drug', 'dm+d', 'Drug Product', 'S', DRUG_CODE, TO_DATE ('19700101', 'yyyymmdd'), TO_DATE ('20991231', 'yyyymmdd'), '', 'VMP' from PACK_DRUG_TO_CODE_2_2  where DRUG_CODE like 'OMOP%'
 ;
 --add units 
 insert into  drug_concept_stage (CONCEPT_NAME,DOMAIN_ID,VOCABULARY_ID,CONCEPT_CLASS_ID,STANDARD_CONCEPT,CONCEPT_CODE,VALID_START_DATE,VALID_END_DATE,INVALID_REASON, source_concept_class_id)

;
commit
;
--to do
-- replace new OMOPs with existing
-- add mappings to new RxE
-- formalize manaul table
-- change ppm to 0.001 of %
-- links to deprecated ATCs and ingredients 

create table r_to_c as 
  select r.* from relationship_to_concept r join concept on concept_id=r.concept_id_2 and vocabulary_id in ('RxNorm', 'RxNorm Extension', 'UCUM') where r.concept_code_1 is not null

;
/*
--work with manual table 
drop table ingr_to_ingr;
create table ingr_to_ingr (CONCEPT_CODE_1	varchar (200),
CONCEPT_NAME_1 varchar (200),	INSERT_ID_1 int,  CONCEPT_CODE_2 varchar (200),	CONCEPT_NAME_2 varchar (200),	INSERT_ID_2 int , REL_TYPE varchar (20),	INVALID_REASON varchar (20))
;
WbImport -file=C:/mappings/DM+D/ingred_to_ingred_FIN_Lena.txt
         -type=text
         -table=ingr_to_ingr
         -encoding="ISO-8859-15"
         -header=true
         -decode=false
