CREATE TABLE I2B2DEMODATA.MAP_TYPE ( 
  MAP_TYPE_CD CHAR(2) PRIMARY KEY,
  MAP_TYPE_DESC VARCHAR2(25) NOT NULL,
  CREATE_USER         VARCHAR2(50),
  UPDATE_USER         VARCHAR2(50),
  CREATE_DATE         DATE,
  UPDATE_DATE         DATE
);


ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP COLUMN MAP_DESC;
ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP CONSTRAINT "FK_MAP_DIMENSION_ENCODING_DIME";
ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP COLUMN TARGET_ENCODING;
ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP CONSTRAINT "FK_MAP_DIMENSION_CONCEPT_DIME";
ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP COLUMN CONCEPT_PATH;

ALTER TABLE I2B2DEMODATA.MAP_DIMENSION ADD (
MAP_TYPE_CD 		CHAR(2) 	NOT NULL REFERENCES MAP_TYPE(MAP_TYPE_CD),
TARGET_CONCEPT_PATH 	VARCHAR2(700) 	NULL REFERENCES CONCEPT_DIMENSION(CONCEPT_PATH),
AUTHOR 			VARCHAR(25) 	NULL,
CREATOR 		VARCHAR(25) 	NULL,
CURATOR 		VARCHAR(25) 	NULL,
CONTRIBUTOR 		VARCHAR(25) 	NULL,
CREATE_DATE 		DATE 		NULL,
ID 			VARCHAR2(25) 	NULL,
LICENSE 		VARCHAR2(50) 	NULL,
LICENSE_TYPE 		VARCHAR2(25) 	NULL,
IS_LICENSE_RESTRICTED 	CHAR(1) 	NULL,
RELEASE_DATE 		DATE 		NULL,
RELEASE_REPO_NAME 	VARCHAR2(50) 	NULL,
RELEASE_URI 		VARCHAR2(50) 	NULL,
RELEASE_VERSION 	VARCHAR2(10) 	NULL,
RESTRICTION_TYPE 	VARCHAR2(25) 	NULL,
RIGHTS_HOLDER 		VARCHAR2(25) 	NULL,
PURPOSE 		VARCHAR2(50) 	NULL,
ORG_NAME 		VARCHAR2(50) 	NULL,
ORG_TYPE 		VARCHAR2(25) 	NULL,
SOURCE_SOFTWARE_ENV 	VARCHAR2(50) 	NULL,
ETL_SCRIPT_URL 		VARCHAR2(50) 	NULL
);


CREATE TABLE I2B2DEMODATA.MAP_SRC_CONCEPT_SET (
MAP_SRC_CONCEPT_ID NUMBER(38) PRIMARY KEY,
MAP_ID NUMBER(38) NOT NULL REFERENCES I2B2DEMODATA.MAP_DIMENSION(MAP_ID),
SOURCE_CONCEPT_PATH VARCHAR2(700),
SOURCE_CONCEPT_CD VARCHAR2(50)
);


ALTER TABLE I2B2DEMODATA.OBSERVATION_FACT DROP CONSTRAINT "FK_OBS_F_PATIENT_DIMENSION";

ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT DROP CONSTRAINT "FK_MAP_DATA_FACT_PATIENT_DIME";

ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT DROP CONSTRAINT "FK_MAP_DATA_FACT_VISIT_DIMENS";

ALTER TABLE I2B2DEMODATA.MAP_AGGR_FACT ADD PATIENT_NUM NUMBER(38) NOT NULL;

ALTER TABLE I2B2DEMODATA.MAP_AGGR_FACT ADD CONCEPT_CD VARCHAR2(50) NOT NULL;


CREATE VIEW I2B2DEMODATA.OBS_FACT_VIEW (ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, CONCEPT_PATH, VALTYPE_CD, TVAL_CHAR, NVAL_NUM, VALUEFLAG_CD, QUANTITY_NUM, UNITS_CD, END_DATE, 
LOCATION_CD, CONFIDENCE_NUM, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID, OBSERVATION_FACT_ID) AS
(
SELECT ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, CONCEPT_PATH, VALTYPE_CD, TVAL_CHAR, NVAL_NUM, VALUEFLAG_CD, QUANTITY_NUM, UNITS_CD, END_DATE, 
LOCATION_CD, CONFIDENCE_NUM, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID, OBSERVATION_FACT_ID
FROM I2B2DEMODATA.OBSERVATION_FACT
UNION ALL
SELECT ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, CONCEPT_PATH, VALTYPE_CD, TVAL_CHAR, NVAL_NUM, VALUEFLAG_CD, QUANTITY_NUM, UNITS_CD, END_DATE, 
LOCATION_CD, CONFIDENCE_NUM, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID, OBSERVATION_FACT_ID
FROM I2B2DEMODATA.MAP_DATA_FACT
UNION ALL
SELECT NULL, PATIENT_NUM, CONCEPT_CD, NULL, NULL, NULL, CONCEPT_PATH,VALTYPE_CD, TVAL_CHAR, NVAL_NUM, NULL, QUANTITY_NUM, UNITS_CD, NULL, NULL, CONFIDENCE_NUM, NULL, NULL, NULL, NULL, NULL, NULL
FROM I2B2DEMODATA.MAP_AGGR_FACT
);


ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT MODIFY TVAL_CHAR VARCHAR2(500);