61 lines
3.2 KiB
SQL
61 lines
3.2 KiB
SQL
-- DM8 部门外部组织编码映射初始化脚本
|
||
-- 包含表结构、字段注释及基础字典数据
|
||
|
||
-- 重复执行时请先备份数据
|
||
DROP TABLE IF EXISTS system_dept_external_code;
|
||
|
||
CREATE TABLE system_dept_external_code (
|
||
id BIGINT NOT NULL,
|
||
dept_id BIGINT NOT NULL,
|
||
system_code VARCHAR(64) NOT NULL,
|
||
external_dept_code VARCHAR(128) NOT NULL,
|
||
external_dept_name VARCHAR(255),
|
||
status TINYINT DEFAULT 0 NOT NULL,
|
||
remark VARCHAR(512),
|
||
tenant_id BIGINT DEFAULT 0,
|
||
creator VARCHAR(64),
|
||
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updater VARCHAR(64),
|
||
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted TINYINT DEFAULT 0 NOT NULL,
|
||
CONSTRAINT pk_system_dept_external_code PRIMARY KEY (id)
|
||
);
|
||
|
||
-- 唯一索引与辅助索引
|
||
CREATE UNIQUE INDEX uk_system_dept_external_code_ext
|
||
ON system_dept_external_code (tenant_id, system_code, external_dept_code);
|
||
CREATE UNIQUE INDEX uk_system_dept_external_code_dept
|
||
ON system_dept_external_code (tenant_id, system_code, dept_id);
|
||
CREATE INDEX idx_system_dept_external_code_dept
|
||
ON system_dept_external_code (tenant_id, dept_id);
|
||
|
||
COMMENT ON TABLE system_dept_external_code IS '部门外部组织编码映射';
|
||
COMMENT ON COLUMN system_dept_external_code.id IS '主键编号';
|
||
COMMENT ON COLUMN system_dept_external_code.dept_id IS '本系统部门编号';
|
||
COMMENT ON COLUMN system_dept_external_code.system_code IS '外部系统标识';
|
||
COMMENT ON COLUMN system_dept_external_code.external_dept_code IS '外部组织编码';
|
||
COMMENT ON COLUMN system_dept_external_code.external_dept_name IS '外部组织名称';
|
||
COMMENT ON COLUMN system_dept_external_code.status IS '状态（0开启 1关闭）';
|
||
COMMENT ON COLUMN system_dept_external_code.remark IS '备注';
|
||
COMMENT ON COLUMN system_dept_external_code.tenant_id IS '租户编号';
|
||
COMMENT ON COLUMN system_dept_external_code.creator IS '创建者';
|
||
COMMENT ON COLUMN system_dept_external_code.create_time IS '创建时间';
|
||
COMMENT ON COLUMN system_dept_external_code.updater IS '更新者';
|
||
COMMENT ON COLUMN system_dept_external_code.update_time IS '更新时间';
|
||
COMMENT ON COLUMN system_dept_external_code.deleted IS '删除标记';
|
||
|
||
-- 初始化外部系统标识字典
|
||
INSERT INTO system_dict_type (id, name, type, status, remark, creator, create_time, updater, update_time, deleted)
|
||
SELECT 20050, '外部系统标识', 'system_dept_external_system', 0, '部门外部组织编码中的外部系统标识', 'admin', SYSDATE, 'admin', SYSDATE, 0
|
||
FROM dual
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM system_dict_type WHERE type = 'system_dept_external_system'
|
||
);
|
||
|
||
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted)
|
||
SELECT 2005001, 1, 'ERP 系统', 'ERP', 'system_dept_external_system', 0, '', '', '企业资源计划系统', 'admin', SYSDATE, 'admin', SYSDATE, 0
|
||
FROM dual
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM system_dict_data WHERE dict_type = 'system_dept_external_system' AND value = 'ERP'
|
||
);
|