 
 commit;
 
+/*
 --get duplicates for some reason 
 delete from concept_relationship_stage a where exists (select 1 from  (
-  select concept_code_1,concept_code_2,relationship_id, max (rowid) as rid from concept_relationship_stage group by concept_code_1,concept_code_2,relationship_id having count ( 1) >1 ) x 
+  select concept_code_1,concept_code_2,relationship_id, max(rowid) as rid from concept_relationship_stage group by concept_code_1,concept_code_2,relationship_id having count(1)>1) x 
   where  a.concept_code_1= x.concept_code_1 and a.concept_code_2= x.concept_code_2 and a.relationship_id =x.relationship_id and x.rid = a.rowid)
 ;  
+
 --6 Working with replacement mappings;
 BEGIN
    DEVV5.VOCABULARY_PACK.CheckReplacementMappings;

;
commit;

-- 5. and 6. Match d_combo, df, mf, but not bn - Marketed Products without Brand, quantified or boxed
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,

);
commit;

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  => 'qr_i', estimate_percent  => null, cascade  => true);

-- Break up mulit-i_combos and write back leaving all other patterns unchanged
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, prec
from (
  select 
    qi_combo, ri_combo, 
    x.df_code, x.df_id, x.dfg_id,
    x.bn_code, x.bn_id,
    x.prec
  from x_pattern x
  join ( -- break up qi_combo from actual combo code, there is no easy way of doing it similar to q_ds
    select qi_combo, trim(regexp_substr(qi_combo, '[^\-]+', 1, levels.column_value)) as qi_code
    from (select distinct qi_combo from x_pattern), -- extension_combo contains i_combos as well
    table(cast(multiset(select level from dual connect by level <= length (regexp_replace(qi_combo, '[^\-]+'))  + 1) as sys.OdciNumberList)) levels
  ) q using(qi_combo)
  join ( -- break up ri_combo
    select ri_combo, trim(regexp_substr(ri_combo, '[^\-]+', 1, levels.column_value)) as ri_code
    from (select distinct ri_combo from x_pattern), -- extension_combo contains i_combos as well
    table(cast(multiset(select level from dual connect by level <= length (regexp_replace(ri_combo, '[^\-]+'))  + 1) as sys.OdciNumberList)) levels
  ) r using(ri_combo)
  join qr_i using(qi_code, ri_code) -- get right component in a combination aligned
  where qd_combo is null -- only Form patterns
minus
  select qi_combo, ri_combo, df_code, df_id, dfg_id, bn_code, bn_id, prec
  from x_pattern
);
commit;

-- 14. Match i_combo, df but not bn, mf - Clinical 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, null as bn_code, null as bn_id, null as mf_code, null as mf_id, null as quant_unit, null as quant_unit_id, 14 as prec
