18002

delete from pawmrpcs.data_check_rule_set where RULE_CODE IN 
(
'LCZX.A.003.004',
'LCZX.A.003.012',
'LCZX.A.019.028',
'LCZX.A.019.053',
'LCZX.A.022.136',
'LCZX.A.029.053',
'LCZX.A.031.052',
'LCZX.A.033.070',
'LCZX.A.034.024',
'LCZX.A.034.034',
'LCZX.A.034.037',
'LCZX.A.051.028',
'LCZX.A.051.053',
'LCZX.A.054.136',
'LCZX.A.061.053',
'LCZX.A.070.032',
'LCZX.A.070.033',
'LCZX.BANK.A.003.004',
'LCZX_BANK.A.019.028',
'LCZX_BANK.A.019.053',
'LCZX_BANK.A.019.057',
'LCZX_BANK.A.022.136',
'LCZX_BANK.A.029.053',
'LCZX_BANK.A.031.052',
'LCZX_BANK.A.051.028',
'LCZX_BANK.A.051.053',
'LCZX_BANK.A.054.136',
'LCZX_BANK.A.061.053',
'RH299.A.1-1.028',
'RH299.A.1-1.059',
'RH299.A.1-4.014',
'RH299.A.2-3.013',
'RH299_BANK.A.1-1.028',
'RH299_BANK.A.1-1.059',
'RH299_BANK.A.1-4.014',
'RH299_BANK.A.2-3.013'
);

INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.003.004', 't_r_lczx_prod_end_info', '“已募集登记”状态的产品可进行终止登记，“已终止登记”状态的产品不能终止登记', '“已募集登记”状态的产品可进行终止登记，“已终止登记”状态的产品不能终止登记', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'PROD_REG_ID\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, 0 AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\n    from t_r_lczx_prod_end_info t\r\n    where  T.FILE_DATE = \'@FILE_DATE\'\r\n       AND (PROD_REG_ID  NOT IN \r\n    (select  PROD_REG_CODE FROM t_r_lczx_prod_raise_register_result)\r\n    or PROD_REG_ID  in \r\n    (select PROD_REG_ID from t_r_lczx_prod_end_info_result))\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.003.012', 't_r_lczx_prod_end_info', '{银行实际实现收入}={本机构管理费}', '{银行实际实现收入}={本机构管理费}', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'BANK_INCO_ACT,HOLD_FEE_S,MANAGER_FEE,SALE_FEE_S,OTHER_FEE\' AS COLUMN_CODE,\r\n       0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n       T.FUND_CATEGORY AS INSTI_TYPE,\r\n       GROUP_CONCAT(IFNULL(T.BANK_INCO_ACT, 0)) AS LEFT_CALCULATION_VALUE,\r\n       GROUP_CONCAT(IFNULL(T.MANAGER_FEE, 0)) AS RIGHT_CALCULATION_VALUE,\r\n       GROUP_CONCAT(ABS(IFNULL(T.BANK_INCO_ACT, 0) - IFNULL(T.MANAGER_FEE, 0)\r\n            ))AS DIFFERENCE\r\nFROM   T_R_LCZX_PROD_END_INFO T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    IFNULL(T.BANK_INCO_ACT, 0) <> IFNULL(T.MANAGER_FEE, 0)\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.019.028', 't_r_lczx_zcys_02', '{资金存入银行}最长200位字符', '{资金存入银行}最长200位字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REP_BANK_NAME\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   T_R_LCZX_ZCYS_02  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND   LENGTH(CONVERT(REP_BANK_NAME USING GBK)) > 200\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.019.053', 't_r_lczx_zcys_02', '{结构性存款挂钩标的}最长256个字符', '{结构性存款挂钩标的}最长256个字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REP_TARGET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   T_R_LCZX_ZCYS_02  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    LENGTH(CONVERT(REP_TARGET USING GBK)) > 256\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.022.136', 't_r_lczx_zcys_05', '{首次重定价日期}日期型，八位长字符，格式为 yyyymmdd', '{首次重定价日期}日期型，八位长字符，格式为 yyyymmdd', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'FIRST_REPRICING_DATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   T_R_LCZX_ZCYS_05  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    IS_DATE_TYPE( FIRST_REPRICING_DATE ,1)=0\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.029.053', 't_r_lczx_zcys_17', '{是否有预期收益率}选择\"是\"时，{预期最低收益率}选填，选择\"否\"时，不可填', '{是否有预期收益率}选择\"是\"时，{预期最低收益率}选填，选择\"否\"时，不可填', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'YIELD_FLAG,MIN_RATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   T_R_LCZX_ZCYS_17  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    T.YIELD_FLAG=\'02\'\r\nAND    T.MIN_RATE IS NOT NULL\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.031.052', 't_r_lczx_practitioner', '{电子邮箱}格式为 xx@xx.xx', '电子邮箱地址不合法,合法email格式如：John@163.com。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'EMAIL\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_practitioner T\r\nWHERE    FILE_DATE=\'@FILE_DATE\'\r\nand (EMAIL  not like \'%@%.%\' or EMAIL like \'@%\'  or EMAIL like \'%@.%\')\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.033.070', 't_r_lczx_filing_register', '{产品销售区域}必填，单选和多选的值都必须在值域以内', '{产品销售区域}必填，单选和多选的值都必须在值域以内', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID\r\n        , \'@RULE_CODE\' AS RULE_CODE\r\n        , GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST\r\n        ,\'SALES_REGION\' AS COLUMN_CODE\r\n        , 0 AS RESULT_CODE,   \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n        , NULL AS LEFT_CALCULATION_VALUE\r\n        , NULL AS RIGHT_CALCULATION_VALUE\r\n        , NULL AS DIFFERENCE \r\n FROM (		\r\n		 SELECT T1.N_ID,T1.FILE_DATE,T1.FUND_CATEGORY,\r\n		 SUBSTRING_INDEX( SUBSTRING_INDEX( T1.SALES_REGION, \',\', h.help_topic_id + 1 ), \',\',- 1 ) AS SALES_REGION  \r\n     FROM  T_R_LCZX_FILING_REGISTER T1\r\n		 left JOIN pawmrpcs.help_topic H\r\n		 ON h.help_topic_id < ( ( length( T1.SALES_REGION ) - length( REPLACE ( T1.SALES_REGION, \',\', \'\' ) ) + 1 )))T\r\n		 WHERE T.FILE_DATE = \'@FILE_DATE\'\r\n		 AND  GET_DICT(\'LCZX_PROD_SALE_REGION\',T.SALES_REGION) = 0\r\n	GROUP  BY T.FUND_CATEGORY, T.FILE_DATE;	');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.034.024', 't_r_lczx_issuance_register', '{是否为分级产品}选择“ 01 是”时{分级比例}必填；{是否为分级产品}选择“02 否”时{分级比例}不可填写。', '{是否为分级产品}选择“ 01 是”时{分级比例}必填；{是否为分级产品}选择“02 否”时{分级比例}不可填写。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST, \'STRUCTURED_OPTION,PRIORITY_RATIO\' AS COLUMN_CODE,\r\n       \'0\' AS RESULT_CODE, FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   T_R_LCZX_ISSUANCE_REGISTER T\r\nWHERE  FILE_DATE = \'@FILE_DATE\'\r\nAND    ((STRUCTURED_OPTION = \'01\' AND PRIORITY_RATIO IS NULL)\r\nOR     (STRUCTURED_OPTION = \'02\' AND PRIORITY_RATIO IS NOT NULL))\r\nGROUP  BY FILE_DATE, FUND_CATEGORY');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.034.034', 't_r_lczx_issuance_register', '当{发行登记}.{开放模式}选择\"01 有规律开放\" 时，{规律开放周期}必填；当{发行登记}.{开放模式}选择“ 02 无规律开放”时，{规律开放周期}必须为空', '当{发行登记}.{开放模式}选择\"01 有规律开放\" 时，{规律开放周期}必填；当{发行登记}.{开放模式}选择“ 02 无规律开放”时，{规律开放周期}必须为空', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST,\r\n       \'OPEN_MODE,REGULAR_OPEN_PERIOD\' AS COLUMN_CODE, \'0\' AS RESULT_CODE,\r\n       FILE_DATE, FUND_CATEGORY AS INSTI_TYPE, NULL AS LEFT_CALCULATION_VALUE,\r\n       NULL AS RIGHT_CALCULATION_VALUE, NULL AS DIFFERENCE\r\nFROM   T_R_LCZX_ISSUANCE_REGISTER T\r\nWHERE  FILE_DATE = \'@FILE_DATE\'\r\nAND   ((OPEN_MODE = \'01\' AND  REGULAR_OPEN_PERIOD IS NULL) OR (OPEN_MODE = \'02\' AND  REGULAR_OPEN_PERIOD IS NOT NULL))\r\nGROUP  BY FILE_DATE, FUND_CATEGORY\r\n');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.034.037', 't_r_lczx_issuance_register', '对{规律开放周期}选择“ 99 其他规律开放周期 ”的开放式产品，{其他规律开放周期（天）}必填;{规律开放周期}选择“ 99 其他规律开放周期”以外的要素时，{其他规律开放周期（天）}必须为空； “开放模式”选择“ 02 无规律开放”时，{其他规律开放周期（天）}必须为空；', '对{规律开放周期}选择“ 99 其他规律开放周期 ”的开放式产品，{其他规律开放周期（天）}必填;{规律开放周期}选择“ 99 其他规律开放周期”以外的要素时，{其他规律开放周期（天）}必须为空； “开放模式”选择“ 02 无规律开放”时，{其他规律开放周期（天）}必须为空；', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'OPERATION_MODE,REGULAR_OPEN_PERIOD,OTHER_REGULAR_OPEN_PERIOD\' AS COLUMN_CODE,\r\n       0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n       T.FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE,\r\n       NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   (SELECT T1.FILE_DATE, T1.N_ID, T1.FUND_CATEGORY,T1.OPEN_MODE,\r\n               T1.REGULAR_OPEN_PERIOD, T1.OTHER_REGULAR_OPEN_PERIOD,\r\n                T3.OPERATION_MODE\r\n         FROM   t_r_lczx_issuance_register T1\r\n         LEFT   JOIN t_r_lczx_filing_register_result T3\r\n				 ON     T3.PRO_CODE = T1.PRODUCT_CODE ) T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    ((T.OPERATION_MODE IN(\'03\',\'04\') AND REGULAR_OPEN_PERIOD =\'99\' AND OTHER_REGULAR_OPEN_PERIOD IS  NULL)\r\nOR    (REGULAR_OPEN_PERIOD <>\'99\' AND OTHER_REGULAR_OPEN_PERIOD IS  NOT NULL)\r\nOR (OPEN_MODE IN (\'02\') AND OTHER_REGULAR_OPEN_PERIOD IS  NOT NULL))\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.051.028', 't_r_lczx_zcys_all', '{资金存入银行}最长200位字符', '{资金存入银行}最长200位字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REP_BANK_NAME_02\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, \'Z70033\' AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   T_R_LCZX_ZCYS_ALL  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'  AND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nAND    LENGTH(CONVERT(REP_BANK_NAME_02 USING GBK)) > 200\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_02\') GROUP  BY  T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.051.053', 't_r_lczx_zcys_all', '{结构性存款挂钩标的}最长256个字符', '{结构性存款挂钩标的}最长256个字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REP_REGIST_ORG_COMMENT_03\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, \'Z70033\' AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   T_R_LCZX_ZCYS_ALL  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'  AND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nAND    LENGTH(CONVERT(REP_REGIST_ORG_COMMENT_03 USING GBK))  > 256\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_03\') GROUP  BY  T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.054.136', 't_r_lczx_zcys_all', '{首次重定价日期}日期型，八位长字符，格式为 yyyymmdd', '{首次重定价日期}日期型，八位长字符，格式为 yyyymmdd', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT\r\n \'@BATCH_ID\' AS BATCH_ID,\r\n \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'FIRST_REPRICING_DATE_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'Z70033\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n T_R_LCZX_ZCYS_ALL T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\r\n SELECT\r\n  ITEM_KEY\r\n FROM\r\n  T_P_LCZX_ZCYS_REPORT\r\n WHERE\r\n  ITEM_GROUP = \'T_R_LCZX_ZCYS_05\')\r\nand  IS_DATE_TYPE(FIRST_REPRICING_DATE_05,1)=0\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.061.053', 't_r_lczx_zcys_all', '{是否有预期收益率}选择\"是\"时，{预期最低收益率}选填，选择\"否\"时，不可填', '{是否有预期收益率}选择\"是\"时，{预期最低收益率}选填，选择\"否\"时，不可填', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT\r\n \'@BATCH_ID\' AS BATCH_ID,\r\n \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'YIELD_FLAG_17,MIN_RATE_17\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'Z70033\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n T_R_LCZX_ZCYS_ALL T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\r\n SELECT\r\n  ITEM_KEY\r\n FROM\r\n  T_P_LCZX_ZCYS_REPORT\r\n WHERE\r\n  ITEM_GROUP = \'T_R_LCZX_ZCYS_17\')\r\nand YIELD_FLAG_17 in (\'02\') and MIN_RATE_17 is not null\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.070.032', 't_r_zjjy_01', '{销售结算资金交易币种}在《世界各地货币代码》中必须存在', '{销售结算资金交易币种}在《世界各地货币代码》中必须存在', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'CUR_TYPE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, NULL AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_zjjy_01  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    GET_DICT(\'LCZX_CURRENCY\',T.CUR_TYPE) = 0 AND  CUR_TYPE IS NOT NULL\r\nGROUP  BY  T.FILE_DATE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.070.033', 't_r_zjjy_01', '{销售结算资金交易币种}值域范围内单选', '{销售结算资金交易币种}值域范围内单选', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'CUR_TYPE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, NULL AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_zjjy_01  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    (GET_DICT(\'LCZX_CURRENCY\',T.CUR_TYPE) = 1 and LENGTH(CUR_TYPE)<>3)\r\nand CUR_TYPE is not null\r\nGROUP  BY  T.FILE_DATE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.BANK.A.003.004', 't_r_lczx_prod_end_info_bank', '“已募集登记”状态的产品可进行终止登记，“已终止登记”状态的产品不能终止登记', '“已募集登记”状态的产品可进行终止登记，“已终止登记”状态的产品不能终止登记', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'PROD_REG_ID\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, 0 AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\n    from t_r_lczx_prod_end_info_bank t\r\n    where  T.FILE_DATE = \'@FILE_DATE\'\r\n       AND (PROD_REG_ID  NOT IN \r\n    (select  PROD_REG_CODE FROM t_r_lczx_prod_raise_register_result)\r\n    or PROD_REG_ID  in \r\n    (select PROD_REG_ID from t_r_lczx_prod_end_info_result))\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.019.028', 't_r_lczx_zcys_02_bank', '{资金存入银行}最长200位字符', '{资金存入银行}最长200位字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REP_BANK_NAME\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_zcys_02_bank  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND   LENGTH(CONVERT(REP_BANK_NAME USING GBK)) > 200\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.019.053', 't_r_lczx_zcys_02_bank', '{结构性存款挂钩标的}最长256个字符', '{结构性存款挂钩标的}最长256个字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REP_TARGET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_zcys_02_bank  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    LENGTH(CONVERT(T.REP_TARGET USING GBK)) > 256\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.019.057', 't_r_lczx_zcys_02_bank', '{备注}为必填项，最长256个字符', '{备注}为必填项，最长256个字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REMARK\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_zcys_02_bank  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    LENGTH(CONVERT(T.REMARK USING GBK)) >256\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.022.136', 't_r_lczx_zcys_05_bank', '{首次重定价日期}日期型，八位长字符，格式为 yyyymmdd', '{首次重定价日期}日期型，八位长字符，格式为 yyyymmdd', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'FIRST_REPRICING_DATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_zcys_05_bank  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    IS_DATE_TYPE( FIRST_REPRICING_DATE ,1)=0\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.029.053', 't_r_lczx_zcys_17_bank', '{是否有预期收益率}选择\"是\"时，{预期最低收益率}选填，选择\"否\"时，不可填', '{是否有预期收益率}选择\"是\"时，{预期最低收益率}选填，选择\"否\"时，不可填', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'YIELD_FLAG,MIN_RATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_zcys_17_bank  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'\r\nAND    T.YIELD_FLAG=\'02\'\r\nAND    T.MIN_RATE IS NOT NULL\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.031.052', 't_r_lczx_practitioner_bank', '{电子邮箱}格式为 xx@xx.xx', '电子邮箱地址不合法,合法email格式如：John@163.com。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'EMAIL\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_practitioner_bank T\r\nWHERE    FILE_DATE=\'@FILE_DATE\'\r\nand (EMAIL  not like \'%@%.%\' or EMAIL like \'@%\'  or EMAIL like \'%@.%\')\r\nGROUP  BY T.FUND_CATEGORY, T.FILE_DATE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.051.028', 't_r_lczx_zcys_all_bank', '{资金存入银行}最长200位字符', '{资金存入银行}最长200位字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REP_BANK_NAME_02\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, \'C10307\' AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_zcys_all_bank  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'  AND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nAND   LENGTH(CONVERT(REP_BANK_NAME_02 USING GBK)) > 200\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_02\') GROUP  BY  T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.051.053', 't_r_lczx_zcys_all_bank', '{结构性存款挂钩标的}最长256个字符', '{结构性存款挂钩标的}最长256个字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n       \'REP_TARGET_02\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n       \'@FILE_DATE\' AS FILE_DATE, \'C10307\' AS INSTI_TYPE,\r\n       NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n       NULL AS DIFFERENCE\r\nFROM   t_r_lczx_zcys_all_bank  T\r\nWHERE  T.FILE_DATE = \'@FILE_DATE\'  AND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nAND   LENGTH(CONVERT(REP_TARGET_02 USING GBK)) > 256\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_02\') GROUP  BY  T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.054.136', 't_r_lczx_zcys_all_bank', '{首次重定价日期}日期型，八位长字符，格式为 yyyymmdd', '{首次重定价日期}日期型，八位长字符，格式为 yyyymmdd', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT\r\n \'@BATCH_ID\' AS BATCH_ID,\r\n \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'FIRST_REPRICING_DATE_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'Z70033\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n t_r_lczx_zcys_all_bank T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\r\n SELECT\r\n  ITEM_KEY\r\n FROM\r\n  T_P_LCZX_ZCYS_REPORT\r\n WHERE\r\n  ITEM_GROUP = \'T_R_LCZX_ZCYS_05\')\r\nand  IS_DATE_TYPE(FIRST_REPRICING_DATE_05,1)=0\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.061.053', 't_r_lczx_zcys_all_bank', '{是否有预期收益率}选择\"是\"时，{预期最低收益率}选填，选择\"否\"时，不可填', '{是否有预期收益率}选择\"是\"时，{预期最低收益率}选填，选择\"否\"时，不可填', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT\r\n \'@BATCH_ID\' AS BATCH_ID,\r\n \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'YIELD_FLAG_17,MIN_RATE_17\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'Z70033\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n t_r_lczx_zcys_all_bank T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\r\n SELECT\r\n  ITEM_KEY\r\n FROM\r\n  T_P_LCZX_ZCYS_REPORT\r\n WHERE\r\n  ITEM_GROUP = \'T_R_LCZX_ZCYS_17\')\r\nand YIELD_FLAG_17 in (\'02\') and MIN_RATE_17 is not null\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299.A.1-1.028', 't_r_299_1_1', '5.对于净值型产品，表1-1募集余额填报资管产品期末份额乘以期末净值的余额，此募集余额与实收本金通常不应相等。故，若表1-1中净值型产品（开放式净值产品、封闭式净值型产品）有数，且表2-1中实收本金和表1-1募集资金余额相等，须核实。 ', '5.对于净值型产品，表1-1募集余额填报资管产品期末份额乘以期末净值的余额，此募集余额与实收本金通常不应相等。故，若表1-1中净值型产品（开放式净值产品、封闭式净值型产品）有数，且表2-1中实收本金和表1-1募集资金余额相等，须核实。 ', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST, GROUP_CONCAT(DISTINCT COL_ORG) AS COLUMN_CODE,\r\n       MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS  RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM   (SELECT CASE\r\n                  WHEN SUM(IF(ITEM_CODE = \'821000\', T.V, 0)) =\r\n                       SUM(IF(ITEM_CODE = \'100000\', T.V, 0))\r\n                       OR (SIGN(SUM(IF(ITEM_CODE = \'121000\', T.V, 0))) <> 0 OR\r\n                            SIGN(SUM(IF(ITEM_CODE = \'123000\', T.V, 0))) <> 0) THEN\r\n                   0\r\n                  ELSE\r\n                   1\r\n                END AS RESULT_CODE, T.FDATE AS FILE_DATE,\r\n                GROUP_CONCAT(DISTINCT CASE\r\n                               WHEN TABLE_NAME IN (\'1_1\') THEN\r\n                                N_ID\r\n                             END) AS N_ID, GROUP_CONCAT(DISTINCT COL_ORG) AS COL_ORG,\r\n                T.FUND_CATEGORY AS INSTI_TYPE\r\n         FROM   T_R_299_REPORT_RESULT T\r\n         WHERE  FDATE = \'@FILE_DATE\'\r\n         AND    TABLE_NAME IN (\'1_1\', \'2_1\')\r\n         AND    ((ITEM_CODE IN (\'100000\', \'121000\', \'123000\') AND COL IN(\'A\',\'B\',\'C\',\'D\',\'E\',\'F\',\'G\')) OR\r\n               (ITEM_CODE = \'821000\' AND COL IN(\'H\',\'I\',\'J\',\'K\',\'L\',\'M\',\'N\')))\r\n         GROUP  BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE) T1\r\nWHERE  T1.RESULT_CODE = \'0\'\r\nGROUP  BY T1.FILE_DATE, T1.INSTI_TYPE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299.A.1-1.059', 't_r_299_1_1', '规则13：表1-1、表2-1，无净值产品，募集资金余额与实收本金相差较大（变动比例1%），请核实；', '规则13：表1-1、表2-1，无净值产品，募集资金余额与实收本金相差较大（变动比例1%），请核实；', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '警告', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST, MAX(COL_ORG) AS COLUMN_CODE,\r\n       MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS  RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM   (SELECT CASE\r\n                  WHEN SUM(IF(ITEM_CODE = \'121000\'\r\n                              AND COL = \'G\', T.V, 0)) +\r\n                       SUM(IF(ITEM_CODE = \'123000\'\r\n                              AND COL = \'G\', T.V, 0)) = 0\r\n                       AND SUM(IF(ITEM_CODE = \'100000\'\r\n                                  AND COL = \'G\', T.V, 0)) <> 0\r\n                       AND ABS(SUM(IF(ITEM_CODE = \'821000\'\r\n                                      AND COL = \'N\', T.V, 0)) /\r\n                               SUM(IF(ITEM_CODE = \'100000\'\r\n                                      AND COL = \'G\', T.V, 0)) - 1) > 0.01 THEN\r\n                   0\r\n                  ELSE\r\n                   1\r\n                END AS RESULT_CODE, T.FDATE AS FILE_DATE,\r\n                GROUP_CONCAT(DISTINCT CASE\r\n                               WHEN TABLE_NAME = \'1_1\' THEN\r\n                                N_ID\r\n                             END) AS N_ID,\r\n                GROUP_CONCAT(DISTINCT CASE\r\n                               WHEN TABLE_NAME = \'1_1\' THEN\r\n                                COL_ORG\r\n                             END) AS COL_ORG, FUND_CATEGORY AS INSTI_TYPE\r\n         FROM   T_R_299_REPORT_RESULT T\r\n         WHERE  FDATE = \'@FILE_DATE\'\r\n         AND    TABLE_NAME IN (\'1_1\', \'2_1\')\r\n         AND    ((ITEM_CODE IN ( \'100000\',\'121000\',\'123000\') AND COL = \'G\')\r\n         OR     (ITEM_CODE = \'821000\' AND COL = \'N\'))\r\n         GROUP  BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE) T1\r\nWHERE  T1.RESULT_CODE = \'0\'\r\nGROUP  BY T1.FILE_DATE, T1.INSTI_TYPE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299.A.1-4.014', 't_r_299_1_4', '规则18：表1-4，只分了一个地区(大于30亿元)，请核实；', '规则18：表1-4，只分了一个地区(大于30亿元)，请核实；', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '警告', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST,\r\n       GROUP_CONCAT(DISTINCT COL_ORG) AS COLUMN_CODE,\r\n       MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS  RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM   (\r\nSELECT \r\nCASE\r\n                  WHEN SUM(IF(a.ITEM_CODE = \'400000\', V, 0)) > 3 * POWER(10, 5) OR \r\n									(SUM(IF(a.ITEM_CODE = \'400000\', V, V2)) > 3 * POWER(10, 5)\r\n                       and ABS(SUM(IF(a.ITEM_CODE NOT IN (\'400000\'), V, 0)) - V2) < 10000) THEN\r\n                   0\r\n                  ELSE\r\n                   1\r\n                END AS RESULT_CODE, a.FDATE AS FILE_DATE,\r\n                GROUP_CONCAT(DISTINCT a.N_ID) AS N_ID,\r\n                GROUP_CONCAT(DISTINCT a.COL_ORG) AS COL_ORG,\r\n                a.FUND_CATEGORY AS INSTI_TYPE\r\nFROM \r\n(SELECT * FROM  t_r_299_report_result  \r\nWHERE  FDATE = \'@FILE_DATE\'\r\nAND    TABLE_NAME IN (\'1_4\')\r\nAND    COL = \'G\') A\r\nLEFT JOIN \r\n(select TABLE_NAME,FDATE,COL,V as V2,FUND_TYPE,FUND_CATEGORY from T_R_299_REPORT_RESULT  \r\nWHERE  FDATE = \'@FILE_DATE\'\r\nAND    TABLE_NAME IN (\'1_4\')\r\nAND    COL = \'G\'\r\nAND  ITEM_CODE = \'400000\') B\r\nON A.FDATE=B.FDATE\r\nAND A.TABLE_NAME=B.TABLE_NAME\r\nAND A.FUND_TYPE=B.FUND_TYPE\r\nAND A.FUND_CATEGORY=B.FUND_CATEGORY\r\nGROUP  BY A.FDATE, A.FUND_CATEGORY, A.FUND_TYPE, A.ITEM_CODE, A.COL) T\r\nWHERE  T.RESULT_CODE = \'0\'\r\nAND N_ID IS NOT NULL\r\nGROUP  BY T.FILE_DATE, T.INSTI_TYPE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299.A.2-3.013', 't_r_299_2_3', '规则31：表2-3，只分了一个地区(大于30亿元)，请核实；', '规则31：表2-3，只分了一个地区(大于30亿元)，请核实；', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '警告', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST,\r\n       GROUP_CONCAT(DISTINCT COL_ORG) AS COLUMN_CODE,\r\n       MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS  RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM   (\r\nSELECT \r\nCASE\r\n                  WHEN SUM(IF(a.ITEM_CODE = \'a00000\', V, 0)) > 3 * POWER(10, 5)\r\n									or  (SUM(IF(a.ITEM_CODE = \'a00000\', V, V2)) > 3 * POWER(10, 5)\r\n                       and ABS(SUM(IF(a.ITEM_CODE NOT IN (\'a00000\'), V, 0)) - V2) < 10000)\r\n											 THEN\r\n                   0\r\n                  ELSE\r\n                   1\r\n                END AS RESULT_CODE, a.FDATE AS FILE_DATE,\r\n                GROUP_CONCAT(DISTINCT a.N_ID) AS N_ID,\r\n                GROUP_CONCAT(DISTINCT a.COL_ORG) AS COL_ORG,\r\n                a.FUND_CATEGORY AS INSTI_TYPE\r\nFROM \r\n(SELECT * FROM  t_r_299_report_result  \r\nWHERE  FDATE = \'@FILE_DATE\'\r\nAND    TABLE_NAME IN (\'2_3\')\r\nAND    COL = \'A\') A\r\nLEFT JOIN \r\n(select TABLE_NAME,FDATE,COL,V as V2,FUND_TYPE,FUND_CATEGORY from t_r_299_report_result  \r\nWHERE  FDATE = \'@FILE_DATE\'\r\nAND    TABLE_NAME IN (\'2_3\')\r\nAND    COL = \'A\'\r\nAND  ITEM_CODE = \'a00000\') B\r\nON A.FDATE=B.FDATE\r\nAND A.TABLE_NAME=B.TABLE_NAME\r\nAND A.FUND_TYPE=B.FUND_TYPE\r\nand A.FUND_CATEGORY=B.FUND_CATEGORY\r\nGROUP  BY A.FDATE, A.FUND_CATEGORY, A.FUND_TYPE, A.ITEM_CODE, A.COL) T\r\nWHERE  T.RESULT_CODE = \'0\'\r\nAND N_ID IS NOT NULL\r\nGROUP  BY T.FILE_DATE, T.INSTI_TYPE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299_BANK.A.1-1.028', 't_r_299_1_1_bank', '5.对于净值型产品，表1-1募集余额填报资管产品期末份额乘以期末净值的余额，此募集余额与实收本金通常不应相等。故，若表1-1中净值型产品（开放式净值产品、封闭式净值型产品）有数，且表2-1中实收本金和表1-1募集资金余额相等，须核实。 ', '5.对于净值型产品，表1-1募集余额填报资管产品期末份额乘以期末净值的余额，此募集余额与实收本金通常不应相等。故，若表1-1中净值型产品（开放式净值产品、封闭式净值型产品）有数，且表2-1中实收本金和表1-1募集资金余额相等，须核实。 ', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST, GROUP_CONCAT(DISTINCT COL_ORG) AS COLUMN_CODE,\r\n       MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS  RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM   (SELECT CASE\r\n                  WHEN SUM(IF(ITEM_CODE = \'821000\', T.V, 0)) =\r\n                       SUM(IF(ITEM_CODE = \'100000\', T.V, 0))\r\n                       OR (SIGN(SUM(IF(ITEM_CODE = \'121000\', T.V, 0))) <> 0 OR\r\n                            SIGN(SUM(IF(ITEM_CODE = \'123000\', T.V, 0))) <> 0) THEN\r\n                   0\r\n                  ELSE\r\n                   1\r\n                END AS RESULT_CODE, T.FDATE AS FILE_DATE,\r\n                GROUP_CONCAT(DISTINCT CASE\r\n                               WHEN TABLE_NAME IN (\'1_1\') THEN\r\n                                N_ID\r\n                             END) AS N_ID, GROUP_CONCAT(DISTINCT COL_ORG) AS COL_ORG,\r\n                T.FUND_CATEGORY AS INSTI_TYPE\r\n         FROM   t_r_299_report_bank_result T\r\n         WHERE  FDATE = \'@FILE_DATE\'\r\n         AND    TABLE_NAME IN (\'1_1\', \'2_1\')\r\n         AND    ((ITEM_CODE IN (\'100000\', \'121000\', \'123000\') AND COL IN(\'A\',\'B\',\'C\',\'D\',\'E\',\'F\',\'G\')) OR\r\n               (ITEM_CODE = \'821000\' AND COL IN(\'H\',\'I\',\'J\',\'K\',\'L\',\'M\',\'N\')))\r\n         GROUP  BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE) T1\r\nWHERE  T1.RESULT_CODE = \'0\'\r\nGROUP  BY T1.FILE_DATE, T1.INSTI_TYPE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299_BANK.A.1-1.059', 't_r_299_1_1_bank', '规则13：表1-1、表2-1，无净值产品，募集资金余额与实收本金相差较大（变动比例1%），请核实；', '规则13：表1-1、表2-1，无净值产品，募集资金余额与实收本金相差较大（变动比例1%），请核实；', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '警告', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST, MAX(COL_ORG) AS COLUMN_CODE,\r\n       MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS  RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM   (SELECT CASE\r\n                  WHEN SUM(IF(ITEM_CODE = \'121000\'\r\n                              AND COL = \'G\', T.V, 0)) +\r\n                       SUM(IF(ITEM_CODE = \'123000\'\r\n                              AND COL = \'G\', T.V, 0)) = 0\r\n                       AND SUM(IF(ITEM_CODE = \'100000\'\r\n                                  AND COL = \'G\', T.V, 0)) <> 0\r\n                       AND ABS(SUM(IF(ITEM_CODE = \'821000\'\r\n                                      AND COL = \'N\', T.V, 0)) /\r\n                               SUM(IF(ITEM_CODE = \'100000\'\r\n                                      AND COL = \'G\', T.V, 0)) - 1) > 0.01 THEN\r\n                   0\r\n                  ELSE\r\n                   1\r\n                END AS RESULT_CODE, T.FDATE AS FILE_DATE,\r\n                GROUP_CONCAT(DISTINCT CASE\r\n                               WHEN TABLE_NAME = \'1_1\' THEN\r\n                                N_ID\r\n                             END) AS N_ID,\r\n                GROUP_CONCAT(DISTINCT CASE\r\n                               WHEN TABLE_NAME = \'1_1\' THEN\r\n                                COL_ORG\r\n                             END) AS COL_ORG, FUND_CATEGORY AS INSTI_TYPE\r\n         FROM   T_R_299_REPORT_BANK_RESULT T\r\n         WHERE  FDATE = \'@FILE_DATE\'\r\n         AND    TABLE_NAME IN (\'1_1\', \'2_1\')\r\n         AND    ((ITEM_CODE IN (\'100000\',\'121000\',\'123000\') AND COL = \'G\')\r\n         OR     (ITEM_CODE = \'821000\' AND COL = \'N\'))\r\n         GROUP  BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE) T1\r\nWHERE  T1.RESULT_CODE = \'0\'\r\nGROUP  BY T1.FILE_DATE, T1.INSTI_TYPE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299_BANK.A.1-4.014', 't_r_299_1_4_bank', '规则18：表1-4，只分了一个地区(大于30亿元)，请核实；', '规则18：表1-4，只分了一个地区(大于30亿元)，请核实；', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '警告', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST,\r\n       GROUP_CONCAT(DISTINCT COL_ORG) AS COLUMN_CODE,\r\n       MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS  RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM   (\r\nSELECT \r\nCASE\r\n                  WHEN SUM(IF(a.ITEM_CODE = \'400000\', V, 0)) > 3 * POWER(10, 5) OR \r\n									(SUM(IF(a.ITEM_CODE = \'400000\', V, V2)) > 3 * POWER(10, 5)\r\n                       and ABS(SUM(IF(a.ITEM_CODE NOT IN (\'400000\'), V, 0)) - V2) < 10000) THEN\r\n                   0\r\n                  ELSE\r\n                   1\r\n                END AS RESULT_CODE, a.FDATE AS FILE_DATE,\r\n                GROUP_CONCAT(DISTINCT a.N_ID) AS N_ID,\r\n                GROUP_CONCAT(DISTINCT a.COL_ORG) AS COL_ORG,\r\n                a.FUND_CATEGORY AS INSTI_TYPE\r\nFROM \r\n(SELECT * FROM  t_r_299_report_bank_result \r\nWHERE  FDATE = \'@FILE_DATE\'\r\nAND    TABLE_NAME IN (\'1_4\')\r\nAND    COL = \'G\') A\r\nLEFT JOIN \r\n(select TABLE_NAME,FDATE,COL,V as V2,FUND_TYPE,FUND_CATEGORY from t_r_299_report_bank_result  \r\nWHERE  FDATE = \'@FILE_DATE\'\r\nAND    TABLE_NAME IN (\'1_4\')\r\nAND    COL = \'G\'\r\nAND  ITEM_CODE = \'400000\') B\r\nON A.FDATE=B.FDATE\r\nAND A.TABLE_NAME=B.TABLE_NAME\r\nAND A.FUND_TYPE=B.FUND_TYPE\r\nAND A.FUND_CATEGORY=B.FUND_CATEGORY\r\nGROUP  BY A.FDATE, A.FUND_CATEGORY, A.FUND_TYPE, A.ITEM_CODE, A.COL) T\r\nWHERE  T.RESULT_CODE = \'0\'\r\nAND N_ID IS NOT NULL\r\nGROUP  BY T.FILE_DATE, T.INSTI_TYPE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299_BANK.A.2-3.013', 't_r_299_2_3_bank', '规则31：表2-3，只分了一个地区(大于30亿元)，请核实；', '规则31：表2-3，只分了一个地区(大于30亿元)，请核实；', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '警告', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n       GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST,\r\n       GROUP_CONCAT(DISTINCT COL_ORG) AS COLUMN_CODE,\r\n       MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS  RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM   (\r\nSELECT \r\nCASE\r\n                  WHEN SUM(IF(a.ITEM_CODE = \'a00000\', V, 0)) > 3 * POWER(10, 5)\r\n									or  (SUM(IF(a.ITEM_CODE = \'a00000\', V, V2)) > 3 * POWER(10, 5)\r\n                       and ABS(SUM(IF(a.ITEM_CODE NOT IN (\'a00000\'), V, 0)) - V2) < 10000)\r\n											 THEN\r\n                   0\r\n                  ELSE\r\n                   1\r\n                END AS RESULT_CODE, a.FDATE AS FILE_DATE,\r\n                GROUP_CONCAT(DISTINCT a.N_ID) AS N_ID,\r\n                GROUP_CONCAT(DISTINCT a.COL_ORG) AS COL_ORG,\r\n                a.FUND_CATEGORY AS INSTI_TYPE\r\nFROM \r\n(SELECT * FROM  t_r_299_report_bank_result  \r\nWHERE  FDATE = \'@FILE_DATE\'\r\nAND    TABLE_NAME IN (\'2_3\')\r\nAND    COL = \'A\') A\r\nLEFT JOIN \r\n(select TABLE_NAME,FDATE,COL,V as V2,FUND_TYPE,FUND_CATEGORY from t_r_299_report_bank_result  \r\nWHERE  FDATE = \'@FILE_DATE\'\r\nAND    TABLE_NAME IN (\'2_3\')\r\nAND    COL = \'A\'\r\nAND  ITEM_CODE = \'a00000\') B\r\nON A.FDATE=B.FDATE\r\nAND A.TABLE_NAME=B.TABLE_NAME\r\nAND A.FUND_TYPE=B.FUND_TYPE\r\nand A.FUND_CATEGORY=B.FUND_CATEGORY\r\nGROUP  BY A.FDATE, A.FUND_CATEGORY, A.FUND_TYPE, A.ITEM_CODE, A.COL) T\r\nWHERE  T.RESULT_CODE = \'0\'\r\nAND N_ID IS NOT NULL\r\nGROUP  BY T.FILE_DATE, T.INSTI_TYPE;');