 
 -- Create products
 create table drug_concept_stage (
+  domain_id varchar2(20),
   concept_name varchar2(255),
   vocabulary_id varchar2(20),
   concept_class_id varchar2(20),
-  standard_concept varchar2(1),
   concept_code varchar2(255), -- need a long one because Ingredient and Dose Form string used as concept_code
   possible_excipient varchar2(1),
   valid_start_date date,

-- Patches
insert /*+ APPEND */ into drug_concept_stage_tmp
select 
  '' as concept_name, 'HCPCS' as vocabulary_id, 'Ingredient' as concept_class_id, null as standard_concept, regexp_replace(regexp_replace(lower(concept_name), '(.+?),? ?(per )?patch.*', '\1'), '\d+(%| ?mg)', '') as concept_code, 
  null as possible_excipient, null as valid_start_date, null as valid_end_date, null as invalid_reason, null as dose_form
from drug_concept_stage where dose_form='Patch' 
;

-- Instillations
insert /*+ APPEND */ into drug_concept_stage_tmp
select 
  '' as concept_name, 'Drug' as domain_id, 'HCPCS' as vocabulary_id, 'Ingredient' as concept_class_id, regexp_replace(lower(concept_name), '(.+?),? ?per instillation.*', '\1') as concept_code, 
  null as possible_excipient, null as valid_start_date, null as valid_end_date, null as invalid_reason, null as dose_form
from drug_concept_stage where dose_form='Instillation' 
;
