 * 7. Write RxNorm Extension *
 ****************************/
 
--- Temporary till q_to_r is stable. Currently, it misses alternative df/bn/mf
-
-insert into q_to_r (q_dcode, r_did)
-select cn.concept_code as q_dcode, c.concept_id as r_did
-from (
-  select concept_name 
-  from (
-    select concept_name, concept_code from extension_name where not exists (select 1 from q_to_r where q_dcode=concept_code)
-  )
-  join (
-    select concept_name, concept_class_id from concept where concept.vocabulary_id like 'RxNorm%' and invalid_reason is null
-  ) using(concept_name)
-)
-join extension_name cn using(concept_name)
-join concept c using(concept_name)
-where c.vocabulary_id like 'RxNorm%' and c.invalid_reason is null
-;
-commit;
-
+-- Temporary till stable xxxxxxxxxxxxxxxxxx
 insert into pack_q_to_r
 select pn.pack_concept_code, c.concept_id as clinical, null as branded, null as supplied, null as marketed, null as neither
 from pack_name pn join concept c using (concept_name) where c.vocabulary_id like 'RxNorm%' and invalid_reason is null 

      else null
    end as v,
    case
      when nu.rxn_unit='%' then ' %' -- percent (gas in Rx)
      when eds.denominator_value is not null then nu.rxn_unit||'/'||de.rxn_unit -- concentration (liquid)
      when eds.numerator_value is not null then nu.rxn_unit||'/'||de.rxn_unit -- concentration (liquid)
      when eds.amount_value is not null then au.rxn_unit -- absolute (solid)

      when others then null;
  end;
end;

-- generate OMOP codes for new concepts
insert into xxx_replace
