 
 -- Create full RxNorm universe from complete_concept_stage
 drop table complete_attribute purge;
-create table complete_attribute as
+create table complete_attribute nologging as
 select distinct
   c.concept_code,
   c.denominator_value as quant,

drop table dss_rowid_update purge;

-- replace packs in pack_content
create table pcs_rowid_update as
select pcs.rowid as irowid, xr.omop_code as pack_concept_code from xxx_replace xr join pack_content_stage pcs on pcs.pack_concept_code=xr.xxx_code;

merge into pack_content_stage pcs

join concept b on b.concept_id=r.concept_id_2 and b.concept_class_id ='Brand Name' 
join concept_ancestor a on d.concept_id=a.ancestor_concept_id
where r.invalid_reason is null
;
commit;

-- Create table with Suppliers for q
drop table q_mf purge;
create table q_mf nologging as 
select css.concept_code as concept_code_1, 
  case when css.mf_code=' ' then 0 -- No Supplier
    when m.concept_id_2 is null then 1 -- Supplier, but not mapped
    else m.concept_id_2 
  end as concept_id_2, 
  nvl(m.precedence, 1) as precedence
from complete_concept_stage css
left join relationship_to_concept m on m.concept_code_1=css.mf_code
;
commit;

-- Create table with Dose Forms for r
drop table r_mf purge;
create table r_mf nologging as 
select r.concept_id_1, r.concept_id_2 from concept_relationship r
join concept d on d.concept_id=r.concept_id_1 and d.vocabulary_id in ('RxNorm', 'RxNorm Extension')
join concept f on f.concept_id=r.concept_id_2 and f.concept_class_id ='Supplier' 
where r.invalid_reason is null and r.relationship_id='Has supplier'
;
commit;

