 ;
 commit;
 
--- 3. and 4. Match d_combo, df, bn, but not mf - Branded Drug, quantified and boxed
+-- 3-6. Match d_combo, df, bn, but not mf - Branded Drug, quantified and boxed
 insert /*+ APPEND */ into x_pattern
 -- take out null values from union for performance
 select qi_combo, ri_combo, qd_combo, rd_combo, df_code, df_id, dfg_id, bn_code, bn_id, null as mf_code, null as mf_id, quant_unit, quant_unit_id,
   case -- if the translation keeps the favorite quant_unit_id give it a better prec 
-    when precedence is null then 3-- solid drug
-    when precedence=1 then 3 -- quant_unit and quant_unit_id match according to prec
-  else 4 end as prec
+    when precedence is null then 3+new_rec-- solid drug
+    when precedence=1 then 3+new_rec -- quant_unit and quant_unit_id match according to prec
+  else 5+new_rec end as prec -- 5 if quant doesn't match
 from (
   select
     qi_combo, first_value(ri_combo) over (partition by qd_combo, df_code, bn_code order by bn_prec, df_prec, div desc, i_prec, u_prec) as ri_combo,     

;
commit;

-- 13. Match i_combo, df, bn but no mf - Branded Forms
insert /*+ APPEND */ into x_pattern
select qi_combo, ri_combo, null as qd_combo, null as rd_combo, df_code, df_id, dfg_id, bn_code, bn_id, null as mf_code, null as mf_id, null as quant_unit, null as quant_unit_id, 13 as prec
from (
  select distinct 
    qi_combo, first_value(ri_combo) over (partition by qi_combo, df_code, bn_code order by bn_prec, df_prec, i_prec) as ri_combo,     
    df_code, first_value(df_id) over (partition by qi_combo, df_code, bn_code order by bn_prec, df_prec, i_prec) as df_id, 
      first_value(dfg_id) over (partition by qi_combo, df_code, bn_code order by bn_prec, df_prec, i_prec) as dfg_id, 
    bn_code, first_value(bn_id) over (partition by qi_combo, bn_code, bn_code order by bn_prec, df_prec, i_prec) as bn_id
  from ( -- create q_existing with all attributes extended to their r-corridors
    select 
      eq.i_combo as qi_combo, c.ri_combo, c.i_prec, 

);
commit;

-- 27-28. If nothing works and no patterns exist, add the best translations (usually Clinical Drug Comps with no descendants)
-- Pick the best translation for each qd_combo and quant_unit_id, so there is a choice when combining in extension_combo
insert /*+ APPEND */ into x_pattern
select q.*,
  case -- if the translation keeps the favorite quant_unit_id give it a better prec 
    when precedence is null then 27 -- solid drug
    when precedence=1 then 27 -- quant_unit and quant_unit_id match according to prec
  else 28 end as prec
from (
  select distinct 
    qi_combo, first_value(ri_combo) over (partition by qd_combo, quant_unit_id order by div desc, i_prec, u_prec) as ri_combo,     
