delete from pawmrpcs.data_check_rule_set where RULE_CODE IN (
'LCZX.A.004.005',
'LCZX.A.004.074',
'LCZX.A.019.046',
'LCZX.A.021.030',
'LCZX.A.022.100',
'LCZX.A.022.103',
'LCZX.A.022.105',
'LCZX.A.022.107',
'LCZX.A.022.122',
'LCZX.A.022.124',
'LCZX.A.022.142',
'LCZX.A.031.052',
'LCZX.A.032.037',
'LCZX.A.032.041',
'LCZX.A.033.104',
'LCZX.A.033.140',
'LCZX.A.045.003',
'LCZX.A.045.999',
'LCZX.A.051.046',
'LCZX.A.054.100',
'LCZX.A.054.103',
'LCZX.A.054.105',
'LCZX.A.054.107',
'LCZX.A.054.122',
'LCZX.A.054.124',
'LCZX.A.054.142',
'LCZX.A.070.046',
'LCZX.BANK.A.004.005',
'LCZX_BANK.A.019.046',
'LCZX_BANK.A.021.030',
'LCZX_BANK.A.022.100',
'LCZX_BANK.A.022.103',
'LCZX_BANK.A.022.105',
'LCZX_BANK.A.022.107',
'LCZX_BANK.A.022.122',
'LCZX_BANK.A.022.124',
'LCZX_BANK.A.022.142',
'LCZX_BANK.A.031.052',
'LCZX_BANK.A.032.037',
'LCZX_BANK.A.032.041',
'LCZX_BANK.A.045.003',
'LCZX_BANK.A.045.999',
'LCZX_BANK.A.051.046',
'LCZX_BANK.A.054.100',
'LCZX_BANK.A.054.103',
'LCZX_BANK.A.054.122',
'LCZX_BANK.A.054.124',
'LCZX_BANK.A.054.142',
'RH299.A.1-1.028',
'RH299.A.1-1.047',
'RH299.A.1-1.048',
'RH299.A.1-1.050',
'RH299.A.1-1.057',
'RH299.A.1-1.058',
'RH299.A.1-1.059',
'RH299.A.1-1.061',
'RH299.A.1-1.063',
'RH299.A.1-1.070',
'RH299.A.1-1.071',
'RH299.A.1-2.014',
'RH299.A.1-4.009',
'RH299.A.1-4.014',
'RH299.A.2-3.013',
'RH299.A.2-4.004',
'RH299.A.2-5.004',
'RH299.A.3-1.007',
'RH299.A.3-1.011',
'RH299.A.3-1.017',
'RH299.A.3-2.001',
'RH299_BANK.A.1-1.028',
'RH299_BANK.A.1-1.047',
'RH299_BANK.A.1-1.048',
'RH299_BANK.A.1-1.050',
'RH299_BANK.A.1-1.057',
'RH299_BANK.A.1-1.058',
'RH299_BANK.A.1-1.059',
'RH299_BANK.A.1-1.061',
'RH299_BANK.A.1-1.063',
'RH299_BANK.A.1-1.070',
'RH299_BANK.A.1-1.071',
'RH299_BANK.A.1-2.014',
'RH299_BANK.A.1-4.009',
'RH299_BANK.A.1-4.014',
'RH299_BANK.A.2-3.013',
'RH299_BANK.A.2-4.004',
'RH299_BANK.A.2-5.004',
'RH299_BANK.A.3-1.007',
'RH299_BANK.A.3-1.011',
'RH299_BANK.A.3-1.017',
'RH299_BANK.A.3-2.001'
);
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.004.005', 't_r_lczx_zcxx_zcjy', '“已募集登记”状态的产品才能进行交易登记', '“已募集登记”状态的产品才能进行交易登记', '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_FINPROD_REG_ID\' 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\n from t_r_lczx_zcxx_zcjy t\r\n where T.FILE_DATE = \'@FILE_DATE\'\r\n AND REP_FINPROD_REG_ID NOT IN \r\n (select PROD_REG_CODE FROM t_r_lczx_prod_raise_register_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.004.074', 't_r_lczx_zcxx_zcjy', '当{资金流动类型}在01-08以外时，{资产计量方式}为不可填', '当{资金流动类型}在01-08以外时，{资产计量方式}为不可填', '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_MONEY_FLOW_TYPE,REP_CAL_TYPE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.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_ZCXX_ZCJY T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) not IN\r\n (\'01\', \'02\', \'03\', \'04\', \'05\', \'06\', \'07\', \'08\')\r\nAND T.REP_CAL_TYPE 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.019.046', 't_r_lczx_zcys_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,\r\n \'REP_BASIS\' 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 GET_DICT(\'LCZX_JXJC\',T.REP_BASIS) = 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.021.030', 't_r_lczx_zcys_04', '[资产/负债类别]为“1108 债券买入返售”时，{回购标的类别}必须是“01 债券”', '[资产/负债类别]为“1108 债券买入返售”时，{回购标的类别}必须是“01 债券”', '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_TYPE,REP_ASSET_TYPE\' 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_04 T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND REP_ASSET_TYPE=\'1108\'\r\nAND REP_TARGET_TYPE <>\'01\'\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.100', 't_r_lczx_zcys_05', '当{担保方式}选择(“抵押“,“质押“)时,{抵质押物类型}为必填项；为空或其他担保方式时，不可填', '当{担保方式}选择(“抵押“,“质押“)时,{抵质押物类型}为必填项；为空或其他担保方式时，不可填', '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 \'GUA_TYPE,MORT_PLEDGE_TYPE\' 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 (\r\n(T.GUA_TYPE IN (\'03\',\'04\')AND T.MORT_PLEDGE_TYPE IS NULL)\r\nOR \r\n((T.GUA_TYPE NOT IN (\'03\',\'04\') or T.GUA_TYPE is null) AND T.MORT_PLEDGE_TYPE IS NOT NULL)\r\n)\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.103', 't_r_lczx_zcys_05', '当{担保方式}选择(“抵押“,“质押“)时,{抵质押物价值(元)}为必填项；为空或其他担保方式时，不可填', '当{担保方式}选择(“抵押“,“质押“)时,{抵质押物价值(元)}为必填项；为空或其他担保方式时，不可填', '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 \'GUA_TYPE,MORT_PLEDGE_PRICE\' 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 (\r\n(T.GUA_TYPE IN (\'03\',\'04\')AND T.MORT_PLEDGE_PRICE IS NULL)\r\nOR \r\n((T.GUA_TYPE NOT IN (\'03\',\'04\') or GUA_TYPE is null) AND T.MORT_PLEDGE_PRICE IS NOT NULL)\r\n)\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.105', 't_r_lczx_zcys_05', '当{担保方式}选择(“信用担保“,“第三方保证“)时,{担保性质}为必填项；为空或其他担保方式时，不可填', '当{担保方式}选择(“信用担保“,“第三方保证“)时,{担保性质}为必填项；为空或其他担保方式时，不可填', '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 \'GUA_TYPE,GUA_NATURE\' 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 ((T.GUA_TYPE IN (\'01\',\'02\')\r\nAND T.GUA_NATURE IS NULL)\r\nOR (T.GUA_TYPE NOT IN (\'01\',\'02\') or T.GUA_TYPE is null\r\nAND T.GUA_NATURE 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.022.107', 't_r_lczx_zcys_05', '当{担保方式}选择(“信用担保“,“第三方保证“)时,{担保人与融资人关系}为必填项；为空或其他担保方式时，不可填', '当{担保方式}选择(“信用担保“,“第三方保证“)时,{担保人与融资人关系}为必填项；为空或其他担保方式时，不可填', '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 \'GUA_TYPE,GUAR_FINAN_RELATION\' 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 ((T.GUA_TYPE IN (\'01\',\'02\')\r\nAND T.GUAR_FINAN_RELATION IS NULL)\r\nOR (T.GUA_TYPE NOT IN (\'01\',\'02\') OR T.GUA_TYPE IS NULL \r\nAND T.GUAR_FINAN_RELATION 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.022.122', 't_r_lczx_zcys_05', '当“资产/负债类别”选择“信托贷款”、\"委托贷款\"、“信贷资产转让”、“委托债权”、“应收账款”、“收/受益权”、“带回购条款的股权性融资”、“债权融资类产品”、“同业借款”、“收益凭证”、“债权投资计划和资产支持计划”时，且{含权类型}不为空时，{行权方式}选择\"百慕大期权\"时，{首次行权日期}为必填，{行权方式}选择(\"美式期权\"，\"欧式期权\")时，{首次行权日期}不可填，{含权类型}为空时，{首次行权日期}不可填;当“资产/负债类别”选择“信贷资产流转和收益权转让产品”时，{首次行权日期}不可填。', '当“资产/负债类别”选择“信托贷款”、\"委托贷款\"、“信贷资产转让”、“委托债权”、“应收账款”、“收/受益权”、“带回购条款的股权性融资”、“债权融资类产品”、“同业借款”、“收益凭证”、“债权投资计划和资产支持计划”时，且{含权类型}不为空时，{行权方式}选择\"百慕大期权\"时，{首次行权日期}为必填，{行权方式}选择(\"美式期权\"，\"欧式期权\")时，{首次行权日期}不可填，{含权类型}为空时，{首次行权日期}不可填;当“资产/负债类别”选择“信贷资产流转和收益权转让产品”时，{首次行权日期}不可填。', '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_ASSET_TYPE,RIGHT_TYPE,EXERCISE_MODE,FIRST_EXERCISE_DATE\' 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, 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 ((T.REP_ASSET_TYPE IN\r\n (\'1202\', \'1203\', \'1204\', \'1205\', \'1206\', \'1207\', \'1208\', \'1209\',\r\n \'1211\', \'1212\', \'1213\') AND\r\n (((T.RIGHT_TYPE IS NOT NULL AND\r\n ((T.EXERCISE_MODE = \'03\' AND T.FIRST_EXERCISE_DATE IS NULL) OR\r\n (T.EXERCISE_MODE IN (\'01\', \'02\') AND T.FIRST_EXERCISE_DATE IS NOT NULL)) OR\r\n (T.RIGHT_TYPE IS NULL AND T.EXERCISE_MODE IS NOT NULL)) OR\r\n ((T.RIGHT_TYPE IS NULL AND T.FIRST_EXERCISE_DATE IS NOT NULL))))) OR\r\n (T.REP_ASSET_TYPE = \'2202\' AND T.FIRST_EXERCISE_DATE 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.022.124', 't_r_lczx_zcys_05', '当“资产/负债类别”选择“信托贷款”、\"委托贷款\"、“信贷资产转让”、“委托债权”、“应收账款”、“收/受益权”、“带回购条款的股权性融资”、“债权融资类产品”、“同业借款”、“收益凭证”、“债权投资计划和资产支持计划”时，且{含权类型}不为空时，{行权方式}选择\"百慕大期权\"时，{行权周期}为必填，{行权方式}选择(\"美式期权\"，\"欧式期权\")时，{行权周期}不可填,{含权类型}为空时，{行权周期}不可填;当“资产/负债类别”选择“信贷资产流转和收益权转让产品”时，{行权周期}不可填.', '当“资产/负债类别”选择“信托贷款”、\"委托贷款\"、“信贷资产转让”、“委托债权”、“应收账款”、“收/受益权”、“带回购条款的股权性融资”、“债权融资类产品”、“同业借款”、“收益凭证”、“债权投资计划和资产支持计划”时，且{含权类型}不为空时，{行权方式}选择\"百慕大期权\"时，{行权周期}为必填，{行权方式}选择(\"美式期权\"，\"欧式期权\")时，{行权周期}不可填,{含权类型}为空时，{行权周期}不可填;当“资产/负债类别”选择“信贷资产流转和收益权转让产品”时，{行权周期}不可填.', '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_ASSET_TYPE,RIGHT_TYPE,EXERCISE_MODE,EXERCISE_TERM\' 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, 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 ((T.REP_ASSET_TYPE IN\r\n (\'1202\', \'1203\', \'1204\', \'1205\', \'1206\', \'1207\', \'1208\', \'1209\',\r\n \'1211\', \'1212\', \'1213\') AND\r\n (((T.RIGHT_TYPE IS NOT NULL AND\r\n ((T.EXERCISE_MODE = \'03\' AND T.EXERCISE_TERM IS NULL) OR\r\n (T.EXERCISE_MODE IN (\'01\', \'02\') AND T.EXERCISE_TERM IS NOT NULL)) OR\r\n (T.RIGHT_TYPE IS NULL AND T.EXERCISE_MODE IS NOT NULL)) OR\r\n ((T.RIGHT_TYPE IS NULL AND T.EXERCISE_TERM IS NOT NULL))))) OR\r\n (T.REP_ASSET_TYPE = \'2202\' AND T.EXERCISE_TERM 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.022.142', 't_r_lczx_zcys_05', '当“资产/负债类别”选择“信托贷款”、委托贷款、“信贷资产转让”、“委托债权”、“应收账款”、“收/受益权”、“带回购条款的股权性融资”、“债权融资类产品”、“同业借款”、“收益凭证”、“债权投资计划和资产支持计划”时，{部分赎回比例%}选填，选择“信贷资产流转和收益权转让产品”时，必须为空', '当“资产/负债类别”选择“信托贷款”、委托贷款、“信贷资产转让”、“委托债权”、“应收账款”、“收/受益权”、“带回购条款的股权性融资”、“债权融资类产品”、“同业借款”、“收益凭证”、“债权投资计划和资产支持计划”时，{部分赎回比例%}选填，选择“信贷资产流转和收益权转让产品”时，必须为空', '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_ASSET_TYPE,PART_REDEM_RATIO\' 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 T.REP_ASSET_TYPE = \'2202\' AND T.PART_REDEM_RATIO 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 \'@%\')\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.032.037', 't_r_lczx_underlying_assets', '{对应资管及委外资产行内资产/负债编码}应能匹配数据库中已有的记录', '对应资管及委外资产行内资产/负债编码不存在', '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 \'ASSET_LIABILITY_CODE\' 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 (\r\nselect a.N_ID,a.FILE_DATE,a.FUND_CATEGORY,a.ASSET_LIABILITY_CODE from t_r_lczx_underlying_assets a\r\nleft join t_r_lczx_underlying_assets_result b\r\non a.ASSET_LIABILITY_CODE=b.ASSET_LIABILITY_CODE\r\nwhere a.FILE_DATE = \'@FILE_DATE\'\r\nand b.ASSET_LIABILITY_CODE is null\r\n ) T\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.032.041', 't_r_lczx_underlying_assets', '{底层资产行内资产负债/编码}应能匹配数据库中已有的记录', '底层资产行内资产负债/编码不存在', '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 \'UNDERLYING_ASSET_LIABILITY_CODE\' 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(\r\nselect A.UNDERLYING_ASSET_LIABILITY_CODE,A.N_ID,A.FUND_CATEGORY, A.FILE_DATE FROM t_r_lczx_underlying_assets A\r\nLEFT JOIN t_r_lczx_underlying_assets_result B\r\nON A.UNDERLYING_ASSET_LIABILITY_CODE=B.UNDERLYING_ASSET_LIABILITY_CODE\r\nWHERE A.FILE_DATE =\'@FILE_DATE\'\r\nand B.UNDERLYING_ASSET_LIABILITY_CODE is null\r\n) T\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.104', 't_r_lczx_filing_register', '当“资金投向地区”要素选择“境外”时，{境外托管机构国别}不等于“ CHN ”', '当“资金投向地区”要素选择“境外”时，{境外托管机构国别}不等于“ CHN ”', '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, GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'OVERSEAS_CUSTODIAN_NATIONALITY\' AS COLUMN_CODE, 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE, NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE, NULL AS DIFFERENCE FROM T_R_LCZX_FILING_REGISTER T WHERE T.FILE_DATE = \'@FILE_DATE\' AND INVESTMENT_REGION=\'02\' AND OVERSEAS_CUSTODIAN_NATIONALITY=\'CHN\' 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.033.140', '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\r\n\'@BATCH_ID\' AS BATCH_ID,\r\n\'@RULE_CODE\' AS RULE_CODE,\r\nGROUP_CONCAT( DISTINCT T.N_ID ) AS N_ID_LIST,\r\n\'NAME,PERIOD\' AS COLUMN_CODE,\r\n0 AS RESULT_CODE,\r\n\'@FILE_DATE\' AS FILE_DATE,\r\nFUND_CATEGORY AS INSTI_TYPE,\r\nNULL AS LEFT_CALCULATION_VALUE,\r\nNULL AS RIGHT_CALCULATION_VALUE,\r\nNULL AS DIFFERENCE \r\nFROM(\r\n select FUND_CATEGORY,FILE_DATE,GROUP_CONCAT( DISTINCT N_ID ) as N_ID,NAME,PERIOD from (\r\n select FUND_CATEGORY,FILE_DATE,N_ID,NAME,PERIOD from T_R_LCZX_FILING_REGISTER\r\n where FILE_DATE = \'@FILE_DATE\' \r\n union all\r\n select FUND_CATEGORY,\'@FILE_DATE\' as FILE_DATE, null as N_ID,NAME,PERIOD from t_r_lczx_filing_register_result) a\r\n group by NAME,PERIOD,FUND_CATEGORY\r\n having count(1) >1 ) T\r\n where N_ID is not null\r\n GROUP BY T.FUND_CATEGORY,\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.045.003', '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 \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST,\r\n \'REP_FINPROD_ID\' 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 (\r\n SELECT REP_FINPROD_ID, FILE_DATE, GROUP_CONCAT(DISTINCT N_ID) AS N_ID\r\n FROM T_R_LCZX_ZCYS_ALL T\r\n WHERE T.FILE_DATE = \'@FILE_DATE\' AND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END\r\n GROUP BY REP_FINPROD_ID, FILE_DATE\r\n HAVING COUNT(1) > 1\r\n union all \r\n select a.REP_FINPROD_ID, \'@FILE_DATE\' as FILE_DATE ,a.N_ID from \r\n (select REP_FINPROD_ID, FILE_DATE, N_ID from T_R_LCZX_ZCYS_ALL \r\n where FILE_DATE = \'@FILE_DATE\' AND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END) a\r\n left join t_r_lczx_zcys_all_result b\r\n on a.REP_FINPROD_ID=b.REP_FINPROD_ID\r\n where b.REP_FINPROD_ID is not null)T1\r\n GROUP BY T1.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.045.999', '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 \'@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_ASSET_TYPE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, REP_ISSUE_PARTY_ID 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\'\r\nAND GET_DICT(\'LCZX_ASSET_TYPE\',T.REP_ASSET_TYPE)=0 \r\nAND REP_ASSET_TYPE IS NOT NULL\r\nGROUP BY T.REP_ISSUE_PARTY_ID, 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.046', '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 \'@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_BASIS_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 REP_BASIS_02 IS NOT NULL\r\nAND GET_DICT(\'LCZX_JXJC\',T.REP_BASIS_02) = 0\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.054.100', '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 \'GUA_TYPE_05,MORT_PLEDGE_TYPE_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 ((T.GUA_TYPE_05 IN (\'03\',\'04\') AND T.MORT_PLEDGE_TYPE_05 IS NULL) \r\nOR ((T.GUA_TYPE_05 NOT IN (\'03\',\'04\') or GUA_TYPE_05 is null) AND T.MORT_PLEDGE_TYPE_05 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.054.103', '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 \'GUA_TYPE_05,MORT_PLEDGE_PRICE_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 ((T.GUA_TYPE_05 IN (\'03\',\'04\') AND T.MORT_PLEDGE_PRICE_05 IS NULL) \r\nOR ((T.GUA_TYPE_05 NOT IN (\'03\',\'04\') or GUA_TYPE_05 is null) AND T.MORT_PLEDGE_PRICE_05 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.054.105', '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 \'GUA_TYPE_05,GUA_NATURE_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 ((T.GUA_TYPE_05 IN (\'01\',\'02\') AND T.GUA_NATURE_05 IS NULL) \r\nOR ((T.GUA_TYPE_05 NOT IN (\'01\',\'02\') or GUA_TYPE_05 is null) AND T.GUA_NATURE_05 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.054.107', '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 \'GUA_TYPE_05,GUAR_FINAN_RELATION_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 ((T.GUA_TYPE_05 IN (\'01\',\'02\') AND T.GUAR_FINAN_RELATION_05 IS NULL) \r\nOR ((T.GUA_TYPE_05 NOT IN (\'01\',\'02\') or GUA_TYPE_05 is null) AND T.GUAR_FINAN_RELATION_05 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.054.122', '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 \'REP_ASSET_TYPE,RIGHT_TYPE_05,EXERCISE_MODE_05,FIRST_EXERCISE_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(\'1202\', \'1203\', \'1204\', \'1205\', \'1206\', \'1207\', \'1208\', \'1209\',\'1211\', \'1212\', \'1213\') AND(((T.RIGHT_TYPE_05 IS NOT NULL AND((T.EXERCISE_MODE_05 = \'03\' AND T.FIRST_EXERCISE_DATE_05 IS NULL) OR(T.EXERCISE_MODE_05 IN (\'01\', \'02\') AND T.FIRST_EXERCISE_DATE_05 IS NOT NULL)) OR(T.RIGHT_TYPE_05 IS NULL AND T.EXERCISE_MODE_05 IS NOT NULL)) OR((T.RIGHT_TYPE_05 IS NULL AND T.FIRST_EXERCISE_DATE_05 IS NOT NULL))))) \r\nOR(T.REP_ASSET_TYPE = \'2202\' AND T.FIRST_EXERCISE_DATE_05 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.054.124', '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 \'REP_ASSET_TYPE,RIGHT_TYPE_05,EXERCISE_MODE_05,EXERCISE_TERM_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(\'1202\', \'1203\', \'1204\', \'1205\', \'1206\', \'1207\', \'1208\', \'1209\',\'1211\', \'1212\', \'1213\') AND(((T.RIGHT_TYPE_05 IS NOT NULL AND((T.EXERCISE_MODE_05 = \'03\' AND T.EXERCISE_TERM_05 IS NULL) OR(T.EXERCISE_MODE_05 IN (\'01\', \'02\') AND T.EXERCISE_TERM_05 IS NOT NULL)) OR(T.RIGHT_TYPE_05 IS NULL AND T.EXERCISE_MODE_05 IS NOT NULL)) OR((T.RIGHT_TYPE_05 IS NULL AND T.EXERCISE_TERM_05 IS NOT NULL))))) \r\nOR(T.REP_ASSET_TYPE = \'2202\' AND T.EXERCISE_TERM_05 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.054.142', '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 \'PART_REDEM_RATIO_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 \r\nT.REP_ASSET_TYPE = \'2202\' AND T.PART_REDEM_RATIO_05 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.046', '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 \'C_TRADE_TYPE,C_DESC\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, ISSUER 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 C_TRADE_TYPE=\'01\' \r\nand (C_DESC<>\'客户账户交易\' or C_DESC is 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.004.005', 't_r_lczx_zcxx_zcjy_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 \'REP_FINPROD_REG_ID\' 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\n from t_r_lczx_zcxx_zcjy_bank t\r\n where T.FILE_DATE = \'@FILE_DATE\'\r\n AND REP_FINPROD_REG_ID NOT IN \r\n (select PROD_REG_CODE FROM t_r_lczx_prod_raise_register_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.046', 't_r_lczx_zcys_02_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 \'REP_BASIS\' 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 GET_DICT(\'LCZX_JXJC\',T.REP_BASIS) = 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.021.030', 't_r_lczx_zcys_04_bank', '[资产/负债类别]为“1108 债券买入返售”时，{回购标的类别}必须是“01 债券”\r\n', '[资产/负债类别]为“1108 债券买入返售”时，{回购标的类别}必须是“01 债券”', '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_TYPE，REP_ASSET_TYPE\' 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_04_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND REP_ASSET_TYPE=\'1108\'\r\nAND REP_TARGET_TYPE <>\'01\'\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.100', 't_r_lczx_zcys_05_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 \'GUA_TYPE,MORT_PLEDGE_TYPE\' 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 (\r\n(T.GUA_TYPE IN (\'03\',\'04\')AND T.MORT_PLEDGE_TYPE IS NULL)\r\nOR \r\n((T.GUA_TYPE NOT IN (\'03\',\'04\') or T.GUA_TYPE is null) AND T.MORT_PLEDGE_TYPE IS NOT NULL)\r\n)\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.103', 't_r_lczx_zcys_05_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 \'GUA_TYPE,MORT_PLEDGE_PRICE\' 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 (\r\n(T.GUA_TYPE IN (\'03\',\'04\')AND T.MORT_PLEDGE_PRICE IS NULL)\r\nOR \r\n((T.GUA_TYPE NOT IN (\'03\',\'04\') or GUA_TYPE is null) AND T.MORT_PLEDGE_PRICE IS NOT NULL)\r\n)\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.105', 't_r_lczx_zcys_05_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 \'GUA_TYPE,GUA_NATURE\' 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 ((T.GUA_TYPE IN (\'01\',\'02\')\r\nAND T.GUA_NATURE IS NULL)\r\nOR (T.GUA_TYPE NOT IN (\'01\',\'02\') OR T.GUA_TYPE IS NULL\r\nAND T.GUA_NATURE 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.022.107', 't_r_lczx_zcys_05_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 \'GUA_TYPE,GUAR_FINAN_RELATION\' 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 ((T.GUA_TYPE IN (\'01\',\'02\')\r\nAND T.GUAR_FINAN_RELATION IS NULL)\r\nOR (T.GUA_TYPE NOT IN (\'01\',\'02\') OR T.GUA_TYPE IS NULL \r\nAND T.GUAR_FINAN_RELATION 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.022.122', 't_r_lczx_zcys_05_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 \'REP_ASSET_TYPE,RIGHT_TYPE,EXERCISE_MODE,FIRST_EXERCISE_DATE\' 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, 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 ((T.REP_ASSET_TYPE IN\r\n (\'1202\', \'1203\', \'1204\', \'1205\', \'1206\', \'1207\', \'1208\', \'1209\',\r\n \'1211\', \'1212\', \'1213\') AND\r\n (((T.RIGHT_TYPE IS NOT NULL AND\r\n ((T.EXERCISE_MODE = \'03\' AND T.FIRST_EXERCISE_DATE IS NULL) OR\r\n (T.EXERCISE_MODE IN (\'01\', \'02\') AND T.FIRST_EXERCISE_DATE IS NOT NULL)) OR\r\n (T.RIGHT_TYPE IS NULL AND T.EXERCISE_MODE IS NOT NULL)) OR\r\n ((T.RIGHT_TYPE IS NULL AND T.FIRST_EXERCISE_DATE IS NOT NULL))))) OR\r\n (T.REP_ASSET_TYPE = \'2202\' AND T.FIRST_EXERCISE_DATE 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.022.124', 't_r_lczx_zcys_05_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 \'REP_ASSET_TYPE,RIGHT_TYPE,EXERCISE_MODE,EXERCISE_TERM\' 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, 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 ((T.REP_ASSET_TYPE IN\r\n (\'1202\', \'1203\', \'1204\', \'1205\', \'1206\', \'1207\', \'1208\', \'1209\',\r\n \'1211\', \'1212\', \'1213\') AND\r\n (((T.RIGHT_TYPE IS NOT NULL AND\r\n ((T.EXERCISE_MODE = \'03\' AND T.EXERCISE_TERM IS NULL) OR\r\n (T.EXERCISE_MODE IN (\'01\', \'02\') AND T.EXERCISE_TERM IS NOT NULL)) OR\r\n (T.RIGHT_TYPE IS NULL AND T.EXERCISE_MODE IS NOT NULL)) OR\r\n ((T.RIGHT_TYPE IS NULL AND T.EXERCISE_TERM IS NOT NULL))))) OR\r\n (T.REP_ASSET_TYPE = \'2202\' AND T.EXERCISE_TERM 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.022.142', 't_r_lczx_zcys_05_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 \'REP_ASSET_TYPE,PART_REDEM_RATIO\' 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 T.REP_ASSET_TYPE = \'2202\' AND T.PART_REDEM_RATIO 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 \'@%\')\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.032.037', 't_r_lczx_underlying_assets_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 \'ASSET_LIABILITY_CODE\' 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 (\r\nselect a.N_ID,a.FILE_DATE,a.FUND_CATEGORY,a.ASSET_LIABILITY_CODE from t_r_lczx_underlying_assets_bank a\r\nleft join t_r_lczx_underlying_assets_result b\r\non a.ASSET_LIABILITY_CODE=b.ASSET_LIABILITY_CODE\r\nwhere a.FILE_DATE = \'@FILE_DATE\'\r\nand b.ASSET_LIABILITY_CODE is null\r\n ) T\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.032.041', 't_r_lczx_underlying_assets_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 \'UNDERLYING_ASSET_LIABILITY_CODE\' 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(\r\nselect A.UNDERLYING_ASSET_LIABILITY_CODE,A.N_ID,A.FUND_CATEGORY, A.FILE_DATE FROM t_r_lczx_underlying_assets_bank A\r\nLEFT JOIN t_r_lczx_underlying_assets_result B\r\nON A.UNDERLYING_ASSET_LIABILITY_CODE=B.UNDERLYING_ASSET_LIABILITY_CODE\r\nWHERE A.FILE_DATE =\'@FILE_DATE\'\r\nand B.UNDERLYING_ASSET_LIABILITY_CODE is null\r\n) T\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.045.003', '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 \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST,\r\n \'REP_FINPROD_ID\' 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 (\r\n SELECT REP_FINPROD_ID, FILE_DATE, GROUP_CONCAT(DISTINCT N_ID) AS N_ID\r\n FROM t_r_lczx_zcys_all_bank T\r\n WHERE T.FILE_DATE = \'@FILE_DATE\' AND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END\r\n GROUP BY REP_FINPROD_ID, FILE_DATE\r\n HAVING COUNT(1) > 1\r\n union all \r\n select a.REP_FINPROD_ID, \'@FILE_DATE\' as FILE_DATE ,a.N_ID from \r\n (select REP_FINPROD_ID, FILE_DATE, N_ID from t_r_lczx_zcys_all_bank \r\n where FILE_DATE = \'@FILE_DATE\' AND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END) a\r\n left join t_r_lczx_zcys_all_bank_result b\r\n on a.REP_FINPROD_ID=b.REP_FINPROD_ID\r\n where b.REP_FINPROD_ID is not null)T1\r\n GROUP BY T1.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.045.999', '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 \'@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_ASSET_TYPE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, REP_ISSUE_PARTY_ID 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\'\r\nAND GET_DICT(\'LCZX_ASSET_TYPE\',T.REP_ASSET_TYPE)=0 \r\nAND REP_ASSET_TYPE IS NOT NULL\r\nGROUP BY T.REP_ISSUE_PARTY_ID, 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.046', '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 \'@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_BASIS_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 REP_BASIS_02 IS NOT NULL\r\nAND GET_DICT(\'LCZX_JXJC\',T.REP_BASIS_02) = 0\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.100', '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 \'GUA_TYPE_05,MORT_PLEDGE_TYPE_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'C10307\' 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 ((T.GUA_TYPE_05 IN (\'03\',\'04\') AND T.MORT_PLEDGE_TYPE_05 IS NULL) \r\nOR ((T.GUA_TYPE_05 NOT IN (\'03\',\'04\') or GUA_TYPE_05 is null) AND T.MORT_PLEDGE_TYPE_05 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_BANK.A.054.103', '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 \'GUA_TYPE_05,MORT_PLEDGE_PRICE_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'C10307\' 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 ((T.GUA_TYPE_05 IN (\'03\',\'04\') AND T.MORT_PLEDGE_PRICE_05 IS NULL) \r\nOR ((T.GUA_TYPE_05 NOT IN (\'03\',\'04\') or GUA_TYPE_05 is null) AND T.MORT_PLEDGE_PRICE_05 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_BANK.A.054.122', '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 \'REP_ASSET_TYPE,RIGHT_TYPE_05,EXERCISE_MODE_05,FIRST_EXERCISE_DATE_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'C10307\' 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(\'1202\', \'1203\', \'1204\', \'1205\', \'1206\', \'1207\', \'1208\', \'1209\',\'1211\', \'1212\', \'1213\') AND(((T.RIGHT_TYPE_05 IS NOT NULL AND((T.EXERCISE_MODE_05 = \'03\' AND T.FIRST_EXERCISE_DATE_05 IS NULL) OR(T.EXERCISE_MODE_05 IN (\'01\', \'02\') AND T.FIRST_EXERCISE_DATE_05 IS NOT NULL)) OR(T.RIGHT_TYPE_05 IS NULL AND T.EXERCISE_MODE_05 IS NOT NULL)) OR((T.RIGHT_TYPE_05 IS NULL AND T.FIRST_EXERCISE_DATE_05 IS NOT NULL))))) \r\nOR(T.REP_ASSET_TYPE = \'2202\' AND T.FIRST_EXERCISE_DATE_05 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_BANK.A.054.124', '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 \'REP_ASSET_TYPE,RIGHT_TYPE_05,EXERCISE_MODE_05,EXERCISE_TERM_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'C10307\' 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(\'1202\', \'1203\', \'1204\', \'1205\', \'1206\', \'1207\', \'1208\', \'1209\',\'1211\', \'1212\', \'1213\') AND(((T.RIGHT_TYPE_05 IS NOT NULL AND((T.EXERCISE_MODE_05 = \'03\' AND T.EXERCISE_TERM_05 IS NULL) OR(T.EXERCISE_MODE_05 IN (\'01\', \'02\') AND T.EXERCISE_TERM_05 IS NOT NULL)) OR(T.RIGHT_TYPE_05 IS NULL AND T.EXERCISE_MODE_05 IS NOT NULL)) OR((T.RIGHT_TYPE_05 IS NULL AND T.EXERCISE_TERM_05 IS NOT NULL))))) \r\nOR(T.REP_ASSET_TYPE = \'2202\' AND T.EXERCISE_TERM_05 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_BANK.A.054.142', '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 \'PART_REDEM_RATIO_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'C10307\' 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 = \'2202\' AND T.PART_REDEM_RATIO_05 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 AND (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.COL,T.ITEM_CODE, 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.047', 't_r_299_1_1', '规则1：表1-1，产品品种不是“01”或“08”，公开募集上期无数，本期有数，请核实；', '规则1：表1-1，产品品种不是“01”或“08”，公开募集上期无数，本期有数，请核实；', '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 (SELECT CASE\r\n WHEN SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) = 0\r\n AND SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) > 0 THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID, MAX(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 IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND FUND_TYPE NOT IN (\'01\', \'08\')\r\n AND ITEM_CODE = \'131000\'\r\n AND TABLE_NAME = \'1_1\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) T1\r\nWHERE RESULT_CODE = \'0\'\r\nand N_ID IS NOT NULL\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.048', 't_r_299_1_1', '规则2：表1-1，产品品种为“03”，公开募集数据增长超过8%，请核实；', '规则2：表1-1，产品品种为“03”，公开募集数据增长超过8%，请核实；', '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 (SELECT CASE\r\n WHEN SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))/\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 1.08 THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID, MAX(COL_ORG) AS COL_ORG, T.FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND FUND_TYPE =\'03\'\r\n AND ITEM_CODE = \'131000\'\r\n AND TABLE_NAME = \'1_1\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) T1\r\nWHERE RESULT_CODE = \'0\'\r\nAND N_ID IS NOT NULL\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.050', 't_r_299_1_1', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'1_1\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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.057', 't_r_299_1_1', '规则11：表1-1、表1-2，上期余额+当期募集-当期兑付与当期余额相差较大，请核实；', '规则11：表1-1、表1-2，上期余额+当期募集-当期兑付与当期余额相差较大，请核实；', '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(FDATE = \'@FILE_DATE\'\r\n AND ITEM_CODE LIKE \'1%\'\r\n AND COL = \'G\', V, 0)) > 0\r\n AND ABS(((SUM(IF(FDATE <> \'@FILE_DATE\' AND ITEM_CODE LIKE \'1%\' AND\r\n COL = \'G\', V, 0)) +\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE LIKE \'2%\' AND\r\n COL = \'G\', V, 0)) -\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE LIKE \'2%\' AND\r\n COL = \'N\', V, 0))) /\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE LIKE \'1%\' AND\r\n COL = \'G\', V, 0)) - 1 )) > CASE\r\n WHEN FUND_TYPE = \'01\' THEN\r\n 0.1\r\n WHEN FUND_TYPE = \'08\' THEN\r\n 0.2\r\n ELSE\r\n 0.15\r\n END THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME IN (\'1_1\')\r\n AND FDATE = \'@FILE_DATE\' THEN\r\n T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME IN (\'1_1\', \'1_2\')\r\n AND COL IN (\'G\', \'N\')\r\n GROUP BY T.FUND_CATEGORY, T.FUND_TYPE, SUBSTR(T.ITEM_CODE, 2)) 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.058', 't_r_299_1_1', '规则12：表1-1、表1-2，产品募集余额总计，上期余额+当期募集-当期兑付等于或约等于当期余额，请核实当期兑付是否填报收益；', '规则12：表1-1、表1-2，产品募集余额总计，上期余额+当期募集-当期兑付等于或约等于当期余额，请核实当期兑付是否填报收益；', '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(FDATE = \'@FILE_DATE\'\r\n AND ITEM_CODE = \'100000\'\r\n AND COL = \'G\', T.V, 0)) > 0\r\n AND ABS((SUM(IF(FDATE <> \'@FILE_DATE\' AND\r\n ITEM_CODE = \'100000\' AND COL = \'G\', T.V, 0)) +\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'200000\' AND\r\n COL = \'G\', T.V, 0)) -\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'200000\' AND\r\n COL = \'N\', T.V, 0))) /\r\n SUM(IF(FDATE = \'@FILE_DATE\'\r\n AND ITEM_CODE = \'100000\'\r\n AND COL = \'G\', T.V, 0)) - 1) <\r\n IF(FUND_TYPE = \'01\', 0.0001, 0.001) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME IN (\'1_1\')\r\n AND FDATE = \'@FILE_DATE\' THEN\r\n T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME IN (\'1_1\', \'1_2\')\r\n AND ((ITEM_CODE = \'100000\' AND COL IN (\'G\')) OR\r\n (ITEM_CODE = \'200000\' AND COL IN (\'G\', \'N\')))\r\n GROUP BY 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\') 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-1.061', 't_r_299_1_1', '规则39：表1-1，产品募集余额总计大于表2-1资产合计，但未分配利润无数，请核实', '规则39：表1-1，产品募集余额总计大于表2-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 = \'100000\' \r\n AND COL = \'G\', T.V, 0)) >\r\n SUM(IF(ITEM_CODE = \'700000\'\r\n AND COL = \'G\', T.V, 0)) \r\n AND\r\n SUM(IF(ITEM_CODE = \'823000\' AND COL = \'N\', 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 = \'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\', \'700000\') AND COL = \'G\')\r\n OR (ITEM_CODE IN ( \'823000\') 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-1.063', 't_r_299_1_1', '规则44：表1-1、表2-1，分客户类型募余额与实收本金份额差异较大，请核实；', '规则44：表1-1、表2-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 \r\n ( IFNULL(SUM(IF(ITEM_CODE = \'111000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821110\', \'821210\'), T.V, 0)), 1) * 100 ) BETWEEN ( IF(FUND_TYPE = \'01\', 50, 90) ) AND (IF(FUND_TYPE = \'01\', 110, 200) )THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'111000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821110\', \'821210\') AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n (IFNULL(SUM(IF(ITEM_CODE = \'112000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821120\', \'821220\'), T.V, 0)), 1) * 100) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND (IF(FUND_TYPE = \'01\', 110, 200)) THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'112000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821120\', \'821220\') AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n (IFNULL(SUM(IF(ITEM_CODE = \'113000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821130\', \'821230\'), T.V, 0)), 1) * 100) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND (IF(FUND_TYPE = \'01\', 110, 200)) THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'113000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821130\', \'821230\') AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE \r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n ( IFNULL(SUM(IF(ITEM_CODE = \'114000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN\r\n (\'821140\', \'821150\', \'821160\', \'821240\',\r\n \'821250\', \'821260\'), T.V, 0)), 1) * 100 ) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND\r\n (IF(FUND_TYPE = \'01\', 110, 200)) THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'114000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN\r\n (\'821140\', \'821150\', \'821160\', \'821240\', \'821250\', \'821260\') AND\r\n COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n (IFNULL(SUM(IF(ITEM_CODE = \'115000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821170\', \'821270\'), T.V, 0)), 1) * 100) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND (IF(FUND_TYPE = \'01\', 110, 200) )THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'115000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821170\', \'821270\') AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n ( IFNULL(SUM(IF(ITEM_CODE = \'116000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821180\', \'821280\'), T.V, 0)), 1) * 100) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND ( IF(FUND_TYPE = \'01\', 110, 200)) THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'116000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821180\', \'821280\') 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-1.070', 't_r_299_1_1', '规则48：表1-1、表2-1，只有净值产品，募集资金余额与权益合计相差较大（变动比例3%），请核实。', '规则48：表1-1、表2-1，只有净值产品，募集资金余额与权益合计相差较大（变动比例3%），请核实。', '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 = \'122000\' AND COL = \'G\', T.V, 0)) +\r\n SUM(IF(ITEM_CODE = \'124000\' AND COL = \'G\', T.V, 0)) = 0 AND\r\n SUM(IF(ITEM_CODE = \'820000\' AND COL = \'N\', T.V, 0)) <> 0)\r\n AND ABS(SUM(IF(ITEM_CODE = \'100000\'\r\n AND COL = \'G\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE = \'820000\'\r\n AND COL = \'N\', T.V, 0)) - 1) > 0.03 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 (\'122000\', \'124000\', \'100000\') AND COL = \'G\')\r\n OR (ITEM_CODE = \'820000\' AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY,T.COL,T.ITEM_CODE,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.071', 't_r_299_1_1', '规则49：在无兑付和展期时，表1-1非净值型产品募集余额当期值减去上期值应等于1-2非净值产品当期募集发生额；', '规则49：在无兑付和展期时，表1-1非净值型产品募集余额当期值减去上期值应等于1-2非净值产品当期募集发生额；', '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(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'222000\' AND\r\n COL = \'N\', T.V, 0)) = 0 AND\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'122000\' AND\r\n COL = \'G\', T.V, 0)) >\r\n SUM(IF(FDATE <> \'@FILE_DATE\' AND ITEM_CODE = \'122000\' AND\r\n COL = \'G\', T.V, 0)) +\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'222000\' AND\r\n COL = \'G\', T.V, 0))) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME = \'1_1\'\r\n AND FDATE = \'@FILE_DATE\' THEN\r\n T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME IN (\'1_1\', \'1_2\')\r\n AND ((ITEM_CODE IN (\'222000\') AND COL IN (\'N\', \'G\')) OR\r\n (ITEM_CODE IN (\'122000\') AND COL = \'G\'))\r\n GROUP BY T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN (SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'224000\' AND\r\n COL = \'N\', T.V, 0)) = 0 AND\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'124000\' AND\r\n COL = \'G\', T.V, 0)) >\r\n SUM(IF(FDATE <> \'@FILE_DATE\' AND ITEM_CODE = \'124000\' AND\r\n COL = \'G\', T.V, 0)) +\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'224000\' AND\r\n COL = \'G\', T.V, 0))) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME = \'1_1\'\r\n AND FDATE = \'@FILE_DATE\' THEN\r\n T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME IN (\'1_1\', \'1_2\')\r\n AND ((ITEM_CODE IN (\'224000\') AND COL IN (\'N\', \'G\')) OR\r\n (ITEM_CODE IN (\'124000\') AND COL = \'G\'))\r\n GROUP BY 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-2.014', 't_r_299_1_2', '3.募集方式仅为公开募集，管理模式应只为集合管理；公开募集有数，集合管理必定有数；若该产品为“08-公募基金”，则非公开募集不应有数。', '3.募集方式仅为公开募集，管理模式应只为集合管理；公开募集有数，集合管理必定有数；若该产品为“08-公募基金”，则非公开募集不应有数。', '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 (SELECT CASE\r\n WHEN (SIGN(SUM(IF(ITEM_CODE = \'231000\', T.V, 0))) =\r\n SIGN(SUM(IF(ITEM_CODE = \'241000\', T.V, 0))) )\r\n AND ((FUND_TYPE = \'08\' AND\r\n SIGN(SUM(IF(ITEM_CODE = \'232000\', T.V, 0))) = 0) OR\r\n FUND_TYPE <> \'08\') THEN\r\n 1\r\n ELSE\r\n 0\r\n END AS RESULT_CODE, GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID,\r\n MAX(COL_ORG) AS COL_ORG, T.FDATE AS FILE_DATE,\r\n 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 = \'1_2\'\r\n AND ITEM_CODE IN (\'231000\', \'241000\', \'232000\')\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.COL, 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.009', 't_r_299_1_4', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'1_4\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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 = \'@FDATE\'\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 = \'@FDATE\'\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 = \'@FDATE\'\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 = \'@FDATE\'\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.A.2-4.004', 't_r_299_2_4', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'2_4\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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.2-5.004', 't_r_299_2_5', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'2_5\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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.3-1.007', 't_r_299_3_1', '6.人民币(A)+外币折人民币(B)=合计(C)', '6.人民币(A)+外币折人民币(B)=合计(C)', '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,\r\n GROUP_CONCAT(VALUE1) AS LEFT_CALCULATION_VALUE,\r\n GROUP_CONCAT(VALUE2) AS RIGHT_CALCULATION_VALUE,\r\n GROUP_CONCAT(VALUE3) AS DIFFERENCE\r\nFROM (SELECT CASE\r\n WHEN SUM(IF(COL IN (\'A\', \'B\'), T.V, 0)) =\r\n SUM(IF(COL = \'C\', T.V, 0)) THEN\r\n 1\r\n ELSE\r\n 0\r\n END AS RESULT_CODE, T.FDATE AS FILE_DATE, MAX(T.N_ID) AS N_ID,\r\n GROUP_CONCAT(DISTINCT COL_ORG) AS COL_ORG,\r\n FUND_CATEGORY AS INSTI_TYPE,\r\n CONCAT(MAX(ITEM_NAME), \':\', SUM(IF(COL IN (\'A\', \'B\'), T.V, 0))) AS VALUE1,\r\n CONCAT(MAX(ITEM_NAME), \':\',\r\n SUM(IF(COL = \'C\', T.V, 0))) AS VALUE2,\r\n CONCAT(MAX(ITEM_NAME), \':\',\r\n ABS(SUM(IF(COL IN (\'A\', \'B\'), T.V, 0)) -\r\n SUM(IF(COL = \'C\', T.V, 0)))) AS VALUE3\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE = \'@FILE_DATE\'\r\n AND TABLE_NAME = \'3_1\'\r\n AND COL IN (\'A\', \'B\', \'C\')\r\n AND ITEM_CODE NOT IN(\'e11000\',\'e12000\',\'e13000\',\'e14000\',\'e15000\',\'e16000\',\'e17000\',\'e21000\',\'e22000\', \'e23000\',\'e24000\',\'e25000\',\'e26000\',\'e2000\',\'e21000\',\'e21000\')\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE, T.ITEM_CODE) 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.3-1.011', 't_r_299_3_1', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5 )THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'3_1\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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.3-1.017', 't_r_299_3_1', '46：表3-1，产品品种为“02”，封闭式(非)净值型产品1个月（含）以下、1-3个月（含）有数，请核实；', '46：表3-1，产品品种为“02”，封闭式(非)净值型产品1个月（含）以下、1-3个月（含）有数，请核实；', '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 (SELECT CASE\r\n WHEN SIGN(SUM(IF(ITEM_CODE IN (\'e41000\',\'e42000\',\'e31000\',\'e32000\') AND COL IN (\'A\',\'B\',\'C\'),T.V,0))) <>0 THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n T.FDATE AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT N_ID) AS N_ID, GROUP_CONCAT(DISTINCT COL_ORG ) AS COL_ORG,\r\n 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 (\'3_1\')\r\n AND ITEM_CODE IN (\'e41000\',\'e42000\',\'e31000\',\'e32000\') AND COL IN (\'A\',\'B\',\'C\')\r\n AND FUND_TYPE = \'02\'\r\n GROUP BY T.FDATE, T.FUND_CATEGORY,T.ITEM_CODE,T.col,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.3-2.001', 't_r_299_3_2', '1.产品品种为“08-公募基金”，则运行方式中“开放式非净值型”“封闭式非净值型”不应有数。', '1.产品品种为“08-公募基金”，则运行方式中“开放式非净值型”“封闭式非净值型”不应有数。', '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,\r\n \'\' AS LEFT_CALCULATION_VALUE,\r\n \'\' AS RIGHT_CALCULATION_VALUE,\r\n \'\' AS DIFFERENCE\r\nFROM (SELECT \'0\' AS RESULT_CODE, T.N_ID AS N_ID, COL_ORG AS COL_ORG,\r\n T.FDATE AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE T.FDATE = \'@FILE_DATE\'\r\n AND T.TABLE_NAME = \'3_2\'\r\n AND (T.ITEM_CODE like \'f2%\' or T.ITEM_CODE like \'f4%\')\r\n AND ITEM_CODE NOT IN(\'f21000\',\'f22000\',\'f23000\',\'f24000\',\'f25000\',\'f26000\',\'f27000\',\'f48000\')\r\n AND IFNULL(T.V, 0) <> 0\r\n AND T.FUND_TYPE = \'08\') T1\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.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 AND (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.COL,T.ITEM_CODE, 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.047', 't_r_299_1_1_bank', '规则1：表1-1，产品品种不是“01”或“08”，公开募集上期无数，本期有数，请核实；', '规则1：表1-1，产品品种不是“01”或“08”，公开募集上期无数，本期有数，请核实；', '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 (SELECT CASE\r\n WHEN SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) = 0\r\n AND SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) > 0 THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID, MAX(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 IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND FUND_TYPE NOT IN (\'01\', \'08\')\r\n AND ITEM_CODE = \'131000\'\r\n AND TABLE_NAME = \'1_1\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) T1\r\nWHERE RESULT_CODE = \'0\'\r\nAND N_ID IS NOT NULL\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.048', 't_r_299_1_1_bank', '规则2：表1-1，产品品种为“03”，公开募集数据增长超过8%，请核实；', '规则2：表1-1，产品品种为“03”，公开募集数据增长超过8%，请核实；', '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 (SELECT CASE\r\n WHEN SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))/\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 1.08 THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID, MAX(COL_ORG) AS COL_ORG, T.FUND_CATEGORY AS INSTI_TYPE\r\n FROM t_r_299_report_bank_result T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND FUND_TYPE =\'03\'\r\n AND ITEM_CODE = \'131000\'\r\n AND TABLE_NAME = \'1_1\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) T1\r\nWHERE RESULT_CODE = \'0\'\r\nAND N_ID IS NOT NULL\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.050', 't_r_299_1_1_bank', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'1_1\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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.057', 't_r_299_1_1_bank', '规则11：表1-1、表1-2，上期余额+当期募集-当期兑付与当期余额相差较大，请核实；', '规则11：表1-1、表1-2，上期余额+当期募集-当期兑付与当期余额相差较大，请核实；', '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(FDATE = \'@FILE_DATE\'\r\n AND ITEM_CODE LIKE \'1%\'\r\n AND COL = \'G\', V, 0)) > 0\r\n AND ABS(((SUM(IF(FDATE <> \'@FILE_DATE\' AND ITEM_CODE LIKE \'1%\' AND\r\n COL = \'G\', V, 0)) +\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE LIKE \'2%\' AND\r\n COL = \'G\', V, 0)) -\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE LIKE \'2%\' AND\r\n COL = \'N\', V, 0))) /\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE LIKE \'1%\' AND\r\n COL = \'G\', V, 0)) - 1) )> CASE\r\n WHEN FUND_TYPE = \'01\' THEN\r\n 0.1\r\n WHEN FUND_TYPE = \'08\' THEN\r\n 0.2\r\n ELSE\r\n 0.15\r\n END THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME IN (\'1_1\')\r\n AND FDATE = \'@FILE_DATE\' THEN\r\n T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_BANK_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME IN (\'1_1\', \'1_2\')\r\n AND COL IN (\'G\', \'N\')\r\n GROUP BY T.FUND_CATEGORY, T.FUND_TYPE, SUBSTR(T.ITEM_CODE, 2)) 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.058', 't_r_299_1_1_bank', '规则12：表1-1、表1-2，产品募集余额总计，上期余额+当期募集-当期兑付等于或约等于当期余额，请核实当期兑付是否填报收益；', '规则12：表1-1、表1-2，产品募集余额总计，上期余额+当期募集-当期兑付等于或约等于当期余额，请核实当期兑付是否填报收益；', '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(FDATE = \'@FILE_DATE\'\r\n AND ITEM_CODE = \'100000\'\r\n AND COL = \'G\', T.V, 0)) > 0\r\n AND ABS((SUM(IF(FDATE <> \'@FILE_DATE\' AND\r\n ITEM_CODE = \'100000\' AND COL = \'G\', T.V, 0)) +\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'200000\' AND\r\n COL = \'G\', T.V, 0)) -\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'200000\' AND\r\n COL = \'N\', T.V, 0))) /\r\n SUM(IF(FDATE = \'@FILE_DATE\'\r\n AND ITEM_CODE = \'100000\'\r\n AND COL = \'G\', T.V, 0)) - 1) <\r\n IF(FUND_TYPE = \'01\', 0.0001, 0.001) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME IN (\'1_1\')\r\n AND FDATE = \'@FILE_DATE\' THEN\r\n T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_BANK_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME IN (\'1_1\', \'1_2\')\r\n AND ((ITEM_CODE = \'100000\' AND COL IN (\'G\')) OR\r\n (ITEM_CODE = \'200000\' AND COL IN (\'G\', \'N\')))\r\n GROUP BY 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\') 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-1.061', 't_r_299_1_1_bank', '规则39：表1-1，产品募集余额总计大于表2-1资产合计，但未分配利润无数，请核实', '规则39：表1-1，产品募集余额总计大于表2-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 = \'100000\' \r\n AND COL = \'G\', T.V, 0)) >\r\n SUM(IF(ITEM_CODE = \'700000\'\r\n AND COL = \'G\', T.V, 0)) \r\n AND\r\n SUM(IF(ITEM_CODE = \'823000\' AND COL = \'N\', 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 = \'1_1\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n \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\', \'700000\') AND COL = \'G\')\r\n OR (ITEM_CODE IN ( \'823000\') 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-1.063', 't_r_299_1_1_bank', '规则44：表1-1、表2-1，分客户类型募余额与实收本金份额差异较大，请核实；', '规则44：表1-1、表2-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 \r\n ( IFNULL(SUM(IF(ITEM_CODE = \'111000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821110\', \'821210\'), T.V, 0)), 1) * 100 ) BETWEEN ( IF(FUND_TYPE = \'01\', 50, 90) ) AND (IF(FUND_TYPE = \'01\', 110, 200) )THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'111000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821110\', \'821210\') AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n (IFNULL(SUM(IF(ITEM_CODE = \'112000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821120\', \'821220\'), T.V, 0)), 1) * 100) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND (IF(FUND_TYPE = \'01\', 110, 200)) THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'112000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821120\', \'821220\') AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n (IFNULL(SUM(IF(ITEM_CODE = \'113000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821130\', \'821230\'), T.V, 0)), 1) * 100) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND (IF(FUND_TYPE = \'01\', 110, 200)) THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'113000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821130\', \'821230\') AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE \r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n ( IFNULL(SUM(IF(ITEM_CODE = \'114000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN\r\n (\'821140\', \'821150\', \'821160\', \'821240\',\r\n \'821250\', \'821260\'), T.V, 0)), 1) * 100 ) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND\r\n (IF(FUND_TYPE = \'01\', 110, 200)) THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'114000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN\r\n (\'821140\', \'821150\', \'821160\', \'821240\', \'821250\', \'821260\') AND\r\n COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n (IFNULL(SUM(IF(ITEM_CODE = \'115000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821170\', \'821270\'), T.V, 0)), 1) * 100) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND (IF(FUND_TYPE = \'01\', 110, 200) )THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'115000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821170\', \'821270\') AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN \r\n ( IFNULL(SUM(IF(ITEM_CODE = \'116000\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE IN (\'821180\', \'821280\'), T.V, 0)), 1) * 100) BETWEEN (IF(FUND_TYPE = \'01\', 50, 90)) AND ( IF(FUND_TYPE = \'01\', 110, 200)) THEN\r\n 1\r\n ELSE\r\n 0\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 T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n 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 = \'116000\' AND COL = \'G\') OR\r\n (ITEM_CODE IN (\'821180\', \'821280\') 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-1.070', 't_r_299_1_1_bank', '规则48：表1-1、表2-1，只有净值产品，募集资金余额与权益合计相差较大（变动比例3%），请核实。', '规则48：表1-1、表2-1，只有净值产品，募集资金余额与权益合计相差较大（变动比例3%），请核实。', '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 = \'122000\' AND COL = \'G\', T.V, 0)) +\r\n SUM(IF(ITEM_CODE = \'124000\' AND COL = \'G\', T.V, 0)) = 0 AND\r\n SUM(IF(ITEM_CODE = \'820000\' AND COL = \'N\', T.V, 0)) <> 0)\r\n AND ABS(SUM(IF(ITEM_CODE = \'100000\'\r\n AND COL = \'G\', T.V, 0)) /\r\n SUM(IF(ITEM_CODE = \'820000\'\r\n AND COL = \'N\', T.V, 0)) - 1) > 0.03 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 (\'122000\', \'124000\', \'100000\') AND COL = \'G\')\r\n OR (ITEM_CODE = \'820000\' AND COL = \'N\'))\r\n GROUP BY T.FDATE, T.FUND_CATEGORY,T.COL,T.ITEM_CODE, 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.071', 't_r_299_1_1_bank', '规则49：在无兑付和展期时，表1-1非净值型产品募集余额当期值减去上期值应等于1-2非净值产品当期募集发生额；', '规则49：在无兑付和展期时，表1-1非净值型产品募集余额当期值减去上期值应等于1-2非净值产品当期募集发生额；', '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(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'222000\' AND\r\n COL = \'N\', T.V, 0)) = 0 AND\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'122000\' AND\r\n COL = \'G\', T.V, 0)) >\r\n SUM(IF(FDATE <> \'@FILE_DATE\' AND ITEM_CODE = \'122000\' AND\r\n COL = \'G\', T.V, 0)) +\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'222000\' AND\r\n COL = \'G\', T.V, 0))) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME = \'1_1\'\r\n AND FDATE = \'@FILE_DATE\' THEN\r\n T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_BANK_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME IN (\'1_1\', \'1_2\')\r\n AND ((ITEM_CODE IN (\'222000\') AND COL IN (\'N\', \'G\')) OR\r\n (ITEM_CODE IN (\'122000\') AND COL = \'G\'))\r\n GROUP BY T.FUND_CATEGORY, T.FUND_TYPE\r\n UNION ALL\r\n SELECT CASE\r\n WHEN (SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'224000\' AND\r\n COL = \'N\', T.V, 0)) = 0 AND\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'124000\' AND\r\n COL = \'G\', T.V, 0)) >\r\n SUM(IF(FDATE <> \'@FILE_DATE\' AND ITEM_CODE = \'124000\' AND\r\n COL = \'G\', T.V, 0)) +\r\n SUM(IF(FDATE = \'@FILE_DATE\' AND ITEM_CODE = \'224000\' AND\r\n COL = \'G\', T.V, 0))) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME = \'1_1\'\r\n AND FDATE = \'@FILE_DATE\' THEN\r\n T.N_ID\r\n END) AS N_ID, \'TOTAL\' AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_BANK_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME IN (\'1_1\', \'1_2\')\r\n AND ((ITEM_CODE IN (\'224000\') AND COL IN (\'N\', \'G\')) OR\r\n (ITEM_CODE IN (\'124000\') AND COL = \'G\'))\r\n GROUP BY 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-2.014', 't_r_299_1_2_bank', '3.募集方式仅为公开募集，管理模式应只为集合管理；公开募集有数，集合管理必定有数；若该产品为“08-公募基金”，则非公开募集不应有数。', '3.募集方式仅为公开募集，管理模式应只为集合管理；公开募集有数，集合管理必定有数；若该产品为“08-公募基金”，则非公开募集不应有数。', '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 (SELECT CASE\r\n WHEN (SIGN(SUM(IF(ITEM_CODE = \'231000\', T.V, 0))) =\r\n SIGN(SUM(IF(ITEM_CODE = \'241000\', T.V, 0))) )\r\n AND ((FUND_TYPE = \'08\' AND\r\n SIGN(SUM(IF(ITEM_CODE = \'232000\', T.V, 0))) = 0) OR\r\n FUND_TYPE <> \'08\') THEN\r\n 1\r\n ELSE\r\n 0\r\n END AS RESULT_CODE, GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID,\r\n MAX(COL_ORG) AS COL_ORG, T.FDATE AS FILE_DATE,\r\n 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 = \'1_2\'\r\n AND ITEM_CODE IN (\'231000\', \'241000\', \'232000\')\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.COL, 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.009', 't_r_299_1_4_bank', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM t_r_299_report_bank_result T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'1_4\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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 = \'@FDATE\'\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 = \'@FDATE\'\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 = \'@FDATE\'\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 = \'@FDATE\'\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.2-4.004', 't_r_299_2_4_bank', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_BANK_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'2_4\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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.2-5.004', 't_r_299_2_5_bank', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5) THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_BANK_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'2_5\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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.3-1.007', 't_r_299_3_1_bank', '6.人民币(A)+外币折人民币(B)=合计(C)', '6.人民币(A)+外币折人民币(B)=合计(C)', '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,\r\n GROUP_CONCAT(VALUE1) AS LEFT_CALCULATION_VALUE,\r\n GROUP_CONCAT(VALUE2) AS RIGHT_CALCULATION_VALUE,\r\n GROUP_CONCAT(VALUE3) AS DIFFERENCE\r\nFROM (SELECT CASE\r\n WHEN SUM(IF(COL IN (\'A\', \'B\'), T.V, 0)) =\r\n SUM(IF(COL = \'C\', T.V, 0)) THEN\r\n 1\r\n ELSE\r\n 0\r\n END AS RESULT_CODE, T.FDATE AS FILE_DATE, MAX(T.N_ID) AS N_ID,\r\n GROUP_CONCAT(DISTINCT COL_ORG) AS COL_ORG,\r\n FUND_CATEGORY AS INSTI_TYPE,\r\n CONCAT(MAX(ITEM_NAME), \':\', SUM(IF(COL IN (\'A\', \'B\'), T.V, 0))) AS VALUE1,\r\n CONCAT(MAX(ITEM_NAME), \':\',\r\n SUM(IF(COL = \'C\', T.V, 0))) AS VALUE2,\r\n CONCAT(MAX(ITEM_NAME), \':\',\r\n ABS(SUM(IF(COL IN (\'A\', \'B\'), T.V, 0)) -\r\n SUM(IF(COL = \'C\', T.V, 0)))) AS VALUE3\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE = \'@FILE_DATE\'\r\n AND TABLE_NAME = \'3_1\'\r\n AND COL IN (\'A\', \'B\', \'C\')\r\n AND ITEM_CODE NOT IN(\'e11000\',\'e12000\',\'e13000\',\'e14000\',\'e15000\',\'e16000\',\'e17000\',\'e21000\',\'e22000\', \'e23000\',\'e24000\',\'e25000\',\'e26000\',\'e2000\',\'e21000\',\'e21000\')\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE, T.ITEM_CODE) 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.3-1.011', 't_r_299_3_1_bank', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '规则4：部分表单，跨期变动比例较大(变动绝对值R(100亿<R<=500亿), 跨期变动超过300%，或者环比变动减少50%以上)，请核实；', '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 (SELECT CASE\r\n WHEN ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) > POWER(10, 6)\r\n AND ABS(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) -\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0))) <=\r\n 5 * POWER(10, 6)\r\n AND SIGN(SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0))) =\r\n SIGN(SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)))\r\n AND (SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) > 4\r\n OR SUM(IF(FDATE = \'@FILE_DATE\', T.V, 0)) /\r\n SUM(IF(FDATE <> \'@FILE_DATE\', T.V, 0)) < 0.5 )THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN FDATE = \'@FILE_DATE\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT T.COL_ORG) AS COL_ORG,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE FDATE IN\r\n (\'@FILE_DATE\',\r\n DATE_FORMAT(LAST_DAY(DATE_SUB(\'@FILE_DATE\', INTERVAL 1 MONTH)),\r\n \'%Y%m%d\'))\r\n AND TABLE_NAME = \'3_1\'\r\n GROUP BY T.FUND_CATEGORY, T.COL, T.FUND_TYPE, T.ITEM_CODE) 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.3-1.017', 't_r_299_3_1_bank', '46：表3-1，产品品种为“02”，封闭式(非)净值型产品1个月（含）以下、1-3个月（含）有数，请核实；', '46：表3-1，产品品种为“02”，封闭式(非)净值型产品1个月（含）以下、1-3个月（含）有数，请核实；', '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 (SELECT CASE\r\n WHEN SIGN(SUM(IF(ITEM_CODE IN (\'e41000\',\'e42000\',\'e31000\',\'e32000\') AND COL IN (\'A\',\'B\',\'C\'),T.V,0))) <>0 THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE,\r\n T.FDATE AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT N_ID) AS N_ID, GROUP_CONCAT(DISTINCT COL_ORG ) AS COL_ORG,\r\n 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 (\'3_1\')\r\n AND ITEM_CODE IN (\'e41000\',\'e42000\',\'e31000\',\'e32000\') AND COL IN (\'A\',\'B\',\'C\')\r\n AND FUND_TYPE = \'02\'\r\n GROUP BY T.FDATE, T.FUND_CATEGORY,T.ITEM_CODE,T.col,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.3-2.001', 't_r_299_3_2_bank', '1.产品品种为“08-公募基金”，则运行方式中“开放式非净值型”“封闭式非净值型”不应有数。', '1.产品品种为“08-公募基金”，则运行方式中“开放式非净值型”“封闭式非净值型”不应有数。', '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,\r\n \'\' AS LEFT_CALCULATION_VALUE,\r\n \'\' AS RIGHT_CALCULATION_VALUE,\r\n \'\' AS DIFFERENCE\r\nFROM (SELECT \'0\' AS RESULT_CODE, T.N_ID AS N_ID, COL_ORG AS COL_ORG,\r\n T.FDATE AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE\r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE T.FDATE = \'@FILE_DATE\'\r\n AND T.TABLE_NAME = \'3_2\'\r\n AND (T.ITEM_CODE like \'f2%\' or T.ITEM_CODE like \'f4%\')\r\n AND ITEM_CODE NOT IN(\'f21000\',\'f22000\',\'f23000\',\'f24000\',\'f25000\',\'f26000\',\'f27000\',\'f48000\')\r\n AND IFNULL(T.V, 0) <> 0\r\n AND T.FUND_TYPE = \'08\') T1\r\nGROUP BY T1.FILE_DATE, T1.INSTI_TYPE;');
18001
最新推荐文章于 2024-08-21 21:54:58 发布
809
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
