             brand_code AS concept_code_2,
             'RxNorm' AS vocabulary_id_1,
             'RxNorm' AS vocabulary_id_2,
-            'RxNorm has ing' AS relationship_id,
+            'Has brand name' AS relationship_id,
             (SELECT latest_update
                FROM vocabulary
               WHERE vocabulary_id = 'RxNorm')

                AND e.vocabulary_id = 'RxNorm'
                AND e.invalid_reason IS NULL
    WHERE d.vocabulary_id = 'ATC' AND d.concept_class_id = 'ATC 5th' -- there are some weird 4th level links, LIKE D11AC 'Medicated shampoos' to an RxNorm Dose Form
   -- add ATC to RxNorm mapping. ATC is both a classification (ATC 1-4) AND a source (ATC 5th)
   UNION ALL
   SELECT DISTINCT d.concept_code AS concept_code_1,

                                 AND concept_code = rxcui2));
COMMIT;

--Rename "RxNorm has ing" to "Has brand name" if concept_code_2 has the concept_class_id='Brand Name' and reverse
UPDATE concept_relationship_stage
   SET RELATIONSHIP_ID = 'Has brand name'
 WHERE ROWID IN (SELECT crs.ROWID
                   FROM concept_relationship_stage crs
                        LEFT JOIN concept_stage cs ON cs.concept_code = crs.concept_code_2 AND cs.vocabulary_id = crs.vocabulary_id_2 AND cs.concept_class_id = 'Brand Name'
                        LEFT JOIN concept c ON c.concept_code = crs.concept_code_2 AND c.vocabulary_id = crs.vocabulary_id_2 AND c.concept_class_id = 'Brand Name'
                  WHERE crs.invalid_reason IS NULL AND crs.relationship_id = 'RxNorm has ing' AND COALESCE (cs.concept_code, c.concept_code) IS NOT NULL);
--reverse
UPDATE concept_relationship_stage
   SET RELATIONSHIP_ID = 'Brand name of'
 WHERE ROWID IN (SELECT crs.ROWID
                   FROM concept_relationship_stage crs
                        LEFT JOIN concept_stage cs ON cs.concept_code = crs.concept_code_1 AND cs.vocabulary_id = crs.vocabulary_id_1 AND cs.concept_class_id = 'Brand Name'
                        LEFT JOIN concept c ON c.concept_code = crs.concept_code_1 AND c.vocabulary_id = crs.vocabulary_id_1 AND c.concept_class_id = 'Brand Name'
                  WHERE crs.invalid_reason IS NULL AND crs.relationship_id = 'RxNorm ing of' AND COALESCE (cs.concept_code, c.concept_code) IS NOT NULL);
COMMIT;

-- Add missing relationships between Branded Packs and their Brand Names
INSERT /*+ APPEND */ INTO  concept_relationship_stage (concept_code_1,
                                        concept_code_2,
