
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);
