eapim-portal/script/portal/tibero/portal.sql

CREATE SEQUENCE PORTAL.hibernate_sequence START WITH 1 INCREMENT BY 1;

CREATE TABLE PORTAL.REVINFO
(
    REV      NUMBER(19,0) PRIMARY KEY,
    REVTSTMP NUMBER(19,0) NULL
);

CREATE TABLE PORTAL.PT_ID
(
    TABLE_NAME VARCHAR(36) NOT NULL,
    NEXT_ID    NUMERIC(30) NULL,
    CONSTRAINT PT_ID_PK PRIMARY KEY (TABLE_NAME)
);


CREATE TABLE PORTAL.PT_CODE
(
    CODE_ID          VARCHAR(6) NOT NULL,
    CODE_ID_NAME     VARCHAR(60) NULL,
    CODE_ID_DC       VARCHAR(200) NULL,
    USE_YN           VARCHAR(1) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    CONSTRAINT PT_CODE_PK PRIMARY KEY (CODE_ID)
);


CREATE TABLE PORTAL.PT_DETAIL_CODE
(
    CODE_ID          VARCHAR(6)  NOT NULL,
    CODE             VARCHAR(15) NOT NULL,
    CODE_VALUE       VARCHAR(4000) NULL,
    CODE_DC          VARCHAR(200) NULL,
    USE_YN           VARCHAR(1) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    CONSTRAINT PT_DETAIL_CODE_PK PRIMARY KEY (CODE_ID, CODE)
);


CREATE TABLE PORTAL.PT_USER_SECURITY
(
    USER_ESNTL_ID VARCHAR(36) NOT NULL,
    MBER_TY_CODE  VARCHAR(5) NULL,
    AUTHOR_CODE   VARCHAR(30) NOT NULL,
    CONSTRAINT PT_USER_SECURITY_PK PRIMARY KEY (USER_ESNTL_ID)
);


CREATE TABLE PORTAL.PT_BOARD_MASTER
(
    BOARD_ID               VARCHAR(36)  NOT NULL,
    BOARD_NAME             VARCHAR(255) NOT NULL,
    BOARD_DESC             VARCHAR(2400) NULL,
    BOARD_TYPE             VARCHAR(6)   NOT NULL,
    REPLY_POSBL_YN         VARCHAR(1) NULL,
    FILE_ATCH_POSBL_YN     VARCHAR(1)   NOT NULL,
    ATCH_POSBL_FILE_NUMBER NUMERIC(2)   NOT NULL,
    ATCH_POSBL_FILE_SIZE   NUMERIC(8) NULL,
    USE_YN                 VARCHAR(1)   NOT NULL,
    TMPLAT_ID              VARCHAR(20) NULL,
    REGISTERED_BY          VARCHAR(36) NULL,
    REGISTERED_ON          TIMESTAMP NULL,
    LAST_MODIFIED_BY       VARCHAR(36) NULL,
    LAST_MODIFIED_ON       TIMESTAMP NULL,
    CONSTRAINT PT_BOARD_MASTER_PK PRIMARY KEY (BOARD_ID)
);

CREATE TABLE PORTAL.PT_BOARD_AUTHORITY
(
    AUTHORITY_ID     VARCHAR(36) NOT NULL,
    BBS_ID           VARCHAR(36) NOT NULL,
    AUTHORITY_CODE   VARCHAR(36) NULL,
    ESNTL_ID         VARCHAR(36) NULL,
    READ_YN          VARCHAR(1)  NOT NULL,
    WRITE_YN         VARCHAR(1)  NOT NULL,
    MODIFY_YN        VARCHAR(1)  NOT NULL,
    DELETE_YN        VARCHAR(1)  NOT NULL,
    ADMIN_YN         VARCHAR(1)  NOT NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL,
    primary key (AUTHORITY_ID)
);

CREATE TABLE PORTAL.PT_BOARD_ARTICLE
(
    BOARD_ID         VARCHAR(36) NOT NULL,
    NTT_NO           NUMERIC(20) NULL,
    NTT_ID           NUMERIC(20) NOT NULL,
    NTT_SJ           VARCHAR(2000) NULL,
    NTT_CN           CLOB NULL,
    ANSWER_YN        VARCHAR(1) NULL,
    PARNTSCTT_NO     NUMERIC(10) NULL,
    ANSWER_LC        NUMERIC(8) NULL,
    SORT_ORDR        NUMERIC(8) NULL,
    RDCNT            NUMERIC(10) NULL,
    USE_YN           VARCHAR(1)  NOT NULL,
    NTCE_BGNDE       VARCHAR(20) NULL,
    NTCE_ENDDE       VARCHAR(20) NULL,
    NTCR_ID          VARCHAR(36) NULL,
    NTCR_NAME        VARCHAR(36) NULL,
    PASSWORD         VARCHAR(200) NULL,
    FILE_ID          VARCHAR(36) NULL,
    NOTICE_YN        VARCHAR(1) NULL,
    SJ_BOLD_YN       VARCHAR(1) NULL,
    SECRET_YN        VARCHAR(1) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    CONSTRAINT PT_BOARD_ARTICLE_PK PRIMARY KEY (NTT_ID, BOARD_ID)
);

CREATE TABLE PORTAL.PT_FILE
(
    FILE_ID          VARCHAR(36) NOT NULL,
    USE_YN           VARCHAR(1) NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL,
    CONSTRAINT PT_FILE_PK PRIMARY KEY (FILE_ID)
);

CREATE TABLE PORTAL.PT_FILE_DETAIL
(
    FILE_ID          VARCHAR(36) NOT NULL,
    FILE_SN          NUMERIC(10) NOT NULL,
    ORIGNL_FILE_NAME VARCHAR(255) NULL,
    FILE_EXTSN       VARCHAR(36) NULL,
    FILE_CN          CLOB NULL,
    FILE_SIZE        NUMERIC(8) NULL,
    CONSTRAINT PT_FILE_DETAIL_PK PRIMARY KEY (FILE_ID, FILE_SN)
);


CREATE TABLE PORTAL.PT_FAQ
(
    FAQ_ID           VARCHAR(36) NOT NULL,
    TITLE            VARCHAR(255) NULL,
    QUESTION         VARCHAR(2500) NULL,
    ANSWER           VARCHAR(2500) NULL,
    RDCNT            NUMERIC(10) NULL,
    FILE_ID          VARCHAR(36) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    CONSTRAINT PT_FAQ_PK PRIMARY KEY (FAQ_ID)
);


CREATE TABLE PORTAL.PT_USER
(
    ESNTL_ID                  VARCHAR(36)  NOT NULL,
    USER_ID                   VARCHAR(256) NOT NULL,
    USER_NAME                 VARCHAR(60)  NOT NULL,
    STATUS                    VARCHAR(15)  NOT NULL,
    MOBILE_PHONE              VARCHAR(15) NULL,
    PASSWORD                  VARCHAR(200) NOT NULL,
    PASSWORD_HINT             VARCHAR(100) NULL,
    PASSWORD_CNSR             VARCHAR(100) NULL,
    LOCK_YN                   VARCHAR(1) NULL,
    LOCK_CNT                  NUMERIC(3) NULL,
    LAST_LOCK_DATE            TIMESTAMP NULL,
    LAST_CHANGE_PASSWORD_DATE TIMESTAMP NULL,
    REGISTERED_ON             TIMESTAMP null,
    REGISTERED_BY             VARCHAR(36) null,
    LAST_MODIFIED_ON          TIMESTAMP null,
    LAST_MODIFIED_BY          VARCHAR(36) null,
    PRIMARY KEY (ESNTL_ID)
);


CREATE UNIQUE INDEX PT_USER_IDX1 ON PORTAL.PT_USER
    (
     USER_ID
        )
;

CREATE TABLE PORTAL.PT_USER_AUD
(
    ESNTL_ID                  VARCHAR(36)  NOT NULL,
    REV                       NUMBER(10)      not null,
    USER_ID                   VARCHAR(256) NOT NULL,
    USER_NAME                 VARCHAR(60)  NOT NULL,
    STATUS                    VARCHAR(15)  NOT NULL,
    MOBILE_PHONE              VARCHAR(15) NULL,
    PASSWORD                  VARCHAR(200) NOT NULL,
    PASSWORD_HINT             VARCHAR(100) NULL,
    PASSWORD_CNSR             VARCHAR(100) NULL,
    LOCK_YN                   VARCHAR(1) NULL,
    LOCK_CNT                  NUMERIC(3) NULL,
    LAST_LOCK_DATE            TIMESTAMP NULL,
    LAST_CHANGE_PASSWORD_DATE TIMESTAMP NULL,
    REGISTERED_ON             TIMESTAMP null,
    REGISTERED_BY             VARCHAR(36) null,
    LAST_MODIFIED_ON          TIMESTAMP null,
    LAST_MODIFIED_BY          VARCHAR(36) null,
    REVTYPE                   NUMBER(3),
    PRIMARY KEY (ESNTL_ID, REV)

);


CREATE TABLE PORTAL.PT_ENT_USER
(
    ESNTL_ID                  VARCHAR(36)  NOT NULL,
    USER_ID                   VARCHAR(256) NOT NULL,
    USER_NAME                 VARCHAR(60)  NOT NULL,
    STATUS                    VARCHAR(15)  NOT NULL,
    IP_ADDRESS                VARCHAR(15) NULL,
    MOBILE_PHONE              VARCHAR(15) NULL,
    OFFICE_PHONE              VARCHAR(15) NULL,
    COMPANY_NAME              VARCHAR(15) NULL,
    CEO_NAME                  VARCHAR(15) NULL,
    BIZRNO                    VARCHAR(15) NULL,
    JURIRNO                   VARCHAR(15) NULL,
    ZIP                       VARCHAR(6) NULL,
    ADRES                     VARCHAR(100) NULL,
    DETAIL_ADRES              VARCHAR(100) NULL,
    FXNUM                     VARCHAR(36) NULL,
    INDUTY_CODE               VARCHAR(15) NULL,
    PASSWORD                  VARCHAR(200) NOT NULL,
    PASSWORD_HINT             VARCHAR(100) NULL,
    PASSWORD_CNSR             VARCHAR(100) NULL,
    ENTRPRS_SE_CODE           VARCHAR(15) NULL,
    LOCK_YN                   VARCHAR(1) NULL,
    LOCK_CNT                  NUMERIC(3) NULL,
    LAST_LOCK_DATE            TIMESTAMP NULL,
    LAST_CHANGE_PASSWORD_DATE TIMESTAMP NULL,
    REGISTERED_ON             TIMESTAMP null,
    REGISTERED_BY             VARCHAR(36) null,
    LAST_MODIFIED_ON          TIMESTAMP null,
    LAST_MODIFIED_BY          VARCHAR(36) null,
    PRIMARY KEY (ESNTL_ID)
);

CREATE UNIQUE INDEX PT_ENT_USER_PK ON PORTAL.PT_ENT_USER
    (
     USER_ID
        )
;

CREATE TABLE PORTAL.PT_ENT_USER_AUD
(
    ESNTL_ID                  VARCHAR(36)  NOT NULL,
    REV                       NUMBER(10)      not null,
    USER_ID                   VARCHAR(256) NOT NULL,
    USER_NAME                 VARCHAR(60)  NOT NULL,
    STATUS                    VARCHAR(15)  NOT NULL,
    IP_ADDRESS                VARCHAR(15) NULL,
    MOBILE_PHONE              VARCHAR(15) NULL,
    OFFICE_PHONE              VARCHAR(15) NULL,
    COMPANY_NAME              VARCHAR(15) NULL,
    CEO_NAME                  VARCHAR(15) NULL,
    BIZRNO                    VARCHAR(15) NULL,
    JURIRNO                   VARCHAR(15) NULL,
    ZIP                       VARCHAR(6) NULL,
    ADRES                     VARCHAR(100) NULL,
    DETAIL_ADRES              VARCHAR(100) NULL,
    FXNUM                     VARCHAR(36) NULL,
    INDUTY_CODE               VARCHAR(15) NULL,
    PASSWORD                  VARCHAR(200) NOT NULL,
    PASSWORD_HINT             VARCHAR(100) NULL,
    PASSWORD_CNSR             VARCHAR(100) NULL,
    ENTRPRS_SE_CODE           VARCHAR(15) NULL,
    LOCK_YN                   VARCHAR(1) NULL,
    LOCK_CNT                  NUMERIC(3) NULL,
    LAST_LOCK_DATE            TIMESTAMP NULL,
    LAST_CHANGE_PASSWORD_DATE TIMESTAMP NULL,
    REGISTERED_ON             TIMESTAMP null,
    REGISTERED_BY             VARCHAR(36) null,
    LAST_MODIFIED_ON          TIMESTAMP null,
    LAST_MODIFIED_BY          VARCHAR(36) null,
    REVTYPE                   NUMBER(3),
    PRIMARY KEY (ESNTL_ID, REV)

);


CREATE TABLE PORTAL.PT_APPROVAL
(
    ID               NUMBER(19,0) PRIMARY KEY,
    APPROVAL_TYPE    VARCHAR(32) NULL,
    BODY             CLOB NULL,
    ENDED_ON         TIMESTAMP NULL,
    REQUESTER_ID     VARCHAR(36) NULL,
    STATUS           VARCHAR(32) NULL,
    TARGET_ID        VARCHAR(36) NULL,
    TITLE            VARCHAR(255) NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL
);

CREATE TABLE PORTAL.PT_APPROVAL_APPROVER
(
    APPROVAL_ID NUMBER(19,0)       NOT NULL,
    ORDINAL     NUMBER(19,0)       NOT NULL,
    APPROVED_ON TIMESTAMP NULL,
    MESSAGE     VARCHAR(255) NULL,
    STATUS      VARCHAR(255) NULL,
    USER_ID     VARCHAR(36) NULL,
    PRIMARY KEY (APPROVAL_ID, ORDINAL)
);

CREATE TABLE PORTAL.PT_ROUTE_INFO
(
    ID                    VARCHAR(36)  NOT NULL PRIMARY KEY,
    API_PATH              VARCHAR(512) NULL,
    DESCRIPTION           CLOB NULL,
    LOG_CONTENT_ENCODE_YN VARCHAR(1) NULL,
    LOG_LEVEL             VARCHAR(1) NULL,
    NAME                  VARCHAR(256) NOT NULL,
    ORDER_NO              INT NULL,
    PATH                  VARCHAR(256) NOT NULL,
    METHOD                VARCHAR(32) NULL,
    META_DATA_STR         CLOB NULL,
    RESPONSE_TYPE         VARCHAR(32) NULL,
    MOCK_URL              VARCHAR(255) NULL,
    SAMPLE_RESPONSE       CLOB NULL,
    STATUS                VARCHAR(32) NULL,
    TYPE                  VARCHAR(2) NULL,
    URI                   VARCHAR(512) NOT NULL,
    USE_YN                VARCHAR(1) NULL,
    FILE_ID               VARCHAR(36) NULL,
    REGISTERED_BY         VARCHAR(36) NULL,
    REGISTERED_ON         TIMESTAMP NULL,
    LAST_MODIFIED_BY      VARCHAR(36) NULL,
    LAST_MODIFIED_ON      TIMESTAMP NULL
);

CREATE TABLE PORTAL.PT_API_KEY
(
    ID               VARCHAR(36) NOT NULL PRIMARY KEY,
    OWNER            VARCHAR(255) NULL,
    NAME             VARCHAR(255) NULL,
    ENV              VARCHAR(32) NULL,
    CLIENT_ID        VARCHAR(255) NULL,
    CLIENT_SECRET    VARCHAR(255) NULL,
    REDIRECT_URL     VARCHAR(255) NULL,
    RESPONSE_TYPE    VARCHAR(255) NULL,
    SCOPE            VARCHAR(2000) NULL,
    STATUS           VARCHAR(255) NULL,
    DESCRIPTION      VARCHAR(2000) NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL
);


CREATE TABLE PORTAL.PT_API_KEY_API
(
    API_KEY_ID VARCHAR(36) NOT NULL,
    API_ID     VARCHAR(36) NOT NULL
);

CREATE TABLE PORTAL.PT_API_KEY_AUD
(
    ID               VARCHAR(36) NOT NULL,
    REV              NUMBER(10)       not null,
    REVTYPE          NUMBER(3),
    OWNER            VARCHAR(255) NULL,
    NAME             VARCHAR(255) NULL,
    ENV              VARCHAR(32) NULL,
    CLIENT_ID        VARCHAR(255) NULL,
    CLIENT_SECRET    VARCHAR(255) NULL,
    REDIRECT_URL     VARCHAR(255) NULL,
    RESPONSE_TYPE    VARCHAR(255) NULL,
    SCOPE            VARCHAR(2000) NULL,
    STATUS           VARCHAR(255) NULL,
    DESCRIPTION      VARCHAR(2000) NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL,
    PRIMARY KEY (ID, REV)
);


CREATE TABLE PORTAL.PT_API_KEY_API_AUD
(
    API_KEY_ID VARCHAR(36) NOT NULL,
    API_ID     VARCHAR(36) NOT NULL,
    REV        NUMBER(10)     not null,
    REVTYPE    NUMBER(3),
    PRIMARY KEY (API_KEY_ID, API_ID, REV)
);


CREATE TABLE PORTAL.PT_AGREEMENTS
(
    AGREEMENTS_TYPE     VARCHAR(36)  NOT NULL,
    REVISION            INT          NOT NULL,
    AGREEMENTS_NAME     VARCHAR(100) NOT NULL,
    AGREEMENTS_CONTENTS CLOB NULL,
    STATUS              VARCHAR(36) NULL,
    PUBLISHED_ON        TIMESTAMP NULL,
    SCHEDULED_ON        TIMESTAMP NULL,
    REGISTERED_BY       VARCHAR(36) NULL,
    REGISTERED_ON       TIMESTAMP NULL,
    LAST_MODIFIED_BY    VARCHAR(36) NULL,
    LAST_MODIFIED_ON    TIMESTAMP NULL,
    primary key (AGREEMENTS_TYPE, REVISION)
);

CREATE TABLE PORTAL.PT_MESSAGE_TEMPLATE
(
    MESSAGE_KEY      VARCHAR(32)  NOT NULL,
    MESSAGE_TYPE     VARCHAR(16)  NOT NULL,
    SUBJECT          VARCHAR(100) NOT NULL,
    CONTENTS         CLOB NULL,
    USE_YN           VARCHAR(1) NULL,
    REGISTERED_BY    VARCHAR(36) NULL,
    REGISTERED_ON    TIMESTAMP NULL,
    LAST_MODIFIED_BY VARCHAR(36) NULL,
    LAST_MODIFIED_ON TIMESTAMP NULL,
    primary key (MESSAGE_KEY, MESSAGE_TYPE)
);

CREATE TABLE PORTAL.PT_MESSAGE_RECIPIENT
(
    MESSAGE_KEY  VARCHAR(32) not null,
    MESSAGE_TYPE VARCHAR(16) not null,
    ESNTL_ID     VARCHAR(36) not null,
    constraint PK_MESSAGE_RECIPIENT PRIMARY KEY (MESSAGE_KEY, MESSAGE_TYPE, ESNTL_ID)
);

CREATE TABLE PORTAL.PT_TOKEN
(
    TOKEN      VARCHAR(255) NOT NULL,
    EMAIL      VARCHAR(255) NOT NULL,
    EXPIRES_ON TIMESTAMP    NOT NULL,
    primary key (TOKEN)
);

CREATE TABLE PORTAL.PT_TWO_FACTOR_AUTH
(
    RECIPIENT   VARCHAR(255) NOT NULL,
    AUTH_NUMBER VARCHAR(255),
    EXPIRES_ON  TIMESTAMP,
    PRIMARY KEY (RECIPIENT)
);

create table PORTAL.PT_QNA_QUESTION
(
    ID          VARCHAR(36)  not null,
    EMAIL       VARCHAR(100) not null,
    NAME        VARCHAR(50)  not null,
    PHONE       VARCHAR(20),
    PASSWORD    VARCHAR(100),
    SUBJECT     VARCHAR(200) not null,
    CONTENT     CLOB         not null,
    STATUS      VARCHAR(20)  not null,
    FILE_ID     VARCHAR(36),
    IP_ADDRESS  VARCHAR(45),
    USER_ID     VARCHAR(36),
    ANSWERED_AT TIMESTAMP,
    CREATED_AT  TIMESTAMP null,
    UPDATED_AT  TIMESTAMP null,
    PRIMARY KEY (ID)
);

create table PORTAL.PT_QNA_ANSWER
(
    ID          VARCHAR(36) not null,
    QUESTION_ID VARCHAR(36) not null,
    ADMIN_ID    VARCHAR(36) not null,
    CONTENT     CLOB        not null,
    FILE_ID     VARCHAR(36),
    CREATED_AT  TIMESTAMP null,
    UPDATED_AT  TIMESTAMP null,
    PRIMARY KEY (ID)
);



CREATE VIEW PORTAL.PT_USER_VIEW
    (ESNTL_ID, USER_ID, PASSWORD, USER_NAME, USER_SE)
AS
SELECT ESNTL_ID,
       USER_ID,
       PASSWORD,
       USER_NAME,
       'USR' AS USER_SE
FROM PORTAL.PT_USER
UNION ALL
SELECT ESNTL_ID,
       USER_ID,
       PASSWORD,
       USER_NAME,
       'ENT' AS USER_SE
FROM PORTAL.PT_ENT_USER
ORDER BY ESNTL_ID;