 ;
 commit;
 
+-- Break up multi-combos
+insert /*+ APPEND */ into x_pattern
+select 
+  qi_combo, ri_combo, qd_combo, rd_combo, df_code, df_id, dfg_id, null as bn_code, null as bn_id, mf_code, mf_id, quant_unit, quant_unit_id, prec
+from (
+  select distinct 
+    q.qi_combo, r.ri_combo, 
+    cast(q_ds as varchar2(20)) as qd_combo, cast(r_ds as varchar2(20)) as rd_combo, 
+    x.df_code, x.df_id, x.dfg_id,
+    x.mf_code, x.mf_id, 
+    x.quant_unit, x.quant_unit_id,
+    prec -- as already exists
+  from ( -- break up all combos for q
+    select distinct d_combo as qd_combo, i_code as qi_combo, ds_code as q_ds from q_ds join q_combo using(concept_code) where d_combo like '%-%'
+  ) q
+  join x_pattern x using(qd_combo) -- find them in the existing complete p to existing r 
+  join ( -- break up all combos for r
+    select distinct d_combo as rd_combo, i_code as ri_combo, ds_code as r_ds from r_ds join r_combo using(concept_id) where d_combo like '%-%'
+  ) r using(rd_combo) 
+  join qr_uds using(q_ds, r_ds) -- get right component in a combination aligned
+minus
+  select qi_combo, ri_combo, qd_combo, rd_combo, df_code, df_id, dfg_id, mf_code, mf_id, quant_unit, quant_unit_id, prec
+  from x_pattern
+);
+commit;
+
 -- the next one needs some tidying up first
 exec DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname  => 'x_pattern', estimate_percent  => null, cascade  => true);
+exec DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname  => 'existing_q', estimate_percent  => null, cascade  => true);
+exec DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname  => 'existing_r', estimate_percent  => null, cascade  => true);
 
 -- 7. and 8. Match d_combo, df, but not bn, mf - Clinical Drug, quantified or boxed
 insert /*+ APPEND */ into x_pattern

commit;

-- Break up multi-combos and write back leaving all other patterns unchanged
-- This is necessary for Clinical Drug Comps where comobos only exist in multi-versions in both q and r
insert /*+ APPEND */ into x_pattern
select distinct 
  q.qi_combo, r.ri_combo, 
  cast(q_ds as varchar2(20)) as qd_combo, cast(r_ds as varchar2(20)) as rd_combo, 
  x.df_code, x.df_id, dfg.dfg_id,
  x.bn_code, x.bn_id, 
  x.mf_code, x.mf_id,
  x.quant_unit, x.quant_unit_id,
  prec -- as already exists
from ( -- break up all combos for q
  select distinct d_combo as qd_combo, i_code as qi_combo, ds_code as q_ds from q_ds join q_combo using(concept_code) where d_combo like '%-%'
) q
join x_pattern x using(qd_combo) -- find them in the existing complete p to existing r 
join ( -- break up all combos for r
  select distinct d_combo as rd_combo, i_code as ri_combo, ds_code as r_ds from r_ds join r_combo using(concept_id) where d_combo like '%-%'
) r using(rd_combo) 
join qr_uds using(q_ds, r_ds) -- get right component in a combination aligned
join dfg on dfg.df_id=x.df_id
where not exists ( -- Check we don't already have that dosage covered
  select 1 from x_pattern e where e.qd_combo=cast(q_ds as varchar2(20)) and e.rd_combo=cast(r_ds as varchar2(20))
);
commit;


;
commit;

-- Break up multi-combos
insert /*+ APPEND */ into x_pattern
select 
  qi_combo, ri_combo, qd_combo, rd_combo, df_code, df_id, dfg_id, null as bn_code, null as bn_id, null as mf_code, null as mf_id, quant_unit, quant_unit_id, prec
from (
  select distinct 
    q.qi_combo, r.ri_combo, 
    cast(q_ds as varchar2(20)) as qd_combo, cast(r_ds as varchar2(20)) as rd_combo, 
    x.df_code, x.df_id, x.dfg_id,
    x.quant_unit, x.quant_unit_id,
    prec -- as already exists
  from ( -- break up all combos for q
    select distinct d_combo as qd_combo, i_code as qi_combo, ds_code as q_ds from q_ds join q_combo using(concept_code) where d_combo like '%-%'
  ) q
  join x_pattern x using(qd_combo) -- find them in the existing complete p to existing r 
  join ( -- break up all combos for r
    select distinct d_combo as rd_combo, i_code as ri_combo, ds_code as r_ds from r_ds join r_combo using(concept_id) where d_combo like '%-%'
  ) r using(rd_combo) 
  join qr_uds using(q_ds, r_ds) -- get right component in a combination aligned
minus
  select qi_combo, ri_combo, qd_combo, rd_combo, df_code, df_id, dfg_id, quant_unit, quant_unit_id, prec
  from x_pattern
);
commit;

-- 9. and 10. Match d_combo, bn, but not df, mf - Branded Component
insert /*+ APPEND */ into x_pattern
select qi_combo, ri_combo, qd_combo, rd_combo, null as df_code, null as df_id, null as dfg_id, bn_code, bn_id, null as mf_code, null as mf_id, quant_unit, quant_unit_id,
