 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)
+ where to_id not like '-%'; 
+
+insert into map_drug (from_code, to_id, map_order)
+select  distinct  cr.concept_code_1,first_value (r.concept_id) over (partition by cr.concept_code_1 order by rc2.precedence), '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.i_combo = e.ri_combo and r.d_combo = e.rd_combo 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
+  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)
+select  distinct  cr.concept_code_1,first_value (r.concept_id) over (partition by cr.concept_code_1 order by rc2.precedence), '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.i_combo = e.ri_combo and r.d_combo = e.rd_combo 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
+  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)
+select   distinct  cr.concept_code_1,first_value (r.concept_id) over (partition by cr.concept_code_1 order by rc2.precedence), '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.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 
+  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)
+select   distinct  cr.concept_code_1,first_value (r.concept_id) over (partition by cr.concept_code_1 order by rc2.precedence), '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.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
+  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)
+select  distinct  cr.concept_code_1,first_value (r.concept_id) over (partition by cr.concept_code_1 order by rc2.precedence), '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.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);
+  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)
-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
+select  distinct  cr.concept_code_1,first_value (r.concept_id) over (partition by cr.concept_code_1 order by rc2.precedence), '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.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 
+  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)
+select  distinct  cr.concept_code_1,first_value (r.concept_id) over (partition by cr.concept_code_1 order by rc2.precedence), '6'  -- Branded Drug Form
+from r_existing r
+  join ex e
+    on r.i_combo = e.ri_combo 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
+  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)
+select  distinct cr.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, 
first_value (c.concept_code) over (partition by from_code order by to_id),
dc.vocabulary_id, 
first_value (c.vocabulary_id) over (partition by from_code order by to_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')  

from e 
join r_existing r
on r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and concept_class_id = 'Clinical Drug Comp')
select distinct cr.concept_code_1, r_concept_id, '9' -- Clinical Drug Comp
from r join e using (concept_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)
and cnt_2=cnt+1 -- take only those where components counts are equal
; 

