-- ============================================================================
-- 瀚高数据库(HighGo DB) 建表脚本 - PostgreSQL兼容模式
-- 基于文档: 项目基础信息表.docx
-- 生成时间: 2026-07-08
-- 类型映射: VARCHAR2→VARCHAR, NUMBER→NUMERIC
-- ============================================================================

-- ============================================================================
-- 一、业务表（15张）
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. ATT_PROJ_BASE - 项目基础信息表
--    表编号：ATT_011
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_PROJ_BASE (
    GUID              CHAR(32)         NOT NULL,
    PROJ_CODE         VARCHAR(20)      NOT NULL,
    PROJ_NAME         VARCHAR(100)     NOT NULL,
    CON_UNIT          VARCHAR(256),
    YEAR              CHAR(4),
    APP_NUM           VARCHAR(20),
    PROJ_TYPE         CHAR(1),
    PROJ_TOT_INV_FUND NUMERIC(12,4),
    LEG_PERS          VARCHAR(20),
    PROJ_BRIN         VARCHAR(1024),
    PROJ_BEG_DATE     DATE,
    PROJ_COMP_DATE    DATE,
    ACC_DATE          DATE,
    NOTE              VARCHAR(256),
    COLL_TIME         TIMESTAMP        NOT NULL,
    UPD_TIME          TIMESTAMP,
    REC_PERS          CHAR(32),
    CONSTRAINT PK_ATT_PROJ_BASE PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_PROJ_BASE IS '项目基础信息表';
COMMENT ON COLUMN ATT_PROJ_BASE.GUID              IS '全球唯一标识码，记录入库时系统自动生成';
COMMENT ON COLUMN ATT_PROJ_BASE.PROJ_CODE         IS '项目代码';
COMMENT ON COLUMN ATT_PROJ_BASE.PROJ_NAME         IS '项目名称';
COMMENT ON COLUMN ATT_PROJ_BASE.CON_UNIT          IS '建设单位全称';
COMMENT ON COLUMN ATT_PROJ_BASE.YEAR              IS '年度，4位定长文本';
COMMENT ON COLUMN ATT_PROJ_BASE.APP_NUM           IS '批复文号';
COMMENT ON COLUMN ATT_PROJ_BASE.PROJ_TYPE         IS '项目类型(字典: 项目类型)';
COMMENT ON COLUMN ATT_PROJ_BASE.PROJ_TOT_INV_FUND IS '项目总投资(万元)，记至4位小数';
COMMENT ON COLUMN ATT_PROJ_BASE.LEG_PERS          IS '项目负责人姓名';
COMMENT ON COLUMN ATT_PROJ_BASE.PROJ_BRIN         IS '项目简介';
COMMENT ON COLUMN ATT_PROJ_BASE.PROJ_BEG_DATE     IS '项目启动时间(YYYY-MM-DD)';
COMMENT ON COLUMN ATT_PROJ_BASE.PROJ_COMP_DATE    IS '项目竣工时间(YYYY-MM-DD)';
COMMENT ON COLUMN ATT_PROJ_BASE.ACC_DATE          IS '项目验收时间(YYYY-MM-DD)';
COMMENT ON COLUMN ATT_PROJ_BASE.NOTE              IS '备注';
COMMENT ON COLUMN ATT_PROJ_BASE.COLL_TIME         IS '采集时间';
COMMENT ON COLUMN ATT_PROJ_BASE.UPD_TIME          IS '更新时间，本条记录未被更新时为空';
COMMENT ON COLUMN ATT_PROJ_BASE.REC_PERS          IS '记录人员GUID，引用ATT_PERS_BASE';

CREATE UNIQUE INDEX IDX_ATT_PROJ_BASE_GUID ON ATT_PROJ_BASE (GUID);
CREATE INDEX IDX_ATT_PROJ_BASE_PROJ_CODE ON ATT_PROJ_BASE (PROJ_CODE);

-- ----------------------------------------------------------------------------
-- 2. ATT_PERS_BASE - 人员基础信息表
--    表编号：ATT_012
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_PERS_BASE (
    GUID          CHAR(32)         NOT NULL,
    PERS_NAME     VARCHAR(60)      NOT NULL,
    PERS_CODE     CHAR(18)         NOT NULL,
    ORG_GUID      CHAR(32),
    PERS_TYPE     CHAR(2)          NOT NULL,
    SEX           CHAR(1)          NOT NULL,
    TELNUMB       VARCHAR(22),
    FAXNUMB       VARCHAR(22),
    MOBILENUMB    VARCHAR(11),
    EMAIL         VARCHAR(60),
    BORN_PLAC     VARCHAR(100),
    BORN_DATE     DATE,
    NATION        CHAR(2),
    ADM_DUTY      VARCHAR(3),
    TITLE         VARCHAR(40),
    SCHOOL        VARCHAR(100),
    MAJOB         VARCHAR(100),
    HIGH_DEGR     VARCHAR(10),
    WDATETIME     DATE,
    STATUS        CHAR(1),
    NOTE          VARCHAR(256),
    COLL_TIME     TIMESTAMP        NOT NULL,
    UPD_TIME      TIMESTAMP,
    REC_PERS      CHAR(32),
    CONSTRAINT PK_ATT_PERS_BASE PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_PERS_BASE IS '人员基础信息表';
COMMENT ON COLUMN ATT_PERS_BASE.GUID       IS '全球唯一标识码，记录入库时系统自动生成';
COMMENT ON COLUMN ATT_PERS_BASE.PERS_NAME  IS '人员姓名';
COMMENT ON COLUMN ATT_PERS_BASE.PERS_CODE  IS '公民身份号码';
COMMENT ON COLUMN ATT_PERS_BASE.ORG_GUID   IS '所属机构GUID';
COMMENT ON COLUMN ATT_PERS_BASE.PERS_TYPE  IS '人员类型(字典: 人员类型)';
COMMENT ON COLUMN ATT_PERS_BASE.SEX        IS '性别(字典: 性别)';
COMMENT ON COLUMN ATT_PERS_BASE.TELNUMB    IS '办公室电话';
COMMENT ON COLUMN ATT_PERS_BASE.FAXNUMB    IS '传真号码';
COMMENT ON COLUMN ATT_PERS_BASE.MOBILENUMB IS '手机号码';
COMMENT ON COLUMN ATT_PERS_BASE.EMAIL      IS '电子邮件';
COMMENT ON COLUMN ATT_PERS_BASE.BORN_PLAC  IS '出生地';
COMMENT ON COLUMN ATT_PERS_BASE.BORN_DATE  IS '出生日期';
COMMENT ON COLUMN ATT_PERS_BASE.NATION     IS '民族(字典: 民族)';
COMMENT ON COLUMN ATT_PERS_BASE.ADM_DUTY   IS '职务级别(字典: 职务级别)';
COMMENT ON COLUMN ATT_PERS_BASE.TITLE      IS '技术职称';
COMMENT ON COLUMN ATT_PERS_BASE.SCHOOL     IS '毕业院校';
COMMENT ON COLUMN ATT_PERS_BASE.MAJOB      IS '所学专业';
COMMENT ON COLUMN ATT_PERS_BASE.HIGH_DEGR  IS '最高学历';
COMMENT ON COLUMN ATT_PERS_BASE.WDATETIME  IS '参加工作时间';
COMMENT ON COLUMN ATT_PERS_BASE.STATUS     IS '状态(1启用 8停用 9注销)';
COMMENT ON COLUMN ATT_PERS_BASE.NOTE       IS '备注';
COMMENT ON COLUMN ATT_PERS_BASE.COLL_TIME  IS '采集时间';
COMMENT ON COLUMN ATT_PERS_BASE.UPD_TIME   IS '更新时间，本条记录未被更新时为空';
COMMENT ON COLUMN ATT_PERS_BASE.REC_PERS   IS '记录人员GUID，引用ATT_PERS_BASE';

CREATE UNIQUE INDEX IDX_ATT_PERS_BASE_GUID ON ATT_PERS_BASE (GUID);
CREATE INDEX IDX_ATT_PERS_BASE_PERS_CODE ON ATT_PERS_BASE (PERS_CODE);
CREATE INDEX IDX_ATT_PERS_BASE_ORG_GUID ON ATT_PERS_BASE (ORG_GUID);

-- ----------------------------------------------------------------------------
-- 3. ATT_PERS_EXT - 监理单位项目组成员信息表
--    表编号：ATT_013
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_PERS_EXT (
    GUID          CHAR(32)         NOT NULL,
    PERS_NAME     VARCHAR(60)      NOT NULL,
    TEND_GUID     CHAR(32),
    ADM_DUTY      VARCHAR(3),
    SEX           CHAR(1),
    BORN_DATE     DATE,
    WORK_DATE     VARCHAR(10),
    NOTE          VARCHAR(256),
    COLL_TIME     TIMESTAMP        NOT NULL,
    UPD_TIME      TIMESTAMP,
    REC_PERS      CHAR(32),
    CONSTRAINT PK_ATT_PERS_EXT PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_PERS_EXT IS '监理单位项目组成员信息表';
COMMENT ON COLUMN ATT_PERS_EXT.GUID      IS '全球唯一标识码，记录入库时系统自动生成';
COMMENT ON COLUMN ATT_PERS_EXT.PERS_NAME IS '人员姓名';
COMMENT ON COLUMN ATT_PERS_EXT.TEND_GUID IS '所属标段GUID';
COMMENT ON COLUMN ATT_PERS_EXT.ADM_DUTY  IS '职务级别';
COMMENT ON COLUMN ATT_PERS_EXT.SEX       IS '性别';
COMMENT ON COLUMN ATT_PERS_EXT.BORN_DATE IS '出生日期';
COMMENT ON COLUMN ATT_PERS_EXT.WORK_DATE IS '累计从事监理工作时间';
COMMENT ON COLUMN ATT_PERS_EXT.NOTE      IS '备注';
COMMENT ON COLUMN ATT_PERS_EXT.COLL_TIME IS '采集时间';
COMMENT ON COLUMN ATT_PERS_EXT.UPD_TIME  IS '更新时间，本条记录未被更新时为空';
COMMENT ON COLUMN ATT_PERS_EXT.REC_PERS  IS '记录人员GUID，引用ATT_PERS_BASE';

CREATE UNIQUE INDEX IDX_ATT_PERS_EXT_GUID ON ATT_PERS_EXT (GUID);
CREATE INDEX IDX_ATT_PERS_EXT_PERS_NAME ON ATT_PERS_EXT (PERS_NAME);
CREATE INDEX IDX_ATT_PERS_EXT_BORN_DATE ON ATT_PERS_EXT (BORN_DATE);
CREATE INDEX IDX_ATT_PERS_EXT_TEND_GUID ON ATT_PERS_EXT (TEND_GUID);

-- ----------------------------------------------------------------------------
-- 4. ATT_ORG_BASE - 机构基础信息表（水利机构）
--    表编号：ATT_014
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_ORG_BASE (
    GUID          CHAR(32)         NOT NULL,
    PGUID         CHAR(32),
    ORG_CODE      VARCHAR(40)      NOT NULL,
    ORG_NAME      VARCHAR(512)     NOT NULL,
    ORG_LEVEL     CHAR(2)          NOT NULL,
    ORG_GRAD      VARCHAR(512),
    ORG_TYPE      CHAR(1)          NOT NULL,
    ORDERNUM      NUMERIC,
    AD_CODE       VARCHAR(40),
    JURD          VARCHAR(15),
    AREA_TYPE     CHAR(1),
    NODE_CODE     VARCHAR(20),
    STATUS        CHAR(1),
    MODIFIER      CHAR(32),
    FROM_DATE     TIME             NOT NULL,
    TO_DATE       TIME,
    NOTE          VARCHAR(2048),
    CONSTRAINT PK_ATT_ORG_BASE PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_ORG_BASE IS '机构基础信息表（水利机构）';
COMMENT ON COLUMN ATT_ORG_BASE.GUID      IS '全球唯一标识码，记录入库时系统自动生成';
COMMENT ON COLUMN ATT_ORG_BASE.PGUID     IS '父GUID';
COMMENT ON COLUMN ATT_ORG_BASE.ORG_CODE  IS '机构代码';
COMMENT ON COLUMN ATT_ORG_BASE.ORG_NAME  IS '机构名称';
COMMENT ON COLUMN ATT_ORG_BASE.ORG_LEVEL IS '级别(字典: 机构级别)';
COMMENT ON COLUMN ATT_ORG_BASE.ORG_GRAD  IS '职责';
COMMENT ON COLUMN ATT_ORG_BASE.ORG_TYPE  IS '类型(字典: 机构类型)';
COMMENT ON COLUMN ATT_ORG_BASE.ORDERNUM  IS '顺序号';
COMMENT ON COLUMN ATT_ORG_BASE.AD_CODE   IS '机构所在地行政区划';
COMMENT ON COLUMN ATT_ORG_BASE.JURD      IS '管辖范围';
COMMENT ON COLUMN ATT_ORG_BASE.AREA_TYPE IS '区域类型(字典: 区域类型)';
COMMENT ON COLUMN ATT_ORG_BASE.NODE_CODE IS '所属节点';
COMMENT ON COLUMN ATT_ORG_BASE.STATUS    IS '状态(1启用 2停用 9注销)';
COMMENT ON COLUMN ATT_ORG_BASE.MODIFIER  IS '修改人GUID，引用ATT_PERS_BASE';
COMMENT ON COLUMN ATT_ORG_BASE.FROM_DATE IS '属性建立时间';
COMMENT ON COLUMN ATT_ORG_BASE.TO_DATE   IS '属性终止时间';
COMMENT ON COLUMN ATT_ORG_BASE.NOTE      IS '备注';

CREATE UNIQUE INDEX IDX_ATT_ORG_BASE_GUID ON ATT_ORG_BASE (GUID);
CREATE INDEX IDX_ATT_ORG_BASE_PGUID ON ATT_ORG_BASE (PGUID);
CREATE INDEX IDX_ATT_ORG_BASE_MODIFIER ON ATT_ORG_BASE (MODIFIER);

-- ----------------------------------------------------------------------------
-- 5. ATT_ORG_EXT - 水利机构扩展信息表
--    表编号：ATT_015
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_ORG_EXT (
    GUID                   CHAR(32)         NOT NULL,
    SOC_CRE_CODE           VARCHAR(20),
    ORG_GUID               CHAR(32),
    WIUN_NAME              VARCHAR(100)     NOT NULL,
    ORG_CLIEN_TYPE         CHAR(1),
    ORG_TYPE               CHAR(1),
    ORG_LEVEL              CHAR(2),
    WIUN_CODE              VARCHAR(20),
    REG_NAME               VARCHAR(100),
    LEG_REP                VARCHAR(100),
    WIUN_WAO_LONG          NUMERIC(11,8),
    WIUN_WAO_LAT           NUMERIC(10,8),
    WIUN_PROP              CHAR(1),
    ADMIN_WIUN_GUID        CHAR(32),
    IF_WATE_ENTE           CHAR(1),
    PRO_ORG_GUID           CHAR(32),
    IS_LEG_ENTI            CHAR(1),
    IF_MINI_DIRE           CHAR(1),
    CONTR_QUAL_GRAD        CHAR(1),
    CONTR_CERT_NUM         VARCHAR(40),
    SUP_QUAL_GRAD          CHAR(1),
    STAN_GRAD              CHAR(1),
    STAN_CERT_CODE         VARCHAR(40),
    SAFE_PROD_LICE_CODE    VARCHAR(40),
    FUND_SOUR              CHAR(1),
    EST_DATE               DATE,
    REGI_CAPI              NUMERIC(12),
    ADDR                   VARCHAR(100),
    REG_LOC                VARCHAR(100),
    MAJ_ACCI               VARCHAR(100),
    LEG_PERS               VARCHAR(40),
    LEG_PERS_TEL           VARCHAR(40),
    CONTA_PERS             VARCHAR(40),
    CONTA_PERS_MOBI        VARCHAR(40),
    CONTA_PERS_ADDR        VARCHAR(100),
    SAFETY_LEG_PERS        VARCHAR(40),
    SAFETY_LEG_PERS_DUTY   VARCHAR(40),
    SAFETY_LEG_PERS_TEL    VARCHAR(22),
    SAFETY_LEG_PERS_MOPH   VARCHAR(11),
    SAFE_PROD_CONTA        VARCHAR(40),
    SAFE_PROD_CONTA_TEL    VARCHAR(22),
    SAFE_PROD_CONTA_MPOH   VARCHAR(11),
    ACC_EME_CONTA          VARCHAR(40),
    ACC_EME_CONTA_MPOH     VARCHAR(11),
    WORK_POP_NUM           NUMERIC(11),
    SAFE_MANA_ORG          VARCHAR(100),
    FULL_MANA_TOT          NUMERIC(14),
    SPE_OPER_PERS_TOT      NUMERIC(14),
    LEG_RESP_PERS_TEL      VARCHAR(22),
    LEG_RESP_PERS_FAX      VARCHAR(22),
    STAN_CONTA_PERS        VARCHAR(20),
    STAN_CONTA_TEL         VARCHAR(22),
    STAN_CONTA_FAX         VARCHAR(22),
    STAN_CONTA_MOPH        VARCHAR(11),
    STAN_CONTA_EMAIL       VARCHAR(100),
    NOTE                   VARCHAR(256),
    COLL_TIME              TIMESTAMP        NOT NULL,
    UPD_TIME               TIMESTAMP,
    REC_PERS               CHAR(32),
    CONSTRAINT PK_ATT_ORG_EXT PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_ORG_EXT IS '水利机构扩展信息表';
COMMENT ON COLUMN ATT_ORG_EXT.GUID                   IS '全球唯一标识码';
COMMENT ON COLUMN ATT_ORG_EXT.SOC_CRE_CODE           IS '社会信用代码';
COMMENT ON COLUMN ATT_ORG_EXT.ORG_GUID               IS '机构GUID';
COMMENT ON COLUMN ATT_ORG_EXT.WIUN_NAME              IS '单位名称';
COMMENT ON COLUMN ATT_ORG_EXT.ORG_CLIEN_TYPE         IS '单位类型(客户端)';
COMMENT ON COLUMN ATT_ORG_EXT.ORG_TYPE               IS '单位类型';
COMMENT ON COLUMN ATT_ORG_EXT.ORG_LEVEL              IS '单位规格';
COMMENT ON COLUMN ATT_ORG_EXT.WIUN_CODE              IS '单位编码';
COMMENT ON COLUMN ATT_ORG_EXT.REG_NAME               IS '注册名称';
COMMENT ON COLUMN ATT_ORG_EXT.LEG_REP                IS '法定代表人';
COMMENT ON COLUMN ATT_ORG_EXT.WIUN_WAO_LONG          IS '单位经度(°)';
COMMENT ON COLUMN ATT_ORG_EXT.WIUN_WAO_LAT           IS '单位纬度(°)';
COMMENT ON COLUMN ATT_ORG_EXT.WIUN_PROP              IS '单位性质';
COMMENT ON COLUMN ATT_ORG_EXT.ADMIN_WIUN_GUID        IS '所属行政主管部门GUID';
COMMENT ON COLUMN ATT_ORG_EXT.IF_WATE_ENTE           IS '是否水利系统';
COMMENT ON COLUMN ATT_ORG_EXT.PRO_ORG_GUID           IS '上级单位';
COMMENT ON COLUMN ATT_ORG_EXT.IS_LEG_ENTI            IS '是否项目法人单位';
COMMENT ON COLUMN ATT_ORG_EXT.IF_MINI_DIRE           IS '是否部直属单位';
COMMENT ON COLUMN ATT_ORG_EXT.CONTR_QUAL_GRAD        IS '施工总承包资质';
COMMENT ON COLUMN ATT_ORG_EXT.CONTR_CERT_NUM         IS '施工总承包资质证书编号';
COMMENT ON COLUMN ATT_ORG_EXT.SUP_QUAL_GRAD          IS '监理资质等级';
COMMENT ON COLUMN ATT_ORG_EXT.STAN_GRAD              IS '标准化等级';
COMMENT ON COLUMN ATT_ORG_EXT.STAN_CERT_CODE         IS '标准化证书编号';
COMMENT ON COLUMN ATT_ORG_EXT.SAFE_PROD_LICE_CODE    IS '安全生产许可证编号';
COMMENT ON COLUMN ATT_ORG_EXT.FUND_SOUR              IS '经费来源';
COMMENT ON COLUMN ATT_ORG_EXT.EST_DATE               IS '成立日期';
COMMENT ON COLUMN ATT_ORG_EXT.REGI_CAPI              IS '注册资本(万元)';
COMMENT ON COLUMN ATT_ORG_EXT.ADDR                   IS '单位地址';
COMMENT ON COLUMN ATT_ORG_EXT.REG_LOC                IS '注册地址';
COMMENT ON COLUMN ATT_ORG_EXT.MAJ_ACCI               IS '发生重大安全事故情况';
COMMENT ON COLUMN ATT_ORG_EXT.LEG_PERS               IS '主要负责人';
COMMENT ON COLUMN ATT_ORG_EXT.LEG_PERS_TEL           IS '主要负责人手机';
COMMENT ON COLUMN ATT_ORG_EXT.CONTA_PERS             IS '联系人';
COMMENT ON COLUMN ATT_ORG_EXT.CONTA_PERS_MOBI        IS '联系人电话';
COMMENT ON COLUMN ATT_ORG_EXT.CONTA_PERS_ADDR        IS '联系人联系地址';
COMMENT ON COLUMN ATT_ORG_EXT.SAFETY_LEG_PERS        IS '分管安全负责人';
COMMENT ON COLUMN ATT_ORG_EXT.SAFETY_LEG_PERS_DUTY   IS '分管安全负责人职务';
COMMENT ON COLUMN ATT_ORG_EXT.SAFETY_LEG_PERS_TEL    IS '分管安全负责人电话';
COMMENT ON COLUMN ATT_ORG_EXT.SAFETY_LEG_PERS_MOPH   IS '分管安全负责人手机';
COMMENT ON COLUMN ATT_ORG_EXT.SAFE_PROD_CONTA        IS '安全生产联系人';
COMMENT ON COLUMN ATT_ORG_EXT.SAFE_PROD_CONTA_TEL    IS '安全生产联系人电话';
COMMENT ON COLUMN ATT_ORG_EXT.SAFE_PROD_CONTA_MPOH   IS '安全生产联系人手机';
COMMENT ON COLUMN ATT_ORG_EXT.ACC_EME_CONTA          IS '事故应急联系人';
COMMENT ON COLUMN ATT_ORG_EXT.ACC_EME_CONTA_MPOH     IS '事故应急联系人手机';
COMMENT ON COLUMN ATT_ORG_EXT.WORK_POP_NUM           IS '从业人员人数(人)';
COMMENT ON COLUMN ATT_ORG_EXT.SAFE_MANA_ORG          IS '安全管理机构';
COMMENT ON COLUMN ATT_ORG_EXT.FULL_MANA_TOT          IS '专职管理人员总数(人)';
COMMENT ON COLUMN ATT_ORG_EXT.SPE_OPER_PERS_TOT      IS '特种作业人员总数(人)';
COMMENT ON COLUMN ATT_ORG_EXT.LEG_RESP_PERS_TEL      IS '法定代表人电话';
COMMENT ON COLUMN ATT_ORG_EXT.LEG_RESP_PERS_FAX      IS '法定代表人传真';
COMMENT ON COLUMN ATT_ORG_EXT.STAN_CONTA_PERS        IS '标准化联系人';
COMMENT ON COLUMN ATT_ORG_EXT.STAN_CONTA_TEL         IS '标准化联系人电话';
COMMENT ON COLUMN ATT_ORG_EXT.STAN_CONTA_FAX         IS '标准化联系人传真';
COMMENT ON COLUMN ATT_ORG_EXT.STAN_CONTA_MOPH        IS '标准化联系人手机';
COMMENT ON COLUMN ATT_ORG_EXT.STAN_CONTA_EMAIL       IS '标准化联系人电子邮箱';
COMMENT ON COLUMN ATT_ORG_EXT.NOTE                   IS '备注';
COMMENT ON COLUMN ATT_ORG_EXT.COLL_TIME              IS '采集时间';
COMMENT ON COLUMN ATT_ORG_EXT.UPD_TIME               IS '更新时间';
COMMENT ON COLUMN ATT_ORG_EXT.REC_PERS               IS '记录人员GUID';

CREATE UNIQUE INDEX IDX_ATT_ORG_EXT_GUID ON ATT_ORG_EXT (GUID);
CREATE INDEX IDX_ATT_ORG_EXT_ORG_GUID ON ATT_ORG_EXT (ORG_GUID);
CREATE INDEX IDX_ATT_ORG_EXT_ADMIN_WIUN_GUID ON ATT_ORG_EXT (ADMIN_WIUN_GUID);

-- ----------------------------------------------------------------------------
-- 6. ATT_ORG_UWAT_BASE - 参建单位信息表
--    表编号：ATT_016
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_ORG_UWAT_BASE (
    GUID          CHAR(32)         NOT NULL,
    PGUID         CHAR(32),
    ORG_CODE      VARCHAR(40)      NOT NULL,
    ORG_NAME      VARCHAR(512)     NOT NULL,
    ORG_LEVEL     CHAR(2)          NOT NULL,
    ORG_GRAD      VARCHAR(512),
    ORG_TYPE      CHAR(1)          NOT NULL,
    ORDERNUM      NUMERIC,
    AD_CODE       VARCHAR(40),
    JURD          VARCHAR(15),
    AREA_TYPE     CHAR(1),
    NODE_CODE     VARCHAR(20),
    STATUS        CHAR(1),
    MODIFIER      CHAR(32),
    FROM_DATE     TIMESTAMP        NOT NULL,
    TO_DATE       TIMESTAMP,
    NOTE          VARCHAR(2048),
    CONSTRAINT PK_ATT_ORG_UWAT_BASE PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_ORG_UWAT_BASE IS '参建单位信息表';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.GUID      IS '全球唯一标识码';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.PGUID     IS '父GUID';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.ORG_CODE  IS '机构代码';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.ORG_NAME  IS '机构名称';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.ORG_LEVEL IS '级别';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.ORG_GRAD  IS '职责';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.ORG_TYPE  IS '类型';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.ORDERNUM  IS '顺序号';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.AD_CODE   IS '机构所在地行政区划';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.JURD      IS '管辖范围';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.AREA_TYPE IS '区域类型';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.NODE_CODE IS '所属节点';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.STATUS    IS '状态';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.MODIFIER  IS '修改人GUID';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.FROM_DATE IS '属性建立时间';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.TO_DATE   IS '属性终止时间';
COMMENT ON COLUMN ATT_ORG_UWAT_BASE.NOTE      IS '备注';

CREATE UNIQUE INDEX IDX_ATT_ORG_UWAT_BASE_GUID ON ATT_ORG_UWAT_BASE (GUID);
CREATE INDEX IDX_ATT_ORG_UWAT_BASE_PGUID ON ATT_ORG_UWAT_BASE (PGUID);
CREATE INDEX IDX_ATT_ORG_UWAT_BASE_MODIFIER ON ATT_ORG_UWAT_BASE (MODIFIER);

-- ----------------------------------------------------------------------------
-- 7. ATT_ORG_UWAT_EXT - 参建单位扩展信息表
--    表编号：ATT_017
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_ORG_UWAT_EXT (
    GUID                   CHAR(32)         NOT NULL,
    SOC_CRE_CODE           VARCHAR(20),
    ORG_GUID               CHAR(32),
    WIUN_NAME              VARCHAR(100)     NOT NULL,
    ORG_CLIEN_TYPE         CHAR(1),
    ORG_TYPE               CHAR(1),
    ORG_LEVEL              CHAR(2),
    WIUN_CODE              VARCHAR(20),
    REG_NAME               VARCHAR(100),
    LEG_REP                VARCHAR(100),
    WIUN_WAO_LONG          NUMERIC(11,8),
    WIUN_WAO_LAT           NUMERIC(10,8),
    WIUN_PROP              CHAR(1),
    IF_WATE_ENTE           CHAR(1),
    ADMIN_WIUN_GUID        CHAR(32),
    PRO_ORG_GUID           CHAR(32),
    IS_LEG_ENTI            CHAR(1),
    IF_MINI_DIRE           CHAR(1),
    CONTR_QUAL_GRAD        CHAR(1),
    CONTR_CERT_NUM         VARCHAR(40),
    SUP_QUAL_GRAD          CHAR(1),
    STAN_GRAD              CHAR(1),
    STAN_CERT_CODE         VARCHAR(40),
    SAFE_PROD_LICE_CODE    VARCHAR(40),
    FUND_SOUR              CHAR(1),
    EST_DATE               DATE,
    REGI_CAPI              NUMERIC(12),
    ADDR                   VARCHAR(100),
    REG_LOC                VARCHAR(100),
    MAJ_ACCI               VARCHAR(100),
    LEG_PERS               VARCHAR(40),
    LEG_PERS_TEL           VARCHAR(40),
    CONTA_PERS             VARCHAR(40),
    CONTA_PERS_MOBI        VARCHAR(40),
    CONTA_PERS_ADDR        VARCHAR(100),
    SAFETY_LEG_PERS        VARCHAR(40),
    SAFETY_LEG_PERS_DUTY   VARCHAR(40),
    SAFETY_LEG_PERS_TEL    VARCHAR(22),
    SAFETY_LEG_PERS_MOPH   VARCHAR(11),
    SAFE_PROD_CONTA        VARCHAR(40),
    SAFE_PROD_CONTA_TEL    VARCHAR(22),
    SAFE_PROD_CONTA_MPOH   VARCHAR(11),
    ACC_EME_CONTA          VARCHAR(40),
    ACC_EME_CONTA_MPOH     VARCHAR(11),
    WORK_POP_NUM           NUMERIC(11),
    PROJ_GROUP_TYPE        CHAR(1),
    PROJ_GROUP_NUM         NUMERIC(5),
    PROJ_CHAR_PERS         VARCHAR(20),
    PROJ_CHAR_PERS_TEL     VARCHAR(22),
    SAFE_MANA_ORG          VARCHAR(100),
    FULL_MANA_TOT          NUMERIC(14),
    SPE_OPER_PERS_TOT      NUMERIC(14),
    LEG_RESP_PERS_TEL      VARCHAR(22),
    LEG_RESP_PERS_FAX      VARCHAR(22),
    STAN_CONTA_PERS        VARCHAR(20),
    STAN_CONTA_TEL         VARCHAR(22),
    STAN_CONTA_FAX         VARCHAR(22),
    STAN_CONTA_MOPH        VARCHAR(11),
    STAN_CONTA_EMAIL       VARCHAR(100),
    NOTE                   VARCHAR(256),
    COLL_TIME              TIMESTAMP        NOT NULL,
    UPD_TIME               TIMESTAMP,
    REC_PERS               CHAR(32),
    CONSTRAINT PK_ATT_ORG_UWAT_EXT PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_ORG_UWAT_EXT IS '参建单位扩展信息表';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.GUID                   IS '全球唯一标识码';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SOC_CRE_CODE           IS '社会信用代码';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.ORG_GUID               IS '机构GUID';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.WIUN_NAME              IS '单位名称';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.ORG_CLIEN_TYPE         IS '单位类型(客户端)';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.ORG_TYPE               IS '单位类型';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.ORG_LEVEL              IS '单位规格';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.WIUN_CODE              IS '单位编码';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.REG_NAME               IS '注册名称';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.LEG_REP                IS '法定代表人';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.WIUN_WAO_LONG          IS '单位经度(°)';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.WIUN_WAO_LAT           IS '单位纬度(°)';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.WIUN_PROP              IS '单位性质';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.IF_WATE_ENTE           IS '是否水利系统';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.ADMIN_WIUN_GUID        IS '水利机构GUID';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.PRO_ORG_GUID           IS '上级单位';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.IS_LEG_ENTI            IS '是否项目法人单位';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.IF_MINI_DIRE           IS '是否部直属单位';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.CONTR_QUAL_GRAD        IS '施工总承包资质';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.CONTR_CERT_NUM         IS '施工总承包资质证书编号';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SUP_QUAL_GRAD          IS '监理资质等级';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.STAN_GRAD              IS '标准化等级';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.STAN_CERT_CODE         IS '标准化证书编号';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFE_PROD_LICE_CODE    IS '安全生产许可证编号';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.FUND_SOUR              IS '经费来源';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.EST_DATE               IS '成立日期';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.REGI_CAPI              IS '注册资本(万元)';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.ADDR                   IS '单位地址';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.REG_LOC                IS '注册地址';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.MAJ_ACCI               IS '发生重大安全事故情况';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.LEG_PERS               IS '主要负责人';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.LEG_PERS_TEL           IS '主要负责人手机';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.CONTA_PERS             IS '联系人';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.CONTA_PERS_MOBI        IS '联系人电话';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.CONTA_PERS_ADDR        IS '联系人联系地址';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFETY_LEG_PERS        IS '分管安全负责人';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFETY_LEG_PERS_DUTY   IS '分管安全负责人职务';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFETY_LEG_PERS_TEL    IS '分管安全负责人电话';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFETY_LEG_PERS_MOPH   IS '分管安全负责人手机';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFE_PROD_CONTA        IS '安全生产联系人';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFE_PROD_CONTA_TEL    IS '安全生产联系人电话';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFE_PROD_CONTA_MPOH   IS '安全生产联系人手机';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.ACC_EME_CONTA          IS '事故应急联系人';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.ACC_EME_CONTA_MPOH     IS '事故应急联系人手机';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.WORK_POP_NUM           IS '从业人员人数(人)';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.PROJ_GROUP_TYPE        IS '项目部类型';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.PROJ_GROUP_NUM         IS '项目组人数(人)';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.PROJ_CHAR_PERS         IS '项目分管安全负责人';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.PROJ_CHAR_PERS_TEL     IS '项目分管安全负责人电话';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SAFE_MANA_ORG          IS '安全管理机构';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.FULL_MANA_TOT          IS '专职管理人员总数(人)';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.SPE_OPER_PERS_TOT      IS '特种作业人员总数(人)';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.LEG_RESP_PERS_TEL      IS '法定代表人电话';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.LEG_RESP_PERS_FAX      IS '法定代表人传真';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.STAN_CONTA_PERS        IS '标准化联系人';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.STAN_CONTA_TEL         IS '标准化联系人电话';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.STAN_CONTA_FAX         IS '标准化联系人传真';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.STAN_CONTA_MOPH        IS '标准化联系人手机';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.STAN_CONTA_EMAIL       IS '标准化联系人电子邮箱';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.NOTE                   IS '备注';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.COLL_TIME              IS '采集时间';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.UPD_TIME               IS '更新时间';
COMMENT ON COLUMN ATT_ORG_UWAT_EXT.REC_PERS               IS '记录人员GUID';

CREATE UNIQUE INDEX IDX_ATT_ORG_UWAT_EXT_GUID ON ATT_ORG_UWAT_EXT (GUID);
CREATE INDEX IDX_ATT_ORG_UWAT_EXT_ORG_GUID ON ATT_ORG_UWAT_EXT (ORG_GUID);
CREATE INDEX IDX_ATT_ORG_UWAT_EXT_ADMIN_WIUN_GUID ON ATT_ORG_UWAT_EXT (ADMIN_WIUN_GUID);

-- ----------------------------------------------------------------------------
-- 8. ATT_ORG_WSUB - 分包单位信息表
--    表编号：ATT_018
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_ORG_WSUB (
    GUID                CHAR(32)         NOT NULL,
    SOC_CRE_CODE        VARCHAR(20),
    ORG_GUID            CHAR(32),
    WIUN_WSUB_NAME      VARCHAR(100)     NOT NULL,
    CONTR_QUAL_GRAD     CHAR(1),
    LEG_PERS            VARCHAR(40),
    LEG_PERS_TEL        VARCHAR(22),
    SAFE_PERS           VARCHAR(20),
    SAFE_PROD_LICE_CODE VARCHAR(20),
    COLL_TIME           TIMESTAMP        NOT NULL,
    UPD_TIME            TIMESTAMP,
    REC_PERS            CHAR(32),
    CONSTRAINT PK_ATT_ORG_WSUB PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_ORG_WSUB IS '分包单位信息表';
COMMENT ON COLUMN ATT_ORG_WSUB.GUID                IS '全球唯一标识码';
COMMENT ON COLUMN ATT_ORG_WSUB.SOC_CRE_CODE        IS '社会信用代码';
COMMENT ON COLUMN ATT_ORG_WSUB.ORG_GUID            IS '机构GUID';
COMMENT ON COLUMN ATT_ORG_WSUB.WIUN_WSUB_NAME      IS '分包单位名称';
COMMENT ON COLUMN ATT_ORG_WSUB.CONTR_QUAL_GRAD     IS '施工总承包资质';
COMMENT ON COLUMN ATT_ORG_WSUB.LEG_PERS            IS '项目负责人';
COMMENT ON COLUMN ATT_ORG_WSUB.LEG_PERS_TEL        IS '项目负责人电话';
COMMENT ON COLUMN ATT_ORG_WSUB.SAFE_PERS           IS '安全员';
COMMENT ON COLUMN ATT_ORG_WSUB.SAFE_PROD_LICE_CODE IS '安全生产许可证编号';
COMMENT ON COLUMN ATT_ORG_WSUB.COLL_TIME           IS '采集时间';
COMMENT ON COLUMN ATT_ORG_WSUB.UPD_TIME            IS '更新时间';
COMMENT ON COLUMN ATT_ORG_WSUB.REC_PERS            IS '记录人员GUID';

CREATE UNIQUE INDEX IDX_ATT_ORG_WSUB_GUID ON ATT_ORG_WSUB (GUID);

-- ----------------------------------------------------------------------------
-- 9. ATT_IRR_CHAN - 灌区渠道
--    表编号：ATT_019
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_IRR_CHAN (
    GUID          CHAR(32)         NOT NULL,
    IRR_CODE      VARCHAR(20)      NOT NULL,
    CHAN_TYPE     CHAR(1),
    CHAN_NUM      NUMERIC(10),
    CHAN_LEN      NUMERIC(11,1),
    NOTE          VARCHAR(256),
    COLL_TIME     TIMESTAMP        NOT NULL,
    UPD_TIME      TIMESTAMP,
    REC_PERS      CHAR(32),
    CONSTRAINT PK_ATT_IRR_CHAN PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_IRR_CHAN IS '灌区渠道';
COMMENT ON COLUMN ATT_IRR_CHAN.GUID      IS '全球唯一标识码';
COMMENT ON COLUMN ATT_IRR_CHAN.IRR_CODE  IS '灌区代码';
COMMENT ON COLUMN ATT_IRR_CHAN.CHAN_TYPE IS '渠道类别(字典: 渠道类别)';
COMMENT ON COLUMN ATT_IRR_CHAN.CHAN_NUM  IS '渠道数量(条)';
COMMENT ON COLUMN ATT_IRR_CHAN.CHAN_LEN  IS '渠道长度(km)';
COMMENT ON COLUMN ATT_IRR_CHAN.NOTE      IS '备注';
COMMENT ON COLUMN ATT_IRR_CHAN.COLL_TIME IS '采集时间';
COMMENT ON COLUMN ATT_IRR_CHAN.UPD_TIME  IS '更新时间';
COMMENT ON COLUMN ATT_IRR_CHAN.REC_PERS  IS '记录人员GUID';

CREATE UNIQUE INDEX IDX_ATT_IRR_CHAN_GUID ON ATT_IRR_CHAN (GUID);
CREATE INDEX IDX_ATT_IRR_CHAN_IRR_CODE ON ATT_IRR_CHAN (IRR_CODE);

-- ----------------------------------------------------------------------------
-- 10. ATT_AD_BASE - 行政区划基础信息表
--     表编号：ATT_020
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
CREATE TABLE ATT_AD_BASE (
    GUID           CHAR(32)         NOT NULL,
    AD_CODE        VARCHAR(20)      NOT NULL,
    AD_NAME        VARCHAR(100)     NOT NULL,
    LOW_LEFT_LONG  NUMERIC(11,8),
    LOW_LEFT_LAT   NUMERIC(10,8),
    UP_RIGHT_LONG  NUMERIC(11,8),
    UP_RIGHT_LAT   NUMERIC(10,8),
    AD_GRAD        CHAR(1)          NOT NULL,
    UP_AD_GUID     CHAR(32),
    NOTE           VARCHAR(256),
    COLL_TIME      TIMESTAMP        NOT NULL,
    UPD_TIME       TIMESTAMP,
    REC_PERS       CHAR(32),
    CONSTRAINT PK_ATT_AD_BASE PRIMARY KEY (GUID)
);

COMMENT ON TABLE  ATT_AD_BASE IS '行政区划基础信息表';
COMMENT ON COLUMN ATT_AD_BASE.GUID          IS '全球唯一标识码';
COMMENT ON COLUMN ATT_AD_BASE.AD_CODE       IS '行政区划代码';
COMMENT ON COLUMN ATT_AD_BASE.AD_NAME       IS '行政区划名称';
COMMENT ON COLUMN ATT_AD_BASE.LOW_LEFT_LONG IS '左下角经度(°)';
COMMENT ON COLUMN ATT_AD_BASE.LOW_LEFT_LAT  IS '左下角纬度(°)';
COMMENT ON COLUMN ATT_AD_BASE.UP_RIGHT_LONG IS '右上角经度(°)';
COMMENT ON COLUMN ATT_AD_BASE.UP_RIGHT_LAT  IS '右上角纬度(°)';
COMMENT ON COLUMN ATT_AD_BASE.AD_GRAD       IS '行政区划级别(字典: 行政区划级别)';
COMMENT ON COLUMN ATT_AD_BASE.UP_AD_GUID    IS '上级行政区划GUID';
COMMENT ON COLUMN ATT_AD_BASE.NOTE          IS '备注';
COMMENT ON COLUMN ATT_AD_BASE.COLL_TIME     IS '采集时间';
COMMENT ON COLUMN ATT_AD_BASE.UPD_TIME      IS '更新时间';
COMMENT ON COLUMN ATT_AD_BASE.REC_PERS      IS '记录人员GUID';

CREATE UNIQUE INDEX IDX_ATT_AD_BASE_GUID ON ATT_AD_BASE (GUID);
CREATE INDEX IDX_ATT_AD_BASE_AD_CODE ON ATT_AD_BASE (AD_CODE);
CREATE INDEX IDX_ATT_AD_BASE_UP_AD_GUID ON ATT_AD_BASE (UP_AD_GUID);

-- ----------------------------------------------------------------------------
-- 11. REL_ENG_ORG - 工程与水利机构关系表
--     表编号：REL_001
-- ----------------------------------------------------------------------------
CREATE TABLE REL_ENG_ORG (
    GUID          CHAR(32)         NOT NULL,
    ENG_GUID      CHAR(32)         NOT NULL,
    ORG_GUID      CHAR(32)         NOT NULL,
    FROM_DATE     TIMESTAMP        NOT NULL,
    TO_DATE       TIMESTAMP,
    CONSTRAINT PK_REL_ENG_ORG PRIMARY KEY (GUID)
);

COMMENT ON TABLE  REL_ENG_ORG IS '工程与水利机构关系表';
COMMENT ON COLUMN REL_ENG_ORG.GUID      IS '全球唯一标识码';
COMMENT ON COLUMN REL_ENG_ORG.ENG_GUID  IS '工程GUID';
COMMENT ON COLUMN REL_ENG_ORG.ORG_GUID  IS '机构GUID';
COMMENT ON COLUMN REL_ENG_ORG.FROM_DATE IS '关系建立时间';
COMMENT ON COLUMN REL_ENG_ORG.TO_DATE   IS '关系终止时间';

CREATE UNIQUE INDEX IDX_REL_ENG_ORG_GUID ON REL_ENG_ORG (GUID);
CREATE INDEX IDX_REL_ENG_ORG_ENG_GUID ON REL_ENG_ORG (ENG_GUID);
CREATE INDEX IDX_REL_ENG_ORG_ORG_GUID ON REL_ENG_ORG (ORG_GUID);

-- ----------------------------------------------------------------------------
-- 12. REL_ENG_ORG_UWAT - 工程与非水利机构关系表
--     表编号：REL_002
-- ----------------------------------------------------------------------------
CREATE TABLE REL_ENG_ORG_UWAT (
    GUID           CHAR(32)         NOT NULL,
    ENG_GUID       CHAR(32)         NOT NULL,
    ORG_UWAT_GUID  CHAR(32)         NOT NULL,
    FROM_DATE      TIMESTAMP        NOT NULL,
    TO_DATE        TIMESTAMP,
    CONSTRAINT PK_REL_ENG_ORG_UWAT PRIMARY KEY (GUID)
);

COMMENT ON TABLE  REL_ENG_ORG_UWAT IS '工程与非水利机构关系表';
COMMENT ON COLUMN REL_ENG_ORG_UWAT.GUID          IS '全球唯一标识码';
COMMENT ON COLUMN REL_ENG_ORG_UWAT.ENG_GUID      IS '工程GUID';
COMMENT ON COLUMN REL_ENG_ORG_UWAT.ORG_UWAT_GUID IS '非水利机构GUID';
COMMENT ON COLUMN REL_ENG_ORG_UWAT.FROM_DATE     IS '关系建立时间';
COMMENT ON COLUMN REL_ENG_ORG_UWAT.TO_DATE       IS '关系终止时间';

CREATE UNIQUE INDEX IDX_REL_ENG_ORG_UWAT_GUID ON REL_ENG_ORG_UWAT (GUID);
CREATE INDEX IDX_REL_ENG_ORG_UWAT_ENG_GUID ON REL_ENG_ORG_UWAT (ENG_GUID);
CREATE INDEX IDX_REL_ENG_ORG_UWAT_ORG_UWAT_GUID ON REL_ENG_ORG_UWAT (ORG_UWAT_GUID);

-- ----------------------------------------------------------------------------
-- 13. REL_PROJ_ORG - 项目与机构关系表
--     表编号：REL_003
-- ----------------------------------------------------------------------------
CREATE TABLE REL_PROJ_ORG (
    GUID          CHAR(32)         NOT NULL,
    PROJ_GUID     CHAR(32)         NOT NULL,
    ORG_GUID      CHAR(32)         NOT NULL,
    FROM_DATE     TIMESTAMP        NOT NULL,
    TO_DATE       TIMESTAMP,
    CONSTRAINT PK_REL_PROJ_ORG PRIMARY KEY (GUID)
);

COMMENT ON TABLE  REL_PROJ_ORG IS '项目与机构关系表';
COMMENT ON COLUMN REL_PROJ_ORG.GUID      IS '全球唯一标识码';
COMMENT ON COLUMN REL_PROJ_ORG.PROJ_GUID IS '项目GUID';
COMMENT ON COLUMN REL_PROJ_ORG.ORG_GUID  IS '机构GUID';
COMMENT ON COLUMN REL_PROJ_ORG.FROM_DATE IS '关系建立时间';
COMMENT ON COLUMN REL_PROJ_ORG.TO_DATE   IS '关系终止时间';

CREATE UNIQUE INDEX IDX_REL_PROJ_ORG_GUID ON REL_PROJ_ORG (GUID);
CREATE INDEX IDX_REL_PROJ_ORG_PROJ_GUID ON REL_PROJ_ORG (PROJ_GUID);
CREATE INDEX IDX_REL_PROJ_ORG_ORG_GUID ON REL_PROJ_ORG (ORG_GUID);

-- ----------------------------------------------------------------------------
-- 14. REL_TEND_ORG - 标段与非水利机构关系表
--     表编号：REL_004
-- ----------------------------------------------------------------------------
CREATE TABLE REL_TEND_ORG (
    GUID           CHAR(32)         NOT NULL,
    TEND_GUID      CHAR(32)         NOT NULL,
    ORG_UWAT_GUID  CHAR(32)         NOT NULL,
    FROM_DATE      TIMESTAMP        NOT NULL,
    TO_DATE        TIMESTAMP,
    CONSTRAINT PK_REL_TEND_ORG PRIMARY KEY (GUID)
);

COMMENT ON TABLE  REL_TEND_ORG IS '标段与非水利机构关系表';
COMMENT ON COLUMN REL_TEND_ORG.GUID          IS '全球唯一标识码';
COMMENT ON COLUMN REL_TEND_ORG.TEND_GUID     IS '标段GUID';
COMMENT ON COLUMN REL_TEND_ORG.ORG_UWAT_GUID IS '非水利机构GUID';
COMMENT ON COLUMN REL_TEND_ORG.FROM_DATE     IS '关系建立时间';
COMMENT ON COLUMN REL_TEND_ORG.TO_DATE       IS '关系终止时间';

CREATE UNIQUE INDEX IDX_REL_TEND_ORG_GUID ON REL_TEND_ORG (GUID);
CREATE INDEX IDX_REL_TEND_ORG_TEND_GUID ON REL_TEND_ORG (TEND_GUID);
CREATE INDEX IDX_REL_TEND_ORG_ORG_UWAT_GUID ON REL_TEND_ORG (ORG_UWAT_GUID);

-- ----------------------------------------------------------------------------
-- 15. REL_USER_TEND - 用户与标段关系表
--     表编号：REL_005
-- ----------------------------------------------------------------------------
CREATE TABLE REL_USER_TEND (
    GUID          CHAR(32)         NOT NULL,
    USER_GUID     CHAR(32)         NOT NULL,
    TEND_GUID     CHAR(32)         NOT NULL,
    FROM_DATE     TIMESTAMP        NOT NULL,
    TO_DATE       TIMESTAMP,
    CONSTRAINT PK_REL_USER_TEND PRIMARY KEY (GUID)
);

COMMENT ON TABLE  REL_USER_TEND IS '用户与标段关系表';
COMMENT ON COLUMN REL_USER_TEND.GUID      IS '全球唯一标识码';
COMMENT ON COLUMN REL_USER_TEND.USER_GUID IS '用户GUID';
COMMENT ON COLUMN REL_USER_TEND.TEND_GUID IS '标段GUID';
COMMENT ON COLUMN REL_USER_TEND.FROM_DATE IS '关系建立时间';
COMMENT ON COLUMN REL_USER_TEND.TO_DATE   IS '关系终止时间';

CREATE UNIQUE INDEX IDX_REL_USER_TEND_GUID ON REL_USER_TEND (GUID);
CREATE INDEX IDX_REL_USER_TEND_USER_GUID ON REL_USER_TEND (USER_GUID);
CREATE INDEX IDX_REL_USER_TEND_TEND_GUID ON REL_USER_TEND (TEND_GUID);


-- ============================================================================
-- 二、字典表（附初始数据）
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 字典_项目类型
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_PROJ_TYPE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_PROJ_TYPE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_PROJ_TYPE IS '项目类型字典';
COMMENT ON COLUMN DICT_PROJ_TYPE.CODE IS '代码';
COMMENT ON COLUMN DICT_PROJ_TYPE.NAME IS '名称';

INSERT INTO DICT_PROJ_TYPE (CODE, NAME) VALUES ('1', '基建项目');
INSERT INTO DICT_PROJ_TYPE (CODE, NAME) VALUES ('2', '预算项目');
INSERT INTO DICT_PROJ_TYPE (CODE, NAME) VALUES ('3', '国科项目');
INSERT INTO DICT_PROJ_TYPE (CODE, NAME) VALUES ('4', '许可项目');
INSERT INTO DICT_PROJ_TYPE (CODE, NAME) VALUES ('5', '生产项目');
INSERT INTO DICT_PROJ_TYPE (CODE, NAME) VALUES ('9', '其他');

-- ----------------------------------------------------------------------------
-- 字典_人员类型
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_PERSON_TYPE (
    CODE  CHAR(2)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_PERSON_TYPE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_PERSON_TYPE IS '人员类型字典';
COMMENT ON COLUMN DICT_PERSON_TYPE.CODE IS '代码';
COMMENT ON COLUMN DICT_PERSON_TYPE.NAME IS '名称';

INSERT INTO DICT_PERSON_TYPE (CODE, NAME) VALUES ('01', '行政');
INSERT INTO DICT_PERSON_TYPE (CODE, NAME) VALUES ('02', '事业');
INSERT INTO DICT_PERSON_TYPE (CODE, NAME) VALUES ('03', '参公');
INSERT INTO DICT_PERSON_TYPE (CODE, NAME) VALUES ('04', '外聘');
INSERT INTO DICT_PERSON_TYPE (CODE, NAME) VALUES ('05', '其他');

-- ----------------------------------------------------------------------------
-- 字典_性别
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_SEX (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_SEX PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_SEX IS '性别字典';
COMMENT ON COLUMN DICT_SEX.CODE IS '代码';
COMMENT ON COLUMN DICT_SEX.NAME IS '名称';

INSERT INTO DICT_SEX (CODE, NAME) VALUES ('0', '未知的性别');
INSERT INTO DICT_SEX (CODE, NAME) VALUES ('1', '男');
INSERT INTO DICT_SEX (CODE, NAME) VALUES ('2', '女');
INSERT INTO DICT_SEX (CODE, NAME) VALUES ('9', '未说明');

-- ----------------------------------------------------------------------------
-- 字典_民族
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_NATION (
    CODE  CHAR(2)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_NATION PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_NATION IS '民族字典';

INSERT INTO DICT_NATION (CODE, NAME) VALUES ('01', '汉族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('02', '蒙古族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('03', '回族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('04', '藏族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('05', '维吾尔族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('06', '苗族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('07', '彝族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('08', '壮族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('09', '布依族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('10', '朝鲜族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('11', '满族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('12', '侗族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('13', '瑶族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('14', '白族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('15', '土家族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('16', '哈尼族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('17', '哈萨克族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('18', '傣族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('19', '黎族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('20', '傈僳族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('21', '佤族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('22', '畲族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('23', '高山族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('24', '拉祜族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('25', '水族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('26', '东乡族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('27', '纳西族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('28', '景颇族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('29', '柯尔克孜族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('30', '土族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('31', '达斡尔族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('32', '仫佬族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('33', '羌族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('34', '布朗族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('35', '撒拉族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('36', '毛南族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('37', '仡佬族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('38', '锡伯族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('39', '阿昌族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('40', '普米族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('41', '塔吉克族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('42', '怒族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('43', '乌孜别克族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('44', '俄罗斯族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('45', '鄂温克族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('46', '德昂族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('47', '保安族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('48', '裕固族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('49', '京族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('50', '塔塔尔族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('51', '独龙族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('52', '鄂伦春族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('53', '赫哲族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('54', '门巴族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('55', '珞巴族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('56', '基诺族');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('97', '其他');
INSERT INTO DICT_NATION (CODE, NAME) VALUES ('98', '外国血统中国籍人士');

-- ----------------------------------------------------------------------------
-- 字典_职务级别
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_DUTY_LEVEL (
    CODE  VARCHAR(3)       NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_DUTY_LEVEL PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_DUTY_LEVEL IS '职务级别字典';

INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('1', '公务员职务级别');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('101', '国家级正职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('102', '国家级副职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('111', '省部级正职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('112', '省部级副职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('121', '厅局级正职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('122', '厅局级副职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('131', '县处级正职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('132', '县处级副职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('141', '乡科级正职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('142', '乡科级副职');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('150', '科员级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('160', '办事员级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('199', '未定职公务员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('2', '职员级别');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('211', '一级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('212', '二级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('221', '三级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('222', '四级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('231', '五级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('232', '六级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('241', '七级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('242', '八级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('250', '九级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('260', '十级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('299', '未定级职员');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('4', '专业技术职务级别');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('410', '高级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('411', '正高级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('412', '副高级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('420', '中级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('430', '初级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('434', '助理级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('435', '员级');
INSERT INTO DICT_DUTY_LEVEL (CODE, NAME) VALUES ('499', '未定职级专业技术人员');

-- ----------------------------------------------------------------------------
-- 字典_机构级别
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_ORG_LEVEL (
    CODE  CHAR(2)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_ORG_LEVEL PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_ORG_LEVEL IS '机构级别字典';

INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('01', '国家级正职');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('02', '国家级副职');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('03', '正部(省)级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('04', '副部(省)级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('05', '司、局、地、厅级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('06', '副司、副局、副地、副厅级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('07', '县、正处级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('08', '副县、副处级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('09', '科级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('10', '副科级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('11', '股级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('12', '副股级');
INSERT INTO DICT_ORG_LEVEL (CODE, NAME) VALUES ('90', '其他级(含无级)');

-- ----------------------------------------------------------------------------
-- 字典_机构类型
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_ORG_TYPE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_ORG_TYPE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_ORG_TYPE IS '机构类型字典';

INSERT INTO DICT_ORG_TYPE (CODE, NAME) VALUES ('1', '单位');
INSERT INTO DICT_ORG_TYPE (CODE, NAME) VALUES ('2', '部门');
INSERT INTO DICT_ORG_TYPE (CODE, NAME) VALUES ('9', '其他');

-- ----------------------------------------------------------------------------
-- 字典_区域类型
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_AREA_TYPE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_AREA_TYPE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_AREA_TYPE IS '区域类型字典';

INSERT INTO DICT_AREA_TYPE (CODE, NAME) VALUES ('1', '行政区划');
INSERT INTO DICT_AREA_TYPE (CODE, NAME) VALUES ('2', '水资源分区');
INSERT INTO DICT_AREA_TYPE (CODE, NAME) VALUES ('3', '流域分区');
INSERT INTO DICT_AREA_TYPE (CODE, NAME) VALUES ('4', '流域片区');
INSERT INTO DICT_AREA_TYPE (CODE, NAME) VALUES ('5', '单位');
INSERT INTO DICT_AREA_TYPE (CODE, NAME) VALUES ('9', '其他');

-- ----------------------------------------------------------------------------
-- 字典_状态
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_STATUS (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_STATUS PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_STATUS IS '状态字典';

INSERT INTO DICT_STATUS (CODE, NAME) VALUES ('1', '启用');
INSERT INTO DICT_STATUS (CODE, NAME) VALUES ('2', '停用');
INSERT INTO DICT_STATUS (CODE, NAME) VALUES ('9', '注销');

-- ----------------------------------------------------------------------------
-- 字典_单位类型(客户端)
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_ORG_CLIENT_TYPE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(100)     NOT NULL,
    CONSTRAINT PK_DICT_ORG_CLIENT_TYPE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_ORG_CLIENT_TYPE IS '单位类型字典(客户端)';

INSERT INTO DICT_ORG_CLIENT_TYPE (CODE, NAME) VALUES ('1', '大中型已建工程运行管理单位');
INSERT INTO DICT_ORG_CLIENT_TYPE (CODE, NAME) VALUES ('2', '大中型在建工程项目法人');
INSERT INTO DICT_ORG_CLIENT_TYPE (CODE, NAME) VALUES ('3', '小型工程管理单位和技术服务单位');

-- ----------------------------------------------------------------------------
-- 字典_单位类型(业务)
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_ORG_BIZ_TYPE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_ORG_BIZ_TYPE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_ORG_BIZ_TYPE IS '单位类型字典(业务)';

INSERT INTO DICT_ORG_BIZ_TYPE (CODE, NAME) VALUES ('1', '工程管理单位');
INSERT INTO DICT_ORG_BIZ_TYPE (CODE, NAME) VALUES ('2', '项目法人');
INSERT INTO DICT_ORG_BIZ_TYPE (CODE, NAME) VALUES ('3', '施工单位');
INSERT INTO DICT_ORG_BIZ_TYPE (CODE, NAME) VALUES ('4', '其他');

-- ----------------------------------------------------------------------------
-- 字典_单位性质
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_ORG_PROP (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_ORG_PROP PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_ORG_PROP IS '单位性质字典';

INSERT INTO DICT_ORG_PROP (CODE, NAME) VALUES ('1', '国有企业');
INSERT INTO DICT_ORG_PROP (CODE, NAME) VALUES ('2', '国有控股企业');
INSERT INTO DICT_ORG_PROP (CODE, NAME) VALUES ('3', '外资企业');
INSERT INTO DICT_ORG_PROP (CODE, NAME) VALUES ('4', '合资企业');
INSERT INTO DICT_ORG_PROP (CODE, NAME) VALUES ('5', '私营企业');
INSERT INTO DICT_ORG_PROP (CODE, NAME) VALUES ('6', '事业单位');
INSERT INTO DICT_ORG_PROP (CODE, NAME) VALUES ('7', '国家行政机关');
INSERT INTO DICT_ORG_PROP (CODE, NAME) VALUES ('8', '政府');

-- ----------------------------------------------------------------------------
-- 字典_是否水利系统
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_IF_WATER_ENTE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_IF_WATER_ENTE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_IF_WATER_ENTE IS '是否水利系统字典';

INSERT INTO DICT_IF_WATER_ENTE (CODE, NAME) VALUES ('1', '是');
INSERT INTO DICT_IF_WATER_ENTE (CODE, NAME) VALUES ('2', '否');
INSERT INTO DICT_IF_WATER_ENTE (CODE, NAME) VALUES ('0', '未知');

-- ----------------------------------------------------------------------------
-- 字典_是否项目法人单位
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_IS_LEG_ENTI (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_IS_LEG_ENTI PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_IS_LEG_ENTI IS '是否项目法人单位字典';

INSERT INTO DICT_IS_LEG_ENTI (CODE, NAME) VALUES ('1', '是');
INSERT INTO DICT_IS_LEG_ENTI (CODE, NAME) VALUES ('2', '否');
INSERT INTO DICT_IS_LEG_ENTI (CODE, NAME) VALUES ('0', '未知');

-- ----------------------------------------------------------------------------
-- 字典_是否部直属单位
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_IF_MINI_DIRE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_IF_MINI_DIRE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_IF_MINI_DIRE IS '是否部直属单位字典';

INSERT INTO DICT_IF_MINI_DIRE (CODE, NAME) VALUES ('1', '是');
INSERT INTO DICT_IF_MINI_DIRE (CODE, NAME) VALUES ('2', '否');
INSERT INTO DICT_IF_MINI_DIRE (CODE, NAME) VALUES ('0', '未知');

-- ----------------------------------------------------------------------------
-- 字典_施工总承包资质
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_CONTR_QUAL_GRAD (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_CONTR_QUAL_GRAD PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_CONTR_QUAL_GRAD IS '施工总承包资质字典';

INSERT INTO DICT_CONTR_QUAL_GRAD (CODE, NAME) VALUES ('1', '一级');
INSERT INTO DICT_CONTR_QUAL_GRAD (CODE, NAME) VALUES ('2', '二级');
INSERT INTO DICT_CONTR_QUAL_GRAD (CODE, NAME) VALUES ('3', '三级');

-- ----------------------------------------------------------------------------
-- 字典_监理资质等级
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_SUP_QUAL_GRAD (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_SUP_QUAL_GRAD PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_SUP_QUAL_GRAD IS '监理资质等级字典';

INSERT INTO DICT_SUP_QUAL_GRAD (CODE, NAME) VALUES ('1', '甲级');
INSERT INTO DICT_SUP_QUAL_GRAD (CODE, NAME) VALUES ('2', '乙级');
INSERT INTO DICT_SUP_QUAL_GRAD (CODE, NAME) VALUES ('3', '丙级');

-- ----------------------------------------------------------------------------
-- 字典_标准化等级
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_STAN_GRAD (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_STAN_GRAD PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_STAN_GRAD IS '标准化等级字典';

INSERT INTO DICT_STAN_GRAD (CODE, NAME) VALUES ('1', '一级');
INSERT INTO DICT_STAN_GRAD (CODE, NAME) VALUES ('2', '二级');
INSERT INTO DICT_STAN_GRAD (CODE, NAME) VALUES ('3', '三级');
INSERT INTO DICT_STAN_GRAD (CODE, NAME) VALUES ('4', '无');

-- ----------------------------------------------------------------------------
-- 字典_经费来源
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_FUND_SOUR (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_FUND_SOUR PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_FUND_SOUR IS '经费来源字典';

INSERT INTO DICT_FUND_SOUR (CODE, NAME) VALUES ('1', '财政全额');
INSERT INTO DICT_FUND_SOUR (CODE, NAME) VALUES ('2', '财政差额');
INSERT INTO DICT_FUND_SOUR (CODE, NAME) VALUES ('3', '自筹自支');
INSERT INTO DICT_FUND_SOUR (CODE, NAME) VALUES ('4', '其他');

-- ----------------------------------------------------------------------------
-- 字典_单位类型(工程建设)
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_ORG_ENG_TYPE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_ORG_ENG_TYPE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_ORG_ENG_TYPE IS '单位类型字典(工程建设)';

INSERT INTO DICT_ORG_ENG_TYPE (CODE, NAME) VALUES ('1', '施工单位');
INSERT INTO DICT_ORG_ENG_TYPE (CODE, NAME) VALUES ('2', '监理单位');

-- ----------------------------------------------------------------------------
-- 字典_渠道类别
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_CHAN_TYPE (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_CHAN_TYPE PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_CHAN_TYPE IS '渠道类别字典';

INSERT INTO DICT_CHAN_TYPE (CODE, NAME) VALUES ('1', '灌溉');
INSERT INTO DICT_CHAN_TYPE (CODE, NAME) VALUES ('2', '灌排结合');

-- ----------------------------------------------------------------------------
-- 字典_行政区划级别
-- ----------------------------------------------------------------------------
CREATE TABLE DICT_AD_GRAD (
    CODE  CHAR(1)          NOT NULL,
    NAME  VARCHAR(50)      NOT NULL,
    CONSTRAINT PK_DICT_AD_GRAD PRIMARY KEY (CODE)
);
COMMENT ON TABLE  DICT_AD_GRAD IS '行政区划级别字典';

INSERT INTO DICT_AD_GRAD (CODE, NAME) VALUES ('1', '国家级');
INSERT INTO DICT_AD_GRAD (CODE, NAME) VALUES ('2', '省级');
INSERT INTO DICT_AD_GRAD (CODE, NAME) VALUES ('3', '地市级');
INSERT INTO DICT_AD_GRAD (CODE, NAME) VALUES ('4', '县级');
INSERT INTO DICT_AD_GRAD (CODE, NAME) VALUES ('5', '乡级');
INSERT INTO DICT_AD_GRAD (CODE, NAME) VALUES ('6', '村');
INSERT INTO DICT_AD_GRAD (CODE, NAME) VALUES ('7', '村组');
