CREATE TABLE "REC_LOAN_LAST"."warning_target" ( "id" VARCHAR(19) NOT NULL, "target_no" VARCHAR(50) DEFAULT '' NOT NULL, "target_name" VARCHAR(50) DEFAULT '' NOT NULL, "target_code" VARCHAR(50) DEFAULT '' NOT NULL, "target_1st_type" VARCHAR(50) DEFAULT '' NOT NULL, "target_2nd_type" VARCHAR(50) DEFAULT '' NOT NULL, "target_desc" VARCHAR(500) DEFAULT '' NOT NULL, "target_unit" VARCHAR(50) DEFAULT '' NOT NULL, "target_status" TINYINT DEFAULT 1 NOT NULL, "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, NOT CLUSTER PRIMARY KEY("id")) STORAGE(ON "rec_loan_last", CLUSTERBTR) ; COMMENT ON TABLE "REC_LOAN_LAST"."warning_target" IS '预警指标表'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."bank_code" IS '银行编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."bank_name" IS '银行名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."create_date" IS '创建日期'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."create_org" IS '登记机构编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."create_org_name" IS '登记机构名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."create_time" IS '登记时间戳'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."create_user" IS '登记银行员工编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."create_user_name" IS '创建人姓名'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."effective_date" IS '生效日期'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."expiration_date" IS '失效日期'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."target_1st_type" IS '指标一级分类'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."target_2nd_type" IS '指标二级分类'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."target_code" IS '指标编码'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."target_desc" IS '指标描述'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."target_name" IS '指标名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."target_no" IS '指标编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."target_status" IS '指标状态，0-失效，1-生效'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."target_unit" IS '指标单位'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."update_date" IS '更新日期'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."update_org" IS '更新机构编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."update_org_name" IS '更新机构名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."update_time" IS '更新时间戳'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."update_user" IS '更新银行员工编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_target"."update_user_name" IS '更新员工姓名'; INSERT INTO "REC_LOAN_LAST"."warning_target" (ID, TARGET_NO, TARGET_NAME, TARGET_CODE, TARGET_1ST_TYPE, TARGET_2ND_TYPE, TARGET_DESC, TARGET_UNIT ) VALUES ('1', 'A000001', '招行余额', 'target03', '02', '10', '招行备付金账户余额，次数', '03'), ('2', 'A000002', '逾期风险', 'target01', '01', '30', '逾期率，百分比（两位小数，最大 100，最小 0）', '02' ), ('3', 'A000003', '审批通过率', 'target02', '01', '40', '授信/用信审批通过率，百分比（两位小数，最大 100 最小 0）', '02' ), ('4', 'A000004', '违约率', 'target04', '01', '30', '违约贷款余额/总贷款余额', '03' ), ('5', 'A000005', '不良率', 'target05', '01', '30', '不良率为后三类（次级、可疑、损失）之和/总贷款余额', '02' ); UPDATE "REC_LOAN_LAST"."warning_target" SET effective_date = '2023-05-29 12:00:00'; CREATE TABLE "REC_LOAN_LAST"."warning_record" ( "id" VARCHAR(19) NOT NULL, "rule_id" VARCHAR(19) DEFAULT '' NOT NULL, "rule_name" VARCHAR(50) DEFAULT '' NOT NULL, "target_name" VARCHAR(50) DEFAULT '' NOT NULL, "product" VARCHAR(200) DEFAULT '' NOT NULL, "warning_level" VARCHAR(10) DEFAULT '' NOT NULL, "notify_method" VARCHAR(50) DEFAULT '' NOT NULL, "user_group" TEXT DEFAULT '' NOT NULL, "warning_desc" VARCHAR(244) 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) ; COMMENT ON TABLE "REC_LOAN_LAST"."warning_record" IS '预警记录表'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."bank_code" IS '银行编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."bank_name" IS '银行名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."create_date" IS '创建日期'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."create_org" IS '登记机构编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."create_org_name" IS '登记机构名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."create_time" IS '登记时间戳'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."create_user" IS '登记银行员工编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."create_user_name" IS '创建人姓名'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."notify_method" IS '通知方式编码'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."product" IS '产品编码'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."rule_id" IS '违反的规则id'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."rule_name" IS '违反的规则名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."target_name" IS '指标名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."update_date" IS '更新日期'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."update_org" IS '更新机构编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."update_org_name" IS '更新机构名称'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."update_time" IS '更新时间戳'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."update_user" IS '更新银行员工编号'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."update_user_name" IS '更新员工姓名'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."user_group" IS '预警用户组编码'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."warning_desc" IS '预警内容'; COMMENT ON COLUMN "REC_LOAN_LAST"."warning_record"."warning_level" IS '预警等级编码'; INSERT INTO "REC_LOAN_LAST".sys_select (id,select_name,select_key,select_desc,create_user,create_org,create_time,create_date,update_user,update_org,update_time,update_date,cur_vers) VALUES ('20747468696347213824','预警规则是否合并','WARNING_IS_MERGE','','00000000','999999','2023-04-21 14:53:44','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746040543825362944','预警管理预警等级','WARNING_LEVEL','预警管理预警等级','00000000','999999','2023-04-17 16:18:46','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746023306615848960','预警管理监控频率','WARNING_MONI_FREQ','预警管理监控频率','00000000','999999','2023-04-17 15:10:16','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746042953343959040','预警管理通知方式','WARNING_NOTIFY_METHOD','预警管理通知方式','00000000','999999','2023-04-17 16:28:20','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747098528429375488','预警管理预警规则','WARNING_RULE','预警管理预警规则','00000000','999999','2023-04-20 14:22:49','2023-02-20','00000000','999999','2023-04-20 14:24:07','2023-04-20',1), ('20747490276360060928','监控状态','WARNING_STATUS','监控状态','00000000','999999','2023-04-21 16:19:29','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746053785268256768','预警管理指标','WARNING_TARGET','预警管理指标','00000000','999999','2023-04-17 17:11:23','2023-02-20','00000000','999999','2023-04-17 17:11:47','2023-04-17',1), ('20749209109521235968','预警指标单位','WARNING_TARGET_UNIT','预警指标单位','00000000','999999','2023-04-26 10:09:31','2023-02-20',NULL,NULL,NULL,NULL,1); INSERT INTO "REC_LOAN_LAST".sys_option (id,option_val,option_txt,option_ord,option_desc,select_id,create_user,create_org,create_time,create_date,update_user,update_org,update_time,update_date,cur_vers) VALUES ('20747468820909654016','0','否',2,NULL,'20747468696347213824','00000000','999999','2023-04-21 14:54:14','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747468778643652608','1','是',1,NULL,'20747468696347213824','00000000','999999','2023-04-21 14:54:04','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746040673769095168','01','严重',1,NULL,'20746040543825362944','00000000','999999','2023-04-17 16:19:17','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746040715582111744','02','关注',2,NULL,'20746040543825362944','00000000','999999','2023-04-17 16:19:27','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746040743851720704','03','一般',3,NULL,'20746040543825362944','00000000','999999','2023-04-17 16:19:34','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746023398856982528','01','按小时',0,NULL,'20746023306615848960','00000000','999999','2023-04-17 15:10:38','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746023431056654336','02','按天',0,NULL,'20746023306615848960','00000000','999999','2023-04-17 15:10:46','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746023458462236672','03','按周',0,NULL,'20746023306615848960','00000000','999999','2023-04-17 15:10:52','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746023483749695488','04','按月',0,NULL,'20746023306615848960','00000000','999999','2023-04-17 15:10:58','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746023557821104128','05','按季',0,NULL,'20746023306615848960','00000000','999999','2023-04-17 15:11:16','2023-02-20',NULL,NULL,NULL,NULL,1); INSERT INTO "REC_LOAN_LAST".sys_option (id,option_val,option_txt,option_ord,option_desc,select_id,create_user,create_org,create_time,create_date,update_user,update_org,update_time,update_date,cur_vers) VALUES ('20746023589102223360','06','按年',0,NULL,'20746023306615848960','00000000','999999','2023-04-17 15:11:24','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746043027318898688','01','首页',0,NULL,'20746042953343959040','00000000','999999','2023-04-17 16:28:38','2023-02-20',NULL,NULL,NULL,NULL,1), ('20746043051528421376','02','短信',0,NULL,'20746042953343959040','00000000','999999','2023-04-17 16:28:44','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747098591138414592','rule01','等于',0,NULL,'20747098528429375488','00000000','999999','2023-04-20 14:23:04','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747098636462063616','rule02','大于等于',0,NULL,'20747098528429375488','00000000','999999','2023-04-20 14:23:15','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747098672520495104','rule03','大于',0,NULL,'20747098528429375488','00000000','999999','2023-04-20 14:23:23','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747098706154618880','rule04','小于等于',0,NULL,'20747098528429375488','00000000','999999','2023-04-20 14:23:31','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747098740455636992','rule05','小于',0,NULL,'20747098528429375488','00000000','999999','2023-04-20 14:23:40','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747098777940131840','rule06','不等于',0,NULL,'20747098528429375488','00000000','999999','2023-04-20 14:23:49','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747477624866996224','rule07','在....之间',0,'在....之间','20747098528429375488','00000000','999999','2023-04-21 15:29:13','2023-02-20',NULL,NULL,NULL,NULL,1); INSERT INTO "REC_LOAN_LAST".sys_option (id,option_val,option_txt,option_ord,option_desc,select_id,create_user,create_org,create_time,create_date,update_user,update_org,update_time,update_date,cur_vers) VALUES ('20747483837512024064','rule08','环比上周期上涨',0,'环比上周期上涨','20747098528429375488','00000000','999999','2023-04-21 15:53:54','2023-02-20','00000000','999999','2023-04-21 15:54:09','2023-04-21',1), ('20747483984258138112','rule09','环比上周期下降',0,'环比上周期下降','20747098528429375488','00000000','999999','2023-04-21 15:54:29','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747484058157580288','rule10','同比昨天同时段上涨',0,'同比昨天同时段上涨','20747098528429375488','00000000','999999','2023-04-21 15:54:47','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747485285599674368','rule11','同比昨天同时段下降',0,'同比昨天同时段下降','20747098528429375488','00000000','999999','2023-04-21 15:59:39','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747485431511121920','rule12','同比近7天同时段均值上涨',0,'同比近7天同时段均值上涨','20747098528429375488','00000000','999999','2023-04-21 16:00:14','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747485497739182080','rule13','同比近7天同时段均值下降',0,'同比近7天同时段均值下降','20747098528429375488','00000000','999999','2023-04-21 16:00:30','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747485696637272064','rule14','对比近7天日均值上涨',0,'对比近7天日均值上涨','20747098528429375488','00000000','999999','2023-04-21 16:01:17','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747485764316561408','rule15','对比近7天日均值下降',0,'对比近7天日均值下降','20747098528429375488','00000000','999999','2023-04-21 16:01:33','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747490392168988672','0','停用',1,'停用','20747490276360060928','00000000','999999','2023-04-21 16:19:57','2023-02-20',NULL,NULL,NULL,NULL,1), ('20747490437345837056','1','启用',2,'启用','20747490276360060928','00000000','999999','2023-04-21 16:20:07','2023-02-20',NULL,NULL,NULL,NULL,1); INSERT INTO "REC_LOAN_LAST".sys_option (id,option_val,option_txt,option_ord,option_desc,select_id,create_user,create_org,create_time,create_date,update_user,update_org,update_time,update_date,cur_vers) VALUES ('20746053943565484032','target01','逾期风险',0,NULL,'20746053785268256768','00000000','999999','2023-04-17 17:12:01','2023-02-20','00000000','999999','2023-04-17 17:12:51','2023-04-17',1), ('20746053979024130048','target02','审批通过率',0,NULL,'20746053785268256768','00000000','999999','2023-04-17 17:12:09','2023-02-20','00000000','999999','2023-04-17 17:13:04','2023-04-17',1), ('20746054090559062016','target03','招行余额',0,NULL,'20746053785268256768','00000000','999999','2023-04-17 17:12:36','2023-02-20','00000000','999999','2023-04-17 17:13:10','2023-04-17',1), ('20754293945822871552','target04','违约率',0,NULL,'20746053785268256768','00000000','999999','2023-05-10 10:54:50','2023-02-20',NULL,NULL,NULL,NULL,1), ('20754294015687393280','target05','不良率',0,NULL,'20746053785268256768','00000000','999999','2023-05-10 10:55:07','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749209296717217792','01','等级',0,'按等级配置进行风险阈值规则配置','20749209109521235968','00000000','999999','2023-04-26 10:10:15','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749209369010241536','02','百分比',0,'按比例进行风险阈值规则配置','20749209109521235968','00000000','999999','2023-04-26 10:10:33','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749209423867543552','03','次数',0,'按次数进行风险阈值规则配置','20749209109521235968','00000000','999999','2023-04-26 10:10:46','2023-02-20',NULL,NULL,NULL,NULL,1); INSERT INTO "REC_LOAN_LAST".sys_tree (id,name,code,pid,route,route_name,ord,lev,flag,icon,tab_url,mark,sec_code,create_user,create_org,create_time,create_date,update_user,update_org,update_time,update_date,cur_vers) VALUES ('20749206989648691200','风险指标','01','20749206403935109120','WARNING_TARGET_TYPE,01','风险指标',1,NULL,NULL,NULL,NULL,NULL,'WARNING_TARGET_TYPE','00000000','999999','2023-04-26 10:01:05','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749207790664286208','运营指标','02','20749206403935109120','WARNING_TARGET_TYPE,02','运营指标',2,NULL,NULL,NULL,NULL,NULL,'WARNING_TARGET_TYPE','00000000','999999','2023-04-26 10:04:16','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749207071303401472','欺诈风险指标','10','20749206989648691200','WARNING_TARGET_TYPE,01,10','风险指标,欺诈风险指标',1,NULL,NULL,NULL,NULL,NULL,'WARNING_TARGET_TYPE','00000000','999999','2023-04-26 10:01:25','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749207869345234944','风控预警指标','10','20749207790664286208','WARNING_TARGET_TYPE,02,10','运营指标,风控预警指标',1,NULL,NULL,NULL,NULL,NULL,'WARNING_TARGET_TYPE','00000000','999999','2023-04-26 10:04:35','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749207123316965376','信用风险指标','20','20749206989648691200','WARNING_TARGET_TYPE,01,20','风险指标,信用风险指标',2,NULL,NULL,NULL,NULL,NULL,'WARNING_TARGET_TYPE','00000000','999999','2023-04-26 10:01:37','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749207189406613504','逾期指标','30','20749206989648691200','WARNING_TARGET_TYPE,01,30','风险指标,逾期指标',3,NULL,NULL,NULL,NULL,NULL,'WARNING_TARGET_TYPE','00000000','999999','2023-04-26 10:01:53','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749207247065710592','业务处理情况','40','20749206989648691200','WARNING_TARGET_TYPE,01,40','风险指标,业务处理情况',4,NULL,NULL,NULL,NULL,NULL,'WARNING_TARGET_TYPE','00000000','999999','2023-04-26 10:02:07','2023-02-20',NULL,NULL,NULL,NULL,1), ('20749206403935109120','预警指标类型','WARNING_TARGET_TYPE','000000','WARNING_TARGET_TYPE','预警指标类型',11,NULL,NULL,NULL,NULL,NULL,'WARNING_TARGET_TYPE','00000000','999999','2023-04-26 09:58:46','2023-02-20',NULL,NULL,NULL,NULL,1);