 BEGIN
 	PERFORM VOCABULARY_PACK.SetLatestUpdate(
 	pVocabularyName			=> 'HemOnc',
-	pVocabularyDate			=> TO_DATE ('20190530', 'yyyymmdd'),
-	pVocabularyVersion		=> 'HemOnc 2019-05-30',
+	pVocabularyDate			=> TO_DATE ('20190829', 'yyyymmdd'),
+	pVocabularyVersion		=> 'HemOnc 2019-08-29',
 	pVocabularyDevSchema	=> 'DEV_HEMONC'
 );
 END $_$;

SELECT NULL AS concept_id,
	concept_name,
	CASE 
		WHEN (
				h.concept_class_id IN (
					'Procedure',
					'Context'
					)
				OR h.concept_name = 'Radiotherapy'
				)
			THEN 'Procedure'
		WHEN h.concept_class_id IN (
				'Regimen',
				'Component',
				'Brand_Name',
				'Component Class',
				'Route',
				'Regimen type'

WHERE crs.concept_code_2 = i.concept_code_1
	AND crs.vocabulary_id_2 = i.vocabulary_id_1;

--6. Update wrong 'Maps to' and relationships from Regimen to Drugs to Brand Names
--and one totally incorrect drug form
--to do, make this step automatic
UPDATE concept_relationship_stage crs
SET concept_code_2 = i.new_code,
	vocabulary_id_2 = i.new_vocab
FROM (
	SELECT DISTINCT crs.concept_code_2 AS old_code,
		crs.vocabulary_id_2 AS old_vocab,
		c2.concept_code AS new_code,
		c2.vocabulary_id AS new_vocab
	FROM concept_relationship_stage crs
	JOIN concept c1 ON c1.concept_code = crs.concept_code_2
		AND c1.vocabulary_id = crs.vocabulary_id_2
		AND c1.vocabulary_id = 'RxNorm'
		AND c1.concept_class_id = 'Brand Name'
	JOIN concept_relationship cr ON cr.concept_id_1 = c1.concept_id
		AND cr.relationship_id = 'Brand name of'
		AND cr.invalid_reason IS NULL
	JOIN concept c2 ON c2.concept_id = cr.concept_id_2
		AND c2.concept_class_id = 'Ingredient'
		AND c2.standard_concept = 'S'
	JOIN concept_stage cs ON cs.concept_code = crs.concept_code_1
		AND cs.concept_name NOT LIKE '% and %' -- avoiding the combinatory drugs, they are mapped manually, see union below
		AND c1.concept_code NOT IN (
			'2119715',
			'1927886'
			) -- Herceptin Hylecta , Rituxan Hycela -  need to make better automatic work-aroud when have time
	WHERE crs.relationship_id IN (
			'Maps to',
			'Has antineopl Rx',
			'Has immunosuppr Rx',
			'Has local therap Rx',
			'Has support med Rx'
			)
	--Clinical Drug Forms Picked up manually
	
