---------------------------------------------------------------------------------------------- ---------- HOSPITAL TABLE 생성 ---------------------------------------------------------------------------------------------- CREATE TABLE MP_ACCESS_HISTORY ( HISTORY_SEQ NUMBER(20,0) DEFAULT 0, USER_ID VARCHAR2 (255), --- DEVICE_UUID_ID VARCHAR2(255), MENU_ID VARCHAR2(255), MENU_NAME VARCHAR2(255), REQ_URI_ADDR VARCHAR2(1024), REMOTE_IP_ADDR VARCHAR2(1024), ACCESS_DT DATE, PLATFORM_TYPE VARCHAR2(50), BROWSER_VALUE VARCHAR2(255), SERVER_IP_ADDR VARCHAR2(1024), ACCESS_HASH_VALUE VARCHAR2(255) ); COMMENT ON TABLE MP_ACCESS_HISTORY IS '사용자의 접근 이력'; CREATE SEQUENCE SEQ_MP_ACCESS_HISTORY_HS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_ACCESS_HISTORY.HISTORY_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_ACCESS_HISTORY.USER_ID IS '사용자 ID'; --- COMMENT ON COLUMN MP_ACCESS_HISTORY.DEVICE_UUID_ID IS '장비 고유식별자 or IP'; COMMENT ON COLUMN MP_ACCESS_HISTORY.MENU_ID IS 'access원하는 메뉴식별자'; COMMENT ON COLUMN MP_ACCESS_HISTORY.MENU_NAME IS 'access원하는 메뉴명 (메뉴가 동적으로 바뀔 수 있으므로 이름백업)'; COMMENT ON COLUMN MP_ACCESS_HISTORY.REQ_URI_ADDR IS 'access 주소 (ui의 action)'; COMMENT ON COLUMN MP_ACCESS_HISTORY.REMOTE_IP_ADDR IS 'client의 ip'; COMMENT ON COLUMN MP_ACCESS_HISTORY.ACCESS_DT IS '요청일시'; COMMENT ON COLUMN MP_ACCESS_HISTORY.PLATFORM_TYPE IS 'http 요청의 user agent 분석을 통해 결정되는 플랫폼 유형'; COMMENT ON COLUMN MP_ACCESS_HISTORY.BROWSER_VALUE IS 'http 요청의 user agent 분석을 통해 결정되는 브라우저 유형'; COMMENT ON COLUMN MP_ACCESS_HISTORY.SERVER_IP_ADDR IS '서버주소'; COMMENT ON COLUMN MP_ACCESS_HISTORY.ACCESS_HASH_VALUE IS '접근이력 위변조 방지용'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_ACCESS ( ACCESS_SEQ NUMBER(20,0), MENU_ID VARCHAR2(255), MENU_NAME VARCHAR2(255), REQ_URI_ADDR VARCHAR2(1024), HIT_CNT NUMBER(*,0), AGG_DT DATE, CONSTRAINT UQ_ACCESS_DT_MENU_ID UNIQUE (AGG_DT, MENU_ID) ); COMMENT ON TABLE MP_AGG_ACCESS IS '메뉴 접근에 대한 통계'; CREATE SEQUENCE SEQ_MP_AGG_ACCESS_AS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AGG_ACCESS.ACCESS_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_ACCESS.MENU_ID IS '메뉴ID'; COMMENT ON COLUMN MP_AGG_ACCESS.MENU_NAME IS '메뉴명'; COMMENT ON COLUMN MP_AGG_ACCESS.REQ_URI_ADDR IS 'access 주소 (ui의 action)'; COMMENT ON COLUMN MP_AGG_ACCESS.HIT_CNT IS '방문횟수'; COMMENT ON COLUMN MP_AGG_ACCESS.AGG_DT IS '통계일시 (이 시간에 해당 메뉴에 얼마나 방문했는지)'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_DAILY ( DAILY_SEQ NUMBER(20,0), AGG_DT DATE, HIT_CNT NUMBER(*,0), WEEK_ORDER NUMBER(*,0), CONSTRAINT UQ_AGG_DT_WEEK_ORDER UNIQUE (AGG_DT, WEEK_ORDER) ); COMMENT ON TABLE MP_AGG_DAILY IS '날짜별, 요일별 접근 통계'; CREATE SEQUENCE SEQ_MP_AGG_DAILY_DS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AGG_DAILY.DAILY_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_DAILY.AGG_DT IS '통계일시 (이 시간에 해당 메뉴에 얼마나 방문했는지)'; COMMENT ON COLUMN MP_AGG_DAILY.HIT_CNT IS '방문횟수'; COMMENT ON COLUMN MP_AGG_DAILY.WEEK_ORDER IS '무슨요일인지'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_HOURLY ( HOURLY_SEQ NUMBER(20,0), AGG_DT DATE, HIT_CNT NUMBER(*,0), TIME_ORDER NUMBER(*,0), CONSTRAINT UQ_AGG_DT_TIME_ORDER UNIQUE (AGG_DT, TIME_ORDER) ); COMMENT ON TABLE MP_AGG_HOURLY IS '시간별 접근 통계'; CREATE SEQUENCE SEQ_MP_AGG_HOURLY_HS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AGG_HOURLY.HOURLY_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_HOURLY.AGG_DT IS '통계일시 (이 시간에 해당 메뉴에 얼마나 방문했는지)'; COMMENT ON COLUMN MP_AGG_HOURLY.HIT_CNT IS '방문횟수'; COMMENT ON COLUMN MP_AGG_HOURLY.TIME_ORDER IS '몇시인지'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_PLATFORM ( PLATFORM_SEQ NUMBER(20,0), PLATFORM_TYPE VARCHAR2(50), HIT_CNT NUMBER(*,0), AGG_DT DATE, CONSTRAINT UQ_AGG_DT_PLATFROM_TYPE UNIQUE (AGG_DT, PLATFORM_TYPE) ); COMMENT ON TABLE MP_AGG_PLATFORM IS '사용자 플랫폼별 접근 통계'; CREATE SEQUENCE SEQ_MP_AGG_PLATFORM_PS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AGG_PLATFORM.PLATFORM_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_PLATFORM.PLATFORM_TYPE IS '플랫폼'; COMMENT ON COLUMN MP_AGG_PLATFORM.AGG_DT IS '통계일시 (이 시간에 해당 메뉴에 얼마나 방문했는지)'; COMMENT ON COLUMN MP_AGG_PLATFORM.HIT_CNT IS '방문횟수'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_API_CATEGORY ( CAT_SEQ NUMBER(20,0), CAT_NAME VARCHAR2(255), CAT_DESC VARCHAR2(500), PARENT_CAT_SEQ NUMBER(20,0), PATH_NAME VARCHAR2(255), CREATE_DT DATE, CREATE_ID VARCHAR2(255), UPDATE_DT DATE, UPDATE_ID VARCHAR2(255), CONSTRAINT PK_MP_API_CATEGORY PRIMARY KEY (CAT_SEQ), CONSTRAINT UQ_API_CAT_PATH_NAME UNIQUE(PATH_NAME) ); COMMENT ON TABLE MP_API_CATEGORY IS 'API를 묶어주는 카테고리'; CREATE SEQUENCE SEQ_MP_API_CATEGORY_CS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_API_CATEGORY.CAT_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_API_CATEGORY.CAT_NAME IS '카테고리명'; COMMENT ON COLUMN MP_API_CATEGORY.CAT_DESC IS '설명'; COMMENT ON COLUMN MP_API_CATEGORY.PARENT_CAT_SEQ IS '부모 시퀀스'; COMMENT ON COLUMN MP_API_CATEGORY.PATH_NAME IS '카테고리 경로'; COMMENT ON COLUMN MP_API_CATEGORY.CREATE_DT IS '생성일시'; COMMENT ON COLUMN MP_API_CATEGORY.CREATE_ID IS '생성자'; COMMENT ON COLUMN MP_API_CATEGORY.UPDATE_DT IS '수정일시'; COMMENT ON COLUMN MP_API_CATEGORY.UPDATE_ID IS '수정자'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_API ( API_SEQ NUMBER(20,0), API_TYPE VARCHAR2(50), DATA_SOURCE_NAME VARCHAR2(255), API_DESC VARCHAR2(500), HTTP_METHOD_TYPE VARCHAR2(50), API_NAME VARCHAR2(255), TARGET_NAME VARCHAR2(255), QUERY_MSG VARCHAR2(2048), RESULT_TYPE VARCHAR2(50), REQ_URL_ADDR VARCHAR2(1024), TARGET_URL_ADDR VARCHAR2(1024), RES_SAMPLE_CL CLOB, CAT_SEQ NUMBER(20,0), CAT_PATH_NAME VARCHAR2(255), CREATE_DT DATE, CREATE_ID VARCHAR2(255), UPDATE_DT DATE, UPDATE_ID VARCHAR2(255), CONSTRAINT PK_MP_API PRIMARY KEY (API_SEQ), CONSTRAINT UQ_API_CAT_SEQ UNIQUE (CAT_PATH_NAME, REQ_URL_ADDR), CONSTRAINT FK_API_CAT_SEQ FOREIGN KEY (CAT_SEQ) REFERENCES MP_API_CATEGORY (CAT_SEQ) ); COMMENT ON TABLE MP_API IS 'API정보'; CREATE SEQUENCE SEQ_MP_API_AS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_API.API_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_API.API_TYPE IS '유형 (SQL, WEB SERVICE..)'; COMMENT ON COLUMN MP_API.DATA_SOURCE_NAME IS 'SQL타입인 경우 사용할 DataSource명'; COMMENT ON COLUMN MP_API.API_DESC IS '설명'; COMMENT ON COLUMN MP_API.HTTP_METHOD_TYPE IS 'HTTP통신이 사용되는 경우 메소드 유형 (GET/PUT/POST/DELETE)'; COMMENT ON COLUMN MP_API.API_NAME IS '이름'; COMMENT ON COLUMN MP_API.TARGET_NAME IS '프로시저명'; COMMENT ON COLUMN MP_API.QUERY_MSG IS 'DB연동인 경우 질의문'; COMMENT ON COLUMN MP_API.RESULT_TYPE IS '결과유형 (MAP, LIST, INT..)'; COMMENT ON COLUMN MP_API.REQ_URL_ADDR IS '요청과 맵핑되는 주소 (category가 제외된 형태)'; COMMENT ON COLUMN MP_API.TARGET_URL_ADDR IS 'HTTP 통신인 경우, API가 호출해야 하는 상대편 웹 서비스 주소 (파라미터 포함)'; COMMENT ON COLUMN MP_API.RES_SAMPLE_CL IS '응답샘플'; COMMENT ON COLUMN MP_API.CAT_SEQ IS 'API가 어느 카테고리에 포함되는지 시퀀스'; COMMENT ON COLUMN MP_API.CAT_PATH_NAME IS 'API가 포함되는 카테고리 경로명 (반정규화)'; COMMENT ON COLUMN MP_API.CREATE_DT IS '생성일시'; COMMENT ON COLUMN MP_API.CREATE_ID IS '생성자'; COMMENT ON COLUMN MP_API.UPDATE_DT IS '수정일시'; COMMENT ON COLUMN MP_API.UPDATE_ID IS '수정자'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_API_HEADER ( HEADER_SEQ NUMBER(20,0), API_SEQ NUMBER(20,0), HEADER_NAME VARCHAR2(255), HEADER_VALUE VARCHAR2(255), CONSTRAINT FK_API_HEADER_API_SEQ FOREIGN KEY (API_SEQ) REFERENCES MP_API (API_SEQ) ); COMMENT ON TABLE MP_API_HEADER IS 'API 헤더 정보'; CREATE SEQUENCE SEQ_MP_API_HEADER_HS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_API_HEADER.HEADER_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_API_HEADER.API_SEQ IS '어떤 API의 헤더인지에 대한 시퀀스'; COMMENT ON COLUMN MP_API_HEADER.HEADER_NAME IS '헤더명'; COMMENT ON COLUMN MP_API_HEADER.HEADER_VALUE IS '헤더값'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_API_PARAM ( PARAM_SEQ NUMBER(20,0), API_SEQ NUMBER(20,0), DATA_TYPE VARCHAR2(50), PARAM_DESC VARCHAR2(500), PARAM_NAME VARCHAR2(255), SAMPLE_VALUE VARCHAR2(4000), CONSTRAINT FK_API_PARAM_API_SEQ FOREIGN KEY (API_SEQ) REFERENCES MP_API (API_SEQ) ); COMMENT ON TABLE MP_API_PARAM IS 'API 파라미터 정보'; CREATE SEQUENCE SEQ_MP_API_PARAM_PS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_API_PARAM.PARAM_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_API_PARAM.API_SEQ IS '어떤 API에 대한 파라미터인지'; COMMENT ON COLUMN MP_API_PARAM.DATA_TYPE IS '파라미터의 데이터 타입'; COMMENT ON COLUMN MP_API_PARAM.PARAM_DESC IS '설명'; COMMENT ON COLUMN MP_API_PARAM.PARAM_NAME IS '이름'; COMMENT ON COLUMN MP_API_PARAM.SAMPLE_VALUE IS '샘플 값'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_MENU ( MENU_ID VARCHAR2(255), PARENT_MENU_ID VARCHAR2(255), MENU_ORDER NUMBER(*,0), MENU_TYPE VARCHAR2(50) DEFAULT 'MAIN', MENU_NAME VARCHAR2(255), ENABLED_YN CHAR(1) DEFAULT 'N', ACCESS_URI_ADDR VARCHAR2(1024), IMAGE_URI_ADDR VARCHAR2(1024), MENU_DESC VARCHAR2(500), AUTH_YN CHAR(1) DEFAULT 'Y', AUTH_VIEW_YN CHAR(1) DEFAULT 'N', AGG_YN CHAR(1) DEFAULT 'N', CONSTRAINT PK_MP_MENU PRIMARY KEY (MENU_ID) ); COMMENT ON TABLE MP_MENU IS '메뉴정보'; COMMENT ON COLUMN MP_MENU.MENU_ID IS '식별자'; COMMENT ON COLUMN MP_MENU.PARENT_MENU_ID IS '부모메뉴 식별자'; COMMENT ON COLUMN MP_MENU.MENU_ORDER IS '표시순서'; COMMENT ON COLUMN MP_MENU.MENU_TYPE IS '메뉴유형 (NAVI, SIDE, CONT..)'; COMMENT ON COLUMN MP_MENU.MENU_NAME IS '메뉴명 (사용자가 보는 text)'; COMMENT ON COLUMN MP_MENU.ENABLED_YN IS '사용되는지 여부'; COMMENT ON COLUMN MP_MENU.ACCESS_URI_ADDR IS '접근주소'; COMMENT ON COLUMN MP_MENU.IMAGE_URI_ADDR IS '이미지 주소'; COMMENT ON COLUMN MP_MENU.MENU_DESC IS '설명'; COMMENT ON COLUMN MP_MENU.AUTH_YN IS '로그인한 사용자만 쓰는지 여부'; COMMENT ON COLUMN MP_MENU.AUTH_VIEW_YN IS '로그인한 사용자에게 보일지 여부'; COMMENT ON COLUMN MP_MENU.AGG_YN IS '통계대상인지 여부 '; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_MENU_PARAM ( MENU_PARAM_SEQ NUMBER(20,0), MENU_ID VARCHAR2(255), PARAM_NAME VARCHAR2(255) NOT NULL, DATA_TYPE VARCHAR2(50) NOT NULL, PARAM_VALUE VARCHAR2(4000), PARAM_DESC VARCHAR2(500), CONSTRAINT FK_PARAM_MENU_ID FOREIGN KEY(MENU_ID) REFERENCES MP_MENU(MENU_ID), CONSTRAINT UQ_PARAM_PARAM_NAME UNIQUE (MENU_ID, PARAM_NAME) ); COMMENT ON TABLE MP_MENU_PARAM IS '메뉴 파라미터 정보'; CREATE SEQUENCE SEQ_MP_MENU_PARAM_SEQ_MPS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_MENU_PARAM.MENU_PARAM_SEQ IS '파라미터 SEQ'; COMMENT ON COLUMN MP_MENU_PARAM.MENU_ID IS '메뉴 ID'; COMMENT ON COLUMN MP_MENU_PARAM.PARAM_NAME IS '파라미터명'; COMMENT ON COLUMN MP_MENU_PARAM.DATA_TYPE IS '데이터 타입'; COMMENT ON COLUMN MP_MENU_PARAM.PARAM_VALUE IS '파라미터 값'; COMMENT ON COLUMN MP_MENU_PARAM.PARAM_DESC IS '파라미터 설명'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_I18N ( MENU_ID VARCHAR2(255), I18N_CD CHAR(2), CD_TEXT VARCHAR2(255), CONSTRAINT UQ_I18N_MENU_ID_I18N_CD UNIQUE (MENU_ID, I18N_CD), CONSTRAINT FK_I18N_MENU_ID FOREIGN KEY (MENU_ID) REFERENCES MP_MENU (MENU_ID) ); COMMENT ON TABLE MP_MENU_PARAM IS '메뉴에 적용되는 다국어 정보'; COMMENT ON COLUMN MP_I18N.MENU_ID IS '메뉴 ID'; COMMENT ON COLUMN MP_I18N.I18N_CD IS '언어코드'; COMMENT ON COLUMN MP_I18N.CD_TEXT IS '코드에 대핟되는 문자열'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_MANAGER ( USER_ID VARCHAR2(255), USER_NAME VARCHAR2(255), ENABLED_YN CHAR(1) DEFAULT 'N', DEPT_NAME VARCHAR2(255), PWD_VALUE VARCHAR2(255), CREATE_DT DATE, CREATE_ID VARCHAR2(255), UPDATE_DT DATE, UPDATE_ID VARCHAR2(255), CONSTRAINT PK_MP_MANAGER PRIMARY KEY (USER_ID) ); COMMENT ON TABLE MP_MANAGER IS '관리자 계정'; COMMENT ON COLUMN MP_MANAGER.USER_ID IS '로그인 ID'; COMMENT ON COLUMN MP_MANAGER.USER_NAME IS '이름'; COMMENT ON COLUMN MP_MANAGER.ENABLED_YN IS '활성화 여부'; COMMENT ON COLUMN MP_MANAGER.PWD_VALUE IS '로그인 암호'; COMMENT ON COLUMN MP_MANAGER.CREATE_DT IS '생성일시'; COMMENT ON COLUMN MP_MANAGER.CREATE_ID IS '생성자'; COMMENT ON COLUMN MP_MANAGER.UPDATE_DT IS '수정일시'; COMMENT ON COLUMN MP_MANAGER.UPDATE_ID IS '수정자'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_TELNO ( TELNO_SEQ NUMBER(20,0), BUILDING_DESC VARCHAR2(500), ROOM_DESC VARCHAR2(500), TEL_VALUE VARCHAR2(255), TELNO_ORDER NUMBER(*,0) DEFAULT 1 ); COMMENT ON TABLE MP_TELNO IS '병원 전화번호'; CREATE SEQUENCE SEQ_MP_TELNO_TS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_TELNO.TELNO_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_TELNO.BUILDING_DESC IS '건물'; COMMENT ON COLUMN MP_TELNO.ROOM_DESC IS '건물내의 방'; COMMENT ON COLUMN MP_TELNO.TEL_VALUE IS '전화번호'; COMMENT ON COLUMN MP_TELNO.TELNO_ORDER IS '표시순서'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_INF_NURSE ( NURSE_SEQ NUMBER (20), USER_ID VARCHAR2 (255), REQ_CODE_VALUE VARCHAR2(255), REQ_VALUE VARCHAR2(255), REQ_ETC_VALUE VARCHAR2(255), REG_DT DATE DEFAULT SYSDATE, UPDATE_DT DATE, RECEIVE_DT DATE ); COMMENT ON TABLE MP_INF_NURSE IS '간호요청'; CREATE SEQUENCE SEQ_MP_IF_NURSE_SEQ INCREMENT BY 1 START WITH 1 NOORDER; COMMENT ON COLUMN MP_INF_NURSE.NURSE_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_INF_NURSE.USER_ID IS '사용자 ID'; COMMENT ON COLUMN MP_INF_NURSE.REQ_CODE_VALUE IS '요청코드나열 (화면에서 사용하기 편한 형태로)'; COMMENT ON COLUMN MP_INF_NURSE.REQ_VALUE IS '요청을 문자료 표시 (부산대 요구사항)'; COMMENT ON COLUMN MP_INF_NURSE.REQ_ETC_VALUE IS '주관식 요청 '; COMMENT ON COLUMN MP_INF_NURSE.REG_DT IS '요청일과 시간'; COMMENT ON COLUMN MP_INF_NURSE.UPDATE_DT IS '기간계에서 요청을 가져간 시간'; COMMENT ON COLUMN MP_INF_NURSE.RECEIVE_DT IS '간호에서 요청을 접수한 시간. 부산대에서 요구하니까 추가는 해주는데 과연 값이 제대로 들어올 것인가 의심하고 있음'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_PUSH_FORM ( FORM_ID VARCHAR2(255), FORM_TYPE VARCHAR2(50), USE_YN CHAR(1), MENU_ID VARCHAR2(255), INCLUDE_NAME_YN CHAR(1), FORM_DESC VARCHAR2(500), REG_DT DATE DEFAULT SYSDATE, CONSTRAINT PK_MP_PUSH_FORM PRIMARY KEY (FORM_ID), CONSTRAINT FK_PUSH_FORM_MENU_ID FOREIGN KEY (MENU_ID) REFERENCES MP_MENU (MENU_ID) ); COMMENT ON TABLE MP_PUSH_FORM IS '사용자에게 전송될 메시지 형식'; COMMENT ON COLUMN MP_PUSH_FORM.FORM_ID IS '메시지 형식에 대한 식별자'; COMMENT ON COLUMN MP_PUSH_FORM.FORM_TYPE IS '유형 : PAGE, MAP, GUIDE'; COMMENT ON COLUMN MP_PUSH_FORM.USE_YN IS '사용여부 (PUSH_MSG쪽에서 FK로 참조될꺼라서 삭제기능이 없을것임)'; COMMENT ON COLUMN MP_PUSH_FORM.MENU_ID IS '유형이 PAGE인 경우 메뉴 식별자. 확장자가 page인 메뉴만 가능'; COMMENT ON COLUMN MP_PUSH_FORM.INCLUDE_NAME_YN IS '메시지에 이름포함 여부'; COMMENT ON COLUMN MP_PUSH_FORM.FORM_DESC IS '형식에 대한 설명'; COMMENT ON COLUMN MP_PUSH_FORM.REG_DT IS '추가된 시간'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_EVENT_LOG ( LOG_SEQ NUMBER(20) DEFAULT 0, USER_ID VARCHAR2 (255), --- DEVICE_UUID_ID VARCHAR2(255), EVENT_NAME VARCHAR2(255) NOT NULL, LOG_DT DATE DEFAULT SYSDATE ); COMMENT ON TABLE MP_EVENT_LOG IS '비콘 이벤트'; CREATE SEQUENCE SEQ_MP_EVENT_LOG_LS INCREMENT BY 1 START WITH 1 NOORDER; COMMENT ON COLUMN MP_EVENT_LOG.LOG_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_EVENT_LOG.USER_ID IS '사용자 ID (NULL일 수 있음)'; --- COMMENT ON COLUMN MP_EVENT_LOG.DEVICE_UUID_ID IS '토큰ID (NULL일 수 있음)'; COMMENT ON COLUMN MP_EVENT_LOG.EVENT_NAME IS '이벤트 이름'; COMMENT ON COLUMN MP_EVENT_LOG.LOG_DT IS '이벤트 수신 시간 (발생시간이 아님)'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_EVENT ( AGG_SEQ NUMBER(20) DEFAULT 0, EVENT_NAME VARCHAR2(255) NOT NULL, HIT_CNT NUMBER(*,0), AGG_DT DATE DEFAULT SYSDATE ); COMMENT ON TABLE MP_AGG_EVENT IS '비콘 이벤트 집계'; CREATE SEQUENCE SEQ_MP_AGG_EVENT_AS INCREMENT BY 1 START WITH 1 NOORDER; COMMENT ON COLUMN MP_AGG_EVENT.AGG_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_EVENT.EVENT_NAME IS '발생된 이벤트명'; COMMENT ON COLUMN MP_AGG_EVENT.HIT_CNT IS '발생횟수'; COMMENT ON COLUMN MP_AGG_EVENT.AGG_DT IS '집계기준시간'; ---------------------------------------------------------------------------------------------- ---------- COMMON TABLE 생성 ---------------------------------------------------------------------------------------------- CREATE TABLE MP_VERSION ( PLATFORM_TYPE VARCHAR2(50), CERT_TYPE VARCHAR2(50) DEFAULT 'InHouse', VERSION_ORDER NUMBER(*,0), APP_NAME VARCHAR2(255), MARKET_URL VARCHAR2(1024), CONSTRAINT UQ_MP_VERSION UNIQUE (PLATFORM_TYPE, CERT_TYPE, APP_NAME) ); COMMENT ON TABLE MP_VERSION IS '앱 버전정보'; COMMENT ON COLUMN MP_VERSION.PLATFORM_TYPE IS '플랫폼 유형'; COMMENT ON COLUMN MP_VERSION.CERT_TYPE IS '인증유형 구분 (InHouse, AppStore 등..)'; COMMENT ON COLUMN MP_VERSION.VERSION_ORDER IS '현재 최신버전. 이 버전보다 이전 버전들은 업데이트를 해야함. 이후버전(지금 심사중인 버전)들은 안해도됨. '; COMMENT ON COLUMN MP_VERSION.APP_NAME IS '배포된 이름'; COMMENT ON COLUMN MP_VERSION.MARKET_URL IS '앱을 다운로드 받기 위한 마켓 주소'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_USER ( USER_SEQ NUMBER (20), USER_ID VARCHAR2 (255), USER_NAME VARCHAR2 (255), LOGIN_FAIL_CNT NUMBER (10), PASSWORD_VALUE VARCHAR2 (255), PASSWORD_UPDATE_DT DATE, REGISTER_DT DATE, LOCAL_CIPHER_KEY_VALUE VARCHAR2 (255), USE_ATTR_ORDER NUMBER (20), CONSTRAINT PK_USER_ID PRIMARY KEY (USER_ID) ); COMMENT ON TABLE MP_USER IS '사용자 정보'; CREATE SEQUENCE SEQ_MP_USER_US INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_USER.USER_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_USER.USER_ID IS '사용자 ID'; COMMENT ON COLUMN MP_USER.USER_NAME IS '사용자 이름'; COMMENT ON COLUMN MP_USER.LOGIN_FAIL_CNT IS '로그인 실패 횟수'; COMMENT ON COLUMN MP_USER.PASSWORD_VALUE IS '로그인 암호 (sha-256)'; COMMENT ON COLUMN MP_USER.REGISTER_DT IS '등록일'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGREEMENT ( AGREEMENT_SEQ NUMBER(20,0), AGREEMENT_ORDER NUMBER(*,0), AGREEMENT_NAME VARCHAR2(255) NOT NULL, AGREEMENT_CL CLOB NOT NULL, AGREEMENT_ID VARCHAR2(255 BYTE), TYPE_NAME VARCHAR2(255) NOT NULL, VERSION_NUMBER NUMBER(20,0), REQUIRED_YN CHAR(1) DEFAULT 'N', ENABLED_YN CHAR(1) DEFAULT 'N', NEW_YN CHAR(1) DEFAULT 'Y', CONSTRAINT PK_AGREEMENT_SEQ PRIMARY KEY (AGREEMENT_SEQ) ); COMMENT ON TABLE MP_AGREEMENT IS '서비스 약관(동의서) 정보'; CREATE SEQUENCE SEQ_MP_AGREEMENT INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_ORDER IS '동의서 순서'; COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_NAME IS '동의서 이름'; COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_CL IS '동의서 내용'; COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_ID IS '동의서 식별자 '; COMMENT ON COLUMN MP_AGREEMENT.TYPE_NAME IS '동의서유형(ALL, MINOR). 현재는 일반/미성년자만 있지만 조만간 외국인도 생길듯하여 NAME으로함'; COMMENT ON COLUMN MP_AGREEMENT.VERSION_NUMBER IS '동의서 버전 '; COMMENT ON COLUMN MP_AGREEMENT.REQUIRED_YN IS '필수 여부'; COMMENT ON COLUMN MP_AGREEMENT.ENABLED_YN IS '활성화 여부'; COMMENT ON COLUMN MP_AGREEMENT.REQUIRED_YN IS '필수 여부'; COMMENT ON COLUMN MP_AGREEMENT.NEW_YN IS '최신 동의서 여부'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_USER_AGREEMENT ( USER_AGREEMENT_SEQ NUMBER (20), AGREEMENT_SEQ NUMBER (20) NOT NULL, AGREEMENT_ID VARCHAR2(255) NOT NULL, USER_ID VARCHAR2(255) NOT NULL, AGREEMENT_YN CHAR (1) DEFAULT 'N', REGISTER_DT DATE, CONSTRAINT PK_USER_AGREEMENT_SEQ PRIMARY KEY (USER_AGREEMENT_SEQ), CONSTRAINT FK_USER_AGREEMENT_SEQ FOREIGN KEY (AGREEMENT_SEQ) REFERENCES MP_AGREEMENT(AGREEMENT_SEQ), CONSTRAINT FK_USER_USER_ID FOREIGN KEY (USER_ID) REFERENCES MP_USER(USER_ID) ); COMMENT ON TABLE MP_USER_AGREEMENT IS '서비스 약관(동의서) 동의여부'; CREATE SEQUENCE SEQ_MP_USER_AGREEMENT_UA INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_USER_AGREEMENT.USER_AGREEMENT_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_USER_AGREEMENT.AGREEMENT_SEQ IS '동의서 시퀀스'; COMMENT ON COLUMN MP_USER_AGREEMENT.AGREEMENT_ID IS '동의서 ID'; COMMENT ON COLUMN MP_USER_AGREEMENT.USER_ID IS '사용자 ID'; COMMENT ON COLUMN MP_USER_AGREEMENT.AGREEMENT_YN IS '동의여부 (Y/N)'; COMMENT ON COLUMN MP_USER_AGREEMENT.REGISTER_DT IS '등록일시'; ---------------------------------------------------------------------------------------------- CREATE TABLE MNS_RECEIVER_DEVICE ( RECEIVER_ID VARCHAR2(255) NOT NULL, DEVICE_TOKEN_ID VARCHAR2(255), PLATFORM_TYPE VARCHAR2(50) NOT NULL, CERT_TYPE VARCHAR2(50) DEFAULT '', REGISTER_DT DATE DEFAULT SYSDATE, UPDATE_DT DATE DEFAULT SYSDATE, CONSTRAINT UQ_RECEIVER_TOKEN_ID UNIQUE (RECEIVER_ID, DEVICE_TOKEN_ID, PLATFORM_TYPE) ); COMMENT ON TABLE MNS_RECEIVER_DEVICE IS 'device token 정보'; COMMENT ON COLUMN MNS_RECEIVER_DEVICE.RECEIVER_ID IS '수신자 아이디 (예 : 사용자ID)'; COMMENT ON COLUMN MNS_RECEIVER_DEVICE.DEVICE_TOKEN_ID IS '토큰'; COMMENT ON COLUMN MNS_RECEIVER_DEVICE.PLATFORM_TYPE IS '플랫폼 구분 (A : Android, I : iPhone)'; COMMENT ON COLUMN MNS_RECEIVER_DEVICE.CERT_TYPE IS '인증유형 구분 (InHouse, AppStore 등..)'; COMMENT ON COLUMN MNS_RECEIVER_DEVICE.REGISTER_DT IS '토큰 등록일'; COMMENT ON COLUMN MNS_RECEIVER_DEVICE.UPDATE_DT IS '토큰 수정일'; ---------------------------------------------------------------------------------------------- CREATE TABLE MNS_CONTENTS ( CONTENTS_SEQ NUMBER(20,0), CONTENTS_MSG VARCHAR2(4000) NOT NULL, SENDER_ID VARCHAR2(100) NOT NULL, CREATE_DT DATE DEFAULT SYSDATE, RESERVE_SEND_DT DATE DEFAULT SYSDATE, CONSTRAINT PK_MNS_CONTENTS PRIMARY KEY (CONTENTS_SEQ) ); COMMENT ON TABLE MNS_CONTENTS IS '전송될 메시지 정보'; CREATE SEQUENCE SEQ_MNS_CONTENTS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MNS_CONTENTS.CONTENTS_SEQ IS '시퀀스'; COMMENT ON COLUMN MNS_CONTENTS.CONTENTS_MSG IS '메시지 내용'; COMMENT ON COLUMN MNS_CONTENTS.SENDER_ID IS '발송자아이디'; COMMENT ON COLUMN MNS_CONTENTS.CREATE_DT IS '레코드 등록일시'; COMMENT ON COLUMN MNS_CONTENTS.RESERVE_SEND_DT IS '예약전송일시'; ---------------------------------------------------------------------------------------------- CREATE TABLE MNS_CONTENTS_RESULT ( CONTENTS_SEQ NUMBER(20,0), COMPLETE_DT DATE NOT NULL, CONSTRAINT FK_CONTENTS_SEQ FOREIGN KEY (CONTENTS_SEQ) REFERENCES MNS_CONTENTS (CONTENTS_SEQ), CONSTRAINT UQ_CONTENTS_SEQ UNIQUE (CONTENTS_SEQ) ); COMMENT ON TABLE MNS_CONTENTS_RESULT IS '메시지 전송결과'; COMMENT ON COLUMN MNS_CONTENTS_RESULT.CONTENTS_SEQ IS '발송순서'; COMMENT ON COLUMN MNS_CONTENTS_RESULT.COMPLETE_DT IS '전체 수신자에 대한 발송완료 일시'; ---------------------------------------------------------------------------------------------- CREATE TABLE MNS_RECEIVER ( CONTENTS_SEQ NUMBER(20,0) , RECEIVER_ID VARCHAR2(255) NOT NULL, CONSTRAINT FK_RC_CONTENTS_SEQ FOREIGN KEY (CONTENTS_SEQ) REFERENCES MNS_CONTENTS (CONTENTS_SEQ), CONSTRAINT UQ_RC_CONTENTS_SEQ_RECEIVER_ID UNIQUE (CONTENTS_SEQ, RECEIVER_ID) VALIDATE ); COMMENT ON TABLE MNS_RECEIVER IS '메시지 수신자'; COMMENT ON COLUMN MNS_RECEIVER.CONTENTS_SEQ IS '발송순서'; COMMENT ON COLUMN MNS_RECEIVER.RECEIVER_ID IS '수신자 아이디'; ---------------------------------------------------------------------------------------------- CREATE TABLE MNS_RECEIVER_RESULT ( CONTENTS_SEQ NUMBER(20,0), RECEIVER_ID VARCHAR2(255), DEVICE_TOKEN_ID VARCHAR2(255), PLATFORM_TYPE VARCHAR2(50), SEND_DT DATE NOT NULL, SUCCESS_YN CHAR(1) NOT NULL, ERROR_TYPE VARCHAR2(50), DELETE_YN CHAR(1), READ_YN CHAR(1), CONSTRAINT FK_CONTENTS_SEQ_RECEIVER_ID FOREIGN KEY (CONTENTS_SEQ, RECEIVER_ID) REFERENCES MNS_RECEIVER (CONTENTS_SEQ, RECEIVER_ID) ); COMMENT ON TABLE MNS_RECEIVER_RESULT IS '메시지 수신자별 전송결과'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.CONTENTS_SEQ IS '발송순서'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.RECEIVER_ID IS '수신자 아이디'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.DEVICE_TOKEN_ID IS '토큰'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.PLATFORM_TYPE IS '플랫폼 구분'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.SEND_DT IS '발송일시'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.SUCCESS_YN IS '발송 성공여부'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.ERROR_TYPE IS '발송실패 에러응답코드'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.DELETE_YN IS '메시지 삭제여부 (삭제예정필드)'; COMMENT ON COLUMN MNS_RECEIVER_RESULT.READ_YN IS '메시지 읽음여부 (삭제예정필드)'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_MSG ( AGG_SEQ NUMBER(20) DEFAULT 0, AGG_DT DATE DEFAULT SYSDATE, TOTAL_CNT NUMBER(*, 0), SUCCESS_CNT NUMBER(*, 0) ); COMMENT ON TABLE MP_AGG_MSG IS '메시지 전송 결과 집계정보'; CREATE SEQUENCE SEQ_MP_AGG_MSG_MS INCREMENT BY 1 START WITH 1 NOORDER; COMMENT ON COLUMN MP_AGG_MSG.AGG_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_MSG.AGG_DT IS '집계날짜'; COMMENT ON COLUMN MP_AGG_MSG.TOTAL_CNT IS '전송횟수'; COMMENT ON COLUMN MP_AGG_MSG.SUCCESS_CNT IS '성공횟수'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_USER_AGE ( AGG_SEQ NUMBER(20, 0), AGE_ORDER NUMBER(*, 0), DATA_CNT NUMBER(*,0), AGG_DT DATE ); COMMENT ON TABLE MP_AGG_USER_AGE IS '가입자 연령별 통계'; CREATE SEQUENCE SEQ_MP_AGG_USER_AU INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AGG_USER_AGE.AGG_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_USER_AGE.AGE_ORDER IS '연령대'; COMMENT ON COLUMN MP_AGG_USER_AGE.DATA_CNT IS '인원수'; COMMENT ON COLUMN MP_AGG_USER_AGE.AGG_DT IS '통계일시'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_USER_TMP --- 사용자 연령대와 우편번호 통계데이터 마련을 위한 임시 테이블. 지우고, 데이터 추가 ( AGE_ORDER NUMBER(*, 0), POSTNO_VALUE VARCHAR2(255) ); COMMENT ON TABLE MP_AGG_USER_TMP IS '가입자 통계를 위한 임시 테이블'; COMMENT ON COLUMN MP_AGG_USER_TMP.AGE_ORDER IS '연령대'; COMMENT ON COLUMN MP_AGG_USER_TMP.POSTNO_VALUE IS '우편번호'; commit; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_USER_POSTNO ( AGG_SEQ NUMBER(20, 0) NOT NULL, POSTNO_VALUE VARCHAR2(255) NOT NULL, DATA_CNT NUMBER(*, 0) NOT NULL, AGG_DT DATE NOT NULL ); COMMENT ON TABLE MP_AGG_USER_POSTNO IS '위치별 가입자 통계'; CREATE SEQUENCE SEQ_MP_AGG_POSTNO_AP INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AGG_USER_POSTNO.AGG_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_USER_POSTNO.POSTNO_VALUE IS '우편번호에 해당하는 시 정보'; COMMENT ON COLUMN MP_AGG_USER_POSTNO.DATA_CNT IS '인원수'; COMMENT ON COLUMN MP_AGG_USER_POSTNO.AGG_DT IS '통계일시'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_AGG_USER_REGISTER ( REG_SEQ NUMBER(20, 0) NOT NULL, REG_CNT NUMBER(*, 0) NOT NULL, AGG_DT DATE NOT NULL ); COMMENT ON TABLE MP_AGG_USER_REGISTER IS '날짜별 가입인원 통계'; CREATE SEQUENCE SEQ_MP_USER_REGISTER_UR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AGG_USER_REGISTER.REG_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_AGG_USER_REGISTER.REG_CNT IS '인원수'; COMMENT ON COLUMN MP_AGG_USER_REGISTER.AGG_DT IS '통계일시'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_ZIPCODE ( ZIP_SEQ NUMBER(20, 0) DEFAULT 0, ZIP_ID VARCHAR2(255) NOT NULL, SI_ID VARCHAR2(255) NOT NULL, SI_VALUE VARCHAR2(255) NOT NULL, GUNGU_VALUE VARCHAR2(255) ); COMMENT ON TABLE MP_ZIPCODE IS '통계를 위한 우편번호 정보'; CREATE SEQUENCE SEQ_MP_ZIPCODE_ZC INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_ZIPCODE.ZIP_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_ZIPCODE.ZIP_ID IS '시군구우편번호 (5자리체계에서 앞에 3자리)'; COMMENT ON COLUMN MP_ZIPCODE.SI_ID IS '시군구우편번호 (5자리체계에서 앞에 2자리)'; COMMENT ON COLUMN MP_ZIPCODE.SI_VALUE IS '시'; COMMENT ON COLUMN MP_ZIPCODE.GUNGU_VALUE IS '군/구'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_HELPER_CONTENTS ( HELPER_SEQ NUMBER(20, 0), USER_ID VARCHAR2 (255) NOT NULL, USER_MSG VARCHAR2(4000) NOT NULL, MENU_ID VARCHAR2(255), SEND_DT DATE DEFAULT SYSDATE ); COMMENT ON TABLE MP_HELPER_CONTENTS IS '도우미 메시지'; CREATE INDEX IDX_HELPER_CONTENTS ON MP_HELPER_CONTENTS(USER_ID, SEND_DT); CREATE SEQUENCE SEQ_MP_HELPER_HS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_HELPER_CONTENTS.HELPER_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_HELPER_CONTENTS.USER_ID IS '사용자 ID'; COMMENT ON COLUMN MP_HELPER_CONTENTS.USER_MSG IS '도우미 화면에 보일 메시지'; COMMENT ON COLUMN MP_HELPER_CONTENTS.MENU_ID IS '어느 메뉴에 붙는지 메뉴 ID (분석용)'; COMMENT ON COLUMN MP_HELPER_CONTENTS.SEND_DT IS '보낸시간. Push를 통해서는 전송실패되었을 수 있으나 어쨌든 서버는 보낸 메시지'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_LOGIN_HISTORY ( LOGIN_SEQ NUMBER(20,0), USER_ID VARCHAR2(255), LOGIN_DT DATE, REMEMBER_ME_YN CHAR(1), LOGIN_HASH_VALUE VARCHAR2 (255) ); COMMENT ON TABLE MP_LOGIN_HISTORY IS '사용자 로그인 이력'; CREATE SEQUENCE SEQ_MP_LOGIN_HISTORY_LS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_LOGIN_HISTORY.LOGIN_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_LOGIN_HISTORY.USER_ID IS '사용자 ID'; COMMENT ON COLUMN MP_LOGIN_HISTORY.LOGIN_DT IS '로그인 일시'; COMMENT ON COLUMN MP_LOGIN_HISTORY.REMEMBER_ME_YN IS '자동 로그인 여부'; COMMENT ON COLUMN MP_LOGIN_HISTORY.LOGIN_HASH_VALUE IS '로그인 해쉬'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_MNS_MSGMAPPING ( USER_ID VARCHAR2 (255) NOT NULL, CONTENTS_SEQ NUMBER(20,0) NOT NULL, FORM_ID VARCHAR2(255) NOT NULL, MESSAGE_VALUE VARCHAR2(255) NOT NULL, LOG_DT DATE DEFAULT SYSDATE ); COMMENT ON TABLE MP_HELPER_CONTENTS IS '메시지 추적을 위한 맵핑정보'; CREATE INDEX IDX_MSGMAPPING_CONTENTS ON MP_MNS_MSGMAPPING(USER_ID); COMMENT ON COLUMN MP_MNS_MSGMAPPING.USER_ID IS '사용자 ID (검색용)'; COMMENT ON COLUMN MP_MNS_MSGMAPPING.CONTENTS_SEQ IS 'PUSH 메시지 구분자'; COMMENT ON COLUMN MP_MNS_MSGMAPPING.FORM_ID IS '메시지 유형'; COMMENT ON COLUMN MP_MNS_MSGMAPPING.MESSAGE_VALUE IS '메시지 본문 내용'; COMMENT ON COLUMN MP_MNS_MSGMAPPING.LOG_DT IS '로그 남기는 시간'; ---------------------------------------------------------------------------------------------- 병원마다 메뉴 구조가 다를 수 있어서 별도의 FORM이 만들어지기 때문에 공통이 될 수 없어서 병원구분이 들어갔음 CREATE TABLE MP_INF_PUSH_MSG ( PUSH_SEQ NUMBER(20), FORM_ID VARCHAR2(255) NOT NULL, HOSPITAL_VALUE VARCHAR2(255) NOT NULL, USER_ID VARCHAR2(255) NOT NULL, USER_NAME VARCHAR2(255), MESSAGE_VALUE VARCHAR2(255) NOT NULL, PARAM_VALUE VARCHAR2(255), STAGE_ORDER NUMBER(2) DEFAULT 40, REQ_DT DATE DEFAULT SYSDATE, SENDER_ID VARCHAR2(255) DEFAULT 'UNKNOWN', PUMPING_DT DATE, SEND_YN CHAR(1) DEFAULT 'N', RESERVE_SEND_DT DATE DEFAULT SYSDATE ); COMMENT ON TABLE MP_INF_PUSH_MSG IS '기간계의 메시지 전송 요청내역'; CREATE SEQUENCE SEQ_MP_INF_FORM_PUSH_PS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_INF_PUSH_MSG.PUSH_SEQ IS '시퀀스'; COMMENT ON COLUMN MP_INF_PUSH_MSG.FORM_ID IS '[기간계] 메시지 형식 (DB가 달라서 FK를 걸어둘 수 없음)'; COMMENT ON COLUMN MP_INF_PUSH_MSG.HOSPITAL_VALUE IS '[기간계] 어느병원에서 쏘는 메시지인지 구분. 이 병원에 등록된 FORM_ID 대로 PUSH가 나갈것임'; COMMENT ON COLUMN MP_INF_PUSH_MSG.USER_ID IS '[기간계] 메시지 수신자'; COMMENT ON COLUMN MP_INF_PUSH_MSG.USER_NAME IS '[기간계/옵션] 메시지 수신자 이름'; COMMENT ON COLUMN MP_INF_PUSH_MSG.MESSAGE_VALUE IS '[기간계] 메시지 내용'; COMMENT ON COLUMN MP_INF_PUSH_MSG.PARAM_VALUE IS '[기간계/옵션] 메시지 형식이 필요로 하는 파라미터. 예) MAP-목적지 (옵션)'; COMMENT ON COLUMN MP_INF_PUSH_MSG.STAGE_ORDER IS '[기간계/필수] 접수(10)-진료(20)-수납(30)-귀가(40).'; COMMENT ON COLUMN MP_INF_PUSH_MSG.SENDER_ID IS '[기간계/옵션] 누가 메시지 전송을 의뢰했는지 확인용'; COMMENT ON COLUMN MP_INF_PUSH_MSG.REQ_DT IS '전송 요청시간'; COMMENT ON COLUMN MP_INF_PUSH_MSG.PUMPING_DT IS '요청을 꺼낸시간'; COMMENT ON COLUMN MP_INF_PUSH_MSG.SEND_YN IS '전송의뢰 성공여부'; COMMENT ON COLUMN MP_INF_PUSH_MSG.RESERVE_SEND_DT IS '[기간계/옵션] 예약전송시간'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_PAYMENT_MASTER ( HOSPITAL_VALUE VARCHAR2(255) NOT NULL, MSG_SEQ NUMBER(9) NOT NULL, MSG_TYPE VARCHAR2(50) NOT NULL, ORDER_ID NUMBER(13) NOT NULL, USER_ID VARCHAR2(255) NOT NULL, DEPARTMENT_TYPE VARCHAR2(50), ORDER_TYPE VARCHAR2(50), EXAM_DT DATE, REQ_DT DATE DEFAULT SYSDATE, PG_TYPE VARCHAR2(50) NOT NULL, CARD_COMPANY_TYPE VARCHAR2(50), CARD_TYPE VARCHAR2(50), CARD_NO_VALUE VARCHAR2(255), CARD_QUOTA_VALUE VARCHAR2(255), CARD_INTEREST_VALUE VARCHAR2(255), TRANS_DT DATE, TRANS_VALUE VARCHAR2(255), TRANS_ID_VALUE VARCHAR2(255), AMOUNT_VALUE VARCHAR2(255) NOT NULL, CANCEL_ORIGIN_ORDER_ID VARCHAR2(255), CANCEL_ORIGIN_TRANS_DT DATE, SEND_DT DATE, SEND_ERROR_YN VARCHAR2(255), SEND_RETRY_CNT NUMBER (10) DEFAULT 0, AUTH_REQ_VALUE VARCHAR2(255), REMARK_VALUE VARCHAR2(255), CONSTRAINT PK_PAYMENT_ORDER_ID PRIMARY KEY (ORDER_ID) ); COMMENT ON TABLE MP_PAYMENT_MASTER IS '거래마스터'; CREATE SEQUENCE SEQ_MP_PAYMENT_MSG_PM INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 ORDER CYCLE; CREATE SEQUENCE SEQ_MP_PAYMENT_ORDER_PO INCREMENT BY 1 START WITH 1 MAXVALUE 9999999999999 ORDER NOCYCLE; COMMENT ON COLUMN MP_PAYMENT_MASTER.HOSPITAL_VALUE IS '병원코드 (031, 032)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.MSG_SEQ IS '전송일자별로 unique한 전문번호. KIS전문 제한에 따라 10자리'; COMMENT ON COLUMN MP_PAYMENT_MASTER.MSG_TYPE IS '승인인지 취소인지여부 (승인 02, 취소12)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.ORDER_ID IS 'PK. 주문번호. Nice의 주문번호, KIS전문의 거래일련번호.'; COMMENT ON COLUMN MP_PAYMENT_MASTER.USER_ID IS '사용자번호'; COMMENT ON COLUMN MP_PAYMENT_MASTER.DEPARTMENT_TYPE IS '진료과코드 (15자리 정도)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.ORDER_TYPE IS '진료유형 (I:입원, O:외래, E:응급)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.EXAM_DT IS '진료일 (시간은 없음)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.REQ_DT IS '거래요청시간. MSG_TYPE에 따라 승인 혹은 취소 요청시간'; COMMENT ON COLUMN MP_PAYMENT_MASTER.PG_TYPE IS 'PG사 유형 (SSG, R2P)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_COMPANY_TYPE IS '카드사코드 (PG사에서 받은 그대로 추가됨)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_TYPE IS '카드타입(01:신용카드, 02:체크카드, 03:해외). 해외는 넣을 수 없음. '; COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_NO_VALUE IS '카드번호'; COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_QUOTA_VALUE IS '할부개월 (00:일시불, 03:3개월)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_INTEREST_VALUE IS '가맹점 무이자 유무 (0:일반, 1:가맹점부담 무이자 사용)'; COMMENT ON COLUMN MP_PAYMENT_MASTER.TRANS_DT IS '거래완료 일시. 승인이면 승인일시, 취소면 취소일시'; COMMENT ON COLUMN MP_PAYMENT_MASTER.TRANS_VALUE IS '거래결과번호. 승인이면 승인번호, 취소이면 취소번호'; COMMENT ON COLUMN MP_PAYMENT_MASTER.TRANS_ID_VALUE IS '거래식별자(NicePay의 T_ID). 취소 요청시 필요함'; COMMENT ON COLUMN MP_PAYMENT_MASTER.AMOUNT_VALUE IS '승인/취소 금액'; COMMENT ON COLUMN MP_PAYMENT_MASTER.CANCEL_ORIGIN_ORDER_ID IS '취소 시 원거래번호'; COMMENT ON COLUMN MP_PAYMENT_MASTER.CANCEL_ORIGIN_TRANS_DT IS '취소 시 원거래 승인일시'; COMMENT ON COLUMN MP_PAYMENT_MASTER.SEND_DT IS '실시간 배치 전송 시간'; COMMENT ON COLUMN MP_PAYMENT_MASTER.SEND_ERROR_YN IS '실시간 배치 전송 에러 여부'; COMMENT ON COLUMN MP_PAYMENT_MASTER.SEND_RETRY_CNT IS '실패 재전송 횟수'; COMMENT ON COLUMN MP_PAYMENT_MASTER.AUTH_REQ_VALUE IS '요청의 유효성 검증 코드'; COMMENT ON COLUMN MP_PAYMENT_MASTER.REMARK_VALUE IS '참고용 '; ---------------------------------------------------------------------------------------------- ---------------------------- MP_AUTH 관련 테이블 -------------------------- CREATE TABLE MP_ATTR_TYPE ( ATTR_TYPE_SEQ NUMBER(20), ATTR_TYPE VARCHAR2(255) NOT NULL, ATTR_TYPE_NAME VARCHAR2(255) NOT NULL, CONSTRAINT PK_MP_ATTR_TYPE PRIMARY KEY (ATTR_TYPE), CONSTRAINT UQ_MP_ATTR_TYPE UNIQUE (ATTR_TYPE, ATTR_TYPE_NAME) ); CREATE SEQUENCE SEQ_MP_ATTR_TYPE INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON TABLE MP_ATTR_TYPE IS '업무 속 타입'; COMMENT ON COLUMN MP_ATTR_TYPE.ATTR_TYPE_SEQ IS '업무 속성 타입 SEQ'; COMMENT ON COLUMN MP_ATTR_TYPE.ATTR_TYPE IS '업무 속성 타입'; COMMENT ON COLUMN MP_ATTR_TYPE.ATTR_TYPE_NAME IS '업무 속성 타입 이름'; CREATE TABLE MP_ATTR ( ATTR_SEQ NUMBER(20), ATTR_TYPE VARCHAR2(255) NOT NULL, ATTR_ID VARCHAR2(255) NOT NULL, ATTR_NAME VARCHAR2(255) NOT NULL, UPPER_ATTR VARCHAR2(255) DEFAULT 'none', CONSTRAINT UQ_MP_ATTR UNIQUE (ATTR_TYPE, ATTR_ID), CONSTRAINT FK_MP_ATTR_ATTR_TYPE FOREIGN KEY (ATTR_TYPE) REFERENCES MP_ATTR_TYPE(ATTR_TYPE) ); CREATE SEQUENCE SEQ_MP_ATTR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON TABLE MP_ATTR IS '업무 속성'; COMMENT ON COLUMN MP_ATTR.ATTR_SEQ IS '업무 속성 SEQ'; COMMENT ON COLUMN MP_ATTR.ATTR_TYPE IS '업무 속성 타입'; COMMENT ON COLUMN MP_ATTR.ATTR_ID IS '업무 속성 ID'; COMMENT ON COLUMN MP_ATTR.ATTR_NAME IS '업무 속성 이름'; CREATE TABLE MP_USER_ATTR ( USER_ATTR_SEQ NUMBER (20), USER_ATTR_ORDER NUMBER (*,0) NOT NULL, USER_ID VARCHAR2(255) NOT NULL, HOSPITAL_ID VARCHAR2(255) NOT NULL, DUTY_ID VARCHAR2(255) NOT NULL, WORK_ID VARCHAR2(255) NOT NULL, CONSTRAINT PK_USER_ATTR_SEQ PRIMARY KEY (USER_ATTR_SEQ), CONSTRAINT FK_USER_ATTR_USER_ID FOREIGN KEY (USER_ID) REFERENCES MP_USER(USER_ID), CONSTRAINT UQ_MP_USER_ATTR UNIQUE (USER_ID, HOSPITAL_ID, DUTY_ID, WORK_ID), CONSTRAINT UQ_MP_USER_ATTR_ORDER UNIQUE(USER_ID, USER_ATTR_ORDER ) ); COMMENT ON TABLE MP_USER_ATTR IS '사용자 업무 테이블'; CREATE SEQUENCE SEQ_MP_USER_ATTR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_USER_ATTR.USER_ATTR_SEQ IS '사용자 업무 SEQ'; COMMENT ON COLUMN MP_USER_ATTR.USER_ATTR_ORDER IS '사용자 업무 우선 순위'; COMMENT ON COLUMN MP_USER_ATTR.USER_ID IS '사용자 ID'; COMMENT ON COLUMN MP_USER_ATTR.HOSPITAL_ID IS '소속 ID'; COMMENT ON COLUMN MP_USER_ATTR.DUTY_ID IS '직무 ID'; COMMENT ON COLUMN MP_USER_ATTR.WORK_ID IS '업무 ID'; CREATE TABLE MP_EXTRA_ATTR ( EXTRA_ATTR_SEQ NUMBER(20), EXTRA_ATTR_ID VARCHAR2(255) NOT NULL, EXTRA_ATTR_NAME VARCHAR2(255) NOT NULL, EXTRA_ATTR_DESC VARCHAR2(500), CONSTRAINT PK_EXTRA_WORK_ID PRIMARY KEY (EXTRA_ATTR_ID), CONSTRAINT UQ_MP_EXTRA_ATTR UNIQUE (EXTRA_ATTR_ID, EXTRA_ATTR_NAME) ); COMMENT ON TABLE MP_EXTRA_ATTR IS '확장 속성'; CREATE SEQUENCE SEQ_MP_EXTRA_ATTR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_EXTRA_ATTR.EXTRA_ATTR_SEQ IS ' 업무 SEQ'; COMMENT ON COLUMN MP_EXTRA_ATTR.EXTRA_ATTR_ID IS '확장 업무 ID'; COMMENT ON COLUMN MP_EXTRA_ATTR.EXTRA_ATTR_NAME IS '확장 업무 이름'; COMMENT ON COLUMN MP_EXTRA_ATTR.EXTRA_ATTR_DESC IS '확장 업무 설명'; CREATE TABLE MP_EXTRA_ATTR_USER ( EXTRA_ATTR_USER_SEQ NUMBER(20), EXTRA_ATTR_ID VARCHAR2(255) NOT NULL, USER_ID VARCHAR2(255) NOT NULL, EXTRA_ATTR_USER_ORDER NUMBER(*,0), CONSTRAINT PK_EXTRA_ATTR_USER_SEQ PRIMARY KEY (EXTRA_ATTR_USER_SEQ), CONSTRAINT FK_EXTRA_ATTR_USER_USER_ID FOREIGN KEY (USER_ID) REFERENCES MP_USER(USER_ID), CONSTRAINT FK_EXTRA_ATTR_ID FOREIGN KEY (EXTRA_ATTR_ID) REFERENCES MP_EXTRA_ATTR(EXTRA_ATTR_ID) ); COMMENT ON TABLE MP_EXTRA_ATTR_USER IS '확장 업무 사용자'; CREATE SEQUENCE SEQ_MP_EXTRA_ATTR_USER INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_EXTRA_ATTR_USER.EXTRA_ATTR_USER_SEQ IS '확장 업무 사용자 SEQ'; COMMENT ON COLUMN MP_EXTRA_ATTR_USER.EXTRA_ATTR_ID IS '확장 업무 ID'; COMMENT ON COLUMN MP_EXTRA_ATTR_USER.USER_ID IS '확장 업무 사용자 ID'; CREATE TABLE MP_AUTH ( AUTH_SEQ NUMBER(20), AUTH_ID VARCHAR2(255) NOT NULL, AUTH_NAME VARCHAR2(255) NOT NULL, AUTH_DESC VARCHAR2(500), CONSTRAINT PK_AUTH_ID PRIMARY KEY (AUTH_ID), CONSTRAINT UQ_MP_AUTH UNIQUE (AUTH_ID, AUTH_NAME) ); COMMENT ON TABLE MP_AUTH IS '권한'; CREATE SEQUENCE SEQ_MP_AUTH INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AUTH.AUTH_SEQ IS '권한 SEQ'; COMMENT ON COLUMN MP_AUTH.AUTH_ID IS '권한 ID'; COMMENT ON COLUMN MP_AUTH.AUTH_NAME IS '권한 이름'; CREATE TABLE MP_AUTH_ATTR ( AUTH_ATTR_SEQ NUMBER(20), AUTH_ID VARCHAR2(255) NOT NULL, AUTH_TYPE VARCHAR2(255) NOT NULL, AUTH_ATTR_TYPE VARCHAR2(255) NOT NULL, AUTH_ATTR_ID VARCHAR2(255) NOT NULL, CONSTRAINT FK_AUTH_ID FOREIGN KEY (AUTH_ID) REFERENCES MP_AUTH(AUTH_ID) ); COMMENT ON TABLE MP_AUTH_ATTR IS '권한 속성'; CREATE INDEX MP_AUTH_ATTR_IDX1 ON MP_AUTH_ATTR(AUTH_ATTR_ID, AUTH_ATTR_TYPE, AUTH_TYPE); CREATE SEQUENCE SEQ_MP_AUTH_ATTR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_ATTR_SEQ IS '권한 속성 SEQ'; COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_ID IS '권한 ID'; COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_TYPE IS '권한 타입 (ADD, MINUS)'; COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_ATTR_TYPE IS '권한 속성 타입'; COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_ATTR_ID IS '권한 속성 ID'; ---------------------------------------------------------------------------------------------- CREATE TABLE MP_MENU_AUTH ( MENU_AUTH_SEQ NUMBER(20), MENU_ID VARCHAR2(255) NOT NULL, AUTH_ID VARCHAR2(255) NOT NULL, CONSTRAINT FK_MENU_AUTH_MENU_ID FOREIGN KEY (MENU_ID) REFERENCES MP_MENU(MENU_ID), CONSTRAINT FK_MENU_AUTH_AUTH_ID FOREIGN KEY (AUTH_ID) REFERENCES MP_AUTH(AUTH_ID), CONSTRAINT UQ_MP_MENU_AUTH UNIQUE (MENU_ID, AUTH_ID) ); COMMENT ON TABLE MP_MENU_AUTH IS '메뉴 권한 속성'; CREATE SEQUENCE SEQ_MP_MENU_AUTH INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_MENU_AUTH.MENU_AUTH_SEQ IS '메뉴 권한 SEQ'; COMMENT ON COLUMN MP_MENU_AUTH.MENU_ID IS '메뉴 ID'; COMMENT ON COLUMN MP_MENU_AUTH.AUTH_ID IS '권한 ID'; CREATE TABLE MP_USER_DEVICE ( DEVICE_SEQ NUMBER(20), USER_ID VARCHAR2(255) NOT NULL, DEVICE_ID VARCHAR2(255) NOT NULL, DEVICE_MODEL VARCHAR2(255) NOT NULL, DEVICE_TYPE VARCHAR2(255) NOT NULL, DEVICE_ALIAS VARCHAR2(255) NOT NULL, CONSTRAINT FK_MP_USER_DEVICE_USER_ID FOREIGN KEY (USER_ID) REFERENCES MP_USER(USER_ID), CONSTRAINT UQ_MP_USER_DEVICE UNIQUE (USER_ID, DEVICE_ID) ); COMMENT ON TABLE MP_USER_DEVICE IS '사용자 Device'; CREATE SEQUENCE SEQ_MP_USER_DEVICE INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_SEQ IS '사용자 Device SEQ'; COMMENT ON COLUMN MP_USER_DEVICE.USER_ID IS '사용자 ID'; COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_ID IS 'Device ID'; COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_MODEL IS 'Device Model(iPhone6s, 갤럭시s6)'; COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_TYPE IS 'Device Type (iOS, android)'; COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_ALIAS IS '사용자 장비 별칭'; CREATE TABLE MP_PUBLIC_DEVICE ( PUBLICE_DEVICE_SEQ NUMBER(20), DEVICE_ID VARCHAR2(255) NOT NULL, DEVICE_MODEL VARCHAR2(255) NOT NULL, DEVICE_TYPE VARCHAR2(255) NOT NULL, DEVICE_ALIAS VARCHAR2(255) NOT NULL, CONSTRAINT UQ_MP_PUBLICE_DEVICE UNIQUE (DEVICE_ID) ); COMMENT ON TABLE MP_PUBLIC_DEVICE IS '공용 Device'; CREATE SEQUENCE SEQ_MP_PUBLIC_DEVICE INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; COMMENT ON COLUMN MP_PUBLIC_DEVICE.PUBLICE_DEVICE_SEQ IS '공용 Device SEQ'; COMMENT ON COLUMN MP_PUBLIC_DEVICE.DEVICE_ID IS 'Device ID'; COMMENT ON COLUMN MP_PUBLIC_DEVICE.DEVICE_MODEL IS 'Device Model(iPhone6s, 갤럭시s6)'; COMMENT ON COLUMN MP_PUBLIC_DEVICE.DEVICE_TYPE IS 'Device Type (iOS, android)'; COMMENT ON COLUMN MP_PUBLIC_DEVICE.DEVICE_ALIAS IS '공용 장비 별칭'; CREATE TABLE MP_DEMO_USER ( USER_ID VARCHAR2(255 BYTE), USER_NAME VARCHAR2(255 BYTE), TELNO_VALUE VARCHAR2(255 BYTE) DEFAULT NULL, INHOSPITAL_YN CHAR(1 BYTE) DEFAULT 'N', CARNO_VALUE VARCHAR2(255 BYTE), HEIGHT_VALUE VARCHAR2(255 BYTE) DEFAULT '0.0', WEIGHT_VALUE VARCHAR2(255 BYTE) DEFAULT '0.0', CHECK_DT DATE DEFAULT NULL, BIRTHDAY_DT DATE, GENDER_VALUE VARCHAR2(255 BYTE) DEFAULT 'M', ZIPCODE_VALUE VARCHAR2(255 BYTE), ZIPCODE_TXT_VALUE VARCHAR2(255 BYTE), ADDR_VALUE VARCHAR2(255 BYTE), CONSTRAINT PK_MCARE_DEMO_USER PRIMARY KEY (USER_ID) ); COMMENT ON COLUMN MP_DEMO_USER.USER_ID IS '사용자 ID'; COMMENT ON COLUMN MP_DEMO_USER.USER_NAME IS '사용자 이름'; COMMENT ON COLUMN MP_DEMO_USER.TELNO_VALUE IS '전화번호'; COMMENT ON COLUMN MP_DEMO_USER.INHOSPITAL_YN IS '재원여부(병원에 있는지 여부)'; COMMENT ON COLUMN MP_DEMO_USER.CARNO_VALUE IS '차량번호'; COMMENT ON COLUMN MP_DEMO_USER.HEIGHT_VALUE IS '키'; COMMENT ON COLUMN MP_DEMO_USER.WEIGHT_VALUE IS '몸무게'; COMMENT ON COLUMN MP_DEMO_USER.CHECK_DT IS '키/몸무게 측정일시'; COMMENT ON COLUMN MP_DEMO_USER.BIRTHDAY_DT IS '생년월일'; COMMENT ON COLUMN MP_DEMO_USER.GENDER_VALUE IS '성별 (1,3:남, 2,4:여, 5,7:외국인남, 6,8:외국인여)'; COMMENT ON COLUMN MP_DEMO_USER.ZIPCODE_VALUE IS '우편번호'; COMMENT ON COLUMN MP_DEMO_USER.ZIPCODE_TXT_VALUE IS '우편번호 대응주소'; COMMENT ON COLUMN MP_DEMO_USER.ADDR_VALUE IS '우편번호 나머지 '; COMMIT;