    and cr.relationship_id = 'Maps to'
    and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
    and cr.vocabulary_id_2 = 'RxNorm Extension'
- where concept_code_1 not in (select from_code from map_drug);
+ where cr.concept_code_1 not in (select from_code from map_drug);
  
  insert into map_drug (from_code, to_id, map_order)
-select  distinct concept_code_1, r.concept_id, '8'  -- Clinical Drug Form
-from r_existing r
-  join ex e
-    on r.i_combo = e.ri_combo and r.df_id = e.df_id 
+select  distinct  cr.concept_code_1,first_value (r.concept_id) over (partition by cr.concept_code_1 order by rc2.precedence), '8'  -- Clinical Drug Form
+from r_existing r
+  join ex e
+    on r.i_combo = e.ri_combo
    and trim(r.d_combo) is null 
    and r.bn_id = 0 and r.quant_value = 0 and r.quant_unit_id = 0 and r.bs = 0 and r.mf_id=0 
    and e.concept_id like '-%'
-  join concept_relationship_stage cr
-    on cr.concept_code_2 = e.concept_code
-   and cr.relationship_id = 'Maps to'
-   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
-   and cr.vocabulary_id_2 = 'RxNorm Extension'
- where concept_code_1 not in (select from_code from map_drug);
+  join relationship_to_concept rc  on rc.concept_id_2 = e.df_id
+  join relationship_to_concept rc2 on rc.concept_code_1 = rc2.concept_code_1 and rc2.concept_id_2 = r.df_id   
+  join concept_relationship_stage cr
+    on cr.concept_code_2 = e.concept_code
+   and cr.relationship_id = 'Maps to'
+   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
+   and cr.vocabulary_id_2 = 'RxNorm Extension'
+ where cr.concept_code_1 not in (select from_code from map_drug);
  
 insert into map_drug (from_code, to_id, map_order)
 with e as (

create table map_drug as
select from_code, to_id, '00' as map_order
 from maps_to 
 where to_id not like '-%';
 
insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '1'    -- Map Marketed Form to corresponding Branded/Clinical Drug (save box size and quant factor)
from r_existing r
  join ex e
    on r.quant_value = e.r_value and r.quant_unit_id = e.quant_unit_id
   and r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and r.df_id = e.df_id and r.bn_id = e.bn_id and r.bs = e.bs
   and r.mf_id=0
   and e.concept_id like '-%'
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
  where concept_code_1 not in (select from_code from map_drug);
 
insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '2'    -- Kick box size out
from r_existing r
  join ex e
    on r.quant_value = e.r_value and r.quant_unit_id = e.quant_unit_id
   and r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and r.df_id = e.df_id and r.bn_id = e.bn_id
   and r.bs = 0 and r.mf_id=0
   and e.concept_id like '-%'
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where concept_code_1 not in (select from_code from map_drug);

insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '3'  -- Kick Quant factor out
from r_existing r
  join ex e
    on r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and r.df_id = e.df_id and r.bn_id = e.bn_id
   and r.quant_value = 0 and r.quant_unit_id = 0 and r.bs = 0 and r.mf_id=0
   and e.concept_id like '-%'
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where concept_code_1 not in (select from_code from map_drug);
 
insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '3'  -- Kick BN out, save Quant factor 
from r_existing r
  join ex e
    on r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and r.df_id = e.df_id 
   and r.quant_value = e.r_value and r.quant_unit_id = e.quant_unit_id and r.bs = 0 and r.mf_id=0 and r.bn_id = 0
   and e.concept_id like '-%'
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where concept_code_1 not in (select from_code from map_drug);

insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '4'  -- Map Branded Drug to corresponding Clinical Drug (save box size)
from r_existing r
  join ex e
    on r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and r.df_id = e.df_id and r.bs = e.bs
   and r.bn_id = 0  and r.quant_value = 0 and r.quant_unit_id = 0 and r.mf_id=0
   and e.concept_id like '-%'
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where concept_code_1 not in (select from_code from map_drug);
 
 insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '5'  -- Map Branded Drug to corresponding Clinical Drug
from r_existing r
  join ex e
    on r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and r.df_id = e.df_id
   and r.bn_id = 0  and r.quant_value = 0 and r.quant_unit_id = 0 and r.bs = 0 and r.mf_id=0
   and e.concept_id like '-%'
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where concept_code_1 not in (select from_code from map_drug);

insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '6'  -- Branded Drug Form
from r_existing r
  join ex e
    on r.i_combo = e.ri_combo and r.df_id = e.df_id and r.bn_id = e.bn_id 
   and trim(r.d_combo) is null -- was ' ' in r_existing.d_combo
   and r.quant_value = 0 and r.quant_unit_id = 0 and r.bs = 0 and r.mf_id=0 
   and e.concept_id like '-%'
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where concept_code_1 not in (select from_code from map_drug);

insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '7'  -- Branded Drug Com
from r_existing r
  join ex e
    on r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and r.bn_id = e.bn_id

truncate table drug_strength_stage;

insert into concept_relationship_stage (concept_code_1,concept_code_2,vocabulary_id_1,vocabulary_id_2,relationship_id,valid_start_date,valid_end_date)
select distinct from_code, c.concept_code, dc.vocabulary_id, c.vocabulary_id,
case when dc.concept_class_id in ('Ingredient','Brand Name','Suppier','Dose Form') then 'Source - RxNorm eq'
     else 'Maps to' end,
sysdate,to_date ('20991231', 'yyyymmdd')  
