 select distinct descendant_concept_id as concept_id_1, concept_id_2
 from concept_relationship join concept_ancestor on ancestor_concept_id=concept_id_1 
 join concept bn on concept_id_2=bn.concept_id and bn.vocabulary_id in ('RxNorm', 'RxNorm Extension') and bn.concept_class_id='Brand Name'
-join concept c on concept_id_1=c.concept_id and ?.vocabulary_id like 'RxNorm%'
+join concept c on concept_id_1=c.concept_id and c.vocabulary_id like 'RxNorm%'
 join concept bd on descendant_concept_id=bd.concept_id and bd.vocabulary_id in ('RxNorm', 'RxNorm Extension')
  and bd.concept_class_id in ('Branded Drug Box', 'Quant Branded Box', 'Branded Drug Comp', 'Quant Branded Drug', 'Branded Drug Form', 'Branded Drug', 'Marketed Product', 'Branded Pack')
 where concept_relationship.invalid_reason is null and relationship_id='Has brand name'


commit;

begin
   devv5.vocabulary_pack.DeleteAmbiguousMAPSTO;
end;

commit;

--6 Working with replacement mappings

BEGIN
   DEVV5.VOCABULARY_PACK.CheckReplacementMappings;
END;
COMMIT;

--7 Deprecate 'Maps to' mappings to deprecated and upgraded concepts
BEGIN
   DEVV5.VOCABULARY_PACK.DeprecateWrongMAPSTO;
END;
COMMIT;

--8 Add mapping from deprecated to fresh concepts
BEGIN
   DEVV5.VOCABULARY_PACK.AddFreshMAPSTO;
END;
COMMIT;

--9 Delete ambiguous 'Maps to' mappings
BEGIN
   DEVV5.VOCABULARY_PACK.DeleteAmbiguousMAPSTO;
END;
COMMIT;

* 3. Write all concept classes, whether existing or not from all possible combinations *
***************************************************************************************/

drop table complete_concept_stage cascade constraints purge;-- select * from complete_concept_stage where rownum < 100;
create table complete_concept_stage nologging as
select 'XXX'||xxx_seq.nextval as concept_code, -- make sure xxx_seq is defined.
  c.*
