 'dm+d' as vocabulary_id_2,
 'Maps to' as relationship_id,
 (select latest_update from vocabulary where vocabulary_id = 'Gemscript') as valid_start_date,
-'31-Dec-2099' as valid_end_date,
+to_date ('31122099', 'ddmmyyyy') as valid_end_date,
 null as invalid_reason
 from THIN_GEMSC_DMD_0417
  ;
 commit
 ;
 --old table from Christian
-insert into concept_relationship_stage
+insert into concept_relationship_stage 
 select 
 null as CONCEPT_ID_1, 
 null as CONCEPT_ID_2,
-'Gemscript' as vocabulary_id_1,
 GEMSCRIPT_DRUG_CODE as concept_code_1,
 DMD_CODE as concept_code_2,
+'Gemscript' as vocabulary_id_1,
 'dm+d' as vocabulary_id_2,
 'Maps to' as relationship_id,
 (select latest_update from vocabulary where vocabulary_id = 'Gemscript') as valid_start_date,
-'31-Dec-2099' as valid_end_date,
+to_date ('31122099', 'ddmmyyyy') as valid_end_date,
 null as invalid_reason
 from gemscript_dmd_map  where  GEMSCRIPT_DRUG_CODE not in (select concept_code_1 from  concept_relationship_stage  )
 ;

null as standard_concept,
GEMSCRIPT_DRUGCODE as concept_code,
(select latest_update from vocabulary where vocabulary_id = 'Gemscript') as valid_start_date, -- TRUNC(SYSDATE)
'31-Dec-2099' as valid_end_date,
null as invalid_reason
from THIN_GEMSC_DMD_0417 where GEMSCRIPT_DRUGCODE not in (select concept_code from concept_stage)
;

'Gemscript' as vocabulary_id_2,
'Maps to' as relationship_id,
(select latest_update from vocabulary where vocabulary_id = 'Gemscript') as valid_start_date,
to_date ('31122099', 'ddmmyyyy') as valid_end_date,
null as invalid_reason
from THIN_GEMSC_DMD_0417  
;
