 drop table dss_rowid_update purge;
 
 -- replace packs in pack_content
-create table pcs_rowid_update as
+create table pcs_rowid_update nologging 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;


  select distinct concept_code as drug_concept_code, denominator_value as q_value from complete_concept_stage
) q_quant on q_quant.drug_concept_code=m.q_dcode
left join ( -- RxNorm might not have drug_strength for all concept_class_id
  select distinct drug_concept_id, nvl(denominator_value, 0) as q_value from drug_strength
) r_quant on r_quant.drug_concept_id=m.r_did
-- get the Supplier for each q and r
join q_mf on q_mf.concept_code_1=m.q_dcode 
left join r_mf on r_mf.concept_id_1=m.r_did -- RxNorm does not have Dose Forms for non-formed concept classes
--get Box_size
join (
  select distinct concept_code as drug_concept_code, box_size as q_value from complete_concept_stage
) q_bs on q_bs.drug_concept_code=m.q_dcode
left join ( -- RxNorm might not have drug_strength for all concept_class_id
  select distinct drug_concept_id, nvl(box_size, 0) as q_value from drug_strength
) r_bs on r_bs.drug_concept_id=m.r_did
-- check for matching 
where q_df.concept_id_2=nvl(r_df.concept_id_2, 0) -- if no Dose Form match 0s
and q_bn.concept_id_2=nvl(r_bn.concept_id_2, 0) -- if no Brand Name match 0s
and q_quant.q_value=nvl(r_quant.q_value, 0) -- if no Quantity factors  match 0s
and q_mf.concept_id_2=nvl(r_mf.concept_id_2, 0) -- if no Dose Form match 0s
and q_bs.q_value=nvl(r_bs.q_value, 0) -- if no box sizes match 0s
;
commit;

