193 lines
10 KiB
SQL
193 lines
10 KiB
SQL
## cus_list表增加字段数据来源（data_source），审批状态（app_stat），流程实例编号（inst_no）
|
||
ALTER TABLE REC_LOAN_LAST.cus_list ADD data_source VARCHAR(12) DEFAULT '';
|
||
ALTER TABLE REC_LOAN_LAST.cus_list ADD app_stat VARCHAR(12) DEFAULT '';
|
||
ALTER TABLE REC_LOAN_LAST.cus_list ADD inst_no VARCHAR(64) DEFAULT '';
|
||
ALTER TABLE REC_LOAN_LAST.cus_list ADD change_app_stat VARCHAR(12) DEFAULT '';
|
||
ALTER TABLE REC_LOAN_LAST.cus_list ADD change_inst_no VARCHAR(64) DEFAULT '';
|
||
ALTER TABLE REC_LOAN_LAST.cus_list ADD change_batch_no VARCHAR(64) DEFAULT '';
|
||
|
||
alter table REC_LOAN_LAST.cus_list modify reason varchar(300);
|
||
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."cus_list"."data_source" IS '数据来源';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."cus_list"."inst_no" IS '流程实例编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."cus_list"."change_app_stat" IS '黑名单状态变更审批状态';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."cus_list"."app_stat" IS '审批状态';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."cus_list"."change_inst_no" IS '黑名单状态变更流程实例编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."cus_list"."change_batch_no" IS '黑名单状态变更流程实例编号';
|
||
|
||
CREATE TABLE "REC_LOAN_LAST"."crdt_approve"
|
||
(
|
||
"id" VARCHAR(32) NOT NULL,
|
||
"batch_no" VARCHAR(32) DEFAULT '' NOT NULL,
|
||
"inst_no" VARCHAR(32) DEFAULT '' NOT NULL,
|
||
"app_stat" VARCHAR(10) DEFAULT '' NOT NULL,
|
||
"data" text DEFAULT '' NOT NULL,
|
||
"export_url" VARCHAR(256) DEFAULT '' NOT NULL,
|
||
NOT CLUSTER PRIMARY KEY("id")) STORAGE(ON "REC_LOAN_LAST", CLUSTERBTR) ;
|
||
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."crdt_approve"."batch_no" IS '批次号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."crdt_approve"."inst_no" IS '流程实例编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."crdt_approve"."app_stat" IS '审批状态';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."crdt_approve"."data" IS '待导出数据';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."crdt_approve"."export_url" IS '导出地址';
|
||
|
||
|
||
|
||
|
||
|
||
ALTER TABLE REC_LOAN_LAST.chan_basic_detail ADD NOTES VARCHAR(500) DEFAULT '';
|
||
ALTER TABLE REC_LOAN_LAST.chan_basic_detail ADD EFFECT_TIME DATE;
|
||
ALTER TABLE REC_LOAN_LAST.chan_basic_detail ADD TEMP_STAT INTEGER;
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."chan_basic_detail"."NOTES" IS '备注';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."chan_basic_detail"."EFFECT_TIME" IS '有效时间';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."chan_basic_detail"."TEMP_STAT" IS '是否存在临时版本，1存在';
|
||
|
||
|
||
-- Drop table
|
||
-- DROP TABLE REC_LOAN_LAST."warning_rule";
|
||
CREATE TABLE "REC_LOAN_LAST"."warning_rule"
|
||
(
|
||
"id" VARCHAR(19) NOT NULL,
|
||
"target_id" VARCHAR(19) DEFAULT '' NOT NULL,
|
||
"rule_name" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"rule_desc" VARCHAR(500) DEFAULT '' NOT NULL,
|
||
"warn_status" TINYINT DEFAULT '1' NOT NULL,
|
||
"product" VARCHAR(200) DEFAULT '' NOT NULL,
|
||
"if_merge" TINYINT DEFAULT '' NOT NULL,
|
||
"monit_frequency" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"notify_method" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"theme" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"user_group" TEXT DEFAULT '' NOT NULL,
|
||
"effective_date" DATETIME(6) DEFAULT NULL,
|
||
"update_effective_date" DATETIME(6) DEFAULT NULL,
|
||
"expiration_date" DATETIME(6) DEFAULT NULL,
|
||
"create_user" VARCHAR(8) DEFAULT '' NOT NULL,
|
||
"create_org" VARCHAR(8) DEFAULT '' NOT NULL,
|
||
"create_time" DATETIME(6) DEFAULT NULL,
|
||
"update_user" VARCHAR(8) DEFAULT '' NOT NULL,
|
||
"update_org" VARCHAR(8) DEFAULT '' NOT NULL,
|
||
"update_time" DATETIME(6) DEFAULT NULL,
|
||
"bank_code" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"bank_name" VARCHAR(30) DEFAULT '' NOT NULL,
|
||
"create_user_name" VARCHAR(30) DEFAULT '' NOT NULL,
|
||
"create_org_name" VARCHAR(30) DEFAULT '' NOT NULL,
|
||
"update_user_name" VARCHAR(30) DEFAULT '' NOT NULL,
|
||
"update_org_name" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"create_date" DATE DEFAULT NULL,
|
||
"update_date" DATE DEFAULT NULL,
|
||
"cur_vers" TINYINT DEFAULT '1' NOT NULL,
|
||
"delete_flag" INTEGER DEFAULT 0 NOT NULL,
|
||
NOT CLUSTER PRIMARY KEY("id")) STORAGE(ON "REC_LOAN_LAST", CLUSTERBTR) ;
|
||
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."bank_code" IS '银行编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."bank_name" IS '银行名称';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."create_date" IS '创建日期';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."create_org" IS '登记机构编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."create_org_name" IS '登记机构名称';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."create_time" IS '登记时间戳';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."create_user" IS '登记银行员工编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."create_user_name" IS '创建人姓名';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."effective_date" IS '生效日期';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."expiration_date" IS '失效日期';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."if_merge" IS '是否合并，0-否，1-是';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."monit_frequency" IS '监控频率编码';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."notify_method" IS '通知方式编码';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."product" IS '产品编码';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."rule_desc" IS '规则描述';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."rule_name" IS '规则名称';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."target_id" IS '指标id';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."theme" IS '主题';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."update_date" IS '更新日期';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."update_effective_date" IS '更新后的生效日期';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."update_org" IS '更新机构编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."update_org_name" IS '更新机构名称';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."update_time" IS '更新时间戳';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."update_user" IS '更新银行员工编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."update_user_name" IS '更新员工姓名';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."user_group" IS '预警用户组编码';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."warn_status" IS '监控状态';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule"."delete_flag" IS '删除标识1';
|
||
-- Drop table
|
||
-- DROP TABLE REC_LOAN_LAST."warning_rule_detail";
|
||
CREATE TABLE "REC_LOAN_LAST"."warning_rule_detail"
|
||
(
|
||
"id" VARCHAR(19) NOT NULL,
|
||
"rule_id" VARCHAR(19) DEFAULT '' NOT NULL,
|
||
"warning_level" VARCHAR(10) DEFAULT '' NOT NULL,
|
||
"monitor_time" INT DEFAULT '' NOT NULL,
|
||
"rule_type" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"threshold" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"create_user" VARCHAR(8) DEFAULT '' NOT NULL,
|
||
"create_org" VARCHAR(8) DEFAULT '' NOT NULL,
|
||
"create_time" DATETIME(6) DEFAULT NULL,
|
||
"update_user" VARCHAR(8) DEFAULT '' NOT NULL,
|
||
"update_org" VARCHAR(8) DEFAULT '' NOT NULL,
|
||
"update_time" DATETIME(6) DEFAULT NULL,
|
||
"bank_code" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"bank_name" VARCHAR(30) DEFAULT '' NOT NULL,
|
||
"create_user_name" VARCHAR(30) DEFAULT '' NOT NULL,
|
||
"create_org_name" VARCHAR(30) DEFAULT '' NOT NULL,
|
||
"update_user_name" VARCHAR(30) DEFAULT '' NOT NULL,
|
||
"update_org_name" VARCHAR(50) DEFAULT '' NOT NULL,
|
||
"create_date" DATE DEFAULT NULL,
|
||
"update_date" DATE DEFAULT NULL,
|
||
"cur_vers" TINYINT DEFAULT '1' NOT NULL,
|
||
NOT CLUSTER PRIMARY KEY("id")) STORAGE(ON "REC_LOAN_LAST", CLUSTERBTR) ;
|
||
|
||
-- act_business_info表添加 business_version_字段
|
||
alter table "REC_LOAN_LAST"."act_business_info" add business_version_ INT;
|
||
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."act_business_info"."business_version_" IS '业务数据版本号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."bank_code" IS '银行编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."bank_name" IS '银行名称';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."create_date" IS '创建日期';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."create_org" IS '登记机构编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."create_org_name" IS '登记机构名称';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."create_time" IS '登记时间戳';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."create_user" IS '登记银行员工编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."create_user_name" IS '创建人姓名';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."monitor_time" IS '监控次数';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."rule_id" IS '预警规则id';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."rule_type" IS '规则类型编码';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."threshold" IS '阈值';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."update_date" IS '更新日期';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."update_org" IS '更新机构编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."update_org_name" IS '更新机构名称';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."update_time" IS '更新时间戳';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."update_user" IS '更新银行员工编号';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."update_user_name" IS '更新员工姓名';
|
||
COMMENT ON COLUMN "REC_LOAN_LAST"."warning_rule_detail"."warning_level" IS '预警等级编码';
|
||
|
||
CREATE TABLE REC_LOAN_LAST."biz_info_backup_his" (
|
||
"id" DECIMAL NOT NULL,
|
||
"biz_key" VARCHAR(32) NOT NULL,
|
||
"biz_id" VARCHAR(100) NOT NULL,
|
||
"backup_json" CLOB,
|
||
"backup_type" VARCHAR(128),
|
||
"tab_name" VARCHAR(512),
|
||
"app_sts" VARCHAR(128),
|
||
"bank_code" VARCHAR(128),
|
||
"create_user" VARCHAR(128),
|
||
"create_org" VARCHAR(64),
|
||
"create_time" TIMESTAMP,
|
||
"create_date" DATE,
|
||
"update_user" VARCHAR(128),
|
||
"update_org" VARCHAR(64),
|
||
"update_time" TIMESTAMP,
|
||
"update_date" DATE,
|
||
"cur_vers" INT DEFAULT 1 NOT NULL,
|
||
CONSTRAINT CONS134219454 PRIMARY KEY ("id")
|
||
);
|
||
|
||
alter table "REC_LOAN_LAST"."prd_base_info" add TWO_VERSION INT;
|
||
alter table "REC_LOAN_LAST"."prd_base_info" add TWO_STATE VARCHAR(100);
|
||
alter table "REC_LOAN_LAST"."prd_base_info" add NOTES VARCHAR(500);
|
||
alter table "REC_LOAN_LAST"."prd_base_info" add EFFECT_TIME DATE;
|
||
alter table "REC_LOAN_LAST"."prd_base_info" add P_CODE VARCHAR(100);
|
||
|
||
alter table "REC_LOAN_LAST"."chan_coop_info" add P_CODE VARCHAR(100);
|
||
alter table "REC_LOAN_LAST"."chan_coop_info" add TWO_STATE VARCHAR(100);
|
||
alter table "REC_LOAN_LAST"."chan_coop_info" add NOTES VARCHAR(500);
|
||
alter table "REC_LOAN_LAST"."chan_coop_info" add EFFECT_TIME DATE;
|
||
|
||
alter table REC_LOAN_LAST.chan_coop_info modify coop_proj_name varchar(999); |