delete from pawmrpcs.data_check_rule_set where rule_code in
(
'LCZX.A.001.033',
'LCZX.A.001.034',
'LCZX.A.019.046',
'LCZX.A.021.030',
'LCZX.A.023.032',
'LCZX.A.045.999',
'LCZX.A.053.030',
'LCZX_BANK.A.019.046',
'LCZX_BANK.A.021.030',
'LCZX_BANK.A.023.032',
'LCZX_BANK.A.045.999',
'LCZX_BANK.A.051.046',
'LCZX_BANK.A.053.030',
'RH299.A.1-1.014',
'RH299.A.1-1.063',
'RH299.A.1-1.070',
'RH299.A.1-2.036',
'RH299.A.3-1.001',
'RH299_BANK.A.1-1.014',
'RH299_BANK.A.1-1.063',
'RH299_BANK.A.1-1.070',
'RH299_BANK.A.1-2.036',
'RH299_BANK.A.3-1.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.001.033', 't_r_lczx_prod_raise_register', '{币种}=“CNY“时，{认购金额}={折算人民币金额}', '{币种}=“CNY“时，{认购金额}={折算人民币金额}', '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 \'CCY_AMOUNT\' 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 substring_index(substring_index(CCY_AMOUNT,\',\',2),\',\',-1) as rg,\r\n substring_index(substring_index(CCY_AMOUNT,\';\',1),\',\',-1) as zs,\r\n substring_index(substring_index(CCY_AMOUNT,\',\',1),\',\',-1) as bz,\r\n CCY_AMOUNT,FILE_DATE,N_ID,FUND_CATEGORY\r\nfrom t_r_lczx_prod_raise_register\r\nwhere FILE_DATE=\'@FILE_DATE\')T \r\nWHERE BZ=\'CNY\' AND RG<>ZS\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.001.034', 't_r_lczx_prod_raise_register', '{产品销售区域}为必填项，单选和多选的值都必须在值域内', '{产品销售区域}为必填项，单选和多选的值都必须在值域内', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'LOCATION_AND_AMOUNT\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (\r\nSELECT * FROM (\r\nSELECT N_ID, FILE_DATE, FUND_CATEGORY, PROD_REG_CODE,\r\n SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(A.LOCATION_AND_AMOUNT,\';\', B.HELP_TOPIC_ID + 1),\';\',-1),\',\', 1), \' \', 1) AS LOCATION_AND_AMOUNT \r\n FROM T_R_LCZX_PROD_RAISE_REGISTER A\r\n left JOIN pawmrpcs.HELP_TOPIC B\r\n ON B.HELP_TOPIC_ID <\r\n (LENGTH(A.LOCATION_AND_AMOUNT) -\r\n LENGTH(REPLACE(A.LOCATION_AND_AMOUNT, \';\', \'\')) + 1) \r\n) T1\r\nWHERE GET_DICT(\'LCZX_PROD_SALE_REGION\', LOCATION_AND_AMOUNT) = 0 AND FILE_DATE=\'@FILE_DATE\'\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.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\nAND T.REP_BASIS 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.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\nOR REP_TARGET_TYPE IS 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.023.032', 't_r_lczx_zcys_15', '{备注}最长256字符', '{备注}最长256字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'REMARK\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM T_R_LCZX_ZCYS_15 T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND length(CONVERT(REMARK using GBK)) >256\r\nGROUP BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.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\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.053.030', 't_r_lczx_zcys_all', '[资产/负债类别]为“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_04\' 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 T.REP_ASSET_TYPE=\'1108\' \r\nAND ( REP_TARGET_TYPE_04 <>\'01\'\r\nOR REP_TARGET_TYPE_04 IS NULL )\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_04\') 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.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\nAND T.REP_BASIS 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.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\nOR REP_TARGET_TYPE IS 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.023.032', 't_r_lczx_zcys_15', '{备注}最长256字符', '{备注}最长256字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'REMARK\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM T_R_LCZX_ZCYS_15 T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND length(CONVERT(REMARK using GBK)) >256\r\nGROUP BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.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\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_BASIS_02 IS NOT NULL \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.053.030', 't_r_lczx_zcys_all_bank', '[资产/负债类别]为“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_04\' 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 T.REP_ASSET_TYPE=\'1108\' \r\nAND ( REP_TARGET_TYPE_04 <>\'01\'\r\nOR REP_TARGET_TYPE_04 IS NULL )\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_04\') 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 ('RH299.A.1-1.014', 't_r_299_1_1', '2.除产品品种为“01-银行非保本理财”和“08-公募基金”外，“其中：货币市场基金或现金管理类产品”不应有数。', '2.除产品品种为“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 \'0\' AS RESULT_CODE, T.N_ID AS N_ID, T.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 = \'1_1\'\r\n AND T.ITEM_CODE = \'193000\'\r\n AND IFNULL(T.V, 0) <> 0\r\n AND T.FUND_TYPE NOT IN (\'01\', \'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.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 = \'1\'\r\nAND T1.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.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\nAND T1.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-2.036', 't_r_299_1_2', '上期募集余额+本期募集-本期兑付-本期余额>10亿', '上期募集余额+本期募集-本期兑付-本期余额>10亿', '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 (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))) > POWER(10, 5) THEN\r\n \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_2\')\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.3-1.001', 't_r_299_3_1', '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\r\n \'@BATCH_ID\' AS BATCH_ID,\r\n \'@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,\r\n FILE_DATE,\r\n INSTI_TYPE,\r\n \'\' AS LEFT_CALCULATION_VALUE,\r\n \'\' AS RIGHT_CALCULATION_VALUE,\r\n \'\' AS DIFFERENCE \r\nFROM\r\n (\r\nSELECT\r\n \'0\' AS RESULT_CODE,\r\n T.N_ID AS N_ID,\r\n COL_ORG AS COL_ORG,\r\n T.FDATE AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE \r\nFROM\r\n T_R_299_REPORT_RESULT T \r\nWHERE\r\n T.FDATE = \'@FILE_DATE\' \r\n AND T.TABLE_NAME = \'3_1\' \r\n AND T.ITEM_CODE IN (\'e20000\',\'e21000\',\'e22000\',\'e23000\',\'e24000\',\'e25000\',\'e26000\',\'e27000\',\'e28000\', \'e40000\', \'e41000\', \'e42000\', \'e43000\', \'e44000\', \'e45000\', \'e46000\', \'e47000\',\'e48000\') \r\n AND IFNULL( T.V, 0 ) <> 0 \r\n AND T.FUND_TYPE = \'08\' \r\n ) T1 \r\nGROUP BY\r\n T1.FILE_DATE,\r\n 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.014', 't_r_299_1_1_bank', '2.除产品品种为“01-银行非保本理财”和“08-公募基金”外，“其中：货币市场基金或现金管理类产品”不应有数。', '2.除产品品种为“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 \'0\' AS RESULT_CODE, T.N_ID AS N_ID, T.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_BANK_RESULT T\r\n WHERE T.FDATE = \'@FILE_DATE\'\r\n AND T.TABLE_NAME = \'1_1\'\r\n AND T.ITEM_CODE = \'193000\'\r\n AND IFNULL(T.V, 0) <> 0\r\n AND T.FUND_TYPE NOT IN (\'01\', \'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.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 = \'1\'\r\nAND T1.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.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\nAND T1.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-2.036', 't_r_299_1_2_bank', '上期募集余额+本期募集-本期兑付-本期余额>10亿', '上期募集余额+本期募集-本期兑付-本期余额>10亿', '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 (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))) > POWER(10, 5) THEN\r\n \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_2\')\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_BANK.A.3-1.001', 't_r_299_3_1_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\r\n \'@BATCH_ID\' AS BATCH_ID,\r\n \'@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,\r\n FILE_DATE,\r\n INSTI_TYPE,\r\n \'\' AS LEFT_CALCULATION_VALUE,\r\n \'\' AS RIGHT_CALCULATION_VALUE,\r\n \'\' AS DIFFERENCE \r\nFROM\r\n (\r\nSELECT\r\n \'0\' AS RESULT_CODE,\r\n T.N_ID AS N_ID,\r\n COL_ORG AS COL_ORG,\r\n T.FDATE AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE \r\nFROM\r\n T_R_299_REPORT_RESULT T \r\nWHERE\r\n T.FDATE = \'@FILE_DATE\' \r\n AND T.TABLE_NAME = \'3_1\' \r\n AND T.ITEM_CODE IN ( \'e20000\',\'e21000\',\'e22000\',\'e23000\',\'e24000\',\'e25000\',\'e26000\',\'e27000\',\'e28000\', \'e40000\', \'e41000\', \'e42000\', \'e43000\', \'e44000\', \'e45000\', \'e46000\', \'e47000\',\'e48000\') \r\n AND IFNULL( T.V, 0 ) <> 0 \r\n AND T.FUND_TYPE = \'08\' \r\n ) T1 \r\nGROUP BY\r\n T1.FILE_DATE,\r\n T1.INSTI_TYPE');
delete from pawmrpcs.data_check_rule_set where RULE_CODE IN
(
'LCZX.A.002.017',
'LCZX.A.002.022',
'LCZX.A.002.025',
'LCZX.A.002.033',
'LCZX.A.002.038',
'LCZX.A.002.042',
'LCZX.A.002.045',
'LCZX.A.002.047',
'LCZX.A.002.095',
'LCZX.A.004.005',
'LCZX.A.016.031',
'LCZX.A.022.096',
'LCZX.A.023.032',
'LCZX.A.033.104',
'LCZX.A.033.112',
'LCZX.A.034.011',
'LCZX.A.045.999',
'LCZX.A.051.053',
'LCZX.A.054.096',
'LCZX.A.056.015',
'LCZX.A.062.019',
'LCZX_BANK.A.002.017',
'LCZX_BANK.A.002.022',
'LCZX_BANK.A.002.025',
'LCZX_BANK.A.002.033',
'LCZX_BANK.A.002.038',
'LCZX_BANK.A.002.042',
'LCZX_BANK.A.002.045',
'LCZX_BANK.A.002.047',
'LCZX_BANK.A.002.095',
'LCZX_BANK.A.004.005',
'LCZX_BANK.A.016.031',
'LCZX_BANK.A.022.096',
'LCZX_BANK.A.023.032',
'LCZX_BANK.A.045.999',
'LCZX_BANK.A.051.053',
'LCZX_BANK.A.054.096',
'LCZX_BANK.A.056.015',
'LCZX_BANK.A.062.019',
'RH299.A.1-1.028',
'RH299.A.1-1.063',
'RH299.A.1-1.070',
'RH299.A.1-1.071',
'RH299.A.1-2.014',
'RH299_BANK.A.1-1.028',
'RH299_BANK.A.1-1.063',
'RH299_BANK.A.1-1.070',
'RH299_BANK.A.1-1.071',
'RH299_BANK.A.1-2.014'
);
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.002.017', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{初始净值}必填，选择“开放式非净值型”和“封闭式非净值型”的，{初始净值}选填', '对于净值型产品,初始净值不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'REPINITNETVAL\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPINITNETVAL,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPINITNETVAL IS 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.002.022', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{产品净值}必填，选择“开放式非净值型”和“封闭式非净值型”的，{产品净值}选填', '对于净值型产品,产品净值不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'REPPRODNETVAL\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPPRODNETVAL,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPPRODNETVAL IS 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.002.025', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{累计净值}必填，选择“开放式非净值型”和“封闭式非净值型”的，{累计净值}选填', '对于净值型产品,累计净值不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'REPSUMNETVAL\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPSUMNETVAL,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPSUMNETVAL IS 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.002.033', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{折算人民币初始净值}必填，选择“开放式非净值型”和“封闭式非净值型”的，{折算人民币初始净值}选填', '对于净值型产品,折算人民币初始净值不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'REPINITNETVALCNY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPINITNETVALCNY,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPINITNETVALCNY IS 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.002.038', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{折算人民币净值}必填，选择“开放式非净值型”和“封闭式非净值型”的，{折算人民币净值}选填', '对于净值型产品,折算人民币净值不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'REPNETVALCNY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPNETVALCNY,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPNETVALCNY IS 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.002.042', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{折算人民币累计净值}必填，选择“开放式非净值型”和“封闭式非净值型”的，{折算人民币累计净值}选填', '对于净值型产品,折算人民币累计净值不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'REPSUMNETVALCNY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPSUMNETVALCNY,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPSUMNETVALCNY IS 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.002.045', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{净值日期}必填，选择“开放式非净值型”和“封闭式非净值型”的，{净值日期}选填', '对于净值型产品,净值日期不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'VAL_DATE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.VAL_DATE,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND VAL_DATE IS 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.002.047', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{实现收益率%}必填，选择“开放式非净值型”和“封闭式非净值型”的，{实现收益率%}选填', '对于非净值型产品,实现收益率不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'REPACHIYIELD\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPACHIYIELD,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPACHIYIELD IS 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.002.095', 't_r_lczx_prod_duration_register', '{产品运作模式}选择“开放式净值型”和“封闭式净值型”的，{产品余额}必填，选择“开放式非净值型”和“封闭式非净值型”的，{产品余额}选填', '对于非净值型产品,产品余额不能为空。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'REPPRODBALANCE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPPRODBALANCE,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPPRODBALANCE IS 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.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.016.031', 't_r_lczx_zcys_09', '{期限}最长5位整数型', '{期限}最长5位整数型', '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 \'TERM\' 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_zcys_09 T\r\n WHERE T.FILE_DATE = \'@FILE_DATE\'\r\n AND (LENGTH(CONVERT(T.TERM USING \'GBK\'))>5 OR (T.TERM REGEXP \'[^0-9]\') = 1)\r\n GROUP BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.022.096', 't_r_lczx_zcys_05', '{重点监控行业和领域类别说明}最长256位字符', '{重点监控行业和领域类别说明}最长256位字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'MON_TYPE_COMM\' 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 LENGTH(CONVERT(MON_TYPE_COMM USING GBK)) > 256\r\nGROUP BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.023.032', 't_r_lczx_zcys_15', '{备注}最长256字符', '{备注}最长256字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'REMARK\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM T_R_LCZX_ZCYS_15 T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND length(CONVERT(REMARK using GBK)) >256\r\nGROUP BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.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\n \'@BATCH_ID\' AS BATCH_ID,\n \'@RULE_CODE\' AS RULE_CODE,\n GROUP_CONCAT( DISTINCT T.N_ID ) AS N_ID_LIST,\n \'OVERSEAS_CUSTODIAN_NATIONALITY\' AS COLUMN_CODE,\n 0 AS RESULT_CODE,\n \'@FILE_DATE\' AS FILE_DATE,\n FUND_CATEGORY AS INSTI_TYPE,\n NULL AS LEFT_CALCULATION_VALUE,\n NULL AS RIGHT_CALCULATION_VALUE,\n NULL AS DIFFERENCE \nFROM\n T_R_LCZX_FILING_REGISTER T \nWHERE\n T.FILE_DATE = \'@FILE_DATE\' \n AND INVESTMENT_REGION = \'02\' \n AND OVERSEAS_CUSTODIAN_NATIONALITY REGEXP \'CHN\' =1\nGROUP BY\n T.FUND_CATEGORY,\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.033.112', 't_r_lczx_filing_register', '{产品品牌}最长120个字符', '产品品牌过长。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT\n \'@BATCH_ID\' AS BATCH_ID,\n \'@RULE_CODE\' AS RULE_CODE,\n GROUP_CONCAT( DISTINCT T.N_ID ) AS N_ID_LIST,\n \'BRAND\' AS COLUMN_CODE,\n 0 AS RESULT_CODE,\n \'@FILE_DATE\' AS FILE_DATE,\n FUND_CATEGORY AS INSTI_TYPE,\n NULL AS LEFT_CALCULATION_VALUE,\n NULL AS RIGHT_CALCULATION_VALUE,\n NULL AS DIFFERENCE \nFROM\n T_R_LCZX_FILING_REGISTER T \nWHERE FILE_DATE =\'@FILE_DATE\' \nand LENGTH( CONVERT ( T.BRAND USING GBK ) ) > 120 \nGROUP BY\n T.FUND_CATEGORY,\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.034.011', 't_r_lczx_issuance_register', '{募集起始日期}必须落在{申报登记}填报的{募集起始日 (从)}和{募集起始日(到)}的区间内', '{募集起始日期}必须落在{申报登记}填报的{募集起始日 (从)}和{募集起始日(到)}的区间内', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'SUBSCRIPTION_START_DATE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE, T1.N_ID, T1.FUND_CATEGORY,\r\n T1.SUBSCRIPTION_START_DATE, \r\n T3.SUBSCRIPTION_START_DATE_START,T3.SUBSCRIPTION_START_DATE_END\r\n FROM t_r_lczx_issuance_register T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REGISTRATION_CODE\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND (SUBSCRIPTION_START_DATE_START >= SUBSCRIPTION_START_DATE_START \r\nor SUBSCRIPTION_START_DATE_START <=SUBSCRIPTION_START_DATE_END)\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.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 T.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.053', 't_r_lczx_zcys_all', '{结构性存款挂钩标的}最长256个字符', '{结构性存款挂钩标的}最长256个字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'REP_TARGET_02\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, \'Z70033\' AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM T_R_LCZX_ZCYS_ALL T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\' AND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nAND LENGTH(CONVERT(REP_TARGET_02 USING GBK)) > 256\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_02\') GROUP BY T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.054.096', 't_r_lczx_zcys_all', '{重点监控行业和领域类别说明}最长256位字符', '{重点监控行业和领域类别说明}最长256位字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT\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 \'MON_TYPE_COMM_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 LENGTH(CONVERT(MON_TYPE_COMM_05 USING GBK)) > 256\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.056.015', 't_r_lczx_zcys_all', '{基金代码}最长20位字符型', '{基金代码}最长20位字符型', '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_FINPROD_ID_16\' 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 (DEPT_NO=\'\' or DEPT_NO=\'@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_16\'\r\n)\r\nAND LENGTH(CONVERT(REP_FINPROD_ID_16 USING GBK)) >20\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.062.019', 't_r_lczx_zcys_all', '{期限}最长5位整数型', '{期限}最长5位整数型', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2022-03-11 18:19:22', '严重', '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 \'TERM_13\' 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\'\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END\r\nAND (LENGTH(TERM_13)>5 OR (T.TERM_13 REGEXP \'[^0-9]\') = 1)\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP=\'T_R_LCZX_ZCYS_09\')\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.002.017', 't_r_lczx_prod_duration_register_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, \'REPINITNETVAL\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPINITNETVAL,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPINITNETVAL IS 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.002.022', 't_r_lczx_prod_duration_register_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, \'REPPRODNETVAL\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPPRODNETVAL,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPPRODNETVAL IS 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.002.025', 't_r_lczx_prod_duration_register_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, \'REPSUMNETVAL\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPSUMNETVAL,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPSUMNETVAL IS 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.002.033', 't_r_lczx_prod_duration_register_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, \'REPINITNETVALCNY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPINITNETVALCNY,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPINITNETVALCNY IS 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.002.038', 't_r_lczx_prod_duration_register_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, \'REPNETVALCNY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPNETVALCNY,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPNETVALCNY IS 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.002.042', 't_r_lczx_prod_duration_register_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, \'REPSUMNETVALCNY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPSUMNETVALCNY,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPSUMNETVALCNY IS 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.002.045', 't_r_lczx_prod_duration_register_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, \'VAL_DATE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.VAL_DATE,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND VAL_DATE IS 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.002.047', 't_r_lczx_prod_duration_register_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, \'REPACHIYIELD\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPACHIYIELD,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPACHIYIELD IS 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.002.095', 't_r_lczx_prod_duration_register_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, \'REPPRODBALANCE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT T1.FILE_DATE,T1.FUND_CATEGORY,\r\n T1.REPFINPRODREGID,T1.N_ID,T1.REPPRODBALANCE,\r\n T3.OPERATION_MODE\r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n LEFT JOIN pawm_fact_pd_prod_base_info_fd T2\r\n ON T2.PROD_REG_ID = T1.REPFINPRODREGID\r\n LEFT JOIN t_r_lczx_filing_register_result T3\r\n ON T3.PRO_CODE = T2.FINPROD_MARKET_ID ) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND OPERATION_MODE IN(\'03\',\'01\') AND REPPRODBALANCE IS 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.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.016.031', 't_r_lczx_zcys_09_bank', '{期限}最长5位整数型', '{期限}最长5位整数型', '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,\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\n \'TERM\' AS COLUMN_CODE, 0 AS RESULT_CODE,\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\n NULL AS DIFFERENCE\n FROM t_r_lczx_zcys_09_bank T\n WHERE T.FILE_DATE = \'@FILE_DATE\'\n AND (LENGTH(CONVERT(T.TERM USING \'GBK\'))>5 OR (T.TERM REGEXP \'[^0-9]\') = 1)\n GROUP BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.022.096', 't_r_lczx_zcys_05_bank', '{重点监控行业和领域类别说明}最长256位字符', '{重点监控行业和领域类别说明}最长256位字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'MON_TYPE_COMM\' 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 LENGTH(CONVERT(MON_TYPE_COMM USING GBK)) > 256\r\nGROUP BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.023.032', 't_r_lczx_zcys_15', '{备注}最长256字符', '{备注}最长256字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'REMARK\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_zcys_15_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND length(CONVERT(REMARK using GBK)) >256\r\nGROUP BY T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.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 T.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.053', 't_r_lczx_zcys_all_bank', '{结构性存款挂钩标的}最长256个字符', '{结构性存款挂钩标的}最长256个字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'REP_TARGET_02\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, \'Z70033\' AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM T_R_LCZX_ZCYS_ALL T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\' AND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nAND LENGTH(CONVERT(REP_TARGET_02 USING GBK)) > 256\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_02\') GROUP BY T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.054.096', 't_r_lczx_zcys_all_bank', '{重点监控行业和领域类别说明}最长256位字符', '{重点监控行业和领域类别说明}最长256位字符', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT\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 \'MON_TYPE_COMM_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 LENGTH(CONVERT(MON_TYPE_COMM_05 USING GBK)) > 256\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.056.015', 't_r_lczx_zcys_all_bank', '{基金代码}最长20位字符型', '{基金代码}最长20位字符型', '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_FINPROD_ID_16\' 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 (DEPT_NO=\'\' or DEPT_NO=\'@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_16\'\r\n)\r\nAND LENGTH(CONVERT(REP_FINPROD_ID_16 USING GBK)) >20\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.062.019', 't_r_lczx_zcys_all_bank', '{期限}最长5位整数型', '{期限}最长5位整数型', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2022-03-11 18:37:04', '严重', '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 \'TERM_13\' 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\'\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END\r\nAND (LENGTH(TERM_13)>5 OR (T.TERM_13 REGEXP \'[^0-9]\') = 1)\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP=\'T_R_LCZX_ZCYS_09\')\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 ('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.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 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 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 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 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 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 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 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 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 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 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 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 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\nAND T1.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.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.FUND_TYPE) T1\r\nWHERE T1.RESULT_CODE = \'0\'\r\nAND T1.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.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))) ) AND\r\n SIGN(SUM(IF(ITEM_CODE = \'232000\',T.V,0)))=0 AND\r\n SIGN(SUM(IF(ITEM_CODE = \'242000\',T.V,0)))=0) OR\r\n SIGN(SUM(IF(ITEM_CODE = \'232000\',T.V,0)))=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,ITEM_CODE,\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\',\'242000\')\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-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.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 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 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 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 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 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 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 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 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 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 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 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 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\nAND T1.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.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.FUND_TYPE) T1\r\nWHERE T1.RESULT_CODE = \'0\'\r\nAND T1.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.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_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_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))) ) AND\r\n SIGN(SUM(IF(ITEM_CODE = \'232000\',T.V,0)))=0 AND\r\n SIGN(SUM(IF(ITEM_CODE = \'242000\',T.V,0)))=0) OR\r\n SIGN(SUM(IF(ITEM_CODE = \'232000\',T.V,0)))=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,ITEM_CODE,\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\',\'242000\')\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');
18004
最新推荐文章于 2024-11-01 11:28:07 发布
扫一扫
955
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
