CREATE TABLE AGE_UNIT_DICT (
    SERIAL_NO        int NOT NULL,
    UNIT_CODE        varchar(10) NOT NULL,
    AGE_UNIT_NAME    varchar(20) NOT NULL,
    IS_DEFAULT       varchar(1) NOT NULL,
    CONSTRAINT PK_AGE_UNIT_DICT PRIMARY KEY (UNIT_CODE)
);

CREATE TABLE BODY_HEIGHT_UNIT_DICT (
    SERIAL_NO                int NOT NULL,
    UNIT_CODE                varchar(10) NOT NULL,
    BODY_HEIGHT_UNIT_NAME    varchar(20) NOT NULL,
    IS_DEFAULT               varchar(1) NOT NULL,
    CONSTRAINT PK_BODY_HEIGHT_UNIT_DICT PRIMARY KEY (UNIT_CODE)
);

CREATE TABLE BODY_WEIGHT_UNIT_DICT (
    SERIAL_NO                int NOT NULL,
    UNIT_CODE                varchar(10) NOT NULL,
    BODY_WEIGHT_UNIT_NAME    varchar(20) NOT NULL,
    IS_DEFAULT               varchar(1) NOT NULL,
    CONSTRAINT PK_BODY_WEIGHT_UNIT_DICT PRIMARY KEY (UNIT_CODE)
);

CREATE TABLE CALL_STATUS_DICT (
    SERIAL_NO    int NOT NULL,
    CALL_CODE    varchar(30) NOT NULL,
    CALL_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_CALL_STATUS_DICT PRIMARY KEY (CALL_CODE)
);

CREATE TABLE CLIENT_DICT (
    SERIAL_NO        SERIAL NOT NULL,
    CLIENT_IP        varchar(50) NULL,
    SERIAL_NUMBER    varchar(50)  NULL,
    MEMO             varchar(300)  NULL,
    CONSTRAINT PK_CLIENT_DICT PRIMARY KEY (SERIAL_NO)
);

CREATE TABLE CONSULTATION_STATUS_DICT (
    SERIAL_NO            int NOT NULL,
    CONSULTATION_CODE    varchar(30) NOT NULL,
    CONSULTATION_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_CONSULTATION_STATUS_DICT PRIMARY KEY (CONSULTATION_CODE)
);

CREATE TABLE UPLOAD_STATUS_DICT (
    SERIAL_NO      int NOT NULL,
    UPLOAD_CODE    varchar(30) NOT NULL,
    UPLOAD_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_UPLOAD_STATUS_DICT PRIMARY KEY (UPLOAD_CODE)
);

CREATE TABLE EXPORT_STATUS_DICT (
    SERIAL_NO      int NOT NULL,
    EXPORT_CODE    varchar(30) NOT NULL,
    EXPORT_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_EXPORT_STATUS_DICT PRIMARY KEY (EXPORT_CODE)
);

CREATE TABLE CHANGE_STATUS_DICT (
    SERIAL_NO      int NOT NULL,
    CHANGE_CODE    varchar(30) NOT NULL,
    CHANGE_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_CHANGE_STATUS_DICT PRIMARY KEY (CHANGE_CODE)
);

CREATE TABLE DIAGSYNC_STATUS_DICT (
    SERIAL_NO        int NOT NULL,
    DIAGSYNC_CODE    varchar(30) NOT NULL,
    DIAGSYNC_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_DIAGSYNC_STATUS_DICT PRIMARY KEY (DIAGSYNC_CODE)
);

CREATE TABLE FILEDELIVERY_STATUS_DICT (
    SERIAL_NO            int NOT NULL,
    FILEDELIVERY_CODE    varchar(30) NOT NULL,
    FILEDELIVERY_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_FILEDELIVERY_STATUS_DICT PRIMARY KEY (FILEDELIVERY_CODE)
);

CREATE TABLE DEPT_OI_ATTR_DICT (
    OI_ATTR_CODE      varchar(1) NOT NULL,
    OI_ATTR_NAME      varchar(30) NOT NULL,
    CONSTRAINT PK_DEPT_OI_ATTR_DICT PRIMARY KEY (OI_ATTR_CODE)
);

CREATE TABLE EXAM_RESULT_STATUS_DICT (
    EXAM_RESULT_STATUS_CODE    varchar(1) NOT NULL,
    EXAM_RESULT_STATUS_NAME    varchar(50) NULL,
    CONSTRAINT PK_EXAM_RESULT_STATUS_DICT PRIMARY KEY (EXAM_RESULT_STATUS_CODE)
);

CREATE TABLE ITEM_STATUS_DICT (
    STATUS_CODE    int NOT NULL,
    STATUS_NAME    varchar(50) NULL,
    CONSTRAINT PK_ITEM_STATUS_DICT PRIMARY KEY (STATUS_CODE)
);

CREATE TABLE KEY_INFO (
    REGISTER_CODE        varchar(44) NOT NULL,
    REGISTER_DATE        varchar(50) NOT NULL,
    EXPIRED_DATE         varchar(50) NULL,
    REGISTERED_NUMBER    varchar(50) NULL,
    IDENTITY_CODE        varchar(50) NULL,
    IS_VALID             int NOT NULL,
    CONSTRAINT PK_KEY_INFO PRIMARY KEY (REGISTER_CODE)
);

CREATE TABLE MEDIA_DICT (
    ID               SERIAL NOT NULL,
    MEDIA_NAME       varchar(16) NOT NULL,
    MEDIA_TYPE       varchar(5) NOT NULL,
    HOST_NAME        varchar(15) NULL,
    PORT             varchar(5) NULL,
    USER_NAME        varchar(64) NULL,
    USER_PWD         varchar(64) NULL,
    CURRENT_MEDIA    varchar(1) NULL,
    CONSTRAINT PK_MEDIA_DICT PRIMARY KEY (ID)
    --CONSTRAINT IX_MEDIA_DICT??
);

CREATE TABLE PACEMAKER_DICT (
    SERIAL_NO         int NOT NULL,
    PACEMAKER_CODE    varchar(1) NOT NULL,
    PACEMAKER_NAME    varchar(20) NOT NULL,
    CONSTRAINT PK_PACEMAKER_DICT PRIMARY KEY (PACEMAKER_CODE)
);

CREATE TABLE PATIENT_SOURCE_DICT (
    SERIAL_NO              int NOT NULL,
    PATIENT_SOURCE_CODE    varchar(1) NOT NULL,
    PATIENT_SOURCE_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_PATIENT_SOURCE_DICT PRIMARY KEY (PATIENT_SOURCE_CODE)
);

CREATE TABLE PRESSURE_UNIT_DICT (
    SERIAL_NO             int NOT NULL,
    UNIT_CODE             varchar(10) NOT NULL,
    PRESSURE_UNIT_NAME    varchar(20) NOT NULL,
    IS_DEFAULT            varchar(1) NOT NULL,
    CONSTRAINT PK_PRESSURE_UNIT_DICT PRIMARY KEY (UNIT_CODE)
);

CREATE TABLE PRINT_STATUS_DICT (
    SERIAL_NO     int NOT NULL,
    PRINT_CODE    varchar(30) NOT NULL,
    PRINT_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_PRINT_STATUS_DICT PRIMARY KEY (PRINT_CODE)
);

CREATE TABLE RACE_DICT (
    SERIAL_NO    int NOT NULL,
    RACE_NAME    varchar(20) NOT NULL,
    RACE_FLAG    varchar(20) NOT NULL,
    CONSTRAINT PK_RACE_DICT PRIMARY KEY (RACE_FLAG)
);

CREATE TABLE REPORTFORMAT_INFO (
    REPORTFORMAT    int NOT NULL,
    MEMO            varchar(100) NULL,
    CONSTRAINT PK_REPORTFORMAT_INFO PRIMARY KEY (REPORTFORMAT)
);

CREATE TABLE EXAM_PRIORITY_DICT (
    SERIAL_NO        int NOT NULL,
    PRIORITY_CODE    varchar(1) NOT NULL,
    PRIORITY_NAME    varchar(20) NOT NULL,
    CONSTRAINT PK_EXAM_PRIORITY_DICT PRIMARY KEY (PRIORITY_CODE)
);

CREATE TABLE CURRENCY_UNIT_DICT (
    SERIAL_NO             int NOT NULL,
    UNIT_CODE             varchar(3) NOT NULL,
    CURRENCY_UNIT_NAME    varchar(20) NOT NULL,
    IS_DEFAULT            varchar(1) NOT NULL,
    CONSTRAINT PK_CURRENCY_UNIT_DICT PRIMARY KEY (UNIT_CODE)
);

CREATE TABLE MESSAGE_TYPE_DICT 
(
    MESSAGE_TYPE_CODE     int NOT NULL, 
    MESSAGE_TYPE_NAME     varchar(50) NOT NULL, 
    CONSTRAINT PK_MESSAGE_TYPE_DICT PRIMARY KEY (MESSAGE_TYPE_CODE)
);

CREATE TABLE EXAM_COLLECT_DATA
(
  SERIAL_NO SERIAL NOT NULL,
  EXAM_ID varchar(15),
  MEDIA integer NOT NULL,
  FILEPATH varchar(256),
  DATA_TYPE varchar(10),
  GUID varchar(50),
  CONSTRAINT PK_EXAM_COLLECT_DATA PRIMARY KEY (SERIAL_NO)
);

CREATE TABLE COLLECTION_TYPE_DICT
(
  SERIAL_NO SERIAL NOT NULL,
  TYPE_NAME varchar(100),
  CONSTRAINT "COLLECTION_TYPE_DICT_PKEY" PRIMARY KEY (SERIAL_NO)
);

CREATE TABLE MATCH_STYLE_DICT(
	SERIAL_NO integer NOT NULL,
	MATCH_STYLE_CODE varchar(30) NOT NULL,
	MATCH_STYLE_NAME varchar(30) NOT NULL,
 CONSTRAINT PK_MATCH_STYLE_DICT PRIMARY KEY (MATCH_STYLE_CODE)
);

CREATE TABLE CUSTOM_TITLE(
	CUSTOM_TITLE_CODE varchar(1) NOT NULL,
	CUSTOM_TITLE_NAME varchar(100) NULL,
 CONSTRAINT PK_CUSTOM_TITLE PRIMARY KEY (CUSTOM_TITLE_CODE)
);

CREATE TABLE RIGHT_CLASS (
    SERIAL_NO      int NOT NULL,
    CLASS_ID       int NOT NULL,
    CLASS_NAME     varchar(100) NOT NULL,
    IS_VALID       int NOT NULL,
    DESCRIPTION    varchar(200) NULL,
    CONSTRAINT PK_RIGHT_CLASS PRIMARY KEY (CLASS_ID)
);

CREATE TABLE RIGHT_DETAIL (
    SERIAL_NO      int NOT NULL,
    RIGHT_ID       int NOT NULL,
    CLASS_ID       int NOT NULL,
    RIGHT_NAME     varchar(100) NOT NULL,
    IS_VALID       int NOT NULL,
    DESCRIPTION    varchar(200) NULL,
    CONSTRAINT PK_RIGHT_DETAIL PRIMARY KEY (RIGHT_ID),
    CONSTRAINT FK_RIGHT_DETAIL_RIGHT_CLASS FOREIGN KEY (CLASS_ID) REFERENCES RIGHT_CLASS (CLASS_ID)
);

CREATE TABLE ROLE_CLASS (
    CLASS_ID       SERIAL NOT NULL,
    CLASS_NAME     varchar(100) NOT NULL,
    IS_VALID       int NOT NULL,
    DESCRIPTION    varchar(200) NULL,
    CONSTRAINT PK_ROLE_CLASS PRIMARY KEY (CLASS_ID)
);

CREATE TABLE SEVERITY_DICT (
    SERIAL_NO        int NOT NULL,
    SEVERITY_CODE    varchar(30) NOT NULL,
    SEVERITY_NAME    varchar(30) NOT NULL,
    CONSTRAINT PK_SEVERITY_DICT PRIMARY KEY (SEVERITY_CODE)
);

CREATE TABLE SEX_DICT (
    SERIAL_NO    int NOT NULL,
    SEX_NAME     varchar(15) NOT NULL,
    SEX_FLAG     varchar(1) NOT NULL,
    CONSTRAINT PK_SEX_DICT PRIMARY KEY (SEX_FLAG)
);

CREATE TABLE USERS (
    USER_ID                 varchar(16) NOT NULL,
    USER_PWD                varchar(32) NOT NULL,
    USER_NAME               varchar(64) NULL,
    ELECTRONIC_SIGNATURE    bytea NULL,
    PRIVATE_KEY             varchar(1000) NULL,
    PUBLIC_KEY              varchar(300) NULL,
    USER_DEPT               varchar(8) NULL,
    USER_STATUS             varchar(1) NOT NULL,
    CREATE_DATE             timestamp NULL,
    CONSTRAINT PK_USERS PRIMARY KEY (USER_ID)
);

CREATE TABLE MESSAGE_GROUP (
    GROUP_ID       int NOT NULL,
    GROUP_NAME     varchar(100) NOT NULL,
    CREATE_BY      varchar(16) NOT NULL,
    CONSTRAINT PK_MESSAGE_GROUP PRIMARY KEY (GROUP_ID),
    CONSTRAINT FK_MESSAGE_GROUP_USERS FOREIGN KEY (CREATE_BY) REFERENCES USERS (USER_ID)
);

CREATE TABLE ROLE_OF_USER (
    USER_ID     varchar(16) NOT NULL,
    CLASS_ID    int NOT NULL,
    CONSTRAINT PK_ROLE_OF_USER PRIMARY KEY (USER_ID, CLASS_ID),
    CONSTRAINT FK_ROLE_OF_USER_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID),
    CONSTRAINT FK_ROLE_OF_USER_ROLE_CLASS FOREIGN KEY (CLASS_ID) REFERENCES ROLE_CLASS (CLASS_ID)
);

CREATE TABLE MESSAGE_GROUP_OF_USER (
    USER_ID     varchar(16) NOT NULL,
    GROUP_ID    int NOT NULL,
    CONSTRAINT PK_MESSAGE_GROUP_OF_USER PRIMARY KEY (USER_ID, GROUP_ID),
    CONSTRAINT FK_MESSAGE_GROUP_OF_USER_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID),
    CONSTRAINT FK_MESSAGE_GROUP_OF_USER_MESSAGE_GROUP FOREIGN KEY (GROUP_ID) REFERENCES MESSAGE_GROUP (GROUP_ID)
);

CREATE TABLE VERSION_INFO (
    VERSION    varchar(10) NOT NULL,
    MEMO       varchar(100) NULL,
    CONSTRAINT PK_VERSION_INFO PRIMARY KEY (VERSION)
);

CREATE TABLE ADDED_RIGHT_OF_USER (
    USER_ID     varchar(16) NOT NULL,
    RIGHT_ID    int NOT NULL,
    IS_ALLOW    int NOT NULL,
    CONSTRAINT PK_ADDED_RIGHT_OF_USER PRIMARY KEY (USER_ID, RIGHT_ID),
    CONSTRAINT FK_ADDED_RIGHT_OF_USER_RIGHT_DETAIL FOREIGN KEY (RIGHT_ID) REFERENCES RIGHT_DETAIL (RIGHT_ID),
    CONSTRAINT FK_ADDED_RIGHT_OF_USER_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE DEPT_DICT (
    SERIAL_NO      int NOT NULL,
    DEPT_CODE      varchar(8) NOT NULL,
    DEPT_NAME      varchar(30) NOT NULL,
    PHONE_NUMBER   varchar(16) NULL,
    OUTP_OR_INP    varchar(1) NOT NULL,
    ITEM_STATUS    int NOT NULL,
    DEPT_LEVEL     int NOT NULL,
    IS_VALID       int NOT NULL,
    CONSTRAINT PK_DEPT_DICT PRIMARY KEY (DEPT_CODE),
    CONSTRAINT FK_DEPT_DICT_DEPT_OI_ATTR_DICT FOREIGN KEY (OUTP_OR_INP) REFERENCES DEPT_OI_ATTR_DICT (OI_ATTR_CODE),
    CONSTRAINT FK_DEPT_DICT_ITEM_STATUS_DICT FOREIGN KEY (ITEM_STATUS) REFERENCES ITEM_STATUS_DICT (STATUS_CODE)
);

CREATE TABLE REQ_DEPT_DICT (
    SERIAL_NO      int NOT NULL,
    DEPT_CODE      varchar(8) NOT NULL,
    DEPT_NAME      varchar(30) NOT NULL,
    IS_VALID       int NOT NULL,
    CONSTRAINT PK_REQ_DEPT_DICT PRIMARY KEY (DEPT_CODE)
);

CREATE TABLE DEPT_LEVEL_RELATION (
    SERIAL_NO              SERIAL NOT NULL,
    CURRENT_DEPT_CODE      varchar(8) NOT NULL,
    UPPER_DEPT_CODE        varchar(8) NULL,
    CONSTRAINT PK_DEPT_LEVEL_RELATION PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_DEPT_LEVEL_RELATION_CURRENT_DEPT_DICT FOREIGN KEY (CURRENT_DEPT_CODE) REFERENCES DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_DEPT_LEVEL_RELATION_UPPER_DEPT_DICT FOREIGN KEY (UPPER_DEPT_CODE) REFERENCES DEPT_DICT (DEPT_CODE)
);

CREATE TABLE RIGHT_OF_ROLE (
    ROLE_CLASS_ID    int NOT NULL,
    RIGHT_ID         int NOT NULL,
    DEPT_CODE        varchar(8) NOT NULL,
    CONSTRAINT PK_RIGHT_OF_ROLE PRIMARY KEY (ROLE_CLASS_ID, RIGHT_ID, DEPT_CODE),
    CONSTRAINT FK_RIGHT_OF_ROLE_DEPT_DICT FOREIGN KEY (DEPT_CODE) REFERENCES DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_RIGHT_OF_ROLE_RIGHT_DETAIL FOREIGN KEY (RIGHT_ID) REFERENCES RIGHT_DETAIL (RIGHT_ID),
    CONSTRAINT FK_RIGHT_OF_ROLE_ROLE_CLASS FOREIGN KEY (ROLE_CLASS_ID) REFERENCES ROLE_CLASS (CLASS_ID)
);

CREATE TABLE EXAM_CLASS_DICT (
    SERIAL_NO          int NOT NULL,
    EXAM_CLASS_CODE    varchar(1) NOT NULL,
    EXAM_CLASS_NAME    varchar(40) NOT NULL,
    IS_VALID           varchar(1) NOT NULL,
    ITEM_STATUS        int NOT NULL,
    CONSTRAINT PK_EXAM_CLASS_DICT PRIMARY KEY (EXAM_CLASS_CODE),
    CONSTRAINT FK_EXAM_CLASS_DICT_ITEM_STATUS_DICT FOREIGN KEY (ITEM_STATUS) REFERENCES ITEM_STATUS_DICT (STATUS_CODE)
);

CREATE TABLE EXAM_ITEM_DICT (
    SERIAL_NO         int NOT NULL,
    EXAM_ITEM_CODE    varchar(2) NOT NULL,
    EXAM_ITEM_NAME    varchar(60) NOT NULL,
    IS_VALID          varchar(1) NOT NULL,
    ITEM_STATUS       int NOT NULL,
    EXAM_ITEM_FEE     numeric(10, 2) NULL,
    CURRENCY_UNIT     varchar(3) NULL,
    CONSTRAINT PK_EXAM_ITEM_DICT PRIMARY KEY (EXAM_ITEM_CODE),
    CONSTRAINT FK_EXAM_ITEM_DICT_ITEM_STATUS_DICT FOREIGN KEY (ITEM_STATUS) REFERENCES ITEM_STATUS_DICT (STATUS_CODE),
    CONSTRAINT FK_EXAM_ITEM_DICT_CURRENCY_UNIT_DICT FOREIGN KEY (CURRENCY_UNIT) REFERENCES CURRENCY_UNIT_DICT (UNIT_CODE)
);

CREATE TABLE EXAM_CLASS_ITEM_DICT (
    EXAM_CLASS_CODE    varchar(1) NOT NULL,
    EXAM_ITEM_CODE     varchar(2) NOT NULL,
    CONSTRAINT PK_EXAM_CLASS_ITEM_DICT PRIMARY KEY (EXAM_CLASS_CODE, EXAM_ITEM_CODE),
    CONSTRAINT FK_EXAM_CLASS_ITEM_DICT_EXAM_CLASS_DICT FOREIGN KEY (EXAM_CLASS_CODE) REFERENCES EXAM_CLASS_DICT (EXAM_CLASS_CODE),
    CONSTRAINT FK_EXAM_CLASS_ITEM_DICT_EXAM_ITEM_DICT FOREIGN KEY (EXAM_ITEM_CODE) REFERENCES EXAM_ITEM_DICT (EXAM_ITEM_CODE)
);

CREATE TABLE DEPT_EXAM_ITEM_DICT (
    DEPT_CODE       varchar(8) NOT NULL,
    EXAM_CLASS_CODE varchar(1) NOT NULL,
    EXAM_ITEM_CODE  varchar(1) NOT NULL,
    CONSTRAINT PK_DEPT_EXAM_ITEM_DICT PRIMARY KEY (DEPT_CODE, EXAM_CLASS_CODE, EXAM_ITEM_CODE),
    CONSTRAINT FK_DEPT_EXAM_ITEM_DICT_DEPT_DICT FOREIGN KEY (DEPT_CODE) REFERENCES DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_DEPT_EXAM_ITEM_DICT_EXAM_CLASS_DICT FOREIGN KEY (EXAM_CLASS_CODE) REFERENCES EXAM_CLASS_DICT (EXAM_CLASS_CODE),
    CONSTRAINT FK_DEPT_EXAM_ITEM_DICT_EXAM_ITEM_DICT FOREIGN KEY (EXAM_ITEM_CODE) REFERENCES EXAM_ITEM_DICT (EXAM_ITEM_CODE)
);

CREATE TABLE ECG_DEVICES_DICT (
    DEVICE_ID        varchar(15) NOT NULL,
    DEPT_CODE        varchar(8) NOT NULL,
    DEVICE_NAME      varchar(30) NOT NULL,
    DEVICE_MODLE     varchar(20) NULL,
    MEMO             varchar(100) NULL,
    DEVICE_IP        varchar(15) NULL,
    DEVICE_STATUS    varchar(50) NULL,
    ITEM_STATUS      int NOT NULL,
    CONSTRAINT PK_ECG_DEVICES_DICT PRIMARY KEY (DEVICE_ID),
    CONSTRAINT FK_ECG_DEVICES_DICT_DEPT_DICT FOREIGN KEY (DEPT_CODE) REFERENCES DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_ECG_DEVICES_DICT_ITEM_STATUS_DICT FOREIGN KEY (ITEM_STATUS) REFERENCES ITEM_STATUS_DICT (STATUS_CODE)
);

CREATE TABLE IN_DIAGNOSING (
    EXAM_ID       varchar(15) NOT NULL,
    USER_ID       varchar(16) NULL,
    USER_IP       varchar(50) NULL,
    BAK_STATUS    varchar(1) NULL,
    CONSTRAINT PK_IN_DIAGNOSING PRIMARY KEY (EXAM_ID),
    CONSTRAINT FK_IN_DIAGNOSING_EXAM_RESULT_STATUS_DICT FOREIGN KEY (BAK_STATUS) REFERENCES EXAM_RESULT_STATUS_DICT (EXAM_RESULT_STATUS_CODE),
    CONSTRAINT FK_IN_DIAGNOSING_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_MANUALLY_MATCH (
    SERIAL_NO             SERIAL NOT NULL,
    APPOINT_ID            varchar(64) NULL,
    EXAM_ID               varchar(15) NULL,
    PERFORMED_BY          varchar(8) NOT NULL,
    EXAM_CLASS            varchar(1) NOT NULL,
    EXAM_ITEM             varchar(2) NOT NULL,
    PATIENT_ID            varchar(64) NULL,
    NAME                  varchar(64) NULL,
    SEX                   varchar(1) NULL,
    DATE_OF_BIRTH         timestamp NULL,
    AGE                   int NULL,
    AGE_UNIT              varchar(10) NULL,
    VISIT_ID              varchar(64) NULL,
    DEVICE_ID             varchar(15) NOT NULL,
    EXAM_DATE_TIME        timestamp NULL,
    CAUSE                 varchar(100) NOT NULL,
    GUID                  varchar(36) NOT NULL,
    RESULT_STATUS         varchar(1) NOT NULL,
    CONSTRAINT PK_EXAM_MANUALLY_MATCH PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_MANUALLY_MATCH_AGE_UNIT_DICT FOREIGN KEY (AGE_UNIT) REFERENCES AGE_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_MANUALLY_MATCH_DEPT_DICT FOREIGN KEY (PERFORMED_BY) REFERENCES DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_EXAM_MANUALLY_MATCH_ECG_DEVICES_DICT FOREIGN KEY (DEVICE_ID) REFERENCES ECG_DEVICES_DICT (DEVICE_ID),
    CONSTRAINT FK_EXAM_MANUALLY_MATCH_EXAM_CLASS_DICT FOREIGN KEY (EXAM_CLASS) REFERENCES EXAM_CLASS_DICT (EXAM_CLASS_CODE),
    CONSTRAINT FK_EXAM_MANUALLY_MATCH_EXAM_ITEM_DICT FOREIGN KEY (EXAM_ITEM) REFERENCES EXAM_ITEM_DICT (EXAM_ITEM_CODE),
    CONSTRAINT FK_EXAM_MANUALLY_MATCH_EXAM_RESULT_STATUS_DICT FOREIGN KEY (RESULT_STATUS) REFERENCES EXAM_RESULT_STATUS_DICT (EXAM_RESULT_STATUS_CODE),
    CONSTRAINT FK_EXAM_MANUALLY_MATCH_SEX_DICT FOREIGN KEY (SEX) REFERENCES SEX_DICT (SEX_FLAG)
);

CREATE TABLE EXAM_APPOINTS (
    ID                    SERIAL NOT NULL,
    APPOINT_ID            varchar(64) NOT NULL,
    PERFORMED_BY          varchar(8) NOT NULL,
    EXAM_CLASS            varchar(1) NOT NULL,
    EXAM_ITEM             varchar(2) NOT NULL,
    PATIENT_ID            varchar(64) NULL,
    PATIENT_NAME          varchar(64) NULL,
    SEX                   varchar(1) NULL,
    DATE_OF_BIRTH         timestamp NULL,
    RACE                  varchar(20) NULL,
    UNIT                  varchar(200) NULL,
    MAILING_ADDRESS       varchar(200) NULL,
    ZIP_CODE              varchar(6) NULL,
    PHONE_NUMBER          varchar(16) NULL,
    AGE                   int NULL,
    AGE_UNIT              varchar(10) NULL,
    BODY_HEIGHT           int NULL,
    BODY_HEIGHT_UNIT      varchar(10) NULL,
    BODY_WEIGHT           int NULL,
    BODY_WEIGHT_UNIT      varchar(10) NULL,
    SYSTOLIC_PRESSURE     int NULL,
    DIASTOLIC_PRESSURE    int NULL,
    PRESSURE_UNIT         varchar(10) NULL,
    EXAM_REASON           varchar(200) NULL,
    DOCTOR_ADVICE         varchar(200) NULL,
    CLIN_SYMP             varchar(400) NULL,
    PHYS_SIGN             varchar(400) NULL,
    RELEVANT_LAB_TEST     varchar(200) NULL,
    RELEVANT_DIAG         varchar(400) NULL,
    PATIENT_SOURCE        varchar(1) NULL,
    VISIT_ID              varchar(64) NULL,
    WARD_NO               varchar(32) NULL,
    SICKBED_NO            varchar(32) NULL,
    REQ_DATE_TIME         timestamp NULL,
    REQ_DEPT_NAME         varchar(30) NULL,
    REQ_DEPT              varchar(8) NULL,
    REQ_PHYSICIAN         varchar(64) NULL,
    REQ_MEMO              varchar(400) NULL,
    RESULT_STATUS         varchar(1) NULL,
    CUSTOM_1              varchar(100) NULL,
    CUSTOM_2              varchar(100) NULL,
    MEDICINE_HISTORY      varchar(400) NULL,
    PATIENT_STATEMENT     varchar(400) NULL,
    DISEASE_HISTORY       varchar(400) NULL,
    PRIORITY              varchar(1) NULL,
    FEE                   numeric(10, 2) NULL,
    CURRENCY_UNIT         varchar(3) NULL,
    CONSTRAINT PK_EXAM_APPOINTS PRIMARY KEY (APPOINT_ID, PERFORMED_BY, EXAM_CLASS, EXAM_ITEM),
    CONSTRAINT FK_EXAM_APPOINTS_AGE_UNIT_DICT FOREIGN KEY (AGE_UNIT) REFERENCES AGE_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_BODY_HEIGHT_UNIT_DICT FOREIGN KEY (BODY_HEIGHT_UNIT) REFERENCES BODY_HEIGHT_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_BODY_WEIGHT_UNIT_DICT FOREIGN KEY (BODY_WEIGHT_UNIT) REFERENCES BODY_WEIGHT_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_DEPT_DICT FOREIGN KEY (PERFORMED_BY) REFERENCES DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_EXAM_CLASS_DICT FOREIGN KEY (EXAM_CLASS) REFERENCES EXAM_CLASS_DICT (EXAM_CLASS_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_EXAM_ITEM_DICT FOREIGN KEY (EXAM_ITEM) REFERENCES EXAM_ITEM_DICT (EXAM_ITEM_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_EXAM_RESULT_STATUS_DICT FOREIGN KEY (RESULT_STATUS) REFERENCES EXAM_RESULT_STATUS_DICT (EXAM_RESULT_STATUS_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_PATIENT_SOURCE_DICT FOREIGN KEY (PATIENT_SOURCE) REFERENCES PATIENT_SOURCE_DICT (PATIENT_SOURCE_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_PRESSURE_UNIT_DICT FOREIGN KEY (PRESSURE_UNIT) REFERENCES PRESSURE_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_RACE_DICT FOREIGN KEY (RACE) REFERENCES RACE_DICT (RACE_FLAG),
    CONSTRAINT FK_EXAM_APPOINTS_SEX_DICT FOREIGN KEY (SEX) REFERENCES SEX_DICT (SEX_FLAG),
    CONSTRAINT FK_EXAM_APPOINTS_REQ_DEPT_DICT FOREIGN KEY (REQ_DEPT) REFERENCES REQ_DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_EXAM_PRIORITY_DICT FOREIGN KEY (PRIORITY) REFERENCES EXAM_PRIORITY_DICT (PRIORITY_CODE),
    CONSTRAINT FK_EXAM_APPOINTS_CURRENCY_UNIT_DICT FOREIGN KEY (CURRENCY_UNIT) REFERENCES CURRENCY_UNIT_DICT (UNIT_CODE)
);

CREATE TABLE PATIENT_INFO (
    PATIENT_ID            varchar(64) NOT NULL,
    PATIENT_NAME          varchar(64) NULL,
    SEX                   varchar(1) NULL,
    DATE_OF_BIRTH         timestamp NULL,
    RACE                  varchar(20) NULL,
    UNIT                  varchar(200) NULL,
    MAILING_ADDRESS       varchar(200) NULL,
    ZIP_CODE              varchar(6) NULL,
    PHONE_NUMBER          varchar(30) NULL,
	ITEM_STATUS			  int NOT NULL,
    CONSTRAINT PK_PATIENT_INFO PRIMARY KEY (PATIENT_ID),
    CONSTRAINT FK_PATIENT_INFO_RACE_DICT FOREIGN KEY (RACE) REFERENCES RACE_DICT (RACE_FLAG),
    CONSTRAINT FK_PATIENT_INFO_SEX_DICT FOREIGN KEY (SEX) REFERENCES SEX_DICT (SEX_FLAG),
	CONSTRAINT FK_PATIENT_INFO_ITEM_STATUS_DICT FOREIGN KEY (ITEM_STATUS) REFERENCES ITEM_STATUS_DICT (STATUS_CODE)
);

CREATE TABLE EXAM_MASTER (
     appoint_id varchar(64),
  exam_id varchar(15) NOT NULL,
  performed_by varchar(8) NOT NULL,
  exam_class varchar(1) NOT NULL,
  exam_item varchar(2) NOT NULL,
  patient_id varchar(64) NOT NULL,
  unit varchar(200),
  mailing_address varchar(200),
  zip_code varchar(6),
  phone_number varchar(30),
  age integer,
  age_unit varchar(10),
  body_height integer,
  body_height_unit varchar(10),
  body_weight integer,
  body_weight_unit varchar(10),
  systolic_pressure integer,
  diastolic_pressure integer,
  pressure_unit varchar(10),
  exam_reason varchar(200),
  doctor_advice varchar(200),
  clin_symp varchar(400),
  phys_sign varchar(400),
  relevant_lab_test varchar(200),
  relevant_diag varchar(400),
  patient_source varchar(1),
  visit_id varchar(64),
  ward_no varchar(32),
  sickbed_no varchar(32),
  req_date_time timestamp NULL,
  req_dept_name varchar(30),
  req_dept varchar(8),
  req_physician varchar(64),
  req_memo varchar(400),
  device_id varchar(15),
  exam_date_time timestamp NULL,
  technician varchar(16),
  report_date_time timestamp NULL,
  reporter varchar(16),
  clin_diag varchar(100),
  result_status varchar(1),
  custom_1 varchar(100),
  custom_2 varchar(100),
  medicine_history varchar(400),
  patient_statement varchar(400),
  disease_history varchar(400),
  regist_user_id varchar(16),
  regist_date_time timestamp NULL,
  order_date_time timestamp NULL,
  severity varchar(30),
  pacemake varchar(1),
  print_status varchar(30) DEFAULT 'UNPRINTED'::varchar,
  reason_statements varchar(4000),
  diag_result varchar(4000),
  queue_serial_number varchar(20),
  call_status varchar(30) DEFAULT 'WAITING'::varchar,
  consultation_status varchar(30) DEFAULT 'NONCONSULT'::varchar,
  upload_status varchar(30) DEFAULT 'UNUPLOADED'::varchar,
  export_status varchar(30) DEFAULT 'UNEXPORTED'::varchar,
  change_status varchar(30) DEFAULT 'UNCHANGED'::varchar,
  FILECHANGE_STATUS varchar(30) DEFAULT 'UNCHANGED'::varchar,
  diagsync_status varchar(30),
  filedelivery_status varchar(30) DEFAULT 'UNDELIVERIED'::varchar,
  heart_rate integer,
  p_duration integer,
  pr_interval integer,
  qrs_duration integer,
  qt_interval integer,
  qtc_interval integer,
  p_axis integer,
  qrs_axis integer,
  t_axis integer,
  sv1 integer,
  sv2 integer,
  rv5 integer,
  rv6 integer,
  file_guid varchar(64),
  record_guid varchar(64) NOT NULL,
  priority varchar(1),
  fee numeric(10,2),
  currency_unit varchar(3),
  consultation_purpose varchar(400),
  req_consultation_physician varchar(16),
  req_consultation_date_time timestamp NULL,  
  match_style varchar(30) CONSTRAINT EM_MATCH_STYLE_DEFAULT_VALUE  DEFAULT ('AUTO'),
  match_upload_datetime timestamp NULL,
  public_key varchar(300),
  digital_signature varchar(450),
  CA_SIGNATURE varchar(2000) NULL,
  ELECTRONIC_STAMP bytea NULL,
    CONSTRAINT PK_EXAM_MASTER PRIMARY KEY (EXAM_ID),
    CONSTRAINT FK_EXAM_MASTER_AGE_UNIT_DICT FOREIGN KEY (AGE_UNIT) REFERENCES AGE_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_MASTER_BODY_HEIGHT_UNIT_DICT FOREIGN KEY (BODY_HEIGHT_UNIT) REFERENCES BODY_HEIGHT_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_MASTER_BODY_WEIGHT_UNIT_DICT FOREIGN KEY (BODY_WEIGHT_UNIT) REFERENCES BODY_WEIGHT_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_MASTER_CALL_STATUS_DICT FOREIGN KEY (CALL_STATUS) REFERENCES CALL_STATUS_DICT (CALL_CODE),
    CONSTRAINT FK_EXAM_MASTER_CONSULTATION_STATUS_DICT FOREIGN KEY (CONSULTATION_STATUS) REFERENCES CONSULTATION_STATUS_DICT (CONSULTATION_CODE),
    CONSTRAINT FK_EXAM_MASTER_DEPT_DICT FOREIGN KEY (PERFORMED_BY) REFERENCES DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_EXAM_MASTER_ECG_DEVICES_DICT FOREIGN KEY (DEVICE_ID) REFERENCES ECG_DEVICES_DICT (DEVICE_ID),
    CONSTRAINT FK_EXAM_MASTER_EXAM_CLASS_DICT FOREIGN KEY (EXAM_CLASS) REFERENCES EXAM_CLASS_DICT (EXAM_CLASS_CODE),
    CONSTRAINT FK_EXAM_MASTER_EXAM_ITEM_DICT FOREIGN KEY (EXAM_ITEM) REFERENCES EXAM_ITEM_DICT (EXAM_ITEM_CODE),
    CONSTRAINT FK_EXAM_MASTER_EXAM_RESULT_STATUS_DICT FOREIGN KEY (RESULT_STATUS) REFERENCES EXAM_RESULT_STATUS_DICT (EXAM_RESULT_STATUS_CODE),
    CONSTRAINT FK_EXAM_MASTER_EXPORT_STATUS_DICT FOREIGN KEY (EXPORT_STATUS) REFERENCES EXPORT_STATUS_DICT (EXPORT_CODE),
    CONSTRAINT FK_EXAM_MASTER_CHANGE_STATUS_DICT FOREIGN KEY (CHANGE_STATUS) REFERENCES CHANGE_STATUS_DICT (CHANGE_CODE),
    CONSTRAINT FK_EXAM_MASTER_FILECHANGE_STATUS_DICT FOREIGN KEY (FILECHANGE_STATUS) REFERENCES CHANGE_STATUS_DICT (CHANGE_CODE),
    CONSTRAINT FK_EXAM_MASTER_DIAGSYNC_STATUS_DICT FOREIGN KEY (DIAGSYNC_STATUS) REFERENCES DIAGSYNC_STATUS_DICT (DIAGSYNC_CODE),
    CONSTRAINT FK_EXAM_MASTER_FILEDELIVERY_STATUS_DICT FOREIGN KEY (FILEDELIVERY_STATUS) REFERENCES FILEDELIVERY_STATUS_DICT (FILEDELIVERY_CODE),
    CONSTRAINT FK_EXAM_MASTER_PACEMAKER_DICT FOREIGN KEY (PACEMAKE) REFERENCES PACEMAKER_DICT (PACEMAKER_CODE),
    CONSTRAINT FK_EXAM_MASTER_PATIENT_SOURCE_DICT FOREIGN KEY (PATIENT_SOURCE) REFERENCES PATIENT_SOURCE_DICT (PATIENT_SOURCE_CODE),
    CONSTRAINT FK_EXAM_MASTER_PRESSURE_UNIT_DICT FOREIGN KEY (PRESSURE_UNIT) REFERENCES PRESSURE_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_MASTER_PRINT_STATUS_DICT FOREIGN KEY (PRINT_STATUS) REFERENCES PRINT_STATUS_DICT (PRINT_CODE),
    CONSTRAINT FK_EXAM_MASTER_SEVERITY_DICT FOREIGN KEY (SEVERITY) REFERENCES SEVERITY_DICT (SEVERITY_CODE),
    CONSTRAINT FK_EXAM_MASTER_UPLOAD_STATUS_DICT FOREIGN KEY (UPLOAD_STATUS) REFERENCES UPLOAD_STATUS_DICT (UPLOAD_CODE),
    CONSTRAINT FK_EXAM_MASTER_USERS_REGIST FOREIGN KEY (REGIST_USER_ID) REFERENCES USERS (USER_ID),
    CONSTRAINT FK_EXAM_MASTER_USERS_TECHNICIAN FOREIGN KEY (TECHNICIAN) REFERENCES USERS (USER_ID),
    CONSTRAINT FK_EXAM_MASTER_USERS_REPORTER FOREIGN KEY (REPORTER) REFERENCES USERS (USER_ID),
    CONSTRAINT FK_EXAM_MASTER_EXAM_PRIORITY_DICT FOREIGN KEY (PRIORITY) REFERENCES EXAM_PRIORITY_DICT (PRIORITY_CODE),
    CONSTRAINT FK_EXAM_MASTER_CURRENCY_UNIT_DICT FOREIGN KEY (CURRENCY_UNIT) REFERENCES CURRENCY_UNIT_DICT (UNIT_CODE),
    CONSTRAINT FK_EXAM_MASTER_USERS_REQ_CONSULTATION FOREIGN KEY (REQ_CONSULTATION_PHYSICIAN) REFERENCES USERS (USER_ID),
    CONSTRAINT FK_EXAM_MASTER_REQ_DEPT_DICT FOREIGN KEY (REQ_DEPT) REFERENCES REQ_DEPT_DICT (DEPT_CODE),
    CONSTRAINT FK_EXAM_MASTER_MATCH_STYLE_DICT FOREIGN KEY (MATCH_STYLE) REFERENCES MATCH_STYLE_DICT (MATCH_STYLE_CODE)
);

CREATE TABLE exam_record_collection
(
  appoint_id varchar(64),
  exam_id varchar(15) NOT NULL,
  performed_by varchar(8) NOT NULL,
  exam_class varchar(1) NOT NULL,
  exam_item varchar(2) NOT NULL,
  patient_id varchar(64),
  unit varchar(200),
  mailing_address varchar(200),
  zip_code varchar(6),
  phone_number varchar(16),
  age integer,
  age_unit varchar(10),
  body_height integer,
  body_height_unit varchar(10),
  body_weight integer,
  body_weight_unit varchar(10),
  systolic_pressure integer,
  diastolic_pressure integer,
  pressure_unit varchar(10),
  exam_reason varchar(200),
  doctor_advice varchar(200),
  clin_symp varchar(400),
  phys_sign varchar(400),
  relevant_lab_test varchar(200),
  relevant_diag varchar(400),
  patient_source varchar(1),
  visit_id varchar(64),
  ward_no varchar(32),
  sickbed_no varchar(32),
  req_date_time timestamp NULL,
  req_dept_name varchar(30),
  req_physician varchar(64),
  req_memo varchar(400),
  device_id varchar(15),
  exam_date_time timestamp NULL,
  technician varchar(16),
  report_date_time timestamp NULL,
  reporter varchar(16),
  clin_diag varchar(100),
  result_status varchar(1),
  custom_1 varchar(100),
  custom_2 varchar(100),
  medicine_history varchar(400),
  patient_statement varchar(400),
  disease_history varchar(400),
  regist_user_id varchar(16),
  regist_date_time timestamp NULL,
  order_date_time timestamp NULL,
  severity varchar(30),
  pacemake varchar(1),
  print_status varchar(30),
  reason_statements varchar(4000),
  diag_result varchar(4000),
  queue_serial_number varchar(20),
  call_status varchar(30),
  consultation_status varchar(30),
  upload_status varchar(30),
  export_status varchar(30),
  change_status varchar(30),
  diagsync_status varchar(30),
  filedelivery_status varchar(30),
  heart_rate integer,
  p_duration integer,
  pr_interval integer,
  qrs_duration integer,
  qt_interval integer,
  qtc_interval integer,
  p_axis integer,
  qrs_axis integer,
  t_axis integer,
  file_guid varchar(64),
  record_guid varchar(64) NOT NULL,
  priority varchar(1),
  fee numeric(10,2),
  currency_unit varchar(3),
  consultation_purpose varchar(400),
  req_consultation_physician varchar(16),
  req_consultation_date_time timestamp NULL,
  public_key varchar(300),
  collecttype varchar(100),
  digital_signature varchar(450),
  REQ_DEPT varchar(8),
  collect_time timestamp NULL,
  patient_name varchar(100),
  date_of_birth timestamp NULL,
  race varchar(10),
  sex varchar(2),
  CONSTRAINT fk_exam_master_age_unit_dict FOREIGN KEY (age_unit)
      REFERENCES age_unit_dict (unit_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_body_height_unit_dict FOREIGN KEY (body_height_unit)
      REFERENCES body_height_unit_dict (unit_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_body_weight_unit_dict FOREIGN KEY (body_weight_unit)
      REFERENCES body_weight_unit_dict (unit_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_call_status_dict FOREIGN KEY (call_status)
      REFERENCES call_status_dict (call_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_change_status_dict FOREIGN KEY (change_status)
      REFERENCES change_status_dict (change_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_consultation_status_dict FOREIGN KEY (consultation_status)
      REFERENCES consultation_status_dict (consultation_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_currency_unit_dict FOREIGN KEY (currency_unit)
      REFERENCES currency_unit_dict (unit_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_dept_dict FOREIGN KEY (performed_by)
      REFERENCES dept_dict (dept_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_diagsync_status_dict FOREIGN KEY (diagsync_status)
      REFERENCES diagsync_status_dict (diagsync_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_ecg_devices_dict FOREIGN KEY (device_id)
      REFERENCES ecg_devices_dict (device_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_exam_class_dict FOREIGN KEY (exam_class)
      REFERENCES exam_class_dict (exam_class_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_exam_item_dict FOREIGN KEY (exam_item)
      REFERENCES exam_item_dict (exam_item_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_exam_priority_dict FOREIGN KEY (priority)
      REFERENCES exam_priority_dict (priority_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_exam_result_status_dict FOREIGN KEY (result_status)
      REFERENCES exam_result_status_dict (exam_result_status_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_export_status_dict FOREIGN KEY (export_status)
      REFERENCES export_status_dict (export_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_filedelivery_status_dict FOREIGN KEY (filedelivery_status)
      REFERENCES filedelivery_status_dict (filedelivery_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_pacemaker_dict FOREIGN KEY (pacemake)
      REFERENCES pacemaker_dict (pacemaker_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_patient_source_dict FOREIGN KEY (patient_source)
      REFERENCES patient_source_dict (patient_source_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_pressure_unit_dict FOREIGN KEY (pressure_unit)
      REFERENCES pressure_unit_dict (unit_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_print_status_dict FOREIGN KEY (print_status)
      REFERENCES print_status_dict (print_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_severity_dict FOREIGN KEY (severity)
      REFERENCES severity_dict (severity_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_upload_status_dict FOREIGN KEY (upload_status)
      REFERENCES upload_status_dict (upload_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_users_regist FOREIGN KEY (regist_user_id)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_users_reporter FOREIGN KEY (reporter)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_users_req_consultation FOREIGN KEY (req_consultation_physician)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_exam_master_users_technician FOREIGN KEY (technician)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);


CREATE TABLE EXAM_MASTER_ITEM_DATA (
    SERIAL_NO    SERIAL NOT NULL,
    EXAM_ID      varchar(15) NULL,
    MEDIA        int NOT NULL,
    PATH         varchar(256) NULL,
    DATA_TYPE    varchar(3) NULL,
    GUID         varchar(36) NULL,
    CONSTRAINT PK_EXAM_MASTER_ITEM_DATA PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_MASTER_ITEM_DATA_MEDIA_DICT FOREIGN KEY (MEDIA) REFERENCES MEDIA_DICT (ID)
);

CREATE TABLE EXAM_REASON_HISTORY (
    SERIAL_NO               SERIAL NOT NULL,
    EXAM_ID                 varchar(15) NOT NULL,
    MODIFY_REASON_DATE_TIME timestamp NOT NULL,
    USER_ID                 varchar(16) NOT NULL,
    REASON_STATEMENTS       varchar(4000) NULL,
    CONSTRAINT PK_EXAM_REASON_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_REASON_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_REASON_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_DIAGNOSIS_HISTORY (
    SERIAL_NO        SERIAL NOT NULL,
    EXAM_ID          varchar(15) NOT NULL,
    REPORT_DATE_TIME timestamp NOT NULL,
    USER_ID          varchar(16) NOT NULL,
    CA_SIGNATURE varchar(2000) NULL,
    ELECTRONIC_STAMP bytea NULL,
    DIAG_RESULT      varchar(4000) NULL,
    CONSTRAINT PK_EXAM_DIAGNOSIS_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_DIAGNOSIS_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_DIAGNOSIS_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_UPLOAD_HISTORY (
    SERIAL_NO        SERIAL NOT NULL,
    EXAM_ID          varchar(15) NOT NULL,
    UPLOAD_DATE_TIME timestamp NOT NULL,
    USER_ID          varchar(16) NOT NULL,
    CONSTRAINT PK_EXAM_UPLOAD_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_UPLOAD_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_UPLOAD_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_DOWNLOAD_HISTORY (
    SERIAL_NO          SERIAL NOT NULL,
    EXAM_ID            varchar(15) NOT NULL,
    DOWNLOAD_DATE_TIME timestamp NOT NULL,
    USER_ID            varchar(16) NOT NULL,
    CONSTRAINT PK_EXAM_DOWNLOAD_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_DOWNLOAD_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_DOWNLOAD_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_PATIENT_HISTORY (
    SERIAL_NO                    SERIAL NOT NULL,
    EXAM_ID                      varchar(15) NOT NULL,
    MODIFY_PATIENT_DATE_TIME     timestamp NOT NULL,
    USER_ID                      varchar(16) NOT NULL,
    CONSTRAINT PK_EXAM_PATIENT_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_PATIENT_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_PATIENT_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_MEASURE_HISTORY (
    SERIAL_NO                SERIAL NOT NULL,
    EXAM_ID                  varchar(15) NOT NULL,
    MODIFY_MEASURE_DATE_TIME timestamp NOT NULL,
    USER_ID                  varchar(16) NOT NULL,
    CONSTRAINT PK_EXAM_MEASURE_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_MEASURE_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_MEASURE_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_DISABLE_HISTORY (
    SERIAL_NO          SERIAL NOT NULL,
    EXAM_ID            varchar(15) NOT NULL,
    DISABLE_DATE_TIME  timestamp NOT NULL,
    USER_ID            varchar(16) NOT NULL,
    REASON             varchar(400) NULL,
    CONSTRAINT PK_EXAM_DISABLE_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_DISABLE_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_DISABLE_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_PRINT_HISTORY (
    SERIAL_NO          SERIAL NOT NULL,
    EXAM_ID            varchar(15) NOT NULL,
    PRINT_DATE_TIME    timestamp NOT NULL,
    USER_ID            varchar(16) NOT NULL,
    CONSTRAINT PK_EXAM_PRINT_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_PRINT_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_PRINT_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_READING_HISTORY (
    SERIAL_NO          SERIAL NOT NULL,
    EXAM_ID            varchar(15) NOT NULL,
    READING_DATE_TIME  timestamp NOT NULL,
    USER_ID            varchar(16) NOT NULL,
    CONSTRAINT PK_EXAM_READING_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_READING_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_READING_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_EXPORT_HISTORY (
    SERIAL_NO          SERIAL NOT NULL,
    EXAM_ID            varchar(15) NOT NULL,
    EXPORT_DATE_TIME   timestamp NOT NULL,
    USER_ID            varchar(16) NOT NULL,
    FORMAT             varchar(10) NOT NULL,
    CONSTRAINT PK_EXAM_EXPORT_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_EXPORT_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_EXPORT_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_SAVEREPORT_HISTORY (
    SERIAL_NO            SERIAL NOT NULL,
    EXAM_ID              varchar(15) NOT NULL,
    SAVEREPORT_DATE_TIME timestamp NOT NULL,
    USER_ID              varchar(16) NOT NULL,
    FORMAT               varchar(10) NOT NULL,
    CONSTRAINT PK_EXAM_SAVEREPORT_HISTORY PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_EXAM_SAVEREPORT_HISTORY_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_EXAM_SAVEREPORT_HISTORY_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE MESSAGE_RECORDS
(
    SERIAL_NO          SERIAL NOT NULL,
    SRC_USER_ID        varchar(16) NOT NULL, 
    DEST_USER_ID       varchar(16) NOT NULL, 
    GROUP_ID           int NULL, 
    MESSAGE_NUMBER     varchar(30) NOT NULL, 
    MESSAGE_TYPE       int NOT NULL, 
    MESSAGE_CONTENT    varchar(400), 
    CREATE_DATE        timestamp NOT NULL,
    IS_VALID           int NOT NULL, 
    CONSTRAINT PK_MESSAGE_RECORDS PRIMARY KEY (SERIAL_NO), 
    CONSTRAINT FK_MESSAGE_RECORDS_USERS_SRC FOREIGN KEY (SRC_USER_ID) REFERENCES USERS (USER_ID), 
    CONSTRAINT FK_MESSAGE_RECORDS_USERS_DEST FOREIGN KEY (DEST_USER_ID) REFERENCES USERS (USER_ID), 
    CONSTRAINT FK_MESSAGE_RECORDS_MESSAGE_GROUP FOREIGN KEY (GROUP_ID) REFERENCES MESSAGE_GROUP (GROUP_ID), 
    CONSTRAINT FK_MESSAGE_RECORDS_MESSAGE_TYPE_DICT FOREIGN KEY (MESSAGE_TYPE) REFERENCES MESSAGE_TYPE_DICT (MESSAGE_TYPE_CODE) 
);

CREATE TABLE CONSULT_EXAM_ID_OF_USERS (
    SERIAL_NO          SERIAL NOT NULL,
    EXAM_ID            varchar(15) NOT NULL,
    USER_ID            varchar(16) NOT NULL,
    CONSTRAINT PK_CONSULT_EXAM_ID_OF_USERS PRIMARY KEY (SERIAL_NO),
    CONSTRAINT FK_CONSULT_EXAM_ID_OF_USERS_EXAM_MASTER FOREIGN KEY (EXAM_ID) REFERENCES EXAM_MASTER (EXAM_ID),
    CONSTRAINT FK_CONSULT_EXAM_ID_OF_USERS_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID)
);

CREATE TABLE EXAM_ID_SERIAL(
	SERIAL_NO		SERIAL NOT NULL,
	DATE			timestamp NOT NULL,
	QUEUE_SERIAL_NUMBER int NOT NULL,
	CONSTRAINT PK_EXAM_ID_SERIAL PRIMARY KEY (SERIAL_NO)
);

CREATE TABLE PATIENT_ID_SERIAL(
	SERIAL_NO		SERIAL NOT NULL,
	DATE			timestamp NOT NULL,
	QUEUE_SERIAL_NUMBER int NOT NULL,
	CONSTRAINT PK_PATIENT_ID_SERIAL PRIMARY KEY (SERIAL_NO)
);

CREATE TABLE EXERCISE_ECG_CODE
(
  CODE CHARACTER VARYING(20) NOT NULL,
  ID SERIAL NOT NULL ,
  IS_VALID BOOLEAN,
  SERIAL_NUMBER varchar(50) NOT NULL,
  CONSTRAINT PK_EXERCISE_ECG_CODE PRIMARY KEY (CODE)
);

--创建视图VIEW_EXAM
CREATE VIEW VIEW_EXAM AS
SELECT EXAM_MASTER.PATIENT_ID, EXAM_MASTER.PATIENT_SOURCE, EXAM_MASTER.VISIT_ID, EXAM_MASTER.APPOINT_ID,
       EXAM_MASTER.EXAM_ID, EXAM_MASTER.REQ_DATE_TIME, REQ_DEPT_DICT.DEPT_NAME AS REQ_DEPT_NAME, EXAM_MASTER.REQ_PHYSICIAN,
       EXAM_MASTER.REQ_MEMO, DEPT_DICT.DEPT_NAME, EXAM_CLASS_DICT.EXAM_CLASS_NAME,
       EXAM_ITEM_DICT.EXAM_ITEM_NAME, EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_CODE,
       EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_NAME, EXAM_MASTER.TECHNICIAN, EXAM_MASTER.EXAM_DATE_TIME,
       EXAM_MASTER.REPORTER, EXAM_MASTER.REPORT_DATE_TIME
FROM   EXAM_MASTER
       LEFT JOIN REQ_DEPT_DICT ON EXAM_MASTER.REQ_DEPT = REQ_DEPT_DICT.DEPT_CODE
       INNER JOIN DEPT_DICT ON EXAM_MASTER.PERFORMED_BY = DEPT_DICT.DEPT_CODE
       INNER JOIN EXAM_CLASS_DICT ON EXAM_MASTER.EXAM_CLASS = EXAM_CLASS_DICT.EXAM_CLASS_CODE
       INNER JOIN EXAM_ITEM_DICT ON EXAM_MASTER.EXAM_ITEM = EXAM_ITEM_DICT.EXAM_ITEM_CODE
       INNER JOIN EXAM_RESULT_STATUS_DICT ON EXAM_MASTER.RESULT_STATUS = EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_CODE;

--创建视图VIEW_EXAM_CLASS
CREATE VIEW VIEW_EXAM_CLASS AS
SELECT SERIAL_NO, EXAM_CLASS_CODE, EXAM_CLASS_NAME
FROM   EXAM_CLASS_DICT
WHERE  (IS_VALID = '1');

--创建视图VIEW_EXAM_DATA
CREATE VIEW VIEW_EXAM_DATA AS
SELECT EXAM_MASTER.PATIENT_ID, EXAM_MASTER.PATIENT_SOURCE, EXAM_MASTER.VISIT_ID, EXAM_MASTER.EXAM_ID,
       EXAM_MASTER.APPOINT_ID, EXAM_MASTER.REQ_DATE_TIME, REQ_DEPT_DICT.DEPT_NAME AS REQ_DEPT_NAME,
       EXAM_MASTER.REQ_PHYSICIAN, EXAM_MASTER.REQ_MEMO, DEPT_DICT.DEPT_NAME AS PERFORMED_BY_NAME,
       EXAM_CLASS_DICT.EXAM_CLASS_NAME, EXAM_ITEM_DICT.EXAM_ITEM_NAME,
       EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_CODE, EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_NAME,
       EXAM_MASTER.TECHNICIAN, EXAM_MASTER.EXAM_DATE_TIME, EXAM_MASTER.REPORTER,
       EXAM_MASTER.REPORT_DATE_TIME, EXAM_MASTER_ITEM_DATA.SERIAL_NO, MEDIA_DICT.MEDIA_TYPE,
       MEDIA_DICT.HOST_NAME, MEDIA_DICT.PORT, MEDIA_DICT.USER_NAME, MEDIA_DICT.USER_PWD,
       EXAM_MASTER_ITEM_DATA.PATH, EXAM_MASTER_ITEM_DATA.DATA_TYPE
FROM   EXAM_MASTER
       INNER JOIN EXAM_MASTER_ITEM_DATA ON EXAM_MASTER.EXAM_ID = EXAM_MASTER_ITEM_DATA.EXAM_ID
       LEFT JOIN REQ_DEPT_DICT ON EXAM_MASTER.REQ_DEPT = REQ_DEPT_DICT.DEPT_CODE
       INNER JOIN DEPT_DICT ON EXAM_MASTER.PERFORMED_BY = DEPT_DICT.DEPT_CODE
       INNER JOIN EXAM_CLASS_DICT ON EXAM_MASTER.EXAM_CLASS = EXAM_CLASS_DICT.EXAM_CLASS_CODE
       INNER JOIN EXAM_ITEM_DICT ON EXAM_MASTER.EXAM_ITEM = EXAM_ITEM_DICT.EXAM_ITEM_CODE
       INNER JOIN EXAM_RESULT_STATUS_DICT ON EXAM_MASTER.RESULT_STATUS = EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_CODE
       INNER JOIN MEDIA_DICT ON EXAM_MASTER_ITEM_DATA.MEDIA = MEDIA_DICT.ID;

--创建视图VIEW_EXAM_DEPT
CREATE VIEW VIEW_EXAM_DEPT AS
SELECT DEPT_DICT.SERIAL_NO, DEPT_DICT.DEPT_CODE, DEPT_DICT.DEPT_NAME, DEPT_OI_ATTR_DICT.OI_ATTR_NAME
FROM   DEPT_DICT
       INNER JOIN DEPT_OI_ATTR_DICT ON DEPT_DICT.OUTP_OR_INP = DEPT_OI_ATTR_DICT.OI_ATTR_CODE;

--创建视图VIEW_EXAM_ITEM
CREATE VIEW VIEW_EXAM_ITEM AS
SELECT EXAM_ITEM_DICT.SERIAL_NO, EXAM_ITEM_DICT.EXAM_ITEM_CODE, EXAM_ITEM_DICT.EXAM_ITEM_NAME,
       DEPT_EXAM_ITEM_DICT.DEPT_CODE, DEPT_EXAM_ITEM_DICT.EXAM_CLASS_CODE
FROM   EXAM_ITEM_DICT
       INNER JOIN DEPT_EXAM_ITEM_DICT ON EXAM_ITEM_DICT.EXAM_ITEM_CODE = DEPT_EXAM_ITEM_DICT.EXAM_ITEM_CODE
WHERE  (EXAM_ITEM_DICT.IS_VALID = '1');

--创建视图VIEW_REQUESTED_EXAM
CREATE VIEW VIEW_REQUESTED_EXAM AS
SELECT EXAM_APPOINTS.DOCTOR_ADVICE, EXAM_APPOINTS.FEE, CURRENCY_UNIT_DICT.CURRENCY_UNIT_NAME,
       EXAM_APPOINTS.PRIORITY AS PRIORITY_CODE, EXAM_PRIORITY_DICT.PRIORITY_NAME AS PRIORITY_NAME,
       EXAM_APPOINTS.ID, EXAM_APPOINTS.APPOINT_ID, EXAM_APPOINTS.PATIENT_ID, EXAM_APPOINTS.VISIT_ID,
       PATIENT_SOURCE_DICT.PATIENT_SOURCE_NAME, EXAM_APPOINTS.PATIENT_NAME, SEX_DICT.SEX_NAME AS SEX,
       EXAM_APPOINTS.AGE, AGE_UNIT_DICT.AGE_UNIT_NAME AS AGE_UNIT, DEPT_DICT.DEPT_NAME,
       EXAM_ITEM_DICT.EXAM_ITEM_NAME, REQ_DEPT_DICT.DEPT_NAME AS REQ_DEPT_NAME, EXAM_APPOINTS.REQ_PHYSICIAN,
       EXAM_APPOINTS.REQ_DATE_TIME, EXAM_CLASS_DICT.EXAM_CLASS_NAME, EXAM_APPOINTS.RESULT_STATUS
FROM   EXAM_APPOINTS
       LEFT OUTER JOIN EXAM_PRIORITY_DICT ON EXAM_APPOINTS.PRIORITY = EXAM_PRIORITY_DICT.PRIORITY_CODE
       LEFT JOIN REQ_DEPT_DICT ON EXAM_APPOINTS.REQ_DEPT = REQ_DEPT_DICT.DEPT_CODE
       LEFT OUTER JOIN DEPT_DICT ON EXAM_APPOINTS.PERFORMED_BY = DEPT_DICT.DEPT_CODE
       LEFT OUTER JOIN EXAM_ITEM_DICT ON EXAM_APPOINTS.EXAM_ITEM = EXAM_ITEM_DICT.EXAM_ITEM_CODE
       LEFT OUTER JOIN SEX_DICT ON EXAM_APPOINTS.SEX = SEX_DICT.SEX_FLAG
       LEFT OUTER JOIN EXAM_CLASS_DICT ON EXAM_APPOINTS.EXAM_CLASS = EXAM_CLASS_DICT.EXAM_CLASS_CODE
       LEFT OUTER JOIN PATIENT_SOURCE_DICT ON EXAM_APPOINTS.PATIENT_SOURCE = PATIENT_SOURCE_DICT.PATIENT_SOURCE_CODE
       LEFT OUTER JOIN AGE_UNIT_DICT ON EXAM_APPOINTS.AGE_UNIT = AGE_UNIT_DICT.UNIT_CODE
       LEFT OUTER JOIN CURRENCY_UNIT_DICT ON EXAM_APPOINTS.CURRENCY_UNIT = CURRENCY_UNIT_DICT.UNIT_CODE
WHERE  (EXAM_APPOINTS.RESULT_STATUS = N'0') OR (EXAM_APPOINTS.RESULT_STATUS IS NULL);

--创建视图VIEW_REGISTED_EXAM
CREATE VIEW VIEW_REGISTED_EXAM AS
SELECT EXAM_MASTER.PRIORITY AS PRIORITY_CODE, EXAM_PRIORITY_DICT.PRIORITY_NAME AS PRIORITY_NAME, EXAM_MASTER.APPOINT_ID,
       EXAM_MASTER.EXAM_ID, PATIENT_INFO.PATIENT_ID, EXAM_MASTER.VISIT_ID, EXAM_MASTER.SICKBED_NO,
       PATIENT_SOURCE_DICT.PATIENT_SOURCE_NAME, PATIENT_INFO.PATIENT_NAME, SEX_DICT.SEX_NAME AS SEX,
       EXAM_MASTER.AGE, AGE_UNIT_DICT.AGE_UNIT_NAME AS AGE_UNIT, DEPT_DICT.DEPT_NAME,
       EXAM_CLASS_DICT.EXAM_CLASS_NAME, EXAM_ITEM_DICT.EXAM_ITEM_NAME, REQ_DEPT_DICT.DEPT_NAME AS REQ_DEPT_NAME,
       EXAM_MASTER.REQ_PHYSICIAN, EXAM_MASTER.REGIST_DATE_TIME, CALL_STATUS_DICT.CALL_NAME,
       EXAM_MASTER.QUEUE_SERIAL_NUMBER, EXAM_MASTER.TECHNICIAN, EXAM_MASTER.FEE, 
       CURRENCY_UNIT_DICT.CURRENCY_UNIT_NAME AS CURRENCY_UNIT_NAME, EXAM_MASTER.DOCTOR_ADVICE, EXAM_MASTER.ORDER_DATE_TIME,
       EXAM_MASTER.REQ_DATE_TIME, ECG_DEVICES_DICT.DEVICE_NAME AS DEVICE_NAME
FROM   EXAM_MASTER
       LEFT OUTER JOIN EXAM_PRIORITY_DICT ON EXAM_MASTER.PRIORITY = EXAM_PRIORITY_DICT.PRIORITY_CODE
       LEFT OUTER JOIN PATIENT_SOURCE_DICT ON EXAM_MASTER.PATIENT_SOURCE = PATIENT_SOURCE_DICT.PATIENT_SOURCE_CODE
       LEFT OUTER JOIN EXAM_CLASS_DICT ON EXAM_MASTER.EXAM_CLASS = EXAM_CLASS_DICT.EXAM_CLASS_CODE
       LEFT OUTER JOIN EXAM_ITEM_DICT ON EXAM_MASTER.EXAM_ITEM = EXAM_ITEM_DICT.EXAM_ITEM_CODE
       LEFT OUTER JOIN AGE_UNIT_DICT ON EXAM_MASTER.AGE_UNIT = AGE_UNIT_DICT.UNIT_CODE
       LEFT JOIN REQ_DEPT_DICT ON EXAM_MASTER.REQ_DEPT = REQ_DEPT_DICT.DEPT_CODE
       LEFT OUTER JOIN DEPT_DICT ON EXAM_MASTER.PERFORMED_BY = DEPT_DICT.DEPT_CODE
       LEFT OUTER JOIN CALL_STATUS_DICT ON EXAM_MASTER.CALL_STATUS = CALL_STATUS_DICT.CALL_CODE
       LEFT OUTER JOIN CURRENCY_UNIT_DICT ON EXAM_MASTER.CURRENCY_UNIT = CURRENCY_UNIT_DICT.UNIT_CODE
       LEFT OUTER JOIN ECG_DEVICES_DICT ON EXAM_MASTER.DEVICE_ID = ECG_DEVICES_DICT.DEVICE_ID,
       PATIENT_INFO
       LEFT OUTER JOIN SEX_DICT ON PATIENT_INFO.SEX = SEX_DICT.SEX_FLAG 
WHERE  (EXAM_MASTER.RESULT_STATUS = '1') AND (PATIENT_INFO.PATIENT_ID = EXAM_MASTER.PATIENT_ID);

--创建视图VIEW_EXAMED
 CREATE VIEW VIEW_EXAMED AS
 SELECT EXAM_MASTER.EXAM_ITEM,EXAM_MASTER.PRIORITY AS PRIORITY_CODE, EXAM_PRIORITY_DICT.PRIORITY_NAME AS PRIORITY_NAME,
        EXAM_MASTER.APPOINT_ID, EXAM_MASTER.EXAM_ID, PATIENT_INFO.PATIENT_ID, EXAM_MASTER.VISIT_ID, 
        PATIENT_SOURCE_DICT.PATIENT_SOURCE_NAME, PATIENT_INFO.PATIENT_NAME, 
        SEX_DICT.SEX_NAME AS SEX, EXAM_MASTER.AGE, AGE_UNIT_DICT.AGE_UNIT_NAME AS AGE_UNIT, EXAM_MASTER.SICKBED_NO,
        DEPT_DICT.DEPT_NAME, EXAM_CLASS_DICT.EXAM_CLASS_NAME, EXAM_ITEM_DICT.EXAM_ITEM_NAME, 
        REQ_DEPT_DICT.DEPT_NAME AS REQ_DEPT_NAME, EXAM_MASTER.REQ_PHYSICIAN, EXAM_MASTER.EXAM_DATE_TIME, ECG_DEVICES_DICT.DEVICE_NAME, EXAM_MASTER.DIAG_RESULT, 
        PRINT_STATUS_DICT.PRINT_NAME AS PRINT_STATUS, EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_NAME AS RESULT_STATUS, EXPORT_STATUS_DICT.EXPORT_NAME AS EXPORT_STATUS,
        CHANGE_STATUS_DICT.CHANGE_NAME AS CHANGE_STATUS, UPLOAD_STATUS_DICT.UPLOAD_NAME AS UPLOAD_STATUS,
        EXAM_MASTER.TECHNICIAN, EXAM_MASTER.REPORTER, CONSULTATION_STATUS_DICT.CONSULTATION_NAME AS CONSULTATION,
        EXAM_MASTER.FEE, CURRENCY_UNIT_DICT.CURRENCY_UNIT_NAME, EXAM_MASTER.DOCTOR_ADVICE, SEVERITY_NAME
FROM EXAM_MASTER
   LEFT JOIN EXAM_PRIORITY_DICT ON EXAM_MASTER.PRIORITY = EXAM_PRIORITY_DICT.PRIORITY_CODE
   LEFT JOIN PATIENT_SOURCE_DICT ON EXAM_MASTER.PATIENT_SOURCE = PATIENT_SOURCE_DICT.PATIENT_SOURCE_CODE
   LEFT JOIN EXAM_ITEM_DICT ON EXAM_MASTER.EXAM_ITEM = EXAM_ITEM_DICT.EXAM_ITEM_CODE
   LEFT JOIN ECG_DEVICES_DICT ON EXAM_MASTER.DEVICE_ID = ECG_DEVICES_DICT.DEVICE_ID
   LEFT JOIN AGE_UNIT_DICT ON EXAM_MASTER.AGE_UNIT = AGE_UNIT_DICT.UNIT_CODE
   LEFT JOIN EXAM_CLASS_DICT ON EXAM_MASTER.EXAM_CLASS = EXAM_CLASS_DICT.EXAM_CLASS_CODE
   LEFT JOIN REQ_DEPT_DICT ON EXAM_MASTER.REQ_DEPT = REQ_DEPT_DICT.DEPT_CODE
   LEFT JOIN DEPT_DICT ON EXAM_MASTER.PERFORMED_BY = DEPT_DICT.DEPT_CODE
   LEFT JOIN PRINT_STATUS_DICT ON EXAM_MASTER.PRINT_STATUS = PRINT_STATUS_DICT.PRINT_CODE
   LEFT JOIN EXAM_RESULT_STATUS_DICT ON EXAM_MASTER.RESULT_STATUS = EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_CODE
   LEFT JOIN EXPORT_STATUS_DICT ON EXAM_MASTER.EXPORT_STATUS = EXPORT_STATUS_DICT.EXPORT_CODE
   LEFT JOIN CHANGE_STATUS_DICT ON EXAM_MASTER.CHANGE_STATUS = CHANGE_STATUS_DICT.CHANGE_CODE
   LEFT JOIN UPLOAD_STATUS_DICT ON EXAM_MASTER.UPLOAD_STATUS = UPLOAD_STATUS_DICT.UPLOAD_CODE
   LEFT JOIN CONSULTATION_STATUS_DICT ON CONSULTATION_STATUS_DICT.CONSULTATION_CODE = EXAM_MASTER.CONSULTATION_STATUS
   LEFT JOIN CURRENCY_UNIT_DICT ON EXAM_MASTER.CURRENCY_UNIT = CURRENCY_UNIT_DICT.UNIT_CODE
   LEFT JOIN SEVERITY_DICT ON EXAM_MASTER.SEVERITY = SEVERITY_DICT.SEVERITY_CODE,
PATIENT_INFO
   LEFT JOIN SEX_DICT ON PATIENT_INFO.SEX = SEX_DICT.SEX_FLAG
  WHERE EXAM_MASTER.RESULT_STATUS = '2' AND PATIENT_INFO.PATIENT_ID = EXAM_MASTER.PATIENT_ID;

--创建视图VIEW_EXAM_RETRIEVE
CREATE VIEW VIEW_EXAM_RETRIEVE AS
SELECT EXAM_MASTER.APPOINT_ID, EXAM_MASTER.EXAM_ID, EXAM_MASTER.PATIENT_ID, PATIENT_INFO.PATIENT_NAME,
       SEX_DICT.SEX_NAME, AGE_UNIT_DICT.AGE_UNIT_NAME, EXAM_CLASS_DICT.EXAM_CLASS_NAME,
       PATIENT_SOURCE_DICT.PATIENT_SOURCE_NAME, EXAM_ITEM_DICT.EXAM_ITEM_NAME, EXAM_MASTER.AGE,
       REQ_DEPT_DICT.DEPT_NAME AS REQ_DEPT_NAME, EXAM_MASTER.REQ_PHYSICIAN, EXAM_MASTER.REQ_DATE_TIME,
       EXAM_MASTER.EXAM_DATE_TIME, EXAM_MASTER.TECHNICIAN, EXAM_MASTER.REPORT_DATE_TIME,
       EXAM_MASTER.REPORTER, EXAM_MASTER.REGIST_DATE_TIME, EXAM_MASTER.ORDER_DATE_TIME, EXAM_MASTER.VISIT_ID,
       EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_NAME, ECG_DEVICES_DICT.DEVICE_NAME, DEPT_DICT.DEPT_NAME,
       EXAM_MASTER.DIAG_RESULT, SEVERITY_DICT.SEVERITY_NAME, PRINT_STATUS_DICT.PRINT_NAME,
       CONSULTATION_STATUS_DICT.CONSULTATION_NAME AS CONSULTATION
FROM   EXAM_MASTER
       INNER JOIN EXAM_CLASS_DICT ON EXAM_MASTER.EXAM_CLASS = EXAM_CLASS_DICT.EXAM_CLASS_CODE
       INNER JOIN EXAM_ITEM_DICT ON EXAM_MASTER.EXAM_ITEM = EXAM_ITEM_DICT.EXAM_ITEM_CODE
       LEFT JOIN REQ_DEPT_DICT ON EXAM_MASTER.REQ_DEPT = REQ_DEPT_DICT.DEPT_CODE
       INNER JOIN DEPT_DICT ON EXAM_MASTER.PERFORMED_BY = DEPT_DICT.DEPT_CODE
       LEFT OUTER JOIN EXAM_RESULT_STATUS_DICT ON EXAM_MASTER.RESULT_STATUS = EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_CODE
       LEFT OUTER JOIN ECG_DEVICES_DICT ON DEPT_DICT.DEPT_CODE = ECG_DEVICES_DICT.DEPT_CODE AND EXAM_MASTER.DEVICE_ID = ECG_DEVICES_DICT.DEVICE_ID
       LEFT OUTER JOIN PATIENT_SOURCE_DICT ON EXAM_MASTER.PATIENT_SOURCE = PATIENT_SOURCE_DICT.PATIENT_SOURCE_CODE
       LEFT OUTER JOIN AGE_UNIT_DICT ON EXAM_MASTER.AGE_UNIT = AGE_UNIT_DICT.UNIT_CODE
       LEFT OUTER JOIN SEVERITY_DICT ON EXAM_MASTER.SEVERITY = SEVERITY_DICT.SEVERITY_CODE
       LEFT OUTER JOIN PRINT_STATUS_DICT ON EXAM_MASTER.PRINT_STATUS = PRINT_STATUS_DICT.PRINT_CODE
       LEFT OUTER JOIN CONSULTATION_STATUS_DICT ON CONSULTATION_STATUS_DICT.CONSULTATION_CODE = EXAM_MASTER.CONSULTATION_STATUS,
       PATIENT_INFO INNER JOIN SEX_DICT ON PATIENT_INFO.SEX = SEX_DICT.SEX_FLAG
WHERE  (PATIENT_INFO.PATIENT_ID = EXAM_MASTER.PATIENT_ID);

--创建视图VIEW_USER_ADDED_RIGHT
CREATE VIEW VIEW_USER_ADDED_RIGHT AS
SELECT USERS.USER_ID, RIGHT_DETAIL.RIGHT_ID, RIGHT_DETAIL.RIGHT_NAME
FROM   USERS
       INNER JOIN ADDED_RIGHT_OF_USER ON USERS.USER_ID = ADDED_RIGHT_OF_USER.USER_ID
       INNER JOIN RIGHT_DETAIL ON ADDED_RIGHT_OF_USER.RIGHT_ID = RIGHT_DETAIL.RIGHT_ID
WHERE  (ADDED_RIGHT_OF_USER.IS_ALLOW = 1);

--创建视图VIEW_USER_DEFAULT_RIGHT
CREATE VIEW VIEW_USER_DEFAULT_RIGHT AS
SELECT USERS.USER_ID, RIGHT_DETAIL.RIGHT_ID, RIGHT_DETAIL.RIGHT_NAME
FROM USERS
     INNER JOIN ROLE_OF_USER ON USERS.USER_ID = ROLE_OF_USER.USER_ID
     INNER JOIN RIGHT_OF_ROLE ON ROLE_OF_USER.CLASS_ID = RIGHT_OF_ROLE.ROLE_CLASS_ID
     INNER JOIN RIGHT_DETAIL ON RIGHT_OF_ROLE.RIGHT_ID = RIGHT_DETAIL.RIGHT_ID
     INNER JOIN ROLE_CLASS ON RIGHT_OF_ROLE.ROLE_CLASS_ID = ROLE_CLASS.CLASS_ID
WHERE (ROLE_CLASS.IS_VALID = 1);

--创建视图VIEW_USER_REMOVED_RIGHT
CREATE VIEW VIEW_USER_REMOVED_RIGHT AS
SELECT USERS.USER_ID, RIGHT_DETAIL.RIGHT_ID, RIGHT_DETAIL.RIGHT_NAME
FROM   USERS
       INNER JOIN ADDED_RIGHT_OF_USER ON USERS.USER_ID = ADDED_RIGHT_OF_USER.USER_ID
       INNER JOIN RIGHT_DETAIL ON ADDED_RIGHT_OF_USER.RIGHT_ID = RIGHT_DETAIL.RIGHT_ID
WHERE  (ADDED_RIGHT_OF_USER.IS_ALLOW = 0);

--创建视图VIEW_USER_RIGHT
CREATE VIEW VIEW_USER_RIGHT AS 
SELECT ROLE_OF_USER.USER_ID, RIGHT_OF_ROLE.RIGHT_ID, RIGHT_OF_ROLE.DEPT_CODE, RIGHT_DETAIL.RIGHT_NAME
FROM   RIGHT_OF_ROLE
       JOIN ROLE_CLASS ON RIGHT_OF_ROLE.ROLE_CLASS_ID = ROLE_CLASS.CLASS_ID
       JOIN RIGHT_DETAIL ON RIGHT_OF_ROLE.RIGHT_ID = RIGHT_DETAIL.RIGHT_ID
       JOIN ROLE_OF_USER ON ROLE_CLASS.CLASS_ID = ROLE_OF_USER.CLASS_ID
WHERE (RIGHT_OF_ROLE.ROLE_CLASS_ID IN ( SELECT ROLE_OF_USER_NICKNAME.CLASS_ID
       FROM ROLE_OF_USER ROLE_OF_USER_NICKNAME WHERE ROLE_CLASS.IS_VALID = 1));

--创建视图VIEW_PATINFO_EXAMMASTER_EXAM
CREATE VIEW VIEW_PATINFO_EXAMMASTER_EXAM AS
SELECT FILEDELIVERY_STATUS, FILEDELIVERY_NAME, EXAM_DISABLE_HISTORY.DISABLE_DATE_TIME, USERS_DISABLE.USER_NAME AS DISABLE_USER_NAME, EXAM_DISABLE_HISTORY.REASON,
       EXAM_MASTER.FEE, CURRENCY_UNIT_DICT.CURRENCY_UNIT_NAME, EXAM_MASTER.DOCTOR_ADVICE,EXAM_MASTER.DIAGSYNC_STATUS,
       EXAM_MASTER.EXAM_DATE_TIME, EXAM_MASTER.DIAG_RESULT, REQ_DEPT_DICT.DEPT_NAME AS REQ_DEPT_NAME, EXAM_MASTER.SICKBED_NO, 
       EXAM_MASTER.VISIT_ID, PATIENT_SOURCE_DICT.PATIENT_SOURCE_NAME, EXAM_MASTER.EXAM_ID, EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_NAME,
       UPLOAD_STATUS_DICT.UPLOAD_NAME, EXPORT_STATUS_DICT.EXPORT_NAME, CHANGE_STATUS_DICT.CHANGE_NAME, PRINT_STATUS_DICT.PRINT_NAME, EXAM_CLASS_DICT.EXAM_CLASS_NAME,
       EXAM_ITEM_DICT.EXAM_ITEM_NAME, DEPT_DICT.DEPT_NAME, ECG_DEVICES_DICT.DEVICE_NAME, USERS_TECHNICIAN.USER_NAME AS TECHNICIAN_NAME, P.PATIENT_ID, P.PATIENT_NAME,
       SEX_DICT.SEX_NAME, AGE_UNIT_DICT.AGE_UNIT_NAME, EXAM_MASTER.APPOINT_ID, EXAM_MASTER.PERFORMED_BY, EXAM_MASTER.EXAM_CLASS,
       EXAM_MASTER.EXAM_ITEM, P.UNIT, P.MAILING_ADDRESS, P.ZIP_CODE, P.PHONE_NUMBER, EXAM_MASTER.AGE, EXAM_MASTER.AGE_UNIT,
       EXAM_MASTER.BODY_HEIGHT, EXAM_MASTER.BODY_HEIGHT_UNIT, EXAM_MASTER.BODY_WEIGHT, EXAM_MASTER.BODY_WEIGHT_UNIT,
       EXAM_MASTER.SYSTOLIC_PRESSURE, EXAM_MASTER.DIASTOLIC_PRESSURE, EXAM_MASTER.PRESSURE_UNIT, EXAM_MASTER.EXAM_REASON,
       EXAM_MASTER.CLIN_SYMP, EXAM_MASTER.PHYS_SIGN, EXAM_MASTER.RELEVANT_LAB_TEST, EXAM_MASTER.RELEVANT_DIAG,
       EXAM_MASTER.PATIENT_SOURCE, EXAM_MASTER.WARD_NO, EXAM_MASTER.REQ_DATE_TIME, EXAM_MASTER.REQ_PHYSICIAN,
       EXAM_MASTER.REQ_MEMO, EXAM_MASTER.DEVICE_ID, EXAM_MASTER.TECHNICIAN, EXAM_MASTER.REPORT_DATE_TIME,
       EXAM_MASTER.REPORTER, EXAM_MASTER.CLIN_DIAG, EXAM_MASTER.RESULT_STATUS, EXAM_MASTER.CUSTOM_1, EXAM_MASTER.CUSTOM_2,
       EXAM_MASTER.MEDICINE_HISTORY, EXAM_MASTER.PATIENT_STATEMENT, EXAM_MASTER.DISEASE_HISTORY, EXAM_MASTER.REGIST_USER_ID,
       EXAM_MASTER.REGIST_DATE_TIME, EXAM_MASTER.SEVERITY, SEVERITY_DICT.SEVERITY_NAME AS SEVERITY_NAME, 
       EXAM_MASTER.PACEMAKE, EXAM_MASTER.PRINT_STATUS, EXAM_MASTER.QUEUE_SERIAL_NUMBER, EXAM_MASTER.CALL_STATUS, 
       EXAM_MASTER.CONSULTATION_STATUS, EXAM_MASTER.UPLOAD_STATUS, EXAM_MASTER.EXPORT_STATUS, EXAM_MASTER.CHANGE_STATUS, CHANGE_STATUS_DICT.CHANGE_NAME AS CHANGE_STATUS_NAME,
       EXAM_MASTER.HEART_RATE, EXAM_MASTER.P_DURATION, EXAM_MASTER.PR_INTERVAL, EXAM_MASTER.QRS_DURATION, EXAM_MASTER.QT_INTERVAL,
       EXAM_MASTER.QTC_INTERVAL, EXAM_MASTER.P_AXIS, EXAM_MASTER.QRS_AXIS, EXAM_MASTER.T_AXIS, EXAM_MASTER.FILE_GUID, P.RACE,
       RACE_DICT.RACE_NAME, P.DATE_OF_BIRTH, CONSULTATION_STATUS_DICT.CONSULTATION_NAME, EXAM_MASTER.PRIORITY AS PRIORITY_CODE, 
       EXAM_PRIORITY_DICT.PRIORITY_NAME AS PRIORITY_NAME, EXAM_MASTER.REQ_CONSULTATION_PHYSICIAN, EXAM_MASTER.REQ_CONSULTATION_DATE_TIME,
       EXAM_MASTER.CONSULTATION_PURPOSE, USERS_REQ_CONSULTATION.USER_NAME AS REQ_CONSULTATION_NAME, USERS_REPORTER.USER_NAME AS REPORTER_NAME,
       EXAM_MASTER.RECORD_GUID
FROM EXAM_MASTER
       JOIN EXAM_CLASS_DICT ON EXAM_MASTER.EXAM_CLASS = EXAM_CLASS_DICT.EXAM_CLASS_CODE
       JOIN EXAM_ITEM_DICT ON EXAM_MASTER.EXAM_ITEM = EXAM_ITEM_DICT.EXAM_ITEM_CODE
       LEFT JOIN REQ_DEPT_DICT ON EXAM_MASTER.REQ_DEPT = REQ_DEPT_DICT.DEPT_CODE
       JOIN DEPT_DICT ON EXAM_MASTER.PERFORMED_BY = DEPT_DICT.DEPT_CODE
       LEFT JOIN FILEDELIVERY_STATUS_DICT ON FILEDELIVERY_STATUS_DICT.FILEDELIVERY_CODE = FILEDELIVERY_STATUS
       LEFT JOIN EXAM_DISABLE_HISTORY ON EXAM_MASTER.EXAM_ID = EXAM_DISABLE_HISTORY.EXAM_ID 
       LEFT JOIN EXAM_RESULT_STATUS_DICT ON EXAM_MASTER.RESULT_STATUS = EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_CODE
       LEFT JOIN ECG_DEVICES_DICT ON DEPT_DICT.DEPT_CODE = ECG_DEVICES_DICT.DEPT_CODE AND EXAM_MASTER.DEVICE_ID = ECG_DEVICES_DICT.DEVICE_ID
       LEFT JOIN PATIENT_SOURCE_DICT ON EXAM_MASTER.PATIENT_SOURCE = PATIENT_SOURCE_DICT.PATIENT_SOURCE_CODE
       LEFT JOIN AGE_UNIT_DICT ON EXAM_MASTER.AGE_UNIT = AGE_UNIT_DICT.UNIT_CODE
       LEFT JOIN SEVERITY_DICT ON EXAM_MASTER.SEVERITY = SEVERITY_DICT.SEVERITY_CODE
       LEFT JOIN PRINT_STATUS_DICT ON EXAM_MASTER.PRINT_STATUS = PRINT_STATUS_DICT.PRINT_CODE
       LEFT JOIN UPLOAD_STATUS_DICT ON EXAM_MASTER.UPLOAD_STATUS = UPLOAD_STATUS_DICT.UPLOAD_CODE
       LEFT JOIN EXPORT_STATUS_DICT ON EXPORT_STATUS_DICT.EXPORT_CODE = EXAM_MASTER.EXPORT_STATUS
       LEFT JOIN CHANGE_STATUS_DICT ON CHANGE_STATUS_DICT.CHANGE_CODE = EXAM_MASTER.CHANGE_STATUS
       LEFT JOIN CONSULTATION_STATUS_DICT ON CONSULTATION_STATUS_DICT.CONSULTATION_CODE = EXAM_MASTER.CONSULTATION_STATUS
       LEFT JOIN USERS AS USERS_TECHNICIAN ON USERS_TECHNICIAN.USER_ID = EXAM_MASTER.TECHNICIAN
       LEFT JOIN USERS AS USERS_REPORTER ON USERS_REPORTER.USER_ID = EXAM_MASTER.REPORTER
       LEFT JOIN USERS AS USERS_REQ_CONSULTATION ON USERS_REQ_CONSULTATION.USER_ID = EXAM_MASTER.REQ_CONSULTATION_PHYSICIAN
       LEFT JOIN USERS AS USERS_DISABLE ON USERS_DISABLE.USER_ID = EXAM_DISABLE_HISTORY.USER_ID
       LEFT JOIN EXAM_PRIORITY_DICT ON EXAM_MASTER.PRIORITY = EXAM_PRIORITY_DICT.PRIORITY_CODE
       LEFT JOIN CURRENCY_UNIT_DICT ON EXAM_MASTER.CURRENCY_UNIT = CURRENCY_UNIT_DICT.UNIT_CODE,
     PATIENT_INFO P
       LEFT JOIN SEX_DICT ON SEX_DICT.SEX_FLAG = P.SEX
       LEFT JOIN RACE_DICT ON RACE_DICT.RACE_FLAG = P.RACE
WHERE P.PATIENT_ID = EXAM_MASTER.PATIENT_ID;


--创建视图view_case_colection
CREATE  VIEW VIEW_CASE_COLECTION AS 
 SELECT EXAMRECORD.DATE_OF_BIRTH, EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_NAME,
 EXAMRECORD.COLLECT_TIME, EXAMRECORD.COLLECTTYPE, EXAMRECORD.EXAM_ID, EXAMRECORD.PATIENT_NAME, 
SEX_DICT.SEX_NAME AS SEX, EXAMRECORD.AGE, AGE_UNIT_DICT.AGE_UNIT_NAME AS AGE_UNIT, EXAMRECORD.SICKBED_NO,
 DEPT_DICT.DEPT_NAME, EXAM_CLASS_DICT.EXAM_CLASS_NAME, EXAM_ITEM_DICT.EXAM_ITEM_NAME, 
EXAMRECORD.REQ_DEPT_NAME, EXAMRECORD.EXAM_DATE_TIME, EXAMRECORD.DIAG_RESULT, EXAMRECORD.CLIN_DIAG, 
EXAMRECORD.MEDICINE_HISTORY, EXAMRECORD.DISEASE_HISTORY
   FROM EXAM_RECORD_COLLECTION EXAMRECORD
   LEFT JOIN EXAM_ITEM_DICT ON EXAMRECORD.EXAM_ITEM = EXAM_ITEM_DICT.EXAM_ITEM_CODE
   LEFT JOIN EXAM_RESULT_STATUS_DICT ON EXAMRECORD.RESULT_STATUS = EXAM_RESULT_STATUS_DICT.EXAM_RESULT_STATUS_CODE
   LEFT JOIN AGE_UNIT_DICT ON EXAMRECORD.AGE_UNIT = AGE_UNIT_DICT.UNIT_CODE
   LEFT JOIN EXAM_CLASS_DICT ON EXAMRECORD.EXAM_CLASS = EXAM_CLASS_DICT.EXAM_CLASS_CODE
   LEFT JOIN DEPT_DICT ON EXAMRECORD.PERFORMED_BY = DEPT_DICT.DEPT_CODE
   LEFT JOIN SEX_DICT ON EXAMRECORD.SEX = SEX_DICT.SEX_FLAG;