 );
 commit;
 
---15
+--16
 --deprecate the drugs that have inaccurate dosage due to difference in ingredients subvarieties
 --for ingredients with not null amount_value
 update concept_stage c set invalid_reason='D',

            join concept_relationship_stage crs on crs.concept_code_1=d.concept_code_1 and crs.vocabulary_id_1='RxNorm Extension' and crs.invalid_reason is null
            --for c2 we cannot use stage table, because we need rx classes
            join concept c2 on c2.concept_code=crs.concept_code_2 and c2.vocabulary_id=crs.vocabulary_id_2 and c2.concept_class_id=d.concept_class_id
            where cs1.concept_name not like '%'||c2.concept_name||'%'
        )
        unpivot ((concept_code_1,vocabulary_id_1,concept_code_2,vocabulary_id_2) 
        FOR relationships IN ((concept_code_1,vocabulary_id_1,concept_code_2,vocabulary_id_2),(concept_code_2,vocabulary_id_2,concept_code_1,vocabulary_id_1)))

and invalid_reason is null;
commit;

--12 
--wrong pack components
update concept_stage  set invalid_reason='D',
valid_end_date=(SELECT latest_update-1 FROM vocabulary WHERE vocabulary_id = 'RxNorm Extension')
