DELETE FROM pawmrpcs.data_check_rule_set WHERE RULE_CODE IN(
'LCZX.A.002.006',
'LCZX.A.002.011',
'LCZX.A.002.014',
'LCZX.A.002.039',
'LCZX.A.002.042',
'LCZX.A.002.043',
'LCZX.A.002.045',
'LCZX.A.002.064',
'LCZX.A.002.095',
'LCZX.A.002.103',
'LCZX.A.004.013',
'LCZX.A.004.016',
'LCZX.A.004.026',
'LCZX.A.004.032',
'LCZX.A.004.034',
'LCZX.A.004.074',
'LCZX.A.024.045',
'LCZX.A.032.005',
'LCZX.A.032.023',
'LCZX.A.032.034',
'LCZX.A.032.040',
'LCZX.A.033.091',
'LCZX.A.037.017',
'LCZX.A.054.047',
'LCZX.A.054.081',
'LCZX.A.054.126',
'LCZX.A.054.138',
'LCZX_BANK.A.002.006',
'LCZX_BANK.A.002.011',
'LCZX_BANK.A.002.014',
'LCZX_BANK.A.002.039',
'LCZX_BANK.A.002.042',
'LCZX_BANK.A.002.043',
'LCZX_BANK.A.002.045',
'LCZX_BANK.A.002.064',
'LCZX_BANK.A.002.095',
'LCZX_BANK.A.002.103',
'LCZX_BANK.A.004.013',
'LCZX_BANK.A.004.016',
'LCZX_BANK.A.004.026',
'LCZX_BANK.A.004.032',
'LCZX_BANK.A.004.034',
'LCZX_BANK.A.004.074',
'LCZX_BANK.A.032.005',
'LCZX_BANK.A.032.023',
'LCZX_BANK.A.032.034',
'LCZX_BANK.A.032.040',
'LCZX_BANK.A.037.017',
'LCZX_BANK.A.054.047',
'LCZX_BANK.A.054.081',
'LCZX_BANK.A.054.126',
'LCZX_BANK.A.054.138',
'LCZX_BANK.A.055.015',
'RH299.A.1-1.061',
'RH299_BANK.A.1-1.061'
);
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.006', 't_r_lczx_prod_duration_register', '{发行机构代码}，产品登记编码，{业务结束日}不能有重复记录', '{发行机构代码}，产品登记编码，{业务结束日}不能有重复记录', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPISSUEPARTYID,REPFINPRODREGID,REPBUSMDATE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T1.FUND_CATEGORY AS INSTI_TYPE, NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE, NULL AS DIFFERENCE\r\nFROM (SELECT REPFINPRODREGID, REPISSUEPARTYID, REPBUSMDATE,\r\n N_ID , FUND_CATEGORY, FILE_DATE,COUNT(1)OVER(PARTITION BY T.REPFINPRODREGID, T.REPISSUEPARTYID, T.REPBUSMDATE,\r\n T.FILE_DATE) as number \r\n FROM T_R_LCZX_PROD_DURATION_REGISTER T\r\n ) T1\r\n WHERE FILE_DATE = \'@FILE_DATE\'\r\n and number>1;');
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.011', 't_r_lczx_prod_duration_register', '{产品登记编码}和{业务结束日}这2个要素不可同时重复', '{产品登记编码}和{业务结束日}这2个要素不可同时重复', 'sql', '1', NULL, 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 N_ID as N_ID_LIST,\r\n \'REPFINPRODREGID,REPBUSMDATE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n 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\n (\r\nSELECT\r\n N_ID ,\r\n FUND_CATEGORY,\r\n FILE_DATE,\r\ncount(1)over(PARTITION by T.REPFINPRODREGID,\r\n T.REPBUSMDATE,\r\n FUND_CATEGORY,\r\n FILE_DATE ) as number\r\nFROM\r\n t_r_lczx_prod_duration_register T \r\n ) T \r\n WHERE FILE_DATE=\'@FILE_DATE\'\r\n and number>1;');
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.014', 't_r_lczx_prod_duration_register', '{产品登记编码}与{业务起始日期}值不能同时重复', '{产品登记编码}与{业务起始日期}值不能同时重复', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPFINPRODREGID,REPBUSVDATE\' 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 N_ID ,FUND_CATEGORY,FILE_DATE,count(1)over(partition by T.REPFINPRODREGID,T.REPBUSVDATE,FUND_CATEGORY,FILE_DATE) as number \r\nFROM t_r_lczx_prod_duration_register T\r\n)T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nand number >1;');
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.039', 't_r_lczx_prod_duration_register', '{币种}=“CNY“时，{产品净值}={折算人民币净值}', '当产品净值的币种为人民币(CNY)时，折算人民币净值与产品净值必须相等。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPNETVALCCY,REPPRODNETVAL,REPNETVALCNY\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n REPSUMNETVAL AS LEFT_CALCULATION_VALUE, REPSUMNETVALCNY AS RIGHT_CALCULATION_VALUE,\r\n ABS(REPSUMNETVAL-REPSUMNETVALCNY) AS DIFFERENCE\r\nFROM T_R_LCZX_PROD_DURATION_REGISTER T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND REPNETVALCCY=\'CNY\'\r\nAND REPPRODNETVAL<>REPNETVALCNY;');
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, 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 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;');
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.043', 't_r_lczx_prod_duration_register', '{币种}=“CNY“时，{累计净值}={折算人民币累计净值}', '当累计净值的币种为人民币(CNY)时，折算人民币累计净值与累计净值必须相等。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPNETVALCCY,REPSUMNETVAL,REPSUMNETVALCNY\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n REPSUMNETVAL AS LEFT_CALCULATION_VALUE, REPSUMNETVALCNY AS RIGHT_CALCULATION_VALUE,\r\n ABS(REPSUMNETVAL-REPSUMNETVALCNY) AS DIFFERENCE\r\nFROM T_R_LCZX_PROD_DURATION_REGISTER T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND REPNETVALCCY=\'CNY\'\r\nAND REPSUMNETVAL<>REPSUMNETVALCNY;');
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, 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 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;');
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.064', 't_r_lczx_prod_duration_register', '存续期登记：同一只产品存续期登记时，业务结束日不得重复', '产品登记编码和业务结束日在报文内不可同时重复。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPFINPRODREGID,REPBUSMDATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT REPFINPRODREGID, REPBUSMDATE, N_ID ,\r\n FUND_CATEGORY, FILE_DATE,count(1)over(PARTITION by T1.REPFINPRODREGID, T1.REPBUSMDATE, T1.FILE_DATE) as number \r\n FROM T_R_LCZX_PROD_DURATION_REGISTER T1\r\n ) T\r\n WHERE T.FILE_DATE = \'@FILE_DATE\'\r\n and number>1;');
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, 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 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;');
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.103', 't_r_lczx_prod_duration_register', '同一个产品不同的记录之间，“产品登记编码”与“业务结束日”两个要素不得同时重复', '该发行机构下已存在该产品的存续期信息。', 'sql', '1', NULL, 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\nN_ID as N_ID_LIST,\r\n\'REPFINPRODREGID,REPBUSMDATE\' AS COLUMN_CODE,\r\n0 AS RESULT_CODE,\r\n\'@FILE_DATE\' AS FILE_DATE,\r\nFUND_CATEGORY AS INSTI_TYPE,\r\nNULL AS LEFT_CALCULATION_VALUE,\r\nNULL AS RIGHT_CALCULATION_VALUE,\r\nNULL AS DIFFERENCE \r\nFROM(\r\n select N_ID ,FUND_CATEGORY,FILE_DATE,REPFINPRODREGID,REPBUSMDATE, \r\n count(1)over(PARTITION by REPFINPRODREGID,REPBUSMDATE ) as number from T_R_LCZX_PROD_DURATION_REGISTER\r\n ) T\r\n where T.FILE_DATE = \'@FILE_DATE\'\r\n and number>1;');
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.013', 't_r_lczx_zcxx_zcjy', '当{资金流动类型}在11-12时，{行内交易编码}、{资金流动类型}唯一', '当{资金流动类型}在11-12时，{行内交易编码}、{资金流动类型}唯一', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T1.N_ID AS N_ID_LIST,\r\n \'REP_BANK_TRADE_CODE,REP_MONEY_FLOW_TYPE\'AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T1.FUND_CATEGORY AS INSTI_TYPE, NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE, NULL AS DIFFERENCE FROM\r\n(SELECT COUNT(1) OVER (PARTITION BY T.FILE_DATE, T.REP_BANK_TRADE_CODE,REP_MONEY_FLOW_TYPE, FUND_CATEGORY) AS NUM,\r\nN_ID,FUND_CATEGORY,FILE_DATE FROM T_R_LCZX_ZCXX_ZCJY T\r\nWHERE NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) IN\r\n (\'11\', \'12\')\r\n ) T1\r\n WHERE T1.FILE_DATE = \'@FILE_DATE\'\r\n and NUM >1;');
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.016', 't_r_lczx_zcxx_zcjy', '当{资金流动类型}在01-10时，{行内资产/负债编码}为必填项', '当{资金流动类型}在01-10时，{行内资产/负债编码}为必填项', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST,\r\n \'REP_MONEY_FLOW_TYPE,REP_BANK_ASSET_CODE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM T_R_LCZX_ZCXX_ZCJY T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) IN\r\n (\'01\', \'02\', \'03\', \'04\', \'05\', \'06\', \'07\', \'08\', \'09\', \'10\')\r\nAND T.REP_BANK_ASSET_CODE IS NULL;');
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.026', 't_r_lczx_zcxx_zcjy', '当{币种}为“CNY”时，{折算人民币金额（元）}={发生金额}', '当{币种}为“CNY”时，{折算人民币金额（元）}={发生金额}', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST,\r\n \'REP_MONEY_CNY,REP_OCCUR_MONEY\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n REP_MONEY_CNY AS LEFT_CALCULATION_VALUE, REP_OCCUR_MONEY AS RIGHT_CALCULATION_VALUE,\r\n ABS(REP_MONEY_CNY-REP_OCCUR_MONEY) AS DIFFERENCE\r\nFROM T_R_LCZX_ZCXX_ZCJY T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.REP_CCY =\'CNY\'\r\nAND IFNULL(REP_OCCUR_MONEY,0) <> IFNULL(REP_MONEY_CNY,0);');
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.032', 't_r_lczx_zcxx_zcjy', '当{资金流动类型}为（03、04、05、06、07、08）时，{数量}={折算人民币金额（元）}', '当{资金流动类型}为（03、04、05、06、07、08）时，{数量}={折算人民币金额（元）}', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST, \'REP_COUNT,REP_MONEY_CNY,REP_MONEY_FLOW_TYPE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n IFNULL(T.REP_COUNT, 0) AS LEFT_CALCULATION_VALUE,\r\n IFNULL(T.REP_MONEY_CNY, 0) AS RIGHT_CALCULATION_VALUE,\r\n ABS(IFNULL(T.REP_COUNT, 0 - IFNULL(T.REP_MONEY_CNY, 0))) AS DIFFERENCE\r\nFROM T_R_LCZX_ZCXX_ZCJY T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) IN\r\n (\'03\', \'04\', \'05\', \'06\', \'07\', \'08\')\r\nAND IFNULL(T.REP_COUNT, 0) <> IFNULL(T.REP_MONEY_CNY, 0);');
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.034', 't_r_lczx_zcxx_zcjy', '当{资金流动类型}在01-08时，{资产计量方式}为必填项', '当{资金流动类型}在01-08时，{资产计量方式}为必填项', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST, \'REP_CAL_TYPE,REP_MONEY_FLOW_TYPE\' 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 T_R_LCZX_ZCXX_ZCJY T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.REP_CAL_TYPE IS NULL AND T.REP_MONEY_FLOW_TYPE IN (\'01\',\'02\',\'03\',\'04\',\'05\',\'06\',\'07\',\'08\');');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.004.074', 't_r_lczx_zcxx_zcjy', '当{资金流动类型}在01-08以外时，{资产计量方式}为不可填', '当{资金流动类型}在01-08以外时，{资产计量方式}为不可填', 'sql', '1', NULL, NULL, NULL, 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 T.N_ID AS N_ID_LIST,\r\n \'REP_MONEY_FLOW_TYPE,REP_CAL_TYPE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM T_R_LCZX_ZCXX_ZCJY T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) not IN\r\n (\'01\', \'02\', \'03\', \'04\', \'05\', \'06\', \'07\', \'08\')\r\nAND T.REP_CAL_TYPE IS NOT NULL;');
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.024.045', 't_r_lczx_zcys_16', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 其中比例部分格式为数字+“%”，如10%；或者数字+“%”+“-”+数字+“%”组成，如10%-20%。', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 其中比例部分格式为数字+“%”，如10%；或者数字+“%”+“-”+数字+“%”组成，如10%-20%。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'REP_FUND_INVEST_ASSET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FUND_CATEGORY,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET\r\nfrom T_R_LCZX_ZCYS_16 a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET)-length(replace(REP_FUND_INVEST_ASSET,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE =\'@FILE_DATE\' \r\nAND (\r\n(REP_FUND_INVEST_ASSET not LIKE \'%%:%\' \r\nand REP_FUND_INVEST_ASSET not LIKE \'%%-%%:%\')\r\nor (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=1 \r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))>1\r\n))\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=1\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))>1\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1),\'.\',\'\'))>1\r\n))\r\n))\r\ngroup by T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.032.005', 't_r_lczx_underlying_assets', '{对应资管及委外资产行内资产/负债编码}长度不能超过40位', '{对应资管及委外资产行内资产/负债编码}长度不能超过40位', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_underlying_assets T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND length(convert (ASSET_LIABILITY_CODE using GBK))>40\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.032.023', 't_r_lczx_underlying_assets', '{底层资产行内资产/负债编码}长度不超过40位', '{底层资产行内资产/负债编码}长度不超过40位', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'UNDERLYING_ASSET_LIABILITY_CODE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_underlying_assets T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND length(convert (UNDERLYING_ASSET_LIABILITY_CODE using GBK))>40\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.032.034', 't_r_lczx_underlying_assets', '{持仓日期}日期类型，八位，格式为yyyymmdd', '{持仓日期}日期类型，八位，格式为yyyymmdd', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPORT_DATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_underlying_assets T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND IS_DATE_TYPE(REPORT_DATE,1)=0 \r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.032.040', 't_r_lczx_underlying_assets', '{对应资管及委外资产行内资产/负债编码}、{底层资产行内资产/负债编码}、{持仓日期}三个要素在不同记录间不得重复,不得重复登记', '对应资管及委外资产行内资产/负债编码,底层资产行内资产负债/编码和持仓日期记录在系统中重复', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,UNDERLYING_ASSET_LIABILITY_CODE,REPORT_DATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM(\r\nselect N_ID ,FILE_DATE,FUND_CATEGORY,DEPT_NO,count(1)over(PARTITION by ASSET_LIABILITY_CODE,UNDERLYING_ASSET_LIABILITY_CODE,REPORT_DATE,FUND_CATEGORY) as number from (\r\nselect DEPT_NO,ASSET_LIABILITY_CODE,UNDERLYING_ASSET_LIABILITY_CODE,REPORT_DATE, N_ID ,FILE_DATE,FUND_CATEGORY from t_r_lczx_underlying_assets\r\nunion all \r\nselect DEPT_NO,ASSET_LIABILITY_CODE,UNDERLYING_ASSET_LIABILITY_CODE,REPORT_DATE, null as N_ID , \'@FILE_DATE\' as FILE_DATE,FUND_CATEGORY from t_r_lczx_underlying_assets_result) a\r\n) T\r\nwhere FILE_DATE = \'@FILE_DATE\'\r\nand N_ID IS NOT NULL\r\nAND number>1 \r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
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.091', 't_r_lczx_filing_register', '产品募集方式选择“公募”，募集起始日期（从）应晚于申报登记日至少 10 个工作日，产品募集方式选择“私募”，募集起始日期（从）应晚于申报登记日至少 2 个工作日；', '产品募集方式选择“公募”，募集起始日期（从）应晚于申报登记日至少 10 个工作日，产品募集方式选择“私募”，募集起始日期（从）应晚于申报登记日至少 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,\r\n \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT( DISTINCT T.N_ID ) AS N_ID_LIST,\r\n \'SUBSCRIPTION_TYPE,SUBSCRIPTION_START_DATE_START\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE FROM (\r\nselect n_id,file_date,SUBSCRIPTION_TYPE,SUBSCRIPTION_START_DATE_START,b.HOLIDAY,b.flag,sum(b.flag)over(partition by n_id ) as days,FUND_CATEGORY\r\nfrom T_R_LCZX_FILING_REGISTER a\r\njoin (select DATE_FORMAT( SUBSTR( HOLIDAY, 1, 10 ), \'%Y%m%d\' ) as HOLIDAY,DV_HOLIDAY_TYPE,\r\n if(IS_HOLIDAY=1,0,1) as flag\r\nfrom t_s_bf_holiday \r\nwhere DV_HOLIDAY_TYPE = \'WORKDAY\') b\r\non (a.file_date<=b.HOLIDAY and a.SUBSCRIPTION_START_DATE_START>=b.HOLIDAY)\r\nand b.DV_HOLIDAY_TYPE = \'WORKDAY\'\r\nwhere file_date=\'@FILE_DATE\') T\r\nWHERE ((SUBSCRIPTION_TYPE =\'01\' and days < 10) or (SUBSCRIPTION_TYPE =\'02\' and days < 2))\r\nGROUP BY\r\n 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.037.017', 't_r_manager_direct', '{借款人类型}字段类型为1位整数型,只能填1或者2', '{借款人类型}字段类型为1位整数型,只能填1或者2', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2022-03-14 16:59:17', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'LOANER_TYPE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_manager_direct T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\' \r\nAND LOANER_TYPE NOT IN (\'1\',\'2\') \r\nGROUP BY T.FILE_DATE, T.FUND_CATEGORY');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.054.047', 't_r_lczx_zcys_all', '{付息频率}最长为2位整数型', '{付息频率}最长为2位整数型', 'sql', '1', NULL, 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 \'INTPAY_CYCLE_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'Z70033\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n T_R_LCZX_ZCYS_ALL T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\r\n SELECT\r\n ITEM_KEY\r\n FROM\r\n T_P_LCZX_ZCYS_REPORT\r\n WHERE\r\n ITEM_GROUP = \'T_R_LCZX_ZCYS_05\')\r\nand (LENGTH(ABS(T.INTPAY_CYCLE_05))>2 OR (ABS(T.INTPAY_CYCLE_05 ) REGEXP \'[^0-9]\') = 1)\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.054.081', 't_r_lczx_zcys_all', '{融资人类型（按技术领域划分）}为必填项，值必须在值域以内', '{融资人类型（按技术领域划分）}为必填项，值必须在值域以内', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'FINAN_TECH_FIELD_05\' 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 ( FINAN_TECH_FIELD_05 IS NULL OR FINAN_TECH_FIELD_05 NOT IN (\'01\',\'99\'))\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_05\') \r\nGROUP BY T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.054.126', 't_r_lczx_zcys_all', '{行权周期}最长5位整数型', '{行权周期}最长5位整数型', 'sql', '1', NULL, 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 \'EXERCISE_TERM_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'Z70033\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n T_R_LCZX_ZCYS_ALL T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\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(ABS(T.EXERCISE_TERM_05))>5 OR (ABS(T.EXERCISE_TERM_05 ) REGEXP \'[^0-9]\') = 1)\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.054.138', 't_r_lczx_zcys_all', '{重定价周期}最长为5为整数型', '{重定价周期}最长为5为整数型', 'sql', '1', NULL, 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 \'REPRICING_TERM_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'Z70033\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n T_R_LCZX_ZCYS_ALL T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\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(ABS (T.REPRICING_TERM_05))>5 OR (ABS(T.REPRICING_TERM_05 ) REGEXP \'[^0-9]\') = 1)\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.002.006', 't_r_lczx_prod_duration_register_bank', '{发行机构代码}，产品登记编码，{业务结束日}不能有重复记录', '{发行机构代码}，产品登记编码，{业务结束日}不能有重复记录', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPISSUEPARTYID,REPFINPRODREGID,REPBUSMDATE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T1.FUND_CATEGORY AS INSTI_TYPE, NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE, NULL AS DIFFERENCE\r\nFROM (SELECT REPFINPRODREGID, REPISSUEPARTYID, REPBUSMDATE,\r\n N_ID , FUND_CATEGORY, FILE_DATE,COUNT(1)OVER(PARTITION BY T.REPFINPRODREGID, T.REPISSUEPARTYID, T.REPBUSMDATE,\r\n T.FILE_DATE) as number \r\n FROM t_r_lczx_prod_duration_register_bank T\r\n ) T1\r\n WHERE FILE_DATE = \'@FILE_DATE\'\r\n and number>1;');
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.011', 't_r_lczx_prod_duration_register_bank', '{产品登记编码}和{业务结束日}这2个要素不可同时重复', '{产品登记编码}和{业务结束日}这2个要素不可同时重复', 'sql', '1', NULL, 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 N_ID as N_ID_LIST,\r\n \'REPFINPRODREGID,REPBUSMDATE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n 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\n (\r\nSELECT\r\n N_ID ,\r\n FUND_CATEGORY,\r\n FILE_DATE,\r\ncount(1)over(PARTITION by T.REPFINPRODREGID,\r\n T.REPBUSMDATE,\r\n FUND_CATEGORY,\r\n FILE_DATE ) as number\r\nFROM\r\n t_r_lczx_prod_duration_register_bank T \r\n ) T \r\n WHERE FILE_DATE=\'@FILE_DATE\'\r\n and number>1;');
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.014', 't_r_lczx_prod_duration_register_bank', '{产品登记编码}与{业务起始日期}值不能同时重复', '{产品登记编码}与{业务起始日期}值不能同时重复', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPFINPRODREGID,REPBUSVDATE\' 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 N_ID ,FUND_CATEGORY,FILE_DATE,count(1)over(partition by T.REPFINPRODREGID,T.REPBUSVDATE,FUND_CATEGORY,FILE_DATE) as number \r\nFROM t_r_lczx_prod_duration_register_bank T\r\n)T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nand number >1;');
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.039', 't_r_lczx_prod_duration_register_bank', '{币种}=“CNY“时，{产品净值}={折算人民币净值}', '当产品净值的币种为人民币(CNY)时，折算人民币净值与产品净值必须相等。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPNETVALCCY,REPPRODNETVAL,REPNETVALCNY\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n REPSUMNETVAL AS LEFT_CALCULATION_VALUE, REPSUMNETVALCNY AS RIGHT_CALCULATION_VALUE,\r\n ABS(REPSUMNETVAL-REPSUMNETVALCNY) AS DIFFERENCE\r\nFROM t_r_lczx_prod_duration_register_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND REPNETVALCCY=\'CNY\'\r\nAND REPPRODNETVAL<>REPNETVALCNY;');
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, 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 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;');
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.043', 't_r_lczx_prod_duration_register_bank', '{币种}=“CNY“时，{累计净值}={折算人民币累计净值}', '当累计净值的币种为人民币(CNY)时，折算人民币累计净值与累计净值必须相等。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPNETVALCCY,REPSUMNETVAL,REPSUMNETVALCNY\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n REPSUMNETVAL AS LEFT_CALCULATION_VALUE, REPSUMNETVALCNY AS RIGHT_CALCULATION_VALUE,\r\n ABS(REPSUMNETVAL-REPSUMNETVALCNY) AS DIFFERENCE\r\nFROM t_r_lczx_prod_duration_register_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND REPNETVALCCY=\'CNY\'\r\nAND REPSUMNETVAL<>REPSUMNETVALCNY;');
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, 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 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;');
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.064', 't_r_lczx_prod_duration_register_bank', '存续期登记：同一只产品存续期登记时，业务结束日不得重复', '产品登记编码和业务结束日在报文内不可同时重复。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPFINPRODREGID,REPBUSMDATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT REPFINPRODREGID, REPBUSMDATE, N_ID ,\r\n FUND_CATEGORY, FILE_DATE,count(1)over(PARTITION by T1.REPFINPRODREGID, T1.REPBUSMDATE, T1.FILE_DATE) as number \r\n FROM t_r_lczx_prod_duration_register_bank T1\r\n ) T\r\n WHERE T.FILE_DATE = \'@FILE_DATE\'\r\n and number>1;');
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, 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 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;');
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.103', 't_r_lczx_prod_duration_register_bank', '同一个产品不同的记录之间，“产品登记编码”与“业务结束日”两个要素不得同时重复', '该发行机构下已存在该产品的存续期信息。', 'sql', '1', NULL, 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\nN_ID as N_ID_LIST,\r\n\'REPFINPRODREGID,REPBUSMDATE\' AS COLUMN_CODE,\r\n0 AS RESULT_CODE,\r\n\'@FILE_DATE\' AS FILE_DATE,\r\nFUND_CATEGORY AS INSTI_TYPE,\r\nNULL AS LEFT_CALCULATION_VALUE,\r\nNULL AS RIGHT_CALCULATION_VALUE,\r\nNULL AS DIFFERENCE \r\nFROM(\r\n select N_ID ,FUND_CATEGORY,FILE_DATE,REPFINPRODREGID,REPBUSMDATE, \r\n count(1)over(PARTITION by REPFINPRODREGID,REPBUSMDATE ) as number from t_r_lczx_prod_duration_register_bank\r\n ) T\r\n where T.FILE_DATE = \'@FILE_DATE\'\r\n and number>1;');
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.013', 't_r_lczx_zcxx_zcjy_bank', '当{资金流动类型}在11-12时，{行内交易编码}、{资金流动类型}唯一', '当{资金流动类型}在11-12时，{行内交易编码}、{资金流动类型}唯一', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T1.N_ID AS N_ID_LIST,\r\n \'REP_BANK_TRADE_CODE,REP_MONEY_FLOW_TYPE\'AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T1.FUND_CATEGORY AS INSTI_TYPE, NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE, NULL AS DIFFERENCE FROM\r\n(SELECT COUNT(1) OVER (PARTITION BY T.FILE_DATE, T.REP_BANK_TRADE_CODE,REP_MONEY_FLOW_TYPE, FUND_CATEGORY) AS NUM,\r\nN_ID,FUND_CATEGORY,FILE_DATE FROM t_r_lczx_zcxx_zcjy_bank T\r\nWHERE NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) IN\r\n (\'11\', \'12\')\r\n ) T1\r\n WHERE T1.FILE_DATE = \'@FILE_DATE\'\r\n and NUM >1;');
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.016', 't_r_lczx_zcxx_zcjy_bank', '当{资金流动类型}在01-10时，{行内资产/负债编码}为必填项', '当{资金流动类型}在01-10时，{行内资产/负债编码}为必填项', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST,\r\n \'REP_MONEY_FLOW_TYPE,REP_BANK_ASSET_CODE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_zcxx_zcjy_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) IN\r\n (\'01\', \'02\', \'03\', \'04\', \'05\', \'06\', \'07\', \'08\', \'09\', \'10\')\r\nAND T.REP_BANK_ASSET_CODE IS NULL;');
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.026', 't_r_lczx_zcxx_zcjy_bank', '当{币种}为“CNY”时，{折算人民币金额（元）}={发生金额}', '当{币种}为“CNY”时，{折算人民币金额（元）}={发生金额}', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST,\r\n \'REP_MONEY_CNY，REP_OCCUR_MONEY\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n REP_MONEY_CNY AS LEFT_CALCULATION_VALUE, REP_OCCUR_MONEY AS RIGHT_CALCULATION_VALUE,\r\n ABS(REP_MONEY_CNY-REP_OCCUR_MONEY) AS DIFFERENCE\r\nFROM t_r_lczx_zcxx_zcjy_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.REP_CCY =\'CNY\'\r\nAND IFNULL(REP_OCCUR_MONEY,0) <> IFNULL(REP_MONEY_CNY,0);');
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.032', 't_r_lczx_zcxx_zcjy_bank', '当{资金流动类型}为（03、04、05、06、07、08）时，{数量}={折算人民币金额（元）}', '当{资金流动类型}为（03、04、05、06、07、08）时，{数量}={折算人民币金额（元）}', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST, \'REP_COUNT,REP_MONEY_CNY,REP_MONEY_FLOW_TYPE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE,\r\n IFNULL(T.REP_COUNT, 0) AS LEFT_CALCULATION_VALUE,\r\n IFNULL(T.REP_MONEY_CNY, 0) AS RIGHT_CALCULATION_VALUE,\r\n ABS(IFNULL(T.REP_COUNT, 0) - IFNULL(T.REP_MONEY_CNY, 0)) AS DIFFERENCE\r\nFROM t_r_lczx_zcxx_zcjy_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) IN\r\n (\'03\', \'04\', \'05\', \'06\', \'07\', \'08\')\r\nAND IFNULL(T.REP_COUNT, 0) <> IFNULL(T.REP_MONEY_CNY, 0);');
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.034', 't_r_lczx_zcxx_zcjy_bank', '当{资金流动类型}在01-08时，{资产计量方式}为必填项', '当{资金流动类型}在01-08时，{资产计量方式}为必填项', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST, \'REP_CAL_TYPE,REP_MONEY_FLOW_TYPE\' 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 T_R_LCZX_ZCXX_ZCJY_BANK T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.REP_CAL_TYPE IS NULL AND T.REP_MONEY_FLOW_TYPE IN (\'01\',\'02\',\'03\',\'04\',\'05\',\'06\',\'07\',\'08\');');
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.074', 't_r_lczx_zcxx_zcjy_bank', '当{资金流动类型}在01-08以外时，{资产计量方式}为不可填', '当{资金流动类型}在01-08以外时，{资产计量方式}为不可填', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2022-04-24 10:20:46', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST,\r\n \'REP_MONEY_FLOW_TYPE,REP_CAL_TYPE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_zcxx_zcjy_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND NUM_CHAR_EXTRACT(T.REP_MONEY_FLOW_TYPE, 2) not IN\r\n (\'01\', \'02\', \'03\', \'04\', \'05\', \'06\', \'07\', \'08\')\r\nAND T.REP_CAL_TYPE IS NOT NULL;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.005', 't_r_lczx_underlying_assets_bank', '{对应资管及委外资产行内资产/负债编码}长度不能超过40位', '{对应资管及委外资产行内资产/负债编码}长度不能超过40位', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_underlying_assets_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND length(convert (ASSET_LIABILITY_CODE using GBK))>40\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.023', 't_r_lczx_underlying_assets_bank', '{底层资产行内资产/负债编码}长度不超过40位', '{底层资产行内资产/负债编码}长度不超过40位', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'UNDERLYING_ASSET_LIABILITY_CODE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_underlying_assets_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND length(convert (UNDERLYING_ASSET_LIABILITY_CODE using GBK))>40 \r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.034', 't_r_lczx_underlying_assets_bank', '{持仓日期}日期类型，八位，格式为yyyymmdd', '{持仓日期}日期类型，八位，格式为yyyymmdd', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPORT_DATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_lczx_underlying_assets_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND IS_DATE_TYPE(REPORT_DATE,1)=0 \r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.040', 't_r_lczx_underlying_assets_bank', '{对应资管及委外资产行内资产/负债编码}、{底层资产行内资产/负债编码}、{持仓日期}三个要素在不同记录间不得重复,不得重复登记', '对应资管及委外资产行内资产/负债编码,底层资产行内资产负债/编码和持仓日期记录在系统中重复', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,UNDERLYING_ASSET_LIABILITY_CODE,REPORT_DATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM(\r\nselect N_ID ,FILE_DATE,FUND_CATEGORY,DEPT_NO,count(1)over(PARTITION by ASSET_LIABILITY_CODE,UNDERLYING_ASSET_LIABILITY_CODE,REPORT_DATE,FUND_CATEGORY) as number from (\r\nselect DEPT_NO,ASSET_LIABILITY_CODE,UNDERLYING_ASSET_LIABILITY_CODE,REPORT_DATE, N_ID ,FILE_DATE,FUND_CATEGORY from t_r_lczx_underlying_assets_bank\r\nunion all \r\nselect DEPT_NO,ASSET_LIABILITY_CODE,UNDERLYING_ASSET_LIABILITY_CODE,REPORT_DATE, null as N_ID , \'@FILE_DATE\' as FILE_DATE,FUND_CATEGORY from t_r_lczx_underlying_assets_result) a\r\n) T\r\nwhere FILE_DATE = \'@FILE_DATE\'\r\nAND N_ID IS NOT NULL\r\nAND number>1 \r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
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.037.017', 't_r_manager_direct_bank', '{借款人类型}字段类型为1位整数型,只能填1或者2', '{借款人类型}字段类型为1位整数型,只能填1或者2', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST, \'LOANER_TYPE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM t_r_manager_direct_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\' \r\nAND LOANER_TYPE NOT IN (\'1\',\'2\') \r\nGROUP BY T.FILE_DATE, T.FUND_CATEGORY');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.054.047', 't_r_lczx_zcys_all_bank', '{付息频率}最长为2位整数型', '{付息频率}最长为2位整数型', 'sql', '1', NULL, 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 \'INTPAY_CYCLE_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(ABS(T.INTPAY_CYCLE_05))>2 OR (ABS(T.INTPAY_CYCLE_05 ) REGEXP \'[^0-9]\') = 1)\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.054.081', 't_r_lczx_zcys_all_bank', '{融资人类型（按技术领域划分）}为必填项，值必须在值域以内', '{融资人类型（按技术领域划分）}为必填项，值必须在值域以内', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'FINAN_TECH_FIELD_05\' 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 ( FINAN_TECH_FIELD_05 IS NULL OR FINAN_TECH_FIELD_05 NOT IN (\'01\',\'99\'))\r\nAND T.REP_ASSET_TYPE IN (SELECT ITEM_KEY FROM T_P_LCZX_ZCYS_REPORT WHERE ITEM_GROUP =\'T_R_LCZX_ZCYS_05\') \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.054.126', 't_r_lczx_zcys_all_bank', '{行权周期}最长5位整数型', '{行权周期}最长5位整数型', 'sql', '1', NULL, 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 \'EXERCISE_TERM_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'C10307\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n t_r_lczx_zcys_all_bank T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\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(ABS (T.EXERCISE_TERM_05))>5 OR (ABS(T.EXERCISE_TERM_05 ) REGEXP \'[^0-9]\') = 1)\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.054.138', 't_r_lczx_zcys_all_bank', '{重定价周期}最长为5为整数型', '{重定价周期}最长为5为整数型', 'sql', '1', NULL, 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 \'REPRICING_TERM_05\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'C10307\' AS INSTI_TYPE,\r\n null AS LEFT_CALCULATION_VALUE,\r\n null AS RIGHT_CALCULATION_VALUE,\r\n null AS DIFFERENCE\r\nFROM\r\n t_r_lczx_zcys_all_bank T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\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(ABS (T.REPRICING_TERM_05))>5 OR (ABS(T.REPRICING_TERM_05 ) REGEXP \'[^0-9]\') = 1)\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.055.015', 't_r_lczx_zcys_all_bank', '{所属国家或地区}值域范围内单选', '{所属国家或地区}值域范围内单选', 'sql', '1', NULL, 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 \'AREA_15\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n \'Z70033\' AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM\r\n t_r_lczx_zcys_all_bank T\r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\'\r\nAND (T.DEPT_NO=\'@DEPT_NO\' OR \'@DEPT_NO\'=\'\')\r\nand T.REP_ASSET_TYPE IN (\r\n SELECT\r\n ITEM_KEY\r\n FROM\r\n T_P_LCZX_ZCYS_REPORT\r\n WHERE\r\n ITEM_GROUP = \'T_R_LCZX_ZCYS_15\')\r\nand (GET_DICT(\'LCZX_COUNTRY_AREA\',AREA_15)=0 OR length(AREA_15)<>3)\r\nand AREA_15 is not null\r\nGROUP BY\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299.A.1-1.061', 't_r_299_1_1', '规则39：表1-1，产品募集余额总计大于表2-1资产合计，但未分配利润无数，请核实', '规则39：表1-1，产品募集余额总计大于表2-1资产合计，但未分配利润无数，请核实', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '警告', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST, MAX(COL_ORG) AS COLUMN_CODE,\r\n MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM (SELECT CASE\r\n WHEN SUM(IF(ITEM_CODE = \'100000\' \r\n AND COL = \'G\', T.V, 0)) >\r\n SUM(IF(ITEM_CODE = \'700000\'\r\n AND COL = \'G\', T.V, 0)) \r\n AND\r\n SUM(IF(ITEM_CODE = \'823000\' AND COL = \'N\', T.V, 0)) = 0 THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, T.FDATE AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME = \'1_1\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME = \'1_1\' THEN\r\n COL_ORG\r\n END) AS COL_ORG, FUND_CATEGORY AS INSTI_TYPE \r\n FROM T_R_299_REPORT_RESULT T\r\n WHERE TABLE_NAME IN (\'1_1\', \'2_1\')\r\n AND (ITEM_CODE IN (\'100000\', \'700000\') AND COL = \'G\')\r\n OR (ITEM_CODE IN (\'823000\') AND COL = \'N\')\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE) T1\r\nWHERE T1.FILE_DATE = \'@FILE_DATE\'\r\nAND T1.RESULT_CODE = \'0\'\r\nGROUP BY T1.FILE_DATE, T1.INSTI_TYPE');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('RH299_BANK.A.1-1.061', 't_r_299_1_1_bank', '规则39：表1-1，产品募集余额总计大于表2-1资产合计，但未分配利润无数，请核实', '规则39：表1-1，产品募集余额总计大于表2-1资产合计，但未分配利润无数，请核实', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '警告', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT N_ID) AS N_ID_LIST, MAX(COL_ORG) AS COLUMN_CODE,\r\n MAX(RESULT_CODE) AS RESULT_CODE, FILE_DATE, INSTI_TYPE, \'\' AS LEFT_CALCULATION_VALUE, \'\' AS RIGHT_CALCULATION_VALUE,\'\' AS DIFFERENCE\r\nFROM (SELECT CASE\r\n WHEN SUM(IF(ITEM_CODE = \'100000\' \r\n AND COL = \'G\', T.V, 0)) >\r\n SUM(IF(ITEM_CODE = \'700000\'\r\n AND COL = \'G\', T.V, 0)) \r\n AND\r\n SUM(IF(ITEM_CODE = \'823000\' AND COL = \'N\', T.V, 0)) = 0 THEN\r\n 0\r\n ELSE\r\n 1\r\n END AS RESULT_CODE, T.FDATE AS FILE_DATE,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME = \'1_1\' THEN\r\n N_ID\r\n END) AS N_ID,\r\n GROUP_CONCAT(DISTINCT CASE\r\n WHEN TABLE_NAME = \'1_1\' THEN\r\n COL_ORG\r\n END) AS COL_ORG, FUND_CATEGORY AS INSTI_TYPE\r\n FROM t_r_299_report_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\', \'700000\') AND COL = \'G\')\r\n OR (ITEM_CODE IN ( \'823000\') AND COL = \'N\')\r\n GROUP BY T.FDATE, T.FUND_CATEGORY, T.FUND_TYPE) T1\r\nWHERE T1.RESULT_CODE = \'0\'\r\nGROUP BY T1.FILE_DATE, T1.INSTI_TYPE');
DELETE FROM pawmrpcs.data_check_rule_set WHERE RULE_CODE IN (
'LCZX.A.002.071',
'LCZX.A.002.074',
'LCZX.A.002.077',
'LCZX.A.024.046',
'LCZX.A.024.047',
'LCZX.A.024.048',
'LCZX.A.056.045',
'LCZX.A.056.046',
'LCZX.A.056.047',
'LCZX.A.056.048',
'LCZX.A.033.091',
'LCZX_BANK.A.002.071',
'LCZX_BANK.A.002.074',
'LCZX_BANK.A.002.077',
'LCZX_BANK.A.024.046',
'LCZX_BANK.A.024.047',
'LCZX_BANK.A.024.048',
'LCZX_BANK.A.056.045',
'LCZX_BANK.A.056.046',
'LCZX_BANK.A.056.047',
'LCZX_BANK.A.056.048'
);
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.071', 't_r_lczx_prod_duration_register', '{该币种累计申购金额}为必填项', '该币种累计申购金额（元）要素不可为空。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'SUBSCRIPTIONHONOR\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSCRIPTIONHONOR, \',\', 2), \',\',\r\n -1) AS BZLJSGJE, REPISSUEPARTYID, N_ID, FILE_DATE,\r\n FUND_CATEGORY\r\n FROM T_R_LCZX_PROD_DURATION_REGISTER A) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.BZLJSGJE regexp \'[^ ]\'=0;');
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.074', 't_r_lczx_prod_duration_register', '{该币种累计兑付金额}为必填项', '该币种累计兑付金额（元）要素不可为空。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'SUBSCRIPTIONHONOR\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSCRIPTIONHONOR, \',\', 3), \',\',\r\n -1) AS BZDFJE, REPISSUEPARTYID, N_ID , FILE_DATE,\r\n FUND_CATEGORY\r\n FROM T_R_LCZX_PROD_DURATION_REGISTER A) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.BZDFJE regexp \'[^ ]\'=0;');
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.077', 't_r_lczx_prod_duration_register', '{该币种累计兑付收益金额}为必填项', '该币种累计兑付收益金额（元）要素不可为空。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'SUBSCRIPTIONHONOR\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT SUBSTRING_INDEX(SUBSCRIPTIONHONOR, \',\', -1) AS DFSYJE,\r\n REPISSUEPARTYID, N_ID , FILE_DATE, FUND_CATEGORY\r\n FROM T_R_LCZX_PROD_DURATION_REGISTER A) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.DFSYJE regexp \'[^ ]\';');
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.024.046', 't_r_lczx_zcys_16', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字部分为decimal(5,2)(其中数字部分最长5位，其中2位小数，不包括运算符)', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字部分为decimal(5,2)(其中数字部分最长5位，其中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 T.N_ID) AS N_ID_LIST,\r\n \'REP_FUND_INVEST_ASSET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FUND_CATEGORY,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET\r\nfrom T_R_LCZX_ZCYS_16 a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET)-length(replace(REP_FUND_INVEST_ASSET,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE = \'@FILE_DATE\' \r\nAND (\r\n (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000>1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000>1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))<>2))\r\n)\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n)\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000>1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000>1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))<>2))\r\n)\r\n)\r\ngroup by T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.024.047', 't_r_lczx_zcys_16', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字需大于等于0，小于等于100。', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字需大于等于0，小于等于100。', '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_FUND_INVEST_ASSET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FUND_CATEGORY,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET\r\nfrom T_R_LCZX_ZCYS_16 a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET)-length(replace(REP_FUND_INVEST_ASSET,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE = \'@FILE_DATE\' \r\nAND (\r\n (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))=0))\r\n)\r\nand \r\n(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)>100 \r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)<0)\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n)\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))=0))\r\n)\r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)>100\r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)<0\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1)>100\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1)<0)\r\n)\r\ngroup by T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.024.048', 't_r_lczx_zcys_16', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 名称部分不得含有英文百分号、冒号、分号、连字号', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 名称部分不得含有英文百分号、冒号、分号、连字号', '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_FUND_INVEST_ASSET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FUND_CATEGORY,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET\r\nfrom T_R_LCZX_ZCYS_16 a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET)-length(replace(REP_FUND_INVEST_ASSET,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE=\'@FILE_DATE\'\r\nand (\r\n (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))=0))\r\n)\r\nand \r\n(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)<100 \r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)>=0)\r\nand SUBSTRING(REP_FUND_INVEST_ASSET,length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\':\',1))+2) regexp \'%|;|:|-\'=1\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))=0))\r\n)\r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)<100\r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)>=0\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1)<100\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1)>=0)\r\nand SUBSTRING(REP_FUND_INVEST_ASSET,length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\':\',1))+2) regexp \'%|;|:|-\'=1\r\n)\r\n)\r\ngroup by T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.033.091', 't_r_lczx_filing_register', '产品募集方式选择“公募”，募集起始日期（从）应晚于申报登记日至少 10 个工作日，产品募集方式选择“私募”，募集起始日期（从）应晚于申报登记日至少 2 个工作日；', '产品募集方式选择“公募”，募集起始日期（从）应晚于申报登记日至少 10 个工作日，产品募集方式选择“私募”，募集起始日期（从）应晚于申报登记日至少 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,\r\n \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT( DISTINCT T.N_ID ) AS N_ID_LIST,\r\n \'SUBSCRIPTION_TYPE,SUBSCRIPTION_START_DATE_START\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE FROM (\r\nselect n_id,file_date,SUBSCRIPTION_TYPE,SUBSCRIPTION_START_DATE_START,b.HOLIDAY,b.flag,sum(b.flag)over(partition by n_id ) as days,FUND_CATEGORY\r\nfrom T_R_LCZX_FILING_REGISTER a\r\njoin (select DATE_FORMAT( SUBSTR( HOLIDAY, 1, 10 ), \'%Y%m%d\' ) as HOLIDAY,DV_HOLIDAY_TYPE,\r\n if(IS_HOLIDAY=1,0,1) as flag\r\nfrom t_s_bf_holiday \r\nwhere DV_HOLIDAY_TYPE = \'WORKDAY\') b\r\non (a.file_date<=b.HOLIDAY and a.SUBSCRIPTION_START_DATE_START>=b.HOLIDAY)\r\nand b.DV_HOLIDAY_TYPE = \'WORKDAY\'\r\nwhere file_date=\'@FILE_DATE\') T\r\nWHERE ((SUBSCRIPTION_TYPE =\'01\' and (days < 10 or SUBSCRIPTION_START_DATE_START<file_date)) or (SUBSCRIPTION_TYPE =\'02\' and (days < 2 or SUBSCRIPTION_START_DATE_START<file_date)))\r\nGROUP BY\r\n 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.056.045', 't_r_lczx_zcys_all', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 其中比例部分格式为数字+“%”，如10%；或者数字+“%”+“-”+数字+“%”组成，如10%-20%。', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 其中比例部分格式为数字+“%”，如10%；或者数字+“%”+“-”+数字+“%”组成，如10%-20%。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST,\r\n \'REP_FUND_INVEST_ASSET_16\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, \'Z70033\' AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET_16, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET_16\r\nfrom T_R_LCZX_ZCYS_ALL a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET_16)-length(replace(REP_FUND_INVEST_ASSET_16,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE =\'@FILE_DATE\' \r\nAND (\r\n(REP_FUND_INVEST_ASSET_16 not LIKE \'%%:%\' \r\nand REP_FUND_INVEST_ASSET_16 not LIKE \'%%-%%:%\')\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=1 \r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))>1\r\n))\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=1\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))>1\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1),\'.\',\'\'))>1\r\n))\r\n))\r\ngroup by T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.056.046', 't_r_lczx_zcys_all', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字部分为decimal(5,2)(其中数字部分最长5位，其中2位小数，不包括运算符)', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字部分为decimal(5,2)(其中数字部分最长5位，其中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 T.N_ID) AS N_ID_LIST,\r\n \'REP_FUND_INVEST_ASSET_16\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, \'Z70033\' AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET_16, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET_16\r\nfrom T_R_LCZX_ZCYS_ALL a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET_16)-length(replace(REP_FUND_INVEST_ASSET_16,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE = \'@FILE_DATE\' \r\nAND (\r\n (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000>1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000>1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))<>2))\r\n)\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n)\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000>1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000>1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))<>2))\r\n)\r\n)\r\ngroup by T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.056.047', 't_r_lczx_zcys_all', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字需大于等于0，小于等于100。', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字需大于等于0，小于等于100。', '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_FUND_INVEST_ASSET_16\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, \'Z70033\' AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET_16, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET_16\r\nfrom T_R_LCZX_ZCYS_ALL a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET_16)-length(replace(REP_FUND_INVEST_ASSET_16,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE = \'@FILE_DATE\' \r\nAND (\r\n (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))=0))\r\n)\r\nand \r\n(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)>100 \r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)<0)\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n)\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))=0))\r\n)\r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)>100\r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)<0\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1)>100\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1)<0)\r\n)\r\ngroup by T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.056.048', '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_FUND_INVEST_ASSET_16\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, \'Z70033\' AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET_16, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET_16\r\nfrom T_R_LCZX_ZCYS_ALL a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET_16)-length(replace(REP_FUND_INVEST_ASSET_16,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE=\'@FILE_DATE\'\r\nand (\r\n (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))=0))\r\n)\r\nand \r\n(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)<100 \r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)>=0)\r\nand SUBSTRING(REP_FUND_INVEST_ASSET_16,length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\':\',1))+2) regexp \'%|;|:|-\'=1\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))=0))\r\n)\r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)<100\r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)>=0\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1)<100\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1)>=0)\r\nand SUBSTRING(REP_FUND_INVEST_ASSET_16,length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\':\',1))+2) regexp \'%|;|:|-\'=1\r\n)\r\n)\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.071', 't_r_lczx_prod_duration_register_bank', '{该币种累计申购金额}为必填项', '该币种累计申购金额（元）要素不可为空。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'SUBSCRIPTIONHONOR\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSCRIPTIONHONOR, \',\', 2), \',\',\r\n -1) AS BZLJSGJE, REPISSUEPARTYID, N_ID, FILE_DATE,\r\n FUND_CATEGORY\r\n FROM t_r_lczx_prod_duration_register_bank A) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.BZLJSGJE regexp \'[^ ]\'=0;');
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.074', 't_r_lczx_prod_duration_register_bank', '{该币种累计兑付金额}为必填项', '该币种累计兑付金额（元）要素不可为空。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'SUBSCRIPTIONHONOR\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSCRIPTIONHONOR, \',\', 3), \',\',\r\n -1) AS BZDFJE, REPISSUEPARTYID, N_ID , FILE_DATE,\r\n FUND_CATEGORY\r\n FROM t_r_lczx_prod_duration_register_bank A) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.BZDFJE regexp \'[^ ]\'=0;');
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.077', 't_r_lczx_prod_duration_register_bank', '{该币种累计兑付收益金额}为必填项', '该币种累计兑付收益金额（元）要素不可为空。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'SUBSCRIPTIONHONOR\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT SUBSTRING_INDEX(SUBSCRIPTIONHONOR, \',\', -1) AS DFSYJE,\r\n REPISSUEPARTYID, N_ID , FILE_DATE, FUND_CATEGORY\r\n FROM t_r_lczx_prod_duration_register_bank A) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.DFSYJE regexp \'[^ ]\';');
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.024.046', 't_r_lczx_zcys_16_bank', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字部分为decimal(5,2)(其中数字部分最长5位，其中2位小数，不包括运算符)', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字部分为decimal(5,2)(其中数字部分最长5位，其中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 T.N_ID) AS N_ID_LIST,\r\n \'REP_FUND_INVEST_ASSET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FUND_CATEGORY,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET\r\nfrom T_R_LCZX_ZCYS_16_BANK a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET)-length(replace(REP_FUND_INVEST_ASSET,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE = \'@FILE_DATE\' \r\nAND (\r\n (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000>1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000>1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))<>2))\r\n)\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n)\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000>1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000>1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))<>2))\r\n)\r\n)\r\ngroup by T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.024.047', 't_r_lczx_zcys_16_bank', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字需大于等于0，小于等于100。', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字需大于等于0，小于等于100。', '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_FUND_INVEST_ASSET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FUND_CATEGORY,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET\r\nfrom T_R_LCZX_ZCYS_16_BANK a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET)-length(replace(REP_FUND_INVEST_ASSET,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE = \'@FILE_DATE\' \r\nAND (\r\n (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))=0))\r\n)\r\nand \r\n(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)>100 \r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)<0)\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n)\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))=0))\r\n)\r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)>100\r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)<0\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1)>100\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1)<0)\r\n)\r\ngroup by T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.024.048', 't_r_lczx_zcys_16_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_FUND_INVEST_ASSET\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nfrom (\r\nselect a.N_ID,a.FUND_CATEGORY,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET\r\nfrom T_R_LCZX_ZCYS_16_BANK a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET)-length(replace(REP_FUND_INVEST_ASSET,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE=\'@FILE_DATE\'\r\nand (\r\n (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))=0))\r\n)\r\nand \r\n(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)<100 \r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)>=0)\r\nand SUBSTRING(REP_FUND_INVEST_ASSET,length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\':\',1))+2) regexp \'%|;|:|-\'=1\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n\r\nand\r\n((REP_FUND_INVEST_ASSET not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1),\'.\',-1))=0))\r\n)\r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)<100\r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',1)>=0\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1)<100\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\'%\',2),\'-\',-1)>=0)\r\nand SUBSTRING(REP_FUND_INVEST_ASSET,length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET,\':\',1))+2) regexp \'%|;|:|-\'=1\r\n)\r\n)\r\ngroup by T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.056.045', 't_r_lczx_zcys_all_bank', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 其中比例部分格式为数字+“%”，如10%；或者数字+“%”+“-”+数字+“%”组成，如10%-20%。', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 其中比例部分格式为数字+“%”，如10%；或者数字+“%”+“-”+数字+“%”组成，如10%-20%。', '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_FUND_INVEST_ASSET_16\' 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 (\r\nselect a.N_ID,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET_16, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET_16\r\nfrom T_R_LCZX_ZCYS_ALL_BANK a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET_16)-length(replace(REP_FUND_INVEST_ASSET_16,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE =\'@FILE_DATE\' \r\nAND (\r\n(REP_FUND_INVEST_ASSET_16 not LIKE \'%%:%\' \r\nand REP_FUND_INVEST_ASSET_16 not LIKE \'%%-%%:%\')\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=1 \r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))>1\r\n))\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=1\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))>1\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1),\'.\',\'\'))>1\r\n))\r\n))\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.056.046', 't_r_lczx_zcys_all_bank', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字部分为decimal(5,2)(其中数字部分最长5位，其中2位小数，不包括运算符)', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字部分为decimal(5,2)(其中数字部分最长5位，其中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 T.N_ID) AS N_ID_LIST,\r\n \'REP_FUND_INVEST_ASSET_16\' 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 (\r\nselect a.N_ID,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET_16, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET_16\r\nfrom T_R_LCZX_ZCYS_ALL_BANK a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET_16)-length(replace(REP_FUND_INVEST_ASSET_16,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE = \'@FILE_DATE\' \r\nAND (\r\n (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000>1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000>1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))<>2))\r\n)\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n)\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000>1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000>1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))<>2))\r\n)\r\n)\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.056.047', 't_r_lczx_zcys_all_bank', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字需大于等于0，小于等于100。', '{基金投资资产}需填写基金投资资产的对应比例及名称。名称及对应比例以“:”作为分隔符。每类二级资产以英文分号作为分隔符。 比例中的数字需大于等于0，小于等于100。', '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_FUND_INVEST_ASSET_16\' 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 (\r\nselect a.N_ID,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET_16, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET_16\r\nfrom T_R_LCZX_ZCYS_ALL_BANK a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET_16)-length(replace(REP_FUND_INVEST_ASSET_16,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE = \'@FILE_DATE\' \r\nAND (\r\n (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))=0))\r\n)\r\nand \r\n(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)>100 \r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)<0)\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n)\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))=0))\r\n)\r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)>100\r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)<0\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1)>100\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1)<0)\r\n)\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.056.048', '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_FUND_INVEST_ASSET_16\' 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 (\r\nselect a.N_ID,a.FILE_DATE,\r\nSUBSTRING_INDEX(SUBSTRING_INDEX( a.REP_FUND_INVEST_ASSET_16, \';\', b.help_topic_id + 1 ) ,\';\',-1) as REP_FUND_INVEST_ASSET_16\r\nfrom T_R_LCZX_ZCYS_ALL_BANK a \r\njoin help_topic b \r\non b.help_topic_id<(length(a.REP_FUND_INVEST_ASSET_16)-length(replace(REP_FUND_INVEST_ASSET_16,\';\',\'\'))+1)) T\r\nWHERE FILE_DATE=\'@FILE_DATE\'\r\nand (\r\n (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%:%\' \r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\n))\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))=0))\r\n)\r\nand \r\n(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)<100 \r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)>=0)\r\nand SUBSTRING(REP_FUND_INVEST_ASSET_16,length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\':\',1))+2) regexp \'%|;|:|-\'=1\r\n)\r\nor (\r\n(REP_FUND_INVEST_ASSET_16 LIKE \'%%-%%:%\'\r\nand ( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1))-length(replace (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',\'\'))<2\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1) regexp \'[^0-9.]\'=0\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1))-length(replace(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1),\'.\',\'\'))<2\r\n))\r\n\r\nand\r\n((REP_FUND_INVEST_ASSET_16 not LIKE \'%.%\'\r\nand SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1)\r\nor \r\n (REP_FUND_INVEST_ASSET_16 LIKE \'%.%\'\r\nand( SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)/1000<1\r\nor length(SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1),\'.\',-1))=0))\r\n)\r\nand (SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)<100\r\nor SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',1)>=0\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1)<100\r\nor SUBSTRING_INDEX(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\'%\',2),\'-\',-1)>=0)\r\nand SUBSTRING(REP_FUND_INVEST_ASSET_16,length(SUBSTRING_INDEX(REP_FUND_INVEST_ASSET_16,\':\',1))+2) regexp \'%|;|:|-\'=1\r\n)\r\n)\r\ngroup by T.FILE_DATE;');
DELETE FROM pawmrpcs.data_check_rule_set WHERE RULE_CODE IN(
'LCZX.A.002.031',
'LCZX.A.002.077',
'LCZX.A.032.011',
'LCZX.A.032.016',
'LCZX.A.032.021',
'LCZX.A.032.035',
'LCZX.A.033.091',
'LCZX_BANK.A.002.031',
'LCZX_BANK.A.002.077',
'LCZX_BANK.A.032.011',
'LCZX_BANK.A.032.016',
'LCZX_BANK.A.032.021',
'LCZX_BANK.A.032.035'
);
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.031', 't_r_lczx_prod_duration_register', '{净值币种}填写币种对应的三位代码(如 CNY)', '{净值币种}填写币种对应的三位代码(如 CNY)', 'sql', '1', NULL, 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 N_ID as N_ID_LIST,\r\n \'REPNETVALCCY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n 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\n T_R_LCZX_PROD_DURATION_REGISTER T \r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\' \r\n AND ( T.REPNETVALCCY REGEXP \'[^A-Z]\' = 1 OR LENGTH ( T.REPNETVALCCY ) <> 3 OR GET_DICT ( \'LCZX_CURRENCY\', REPNETVALCCY ) = 0 ) \r\n and REPNETVALCCY is not null;');
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.077', 't_r_lczx_prod_duration_register', '{该币种累计兑付收益金额}为必填项', '该币种累计兑付收益金额（元）要素不可为空。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'SUBSCRIPTIONHONOR\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT SUBSTRING_INDEX(SUBSCRIPTIONHONOR, \',\', -1) AS DFSYJE,\r\n REPISSUEPARTYID, N_ID , FILE_DATE, FUND_CATEGORY\r\n FROM T_R_LCZX_PROD_DURATION_REGISTER A) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.DFSYJE regexp \'[^ ]\'=0;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.032.011', 't_r_lczx_underlying_assets', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总折算人民币发行金额}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总折算人民币发行金额}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,AGG_AMOUNT\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,AGG_AMOUNT from t_r_lczx_underlying_assets\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.032.016', 't_r_lczx_underlying_assets', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总数量}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总数量}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,AGG_QUANTITY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,AGG_QUANTITY from t_r_lczx_underlying_assets\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.032.021', 't_r_lczx_underlying_assets', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产未投资头寸}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产未投资头寸}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,NON_INVESTED_AMOUNT\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,NON_INVESTED_AMOUNT from t_r_lczx_underlying_assets\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.032.035', 't_r_lczx_underlying_assets', '{持仓日期}值为周末或者月末的最后一个自然日', '{持仓日期}值为周末或者月末的最后一个自然日', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPORT_DATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\n FROM t_r_lczx_underlying_assets T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND (REPORT_DATE <> DATE_FORMAT(last_day(REPORT_DATE),\'%Y%m%d\')\r\nAND (DAYOFWEEK(REPORT_DATE) <> 1 AND DAYOFWEEK(REPORT_DATE) <> 7)\r\nOR (IS_DATE_TYPE(REPORT_DATE, 1)=0))\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
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.091', 't_r_lczx_filing_register', '产品募集方式选择“公募”，募集起始日期（从）应晚于申报登记日至少 10 个工作日，产品募集方式选择“私募”，募集起始日期（从）应晚于申报登记日至少 2 个工作日；', '产品募集方式选择“公募”，募集起始日期（从）应晚于申报登记日至少 10 个工作日，产品募集方式选择“私募”，募集起始日期（从）应晚于申报登记日至少 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,\r\n \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT( DISTINCT T.N_ID ) AS N_ID_LIST,\r\n \'SUBSCRIPTION_TYPE,SUBSCRIPTION_START_DATE_START\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE FROM (\r\nselect n_id,file_date,SUBSCRIPTION_TYPE,SUBSCRIPTION_START_DATE_START,b.HOLIDAY,b.flag,sum(b.flag)over(partition by n_id ) as days,FUND_CATEGORY\r\nfrom T_R_LCZX_FILING_REGISTER a\r\njoin (select DATE_FORMAT( SUBSTR( HOLIDAY, 1, 10 ), \'%Y%m%d\' ) as HOLIDAY,DV_HOLIDAY_TYPE,\r\n if(IS_HOLIDAY=1,0,1) as flag\r\nfrom t_s_bf_holiday \r\nwhere DV_HOLIDAY_TYPE = \'WORKDAY\') b\r\non (a.file_date<=b.HOLIDAY and a.SUBSCRIPTION_START_DATE_START>=b.HOLIDAY)\r\nand b.DV_HOLIDAY_TYPE = \'WORKDAY\'\r\nunion all\r\nselect n_id,file_date,SUBSCRIPTION_TYPE,SUBSCRIPTION_START_DATE_START,\'\' as HOLIDAY, \'\' as flag, \'\'as days,FUND_CATEGORY\r\nfrom T_R_LCZX_FILING_REGISTER b\r\nwhere SUBSCRIPTION_START_DATE_START<file_date\r\n) T\r\nWHERE FILE_DATE=\'@FILE_DATE\'\r\nand (SUBSCRIPTION_START_DATE_START<file_date\r\nor((SUBSCRIPTION_TYPE =\'01\' and days < 10) \r\nor (SUBSCRIPTION_TYPE =\'02\' and days < 2 )))\r\nGROUP BY\r\n 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.031', 't_r_lczx_prod_duration_register_bank', '{净值币种}填写币种对应的三位代码(如 CNY)', '{净值币种}填写币种对应的三位代码(如 CNY)', 'sql', '1', NULL, 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 N_ID as N_ID_LIST,\r\n \'REPNETVALCCY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n 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\n t_r_lczx_prod_duration_register_bank T \r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\' \r\n AND ( T.REPNETVALCCY REGEXP \'[^A-Z]\' = 1 OR LENGTH ( T.REPNETVALCCY ) <> 3 OR GET_DICT ( \'LCZX_CURRENCY\', REPNETVALCCY ) = 0 ) \r\n and REPNETVALCCY is not null;');
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.077', 't_r_lczx_prod_duration_register_bank', '{该币种累计兑付收益金额}为必填项', '该币种累计兑付收益金额（元）要素不可为空。', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'SUBSCRIPTIONHONOR\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\nFROM (SELECT SUBSTRING_INDEX(SUBSCRIPTIONHONOR, \',\', -1) AS DFSYJE,\r\n REPISSUEPARTYID, N_ID , FILE_DATE, FUND_CATEGORY\r\n FROM t_r_lczx_prod_duration_register_bank A) T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.DFSYJE regexp \'[^ ]\'=0;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.011', 't_r_lczx_underlying_assets_bank', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总折算人民币发行金额}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总折算人民币发行金额}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,AGG_AMOUNT\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets_bank T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,AGG_AMOUNT from t_r_lczx_underlying_assets\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.016', 't_r_lczx_underlying_assets_bank', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总数量}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总数量}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,AGG_QUANTITY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets_bank T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,AGG_QUANTITY from t_r_lczx_underlying_assets\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.021', 't_r_lczx_underlying_assets_bank', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产未投资头寸}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产未投资头寸}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,NON_INVESTED_AMOUNT\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets_bank T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,NON_INVESTED_AMOUNT from t_r_lczx_underlying_assets\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.035', 't_r_lczx_underlying_assets_bank', '{持仓日期}值为周末或者月末的最后一个自然日', '{持仓日期}值为周末或者月末的最后一个自然日', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n N_ID as N_ID_LIST,\r\n \'REPORT_DATE\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE,\r\n NULL AS LEFT_CALCULATION_VALUE, NULL AS RIGHT_CALCULATION_VALUE,\r\n NULL AS DIFFERENCE\r\n FROM t_r_lczx_underlying_assets_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND (REPORT_DATE <> DATE_FORMAT(last_day(REPORT_DATE),\'%Y%m%d\')\r\nAND (DAYOFWEEK(REPORT_DATE) <> 1 AND DAYOFWEEK(REPORT_DATE) <> 7)\r\nOR (IS_DATE_TYPE(REPORT_DATE, 1)=0)) \r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE DEPT_NO=\'@DEPT_NO\' END;');
DELETE FROM pawmrpcs.data_check_rule_set WHERE RULE_CODE IN(
'LCZX.A.001.058',
'LCZX.A.004.026',
'LCZX.A.033.017',
'LCZX.A.033.134',
'LCZX_BANK.A.004.026',
'LCZX_BANK.A.032.011',
'LCZX_BANK.A.032.016',
'LCZX_BANK.A.032.021'
);
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.058', '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 N_ID) AS N_ID_LIST, \'PROD_REG_CODE\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE,\r\n T.FUND_CATEGORY AS INSTI_TYPE, NULL AS LEFT_CALCULATION_VALUE,\r\n NULL AS RIGHT_CALCULATION_VALUE, NULL AS DIFFERENCE\r\nFROM T_R_LCZX_PROD_RAISE_REGISTER T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND PROD_REG_CODE not in (select prod_reg_id from t_r_lczx_register_code where prod_reg_id 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.004.026', 't_r_lczx_zcxx_zcjy', '当{币种}为“CNY”时，{折算人民币金额（元）}={发生金额}', '当{币种}为“CNY”时，{折算人民币金额（元）}={发生金额}', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST,\r\n \'REP_MONEY_CNY,REP_OCCUR_MONEY,REP_CCY\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n REP_MONEY_CNY AS LEFT_CALCULATION_VALUE, REP_OCCUR_MONEY AS RIGHT_CALCULATION_VALUE,\r\n ABS(REP_MONEY_CNY-REP_OCCUR_MONEY) AS DIFFERENCE\r\nFROM T_R_LCZX_ZCXX_ZCJY T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.REP_CCY =\'CNY\'\r\nAND IFNULL(REP_OCCUR_MONEY,0) <> IFNULL(REP_MONEY_CNY,0);');
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.017', 't_r_lczx_filing_register', '{产品设计人姓名}必须在从业人员登记表存在', '{产品设计人姓名}必须在从业人员登记表存在', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT\r\n \'@BATCH_ID\' AS BATCH_ID,\r\n \'@RULE_CODE\' AS RULE_CODE,\r\n GROUP_CONCAT( DISTINCT T.N_ID ) AS N_ID_LIST,\r\n \'DESIGNER_NAME\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE,\r\n 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\n T_R_LCZX_FILING_REGISTER T \r\nWHERE\r\n T.FILE_DATE = \'@FILE_DATE\' \r\n AND DESIGNER_NAME NOT IN ( SELECT DISTINCT NAME FROM t_r_lczx_practitioner_result WHERE NAME IS NOT NULL) \r\nGROUP BY\r\n T.FUND_CATEGORY,\r\n T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX.A.033.134', 't_r_lczx_filing_register', '{产品投资国家或地区}非空时，单选和多选的值都必须在《世界各国和地区名称代码》存在', '产品投资国家或地区不在值域范围内。', 'sql', '1', '', NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID\r\n , \'@RULE_CODE\' AS RULE_CODE\r\n , GROUP_CONCAT(DISTINCT T.N_ID) AS N_ID_LIST\r\n ,\'SPECIFIC_COUNTRY_OR_REGION\' AS COLUMN_CODE\r\n , 0 AS RESULT_CODE, \'@FILE_DATE\' AS FILE_DATE, FUND_CATEGORY AS INSTI_TYPE\r\n , NULL AS LEFT_CALCULATION_VALUE\r\n , NULL AS RIGHT_CALCULATION_VALUE\r\n , NULL AS DIFFERENCE \r\n from (\r\nSELECT a.N_ID,a.FUND_CATEGORY,FILE_DATE\r\n ,SUBSTRING_INDEX( SUBSTRING_INDEX( a.SPECIFIC_COUNTRY_OR_REGION, \',\', h.help_topic_id + 1 ), \',\',- 1 ) AS SPECIFIC_COUNTRY_OR_REGION \r\n FROM T_R_LCZX_FILING_REGISTER a \r\n JOIN pawmrpcs.help_topic h \r\n ON h.help_topic_id < ( ( length( a.SPECIFIC_COUNTRY_OR_REGION ) - length( REPLACE ( a.SPECIFIC_COUNTRY_OR_REGION, \',\', \'\' ) ) + 1 ) )\r\n where SPECIFIC_COUNTRY_OR_REGION is not null)T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nand GET_DICT(\'LCZX_COUNTRY_AREA\', SPECIFIC_COUNTRY_OR_REGION) = 0\r\ngroup by T.FUND_CATEGORY, T.FILE_DATE;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.004.026', 't_r_lczx_zcxx_zcjy_bank', '当{币种}为“CNY”时，{折算人民币金额（元）}={发生金额}', '当{币种}为“CNY”时，{折算人民币金额（元）}={发生金额}', 'sql', '1', NULL, NULL, NULL, NULL, '0', NULL, 'admin', '2021-09-22 11:30:33', 'admin', '2021-09-22 14:40:03', '严重', 'A', 'SELECT \'@BATCH_ID\' AS BATCH_ID, \'@RULE_CODE\' AS RULE_CODE,\r\n T.N_ID AS N_ID_LIST,\r\n \'REP_MONEY_CNY,REP_OCCUR_MONEY,REP_CCY\' AS COLUMN_CODE, 0 AS RESULT_CODE,\r\n \'@FILE_DATE\' AS FILE_DATE, T.FUND_CATEGORY AS INSTI_TYPE,\r\n REP_MONEY_CNY AS LEFT_CALCULATION_VALUE, REP_OCCUR_MONEY AS RIGHT_CALCULATION_VALUE,\r\n ABS(REP_MONEY_CNY-REP_OCCUR_MONEY) AS DIFFERENCE\r\nFROM t_r_lczx_zcxx_zcjy_bank T\r\nWHERE T.FILE_DATE = \'@FILE_DATE\'\r\nAND T.REP_CCY =\'CNY\'\r\nAND IFNULL(REP_OCCUR_MONEY,0) <> IFNULL(REP_MONEY_CNY,0);');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.011', 't_r_lczx_underlying_assets_bank', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总折算人民币发行金额}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总折算人民币发行金额}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,AGG_AMOUNT\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets_bank T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,AGG_AMOUNT from t_r_lczx_underlying_assets_bank\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.016', 't_r_lczx_underlying_assets_bank', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总数量}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产当前总数量}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,AGG_QUANTITY\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets_bank T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,AGG_QUANTITY from t_r_lczx_underlying_assets_bank\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
INSERT INTO pawmrpcs.data_check_rule_set(`RULE_CODE`, `REPORT_CODE`, `RULE_NAME`, `RULE_DESC`, `RULE_TYPE`, `STATUS`, `COL_CODE`, `EXP_TYPE`, `EXP_DEFINE`, `EXP_PARAM`, `UNION_TYPE`, `RULE_FORMULA`, `CREATE_USER`, `CREATE_TIME`, `UPDATE_USER`, `UPDATE_TIME`, `RULE_LEVEL`, `RULE_CODE_CREATE_TYPE`, `RULE_SQL`) VALUES ('LCZX_BANK.A.032.021', 't_r_lczx_underlying_assets_bank', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产未投资头寸}须保持一致', '对于同一{对应资管及委外资产行内资产/负债编码}、同一{持仓日期}下，对于不同{底层资产行内资产/负债编码}的记录，{资管及委外资产未投资头寸}须保持一致', 'sql', '1', NULL, 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 T.N_ID as N_ID_LIST,\r\n \'ASSET_LIABILITY_CODE,REPORT_DATE,UNDERLYING_ASSET_LIABILITY_CODE,NON_INVESTED_AMOUNT\' AS COLUMN_CODE,\r\n 0 AS RESULT_CODE,\r\n \'@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\n t_r_lczx_underlying_assets_bank T\r\n where (ASSET_LIABILITY_CODE,REPORT_DATE) in (\r\n select ASSET_LIABILITY_CODE,REPORT_DATE from (\r\n select distinct ASSET_LIABILITY_CODE,REPORT_DATE,NON_INVESTED_AMOUNT from t_r_lczx_underlying_assets_bank\r\n where FILE_DATE = \'@FILE_DATE\') a\r\n group by ASSET_LIABILITY_CODE,REPORT_DATE \r\n having count(1)>1)\r\nAND CASE WHEN \'@DEPT_NO\'=\'\' THEN 1=1 ELSE T.DEPT_NO=\'@DEPT_NO\' END;');
217
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
