pg_setup.sql

--set search_path TO apdp;

--SHOW search_path;

CREATE TABLE DB_SCH_DTA (
    PRJ_ID        NUMERIC(20) NOT NULL,
    CONN_DTA_ID   NUMERIC(20) NOT NULL,
    ENV_NM        VARCHAR(15) NOT NULL,
    SCH_NM        VARCHAR(25) NOT NULL,
    USR_NM        VARCHAR(30),
    MTA_EFC_DTE   TIMESTAMP,
    CRT_USR_ID    VARCHAR(15),
    MTA_UPT_DTE   TIMESTAMP
);

CREATE INDEX DB_SCH_DTA_X1 ON
    DB_SCH_DTA (
        PRJ_ID
    ASC,
        CONN_DTA_ID
    ASC,
        ENV_NM
    ASC );

ALTER TABLE DB_SCH_DTA
    ADD CONSTRAINT DB_SCH_DTA_PK PRIMARY KEY ( PRJ_ID,
    CONN_DTA_ID,
    ENV_NM,
    SCH_NM );

CREATE TABLE DIM_CONN_DTA (
    PRJ_ID        NUMERIC(20) NOT NULL,
    CONN_DTA_ID   NUMERIC(20) NOT NULL,
    ENV_NM        VARCHAR(15) NOT NULL,
    SER_TYP       VARCHAR(20) NOT NULL,
    SER_DSC       VARCHAR(50) NOT NULL,
    SER_NM        VARCHAR(25),
    POT           NUMERIC(10),
    HST_NM        VARCHAR(30),
    SRV_NM        VARCHAR(30),
    MTA_EFC_DTE   TIMESTAMP,
    MTA_UPT_DTE   TIMESTAMP,
    CRT_USR_ID    VARCHAR(15),
    UPT_USR_ID    VARCHAR(15)
);

CREATE INDEX DIM_CONN_DTA_X1 ON
    DIM_CONN_DTA ( PRJ_ID ASC );

ALTER TABLE DIM_CONN_DTA
    ADD CONSTRAINT DIM_CONN_DTA_PK PRIMARY KEY ( PRJ_ID,
    CONN_DTA_ID,
    ENV_NM );

CREATE TABLE DIM_DB_DEP_OBJ (
    DB_MGN_ID         NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID    VARCHAR(30) NOT NULL,
    DB_SCRT_FIL_NM    VARCHAR(25) NOT NULL,
    DB_SCRT_SRC_LOC   VARCHAR(30) NOT NULL,
    DB_SCRT_TGT_DB    VARCHAR(30) NOT NULL,
    DB_SCRT_TGT_SCH   VARCHAR(20) NOT NULL,
    DB_SCRT_TGT_OBJ   VARCHAR(25) NOT NULL,
    MTA_EFC_DTE       TIMESTAMP,
    MTA_UPT_DTE       TIMESTAMP,
    CRT_USR_ID        VARCHAR(15),
    UPT_USR_ID        VARCHAR(15)
);

CREATE INDEX DIM_DB_DEP_OBJ_X1 ON
    DIM_DB_DEP_OBJ ( LETO_UI_SBM_ID ASC );

ALTER TABLE DIM_DB_DEP_OBJ ADD CONSTRAINT DIM_DB_DEP_OBJ_PK PRIMARY KEY ( DB_MGN_ID );

CREATE TABLE DIM_ENV (
    ENV_ID        NUMERIC(20) NOT NULL,
    SRC_SCH       VARCHAR(15),
    TGT_SCH       VARCHAR(15),
    ENV_NM        VARCHAR(15),
    TAB_NUM       NUMERIC(10),
    MTA_EFC_DTE   TIMESTAMP
);

ALTER TABLE DIM_ENV ADD CONSTRAINT DIM_ENV_PK PRIMARY KEY ( ENV_ID );

CREATE TABLE DIM_HPQC_TST_SET (
    LETO_UI_SBM_ID   VARCHAR(30) NOT NULL,
    TST_SET_ID       NUMERIC(20) NOT NULL,
    TST_SET_NM       VARCHAR(50),
    PRJ_FOL_PAH      VARCHAR(50),
    CYL_NM           VARCHAR(15),
    MTA_EFC_DTE      TIMESTAMP,
    MTA_UPD_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15),
    UPT_USR_ID       VARCHAR(15)
);

CREATE INDEX DIM_HPQC_TST_SET_X1 ON
    DIM_HPQC_TST_SET ( LETO_UI_SBM_ID ASC );

ALTER TABLE DIM_HPQC_TST_SET ADD CONSTRAINT DIM_HPQC_TST_SET_PK PRIMARY KEY ( LETO_UI_SBM_ID,
TST_SET_ID );

CREATE TABLE DIM_INFA_DPM (
    INFA_DPM_ID      NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID   VARCHAR(30) NOT NULL,
    INFA_DOM         VARCHAR(25) NOT NULL,
    INFA_SRC_RPO     VARCHAR(30) NOT NULL,
    INFA_TGT_FOL     VARCHAR(30) NOT NULL,
    INFA_DPM_GRP     VARCHAR(30) NOT NULL,
    INFA_SRC_SER     VARCHAR(30) NOT NULL,
    INFA_TGT_SER     VARCHAR(30) NOT NULL,
    INFA_POT         NUMERIC(10) NOT NULL,
    INFA_TGT_RPO     VARCHAR(30),
    MTA_EFC_DTE      TIMESTAMP,
    MTA_UPT_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15),
    UPT_USR_ID       VARCHAR(15)
);

CREATE INDEX DIM_INFA_DPM_X1 ON
    DIM_INFA_DPM ( LETO_UI_SBM_ID ASC );

ALTER TABLE DIM_INFA_DPM ADD CONSTRAINT DIM_INFA_DPM_PK PRIMARY KEY ( INFA_DPM_ID );

CREATE TABLE DIM_JIRA (
    JIRA_CDA_ID     VARCHAR(15) NOT NULL,
    SUB_PRJ_ID      NUMERIC(20) NOT NULL,
    JIRA_PMR        VARCHAR(100) NOT NULL,
    JIRA_CDA_DESC   VARCHAR(100) NOT NULL,
    JIRA_BOARD      VARCHAR(50) NOT NULL,
    MTA_EFC_DTE     TIMESTAMP,
    MTA_UPT_DTE     TIMESTAMP,
    CRT_USR_ID      VARCHAR(15),
    UPT_USR_ID      VARCHAR(15)
);

CREATE INDEX DIM_JIRA_X1 ON
    DIM_JIRA ( SUB_PRJ_ID ASC );

ALTER TABLE DIM_JIRA ADD CONSTRAINT DIM_JIRA_PK PRIMARY KEY ( JIRA_CDA_ID );

CREATE TABLE DIM_JIRA_CDA_MOVE (
    JIRA_CDA_MOV_ID       NUMERIC(20) NOT NULL,
    JIRA_CDA_ID           VARCHAR(15) NOT NULL,
    JIRA_MOV_USR          VARCHAR(50) NOT NULL,
    FROM_JIRA_SWIM_LANE   VARCHAR(100) NOT NULL,
    TO_JIRA_SWIM_LANE     VARCHAR(100) NOT NULL,
    JIRA_CDA_MOV_RSLT     VARCHAR(400) NOT NULL,
    MTA_EFC_DTE           TIMESTAMP,
    CRT_USR_ID            VARCHAR(15)
);

CREATE INDEX DIM_JIRA_CDA_MOVE_X1 ON
    DIM_JIRA_CDA_MOVE ( JIRA_CDA_ID ASC );

ALTER TABLE DIM_JIRA_CDA_MOVE ADD CONSTRAINT DIM_JIRA_CDA_MOVE_PK PRIMARY KEY ( JIRA_CDA_MOV_ID );

CREATE TABLE DIM_PRJ (
    PRJ_ID        NUMERIC(20) NOT NULL,
    USR_ID        VARCHAR(15) NOT NULL,
    PRJ_NM        VARCHAR(50) NOT NULL,
    PRJ_MNG_NM    VARCHAR(30) NOT NULL,
    PRJ_DSC       VARCHAR(100),
    PRJ_FOL_NM    VARCHAR(50),
    ETL_DOM       VARCHAR(30),
    MTA_EFC_DTE   TIMESTAMP,
    MTA_UPT_DTE   TIMESTAMP,
    CRT_USR_ID    VARCHAR(15),
    UPT_USR_ID    VARCHAR(15)
);

CREATE INDEX DIM_PRJ_X1 ON
    DIM_PRJ ( USR_ID ASC );

ALTER TABLE DIM_PRJ ADD CONSTRAINT DIM_PRJ_PK PRIMARY KEY ( PRJ_ID );

CREATE TABLE DIM_ROL (
    ROL_ID        NUMERIC(20) NOT NULL,
    ROL_NM        VARCHAR(20) NOT NULL,
    ROL_DSC       VARCHAR(30),
    MTA_EFC_DTE   TIMESTAMP,
    MTA_UPT_DTE   TIMESTAMP,
    CRT_USR_ID    VARCHAR(15),
    UPT_USR_ID    VARCHAR(15)
);

ALTER TABLE DIM_ROL ADD CONSTRAINT DIM_ROL_PK PRIMARY KEY ( ROL_ID );

CREATE TABLE DIM_SER_FIL (
    SER_FIL_DEP_ID    NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID    VARCHAR(30) NOT NULL,
    SRC_SER_NM        VARCHAR(50) NOT NULL,
    SRC_FIL_PAH       VARCHAR(50) NOT NULL,
    SRC_SCRT_FIL_NM   VARCHAR(15) NOT NULL,
    TGT_SER_NM        VARCHAR(50) NOT NULL,
    PRSN              VARCHAR(15) NOT NULL,
    MTA_EFC_DTE       TIMESTAMP,
    MTA_UPT_DTE       TIMESTAMP,
    CRT_USR_ID        VARCHAR(15),
    UPT_USR_ID        VARCHAR(15)
);

CREATE INDEX DIM_SER_FIL_X1 ON
    DIM_SER_FIL ( LETO_UI_SBM_ID ASC );

ALTER TABLE DIM_SER_FIL ADD CONSTRAINT DIM_SER_FIL_PK PRIMARY KEY ( SER_FIL_DEP_ID );

CREATE TABLE DIM_SUB_PRJ (
    SUB_PRJ_ID    NUMERIC(20) NOT NULL,
    PRJ_ID        NUMERIC(20) NOT NULL,
    SUB_PRJ_NM    VARCHAR(50) NOT NULL,
    SUB_PRJ_DSC   VARCHAR(100),
    ETL_FOL_PAH   VARCHAR(50),
    DB_FOL_PAH    VARCHAR(50),
    RPT_FOL_PAH   VARCHAR(50),
    SRC_FIL_PAH   VARCHAR(50),
    TGT_FIL_PAH   VARCHAR(50),
    MTA_EFC_DTE   TIMESTAMP,
    MTA_UPT_DTE   TIMESTAMP,
    CRT_USR_ID    VARCHAR(15),
    UPT_USR_ID    VARCHAR(15)
);

CREATE INDEX DIM_SUB_PRJ_X1 ON
    DIM_SUB_PRJ ( PRJ_ID ASC );

ALTER TABLE DIM_SUB_PRJ ADD CONSTRAINT DIM_SUB_PRJ_PK PRIMARY KEY ( SUB_PRJ_ID );

CREATE TABLE DIM_TST_CAS (
    TC_ID          NUMERIC(20) NOT NULL,
    TST_SET_ID     NUMERIC(19) NOT NULL,
    ENV_ID         NUMERIC(20) NOT NULL,
    TST_CAS_ID     NUMERIC(20) NOT NULL,
    TST_CAS_NM     VARCHAR(50) NOT NULL,
    SRC_ID         NUMERIC(50) NOT NULL,
    TGT_ID         NUMERIC(19) NOT NULL,
    QRY_1          VARCHAR(500) NOT NULL,
    TST_CAS_CPX    VARCHAR(50) NOT NULL,
    WFL_NM         VARCHAR(20),
    QRY_2          VARCHAR(500),
    IS_MTA_IND     VARCHAR(15),
    MTA_CURR_IND   VARCHAR(10),
    MTA_EFC_DTE    TIMESTAMP,
    MTA_UPT_DTE    TIMESTAMP,
    CRT_USR_ID     VARCHAR(15),
    UPT_USR_ID     VARCHAR(15)
);

CREATE INDEX DIM_TST_CAS_X1 ON
    DIM_TST_CAS ( TST_SET_ID ASC );

CREATE INDEX DIM_TST_CAS_X2 ON
    DIM_TST_CAS ( ENV_ID ASC );

ALTER TABLE DIM_TST_CAS ADD CONSTRAINT DIM_TST_CAS_PK PRIMARY KEY ( TC_ID );

CREATE TABLE DIM_TST_INST (
    TST_INST_ID      NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID   VARCHAR(30) NOT NULL,
    TST_SET_ID       NUMERIC(20) NOT NULL,
    TST_INST_NM      VARCHAR(50),
    EXC_STU          VARCHAR(30),
    MTA_EFC_DTE      TIMESTAMP,
    MTA_UPD_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15),
    UPT_USR_ID       VARCHAR(15)
);

CREATE INDEX DIM_TST_INST_X1 ON
    DIM_TST_INST ( LETO_UI_SBM_ID ASC,
    TST_SET_ID ASC );

ALTER TABLE DIM_TST_INST
    ADD CONSTRAINT DIM_TST_INST_PK PRIMARY KEY ( TST_INST_ID,
    LETO_UI_SBM_ID,
    TST_SET_ID );

CREATE TABLE DIM_TST_INST_ATT (
    HPQC_ATT_ID      NUMERIC(20) NOT NULL,
    TST_INST_ID      NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID   VARCHAR(30) NOT NULL,
    TST_SET_ID       NUMERIC(20) NOT NULL,
    ATT_NM           VARCHAR(30) NOT NULL,
    ATT_FIL_TYP      VARCHAR(15),
    ATT_FIL          BYTEA,
    MTA_EFC_DTE      TIMESTAMP,
    MTA_UPD_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15),
    UPT_USR_ID       VARCHAR(15)
);

CREATE INDEX DIM_TST_INST_ATT_X1 ON
    DIM_TST_INST_ATT (
        TST_INST_ID
    ASC,
        LETO_UI_SBM_ID
    ASC,
        TST_SET_ID
    ASC );

ALTER TABLE DIM_TST_INST_ATT ADD CONSTRAINT DIM_TST_INST_ATT_PK PRIMARY KEY ( HPQC_ATT_ID );

CREATE TABLE DIM_TST_SET (
    TST_SET_ID       NUMERIC(19) NOT NULL,
    LETO_UI_SBM_ID   VARCHAR(30) NOT NULL,
    TST_SET_NM       VARCHAR(50) NOT NULL,
    WFL_NM           VARCHAR(30) NOT NULL,
    MTA_EFC_DTE      TIMESTAMP,
    MTA_UPT_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15),
    UPT_USR_ID       VARCHAR(15)
);

CREATE INDEX DIM_TST_SET_X1 ON
    DIM_TST_SET ( LETO_UI_SBM_ID ASC );

ALTER TABLE DIM_TST_SET ADD CONSTRAINT DIM_TST_SET_PK PRIMARY KEY ( TST_SET_ID );

CREATE TABLE DIM_UI_SBM (
    LETO_UI_SBM_ID       VARCHAR(30) NOT NULL,
    SUB_PRJ_ID           NUMERIC(20) NOT NULL,
    USR_ID               VARCHAR(15) NOT NULL,
    LETO_UI_SBM_TYP      VARCHAR(25) NOT NULL,
    UI_TST_SBM_SUB_TYP   VARCHAR(25),
    EXC_STU              VARCHAR(30),
    MTA_EFC_DTE          TIMESTAMP,
    MTA_UPT_DTE          TIMESTAMP,
    CRT_USR_ID           VARCHAR(15),
    UPT_USR_ID           VARCHAR(15)
);

CREATE INDEX DIM_UI_SBM_X1 ON
    DIM_UI_SBM ( SUB_PRJ_ID ASC );

CREATE INDEX DIM_UI_SBM_X2 ON
    DIM_UI_SBM ( USR_ID ASC );

ALTER TABLE DIM_UI_SBM ADD CONSTRAINT DIM_UI_SBM_PK PRIMARY KEY ( LETO_UI_SBM_ID );

CREATE TABLE DIM_USR (
    USR_ID        VARCHAR(15) NOT NULL,
    USR_FUL_NM    VARCHAR(30) NOT NULL,
    DPT           VARCHAR(30) NOT NULL,
    MNG_ID        VARCHAR(15) NOT NULL,
    EMAIL         VARCHAR(25) NOT NULL,
    SUB_PRJ_NM    VARCHAR(30),
    PHONE         VARCHAR(15),
    MTA_EFC_DTE   TIMESTAMP,
    MTA_UPT_DTE   TIMESTAMP
);

ALTER TABLE DIM_USR ADD CONSTRAINT DIM_USR_PK PRIMARY KEY ( USR_ID );

CREATE TABLE FACT_DEP_EXC (                                                      ------*
    DPM_EXC_ID        NUMERIC(20) NOT NULL,
    JIRA_CDA_MOV_ID   NUMERIC(19) NOT NULL,
    UI_EXC_TRG_ID     NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID    VARCHAR(30) NOT NULL,
    RSL               VARCHAR(400) NOT NULL,
    INFA_DEP_RSL      VARCHAR(50),
    EXC_STT_TME       TIMESTAMP,
    EXC_END_TME       TIMESTAMP,
    DB_OBJ_RSL        VARCHAR(50),
    SER_FIL_DEP_RSL   VARCHAR(50),
    MTA_EFC_DTE       TIMESTAMP,
    CRT_USR_ID        VARCHAR(15),
    MTA_UPT_DTE       TIMESTAMP,
    UPT_USR_ID        VARCHAR(15)
);
--SHOW SEARCH_PATH;
---ALTER TABLE FACT_DEP_EXC ALTER COLUMN UI_EXC_TRG_ID TYPE NUMERIC(20);

CREATE INDEX FACT_DEP_EXC_X1 ON
    FACT_DEP_EXC ( JIRA_CDA_MOV_ID ASC );

CREATE INDEX FACT_DEP_EXC_X2 ON
    FACT_DEP_EXC ( UI_EXC_TRG_ID ASC );

ALTER TABLE FACT_DEP_EXC ADD CONSTRAINT FACT_DEP_EXC_PK PRIMARY KEY ( DPM_EXC_ID );

CREATE TABLE FACT_TST_CAS_LOG (
    TST_CAS_LOG_ID    NUMERIC(19) NOT NULL,
    TC_ID             NUMERIC(20) NOT NULL,
    TST_EXC_ID        NUMERIC(20) NOT NULL,
    TST_CAS_EXC_RSL   VARCHAR(500) NOT NULL,
    QRY_1             VARCHAR(400),
    QRY_1_VAL         VARCHAR(100),
    QUERY2            VARCHAR(400),
    QRY_2_VAL         VARCHAR(100),
    TC_LOG_FIL_NM     VARCHAR(100),
    MTA_EFC_DTE       TIMESTAMP,
    CRT_USR_ID        VARCHAR(15)
);

CREATE INDEX FACT_TST_CAS_LOG_X1 ON
    FACT_TST_CAS_LOG ( TC_ID ASC );

CREATE INDEX FACT_TST_CAS_LOG_X2 ON
    FACT_TST_CAS_LOG ( TST_EXC_ID ASC );

ALTER TABLE FACT_TST_CAS_LOG ADD CONSTRAINT FACT_TST_CAS_LOG_PK PRIMARY KEY ( TST_CAS_LOG_ID );

CREATE TABLE FACT_TST_EXC (
    TST_EXC_ID        NUMERIC(20) NOT NULL,
    JIRA_CDA_MOV_ID   NUMERIC(19) NOT NULL,
    UI_EXC_TRG_ID     NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID    VARCHAR(30) NOT NULL,
    EXC_STT_TME       TIMESTAMP,
    EXC_END_TME       TIMESTAMP,
    TOT_EXC_TME       NUMERIC(7,2),
    TST_EXC_RSL       VARCHAR(50),
    MTA_EFC_DTE       TIMESTAMP,
    CRT_USR_ID        VARCHAR(15)
);

CREATE INDEX FACT_TST_EXC_X1 ON
    FACT_TST_EXC ( JIRA_CDA_MOV_ID ASC );

CREATE INDEX FACT_TST_EXC_X2 ON
    FACT_TST_EXC ( UI_EXC_TRG_ID ASC );

ALTER TABLE FACT_TST_EXC ADD CONSTRAINT FACT_TST_EXC_PK PRIMARY KEY ( TST_EXC_ID );

CREATE TABLE FACT_TST_INST_LOG (
    TST_INST_ID      NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID   VARCHAR(30) NOT NULL,
    TST_SET_ID       NUMERIC(20) NOT NULL,
    TST_EXC_ID       NUMERIC(20) NOT NULL,
    TST_RSL_ID       SERIAL NOT NULL,
    TST_RSL_FIL_NM   VARCHAR(30),
    TST_STP_RSL      VARCHAR(30),
    MTA_EFC_DTE      TIMESTAMP,
    TOT_EXC_TME      NUMERIC(7,2)
);

CREATE INDEX FACT_TST_INST_LOG_X1 ON
    FACT_TST_INST_LOG (
        TST_INST_ID
    ASC,
        LETO_UI_SBM_ID
    ASC,
        TST_SET_ID
    ASC );

CREATE INDEX FACT_TST_INST_LOG_X2 ON
    FACT_TST_INST_LOG ( TST_EXC_ID ASC );
	
ALTER TABLE FACT_TST_INST_LOG
    ADD CONSTRAINT FACT_TST_INST_LOG_PK PRIMARY KEY ( TST_RSL_ID,
    TST_INST_ID,
    LETO_UI_SBM_ID,
    TST_SET_ID );	

CREATE TABLE UI_EXC_TRG (
    UI_EXC_TRG_ID         NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID        VARCHAR(30) NOT NULL,
    SUB_PRJ_ID            NUMERIC(20) NOT NULL,
    UI_EXC_TRG_TYP        VARCHAR(20),
    TST_EXC_TRG_SUB_TYP   VARCHAR(15),
    MTA_EFC_DTE           TIMESTAMP,
    CRT_USR_ID            VARCHAR(15)
);

--ALTER TABLE UI_EXC_TRG ALTER COLUMN UI_EXC_TRG_ID TYPE NUMERIC(20);

CREATE INDEX UI_EXC_TRG_X1 ON
    UI_EXC_TRG ( LETO_UI_SBM_ID ASC );

CREATE INDEX UI_EXC_TRG_X2 ON
    UI_EXC_TRG ( SUB_PRJ_ID ASC );

ALTER TABLE UI_EXC_TRG ADD CONSTRAINT UI_EXC_TRG_PK PRIMARY KEY ( UI_EXC_TRG_ID );

CREATE TABLE USR_ROL_GRP (
    USR_ID           VARCHAR(15) NOT NULL,
    ROL_ID           NUMERIC(20) NOT NULL,
    USR_ROL_GRP_ID   VARCHAR(15),
    MTA_EFC_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15)
);

CREATE INDEX USR_ROL_GRP_X1 ON
    USR_ROL_GRP ( USR_ID ASC );

CREATE INDEX USR_ROL_GRP_X2 ON
    USR_ROL_GRP ( ROL_ID ASC );

ALTER TABLE DB_SCH_DTA
    ADD CONSTRAINT DB_SCH_DTA_F1 FOREIGN KEY ( PRJ_ID,
    CONN_DTA_ID,
    ENV_NM )
        REFERENCES DIM_CONN_DTA ( PRJ_ID,
        CONN_DTA_ID,
        ENV_NM );

ALTER TABLE DIM_CONN_DTA
    ADD CONSTRAINT DIM_CONN_DTA_F1 FOREIGN KEY ( PRJ_ID )
        REFERENCES DIM_PRJ ( PRJ_ID );

ALTER TABLE DIM_DB_DEP_OBJ
    ADD CONSTRAINT DIM_DB_DEP_OBJ_F1 FOREIGN KEY ( LETO_UI_SBM_ID )
        REFERENCES DIM_UI_SBM ( LETO_UI_SBM_ID );

ALTER TABLE DIM_HPQC_TST_SET
    ADD CONSTRAINT DIM_HPQC_TST_SET_F1 FOREIGN KEY ( LETO_UI_SBM_ID )
        REFERENCES DIM_UI_SBM ( LETO_UI_SBM_ID );

ALTER TABLE DIM_INFA_DPM
    ADD CONSTRAINT DIM_INFA_DPM_F1 FOREIGN KEY ( LETO_UI_SBM_ID )
        REFERENCES DIM_UI_SBM ( LETO_UI_SBM_ID );

ALTER TABLE DIM_JIRA_CDA_MOVE
    ADD CONSTRAINT DIM_JIRA_CDA_MOVE_F1 FOREIGN KEY ( JIRA_CDA_ID )
        REFERENCES DIM_JIRA ( JIRA_CDA_ID );

ALTER TABLE DIM_JIRA
    ADD CONSTRAINT DIM_JIRA_F1 FOREIGN KEY ( SUB_PRJ_ID )
        REFERENCES DIM_SUB_PRJ ( SUB_PRJ_ID );

ALTER TABLE DIM_PRJ
    ADD CONSTRAINT DIM_PRJ_F1 FOREIGN KEY ( USR_ID )
        REFERENCES DIM_USR ( USR_ID );

ALTER TABLE DIM_SER_FIL
    ADD CONSTRAINT DIM_SER_FIL_F1 FOREIGN KEY ( LETO_UI_SBM_ID )
        REFERENCES DIM_UI_SBM ( LETO_UI_SBM_ID );

ALTER TABLE DIM_SUB_PRJ
    ADD CONSTRAINT DIM_SUB_PRJ_F1 FOREIGN KEY ( PRJ_ID )
        REFERENCES DIM_PRJ ( PRJ_ID );

ALTER TABLE DIM_TST_CAS
    ADD CONSTRAINT DIM_TST_CAS_F1 FOREIGN KEY ( TST_SET_ID )
        REFERENCES DIM_TST_SET ( TST_SET_ID );

ALTER TABLE DIM_TST_CAS
    ADD CONSTRAINT DIM_TST_CAS_F2 FOREIGN KEY ( ENV_ID )
        REFERENCES DIM_ENV ( ENV_ID );

ALTER TABLE DIM_TST_INST_ATT
    ADD CONSTRAINT DIM_TST_INST_ATT_F1 FOREIGN KEY ( TST_INST_ID,
    LETO_UI_SBM_ID,
    TST_SET_ID )
        REFERENCES DIM_TST_INST ( TST_INST_ID,
        LETO_UI_SBM_ID,
        TST_SET_ID );

ALTER TABLE DIM_TST_INST
    ADD CONSTRAINT DIM_TST_INST_F1 FOREIGN KEY ( LETO_UI_SBM_ID,
    TST_SET_ID )
        REFERENCES DIM_HPQC_TST_SET ( LETO_UI_SBM_ID,
        TST_SET_ID );

ALTER TABLE DIM_TST_SET
    ADD CONSTRAINT DIM_TST_SET_F1 FOREIGN KEY ( LETO_UI_SBM_ID )
        REFERENCES DIM_UI_SBM ( LETO_UI_SBM_ID );

ALTER TABLE DIM_UI_SBM
    ADD CONSTRAINT DIM_UI_SBM_F1 FOREIGN KEY ( SUB_PRJ_ID )
        REFERENCES DIM_SUB_PRJ ( SUB_PRJ_ID );

ALTER TABLE DIM_UI_SBM
    ADD CONSTRAINT DIM_UI_SBM_F2 FOREIGN KEY ( USR_ID )
        REFERENCES DIM_USR ( USR_ID );

ALTER TABLE FACT_DEP_EXC
    ADD CONSTRAINT FACT_DEP_EXC_F1 FOREIGN KEY ( JIRA_CDA_MOV_ID )
        REFERENCES DIM_JIRA_CDA_MOVE ( JIRA_CDA_MOV_ID );

ALTER TABLE FACT_DEP_EXC
    ADD CONSTRAINT FACT_DEP_EXC_F2 FOREIGN KEY ( UI_EXC_TRG_ID )
        REFERENCES UI_EXC_TRG ( UI_EXC_TRG_ID );

ALTER TABLE FACT_TST_CAS_LOG
    ADD CONSTRAINT FACT_TST_CAS_LOG_F1 FOREIGN KEY ( TC_ID )
        REFERENCES DIM_TST_CAS ( TC_ID );

ALTER TABLE FACT_TST_CAS_LOG
    ADD CONSTRAINT FACT_TST_CAS_LOG_F2 FOREIGN KEY ( TST_EXC_ID )
        REFERENCES FACT_TST_EXC ( TST_EXC_ID );

ALTER TABLE FACT_TST_EXC
    ADD CONSTRAINT FACT_TST_EXC_F1 FOREIGN KEY ( JIRA_CDA_MOV_ID )
        REFERENCES DIM_JIRA_CDA_MOVE ( JIRA_CDA_MOV_ID );

ALTER TABLE FACT_TST_EXC
    ADD CONSTRAINT FACT_TST_EXC_F2 FOREIGN KEY ( UI_EXC_TRG_ID )               --------error
        REFERENCES UI_EXC_TRG ( UI_EXC_TRG_ID );

--set search_path to apdp;
--show search_path;

ALTER TABLE FACT_TST_INST_LOG
    ADD CONSTRAINT FACT_TST_INST_LOG_F1 FOREIGN KEY ( TST_INST_ID,
    LETO_UI_SBM_ID,
    TST_SET_ID )
        REFERENCES DIM_TST_INST ( TST_INST_ID,
        LETO_UI_SBM_ID,
        TST_SET_ID );

ALTER TABLE FACT_TST_INST_LOG
    ADD CONSTRAINT FACT_TST_INST_LOG_F2 FOREIGN KEY ( TST_EXC_ID )
        REFERENCES FACT_TST_EXC ( TST_EXC_ID );

ALTER TABLE UI_EXC_TRG
    ADD CONSTRAINT UI_EXC_TRG_F1 FOREIGN KEY ( LETO_UI_SBM_ID )
        REFERENCES DIM_UI_SBM ( LETO_UI_SBM_ID );

ALTER TABLE UI_EXC_TRG
    ADD CONSTRAINT UI_EXC_TRG_F2 FOREIGN KEY ( SUB_PRJ_ID )
        REFERENCES DIM_SUB_PRJ ( SUB_PRJ_ID );

ALTER TABLE USR_ROL_GRP
    ADD CONSTRAINT USR_ROL_GRP_F1 FOREIGN KEY ( USR_ID )
        REFERENCES DIM_USR ( USR_ID );

ALTER TABLE USR_ROL_GRP
    ADD CONSTRAINT USR_ROL_GRP_F2 FOREIGN KEY ( ROL_ID )
        REFERENCES DIM_ROL ( ROL_ID );

commit;